Query HBase tables with Impala

As described in other blog posts, Impala uses Hive Metastore Service to query the underlaying data. In this post I use the Hive-HBase handler to connect Hive and HBase and query the data later with Impala. In the past I've written a tutorial (http://mapredit.blogspot.de/2012/12/using-hives-hbase-handler.html) how to connect HBase and Hive, please follow the instructions there.

This approach offers Data Scientists a wide field of work with data stored in HDFS and / or HBase. You will get the possibility to run queries against your stored data independently which technology and database do you use, simply by querying the different data sources in a fast and easy way.

I use the official available census data gathered in 2000 by the US government. The goal is to push this data as CSV into HBase and query this table per Impala. I've made a demonstration script which is available in my git repository.

Demonstration scenario

The dataset looks pretty simple:

cat DEC_00_SF3_P077_with_ann_noheader.csv

8600000US00601,00601,006015-DigitZCTA,0063-DigitZCTA,11102
8600000US00602,00602,006025-DigitZCTA,0063-DigitZCTA,12869
8600000US00603,00603,006035-DigitZCTA,0063-DigitZCTA,12423
8600000US00604,00604,006045-DigitZCTA,0063-DigitZCTA,33548
8600000US00606,00606,006065-DigitZCTA,0063-DigitZCTA,10603

Create the HBase table:

create 'zipcode_hive', 'id', 'zip', 'desc', 'income'

and create an external table in Hive which looks as follows:

CREATE EXTERNAL TABLE ZIPCODE_HBASE (key STRING,zip STRING,desc1 STRING,desc2 STRING,income STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,zip:zip,desc:desc1,desc:desc2,income:income") TBLPROPERTIES("hbase.table.name" = "zipcode_hive");

Here we map the Hive tables per HBaseStorageHandler to the HBase scheme we've used in the step above.

After these steps are successfully finished, we need to copy the CSV data into HBase. I chose Pig for this task but you can use a translate table in Hive, too.

Here's my Pig script:

cat PopulateData.pig


copyFromLocal DEC_00_SF3_P077_with_ann_noheader.csv ziptest.csv
A = LOAD 'ziptest.csv' USING PigStorage(',') as (id:chararray, zip:chararray, desc1:chararray, desc2:chararray, income:chararray); STORE A INTO 'hbase://zipcode_hive' USING org.apache.pig.backend.hadoop.hbase.HBaseStorage('zip:zip,desc:desc1,desc:desc2,income:income');

The job takes a few seconds and the data is available per HBase:

scan 'zipcode_hive', LIMIT => 2


ROW                                    COLUMN+CELL                                                                                                    
 8600000US00601                        column=desc:desc1, timestamp=1368880594523, value=006015-DigitZCTA                                             
 8600000US00601                        column=desc:desc2, timestamp=1368880594523, value=0063-DigitZCTA                                               
 8600000US00601                        column=income:income, timestamp=1368880594523, value=11102                                                     
 8600000US00601                        column=zip:zip, timestamp=1368880594523, value=00601                                                           
 8600000US00602                        column=desc:desc1, timestamp=1368880594523, value=006025-DigitZCTA                                             
 8600000US00602                        column=desc:desc2, timestamp=1368880594523, value=0063-DigitZCTA                                               
 8600000US00602                        column=income:income, timestamp=1368880594523, value=12869                                                     
 8600000US00602                        column=zip:zip, timestamp=1368880594523, value=00602 

Now we do the same with Impala:

select * from zipcode_hbase limit 4


Using service name 'impala' for kerberos
Connected to hadoop1:21000
Server version: impalad version 1.0 RELEASE (build d1bf0d1dac339af3692ffa17a5e3fdae0aed751f)
Query: select *
from ZIPCODE_HBASE limit 4
Query finished, fetching results ...
+----------------+------------------+----------------+--------+-------+
| key            | desc1            | desc2          | income | zip   |
+----------------+------------------+----------------+--------+-------+
| 8600000US00601 | 006015-DigitZCTA | 0063-DigitZCTA | 11102  | 00601 |
| 8600000US00602 | 006025-DigitZCTA | 0063-DigitZCTA | 12869  | 00602 |
| 8600000US00603 | 006035-DigitZCTA | 0063-DigitZCTA | 12423  | 00603 |
| 8600000US00604 | 006045-DigitZCTA | 0063-DigitZCTA | 33548  | 00604 |
+----------------+------------------+----------------+--------+-------+
Returned 4 row(s) in 0.42s

Another query to get the incomes between 1,000 and 5,000 US$, sorted by income:

select * from zipcode_hbase where income between '1000' and '5000' order by income DESC limit 20;


+----------------+------------------+----------------+--------+-------+
| key            | desc1            | desc2          | income | zip   |
+----------------+------------------+----------------+--------+-------+
| 8600000US64138 | 641385-DigitZCTA | 6413-DigitZCTA | 49995  | 64138 |
| 8600000US12477 | 124775-DigitZCTA | 1243-DigitZCTA | 49993  | 12477 |
| 8600000US33025 | 330255-DigitZCTA | 3303-DigitZCTA | 49991  | 33025 |
| 8600000US44119 | 441195-DigitZCTA | 4413-DigitZCTA | 49988  | 44119 |
| 8600000US34997 | 349975-DigitZCTA | 3493-DigitZCTA | 49982  | 34997 |
| 8600000US70665 | 706655-DigitZCTA | 7063-DigitZCTA | 49981  | 70665 |
| 8600000US28625 | 286255-DigitZCTA | 2863-DigitZCTA | 49981  | 28625 |
| 8600000US76134 | 761345-DigitZCTA | 7613-DigitZCTA | 49979  | 76134 |
| 8600000US44618 | 446185-DigitZCTA | 4463-DigitZCTA | 49978  | 44618 |
| 8600000US65714 | 657145-DigitZCTA | 6573-DigitZCTA | 49978  | 65714 |
| 8600000US77338 | 773385-DigitZCTA | 7733-DigitZCTA | 49976  | 77338 |
| 8600000US14622 | 146225-DigitZCTA | 1463-DigitZCTA | 49972  | 14622 |
| 8600000US84339 | 843395-DigitZCTA | 8433-DigitZCTA | 49972  | 84339 |
| 8600000US85020 | 850205-DigitZCTA | 8503-DigitZCTA | 49967  | 85020 |
| 8600000US64061 | 640615-DigitZCTA | 6403-DigitZCTA | 49964  | 64061 |
| 8600000US97361 | 973615-DigitZCTA | 9733-DigitZCTA | 49961  | 97361 |
| 8600000US30008 | 300085-DigitZCTA | 3003-DigitZCTA | 49960  | 30008 |
| 8600000US48634 | 486345-DigitZCTA | 4863-DigitZCTA | 49958  | 48634 |
| 8600000US47923 | 479235-DigitZCTA | 4793-DigitZCTA | 49946  | 47923 |
| 8600000US46958 | 469585-DigitZCTA | 4693-DigitZCTA | 49946  | 46958 |
+----------------+------------------+----------------+--------+-------+
Returned 20 row(s) in 1.08s

HBase: MSLAB and CMS vs. ParallelGC

Tuning Java opts for HBase, for example, are necessary steps to get the best performance and stability in large installations. The optimal recommendation looks like:

HBASE_OPTS="-XX:+UseParNewGC -XX:+UseConcMarkSweepGC -XX:CMSInitiatingOccupancyFraction=70 -XX:+CMSParallelRemarkEnabled"

But you can also achieve great success with:

HBASE_OPTS="-server -XX:+UseParallelGC XX:+UseParallelOldGC -XX:ParallelGCThreads=8"

What are the differences between ParallelGC and CMS? 

CMS uses more CPU, but runs concurrently. If a thread is failing, CMS falls back to a non-parallel mode and stops the VM for the entire time it's collecting. But this risk can be minimized by using MSLAB in your HBase configuration.

ParallelGC have a better throughput and longer pause times, and stop the VM on every collection. Means for HBase, you'll have a pause (around 1 sec per GB), which can lead on high loaded clusters to outages in a non acceptable time range.

MSLAB (MemStore-Local Allocation Buffers)

The most GC pauses are caused by old-gen fragmentation, and CMS can't defragment without pause the VM (Juliet pause). MSLAB now moves the memstore allocations into the configured chunks into the old generation. When you start or upgrade into HBase 0.92x, MSLAB is enabled per default (http://hbase.apache.org/book/upgrade0.92.html). 

hbase.hregion.memstore.mslab.enabled=true
hbase.hregion.memstore.mslab.chunksize=2MB (2MB per default)
hbase.hregion.memstore.mslab.max.allocation=256KB (256KB per default)

More about MSLAB you can find in Todd's presentation: http://www.slideshare.net/cloudera/hbase-hug-presentation

Flume 1.3.1 Windows binary release online

Andy Blozhou, a Chinese Flume enthusiast provide precompiled Windows binaries of Flume-1.3.1, including a startup bat and Avro bat.
You can grap this build on their website http://abloz.com/flume/windows_download.html :

======== snip ========

This is the flume-ng 1.3.1 windows version for download.

apache-flume-1.3.1-bin.zip 

simple usage:

unzip the apache-flume-1.3.1-bin.zip

run bin/flume.bat for agent. 

run bin/flume-avroclient.bat for avro-client. 

Need modify for your own env. 

detail:

(To compile flume-ng on windows, please reference http://mapredit.blogspot.com/2012/07/run-flume-13x-on-windows.html or my chinese version http://abloz.com/2013/02/18/compile-under-windows-flume-1-3-1.html)

1.download the windows version of flume 1.3.1 file apache-flume-1.3.1-bin.zip from http://abloz.com/flume/windows_download.html

2.unzip the apache-flume-1.3.1-bin.zip to a directory.

3.install jdk 1.6 from oracle,and set JAVA_HOME of the env.

download from http://www.oracle.com/technetwork/java/javase/downloads/index.html

4.test agent:

4.1 modify settings of conf/console.conf,conf/hdfs.conf for agent test.

4.2 test source syslog, sink: console out agent

4.2.1 check flume.bat,modify the variables to your env.

4.2.2 click flume.bat

4.2.3 on another computer run command:

echo "<13>test msg" >/tmp/msg

nc -v your_flume_sysloghost port < /tmp/msg

4.2.4 check your syslog host flume output

4.2.5 samples see http://abloz.com/2013/02/18/compile-under-windows-flume-1-3-1.html

4.3 test avro-client

4.3.1 run a avro source flume agent on a node.

4.3.2 modify flume-avroclient.bat and head.txt

4.3.3 run flume-avroclient.bat

tested on windows7 32bit version

enjoy!

Andy

2013.2.20

http://abloz.com

LZO Compression with Oozie

It happens, when one of the compression codec is switched to LZO, that Oozie can't start any MR job successfully. Usually this is done per core-site.xml:

<property>

<name>io.compression.codecs</name>

<value>org.apache.hadoop.io.compress.GzipCodec,org.apache.hadoop.io.compress.DefaultCodec,com.hadoop.compression.lzo.LzoCodec,com.hadoop.compression.lzo.LzopCodec,org.apache.hadoop.io.compress.BZip2Codec</value>

</property>

<property>

<name>io.compression.codec.lzo.class</name>

<value>com.hadoop.compression.lzo.LzoCodec</value>

</property>

Oozie reports a ClassNotFound error (java.lang.ClassNotFoundException: Class com.hadoop.compression.lzo.LzoCodec not found). To get the jobs running copy or link hadoop-lzo.jar into /var/lib/oozie/ and restart Oozie's server. 

The second, most common issue most people forget is to set the shared lib directory:

[root@hadoop2 ~]# sudo -u hdfs hadoop fs -mkdir  /user/oozie [root@hadoop2 ~]# sudo -u hdfs hadoop fs -chown oozie:oozie /user/oozie [root@hadoop2 ~]# mkdir /tmp/share && cd /tmp/share && tar xvfz /usr/lib/oozie/oozie-sharelib.tar.gz [root@hadoop2 ~]# sudo -u oozie hadoop fs -put share /user/oozie/share From CDH 4.1 on a jar package is delivered, called uber JAR. It contains only dependencies to other jar files in a lib/ folder inside of it. After enabling this property, the user can use this in their mapreduce jobs and notify Oozie about this special jar file. You can enable this package per oozie-site.xml <property> <name>oozie.action.mapreduce.uber.jar.enable</name> <value>true</value> When this property is set, users can use the oozie.mapreduce.uber.jar configuration property in their MapReduce workflows to notify Oozie that the specified JAR file is an uber JAR.

Impala and Kerberos

First, Impala is beta software and has some limitations. Stay tuned and test this, you'll see it can be change your BI world dramatically.

What is Impala? 

Impala provides fast, interactive SQL queries directly on your Apache Hadoop data stored in HDFS or HBase. In addition to using the same unified storage platform, Impala also uses the same metadata, SQL syntax (Hive SQL), ODBC driver and user interface (Hue Beeswax) as Apache Hive. This provides a familiar and unified platform for batch-oriented or real-time queries.
(https://ccp.cloudera.com/display/IMPALA10BETADOC/Introducing+Cloudera+Impala)

You can build Impala by source (https://github.com/cloudera/impala) or you can grab them by using yum on a RHEL / CentOS 6x server. Imapla doesn't support RHEL / CentOS prior 6, since the most part of Impala is written in C++.

I choose the rpm-version for this article, but the compiled version will work in the same manner. To grab impala directly per yum setup a new repository:

#> cat /etc/yum.repos.d/impala.repo
[cloudera-impala]
name=Impala
baseurl=http://beta.cloudera.com/impala/redhat/6/x86_64/impala/0/
gpgkey = http://beta.cloudera.com/impala/redhat/6/x86_64/impala/RPM-GPG-KEY-cloudera
gpgcheck = 1

and install impala and all needed libs per yum:

yum install impala impala-shell cyrus-sasl-devel cyrus-sasl-gssapi gcc-c++ gcc c++ python-setuptools -y && easy_install sasl

You should use the newest JDK from Oracle and you have to install it along your cluster, in this article jdk-6u37-linux-x64-rpm.bin was the actual release. Note, you have to install the JDK after you have installed Impala per yum, as the dependencies install OpenJDK too. To avoid the using of OpenJDK point your system(s) per alternative to the release you want to use:

alternatives --install /usr/bin/javaws javaws /usr/java/latest/jre/bin/javaws 20000
alternatives --install /usr/bin/java java /usr/java/latest/jre/bin/java 20000

To be sure you're running with the JDK you've installed ago you should check it:

java -version
java version "1.6.0_37"
Java(TM) SE Runtime Environment (build 1.6.0_37-b06)
Java HotSpot(TM) 64-Bit Server VM (build 20.12-b01, mixed mode)

One of the things which can be go wrong are some missed libs, they are dynamically linked from impalad and not present in the default library stores. Check it with ldd and link the missed libs into /usr/lib64/, in my case I did:

ln -s /usr/java/jdk1.6.0_37/jre/lib/amd64/server/libjvm.so /usr/lib64/libjvm.so
ln -s /usr/java/jdk1.6.0_37/jre/lib/amd64/libjsig.so /usr/lib64/libjsig.so
ln -s /usr/lib/impala/lib/libhdfs.so.0.0.0 /usr/lib64/libhdfs.so.0.0.0

you should check if you find any missed libraries by using ldd (ldd /usr/lib/impala/sbin/impalad) .

Copy your hive, hdfs and hbase config files into the config directory of Impala and create a log4.properties file within $IMPALA_HOME/conf/:

log.threshold=INFO
main.logger=FA
impala.root.logger=${log.threshold},${main.logger}
log4j.rootLogger=${impala.root.logger}
log.dir=/var/log/impalad
log.file=impalad.INFO
log4j.appender.FA=org.apache.log4j.FileAppender
log4j.appender.FA.File=${log.dir}/${log.file}
log4j.appender.FA.layout=org.apache.log4j.PatternLayout
log4j.appender.FA.layout.ConversionPattern=%p%d{MMdd HH:mm:ss.SSS'000'} %t %c] %m%n
log4j.appender.console=org.apache.log4j.ConsoleAppender
log4j.appender.console.target=System.err
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=%d{yy/MM/dd HH:mm:ss} %p %c{2}: %m%n

The config directory within impalas home should have the following files present, to determine which home-directory the user impala use check it with "echo ~impala".

ll /usr/lib/impala/conf/
total 24
-rw-r--r-- 1 root root 1243 Dec 10 14:59 core-site.xml
-rw-r--r-- 1 root root 4596 Sep 2 09:35 hdfs-site.xml
-rw-r--r-- 1 root root 1157 Dec 10 10:36 hive-site.xml
-rw------- 1 impala impala 594 Dec 11 12:29 impala.keytab
-rw-r--r-- 1 root root 647 Dec 11 12:31 log4j.properties

Sync the content of the directory to all other nodes in your cluster.

Kerberos integration

If you use RHEL kerberos KDC packages you have to tweak your principals. From RHEL4 on principals getting a default renew_lifetime by zero. Means, you can get a renewable ticket, but you can't renew this.

To solve this you have to modify the krbtgt principal as well all other principals who should have the availability to renew their tickets.

kadmin.local: modprinc -maxrenewlife 1day krbtgt/ALO.ALT@ALO.ALT
kadmin.local: addprinc -randkey -maxrenewlife 1day +allow_renewable impala/hadoop1.alo.alt@ALO.ALT

Export the keytab (xst -norandkey -k impala.keytab impala/hadoop1.alo.alt@ALO.ALT HTTP/hadoop1.alo.alt@ALO.ALT), place it in $IMPALA_HOME/conf and obtain a renewable ticket with
sudo -u impala kinit -r 1day -k -t /usr/lib/impala/conf/impala.keytab impala/hadoop1.alo.alt@ALO.ALT

I created a poor startscript to check if all is working as expected and start statestore as well as  impalad on your server:

CONF=/usr/lib/impala/conf
USER=impala
PWD=`echo ~$USER`
HOST=`hostname`
REALM=ALO.ALT

export GLOG_minloglevel=0
export GLOG_logbuflevel=-1
export GLOG_log_dir=/var/log/impala
export GLOG_max_log_size=200

mkdir -p /var/log/impala
chown -R impala: /var/log/impala

# obtain a new ticket
sudo -u impala kinit -r 1day -k -t $CONF/$USER.keytab $USER/$HOST@$REALM

#start it up

statestored -state_store_port=24000 -enable_webserver=true -webserver_port=25010 -log_filename=impala-state-store -principal=$USER/$HOST@$REALM -keytab_file=$CONF/impala.keytab &

impalad -state_store_host=hadoop1.alo.alt -nn=hadoop1.alo.alt -nn_port=9000 -hostname=hadoop1.alo.alt -ipaddress=192.168.56.101 -enable_webserver=true -webserver_port=25000 -principal=$USER/$HOST@$REALM -keytab_file=$CONF/impala.keytab -kerberos_ticket_life=36000 -log_filename=impala &

To control if all is running well you can now point your browser to the configured webservices:

statestore: http://<statestore-server>:25010

impalad: http://<impala-server>:25000

Both services deliver a bunch of monitoring features, as example you can grab metrics from the /metrics endpoint.

Using impala-shell with kerberos

To use impala shell with kerberos you have to get a valid ticket for your user first and have to invoke the shell per impala-shell -k. Note, that on all clients you have to install python sasl (best way per easy_install sasl)

[~]$ impala-shell -k

Using service name 'impala' for kerberos

Welcome to the Impala shell. Press TAB twice to see a list of available commands.

Copyright (c) 2012 Cloudera, Inc. All rights reserved.

(Build version: Impala v0.3 (3cb725b) built on Fri Nov 23 13:51:59 PST 2012)

[Not connected] > connect hadoop1:21000

[hadoop1:21000] > show tables

hbase_test

hbase_test2

hivetest1

hivetest2

[hadoop1:21000] >