question Re: How do I create an ORC Hive table from Spark? in Archives of Support Questions (Read Only)

How do I create an ORC Hive table from Spark?

christopher_w_m — Thu, 19 Nov 2015 11:03:41 GMT

I'm currently using Spark 1.4 and I'm loading some data into a DataFrame using jdbc:

val jdbcDF = sqlContext.load("jdbc", options)

How can I save the jdbcDF DataFrame to a Hive table using the ORC file format?

Re: How do I create an ORC Hive table from Spark?

vshukla — Thu, 19 Nov 2015 11:07:46 GMT

df.write.format("orc") will get you there.

See: http://hortonworks.com/blog/bringing-orc-support-into-apache-spark/ or http://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.3.2/bk_spark-guide/content/ch_orc-spark.html

Re: How do I create an ORC Hive table from Spark?

christopher_w_m — Thu, 19 Nov 2015 11:15:58 GMT

Thanks for the helpful links! Should I create the Hive table ahead of time or could I do everything within spark?

Re: How do I create an ORC Hive table from Spark?

vshukla — Thu, 19 Nov 2015 11:24:50 GMT

You can just write out the DF as ORC and the underlying directory will be created. LMK, if this doesn't work.

Re: How do I create an ORC Hive table from Spark?

bwilson — Thu, 19 Nov 2015 11:41:14 GMT

The way I have done this is to first register a temp table in Spark and then leverage the sql method of the HiveContext to create a new table in hive using the data from the temp table. For example if I have a dataframe df and HiveContext hc the general process is:

df.registerTempTable("my_temp_table")
hc.sql("CREATE TABLE new_table_name STORED AS ORC  AS SELECT * from my_temp_table")

Re: How do I create an ORC Hive table from Spark?

nsabharwal — Thu, 19 Nov 2015 11:52:21 GMT

@Kit Menke You can create within spark. Please see this https://hortonworks.com/hadoop-tutorial/apache-spark-1-5-1-technical-preview-with-hdp-2-3/

Re: How do I create an ORC Hive table from Spark?

christopher_w_m — Thu, 19 Nov 2015 23:39:30 GMT

Very interesting! I will try this out!

Re: How do I create an ORC Hive table from Spark?

christopher_w_m — Thu, 19 Nov 2015 23:41:25 GMT

Yep, the ORC directory is created but a Hive table is not.

Re: How do I create an ORC Hive table from Spark?

gbraccialli3 — Sat, 12 Dec 2015 09:33:30 GMT

@Kit Menke

If you want to access your table from hive, you have two options:

1- create table ahead and use df.write.fromat("orc")

2- use Brandon's suggestion here, register df as temp_table and do create table as select from temp_table.

See code examples here:

https://community.hortonworks.com/questions/6023/orgapachesparksparkexception-task-failed-while-wri.html#answer-6048

If you use saveAsTable function, it will create a table in hive metastore, but hive wont be able to query it. Only spark can use the table with this method.

Re: How do I create an ORC Hive table from Spark?

DivyaGehlot13 — Thu, 17 Dec 2015 09:56:32 GMT

@vshukla

I am also facing the same issue .. I saved the data in orc format from DF and created external hive table ..when I do show tables in hive context in spark it shows me the table but I couldnt see any table in my hive warehouse so when I query the hive external table. when I just create the hive table(no df no data processing ) using hivecontext table get created and able to query also .Unable to understand this strange behaviour . Am I misisng something ?

for ex : hiveContext.sql("CREATE TABLE IF NOT EXISTS TestTable (name STRING, age STRING)")

shows me the table in hive also.

Re: How do I create an ORC Hive table from Spark?

vshukla — Thu, 17 Dec 2015 10:21:31 GMT

Divya,

What is the user account when DF is used to create the external Hive table?

What is the user account when you try to see the table in Hive (& did you use HiveCli or Hive/Beeline or some ODBC tool?)

Re: How do I create an ORC Hive table from Spark?

DivyaGehlot13 — Fri, 18 Dec 2015 14:26:14 GMT

@vshukla I am logging in as hdfs user on HDP 2.3.2 sandbox

and using the same account to see tables in hive.Yes , I am using hive CLI and even browsed HDFS files through Ambari .Couldnt see any tables created.

Re: How do I create an ORC Hive table from Spark?

hoda_moradi2014 — Fri, 25 Mar 2016 22:45:48 GMT

@Brandon Wilson I tried your suggestion it creates the hive table but I get this error:

org.apache.hadoop.hive.ql.metadata.HiveException: Unable to alter table.

and it does not load data into my table. do you have any idea how to solve this?