R - Query from Drill

Owned by Charline Simon (Unlicensed)
Last updated: Oct 06, 2016 by Aurèle Morvan (Unlicensed)
1 min read


Github project : example-R-Query-From-Drill

Dependencies

R package

RJDBC : https://cran.r-project.org/web/packages/RJDBC/index.html
This allows R to connect to any DBMS that has a JDBC driver.

Drill JDBC drivers

Download the Drill JDBC drivers : https://drill.apache.org/docs/using-the-jdbc-driver/


Parameters

  • pathDriverDrill : The path where is the driver drill
  • IP_Drill : Internet protocol Drill
  • Port_Drill : Port of Drill
  • Table_Name : Name of Table
  • BDD_Name : Name of BDD


Code explanation for querying Drill

Loading JDBC driver

drv <- JDBC(driverClass = "org.apache.drill.jdbc.Driver", classPath = pathDriverDrill)

Connection 

drillConnectionURL <- "jdbc:drill:drillbit=IP_Drill:Port_Drill"
conn <- dbConnect(drv, "drillConnectionURL")

Query

Show Tables

dbListTables(conn)

Read Table

BDD_Name <- "cp"
Table_Name <- "`employee.json`"
dbReadTable(conn, paste(BDD_Name, ".", Table_Name, sep="")

Count the number of rows (in table)

dbGetQuery(conn, paste("SELECT COUNT(*) FROM ", BDD_Name, ".", Table_Name, sep=""))