{"metadata":{"kernelspec":{"name":"ir","display_name":"R","language":"R"},"language_info":{"name":"R","codemirror_mode":"r","pygments_lexer":"r","mimetype":"text/x-r-source","file_extension":".r","version":"4.0.5"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"# Fast and less memory data processing with Apache Arrow and DuckDB\n\nIn previous [notebook](https://www.kaggle.com/code/igjit1/memory-reduction-feature-engineering-with-dplyr), I showed how to process huge train data with dplyr.\nLater, I realized that using DuckDB would be a simpler solution.\n\n[DuckDB](https://duckdb.org/) is an in-process (serverless) database for analytics.\nChanging the dplyr backend from tibble to DuckDB made my dplyr pipeline 4 times faster!\n\nSee this great tutorial about Apache Arrow and DuckDB within R: [Larger-Than-Memory Data Workflows with Apache Arrow](https://arrow-user2022.netlify.app/)\n\nDuckDB is also deeply integrated into Python for efficient data analysis, so give it a try.\nI think DuckDB will be a powerful tool for many Kaggle users working with huge data.","metadata":{}},{"cell_type":"markdown","source":"## Installation\n\nInstall latest binary package.\nSee https://docs.rstudio.com/rspm/admin/serving-binaries/ for detail.\n","metadata":{}},{"cell_type":"code","source":"repos <- \"https://packagemanager.rstudio.com/cran/__linux__/focal/latest\"\nua <- sprintf(\"R/%s R (%s)\", getRversion(), paste(getRversion(), R.version[\"platform\"], R.version[\"arch\"], R.version[\"os\"]))\n\ninstall.packages(\"duckdb\", dependencies = FALSE, repos = repos, headers = c(\"User-Agent\" = ua))","metadata":{"execution":{"iopub.status.busy":"2022-07-13T20:41:33.23268Z","iopub.execute_input":"2022-07-13T20:41:33.234503Z","iopub.status.idle":"2022-07-13T20:42:06.568837Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"packageVersion(\"duckdb\")","metadata":{"execution":{"iopub.status.busy":"2022-07-13T20:42:06.571029Z","iopub.execute_input":"2022-07-13T20:42:06.599846Z","iopub.status.idle":"2022-07-13T20:42:06.618989Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Process and Feature Engineer Train Data","metadata":{}},{"cell_type":"code","source":"library(tidyverse)\nlibrary(arrow)","metadata":{"_uuid":"051d70d956493feee0c6d64651c6a088724dca2a","_execution_state":"idle","execution":{"iopub.status.busy":"2022-07-13T20:42:06.622328Z","iopub.execute_input":"2022-07-13T20:42:06.623768Z","iopub.status.idle":"2022-07-13T20:42:07.455129Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Read parquet file as an Arrow Table with `as_data_frame = FALSE` option.","metadata":{}},{"cell_type":"code","source":"train_tbl <- arrow::read_parquet(\"../input/amex-data-integer-dtypes-parquet-format/train.parquet\", as_data_frame = FALSE)","metadata":{"execution":{"iopub.status.busy":"2022-07-13T20:42:07.458545Z","iopub.execute_input":"2022-07-13T20:42:07.460566Z","iopub.status.idle":"2022-07-13T20:42:17.24306Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_tbl %>% class","metadata":{"execution":{"iopub.status.busy":"2022-07-13T20:42:17.24689Z","iopub.execute_input":"2022-07-13T20:42:17.248362Z","iopub.status.idle":"2022-07-13T20:42:17.264729Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"My R version of feature engineering based on @cdeotte [notebook](https://www.kaggle.com/code/cdeotte/xgboost-starter-0-793).","metadata":{}},{"cell_type":"code","source":"process_and_feature_engineer <- function(df) {\n  cat_features <- c(\"B_30\", \"B_38\", \"D_114\", \"D_116\", \"D_117\", \"D_120\", \"D_126\", \"D_63\", \"D_64\", \"D_66\", \"D_68\")\n  num_features <- setdiff(colnames(df), c(cat_features, \"customer_ID\", \"S_2\"))\n\n  df %>%\n    group_by(customer_ID) %>%\n    summarise(n = n(),\n              across({{ cat_features }}, list(last = last, nd = n_distinct)),\n              across({{ num_features }}, list(mean = mean, sd = sd, min = min, max = max, last = last)))\n}","metadata":{"execution":{"iopub.status.busy":"2022-07-13T20:42:17.267087Z","iopub.execute_input":"2022-07-13T20:42:17.268436Z","iopub.status.idle":"2022-07-13T20:42:17.296702Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Process with DuckDB.","metadata":{}},{"cell_type":"code","source":"train_X <- train_tbl %>%\n  to_duckdb() %>%\n  process_and_feature_engineer %>%\n  collect","metadata":{"execution":{"iopub.status.busy":"2022-07-13T20:42:17.299019Z","iopub.execute_input":"2022-07-13T20:42:17.300369Z","iopub.status.idle":"2022-07-13T20:44:22.915145Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_X %>% head","metadata":{"execution":{"iopub.status.busy":"2022-07-13T20:44:22.919178Z","iopub.execute_input":"2022-07-13T20:44:22.920848Z","iopub.status.idle":"2022-07-13T20:44:23.413085Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Save the result.","metadata":{}},{"cell_type":"code","source":"arrow::write_parquet(train_X, \"train_X.parquet\")","metadata":{"execution":{"iopub.status.busy":"2022-07-13T20:44:23.416449Z","iopub.execute_input":"2022-07-13T20:44:23.417979Z","iopub.status.idle":"2022-07-13T20:44:45.285341Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"rm(train_X)\ngc() %>% invisible","metadata":{"execution":{"iopub.status.busy":"2022-07-13T20:44:45.287495Z","iopub.execute_input":"2022-07-13T20:44:45.288779Z","iopub.status.idle":"2022-07-13T20:44:45.582031Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Benchmark","metadata":{}},{"cell_type":"code","source":"install.packages(\"bench\")","metadata":{"execution":{"iopub.status.busy":"2022-07-13T20:44:45.584102Z","iopub.execute_input":"2022-07-13T20:44:45.585311Z","iopub.status.idle":"2022-07-13T20:45:10.662256Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Use a part of train data for benchmarking to avoid out of memory.","metadata":{}},{"cell_type":"code","source":"train_part <- train_tbl %>%\n  head(200000)","metadata":{"execution":{"iopub.status.busy":"2022-07-13T20:45:10.664793Z","iopub.execute_input":"2022-07-13T20:45:10.666132Z","iopub.status.idle":"2022-07-13T20:45:10.681199Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Prepare tibble.","metadata":{}},{"cell_type":"code","source":"train_part_tibble <- train_part %>%\n  collect","metadata":{"execution":{"iopub.status.busy":"2022-07-13T20:45:10.684598Z","iopub.execute_input":"2022-07-13T20:45:10.686187Z","iopub.status.idle":"2022-07-13T20:45:10.718946Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_part_tibble %>% class","metadata":{"execution":{"iopub.status.busy":"2022-07-13T20:45:10.722731Z","iopub.execute_input":"2022-07-13T20:45:10.724484Z","iopub.status.idle":"2022-07-13T20:45:10.763118Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Prepare DuckDB.","metadata":{}},{"cell_type":"code","source":"train_part_duckdb <- train_part %>%\n  to_duckdb()","metadata":{"execution":{"iopub.status.busy":"2022-07-13T20:45:10.765521Z","iopub.execute_input":"2022-07-13T20:45:10.766876Z","iopub.status.idle":"2022-07-13T20:45:11.322834Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_part_duckdb %>% class","metadata":{"execution":{"iopub.status.busy":"2022-07-13T20:45:11.32534Z","iopub.execute_input":"2022-07-13T20:45:11.326714Z","iopub.status.idle":"2022-07-13T20:45:11.341346Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Benchmarking.","metadata":{}},{"cell_type":"code","source":"result <- bench::mark(\n  tibble = train_part_tibble %>% process_and_feature_engineer,\n  duckdb = train_part_duckdb %>% process_and_feature_engineer %>% collect,\n  iterations = 5,\n  check = FALSE\n)","metadata":{"execution":{"iopub.status.busy":"2022-07-13T20:45:11.34359Z","iopub.execute_input":"2022-07-13T20:45:11.344838Z","iopub.status.idle":"2022-07-13T21:00:16.571047Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"result %>%\n  select(\"expression\", \"min\", \"median\", \"itr/sec\", \"mem_alloc\", \"gc/sec\", \"n_itr\", \"n_gc\", \"total_time\")","metadata":{"execution":{"iopub.status.busy":"2022-07-13T21:00:16.573504Z","iopub.execute_input":"2022-07-13T21:00:16.574845Z","iopub.status.idle":"2022-07-13T21:00:16.6469Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"DuckDB is more than 4 times faster than tibble and consumes less memory. It's amazing.","metadata":{}},{"cell_type":"code","source":"result %>% plot","metadata":{"execution":{"iopub.status.busy":"2022-07-13T21:00:16.650533Z","iopub.execute_input":"2022-07-13T21:00:16.652154Z","iopub.status.idle":"2022-07-13T21:00:17.456755Z"},"trusted":true},"execution_count":null,"outputs":[]}]}