I have a csv file data and want to import into hive table using spark. Below are the steps I am using for the same: 1. csv file data containing header: id,fname,lname,email,street,city,state,country,zipcode,cob_dt,mis_dt
1,James,Joy,james.joy@hotmail.com,"8th Street, E Avenue ",Anchorage,AK,US,99501,2017-12-26 22:36:34.0,2017-12-26 00:00:00.0 2. Created Hive Table as below: create external table customersPart(id int, fname varchar(32), lname varchar(32), email varchar(64), city varchar(32), state varchar(10), zipcode int, cob_dt timestamp)PARTITIONED BY (country string, AS_OF_dt string)row format delimitedfields terminated by ','STORED AS ORClocation '/user/chaithu/hive/retailPartitions/customers/'TBLPROPERTIES('hive.exec.dynamic.partition.mode'='nonstrict','hive.exec.dynamic.partition'='true','orc.compress'='SNAPPY'); 3. creating a spark Data Frame as below: > val conf = new SparkConf().setMaster("local").setAppName("LoadToHivePart")
> conf.set("spark.ui.port","4041")
> val sc=new SparkContext(conf)
> val sqlContext = new org.apache.spark.sql.SQLContext(sc)
> val hc = new org.apache.spark.sql.hive.HiveContext(sc)
> hc.setConf("hive.metastore.uris","thrift://127.0.0.1:9083")
> hc.setConf("hive.exec.dynamic.partition", "true")
> hc.setConf("hive.exec.dynamic.partition.mode", "nonstrict")
> val fileData = hc.read.format("com.databricks.spark.csv").option("header","true").option("inferschema","true").option("delimiter",",").load("/hdfspath/customers/csv/customers_20171226")
> fileData.registerTempTable("custData")
> customersData.withColumnRenamed("mis_dt","as_of_dt").write.format("orc").mode(SaveMode.Append).partitionBy("as_of_dt","country").saveAsTable("customerspart") 4. Data got loaded into hive table successfully and when try to display data it is showing as below: > select * from customersPart;
id
fname
lname
email
city
state
zipcode
cob_dt
country
as_of_date
1
James
Joy
james.joy@hotmail.com
8th Street, E Avenue
Anchorage
NULL
1970-01-02 09:08:21
2017-12-26 22:36:34
US
The issue I am facing here is: 1. The column zipcode with int datatype is displaying "NULL" values. 2. cob_dt displaying is not correct it is defaulted to 1970-01-02. 2. The Hive partition columns "country" and "as_of_date" are displaying in reverse order (results are swapped). Kindly help me to solve the above issue.
... View more
