How-to: Do Statistical Analysis with Impala and R

The new RImpala package brings the speed and interactivity of Impala to queries from R.

Our thanks to Austin Chungath, Sachin Sudarshana, and Vikas Raguttahalli of Mu Sigma, a Decision Sciences and Big Data analytics company, for the guest post below.

As is well known, Apache Hadoop traditionally relies on the MapReduce paradigm for parallel processing, which is an excellent programming model for batch-oriented workloads. But when ad hoc, interactive querying is required, the batch model fails to meet performance expectations due to its inherent latency.

To overcome this drawback, Cloudera introduced Cloudera Impala, the open source distributed SQL query engine for Hadoop data. Impala brings the necessary speed to queries that were otherwise not interactive when executed by the batch Apache Hive engine; Hive queries that used to take minutes can be executed in a matter of seconds using Impala.

Impala is quite exciting for us at Mu Sigma because existing Hive queries can run interactively with few or no changes. Furthermore, because we do a lot of our statistical computing on R, the popular open source statistical computing language, we considered it worthwhile to bring the speed of Impala to R.

To meet that goal, we have created a new R package, RImpala, which connects Impala to R. RImpala enables querying the data residing in HDFS and Apache HBase from R, which can be further processed as an R object using R functions. RImpala is now available for download from the Comprehensive R Archive Network (CRAN) under GNU General Public License (GPL3).

The RImpala architecture is simple: we used the existing Impala JDBC drivers and wrote a Java program to connect and query Impala, which we then called from R using the rJava package. We put them all together in an R package that you can use to easily query Impala from R.

Steps for Installing RImpala

Assuming that you have R and Impala already installed, installing the RImpala package is straightforward and is done in a manner similar to any other R package. There are two steps to installing RImpala and getting it working:

Step 1: Install the package from CRAN

You can install RImpala directly using the install.packages() command in R.

> install.packages("RImpala")

 

Alternatively, if you need to do offline installation of the package, you can download it from here and install using the R CMD INSTALL command:

R CMD install RImpala_0.1.1.tar.gz

 

Step 2: Install the Impala JDBC drivers

You need to install Cloudera’s JDBC drivers before you can use the RImpala package that we installed earlier. Cloudera provides JBDC jars on its website that you can download directly. As of this writing, this is the link to zip file containing the JDBC jars.

There are two ways to do this:

  1. If you have Impala installed on the machine running R, then you will have the necessary JDBC jars already (probably in /usr/lib/impala/lib) and you can use them to initiate the connection to Impala.
  2. If the machine running R is a different server than the Impala server, then you need to download the JDBC jars from the above link and extract it to a location that can be accessed by the R user.

After you have installed the JDBC drivers you can start using the RImpala package:

  1. Load the library.
    library(RImpala)
    

     

  2. Initialize the JDBC jars.
    rimpala.init("/path/to/impala/jars")
    

     

  3. Connect to Impala.
    rimpala.connect("IP or Hostname of Impala server", "port")
    

     

    The following is an Rscript showing how to connect to Impala:

    library(RImpala)
    rimpala.init(libs="/tmp/impala/jars/")
    rimpala.connect("192.168.10.1","21050")
    

     

    Location of JDBC jars = /tmp/impala/jars

    IP of the server running impalad service = 192.168.10.1

    Port where the impalad service is listening = 21050

The default parameter for the rimpala.init() function is “/usr/lib/impala/lib” and the default parameters for rimpala.connect() function are “localhost” and “21050” respectively.

To run a query on the impalad instance that the client has connected, you can use the rimpala.query() function. Example:

result

 

All the contents of the sample_table will be stored in the result object as a data frame. This data frame can now be used for further analytical processing in R.

You can also install the RImpala package on a client machine running Microsoft Windows. Since the JDBC jars are platform independent, you can extract them into a folder on a Windows machine (such as “C:\Program Files\impala”) and then this location can be passed as a parameter to the rimpala.init() function.

The following a simple example that shows you how to use RImpala:

> library(RImpala)
Loading required package: rJava

> rimpala.init(libs="/tmp/impala/jars/") # Adds the impala JDBC jars present in the "/tmp/impala/jars/" folder to the classpath
[1] "Classpath added successfully"

> rimpala.connect(IP="192.168.10.1",port="21050")  # Establishes a connection to impalad instance running on the machine 172.25.1.151 on the port 21050
[1] TRUE

> rimpala.invalidate() # Invalidates the metadata of all the tables present in the Hive metastore
[1] TRUE

> rimpala.showdatabases()# Displays all the databases available
# Output #
	name
1	airlines
2	bank
3	default

> rimpala.usedatabase("bank") # Changes the current database to "bank"
Database changed to bank
[1] TRUE

> rimpala.showtables() # Displays all the tables present in the current database
# Output  #
name
1	bank_web_clicks
2	ticker_100m
3	stock_1gb
4	weblog_10gb

> rimpala.describe("bank_web_clicks") # Describes the table "bank_web_clicks"
# Output  #
Name		type		comment
1	customer_id	int     	Customer ID
2	session_id	int		Session ID
3	page		string	Web page name
4	datestamp	timestamp	Date

> result  result
# Output #
customer_id	 session_id	cnt
1	32 		 21		5200
2	34  		 12 		5100
3	35  		 49  		4105
4	32  		 34  		3600
5	36  		 32  		3218
6	37		 67  		3190
7	31  		 45  		2990
8	35		 75  		2300
9	34  		 69  		2113

> rimpala.close() # Closes the connection to the impalad instance
[1] TRUE

 

Conclusion

Impala is an exciting new technology that is gaining popularity and will probably grow to be an enterprise asset in the Hadoop world. We hope that RImpala will be a fruitful package for all Big Data analysts to leverage the power of Impala from R.

Impala is an ongoing and thriving effort at Cloudera and will continue to evolve with richer functionality and improved performance – and so will RImpala. We will continue to improve the package over time and incorporate new features into RImpala as and when they are made available in Impala.  

Austin Chungath is a Senior Research Analyst with Mu Sigma’s Innovation & Development Team and maintainer of the RImpala project. He does research on various tools in the Hadoop ecosystem and the possibilities that they bring for analytics. He spends his free time contributing to Open Source projects like Apache Tez or building small robots.

Sachin Sudarshana is a Research Analyst with Mu Sigma’s Innovation & Development Team. His responsibilities include researching emerging tools in the Hadoop ecosystem and how they can be leveraged in an analytics context.

Vikas Raguttahalli is a Research Lead with Mu Sigma’s Innovation & Development Team. He is responsible for working with client delivery teams and helping clients institutionalize Big Data within their organizations, as well as researching new and upcoming Big Data tools. His expertise includes R, MapReduce, Hive, Pig, Mahout and the wider Hadoop ecosystem.

Filed under:

16 Responses
  • Kostiantyn / December 16, 2013 / 10:53 AM

    As I see rimpala.connect doesn’t have possibility to pass login/password. How can this Impala connection be secured?

  • svkerr / December 16, 2013 / 10:14 PM

    This does not solve the R memory bound problem?

  • Austin Chungath / December 17, 2013 / 6:58 AM

    Hi Kostiantyn,
    We are working on creating a secure connection from RImpala. We will let you know when it is done. Please feel free to suggest any more features that you might need.
    Thanks.

  • Kamlesh Kshirsagar / December 19, 2013 / 4:30 AM

    Hello,

    I tried the steps above. I get following error:

    > rimpala.connect(“localhost”,”21050″)
    Error in .jcall(“RJavaTools”, “Ljava/lang/Object;”, “invokeMethod”, cl, :
    java.lang.NoClassDefFoundError: org.apache.hive.jdbc.HiveConnection

    Please can you help me.

    Regards,
    Kamlesh

  • Austin Chungath / December 19, 2013 / 6:14 PM

    Kamlesh,

    Extract the jars from https://downloads.cloudera.com/impala-jdbc/impala-jdbc-0.5-2.zip

    to say /tmp/impala-jdbc

    then start R, assuming that impala is running on 127.0.0.1 at 21050 the following should work:

    library(RImpala)
    rimpala.init(libs=”/tmp/impala-jdbc/”)
    rimpala.connect(IP=”127.0.0.1″,port=”21050″)

  • Austin Chungath / December 19, 2013 / 11:09 PM

    Svkerr,
    The results of your query from Impala that you load into R through the RImpala package will still be bound by how much memory R has at its disposal.

  • Kamlesh Kshirsagar / January 10, 2014 / 6:45 AM

    Hello Austin,

    I followed the steps you mentioned. I am still getting an error.

    > library(RImpala)
    > rimpala.init(libs=”/tmp/impala-jdbc/”)
    [1] “Classpath added successfully”
    > rimpala.connect(IP=”127.0.0.1″,port=”21050″)
    Error in .jcall(“RJavaTools”, “Ljava/lang/Object;”, “invokeMethod”, cl, :
    java.lang.NoClassDefFoundError: org.apache.hive.jdbc.HiveConnection

    Please could you help me on this.

    Regards,

    Kamlesh

  • Austin Chungath / January 14, 2014 / 12:46 AM

    As discussed in our conversation over mail. Connecting to your VM from an R session outside the VM solved the problem. Not sure why the NoClassDefFoundError happens from the R session inside the VM – must be some Java environment settings gone wrong.

    To install RImpala on Windows:
    Download R and install : http://cran.r-project.org/bin/windows/base/
    Download RStudio and install: http://www.rstudio.com/ide/download/

    Copy and extract this https://downloads.cloudera.com/impala-jdbc/impala-jdbc-0.5-2.zip to a location on your file system say C:\impala-jar

    > library(RImpala)
    Loading required package: rJava
    > rimpala.init(libs=”C:\\impala-jar”)
    [1] “Classpath added successfully”
    > rimpala.connect(IP=”192.168.204.164″,port=”21050″)
    [1] TRUE
    > rimpala.invalidate()
    > rimpala.showdatabases()
    name
    1 default

  • Kiran Kaipa / January 15, 2014 / 11:03 PM

    Hi,
    Firstly, I am excited to use this library and trying to get started.
    I am setting up Impala connection using my windows pc version R and RStudio.
    When I had to initialize I had to point to individual jars in the extracted folder for class path. e.g. rimpala.init(libs=”./impala-jar//slf4j-log4j12-1.6.1.jar”)
    for all the jars.
    I get [1] “Classpath added successfully” for all jars.
    Next when I am trying to use rimpala.connect() to connect to my impala server but it returns a NULL error and FALSE message. Can you please help?
    Here is the o/p:
    rimpala.connect(“BISOLHYD3″,”21050″)
    Error: null
    [1] FALSE
    rimpala.connect(“10.136.225.34″,”21050″)
    Error: null
    [1] FALSE

  • Slaton Lipscomb / January 28, 2014 / 7:51 PM

    No problem connecting to Impala, or running commands like showtables, describe, etc. I can run some queries.

    However I am seeing “java.sql.SQLException: Method not supported” for any query I attempt that changes a column name using SELECT .. AS ..

    Example:
    rimpala.query(“SELECT foo AS bar FROM my_table LIMIT 20″)
    Error in .jcall(“RJavaTools”, “Ljava/lang/Object;”, “invokeMethod”, cl, :
    java.sql.SQLException: Method not supported

    Is this a limit in Cloudera’s current JDBC driver?

  • Austin Chungath / January 29, 2014 / 2:32 AM

    Hi Kiran,
    It’s nice to hear from you.
    The RImpala code requires all the jdbc jars to be in a directory and you need to give the path to the directory in the init function.
    Adding one jar at a time will not work as the init function expects a directory. But init function still says “Classpath added successfully” even when you are adding one jar at a time. Thanks for bringing this to my notice.

  • kamal / February 13, 2014 / 11:18 PM

    No problem connecting to Impala, or running commands like showtables, describe, etc. I can run some queries.

    However I am seeing “java.sql.SQLException: Method not supported” for any query I attempt that changes a column name using SELECT .. AS ..

    Example:
    rimpala.query(“SELECT foo AS bar FROM my_table LIMIT 20″)
    Error in .jcall(“RJavaTools”, “Ljava/lang/Object;”, “invokeMethod”, cl, :
    java.sql.SQLException: Method not supported

    I have added all the Required jar file in directory then also getting the same error

  • Austin Chungath / February 25, 2014 / 3:07 AM

    For those who are interested – the source is available at https://github.com/austincv/RImpala

  • Vinodh / March 06, 2014 / 10:51 PM

    Hi,
    I am able to connect. But when I execute a query I am getting following error
    df=rimpala.query(“select id,name from test1″)
    Error in `colnames<-`(`*tmp*`, value = c("id", "name")) :
    'names' attribute [2] must be the same length as the vector [1]
    If I put only one field in select query it works.
    Appreciate help

  • Sachin Sudarshana / March 18, 2014 / 11:16 PM

    HI Vinodh,

    I’m not able to replicate the error that you’ve mentioned.
    Could you please check if the table does contain 2 columns?
    If possible, share a sample of your dataset and I will try to resolve the issue.

    Thank you,
    Sachin

  • Vinodh S / March 20, 2014 / 11:58 PM

    Hi Sachin,
    Thanks for looking into this.
    The table Test in Impala is having the structure
    id name
    1 vinodh
    I am using query select * from test.
    Error is same with any query done on any table which fetches more than one column.
    I have checked the source file RImpala.R. The error is in rimpala.query in line colnames(onlyData)=colNames.
    If I select only one column it works fine.
    Hope this helps
    Thanks,
    Vinodh S

Leave a comment


− six = 1