question Re: I have few column to make external table in hive from csv file but some column is empty what data type I need to define or leave it empty? in Archives of Support Questions (Read Only)

I have few column to make external table in hive from csv file but some column is empty what data type I need to define or leave it empty?

vijaysvnit2005 — Thu, 03 Mar 2016 22:13:18 GMT

I have trouble to create external table in hive

CREATE EXTERNAL TABLE IF NOT EXISTS DB.TableName( SOURCE_ID VARCHAR(30) , SOURCE_ID_TYPE VARCHAR(30) , SOURCE_NAME VARCHAR(30) , DEVICE_ID_1 VARCHAR(30)

) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE location 'hdfs:///user/hive';

column name SOURCE_NAME IS EMPTY(null)

I am failed to create the external table can you help me where I do mistake?

Re: I have few column to make external table in hive from csv file but some column is empty what data type I need to define or leave it empty?

jyadav — Thu, 03 Mar 2016 23:41:31 GMT

@mike pal

You probably need to convert empty values into NULL, add below option while creating table.

EDITED: Full DDL.

CREATE EXTERNAL TABLE IF NOT EXISTS DB.TableName(SOURCE_ID VARCHAR(30) ,SOURCE_ID_TYPE VARCHAR(30) ,SOURCE_NAME VARCHAR(30) ,DEVICE_ID_1 VARCHAR(30)) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE location 'hdfs:///user/hive' TBLPROPERTIES ('serialization.null.format'='');

Re: I have few column to make external table in hive from csv file but some column is empty what data type I need to define or leave it empty?

vijaysvnit2005 — Thu, 03 Mar 2016 23:41:32 GMT

Thanks to all

Re: I have few column to make external table in hive from csv file but some column is empty what data type I need to define or leave it empty?

vijaysvnit2005 — Thu, 03 Mar 2016 23:55:12 GMT

Can you write full create table what looks like if you can I really apreciated

Re: I have few column to make external table in hive from csv file but some column is empty what data type I need to define or leave it empty?

jyadav — Fri, 04 Mar 2016 02:25:18 GMT

I edited my answer with DDL, hope that will help. Thanks

Re: I have few column to make external table in hive from csv file but some column is empty what data type I need to define or leave it empty?

vijaysvnit2005 — Fri, 04 Mar 2016 05:43:33 GMT

Thanks Jitendra