impala-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dejan Prokić <dej...@nordeus.com>
Subject Re: Bug with string in maps
Date Tue, 16 Aug 2016 06:11:08 GMT
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 <tarmstrong@cloudera.com>:

> 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ć <dejanp@nordeus.com> 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
>>
>
>

Mime
View raw message