@Ravi Chinni When you load data into hive table the default Missing values are represented by the special value NULL. For Example: 1,abc,40000,maths$physics$chemistry,hyd 2,hadoop,3000,maths$physics,bang 3,mapreduce,4000,social,bang Find the Below Queries: hive> create table array_table(id int,name string,sal int,sub array<string>, city string) row format delimited fields terminated by ',' collection items terminated by '$'; OK Time taken: 0.06 seconds hive> load data local inpath '/Users/abc/sample.txt' into table array_table; Loading data to table 7may.array_table Table 7may.array_table stats: [numFiles=1, totalSize=106] OK Time taken: 0.146 seconds hive> select * from array_table; OK 1abc40000["maths","physics","chemistry"]hyd 2hadoop3000["maths","physics"]bang 3mapreduce4000["social"]bang Time taken: 0.121 seconds, Fetched: 3 row(s) hive> select * from array_table where sub[2] is not null; OK 1abc40000["maths","physics","chemistry"]hyd Time taken: 0.091 seconds, Fetched: 1 row(s) hive> select * from array_table where sub[1] is not null; OK 1abc40000["maths","physics","chemistry"]hyd 2hadoop3000["maths","physics"]bang Time taken: 0.089 seconds, Fetched: 2 row(s) Kindly reply if it is useful for your requirement. ... View more

@Ravi Chinni When you load data the default Missing values are represented by the special value NULL. For Example: Find the sample data: 1,abc,4000,maths$physics$chemistry,hyd 2,hadoop,3000,maths$physics,bang 3,mapreduce,4000,social,bang For the above data the table create statement: create table array_table(id int,name string,sal int,sub array<string>, city string) row format delimited fields terminated by ',' collection items terminated by '$'; Load statement: load data local inpath '/Users/abc/Downloads/arrayfile.txt' into table array_table; hive> select * from array_table; OK 1abc40000["maths","physics","chemistry"]hyd 2hadoop3000["maths","physics"]bang 3mapreduce4000["social"]bang Time taken: 0.086 seconds, Fetched: 3 row(s) hive> select * from array_table where sub[2] is not null; OK 1abc40000["maths","physics","chemistry"]hyd Time taken: 0.091 seconds, Fetched: 1 row(s) hive> select * from array_table where sub[1] is not null; OK 1abc40000["maths","physics","chemistry"]hyd 2hadoop3000["maths","physics"]bang Time taken: 0.089 seconds, Fetched: 2 row(s) Is this what you are looking for.? Can you kinldy respond back if it meets your requirement.? ... View more

When you load data into hive table the default Missing values are represented by the special value NULL. SO for simple or complex queries its not possible to insert null values into hive tables using INSERT INTO clause. ... View more

@Ravi Chinni If your sample data is like this: 1,abc,40000,maths$physics$chemistry,99@90@87,pf#500$epf#200,hyd$ap$500001 then you can use the below query. create table struct_tables(id int,name string,sal int,sub array<string>, pfstatus map<string,int>,address struct<city:string,state:string,pincode:bigint>) row format delimited fields terminated by ',' collection items terminated by '$' map keys terminated by '#'; In general FILELDS TERMINATED BY does not support multi-character delimiters for collections in Hive. Hence If your data is in this format 1,abc,40000,maths$physics$chemistry,99@90@87,pf#500$epf#200,hyd$ap$500001 then you need to replace either one of the delimiters with one (i.e either # with @ or @ with #) and then you can use the create statement. Also refer the below link for using multiple delimiters : https://cwiki.apache.org/confluence/display/Hive/MultiDelimitSerDe http://hadoopinrealworld.com/how-to-use-multi-character-delimiter-in-a-hive-table/ Hope this info helps you.. ... View more