Running SQL Query on Hadoop : Apache Hive Alternatives

previous post - Mahout 0.11.0 on Spark1.4.1

Hive is the SQL programmer friendly tool for running SQL query on Hadoop HDFS File system. While running query Hive will convert SQL like query into MapReduce.

Hive is not the only tool will do the same. This post will let give synopsis on open source alternative of Hive.

1) spark sql (previously Shark - Sql on Spark) - will be the best alternative of Hive over Spark. Spark SQL is Spark's module for working with structured data.

2) Cloudera Impala - like Hive but it uses its own execution daemons which we need to install every datanodes in Hadoop cluster. Impala do BI-style Queries on Hadoop.

3) Facebook Presto - like Impala need to install all datanodes. Presto is an open source distributed SQL query engine for running interactive analytic queries against data sources of all sizes ranging from gigabytes to petabytes.

4) Apache Drill - Schema free SQL for Hadoop. It support multiple datastores HDFS, MongoDB and Hbase

Next : 3D visualization using R

Configuring Hive at HUE (Hadoop ui)

previous post - Hue installation error

I have successfully compliled latest version of Hue from source. when i was access by http://localhost:8000/ have seen warning Hive editor not configured.

Hive Editor - The application won't work without a running HiveServer2.

to start Hive

bdalab@solai:/opt$ $HIVE_HOME/bin/hive --service hiveserver2

in another terminal

bdalab@solai:/opt$ $HIVE_HOME/bin/beeline -u jdbc:hove2//localhost:10000

i have got permission denied error Error

Error: Failed to open new session: java.lang.RuntimeException: java.lang.RuntimeException: org.apache.hadoop.security.AccessControlException: Permission denied: user=APP, access=EXECUTE, inode="/tmp/hive/APP/704d9b8d-a9d5-4be6-a8f1-082e8eba9a0c":hdfs:supergroup:drwx------

Solution

then I have changed derby (javax.jdo.option.ConnectionUserName ) username From APP to hdfs in hive_default.xml under HIVE_HOME/conf

Next : Hive with Zeppelin

return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask

previous post - working with HDFS commands from DataNode

I have created partiton table in Hive, When I do Insert into partition table I was stuck of with these error..

Error

    Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask

....
[Fatal Error] Operator FS_2 (id=2): Number of dynamic partitions exceeded hive.exec.max.dynamic.partitions.pernode.

above error in first line was little confusing, if you scroll up the console you may get the second line which wss the real cause of the exception.

Solution

  bdalab@solai:/opt$ hive

  hive> set hive.exec.max.dynamic.partitions.pernode=500

by default hive.exec.max.dynamic.partitions.pernode set to 100, if the partition will exceeds the limit, you will get an error. Just change the default value based on the requirement to rid out of these.

more : Hive 1.0 Status KILLED Aggregation is not enabled

Hive 1.0 Status KILLED Aggregation is not enabled

While creating partition on Hive, the job killed frequently with status KILLED.
Then i have gone through the log file, have seen actual root cause of the issue.

Error 1)

#ERROR

1) Status KILLED Aggregation is not enabled. Try the nodemanager at localhost:54455

2) return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask MapReduce Jobs Launched:

3) [Fatal Error] Operator FS_2 (id=2): Number of dynamic partitions exceeded hive.exec.max.dynamic.partitions.pernode.

Solution
error shows it exceeds number of partition on single node I just increased the no.of partitions pernode to 200, by default it is 100

root@solai[bin]# hive
hive> set hive.exec.max.dynamic.partitions.pernode=200

the above solution will apply only for the current session, if you come out / exit the session hive.exec.max.dynamic.partitions.pernode property will assign 100.
If you want the changes to be made permanent, open hive-site.xml file under $HIVE_HOME/conf and change.

root@solai[bin]# sudo vim.tiny $HIVE_HOME/conf/hive-site.xml

Simple way to configuring mysql or Postgresql RDBMS as Hive metastore

Hive will store the metadata information (i.e like RDBMS will stores the table
and column information) out of HDFS and it will process the data available in HDFS.

By default Hive store its metastore into Derby a lightweight database.
which will serve single instance at a time. If you try to start mutltiple instance of Hive, you will get error like
"Another instance of Derby may have already booted the database".

In this will see how we can configure other RDBMS (MySQL & PostgreSQL) as Hive metastore.

Create / rename hive-default.xml.template TO hive-site.xml under $HIVE_HOME/conf

hadoop@solai# vim.tiny $HIVE_HOME/conf/hive-default.xml

change the value of the following property

<property>

<name>javax.jdo.option.ConnectionURL</name>

<value>jdbc:mysql://localhost:3306/hivedb</value> 

</property>

<property>

<name>javax.jdo.option.ConnectionDriverName</name>

<value>com.mysql.jdbc.Driver</value>

</property>

<property>

<name>javax.jdo.option.ConnectionUserName</name> 

<value>mysqlroot</value> 

</property>

<property>

<name>javax.jdo.option.ConnectionPassword</name> 

<value>hive@123</value> 
</property>

download and plcae the "mysql-connector-java-5.x.xx-bin.jar" to the $HIVE_HOME/lib

hadoop@solai# mv /home/hadoop/Downloads/mysql-connector-java-5.1.31.tar.gz $HIVE_HOME/lib

In Mysql create database "hivedb" and load the hive schema to the database "hivedb"

mysql> create database hivedb;
mysql> use hivedb;

## following will create hive schema in mysql database.
mysql> SOURCE $HIVE_HOME/scripts/metastore/upgrade/mysql/hive-schema-0.12.0.mysql.sql

its important to restrict user to alter / delete hivedb.

mysql> CREATE USER 'mysqlroot'@'hivedb' IDENTIFIED BY 'hive@123';



mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'mysqlroot'@'hivedb';



mysql> GRANT SELECT,INSERT,UPDATE,DELETE,LOCK TABLES,EXECUTE ON hivedb.* TO 'mysqlroot'@'localhost';



mysql> FLUSH PRIVILEGES;



mysql> quit;

Enter Hive CLI, for create table

hadoop@solai#$HIVE_HOME/bin/hive

hive> create table testHiveMysql(uname string, uplace string);

enter into mysql to check the schema information created in hive environment. following lines will return the table and column information.

mysql> select * from TBLS;
mysql> select * from COLUMNS_V2;
mysql> show tables;

show tables, will return all the tables pertaining to the Hive schema