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