Today, I will discuss about to how to create Hive table using file in which fields length is different for all fields .To put in differently, it is neither fixed length file nor delimited file.Below is the example of file content
Definition of a file
FIELD1, length 2, value are 10 ,10
FIELD2, length 5 , value are 5,125
FIELD3, length 6 , value are 3,12
FIELD4, length 7, value are ABC,EF
FIELD5, length 4, value are 12,21
FIELD6, length 10, value are 15,10
In order to create table for such files, use the below syntax for the same.
CREATE EXTERNAL TABLE db_name.hive_table(
FIELD1 String ,
FIELD2 String ,
FIELD3 String ,
FIELD4 String ,
FIELD5 String ,
FIELD6 String
)
ROW FORMAT SERDE ‘org.apache.hadoop.hive.serde2.RegexSerDe’
WITH SERDEPROPERTIES
(
“input.regex” = “(.{2})(.{5})(.6})(.{7})(.{4})(.{10})”
)
STORED AS TEXTFILE
location ‘<Hadoop-file-path>’
;