- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Float this Question for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
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?
- Labels:
-
Apache Hive
Created ‎03-03-2016 02:13 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
Created ‎03-03-2016 03:41 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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'='');
Created ‎03-03-2016 03:41 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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'='');
Created ‎03-03-2016 03:41 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks to all
Created ‎03-03-2016 03:55 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can you write full create table what looks like if you can I really apreciated
Created ‎03-03-2016 06:25 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I edited my answer with DDL, hope that will help. Thanks
Created ‎03-03-2016 09:43 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks Jitendra
