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

Mime
View raw message