Search code examples
hadoophivehqlimpala

Hive table creating for nested CSV data

i have below data how to create hive table for below data, the above data should be created by only 3 columns id,sal,name. 

1,1000,sdadada
2,2000,sadssaa
3,3000,dasasa,daaaas
Solution

  • Try it, it will resolve your problem.

    Here is my data file contains:

    vivekanand@sys:~/vivek/stack$ cat test.dat 
1,1000,sdadada
2,2000,sadssaa
3,3000,dasasa,daaaas

    Now your solution is here.

    hive> create table table_1(
    > id int,
    > sal int,
    > name string)
    > row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe' 
    > WITH SERDEPROPERTIES ('input.regex'='^(\\d+)\\,([^\\,]*)\\,(\\S+).*');
OK
Time taken: 0.331 seconds
hive> load data local inpath '/home/vivekanand/vivek/stack/test.dat' into table table_1;
Loading data to table default.table_1
OK
Time taken: 0.162 seconds
hive> select * from table_1;
OK
1   1000    sdadada
2   2000    sadssaa
3   3000    dasasa,daaaas
Time taken: 0.067 seconds, Fetched: 3 row(s)