Hive table creating for nested CSV data

Question

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

I have updated my suggestion check out below, hopefully, it resolves your problem. — , May 08 '18 at 07:19
Thank you vivek, but could you please let me know when we have to use RegexSerde — Sai Mammahi, May 08 '18 at 17:05
In this type of scenario only we use the RegexSerde, where the fixed delimiter is not a good solution. — , May 09 '18 at 05:46

score 0 · Accepted Answer · 2018-05-08T07:23:35.610

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)

if i use above schema, it doesnt print the second name of 3rd row. — Sai Mammahi, May 08 '18 at 05:41

Hive table creating for nested CSV data

1 Answers1