How to connect secure Impala cluster from RStudio on macOS with implyr
Impala is very fast SQL-on-Hadoop, and it will enhance your R experience with implyr, a dplyr based interface for Apache Impala (incubating) created by Ian Cook. I will show you how to setup connection to Kerberized Impala cluster with implyr from local macOS. You can find my GitHub repo as follows:
Setting up ODBC environment for macOS
Install unixODBC with homebrew
First, we will install unixODBC to handle Impala with ODBC. In R world, ODBC is preferred to connect Impala because of its performance and compatibility. Let’s install unixODBC with homebrew.
$ brew install unixodbc
Download and install the latest version of the Impala ODBC driver from Cloudera
You can download the latest Impala ODBC Driver.
Configure your .odbc.ini and .odbcinst.ini
After installing Impala ODBC driver for macOS, basic configuration templates can be found in /opt/cloudera/impalaodbc/Setup/
.
cp /opt/cloudera/impalaodbc/Setup/odbc.ini ~/.odbc.ini
cp /opt/cloudera/impalaodbc/Setup/odbcinst.ini ~/.odbcinst.ini
Before using following setting, you must replace HOST
and KrbRealm
with appropriate ones. Let’s modify your .odbc.ini
as follows:
[ODBC]
# Specify any global ODBC configuration here such as ODBC tracing.[ODBC Data Sources]
Impala=Cloudera ODBC Driver for Impala[Impala]# Description: DSN Description.
# This key is not necessary and is only to give a description of the data source.
Description=Cloudera Impala ODBC Driver DSN# Driver: The location where the ODBC driver is installed to.
Driver=/opt/cloudera/impalaodbc/lib/universal/libclouderaimpalaodbc.dylib# The DriverUnicodeEncoding setting is only used for SimbaDM
# When set to 1, SimbaDM runs in UTF-16 mode.
# When set to 2, SimbaDM runs in UTF-8 mode.
#DriverUnicodeEncoding=2# Values for HOST, PORT, KrbFQDN, and KrbServiceName should be set here.
# They can also be specified on the connection string.
HOST=[REPLACE_YOUR_IMPALA_HOST]
PORT=21050
Schema=default# The authentication mechanism.
# 0 — No authentication (NOSASL)
# 1 — Kerberos authentication (SASL)
# 2 — Username authentication (SASL)
# 3 — Username/password authentication (NOSASL or SASL depending on UseSASL configuration)
AuthMech=1# Set to 1 to use SASL for authentication.
# Set to 0 to not use SASL.
# When using Kerberos authentication (SASL) or Username authentication (SASL) SASL is always used
# and this configuration is ignored. SASL is always not used for No authentication (NOSASL).
UseSASL=1# Kerberos related settings.
KrbFQDN=_HOST
KrbRealm=[REPLACE_YOUR_REALM]
KrbServiceName=impala# Username/password authentication with SASL settings.
UID=
PWD=# Set to 0 to disable SSL.
# Set to 1 to enable SSL.
SSL=1
CAIssuedCertNamesMismatch=1
TrustedCerts=/opt/cloudera/impalaodbc/lib/universal/cacerts.pem# If you use SSL with AllowSelfSignedServerCert, you can set this configuration.
#AllowSelfSignedServerCert=1# Specify the proxy user ID to use.
#DelegationUID=# General settings
TSaslTransportBufSize=1000
RowsFetchedPerBlock=10000
SocketTimeout=0
StringColumnLength=32767
UseNativeQuery=0
After setting up the .odbc.ini
, your application will refer this setting with appropriate DSN name, like Impala
in this case.
Check the configuration
After configuration, you should kinit with your principal.
$ kinit $USER@YOUR_REALM
You should replace `$USER` and `YOUR_REALM` with the appropriate REALM.
Before using RStudio on you mac, you can check configuration with `isql` command.
$ isql -v “Impala”
+ — — — — — — — — — — — — — — — — — — — -+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+ — — — — — — — — — — — — — — — — — — — -+
SQL>
Implyr Example
After setting .odbc.ini you can connect secure Impala cluster with {implyr}
. For instance, We will visualize the airports’ data.
First, install R packages.
install.packages(c(“implyr”, “odbc”, “DBI”, “dplyr”, “ggplot2”, “ggExtra”))
Then, connect the Impala cluster.
library(implyr)
library(odbc)
drv <- odbc::odbc()
impala <- src_impala(
drv = drv,
dsn = “Impala”
)
If your .odbc.ini
is configured properly, you can connect to Impala cluster.
Let’s visualize the airports data. In this case, we assume the data is in u_ariga
database, so that we will change database using SQL use u_ariga
.
library(DBI)
# Change database
dbExecute(impala, “use u_ariga”)
dbGetQuery(impala, “show tables”)
airports <- tbl(impala, “airports_pq”)# Show the head of airports data
View(airports)airports %>% filter(latitude < 35) %>% count()
#903
Finally, we will show a joint histogram of longitude and latitude.
airports_by_geo <- airports %>% select(longitude, latitude) %>% collect()library(ggplot2)p <- ggplot(airports_by_geo, aes(longitude, latitude)) + geom_point() + theme_classic()
ggExtra::ggMarginal(p, type = “histogram”)
Conclusion
{implyr}
is a great package for Impala and dplyr but it is pretty young project. If you find some problems, why don’t you post into the GitHub issue?