/
R - Query from Drill

R - Query from Drill

Oct 06, 2016

 

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=""))