{"id":1874,"date":"2025-03-30T13:43:16","date_gmt":"2025-03-30T11:43:16","guid":{"rendered":"https:\/\/lorentzen.ch\/?p=1874"},"modified":"2025-03-30T13:43:17","modified_gmt":"2025-03-30T11:43:17","slug":"converting-arbitrarily-large-csvs-to-parquet-with-r","status":"publish","type":"post","link":"https:\/\/lorentzen.ch\/index.php\/2025\/03\/30\/converting-arbitrarily-large-csvs-to-parquet-with-r\/","title":{"rendered":"Converting arbitrarily large CSVs to Parquet with R"},"content":{"rendered":"\n

In this recent post<\/a>, we have used Polars<\/strong><\/a> and DuckDB<\/strong><\/a> to convert a large CSV file to Parquet in steaming mode – and Python. <\/p>\n\n\n\n

Different people have contacted me and asked: “and in R?”<\/p>\n\n\n\n

Simple answer: We have DuckDB, and we have different Polars bindings. Here, we are using {polars}<\/a> which is currently being overhauled into {neopandas}.<\/p>\n\n\n\n

So let’s not wait any longer!<\/p>\n\n\n\n

\"\"
\"\"<\/p>\n\n\n\n

Run times are on a Windows system with an Intel i7-13700H CPU. <\/p>\n\n\n\n

Generate 2.2 GB csv file<\/h2>\n\n\n\n

We use {data.table} to dump a randomly generated dataset with 100 Mio rows into a csv file.<\/p>\n\n\n

\n\t\t\t
\n\t\t\t\t
\n\t\t\t\t
R<\/div>\n\t\t\t<\/div><\/div>\n\t\t\t<\/div>\n\t\t\t
\n\n
library(data.table)\n\nset.seed(1)\n\nn <- 1e8\n\ndf <- data.frame(\n  X = sample(letters[1:3], n, TRUE),\n  Y = runif(n),\n  Z = sample(1:5, n, TRUE)\n)\n\nfwrite(df, \"data.csv\")<\/pre><\/div>\n\n<\/div><\/div>\n\t\t<\/div>\n\n\n
DuckDB<\/h2>\n\n\n\n
Then, we use DuckDB<\/a> to fire a query to the file and stream the result into Parquet. <\/p>\n\n\n\n
Threads and RAM can be set on the fly, which is very convenient. Setting a low memory limit (e.g., 500 MB) will work – try it out!<\/p>\n\n\n
\n\t\t\t
\n\t\t\t\t
\n\t\t\t\t
R<\/div>\n\t\t\t<\/div><\/div>\n\t\t\t<\/div>\n\t\t\t
\n\n
library(duckdb)\n\ncon <- dbConnect(duckdb(config = list(threads = \"8\", memory_limit = \"4GB\")))\n\nsystem.time( # 3.5s\n  dbSendQuery(\n    con,\n    \"\n    COPY (\n      SELECT Y, Z\n      FROM 'data.csv'\n      WHERE X == 'a'\n      ORDER BY Y\n    ) TO 'data.parquet' (FORMAT parquet, COMPRESSION zstd)\n    \"\n  )\n)\n\n# Check\ndbGetQuery(con, \"SELECT COUNT(*) N FROM 'data.parquet'\") # 33329488\ndbGetQuery(con, \"SELECT * FROM 'data.parquet' LIMIT 5\")\n#              Y Z\n# 1 5.355105e-09 4\n# 2 9.080395e-09 5\n# 3 2.258457e-08 2\n# 4 3.445894e-08 2\n# 5 6.891787e-08 1<\/pre><\/div>\n\n<\/div><\/div>\n\t\t<\/div>\n\n\n
3.5 seconds – wow! The resulting file looks good. It is 125 MB large.<\/p>\n\n\n\n
Polars<\/h2>\n\n\n\n
Let’s do the same with Polars.<\/p>\n\n\n\n
# Sys.setenv(NOT_CRAN = "true")\n# install.packages("polars", repos = "https:\/\/community.r-multiverse.org")\nlibrary(polars)\n\npolars_info()\n\nsystem.time( # 9s\n  (\n    pl$scan_csv("data.csv")\n    $filter(pl$col("X") == "a")\n    $drop("X")\n    $sort("Y")\n    $sink_parquet("data.parquet", row_group_size = 1e5)\n  )\n)\n\n# Check\npl$scan_parquet("data.parquet")$head()$collect()\n# shape: (5, 2)\n# \u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2510\n# \u2502 Y         \u2506 Z   \u2502\n# \u2502 ---       \u2506 --- \u2502\n# \u2502 f64       \u2506 i64 \u2502\n# \u255e\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u256a\u2550\u2550\u2550\u2550\u2550\u2561\n# \u2502 5.3551e-9 \u2506 4   \u2502\n# \u2502 9.0804e-9 \u2506 5   \u2502\n# \u2502 2.2585e-8 \u2506 2   \u2502\n# \u2502 3.4459e-8 \u2506 2   \u2502\n# \u2502 6.8918e-8 \u2506 1   \u2502\n# \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2518<\/pre><\/div>\n\n\n\n
With nine seconds, it is slower than DuckDB. But the output looks as expected and has the same size as with DuckDB.<\/p>\n\n\n\n
Final words<\/h2>\n\n\n\n