Using code chunks with R code in RMarkdown documents is a well understood (and much appreciated!) topic. In this post I would like to draw attention to a slightly different aspect of RMarkdown, that is the option of writing code chunks in different programming lanugages.
I yet have to find the need to mix and match R and Python code in a single document, but I have found it advantageous to use SQL code chunks.
Like it or not SQL is the de facto language of data, and SQL code is immediately clear to any old BI hand - much more so than a dplyr
pipeline. In addition it allows me to use features of SQL language that do not translate easily to R code.
The first task is creating a database connection; this needs to be done in a R (or Python, but let us stick to R) code chunk.
library(odbc)
con <- dbConnect(odbc::odbc(),
driver = "PostgreSQL Unicode",
server = "db.jla-data.net",
port = 5432,
uid = "babisobot", # user babisobot has select rights only ...
password = "babisobot", # ... so his password need not be too secret :)
database = "dbase")
The next chunk is declared as SQL {sql ... }
and and it is necessary to specify both connection = con
and output.var = "frmVystup"
in the header (i.e. in the curly braces). The quotation marks around output variable are important.
select
date_trunc('day', saved) date,
count(1) volume
from
babisobot
group by
date_trunc('day', saved)
order by
2 desc
limit 5
Now that I have the result of SQL script safely stored in variable frmVystup
I can use it in my futher work in R. For this proof of concept showing the data frame in a simple kable
is enough.
library(kableExtra)
kable(frmVystup, # the variable created in previous chunk
format = 'html',
booktabs = T,
align = c('l', 'r')) %>%
kable_styling(full_width = F) %>%
column_spec(1, width = "6cm")
date | volume |
---|---|
2018-03-25 | 3363 |
2018-04-06 | 1753 |
2018-04-10 | 1741 |
2018-03-27 | 1537 |
2018-04-11 | 1505 |
The last, but not least, thing is not forgetting about closing the database connection on exit.
dbDisconnect(con) # because it is good manners to shut the door and turn off the light