Actually, the column with group_concat is mostly bad. Most rows in result set have similar value with different length. Here is result set I get where I highlighted rows with bad value for smallint_required_param_str column (result shows *327689*, but correct values are -32768 for user 999 and -32769 for user 1010):
+---------+-----------------------------+-----------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+ | user_id | smallint_required_param_int | smallint_required_param_str | all_values_from_map | date_id | +---------+-----------------------------+-----------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+ | 1000 | -32768 | -32768 | smallint_required_param:-32768,float_required_param:4.5,boolean_required_param:true,double_optional_param:55,float_optional_param:44,bigint_required_param:-9223372036854775808,string_optional_param:CBA,bigint_optional_param:9223372036854775807,smallint_optional_param:32767,int_optional_param:2147483647,double_required_param:5.5,string_required_param:abc,int_required_param:-2147483648,boolean_optional_param:false | 1992 | *| 999 | -32768 | 327689 | smallint_required_param:1,float_required_param:4.5,boolean_required_param:true,non_mapped_param:not mapped,double_optional_param:55,float_optional_param:44,bigint_required_param:3,string_optional_param:CBA,bigint_optional_param:33,smallint_optional_param:11,int_optional_param:22,double_required_param:5.5,string_required_param:abc,int_required_param:2,boolean_optional_param:false83648,boolean_optional_param:false | 1991 |* | 1001 | 1 | 1 | ,smallint_required_param:1,float_required_param:4.5,boolean_required_param:true,non_mapped_param:not mapped,double_optional_param:55,float_optional_param:44,bigint_required_param:3,string_optional_param:CBA,bigint_optional_param:33,smallint_optional_param:11,int_optional_param:22,double_required_param:5.5,string_required_param:abc,int_required_par | 1991 | | 1002 | 1 | 1 | float_required_param:4.5,smallint_required_param:1,smallint_optional_param:32768,boolean_required_param:true,double_required_param:5.5,string_required_param:,int_required_para | 1991 | | 1003 | 1 | 1 | smallint_required_param:1,float_required_param:4.5,boolean_required_param:true,non_mapped_param:not mapped,double_optional_param:55,float_optional_param:44,bigint_required_param:3,string_optional_param:CBA,bigint_optional_param:33,smallint_optional_param:11,int_optional_param:22,double_required_param:5.5,string_required_param:abc,int_required_param:2,boolean_optional_param:false | 1991 | | 1004 | NULL | NULL | ,float_required_param:4.5,smallint_required_param:1,smallint_optional_param:32768,boolean_required_param:true,double_required_param:5.5,string_re | 1991 | | 1005 | 1 | 1 | float_required_param:4.5,smallint_required_param:1,smallint_optional_param:32768,boolean_required_param:true,double_required_param:5.5,string_required_param:,int_required_param:2,bigint_req | 1991 | | 1006 | 1 | 1 | ,float_required_param:4.5,smallint_required_param:1,smallint_optional_param:32768,boolean_required_param:true,double_required_param:5.5,string_required_param:,int_required_param:2,bigi | 1991 | | 1007 | NULL | asd | float_required_param:4.5,smallint_required_param:1,smallint_optional_param:32768,boolean_required_param:true,double_required_param:5.5,string_required_param:,int_required_param: | 1991 | | 1008 | 1 | 1 | ,float_required_param:4.5,smallint_required_param:1,smallint_optional_param:32768,boolean_required_param:true,double_required_param:5.5,string_required_param:,int_required | 1991 | | 1009 | 1 | 1 | float_required_param:4.5,smallint_required_param:1,smallint_optional_param:32768,boolean_required_param:true,double_required_param:5.5,string_required_param:,int_required_p | 1991 | *| 1010 | NULL | 327689 | ,float_required_param:4.5,smallint_required_param:1,smallint_optional_param:32768,boolean_required_param:true,double_required_param:5.5,string_required_param:,int_required_para | 1991 |* | 1011 | NULL | 32768 | float_required_param:4.5,smallint_required_param:1,smallint_optional_param:32768,boolean_required_param:true,double_required_param:5.5,string_required_param:,int_required_param:2, | 1991 | | 1012 | 1 | 1 | ,float_required_param:4.5,smallint_required_param:1,smallint_optional_param:32768,boolean_required_param:true,double_required_param:5.5,string_required_param:,int_required_param:2,b | 1991 | | 1013 | 1 | 1 | float_required_param:4.5,smallint_required_param:1,smallint_optional_param:32768,boolean_required_param:true,double_required_param:5.5,string_required_param:,int_required_param:2,bigin | 1991 | | 1014 | 1 | 1 | ,float_required_param:4.5,smallint_required_param:1,smallint_optional_param:32768,boolean_required_param:true,double_required_param:5.5,string_required_param:,int_required_param:2,bigint_req | 1991 | | 1015 | 1 | 1 | float_required_param:4.5,smallint_required_param:1,smallint_optional_param:32768,boolean_required_param:true,double_required_param:5.5,string_required_param:,int_required_param:2,bigint_require | 1991 | | 1016 | 1 | 1 | ,float_required_param:4.5,smallint_required_param:1,smallint_optional_param:32768,boolean_required_param:true,double_required_param:5.5,string_required_param:,int_required_param:2,bigint_required_par | 1991 | | 1017 | 1 | 1 | float_required_param:4.5,smallint_required_param:1,smallint_optional_param:32768,boolean_required_param:true,double_required_param:5.5,string_required_param:,int_required_param:2,bigint_required_param:3 | 1991 | +---------+-----------------------------+-----------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+ Here is the result set from simple query which works fine (select * from event e, e.event_map): +---------+----------+---------------+---------------+-----------+---------+-------------------------+----------------------+ | user_id | event_id | event_type_id | ts_bigint | server_id | date_id | key | value | +---------+----------+---------------+---------------+-----------+---------+-------------------------+----------------------+ | 1000 | 3 | 4 | 1445394419000 | 1 | 1992 | smallint_required_param | -32768 | | 1000 | 3 | 4 | 1445394419000 | 1 | 1992 | float_required_param | 4.5 | | 1000 | 3 | 4 | 1445394419000 | 1 | 1992 | boolean_required_param | true | | 1000 | 3 | 4 | 1445394419000 | 1 | 1992 | double_optional_param | 55 | | 1000 | 3 | 4 | 1445394419000 | 1 | 1992 | float_optional_param | 44 | | 1000 | 3 | 4 | 1445394419000 | 1 | 1992 | bigint_required_param | -9223372036854775808 | | 1000 | 3 | 4 | 1445394419000 | 1 | 1992 | string_optional_param | CBA | | 1000 | 3 | 4 | 1445394419000 | 1 | 1992 | bigint_optional_param | 9223372036854775807 | | 1000 | 3 | 4 | 1445394419000 | 1 | 1992 | smallint_optional_param | 32767 | | 1000 | 3 | 4 | 1445394419000 | 1 | 1992 | int_optional_param | 2147483647 | | 1000 | 3 | 4 | 1445394419000 | 1 | 1992 | double_required_param | 5.5 | | 1000 | 3 | 4 | 1445394419000 | 1 | 1992 | string_required_param | abc | | 1000 | 3 | 4 | 1445394419000 | 1 | 1992 | int_required_param | -2147483648 | | 1000 | 3 | 4 | 1445394419000 | 1 | 1992 | boolean_optional_param | false | | 999 | 3 | 4 | 1445354419000 | 1 | 1991 | smallint_required_param | -32768 | | 999 | 3 | 4 | 1445354419000 | 1 | 1991 | float_required_param | 4.5 | | 999 | 3 | 4 | 1445354419000 | 1 | 1991 | boolean_required_param | true | | 999 | 3 | 4 | 1445354419000 | 1 | 1991 | double_optional_param | 55 | | 999 | 3 | 4 | 1445354419000 | 1 | 1991 | float_optional_param | 44 | | 999 | 3 | 4 | 1445354419000 | 1 | 1991 | bigint_required_param | -9223372036854775808 | | 999 | 3 | 4 | 1445354419000 | 1 | 1991 | string_optional_param | CBA | | 999 | 3 | 4 | 1445354419000 | 1 | 1991 | bigint_optional_param | 9223372036854775807 | | 999 | 3 | 4 | 1445354419000 | 1 | 1991 | smallint_optional_param | 32767 | | 999 | 3 | 4 | 1445354419000 | 1 | 1991 | int_optional_param | 2147483647 | | 999 | 3 | 4 | 1445354419000 | 1 | 1991 | double_required_param | 5.5 | | 999 | 3 | 4 | 1445354419000 | 1 | 1991 | string_required_param | abc | | 999 | 3 | 4 | 1445354419000 | 1 | 1991 | int_required_param | -2147483648 | | 999 | 3 | 4 | 1445354419000 | 1 | 1991 | boolean_optional_param | false | | 1001 | 3 | NULL | 1445354419000 | 1 | 1991 | smallint_required_param | 1 | | 1001 | 3 | NULL | 1445354419000 | 1 | 1991 | float_required_param | 4.5 | | 1001 | 3 | NULL | 1445354419000 | 1 | 1991 | boolean_required_param | true | | 1001 | 3 | NULL | 1445354419000 | 1 | 1991 | double_optional_param | 55 | | 1001 | 3 | NULL | 1445354419000 | 1 | 1991 | float_optional_param | 44 | | 1001 | 3 | NULL | 1445354419000 | 1 | 1991 | bigint_required_param | 3 | | 1001 | 3 | NULL | 1445354419000 | 1 | 1991 | string_optional_param | CBA | | 1001 | 3 | NULL | 1445354419000 | 1 | 1991 | bigint_optional_param | 33 | | 1001 | 3 | NULL | 1445354419000 | 1 | 1991 | smallint_optional_param | 11 | | 1001 | 3 | NULL | 1445354419000 | 1 | 1991 | int_optional_param | 22 | | 1001 | 3 | NULL | 1445354419000 | 1 | 1991 | double_required_param | 5.5 | | 1001 | 3 | NULL | 1445354419000 | 1 | 1991 | string_required_param | abc | | 1001 | 3 | NULL | 1445354419000 | 1 | 1991 | int_required_param | 2 | | 1001 | 3 | NULL | 1445354419000 | 1 | 1991 | boolean_optional_param | false | | 1002 | 3 | 4 | 1445354419000 | 1 | 1991 | smallint_required_param | 1 | | 1002 | 3 | 4 | 1445354419000 | 1 | 1991 | float_required_param | 4.5 | | 1002 | 3 | 4 | 1445354419000 | 1 | 1991 | boolean_required_param | true | | 1002 | 3 | 4 | 1445354419000 | 1 | 1991 | double_required_param | 5.5 | | 1002 | 3 | 4 | 1445354419000 | 1 | 1991 | string_required_param | abc | | 1002 | 3 | 4 | 1445354419000 | 1 | 1991 | int_required_param | 2 | | 1002 | 3 | 4 | 1445354419000 | 1 | 1991 | bigint_required_param | 3 | | 1003 | 3 | 4 | 1445354419000 | 1 | 1991 | smallint_required_param | 1 | | 1003 | 3 | 4 | 1445354419000 | 1 | 1991 | float_required_param | 4.5 | | 1003 | 3 | 4 | 1445354419000 | 1 | 1991 | boolean_required_param | true | | 1003 | 3 | 4 | 1445354419000 | 1 | 1991 | non_mapped_param | not mapped | | 1003 | 3 | 4 | 1445354419000 | 1 | 1991 | double_optional_param | 55 | | 1003 | 3 | 4 | 1445354419000 | 1 | 1991 | float_optional_param | 44 | | 1003 | 3 | 4 | 1445354419000 | 1 | 1991 | bigint_required_param | 3 | | 1003 | 3 | 4 | 1445354419000 | 1 | 1991 | string_optional_param | CBA | | 1003 | 3 | 4 | 1445354419000 | 1 | 1991 | bigint_optional_param | 33 | | 1003 | 3 | 4 | 1445354419000 | 1 | 1991 | smallint_optional_param | 11 | | 1003 | 3 | 4 | 1445354419000 | 1 | 1991 | int_optional_param | 22 | | 1003 | 3 | 4 | 1445354419000 | 1 | 1991 | double_required_param | 5.5 | | 1003 | 3 | 4 | 1445354419000 | 1 | 1991 | string_required_param | abc | | 1003 | 3 | 4 | 1445354419000 | 1 | 1991 | int_required_param | 2 | | 1003 | 3 | 4 | 1445354419000 | 1 | 1991 | boolean_optional_param | false | | 1004 | 3 | 4 | 1445354419000 | 1 | 1991 | float_required_param | 4.5 | | 1004 | 3 | 4 | 1445354419000 | 1 | 1991 | boolean_required_param | true | | 1004 | 3 | 4 | 1445354419000 | 1 | 1991 | double_required_param | 5.5 | | 1004 | 3 | 4 | 1445354419000 | 1 | 1991 | string_required_param | abc | | 1004 | 3 | 4 | 1445354419000 | 1 | 1991 | int_required_param | 2 | | 1004 | 3 | 4 | 1445354419000 | 1 | 1991 | bigint_required_param | 3 | | 1005 | 3 | 4 | 1445354419000 | 1 | 1991 | float_required_param | 4.5 | | 1005 | 3 | 4 | 1445354419000 | 1 | 1991 | smallint_required_param | 1 | | 1005 | 3 | 4 | 1445354419000 | 1 | 1991 | server_id | abc | | 1005 | 3 | 4 | 1445354419000 | 1 | 1991 | boolean_required_param | true | | 1005 | 3 | 4 | 1445354419000 | 1 | 1991 | double_required_param | 5.5 | | 1005 | 3 | 4 | 1445354419000 | 1 | 1991 | string_required_param | abc | | 1005 | 3 | 4 | 1445354419000 | 1 | 1991 | int_required_param | 2 | | 1005 | 3 | 4 | 1445354419000 | 1 | 1991 | bigint_required_param | 3 | | 1006 | 3 | 4 | 1445354419000 | 1 | 1991 | float_required_param | 4.5 | | 1006 | 3 | 4 | 1445354419000 | 1 | 1991 | smallint_required_param | 1 | | 1006 | 3 | 4 | 1445354419000 | 1 | 1991 | server_id | 12 | | 1006 | 3 | 4 | 1445354419000 | 1 | 1991 | boolean_required_param | true | | 1006 | 3 | 4 | 1445354419000 | 1 | 1991 | double_required_param | 5.5 | | 1006 | 3 | 4 | 1445354419000 | 1 | 1991 | string_required_param | abc | | 1006 | 3 | 4 | 1445354419000 | 1 | 1991 | int_required_param | 2 | | 1006 | 3 | 4 | 1445354419000 | 1 | 1991 | bigint_required_param | 3 | | 1007 | 3 | 4 | 1445354419000 | 1 | 1991 | smallint_required_param | asd | | 1007 | 3 | 4 | 1445354419000 | 1 | 1991 | float_required_param | 4.5 | | 1007 | 3 | 4 | 1445354419000 | 1 | 1991 | boolean_required_param | true | | 1007 | 3 | 4 | 1445354419000 | 1 | 1991 | double_required_param | 5.5 | | 1007 | 3 | 4 | 1445354419000 | 1 | 1991 | string_required_param | abc | | 1007 | 3 | 4 | 1445354419000 | 1 | 1991 | int_required_param | 2 | | 1007 | 3 | 4 | 1445354419000 | 1 | 1991 | bigint_required_param | 3 | | 1008 | 3 | 4 | 1445354419000 | 1 | 1991 | smallint_required_param | 1 | | 1008 | 3 | 4 | 1445354419000 | 1 | 1991 | float_required_param | 4.5 | | 1008 | 3 | 4 | 1445354419000 | 1 | 1991 | boolean_required_param | true | | 1008 | 3 | 4 | 1445354419000 | 1 | 1991 | double_required_param | 5.5 | | 1008 | 3 | 4 | 1445354419000 | 1 | 1991 | string_required_param | abc | | 1008 | 3 | 4 | 1445354419000 | 1 | 1991 | int_required_param | 2 | | 1008 | 3 | 4 | 1445354419000 | 1 | 1991 | bigint_required_param | 3 | | 1009 | 3 | 4 | 1445354419000 | 1 | 1991 | smallint_required_param | 1 | | 1009 | 3 | 4 | 1445354419000 | 1 | 1991 | float_required_param | 4.5 | | 1009 | 3 | 4 | 1445354419000 | 1 | 1991 | boolean_required_param | true | | 1009 | 3 | 4 | 1445354419000 | 1 | 1991 | double_required_param | 5.5 | | 1009 | 3 | 4 | 1445354419000 | 1 | 1991 | string_required_param | | | 1009 | 3 | 4 | 1445354419000 | 1 | 1991 | int_required_param | 2 | | 1009 | 3 | 4 | 1445354419000 | 1 | 1991 | bigint_required_param | 3 | | 1010 | 3 | 4 | 1445354419000 | 1 | 1991 | smallint_required_param | -32769 | | 1010 | 3 | 4 | 1445354419000 | 1 | 1991 | float_required_param | 4.5 | | 1010 | 3 | 4 | 1445354419000 | 1 | 1991 | boolean_required_param | true | | 1010 | 3 | 4 | 1445354419000 | 1 | 1991 | double_required_param | 5.5 | | 1010 | 3 | 4 | 1445354419000 | 1 | 1991 | string_required_param | asd | | 1010 | 3 | 4 | 1445354419000 | 1 | 1991 | int_required_param | 2 | | 1010 | 3 | 4 | 1445354419000 | 1 | 1991 | bigint_required_param | 3 | | 1011 | 3 | 4 | 1445354419000 | 1 | 1991 | smallint_required_param | 32768 | | 1011 | 3 | 4 | 1445354419000 | 1 | 1991 | float_required_param | 4.5 | | 1011 | 3 | 4 | 1445354419000 | 1 | 1991 | boolean_required_param | true | | 1011 | 3 | 4 | 1445354419000 | 1 | 1991 | double_required_param | 5.5 | | 1011 | 3 | 4 | 1445354419000 | 1 | 1991 | string_required_param | asd | | 1011 | 3 | 4 | 1445354419000 | 1 | 1991 | int_required_param | 2 | | 1011 | 3 | 4 | 1445354419000 | 1 | 1991 | bigint_required_param | 3 | | 1012 | 3 | 4 | 1445354419000 | 1 | 1991 | smallint_required_param | 1 | | 1012 | 3 | 4 | 1445354419000 | 1 | 1991 | float_required_param | 4.5 | | 1012 | 3 | 4 | 1445354419000 | 1 | 1991 | boolean_required_param | true | | 1012 | 3 | 4 | 1445354419000 | 1 | 1991 | double_required_param | 5.5 | | 1012 | 3 | 4 | 1445354419000 | 1 | 1991 | string_required_param | asd | | 1012 | 3 | 4 | 1445354419000 | 1 | 1991 | int_required_param | -2147483649 | | 1012 | 3 | 4 | 1445354419000 | 1 | 1991 | bigint_required_param | 3 | | 1013 | 3 | 4 | 1445354419000 | 1 | 1991 | smallint_required_param | 1 | | 1013 | 3 | 4 | 1445354419000 | 1 | 1991 | float_required_param | 4.5 | | 1013 | 3 | 4 | 1445354419000 | 1 | 1991 | boolean_required_param | true | | 1013 | 3 | 4 | 1445354419000 | 1 | 1991 | double_required_param | 5.5 | | 1013 | 3 | 4 | 1445354419000 | 1 | 1991 | string_required_param | asd | | 1013 | 3 | 4 | 1445354419000 | 1 | 1991 | int_required_param | 2147483648 | | 1013 | 3 | 4 | 1445354419000 | 1 | 1991 | bigint_required_param | 3 | | 1014 | 3 | 4 | 1445354419000 | 1 | 1991 | smallint_required_param | 1 | | 1014 | 3 | 4 | 1445354419000 | 1 | 1991 | float_required_param | 4.5 | | 1014 | 3 | 4 | 1445354419000 | 1 | 1991 | boolean_required_param | true | | 1014 | 3 | 4 | 1445354419000 | 1 | 1991 | double_required_param | 5.5 | | 1014 | 3 | 4 | 1445354419000 | 1 | 1991 | string_required_param | asd | | 1014 | 3 | 4 | 1445354419000 | 1 | 1991 | int_required_param | 2 | | 1014 | 3 | 4 | 1445354419000 | 1 | 1991 | bigint_required_param | -9223372036854775809 | | 1015 | 3 | 4 | 1445354419000 | 1 | 1991 | smallint_required_param | 1 | | 1015 | 3 | 4 | 1445354419000 | 1 | 1991 | float_required_param | 4.5 | | 1015 | 3 | 4 | 1445354419000 | 1 | 1991 | boolean_required_param | true | | 1015 | 3 | 4 | 1445354419000 | 1 | 1991 | double_required_param | 5.5 | | 1015 | 3 | 4 | 1445354419000 | 1 | 1991 | string_required_param | asd | | 1015 | 3 | 4 | 1445354419000 | 1 | 1991 | int_required_param | 2 | | 1015 | 3 | 4 | 1445354419000 | 1 | 1991 | bigint_required_param | 9223372036854775808 | | 1016 | 3 | 4 | 1445354419000 | 1 | 1991 | float_required_param | 4.5 | | 1016 | 3 | 4 | 1445354419000 | 1 | 1991 | smallint_required_param | 1 | | 1016 | 3 | 4 | 1445354419000 | 1 | 1991 | smallint_optional_param | -32769 | | 1016 | 3 | 4 | 1445354419000 | 1 | 1991 | boolean_required_param | true | | 1016 | 3 | 4 | 1445354419000 | 1 | 1991 | double_required_param | 5.5 | | 1016 | 3 | 4 | 1445354419000 | 1 | 1991 | string_required_param | | | 1016 | 3 | 4 | 1445354419000 | 1 | 1991 | int_required_param | 2 | | 1016 | 3 | 4 | 1445354419000 | 1 | 1991 | bigint_required_param | 3 | | 1017 | 3 | 4 | 1445354419000 | 1 | 1991 | float_required_param | 4.5 | | 1017 | 3 | 4 | 1445354419000 | 1 | 1991 | smallint_required_param | 1 | | 1017 | 3 | 4 | 1445354419000 | 1 | 1991 | smallint_optional_param | 32768 | | 1017 | 3 | 4 | 1445354419000 | 1 | 1991 | boolean_required_param | true | | 1017 | 3 | 4 | 1445354419000 | 1 | 1991 | double_required_param | 5.5 | | 1017 | 3 | 4 | 1445354419000 | 1 | 1991 | string_required_param | | | 1017 | 3 | 4 | 1445354419000 | 1 | 1991 | int_required_param | 2 | | 1017 | 3 | 4 | 1445354419000 | 1 | 1991 | bigint_required_param | 3 | +---------+----------+---------------+---------------+-----------+---------+-------------------------+----------------------+ *Dejan Prokić* | Data Engineer | Nordeus 2016-08-16 0:04 GMT+02:00 Tim Armstrong <[email protected]>: > I'm able to load and query the data but I don't see which results are > wrong just from eyeballing it - could you maybe highlight which returned > rows and values from the query are wrong? > > On Mon, Aug 15, 2016 at 3:54 AM, Dejan Prokić <[email protected]> wrote: > >> Hello, >> >> I have a problem to extract data from a column of type MAP<STRING, >> STRING>, impala returns me bad data. Please, tell me if there is a >> workaround to extract specific values from a map. This is version of impala >> I use: >> Server version: impalad version 2.3.0-cdh5.5.2 RELEASE (build >> cc1125f10419a7269366f7f950f57b24b07acd64) >> >> Here is an example which doesn't work well: >> >> -- this query returns correct result only for >> smallint_required_param_int, CASE without CAST and GROUP_CONCAT return bad >> data >> SELECT >> e.user_id, >> m.smallint_required_param_int, >> m.smallint_required_param_str, >> m.all_values_from_map, >> e.date_id >> FROM event e, >> (SELECT >> MAX(CASE WHEN key='smallint_required_param' THEN CAST(value AS SMALLINT) >> END) AS smallint_required_param_int, >> MAX(CASE WHEN key='smallint_required_param' THEN value END) AS >> smallint_required_param_str, >> GROUP_CONCAT(concat(key, ':', value), ',') as all_values_from_map >> FROM e.event_map) m >> WHERE e.event_id = 3; >> >> >> Here is how to prepare data from attachment: >> >> -- hive >> CREATE TABLE event_unpartitioned >> ROW FORMAT >> SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe' >> STORED AS >> INPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat' >> OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat >> ' >> TBLPROPERTIES ('avro.schema.literal'='{ >> "namespace": "testing.hive.avro.serde", >> "name": "events", >> "type": "record", >> "fields": [ >> {"name": "user_id", "type": ["null", "long"], "default": null}, >> {"name": "event_id", "type": ["null", "int"] , "default": null}, >> {"name": "event_type_id", "type": ["null", "int"] , "default": null}, >> {"name": "ts_bigint", "type": ["null", "long"], "default": null}, >> {"name": "event_map", "type": ["null", {"type": "map", "values": >> "string"}], "default": null}, >> {"name": "server_id", "type": ["null", "int"], "default": null}, >> {"name": "date_id", "type": ["null", "int"], "default": null} >> ] >> }'); >> >> -- impala >> CREATE TABLE event ( >> user_id BIGINT, >> event_id INT, >> event_type_id INT, >> ts_bigint BIGINT, >> event_map MAP<STRING, STRING>, >> server_id INT >> ) >> PARTITIONED BY (date_id INT) >> STORED AS PARQUET; >> >> -- bash >> hdfs dfs -put events.avro /user/hive/warehouse/event_unpartitioned/ >> >> -- hive >> set hive.exec.dynamic.partition.mode=nonstrict; >> insert into event partition (date_id) select * from event_unpartitioned; >> >> -- impala >> refresh event; >> >> select * from event e, e.event_map; -- this query returns correct results >> >> Thanks >> >> *Dejan Prokić* | Data Engineer | Nordeus >> > >
