Insert data into Hive from spark

Using the SchemaRDD / DataFrame API via HiveContext

Assume you're using the latest code, something probably like:

val hc = new HiveContext(sc)
import hc.implicits._
existedRdd.toDF().insertInto("hivetable")
or

existedRdd.toDF().registerTempTable("mydata")
hc.sql("insert into hivetable as select xxx from mydata")

Using Hive from R

Using Hive from R

We need to install following packages

R CMD INSTALL rJava
R CMD INSTALL RHive

rm(list=ls())
options( java.parameters = "-Xmx8g" )

library(RJDBC)
if (Sys.getenv("JAVA_HOME")!="") Sys.setenv(JAVA_HOME="")
.jinit()
for(l in list.files('/usr/phd/3.0.0.0-249/hadoop',pattern="*.jar",recursive=TRUE)){ .jaddClassPath(paste("/usr/phd/3.0.0.0-249/hadoop/",l,sep=""))}


hivedrv <- JDBC("org.apache.hive.jdbc.HiveDriver", "/usr/phd/3.0.0.0-249/hive/lib/hive-jdbc.jar")
conn <- dbConnect(hivedrv, "jdbc:hive2://hive:10000/default", "myusername")

counter <- dbGetQuery(conn, "select count(*) from default.tablename")

counter

Change the paths for jar above as per your Hadoop distribution

Passing and set hive conf variables in JDBC and ODBC server

If we want to pass the JDBC and ODBC server with custom hive conf which are normally set via set commands in the script.

The syntax s

jdbc:hive2://<host>:<port>/dbName;sess_var_list?hive_conf_list#hive_var_list

Example can be

jdbc:hive2://foobar:10000/database;auth=noSasl?mapreduce.map.memory.mb=8000;mapreduce.map.java.opts=-Xmx6277m;#foo=bar

Hive Warning: Value had a \n character in it

You stare a hive query it gives following warning

Warning: Value had a \n character in it

Check that your query has proper semi colons for all statements.

Update Hive Metastore

Update Hive Metastore

Hive 0.12 includes the Schema update tool

$ bin/schematool -help

usage: schemaTool

-dbType <databaseType>             Metastore database type

-dryRun                            list SQL scripts (no execute)

-help                              print this message

-info                              Show config and schema details

-initSchema                        Schema initialization

-initSchemaTo <initTo>             Schema initialization to a version

-passWord <password>               Override config file password

-upgradeSchema                     Schema upgrade

-upgradeSchemaFrom <upgradeFrom>   Schema upgrade from a version

-userName <user>                   Override config file user name

-verbose                           only print SQL statements

Depending on what metastore you are using

dbType can be

mysql, postgres, derby or oracle

Update MySQL Hive metastore from 0.10.0 to 0.12.0

For example

bin/schematool -dbType mysql -upgradeSchemaFrom 0.10.0

You can also do a dry run with passing option -dryRun

Hive schematool to create metastore

Hive 0.12 has a new tool called schematool

This tool is used for two purpose

1) Create new metastore database when you install new Hive

2) Upgrade the existing Hive metastore to new version

To create new metastore when you install new hive

Create hive-site.xml

Inside that specify the details for your mysql ( or other ) database

Use the following command to populate that database

$bin/schematool -dbType mysql –initSchema

schematool -dbType mysql –info

You can also see the details 

Debug Hive query and startup

Start hive with following command.

 

bin/hive --hiveconf hive.root.logger=DEBUG,console

 

Logs

 

The logs of the user queries and metastore can be analysed by

User query logs

 

/etc/hive/hive-log4j.properties

 

Configure the variable

 

hive.log.dir

 

Hive metastore logs

 

/etc/hive-metastore/hive-log4j.properties

 

Configure the variable

 

hive.log.dir

 

A very good presentation on how to debug hive errors

 

 

De-Bugging Hive with Hadoop-in-the-Cloud from Hadoop_Summit

Hive Shark Impala Comparison

Credits
This post is nothing but reproduce of work done here at AmpLabs. If you want latest and detailed read i suggest you to go there.
Bigdata world is so beautiful , the research in this field is driving at such a fast pace that eventually BigData is no more synonymous with long running queries , its becoming LiveData everywhere.
The work compares the computation time of Redshift , Hive , Impala , Shark with different types of queries










The performance of Shark in memory has been consistent in all 4 types. It would be interesting to see the comparison when Hive 0.11 is used as it also adds few performance improvements driven by work at Hortonworks.

Handle schema changes evolution in Hadoop

In Hadoop if you use Hive if you try to have different schemas for different partition , you cannot have field inserted in middle.

If the fields are added in end you can use Hive natively.

However things would break if field is inserted in middle.

There are few ways to handle schema evolution and changes in hadoop

Use Avro

For flat schema of a database tables ( or files ) ,  generate avro schema. This Avro schema can be used anywhere in programming or mapping it with hive using AvroSerde

https://cwiki.apache.org/Hive/avroserde-working-with-avro-from-hive.html

I am exploring various JSON apis which can be used and also exploring various methods i can do.

http://www.infoq.com/articles/AVROSchemaJAXB

Nokia has released code to generate Avro schemas from XMLs

https://github.com/Nokia/Avro-Schema-Generator

Okay my problem statement and solution are simple.

The ideas in my mind are

1. Store Schema details of Table in some database
2. Read the database field details and generate Avro schema
3. Store it to some location in Hadoop  /schema/tableschema
4. Map the Hive to use this avro schema location in HDFS
5. If some change comes in schema update the database and the system would again generate new avro schema
6. Push the new schema to HDFS
7. Hive would use new schema without breaking old data should be able to support schema changes and evolution for data in Hadoop

Most NoSQL databases have similar approach , check Oracle link below

http://docs.oracle.com/cd/NOSQL/html/GettingStartedGuide/avroschemas.html

Oracle NoSQL solution manages the schema information and changes in KeyStore

http://docs.oracle.com/cd/NOSQL/html/GettingStartedGuide/provideschema.html

 

Use ORC

https://github.com/hortonworks/orc

Hortonworks guys are working on new file format which have similar feature of storing schema within data like Avro

http://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.0.0.2/ds_Hive/orcfile.html

The "versioned metadata" means that the ORC file's metadata is stored in ProtoBufs so that we can add (or remove) fields to the metadata. That means that for some changes to ORC file format we can provide both forward and backward compatibility.

ORC files like Avro files are self-describing. They include the type structure of the records in the metadata of the file. It will take more integration work with hive to make the schemas very flexible with ORC.

Configure hiveserver2

Please configure Hive with MySQL first before starting hiveserver2

Follow this post

http://jugnu-life.blogspot.com.au/2012/05/hive-mysql-setup-configuration.html

You can use some other database like Oracle also.

Add the following settings in hive-site.xml

<property>

  <name>hive.support.concurrency</name>

  <description>Enable Hive's Table Lock Manager Service</description>

  <value>true</value>

</property>

<property>

  <name>hive.zookeeper.quorum</name>

  <description>Zookeeper quorum used by Hive's Table Lock Manager</description>

  <value>zookeeper_hos1,zookeeper_host2,zookeeper_host3</value>

</property>

<property>

  <name>datanucleus.autoCreateSchema</name>

  <value>false</value>

</property>

 

<property>

  <name>datanucleus.fixedDatastore</name>

  <value>true</value>

</property>

From command prompt

Start hiveserver2

>hiveserver2

The hiveserver2 binary is present in bin folder of hive directory , so you can go to that folder and run it in case you installed hive via tar ball

How to check

Now just start beeline and see if things are working fine

The userame password are not requied if you haven’t configured any LDAP settings for hiveserver

$ /usr/lib/hive/bin/beeline

beeline> !connect jdbc:hive2://localhost:10000 username password org.apache.hive.jdbc.HiveDriver

0: jdbc:hive2://localhost:10000> SHOW TABLES;

show tables;

+-----------+

| tab_name  |

+-----------+

+-----------+

No rows selected (0.238 seconds)

0: jdbc:hive2://localhost:10000>

Export Hive query result to file

$hive -e 'select * from myTable' > MyResultsFile.txt

Null behaviour in Sqoop and Hive

Sqoop by default imports the null values with string null as output.

So any record having null in data would be shown like below in HDFS

|N|null|Jagat Singh|BigData

The issue with such kind of import is that we cannot write hive queries like show me all records where the column is not null.

To know better we should keep in mind that the Hive default representation for null is

\N

So to make Hive treat the record as null should be imported as \N

Read link below

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types#LanguageManualTypes-HandlingofNULLValues

the corresponding Sqoop query to make such kind of import work would be

sqoop import  …--null-string '\\N' --null-non-string '\\N'

We have to escape the \N parameter properly


Note
 \N (backslash with a capital N)—not to be confused with \n, which is only a single newline character.

Hope this helps

Pentaho Reporting BI Server

After you are done with creation of report using report tool you can publish report to BI server

Go to

biserver-ce/pentaho-solutions/system/publisher_config.xml

Change the publisher password to something

Use the same as publisher password while using report creator tool

Hive MySql setup configuration

Hive use Derby database by default for storing its data.

But it has limitation that only one user can access it and the data cannot be shared among multiple machines.

So we can use MySQL Database to store the metadata in hive.

Go to hive-site.xml

and configure the following properties

<property>
  <name>javax.jdo.option.ConnectionURL</name>
  <value>jdbc:mysql://hostname:3306/hive?createDatabaseIfNotExist=true</value>
</property>
<property>
  <name>javax.jdo.option.ConnectionDriverName</name>
  <value>com.mysql.jdbc.Driver</value>
</property>
<property>
  <name>javax.jdo.option.ConnectionUserName</name>
  <value>hadoop</value>
</property>
<property>
  <name>javax.jdo.option.ConnectionPassword</name>
  <value>hadoop</value>
</property>

Create one use hadoop in MySQL and give its password chosen above with grant all privileges

CREATE USER 'hadoop'@'hostname' IDENTIFIED BY 'hadoop';
GRANT ALL PRIVILEGES ON *.* TO 'hadoop' WITH GRANT OPTION;

Thats it :)

Start using Hive

Error retrieving next row Hive Pentaho

Error retrieving next row

Pentaho

Download the latest version of

hive-jdbc-0.7.0-pentaho-SNAPSHOT.jar.

Hive Could not load shims in class null , Pentaho

Error connecting to database [test] : org.pentaho.di.core.exception.KettleDatabaseException:
Error occured while trying to connect to the database

Error connecting to database: (using class org.apache.hadoop.hive.jdbc.HiveDriver)
org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.RuntimeException: java.lang.RuntimeException: Could not load shims in class null

Solution:

This error is due to incompatibility between version of hadoop and hive supported

Drop the supported Hive , Hadoop version jar in pentaho/lib/jdbc directory

Hive index for performance

Indexed Hive

View more presentations from NikhilDeshpande

Start Hive Thrift Server

Hive Thrift server is very useful for integrating HIVE with applications which can talk to Thrift.

Its very much used by reporting tools such as Pentaho

To Start HIVE thrift server just type

# hive --service hiveserver

It would show message like

Starting Hive Thrift Server

To check it hive server has been started successfully

Type

#netstat -nl | grep 10000

Some service must be running there.

Hive do have compatibility issues with latest version of Hadoop. So if your thirft server is not starting just check it hadoop version is supported or not.