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 Wed, 24 Aug 2016 12:47:16 GMT
Thanks Tim. My Cloudera Manager doesn't offer upgrade yet, until then I
will use hive or spark for this data.

*Dejan Prokić* | Data Engineer | Nordeus

2016-08-23 22:14 GMT+02:00 Tim Armstrong <tarmstrong@cloudera.com>:

> Looks like you're running into  https://issues.cloudera.org/br
> owse/IMPALA-3311. I was able to reproduce the weird results by undoing
> the fix on my local setup.
>
> We have the fix in Impala 2.6 but it hasn't been backported to Impala 2.3
> right now.
>
> On Tue, Aug 23, 2016 at 12:59 PM, Tim Armstrong <tarmstrong@cloudera.com>
> wrote:
>
>> Hi Dejan,
>>   Sorry for the slow response - have been a bit swamped here. I tested
>> this out on the latest development version of Impala and it looks like it's
>> returning the correct results:
>>
>> +---------+-----------------------------+-------------------
>> ----------+-------------------------------------------------
>> ------------------------------------------------------------
>> ------------------------------------------------------------
>> ------------------------------------------------------------
>> ------------------------------------------------------------
>> ------------------------------------------------------------
>> ------------------------------------------------------------
>> --------+---------+
>> | 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:-92233720368547
>> 75808,string_optional_param:CBA,bigint_optional_param:9223
>> 372036854775807,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                      | -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:-92233720368547
>> 75808,string_optional_param:CBA,bigint_optional_param:9223
>> 372036854775807,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
>> | 1991    |
>> | 1001    | 1                           | 1                           |
>> smallint_required_param:1,float_required_param:4.5,boolean_
>> required_param:true,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_requi
>> red_param:5.5,string_required_param:abc,int_required_param:
>> 2,boolean_optional_param:false
>>                                       | 1991    |
>> | 1002    | 1                           | 1                           |
>> smallint_required_param:1,float_required_param:4.5,boolean_
>> required_param:true,double_required_param:5.5,string_
>> required_param:abc,int_required_param:2,bigint_required_
>> param:3
>>
>>
>>                                                                     |
>> 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:5
>> 5,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_requi
>> red_param:5.5,string_required_param:abc,int_required_param:
>> 2,boolean_optional_param:false                                   |
>> 1991    |
>> | 1004    | NULL                        | NULL                        |
>> float_required_param:4.5,boolean_required_param:true,double_
>> required_param:5.5,string_required_param:abc,int_require
>> d_param:2,bigint_required_param:3
>>
>>
>>
>>                                                                 |
>> 1991    |
>> | 1005    | 1                           | 1                           |
>> float_required_param:4.5,smallint_required_param:1,server_
>> id:abc,boolean_required_param:true,double_required_param:5.
>> 5,string_required_param:abc,int_required_param:2,bigint_re
>> quired_param:3
>>
>>
>>                                                              | 1991    |
>> | 1006    | 1                           | 1                           |
>> float_required_param:4.5,smallint_required_param:1,server_
>> id:12,boolean_required_param:true,double_required_param:5.
>> 5,string_required_param:abc,int_required_param:2,bigint_
>> required_param:3
>>
>>
>>                                                                 |
>> 1991    |
>> | 1007    | NULL                        | asd                         |
>> smallint_required_param:asd,float_required_param:4.5,boolean
>> _required_param:true,double_required_param:5.5,string_
>> required_param:abc,int_required_param:2,bigint_required_
>> param:3
>>
>>
>>                                                                   |
>> 1991    |
>> | 1008    | 1                           | 1                           |
>> smallint_required_param:1,float_required_param:4.5,boolean_
>> required_param:true,double_required_param:5.5,string_
>> required_param:abc,int_required_param:2,bigint_required_
>> param:3
>>
>>
>>                                                                     |
>> 1991    |
>> | 1009    | 1                           | 1                           |
>> smallint_required_param:1,float_required_param:4.5,boolean_
>> required_param:true,double_required_param:5.5,string_
>> required_param:,int_required_param:2,bigint_required_param:
>> 3
>>
>>
>>                                                                  |
>> 1991    |
>> | 1010    | NULL                        | -32769                      |
>> smallint_required_param:-32769,float_required_param:4.5,
>> boolean_required_param:true,double_required_param:5.5,string
>> _required_param:asd,int_required_param:2,bigint_required_
>> param:3
>>
>>
>>                                                                | 1991
>> |
>> | 1011    | NULL                        | 32768                       |
>> smallint_required_param:32768,float_required_param:4.5,boole
>> an_required_param:true,double_required_param:5.5,string_
>> required_param:asd,int_required_param:2,bigint_required_
>> param:3
>>
>>
>>                                                                 |
>> 1991    |
>> | 1012    | 1                           | 1                           |
>> smallint_required_param:1,float_required_param:4.5,boolean_
>> required_param:true,double_required_param:5.5,string_
>> required_param:asd,int_required_param:-2147483649,bigint_
>> required_param:3
>>
>>
>>                                                                    |
>> 1991    |
>> | 1013    | 1                           | 1                           |
>> smallint_required_param:1,float_required_param:4.5,boolean_
>> required_param:true,double_required_param:5.5,string_
>> required_param:asd,int_required_param:2147483648,bigint_
>> required_param:3
>>
>>
>>                                                                     |
>> 1991    |
>> | 1014    | 1                           | 1                           |
>> smallint_required_param:1,float_required_param:4.5,boolean_
>> required_param:true,double_required_param:5.5,string_
>> required_param:asd,int_required_param:2,bigint_required_
>> param:-9223372036854775809
>>
>>
>>                                                                     |
>> 1991    |
>> | 1015    | 1                           | 1                           |
>> smallint_required_param:1,float_required_param:4.5,boolean_
>> required_param:true,double_required_param:5.5,string_
>> required_param:asd,int_required_param:2,bigint_required_
>> param:9223372036854775808
>>
>>
>>                                                                     |
>> 1991    |
>> | 1016    | 1                           | 1                           |
>> float_required_param:4.5,smallint_required_param:1,smallint_
>> optional_param:-32769,boolean_required_param:true,double_
>> required_param:5.5,string_required_param:,int_required_
>> param:2,bigint_required_param:3
>>
>>
>>                                                                 |
>> 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    |
>> +---------+-----------------------------+-------------------
>> ----------+-------------------------------------------------
>> ------------------------------------------------------------
>> ------------------------------------------------------------
>> ------------------------------------------------------------
>> ------------------------------------------------------------
>> ------------------------------------------------------------
>> ------------------------------------------------------------
>> --------+---------+
>>
>>
>> I'm trying to figure out what the root cause may have been so that I can
>> figure out what release it may have been fixed in.
>>
>> On Mon, Aug 22, 2016 at 3:02 PM, Dejan Prokić <dejanp@nordeus.com> wrote:
>>
>>> Is there any solution for this problem?
>>>
>>> Thanks
>>>
>>> 16.08.2016. 08.11, "Dejan Prokić" <dejanp@nordeus.com> је написао/ла:
>>>
>>>> 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,bool
>>>> ean_required_param:true,double_optional_param:55,float_optio
>>>> nal_param:44,bigint_required_param:-9223372036854775808,stri
>>>> ng_optional_param:CBA,bigint_optional_param:922337203685477
>>>> 5807,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:5
>>>> 5,float_optional_param:44,bigint_required_param:3,string_opt
>>>> ional_param:CBA,bigint_optional_param:33,smallint_optional_p
>>>> aram:11,int_optional_param:22,double_required_param:5.5,stri
>>>> ng_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_requ
>>>> ired_param:5.5,string_required_param:,int_required_para
>>>>
>>>>
>>>>
>>>>                                                         | 1991    |
>>>> | 1003    | 1                           | 1                           |
>>>> smallint_required_param:1,float_required_param:4.5,boolean_r
>>>> equired_param:true,non_mapped_param:not mapped,double_optional_param:5
>>>> 5,float_optional_param:44,bigint_required_param:3,string_opt
>>>> ional_param:CBA,bigint_optional_param:33,smallint_optional_p
>>>> aram:11,int_optional_param:22,double_required_param:5.5,stri
>>>> ng_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_req
>>>> uired_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_requ
>>>> ired_param:5.5,string_required_param:,int_required_param:2,b
>>>> igint_req
>>>>
>>>>
>>>>                                                         | 1991    |
>>>> | 1006    | 1                           | 1                           |
>>>> ,float_required_param:4.5,smallint_required_param:1,smallint
>>>> _optional_param:32768,boolean_required_param:true,double_req
>>>> uired_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_requ
>>>> ired_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_req
>>>> uired_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_requ
>>>> ired_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_requ
>>>> ired_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_req
>>>> uired_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_requ
>>>> ired_param:5.5,string_required_param:,int_required_param:2,b
>>>> igin
>>>>
>>>>
>>>>                                                         | 1991    |
>>>> | 1014    | 1                           | 1                           |
>>>> ,float_required_param:4.5,smallint_required_param:1,smallint
>>>> _optional_param:32768,boolean_required_param:true,double_req
>>>> uired_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_requ
>>>> ired_param:5.5,string_required_param:,int_required_param:2,b
>>>> igint_require
>>>>
>>>>
>>>>                                                         | 1991    |
>>>> | 1016    | 1                           | 1                           |
>>>> ,float_required_param:4.5,smallint_required_param:1,smallint
>>>> _optional_param:32768,boolean_required_param:true,double_req
>>>> uired_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_requ
>>>> ired_param:5.5,string_required_param:,int_required_param:2,b
>>>> igint_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