{"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":"code","source":"library(tidyverse) # metapackage of all tidyverse packages\nlibrary(arrow)\nlibrary(duckdb)\n\nsessioninfo::session_info()","metadata":{"_uuid":"051d70d956493feee0c6d64651c6a088724dca2a","_execution_state":"idle","execution":{"iopub.status.busy":"2022-11-18T17:04:41.187198Z","iopub.execute_input":"2022-11-18T17:04:41.234551Z","iopub.status.idle":"2022-11-18T17:04:43.350515Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We define a function that unnests the events from the json files using DuckDB:","metadata":{}},{"cell_type":"code","source":"unnest_events <- function(x) {\n  # register the arrow table as a duckdb table\n  duckdb::duckdb_register_arrow(con, \"x\", x)\n    \n  # Use duckdb to unnest the json data.\n  dbExecute(con, \"drop view if exists y\")\n  res <- dbExecute(\n    con,\n    \"\n    create view y as\n    select\n      session,\n      events.aid as aid,\n      events.ts as ts,\n      events.type as type\n    from (\n      select session, unnest(events) as events from x\n    )\n    \"\n  )\n\n  tbl(con, \"y\") %>%\n    to_arrow()\n}","metadata":{"execution":{"iopub.status.busy":"2022-11-18T17:07:31.364038Z","iopub.execute_input":"2022-11-18T17:07:31.366171Z","iopub.status.idle":"2022-11-18T17:07:31.383476Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Now just read, unnest and save as parquet files:","metadata":{}},{"cell_type":"code","source":"con <- dbConnect(duckdb::duckdb())\nraw_data <- \"../input/otto-recommender-system/\"\nproc_data <- \"/kaggle/working/otto/\"\n\nfor (set in c(\"test\", \"train\")) {\n  cat(\"------ Processing set:\", set, \"--------\\n\")\n\n  path <- fs::path(\n    raw_data,\n    paste0(set, \".jsonl\")\n  )\n\n  x <- arrow::read_json_arrow(path, as_data_frame = FALSE)\n  y <- unnest_events(x)\n\n  y %>%\n    write_dataset(file.path(proc_data, set))\n\n  rm(x, y)\n}\n\ndbDisconnect(con, shutdown=TRUE)","metadata":{"execution":{"iopub.status.busy":"2022-11-18T17:07:57.613323Z","iopub.execute_input":"2022-11-18T17:07:57.615218Z","iopub.status.idle":"2022-11-18T17:08:09.838879Z"},"trusted":true},"execution_count":null,"outputs":[]}]}