{"id":612,"date":"2022-10-05T10:09:59","date_gmt":"2022-10-05T08:09:59","guid":{"rendered":"https:\/\/www.meb.ki.se\/sites\/meb-it\/?p=612"},"modified":"2024-01-30T13:35:13","modified_gmt":"2024-01-30T12:35:13","slug":"loading-temporary-data-into-the-registry-database-and-how-to-do-pass-thru-sql-with-r","status":"publish","type":"post","link":"https:\/\/www.meb.ki.se\/sites\/meb-it\/loading-temporary-data-into-the-registry-database-and-how-to-do-pass-thru-sql-with-r\/","title":{"rendered":"Loading temporary data into the registry database, and how to do pass-thru SQL"},"content":{"rendered":"\n

Sometimes you want to do something that involves a lot of data from the registry database, but you do not want to pull all that data into R — maybe you are only filtering out some subset of data with a join on a small set of keys that you have as a data frame in R, or you need to do a join of two large tables\/views in kosmos, and wants to use the indexes on those tables to speed up the operation.<\/p>\n\n\n\n

The example data<\/h3>\n\n\n\n

The example code uses the LMED<\/code> data in CRIME3<\/code>: the prescription registry for the year 2015 is about 25M rows, and the entire LMED (years 2005 to 2017) dataset is 800M rows, for a total of about 350GB of data. We will select a (very) small subset of ATC codes from the 2015 data, and then get data for those ATC codes from the entire dataset.<\/p>\n\n\n\n

The example is somewhat contrived, since the whole operation could be run in the database, without fetching the small subset to R\/Python — but this is to demonstrate how to write a temporary table .<\/p>\n\n\n\n

The connection<\/h3>\n\n\n\n

The VDI environment, as well as the compute servers, have a ODBC data source<\/em> named kosmos<\/code> that we will use.<\/p>\n\n\n\n

With R<\/h2>\n\n\n\n

Preparations<\/h3>\n\n\n\n

The following has been tested in R 4.2, both on vector<\/code> and in RStudio on a Windows machine. <\/p>\n\n\n\n

You need two packages: DBI<\/a><\/code> and odbc<\/a><\/code>. Both are in CRAN, and regularly updated.<\/p>\n\n\n\n

Code<\/h3>\n\n\n\n

First connect to the database:<\/p>\n\n\n\n

> dbhandle <- DBI::dbConnect(odbc::odbc(), dsn = \"kosmos\")<\/code><\/pre>\n\n\n\n
… then run the first query in the database:<\/p>\n\n\n\n
> query <- DBI::dbSendQuery(dbhandle, \n+   'select distinct ATC, LAKMED from MGRCRIME3.MGRCRIME3.V_LMED2015 where TKOST > 500000')\n> result <- DBI::dbFetch(query)\n> DBI::dbClearResult(query)\n> result\n\n      ATC                                     LAKMED\n1 J05AX14       Daklinza, filmdragerad tablett 60 mg\n2 R07AX02      Kalydeco, filmdragerad tablett 150 mg\n3   J05AX Harvoni, filmdragerad tablett 90 mg\/400 mg\n4 J05AX15       Sovaldi, filmdragerad tablett 400 mg<\/code><\/pre>\n\n\n\n
Upload the results to a temporary table in the registry database — the #<\/code>-mark tells the database engine to make it temporary:<\/p>\n\n\n\n
> DBI::dbWriteTable(dbhandle, \"#expensive_2015\", result)<\/code><\/pre>\n\n\n\n
… and use it in a second query:<\/p>\n\n\n\n
> query_2 <- DBI::dbSendQuery(dbhandle, \n+   'select distinct a.LAKMED from MGRCRIME3.MGRCRIME3.V_LMED a join #expensive_2015 e on e.atc = a.atc')\n> result_2 <- DBI::dbFetch(query_2)\n> DBI::dbClearResult(query_2)\n> DBI::dbDisconnect(dbhandle)\n> result_2\n                                              LAKMED\n1               Daklinza, filmdragerad tablett 30 mg\n2         Harvoni, filmdragerad tablett 90 mg\/400 mg\n3              Kalydeco, filmdragerad tablett 150 mg\n4               Exviera, filmdragerad tablett 250 mg\n5               Sovaldi, filmdragerad tablett 400 mg\n6 Viekirax, filmdragerad tablett 12,5 mg\/75 mg\/50 mg\n7               Daklinza, filmdragerad tablett 60 mg<\/code><\/pre>\n\n\n\n
An alternative without (visible) pass-thru SQL<\/h3>\n\n\n\n
There is more than one way to do this exact operation, and this specific example can also be acheived with dplyr<\/a>\/dbplyr<\/a><\/code>. This works with local data too, you just have to be careful with the order of inputs<\/em>, since dplyr<\/code> will copy the second<\/strong> input to a temp table in the database, regardless<\/strong> of which is the local one, and which is smaller. <\/p>\n\n\n\n
> library(dplyr)\n> library(dbplyr)\n\n> crime3 <- DBI::dbConnect(odbc::odbc(),\n+                          dsn = \"kosmos\", database = \"MGRCRIME3\")\n> lmed15 <- tbl(crime3, in_schema(\"MGRCRIME3\", \"V_LMED2015\"))\n> lmed <- tbl(crime3, in_schema(\"MGRCRIME3\", \"V_LMED\"))\n>\n> result <- lmed %>% \n+   # either in the database\n+   # inner_join(lmed15 %>% filter(tkost > 500000) %>% select(ATC)) %>%\n+   # or with local data\n+   inner_join(data.frame(\"ATC\" = c(\"J05AX\", \"J05AX14\", \"J05AX15\", \"R07AX02\")), \n+              copy = TRUE) %>% \n+   select(\"LAKMED\") %>% distinct()\nJoining, by = \"ATC\"\n> result\n# Source:   SQL [7 x 1]\n# Database: Microsoft SQL Server 13.00.7016[meb\\joelus@MEB-SQL02\\KOSMOS\/MGRCRIME3]\n  LAKMED                                            \n  <chr>                                             \n1 Harvoni, filmdragerad tablett 90 mg\/400 mg        \n2 Daklinza, filmdragerad tablett 60 mg              \n3 Sovaldi, filmdragerad tablett 400 mg              \n4 Exviera, filmdragerad tablett 250 mg              \n5 Viekirax, filmdragerad tablett 12,5 mg\/75 mg\/50 mg\n6 Kalydeco, filmdragerad tablett 150 mg             \n7 Daklinza, filmdragerad tablett 30 mg   \n\n> # this must come *after* we use the result, since dplyr\/dbplyr is being \"lazy\"\n> DBI::dbDisconnect(dbhandle)<\/code><\/pre>\n\n\n\n
<\/p>\n\n\n\n
With Python<\/h2>\n\n\n\n
Preparation<\/h3>\n\n\n\n
You need the pyodbc<\/code> package — and for convenience the following code also uses pandas<\/code> …<\/p>\n\n\n\n
Code<\/h3>\n\n\n\n
Connecting to the database:<\/p>\n\n\n\n
import pandas as pd\nimport pyodbc \n  \nconn = pyodbc.connect('DSN=kosmos;'\n                      'Database=MGRCRIME3;')<\/code><\/pre>\n\n\n\n
… and run the first query in the database:<\/p>\n\n\n\n
df = pd.read_sql_query('select distinct ATC, LAKMED from MGRCRIME3.V_LMED2015 where TKOST > 500000', conn)\n\nprint(df)\n\n       ATC                                      LAKMED\n0  J05AX14        Daklinza, filmdragerad tablett 60 mg\n1    J05AX  Harvoni, filmdragerad tablett 90 mg\/400 mg\n2  R07AX02       Kalydeco, filmdragerad tablett 150 mg\n3  J05AX15        Sovaldi, filmdragerad tablett 400 mg\n<\/code><\/pre>\n\n\n\n
Create a temporary table:<\/p>\n\n\n\n
cursor = conn.cursor()\n\ncursor.execute('''CREATE TABLE #expensive_2015 (atc varchar(15),lakmed varchar(100))''')\n\nconn.commit()<\/code><\/pre>\n\n\n\n
… load the dataframe into that:<\/p>\n\n\n\n
for index, row in df.iterrows():\n     cursor.execute(\"INSERT INTO #expensive_2015 (atc,lakmed) values(?,?)\", row.ATC, row.LAKMED)\nconn.commit()<\/code><\/pre>\n\n\n\n
And finally, run the join query:<\/p>\n\n\n\n
df2=pd.read_sql_query('select distinct a.LAKMED from MGRCRIME3.V_LMED a join #expensive_2015 e on e.atc = a.atc', conn)\n\nprint(df2)\n\n                                              LAKMED\n0         Harvoni, filmdragerad tablett 90 mg\/400 mg\n1               Daklinza, filmdragerad tablett 30 mg\n2               Sovaldi, filmdragerad tablett 400 mg\n3               Exviera, filmdragerad tablett 250 mg\n4              Kalydeco, filmdragerad tablett 150 mg\n5               Daklinza, filmdragerad tablett 60 mg\n6  Viekirax, filmdragerad tablett 12,5 mg\/75 mg\/5...<\/code><\/pre>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
Sometimes you want to do something that involves a lot of data from the registry database, but you do not want to pull all that data into R — maybe you are only filtering out some subset of data with a join on a small set of keys that you have as a data frame […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[11,30,21,22],"tags":[],"class_list":["post-612","post","type-post","status-publish","format-standard","hentry","category-dba","category-python","category-r","category-sql"],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":6}},"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.meb.ki.se\/sites\/meb-it\/wp-json\/wp\/v2\/posts\/612","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.meb.ki.se\/sites\/meb-it\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.meb.ki.se\/sites\/meb-it\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.meb.ki.se\/sites\/meb-it\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.meb.ki.se\/sites\/meb-it\/wp-json\/wp\/v2\/comments?post=612"}],"version-history":[{"count":11,"href":"https:\/\/www.meb.ki.se\/sites\/meb-it\/wp-json\/wp\/v2\/posts\/612\/revisions"}],"predecessor-version":[{"id":888,"href":"https:\/\/www.meb.ki.se\/sites\/meb-it\/wp-json\/wp\/v2\/posts\/612\/revisions\/888"}],"wp:attachment":[{"href":"https:\/\/www.meb.ki.se\/sites\/meb-it\/wp-json\/wp\/v2\/media?parent=612"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.meb.ki.se\/sites\/meb-it\/wp-json\/wp\/v2\/categories?post=612"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.meb.ki.se\/sites\/meb-it\/wp-json\/wp\/v2\/tags?post=612"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}