apache drill

[Triamus]

apache drill & parquet

• drill service in embedded mode @ http://localhost:8047/
• https://blog.ouseful.info/2017/06/03/querying-large-csv-files-with-apache-drill/
• https://rud.is/b/2017/05/31/drilling-into-csvs-teaser-trailer/
• https://mapr.com/blog/how-guide-getting-started-apache-drill/
• https://drill.apache.org/docs/create-table-as-ctas/
• https://drill.apache.org/docs/file-system-storage-plugin/
• sqlline.bat -u "jdbc:drill:zk=local"
• linux: bin/sqlline -u jdbc:drill:zk=local
• make dfs default: use dfs;
• SELECT * FROM dfs.D:/data/other/claims.csv limit 5;
• CREATE TABLE dfs.pq.D:/data/other/claims.parquet AS SELECT * FROM dfs.csv.D:/data/other/claims.csv;
• CREATE TABLE dfs.tmp.D:/data/other/claims.parquet AS SELECT * FROM dfs.csv.D:/data/other/claims.csv;
• SELECT * FROM dfs.tmp.D:/data/other/claims.parquet limit 5;
• SELECT hostname FROM sys.drillbits WHERE current = true;
• SELECT version FROM sys.version;
• select * from dfs.csv.D:/data/other/claims.csv limit 5;
• https://rud.is/b/2017/12/20/r%E2%81%B6-series-random-sampling-from-apache-drill-tables-with-r-sergeant/
• https://rud.is/b/2017/01/22/create-parquet-files-from-r-data-frames-with-sergeant-apache-drill-a-k-a-make-parquet-files-great-again-in-r/
• https://rud.is/b/2017/08/01/r%E2%81%B6-reticulating-parquet-files/
• https://rud.is/b/2017/07/17/ten-hut-the-apache-drill-r-interface-package-sergeant-is-now-on-cran/
• https://rud.is/b/2017/06/17/replicating-the-apache-drill-yelp-academic-dataset-with-sergeant/
• https://community.mapr.com/community/exchange/blog/2017/02/21/how-to-use-sql-hadoop-drill-rest-json-nosql-and-hbase-in-a-simple-rest-client
• Apache Drill: The SQL Query Engine for Hadoop and NoSQL https://www.amazon.com/Apache-Drill-Query-Engine-Hadoop/dp/1449362044/ref=sr_1_1?ie=UTF8&qid=1515617481&sr=8-1&keywords=apache+drill
• https://mapr.com/products/apache-drill/assets/mapr-datasheet-apache-drill.pdf
• Self-service BI for big data applications using Apache Drill http://www.ba4all.io/sites/default/files/imce/Self-Service%20BI%20for%20Big%20Data%20Applications%20Using%20Apache%20Drill%20%28Martijn%20kieboom%2C%20Mats%20Uddenfeldt%29.pdf

select 
  csv.row_id, 
  csv.name,
  parquet.vehicle,
  parquet.model_year
from 
  dfs.tmp.`D:/data/other/claims.csv` as csv
left join
  dfs.tmp.`D:/data/other/claims.parquet` as parquet
on
  csv.row_id = parquet.row_id
where
  csv.blind_make = 'AR'
limit 5;

select csv.row_id, csv.name, parquet.vehicle, parquet.model_year from dfs.tmp.`D:/data/other/claims.csv` csv left join dfs.tmp.`D:/data/other/claims.parquet` parquet on csv.row_id = parquet.row_id where csv.blind_make = "AR" limit 5;

---

title: "R Notebook" output: html_notebook editor_options: chunk_output_type: console

library(sergeant)
# use localhost if running standalone on same system otherwise the host or IP of your Drill server
ds <- src_drill("localhost")
ds
dc <- drill_connection()
dc

dc %>% drill_status()
dc %>% drill_version()
dc %>% drill_metrics()
dc %>% drill_options()
dc %>% drill_stats()
dc %>% drill_storage()
dc %>% drill_threads()

dc %>% drill_show_files("dfs.tmp")
dc %>% drill_show_schemas()

library(tidyverse)
#ds %>% drill_version()

see available methods

sql_translate_env(src_drill()$con)

dc %>%
  drill_query("SELECT * FROM dfs.`C:/data/flight_delay/Flight_Delays_Sample.csv` limit 5")

dc %>%
  drill_query("SELECT * FROM dfs.`C:/data/flight_delay/Flight_Delays_Sample.parquet` limit 5")

db <- tbl(ds, "cp.`employee.json`")

install.packages("sergeant")
library(sergeant)

curl -o ~/data/misc/Bike-Sharing-Dataset.zip https://archive.ics.uci.edu/ml/machine-learning-databases/00275/Bike-Sharing-Dataset.zip

unzip ~/data/misc/Bike-Sharing-Dataset.zip