DuckDB<\/h2>\n\n\n\n
DuckDB<\/a> is a fantastic in-process<\/em> SQL database management system written completely in C++. Check its official documentation<\/a> and other blogposts like this<\/a> to get a feeling of its superpowers. It is getting better and better!<\/p>\n\n\n\n Some of the highlights:<\/p>\n\n\n\n DuckDB is super easy to install:<\/p>\n\n\n\n Additional packages required to run the code of this post are indicated in the code.<\/p>\n\n\n\n Let’s start by loading a dataset, initializing DuckDB and running a simple query.<\/p>\n\n\n\n The dataset we use here contains information on over 20,000 sold houses in Kings County. Along with the sale price, different features describe the size and location of the properties. The dataset is available on OpenML.org<\/a> with ID 42092.<\/p>\n\n\n If you like SQL, then you can do your data preprocessing and simple analyses with DuckDB. Here, we calculate the average house price per online grade (the higher the grade, the better the house).<\/p>\n\n\n The last query will be applied directly to files on disk<\/strong>. To demonstrate this fantastic feature, we first save “df” as a parquet file and “avg” as a csv file. <\/p>\n\n\n Let’s load some columns of “housing.parquet” data, but only rows with grades having an average price of one million USD. Agreed, that query does not make too much sense but I hope you get the idea…\ud83d\ude03<\/p>\n\n\n The Python notebook and R code can be found at:<\/p>\n\n\n\n “R <-> Python” continued… DuckDB: Quacking SQL<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17],"tags":[10,6,5],"class_list":["post-745","post","type-post","status-publish","format-standard","hentry","category-programming","tag-lost-in-translation","tag-python","tag-r"],"featured_image_src":null,"author_info":{"display_name":"Michael Mayer","author_link":"https:\/\/lorentzen.ch\/index.php\/author\/michael\/"},"_links":{"self":[{"href":"https:\/\/lorentzen.ch\/index.php\/wp-json\/wp\/v2\/posts\/745","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/lorentzen.ch\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/lorentzen.ch\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/lorentzen.ch\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/lorentzen.ch\/index.php\/wp-json\/wp\/v2\/comments?post=745"}],"version-history":[{"count":20,"href":"https:\/\/lorentzen.ch\/index.php\/wp-json\/wp\/v2\/posts\/745\/revisions"}],"predecessor-version":[{"id":769,"href":"https:\/\/lorentzen.ch\/index.php\/wp-json\/wp\/v2\/posts\/745\/revisions\/769"}],"wp:attachment":[{"href":"https:\/\/lorentzen.ch\/index.php\/wp-json\/wp\/v2\/media?parent=745"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/lorentzen.ch\/index.php\/wp-json\/wp\/v2\/categories?post=745"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/lorentzen.ch\/index.php\/wp-json\/wp\/v2\/tags?post=745"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Installation<\/h2>\n\n\n\n
install.packages(\"duckdb\")<\/code><\/li>pip install duckdb<\/code><\/li><\/ul>\n\n\n\nA first query<\/h2>\n\n\n\n
library(OpenML)\nlibrary(duckdb)\nlibrary(tidyverse)\n\n# Load data\ndf <- getOMLDataSet(data.id = 42092)$data\n\n# Initialize duckdb, register df and materialize first query\ncon = dbConnect(duckdb())\nduckdb_register(con, name = \"df\", df = df)\ncon %>% \n dbSendQuery(\"SELECT * FROM df limit 5\") %>% \n dbFetch()<\/pre><\/div>\n\n<\/div>
import duckdb\nimport pandas as pd\nfrom sklearn.datasets import fetch_openml\n\n# Load data\ndf = fetch_openml(data_id=42092, as_frame=True)[\"frame\"]\n\n# Initialize duckdb, register df and fire first query\n# If out-of-RAM: duckdb.connect(\"py.duckdb\", config={\"temp_directory\": \"a_directory\"})\ncon = duckdb.connect()\ncon.register(\"df\", df)\ncon.execute(\"SELECT * FROM df limit 5\").fetchdf()<\/pre><\/div>\n\n<\/div><\/div>\n\t\t<\/div>\n\n\n![\"\"](\"https:\/\/lorentzen.ch\/wp-content\/uploads\/2022\/04\/image.png\")
Average price per grade<\/h2>\n\n\n\n
query <- \n \"\n SELECT AVG(price) avg_price, grade \n FROM df \n GROUP BY grade\n ORDER BY grade\n \"\navg <- con %>% \n dbSendQuery(query) %>% \n dbFetch()\n\navg\n<\/pre><\/div>\n\n<\/div>
# Average price per grade\nquery = \"\"\"\n SELECT AVG(price) avg_price, grade \n FROM df \n GROUP BY grade\n ORDER BY grade\n \"\"\"\navg = con.execute(query).fetchdf()\navg<\/pre><\/div>\n\n<\/div><\/div>\n\t\t<\/div>\n\n\n
![\"\"](\"https:\/\/lorentzen.ch\/wp-content\/uploads\/2022\/04\/image-1.png\")
Highlight: queries to files<\/h2>\n\n\n\n
write_parquet(df, \"housing.parquet\")\nwrite.csv(avg, \"housing_avg.csv\", row.names = FALSE)\n<\/pre><\/div>\n\n<\/div>
# Save df and avg to different file types\ndf.to_parquet(\"housing.parquet\") # pyarrow=7\navg.to_csv(\"housing_avg.csv\", index=False)<\/pre><\/div>\n\n<\/div><\/div>\n\t\t<\/div>\n\n\n
# \"Complex\" query\nquery2 <- \"\n SELECT price, sqft_living, A.grade, avg_price\n FROM 'housing.parquet' A\n LEFT JOIN 'housing_avg.csv' B\n ON A.grade = B.grade\n WHERE B.avg_price > 1000000\n \"\n\nexpensive_grades <- con %>% \n dbSendQuery(query2) %>% \n dbFetch()\n\nhead(expensive_grades)\n\n# dbDisconnect(con)<\/pre><\/div>\n\n<\/div>
# Complex query\nquery2 = \"\"\"\n SELECT price, sqft_living, A.grade, avg_price\n FROM 'housing.parquet' A\n LEFT JOIN 'housing_avg.csv' B\n ON A.grade = B.grade\n WHERE B.avg_price > 1000000\n \"\"\"\nexpensive_grades = con.execute(query2).fetchdf()\nexpensive_grades\n\n# con.close()<\/pre><\/div>\n\n<\/div><\/div>\n\t\t<\/div>\n\n\n
![\"\"](\"https:\/\/lorentzen.ch\/wp-content\/uploads\/2022\/04\/image-2.png\")
Last words<\/h2>\n\n\n\n