impala-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tim Armstrong <tarmstr...@cloudera.com>
Subject Re: Bug with string in maps
Date Tue, 23 Aug 2016 20:14:23 GMT
Looks like you're running into  https://issues.cloudera.org/
browse/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:-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                      | -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 | 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_required_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: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,boolean_required_param:true,
> double_required_param:5.5,string_required_param:abc,int_
> required_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_
> required_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,
> boolean_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,
>>> string_optional_param:CBA,bigint_optional_param:92233720
>>> 36854775807,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_
>>> 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_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_
>>> 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_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,
>>> bigint_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,
>>> bigin
>>>
>>>
>>>                                                          | 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,
>>> bigint_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,
>>> 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