Read & Write from Impala

Github project : https://github.com/saagie/example-R-querying-impala

Connect with ODBC

Dependencies

R package

DBI: Standard database interface
odbc: Connect to ODBC databases using DBI
dplyr: Data manipulation library
dbplyr: Converts data manipulation written in R to SQL
implyr: Same as above but specific for Impala
getPass: Library to hide password typed in Rstudio notebook

If you are using this on the Saagie platform, all dependencies and drivers should already be installed. If not, or if you want to connect from outside of the plateform, read the installation guide at the end of the odbc article.

 

Authentification without Kerberos 

Parameters

• Driver: Default Cloudera ODBC Driver for Impala 64-bit on the platform. The name of the driver to use (Default name on Windows is usually "Cloudera ODBC Driver for Impala", Default name on Linux is usually "Cloudera ODBC Driver for Impala 64-bit")
• Host: Ip or hostname of the Impala database. You can find it under Impala > External Connections > Host. It has the format dn1.pX.company.prod.saagie.io.
• Port: Default is 21050
• Schema: Schema in which to execute the queries

Additional parameters for authentification 

• AuthMech: The authentification mechanism, use 3 for authentification with user / password
• UseSASL: Simple Authentification and Security Layer, use 1 for authentification with user / password
• UID: Your Saagie username on the platform
• PWD: Your Saagie password on the platform

Code example

library(DBI)
 
con <- dbConnect(odbc::odbc(),
                      Driver   = "Cloudera ODBC Driver for Impala 64-bit",
                      Host     = "dn1.pX.company.prod.saagie.io",
                      Port     = 21050,
                      Schema   = "default",
                      AuthMech = 3,
                      UseSASL  = 1,
                      UID      = "user",
                      PWD      = "password")

Impala over SSL

If your Impala is secured with SSL, you have to add the following parameters to your command:

• SSL=1    → Mandatory. The client will communicate over SSL to the server.
• AllowSelfSignedServerCert=1   → Optional. To allow authentication using self-signed certificates that have not been added to the list of
  trusted certificates. NB : The certificate chain file is not yet available from Saagie's containers.
• AllowHostNameCNMismatch=1   → Optional. To allow the common name of a CA-issued SSL certificate to not match the host name of the Impala server.

 

Authentification with Kerberos 

Parameters

• Driver: Default Cloudera ODBC Driver for Impala 64-bit on the platform. The name of the driver to use (Default name on Windows is usually "Cloudera ODBC Driver for Impala", Default name on Linux is usually "Cloudera ODBC Driver for Impala 64-bit")
• Host: Ip or hostname of the Impala database. Default is dn1.pX.company.prod.saagie.io
• Port: Default is 21050
• Schema: Schema in which to execute the queries

Additional parameters for authentification 

• AuthMech: The authentification mechanism, use 1 for authentification with Kerberos ticket
• User: Your Saagie username on the platform
• Password: Your Saagie password on the platform

Code example

library(DBI)
library(getPass)

# Method 1 (interactive) : Use in Rstudio. Interactive pop up to enter password
system('kinit user',input=getPass('Enter your password: '))

# Method 2 (scripts) : Use outside of Rstudio. 
# Password is written in command line or stored in a environment variable
# Uncomment next line to use
# system('echo password | kinit user')
 
con <- dbConnect(odbc::odbc(),
                 Driver = "Cloudera ODBC Driver for Impala 64-bit",
                 Host = "dn1.pX.company.prod.saagie.io",
                 Port = 21050,
                 Schema = "default",
                 AuthMech = 1)

 

Query examples

Show Tables

# On the version 2.5.39 of the Linux Impala ODBC driver, the method dbListTables returns only the first letter of each schema and table
# One workaround is to use plain SQL "show schemas" and "show tables"
 
# List all tables from all schemas
dbListTables(con)
dbGetQuery(con, 'show schemas')
dbGetQuery(con, 'show tables')

Get all elements of a table

sample <- DBI::dbReadTable(con, "sample") # Fetch all data from a table

Execute queries in dplyr syntax

# Create a lazy tbl from an Impala table
forecast_db <- tbl(con, in_schema('forecast', 'forecast_weekly'))
forecast_db # Print columns and column types
 
# The query is written in dplyr syntax, but executed on a remote sql database
query <- forecast_db %>%
    summarise(mean_forecast = forecast %>% as.numeric() %>% mean)
 
show_query(query) # Show the query that will be executed
query # Executes the lazy query when the result is printed
 
# Example of usable dplyr verbs
forecast_db %>%
    filter(prediction_date == max(prediction_date)) %>%
    group_by(reference) %>%
    summarise(forecast_mean = mean(forecast),
              forecast_max = max(forecast),
              nb_forecast_total = n())
 
sales_db <- tbl(con, in_schema('forecast', 'sales_weekly'))
sales_db
 
forecast_db <- forecast_db %>%
    mutate(reference = as.character(reference))
 
diff_db <- inner_join(forecast_db, sales_db, by=c('reference', 'year', 'week'))
 
diff_by_ref <- diff_db %>%
    group_by(reference, year, week) %>%
    summarise(diff_by_week = abs(forecast - quantity)) %>% # Difference between forecast and reality for each prediction
    group_by(reference) %>%
    summarise(diff_by_ref = sum(diff_by_week)) # Sum of all differences for each reference
 
diff_db # Executes all the lazy queries above

 

Installation details (only use if necessary)

Impala ODBC driver

Download the Impala ODBC driver from Cloudera https://www.cloudera.com/downloads/connectors/impala/odbc/2-5-39.html

On Windows, install the .msi executable

On Linux, install odbcinst tool

sudo apt-get install odbcinst

Install the .deb package (or other extension depending on your distribution)

sudo dpkg -i clouderaimpalaodbc_*.deb

Execute the following command line to register the driver as installed

 sudo odbcinst -i -d -f /opt/cloudera/impalaodbc/Setup/odbcinst.ini

Package installation

if (!length(find.package("odbc", quiet = TRUE))) {
 
 # Install OS package for ODBC
 system("sudo apt-get install -y unixodbc unixodbc-dev", intern = TRUE)
 # Install R package for ODBC
 install.packages("odbc", repos = "https://cloud.r-project.org")
 
}

# If Impala ODBC driver is missing:
if (!file.exists("/etc/odbcinst.ini") ||
 !length(grep("Cloudera ODBC Driver for Impala 64-bit", readLines("/etc/odbcinst.ini"), ignore.case = TRUE))) {
 
 # Retrieve Impala drivers for Debianoids from Cloudera
 if (!dir.exists("driver-impala")) dir.create("driver-impala")
 if (!length(Sys.glob("driver-impala/clouderahiveodbc_*.deb")))
 download.file(paste0("https://downloads.cloudera.com/connectors/impala_odbc_2.5.40.1025/",
 "Debian/clouderaimpalaodbc_2.5.40.1025-2_amd64.deb"),
 destfile = "driver-impala/clouderaimpalaodbc_2.5.40.1025-2_amd64.deb")
 
 # Install driver
 system("sudo dpkg -i driver-impala/clouderaimpalaodbc_*.deb", intern = TRUE)
 
 # Setup driver and /etc/odbcinst.ini file
 system("sudo apt-get install -y odbcinst", intern = TRUE)
 system("sudo odbcinst -i -d -f /opt/cloudera/impalaodbc/Setup/odbcinst.ini", intern = TRUE)
 
}

 

With JDBC

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.

Impala JDBC drivers

Download the Impala JDBC drivers

Download Impala JDBC driver

download.file('https://github.com/Mu-Sigma/RImpala/blob/master/impala-jdbc-cdh5.zip?raw=true', 'jdbc.zip',
              method ='curl', extra ='-L')
unzip('jdbc.zip')

Parameters

impalaConnectionUrl : Url used to communicate with Impala through a datanode without authentication.

If no athentication, the format is as follows :

jdbc:>hive2://datanode1dns:port/;auth=noSasl

The impala port is usually 21050.

Code explanation for querying impala

Loading JDBC driver and connection

drv <- JDBC(driverClass = "org.apache.hive.jdbc.HiveDriver",
 classPath = list.files("impala-jdbc-cdh5", pattern = "jar$", full.names = TRUE),
 identifier.quote = "`")

impalaConnectionUrl <- "jdbc:hive2://datanode1dns:port/"
conn <- dbConnect(drv, impalaConnectionUrl,user="xxxx",password="xxxx")

Query and Insert : Examples

Show Tables

# All databases
dbListTables(conn)
# The database "default"
dbGetQuery(conn,"show tables")

Get all elements of a table

# In database "default"
d <- dbReadTable(conn,"table_name")
# OR
d <- dbGetQuery(conn,"select * from table_name")
 
# Other that the database "default")
d <- dbReadTable(conn, nameBDD.table_name)

Create a table in parquet format

dbSendUpdate(conn, "CREATE TABLE table_name (attribute1 string, attribute2 int) STORED AS PARQUET")

Insert data into a table

dbGetQuery(conn, "INSERT INTO table_name VALUES ('test', 1)")

Refresh Tables

dbGetQuery(conn, "INVALIDATE METADATA")