You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@impala.apache.org by Dejan Prokić <de...@nordeus.com> on 2016/08/15 10:54:11 UTC
Bug with string in maps
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
Re: Bug with string in maps
Posted by Dejan Prokić <de...@nordeus.com>.
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 <ta...@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 <ta...@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ć <de...@nordeus.com> wrote:
>>
>>> Is there any solution for this problem?
>>>
>>> Thanks
>>>
>>> 16.08.2016. 08.11, "Dejan Prokić" <de...@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 <ta...@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ć <de...@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
>>>>>>
>>>>>
>>>>>
>>>>
>>
>
Re: Bug with string in maps
Posted by Tim Armstrong <ta...@cloudera.com>.
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 <ta...@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ć <de...@nordeus.com> wrote:
>
>> Is there any solution for this problem?
>>
>> Thanks
>>
>> 16.08.2016. 08.11, "Dejan Prokić" <de...@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 <ta...@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ć <de...@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
>>>>>
>>>>
>>>>
>>>
>
Re: Bug with string in maps
Posted by Tim Armstrong <ta...@cloudera.com>.
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ć <de...@nordeus.com> wrote:
> Is there any solution for this problem?
>
> Thanks
>
> 16.08.2016. 08.11, "Dejan Prokić" <de...@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 <ta...@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ć <de...@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
>>>>
>>>
>>>
>>
Re: Bug with string in maps
Posted by Dejan Prokić <de...@nordeus.com>.
Is there any solution for this problem?
Thanks
16.08.2016. 08.11, "Dejan Prokić" <de...@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:-9223372036854775808,string_
> optional_param:CBA,bigint_optional_param:9223372036854775807,smallint_
> optional_param:32767,int_optional_param:2147483647,
> double_required_param:5.5,string_required_param:abc,int_
> required_param:-2147483648,boolean_optional_param:false | 1992 |
> *| 999 | -32768 | 327689 |
> smallint_required_param:1,float_required_param:4.5,boolean_required_param:true,non_mapped_param:not
> mapped,double_optional_param:55,float_optional_param:44,bigint_required_param:3,string_optional_param:CBA,bigint_optional_param:33,smallint_optional_param:11,int_optional_param:22,double_required_param:5.5,string_required_param:abc,int_required_param:2,boolean_optional_param:false83648,boolean_optional_param:false
> | 1991 |*
> | 1001 | 1 | 1 |
> ,smallint_required_param:1,float_required_param:4.5,
> boolean_required_param:true,non_mapped_param:not
> mapped,double_optional_param:55,float_optional_param:44,
> bigint_required_param:3,string_optional_param:CBA,
> bigint_optional_param:33,smallint_optional_param:11,
> int_optional_param:22,double_required_param:5.5,string_
> required_param:abc,int_required_par
> | 1991 |
> | 1002 | 1 | 1 |
> float_required_param:4.5,smallint_required_param:1,
> smallint_optional_param:32768,boolean_required_param:true,
> double_required_param:5.5,string_required_param:,int_
> required_para
>
>
>
> | 1991 |
> | 1003 | 1 | 1 |
> smallint_required_param:1,float_required_param:4.5,
> boolean_required_param:true,non_mapped_param:not
> mapped,double_optional_param:55,float_optional_param:44,
> bigint_required_param:3,string_optional_param:CBA,
> bigint_optional_param:33,smallint_optional_param:11,
> int_optional_param:22,double_required_param:5.5,string_
> required_param:abc,int_required_param:2,boolean_
> optional_param:false | 1991 |
> | 1004 | NULL | NULL |
> ,float_required_param:4.5,smallint_required_param:1,
> smallint_optional_param:32768,boolean_required_param:true,
> double_required_param:5.5,string_re
>
>
>
> |
> 1991 |
> | 1005 | 1 | 1 |
> float_required_param:4.5,smallint_required_param:1,
> smallint_optional_param:32768,boolean_required_param:true,
> double_required_param:5.5,string_required_param:,int_
> required_param:2,bigint_req
>
>
>
> | 1991 |
> | 1006 | 1 | 1 |
> ,float_required_param:4.5,smallint_required_param:1,
> smallint_optional_param:32768,boolean_required_param:true,
> double_required_param:5.5,string_required_param:,int_
> required_param:2,bigi
>
>
> |
> 1991 |
> | 1007 | NULL | asd |
> float_required_param:4.5,smallint_required_param:1,
> smallint_optional_param:32768,boolean_required_param:true,
> double_required_param:5.5,string_required_param:,int_
> required_param:
>
>
>
> | 1991 |
> | 1008 | 1 | 1 |
> ,float_required_param:4.5,smallint_required_param:1,
> smallint_optional_param:32768,boolean_required_param:true,
> double_required_param:5.5,string_required_param:,int_
> required
>
>
> |
> 1991 |
> | 1009 | 1 | 1 |
> float_required_param:4.5,smallint_required_param:1,
> smallint_optional_param:32768,boolean_required_param:true,
> double_required_param:5.5,string_required_param:,int_
> required_p
>
>
>
> | 1991 |
> *| 1010 | NULL | 327689 |
> ,float_required_param:4.5,smallint_required_param:1,smallint_optional_param:32768,boolean_required_param:true,double_required_param:5.5,string_required_param:,int_required_para
> | 1991 |*
> | 1011 | NULL | 32768 |
> float_required_param:4.5,smallint_required_param:1,
> smallint_optional_param:32768,boolean_required_param:true,
> double_required_param:5.5,string_required_param:,int_
> required_param:2,
>
>
>
> | 1991 |
> | 1012 | 1 | 1 |
> ,float_required_param:4.5,smallint_required_param:1,
> smallint_optional_param:32768,boolean_required_param:true,
> double_required_param:5.5,string_required_param:,int_
> required_param:2,b
>
>
> |
> 1991 |
> | 1013 | 1 | 1 |
> float_required_param:4.5,smallint_required_param:1,
> smallint_optional_param:32768,boolean_required_param:true,
> double_required_param:5.5,string_required_param:,int_
> required_param:2,bigin
>
>
>
> | 1991 |
> | 1014 | 1 | 1 |
> ,float_required_param:4.5,smallint_required_param:1,
> smallint_optional_param:32768,boolean_required_param:true,
> double_required_param:5.5,string_required_param:,int_
> required_param:2,bigint_req
>
>
> |
> 1991 |
> | 1015 | 1 | 1 |
> float_required_param:4.5,smallint_required_param:1,
> smallint_optional_param:32768,boolean_required_param:true,
> double_required_param:5.5,string_required_param:,int_
> required_param:2,bigint_require
>
>
>
> | 1991 |
> | 1016 | 1 | 1 |
> ,float_required_param:4.5,smallint_required_param:1,
> smallint_optional_param:32768,boolean_required_param:true,
> double_required_param:5.5,string_required_param:,int_
> required_param:2,bigint_required_par
>
>
>
> | 1991 |
> | 1017 | 1 | 1 |
> float_required_param:4.5,smallint_required_param:1,
> smallint_optional_param:32768,boolean_required_param:true,
> double_required_param:5.5,string_required_param:,int_
> required_param:2,bigint_required_param:3
>
>
>
> | 1991 |
> +---------+-----------------------------+-------------------
> ----------+-------------------------------------------------
> ------------------------------------------------------------
> ------------------------------------------------------------
> ------------------------------------------------------------
> ------------------------------------------------------------
> ------------------------------------------------------------
> ------------------------------------------------------------
> --------+---------+
>
> Here is the result set from simple query which works fine (select * from
> event e, e.event_map):
>
> +---------+----------+---------------+---------------+------
> -----+---------+-------------------------+----------------------+
> | user_id | event_id | event_type_id | ts_bigint | server_id | date_id
> | key | value |
> +---------+----------+---------------+---------------+------
> -----+---------+-------------------------+----------------------+
> | 1000 | 3 | 4 | 1445394419000 | 1 | 1992
> | smallint_required_param | -32768 |
> | 1000 | 3 | 4 | 1445394419000 | 1 | 1992
> | float_required_param | 4.5 |
> | 1000 | 3 | 4 | 1445394419000 | 1 | 1992
> | boolean_required_param | true |
> | 1000 | 3 | 4 | 1445394419000 | 1 | 1992
> | double_optional_param | 55 |
> | 1000 | 3 | 4 | 1445394419000 | 1 | 1992
> | float_optional_param | 44 |
> | 1000 | 3 | 4 | 1445394419000 | 1 | 1992
> | bigint_required_param | -9223372036854775808 |
> | 1000 | 3 | 4 | 1445394419000 | 1 | 1992
> | string_optional_param | CBA |
> | 1000 | 3 | 4 | 1445394419000 | 1 | 1992
> | bigint_optional_param | 9223372036854775807 |
> | 1000 | 3 | 4 | 1445394419000 | 1 | 1992
> | smallint_optional_param | 32767 |
> | 1000 | 3 | 4 | 1445394419000 | 1 | 1992
> | int_optional_param | 2147483647 |
> | 1000 | 3 | 4 | 1445394419000 | 1 | 1992
> | double_required_param | 5.5 |
> | 1000 | 3 | 4 | 1445394419000 | 1 | 1992
> | string_required_param | abc |
> | 1000 | 3 | 4 | 1445394419000 | 1 | 1992
> | int_required_param | -2147483648 |
> | 1000 | 3 | 4 | 1445394419000 | 1 | 1992
> | boolean_optional_param | false |
> | 999 | 3 | 4 | 1445354419000 | 1 | 1991
> | smallint_required_param | -32768 |
> | 999 | 3 | 4 | 1445354419000 | 1 | 1991
> | float_required_param | 4.5 |
> | 999 | 3 | 4 | 1445354419000 | 1 | 1991
> | boolean_required_param | true |
> | 999 | 3 | 4 | 1445354419000 | 1 | 1991
> | double_optional_param | 55 |
> | 999 | 3 | 4 | 1445354419000 | 1 | 1991
> | float_optional_param | 44 |
> | 999 | 3 | 4 | 1445354419000 | 1 | 1991
> | bigint_required_param | -9223372036854775808 |
> | 999 | 3 | 4 | 1445354419000 | 1 | 1991
> | string_optional_param | CBA |
> | 999 | 3 | 4 | 1445354419000 | 1 | 1991
> | bigint_optional_param | 9223372036854775807 |
> | 999 | 3 | 4 | 1445354419000 | 1 | 1991
> | smallint_optional_param | 32767 |
> | 999 | 3 | 4 | 1445354419000 | 1 | 1991
> | int_optional_param | 2147483647 |
> | 999 | 3 | 4 | 1445354419000 | 1 | 1991
> | double_required_param | 5.5 |
> | 999 | 3 | 4 | 1445354419000 | 1 | 1991
> | string_required_param | abc |
> | 999 | 3 | 4 | 1445354419000 | 1 | 1991
> | int_required_param | -2147483648 |
> | 999 | 3 | 4 | 1445354419000 | 1 | 1991
> | boolean_optional_param | false |
> | 1001 | 3 | NULL | 1445354419000 | 1 | 1991
> | smallint_required_param | 1 |
> | 1001 | 3 | NULL | 1445354419000 | 1 | 1991
> | float_required_param | 4.5 |
> | 1001 | 3 | NULL | 1445354419000 | 1 | 1991
> | boolean_required_param | true |
> | 1001 | 3 | NULL | 1445354419000 | 1 | 1991
> | double_optional_param | 55 |
> | 1001 | 3 | NULL | 1445354419000 | 1 | 1991
> | float_optional_param | 44 |
> | 1001 | 3 | NULL | 1445354419000 | 1 | 1991
> | bigint_required_param | 3 |
> | 1001 | 3 | NULL | 1445354419000 | 1 | 1991
> | string_optional_param | CBA |
> | 1001 | 3 | NULL | 1445354419000 | 1 | 1991
> | bigint_optional_param | 33 |
> | 1001 | 3 | NULL | 1445354419000 | 1 | 1991
> | smallint_optional_param | 11 |
> | 1001 | 3 | NULL | 1445354419000 | 1 | 1991
> | int_optional_param | 22 |
> | 1001 | 3 | NULL | 1445354419000 | 1 | 1991
> | double_required_param | 5.5 |
> | 1001 | 3 | NULL | 1445354419000 | 1 | 1991
> | string_required_param | abc |
> | 1001 | 3 | NULL | 1445354419000 | 1 | 1991
> | int_required_param | 2 |
> | 1001 | 3 | NULL | 1445354419000 | 1 | 1991
> | boolean_optional_param | false |
> | 1002 | 3 | 4 | 1445354419000 | 1 | 1991
> | smallint_required_param | 1 |
> | 1002 | 3 | 4 | 1445354419000 | 1 | 1991
> | float_required_param | 4.5 |
> | 1002 | 3 | 4 | 1445354419000 | 1 | 1991
> | boolean_required_param | true |
> | 1002 | 3 | 4 | 1445354419000 | 1 | 1991
> | double_required_param | 5.5 |
> | 1002 | 3 | 4 | 1445354419000 | 1 | 1991
> | string_required_param | abc |
> | 1002 | 3 | 4 | 1445354419000 | 1 | 1991
> | int_required_param | 2 |
> | 1002 | 3 | 4 | 1445354419000 | 1 | 1991
> | bigint_required_param | 3 |
> | 1003 | 3 | 4 | 1445354419000 | 1 | 1991
> | smallint_required_param | 1 |
> | 1003 | 3 | 4 | 1445354419000 | 1 | 1991
> | float_required_param | 4.5 |
> | 1003 | 3 | 4 | 1445354419000 | 1 | 1991
> | boolean_required_param | true |
> | 1003 | 3 | 4 | 1445354419000 | 1 | 1991
> | non_mapped_param | not mapped |
> | 1003 | 3 | 4 | 1445354419000 | 1 | 1991
> | double_optional_param | 55 |
> | 1003 | 3 | 4 | 1445354419000 | 1 | 1991
> | float_optional_param | 44 |
> | 1003 | 3 | 4 | 1445354419000 | 1 | 1991
> | bigint_required_param | 3 |
> | 1003 | 3 | 4 | 1445354419000 | 1 | 1991
> | string_optional_param | CBA |
> | 1003 | 3 | 4 | 1445354419000 | 1 | 1991
> | bigint_optional_param | 33 |
> | 1003 | 3 | 4 | 1445354419000 | 1 | 1991
> | smallint_optional_param | 11 |
> | 1003 | 3 | 4 | 1445354419000 | 1 | 1991
> | int_optional_param | 22 |
> | 1003 | 3 | 4 | 1445354419000 | 1 | 1991
> | double_required_param | 5.5 |
> | 1003 | 3 | 4 | 1445354419000 | 1 | 1991
> | string_required_param | abc |
> | 1003 | 3 | 4 | 1445354419000 | 1 | 1991
> | int_required_param | 2 |
> | 1003 | 3 | 4 | 1445354419000 | 1 | 1991
> | boolean_optional_param | false |
> | 1004 | 3 | 4 | 1445354419000 | 1 | 1991
> | float_required_param | 4.5 |
> | 1004 | 3 | 4 | 1445354419000 | 1 | 1991
> | boolean_required_param | true |
> | 1004 | 3 | 4 | 1445354419000 | 1 | 1991
> | double_required_param | 5.5 |
> | 1004 | 3 | 4 | 1445354419000 | 1 | 1991
> | string_required_param | abc |
> | 1004 | 3 | 4 | 1445354419000 | 1 | 1991
> | int_required_param | 2 |
> | 1004 | 3 | 4 | 1445354419000 | 1 | 1991
> | bigint_required_param | 3 |
> | 1005 | 3 | 4 | 1445354419000 | 1 | 1991
> | float_required_param | 4.5 |
> | 1005 | 3 | 4 | 1445354419000 | 1 | 1991
> | smallint_required_param | 1 |
> | 1005 | 3 | 4 | 1445354419000 | 1 | 1991
> | server_id | abc |
> | 1005 | 3 | 4 | 1445354419000 | 1 | 1991
> | boolean_required_param | true |
> | 1005 | 3 | 4 | 1445354419000 | 1 | 1991
> | double_required_param | 5.5 |
> | 1005 | 3 | 4 | 1445354419000 | 1 | 1991
> | string_required_param | abc |
> | 1005 | 3 | 4 | 1445354419000 | 1 | 1991
> | int_required_param | 2 |
> | 1005 | 3 | 4 | 1445354419000 | 1 | 1991
> | bigint_required_param | 3 |
> | 1006 | 3 | 4 | 1445354419000 | 1 | 1991
> | float_required_param | 4.5 |
> | 1006 | 3 | 4 | 1445354419000 | 1 | 1991
> | smallint_required_param | 1 |
> | 1006 | 3 | 4 | 1445354419000 | 1 | 1991
> | server_id | 12 |
> | 1006 | 3 | 4 | 1445354419000 | 1 | 1991
> | boolean_required_param | true |
> | 1006 | 3 | 4 | 1445354419000 | 1 | 1991
> | double_required_param | 5.5 |
> | 1006 | 3 | 4 | 1445354419000 | 1 | 1991
> | string_required_param | abc |
> | 1006 | 3 | 4 | 1445354419000 | 1 | 1991
> | int_required_param | 2 |
> | 1006 | 3 | 4 | 1445354419000 | 1 | 1991
> | bigint_required_param | 3 |
> | 1007 | 3 | 4 | 1445354419000 | 1 | 1991
> | smallint_required_param | asd |
> | 1007 | 3 | 4 | 1445354419000 | 1 | 1991
> | float_required_param | 4.5 |
> | 1007 | 3 | 4 | 1445354419000 | 1 | 1991
> | boolean_required_param | true |
> | 1007 | 3 | 4 | 1445354419000 | 1 | 1991
> | double_required_param | 5.5 |
> | 1007 | 3 | 4 | 1445354419000 | 1 | 1991
> | string_required_param | abc |
> | 1007 | 3 | 4 | 1445354419000 | 1 | 1991
> | int_required_param | 2 |
> | 1007 | 3 | 4 | 1445354419000 | 1 | 1991
> | bigint_required_param | 3 |
> | 1008 | 3 | 4 | 1445354419000 | 1 | 1991
> | smallint_required_param | 1 |
> | 1008 | 3 | 4 | 1445354419000 | 1 | 1991
> | float_required_param | 4.5 |
> | 1008 | 3 | 4 | 1445354419000 | 1 | 1991
> | boolean_required_param | true |
> | 1008 | 3 | 4 | 1445354419000 | 1 | 1991
> | double_required_param | 5.5 |
> | 1008 | 3 | 4 | 1445354419000 | 1 | 1991
> | string_required_param | abc |
> | 1008 | 3 | 4 | 1445354419000 | 1 | 1991
> | int_required_param | 2 |
> | 1008 | 3 | 4 | 1445354419000 | 1 | 1991
> | bigint_required_param | 3 |
> | 1009 | 3 | 4 | 1445354419000 | 1 | 1991
> | smallint_required_param | 1 |
> | 1009 | 3 | 4 | 1445354419000 | 1 | 1991
> | float_required_param | 4.5 |
> | 1009 | 3 | 4 | 1445354419000 | 1 | 1991
> | boolean_required_param | true |
> | 1009 | 3 | 4 | 1445354419000 | 1 | 1991
> | double_required_param | 5.5 |
> | 1009 | 3 | 4 | 1445354419000 | 1 | 1991
> | string_required_param | |
> | 1009 | 3 | 4 | 1445354419000 | 1 | 1991
> | int_required_param | 2 |
> | 1009 | 3 | 4 | 1445354419000 | 1 | 1991
> | bigint_required_param | 3 |
> | 1010 | 3 | 4 | 1445354419000 | 1 | 1991
> | smallint_required_param | -32769 |
> | 1010 | 3 | 4 | 1445354419000 | 1 | 1991
> | float_required_param | 4.5 |
> | 1010 | 3 | 4 | 1445354419000 | 1 | 1991
> | boolean_required_param | true |
> | 1010 | 3 | 4 | 1445354419000 | 1 | 1991
> | double_required_param | 5.5 |
> | 1010 | 3 | 4 | 1445354419000 | 1 | 1991
> | string_required_param | asd |
> | 1010 | 3 | 4 | 1445354419000 | 1 | 1991
> | int_required_param | 2 |
> | 1010 | 3 | 4 | 1445354419000 | 1 | 1991
> | bigint_required_param | 3 |
> | 1011 | 3 | 4 | 1445354419000 | 1 | 1991
> | smallint_required_param | 32768 |
> | 1011 | 3 | 4 | 1445354419000 | 1 | 1991
> | float_required_param | 4.5 |
> | 1011 | 3 | 4 | 1445354419000 | 1 | 1991
> | boolean_required_param | true |
> | 1011 | 3 | 4 | 1445354419000 | 1 | 1991
> | double_required_param | 5.5 |
> | 1011 | 3 | 4 | 1445354419000 | 1 | 1991
> | string_required_param | asd |
> | 1011 | 3 | 4 | 1445354419000 | 1 | 1991
> | int_required_param | 2 |
> | 1011 | 3 | 4 | 1445354419000 | 1 | 1991
> | bigint_required_param | 3 |
> | 1012 | 3 | 4 | 1445354419000 | 1 | 1991
> | smallint_required_param | 1 |
> | 1012 | 3 | 4 | 1445354419000 | 1 | 1991
> | float_required_param | 4.5 |
> | 1012 | 3 | 4 | 1445354419000 | 1 | 1991
> | boolean_required_param | true |
> | 1012 | 3 | 4 | 1445354419000 | 1 | 1991
> | double_required_param | 5.5 |
> | 1012 | 3 | 4 | 1445354419000 | 1 | 1991
> | string_required_param | asd |
> | 1012 | 3 | 4 | 1445354419000 | 1 | 1991
> | int_required_param | -2147483649 |
> | 1012 | 3 | 4 | 1445354419000 | 1 | 1991
> | bigint_required_param | 3 |
> | 1013 | 3 | 4 | 1445354419000 | 1 | 1991
> | smallint_required_param | 1 |
> | 1013 | 3 | 4 | 1445354419000 | 1 | 1991
> | float_required_param | 4.5 |
> | 1013 | 3 | 4 | 1445354419000 | 1 | 1991
> | boolean_required_param | true |
> | 1013 | 3 | 4 | 1445354419000 | 1 | 1991
> | double_required_param | 5.5 |
> | 1013 | 3 | 4 | 1445354419000 | 1 | 1991
> | string_required_param | asd |
> | 1013 | 3 | 4 | 1445354419000 | 1 | 1991
> | int_required_param | 2147483648 |
> | 1013 | 3 | 4 | 1445354419000 | 1 | 1991
> | bigint_required_param | 3 |
> | 1014 | 3 | 4 | 1445354419000 | 1 | 1991
> | smallint_required_param | 1 |
> | 1014 | 3 | 4 | 1445354419000 | 1 | 1991
> | float_required_param | 4.5 |
> | 1014 | 3 | 4 | 1445354419000 | 1 | 1991
> | boolean_required_param | true |
> | 1014 | 3 | 4 | 1445354419000 | 1 | 1991
> | double_required_param | 5.5 |
> | 1014 | 3 | 4 | 1445354419000 | 1 | 1991
> | string_required_param | asd |
> | 1014 | 3 | 4 | 1445354419000 | 1 | 1991
> | int_required_param | 2 |
> | 1014 | 3 | 4 | 1445354419000 | 1 | 1991
> | bigint_required_param | -9223372036854775809 |
> | 1015 | 3 | 4 | 1445354419000 | 1 | 1991
> | smallint_required_param | 1 |
> | 1015 | 3 | 4 | 1445354419000 | 1 | 1991
> | float_required_param | 4.5 |
> | 1015 | 3 | 4 | 1445354419000 | 1 | 1991
> | boolean_required_param | true |
> | 1015 | 3 | 4 | 1445354419000 | 1 | 1991
> | double_required_param | 5.5 |
> | 1015 | 3 | 4 | 1445354419000 | 1 | 1991
> | string_required_param | asd |
> | 1015 | 3 | 4 | 1445354419000 | 1 | 1991
> | int_required_param | 2 |
> | 1015 | 3 | 4 | 1445354419000 | 1 | 1991
> | bigint_required_param | 9223372036854775808 |
> | 1016 | 3 | 4 | 1445354419000 | 1 | 1991
> | float_required_param | 4.5 |
> | 1016 | 3 | 4 | 1445354419000 | 1 | 1991
> | smallint_required_param | 1 |
> | 1016 | 3 | 4 | 1445354419000 | 1 | 1991
> | smallint_optional_param | -32769 |
> | 1016 | 3 | 4 | 1445354419000 | 1 | 1991
> | boolean_required_param | true |
> | 1016 | 3 | 4 | 1445354419000 | 1 | 1991
> | double_required_param | 5.5 |
> | 1016 | 3 | 4 | 1445354419000 | 1 | 1991
> | string_required_param | |
> | 1016 | 3 | 4 | 1445354419000 | 1 | 1991
> | int_required_param | 2 |
> | 1016 | 3 | 4 | 1445354419000 | 1 | 1991
> | bigint_required_param | 3 |
> | 1017 | 3 | 4 | 1445354419000 | 1 | 1991
> | float_required_param | 4.5 |
> | 1017 | 3 | 4 | 1445354419000 | 1 | 1991
> | smallint_required_param | 1 |
> | 1017 | 3 | 4 | 1445354419000 | 1 | 1991
> | smallint_optional_param | 32768 |
> | 1017 | 3 | 4 | 1445354419000 | 1 | 1991
> | boolean_required_param | true |
> | 1017 | 3 | 4 | 1445354419000 | 1 | 1991
> | double_required_param | 5.5 |
> | 1017 | 3 | 4 | 1445354419000 | 1 | 1991
> | string_required_param | |
> | 1017 | 3 | 4 | 1445354419000 | 1 | 1991
> | int_required_param | 2 |
> | 1017 | 3 | 4 | 1445354419000 | 1 | 1991
> | bigint_required_param | 3 |
> +---------+----------+---------------+---------------+------
> -----+---------+-------------------------+----------------------+
>
>
> *Dejan Prokić* | Data Engineer | Nordeus
>
> 2016-08-16 0:04 GMT+02:00 Tim Armstrong <ta...@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ć <de...@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
>>>
>>
>>
>
Re: Bug with string in maps
Posted by Dejan Prokić <de...@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:-9223372036854775808,string_optional_param:CBA,bigint_optional_param:9223372036854775807,smallint_optional_param:32767,int_optional_param:2147483647,double_required_param:5.5,string_required_param:abc,int_required_param:-2147483648,boolean_optional_param:false
| 1992 |
*| 999 | -32768 | 327689 |
smallint_required_param:1,float_required_param:4.5,boolean_required_param:true,non_mapped_param:not
mapped,double_optional_param:55,float_optional_param:44,bigint_required_param:3,string_optional_param:CBA,bigint_optional_param:33,smallint_optional_param:11,int_optional_param:22,double_required_param:5.5,string_required_param:abc,int_required_param:2,boolean_optional_param:false83648,boolean_optional_param:false
| 1991 |*
| 1001 | 1 | 1 |
,smallint_required_param:1,float_required_param:4.5,boolean_required_param:true,non_mapped_param:not
mapped,double_optional_param:55,float_optional_param:44,bigint_required_param:3,string_optional_param:CBA,bigint_optional_param:33,smallint_optional_param:11,int_optional_param:22,double_required_param:5.5,string_required_param:abc,int_required_par
| 1991 |
| 1002 | 1 | 1 |
float_required_param:4.5,smallint_required_param:1,smallint_optional_param:32768,boolean_required_param:true,double_required_param:5.5,string_required_param:,int_required_para
| 1991 |
| 1003 | 1 | 1 |
smallint_required_param:1,float_required_param:4.5,boolean_required_param:true,non_mapped_param:not
mapped,double_optional_param:55,float_optional_param:44,bigint_required_param:3,string_optional_param:CBA,bigint_optional_param:33,smallint_optional_param:11,int_optional_param:22,double_required_param:5.5,string_required_param:abc,int_required_param:2,boolean_optional_param:false
| 1991 |
| 1004 | NULL | NULL |
,float_required_param:4.5,smallint_required_param:1,smallint_optional_param:32768,boolean_required_param:true,double_required_param:5.5,string_re
| 1991 |
| 1005 | 1 | 1 |
float_required_param:4.5,smallint_required_param:1,smallint_optional_param:32768,boolean_required_param:true,double_required_param:5.5,string_required_param:,int_required_param:2,bigint_req
| 1991 |
| 1006 | 1 | 1 |
,float_required_param:4.5,smallint_required_param:1,smallint_optional_param:32768,boolean_required_param:true,double_required_param:5.5,string_required_param:,int_required_param:2,bigi
| 1991 |
| 1007 | NULL | asd |
float_required_param:4.5,smallint_required_param:1,smallint_optional_param:32768,boolean_required_param:true,double_required_param:5.5,string_required_param:,int_required_param:
| 1991 |
| 1008 | 1 | 1 |
,float_required_param:4.5,smallint_required_param:1,smallint_optional_param:32768,boolean_required_param:true,double_required_param:5.5,string_required_param:,int_required
| 1991 |
| 1009 | 1 | 1 |
float_required_param:4.5,smallint_required_param:1,smallint_optional_param:32768,boolean_required_param:true,double_required_param:5.5,string_required_param:,int_required_p
| 1991 |
*| 1010 | NULL | 327689 |
,float_required_param:4.5,smallint_required_param:1,smallint_optional_param:32768,boolean_required_param:true,double_required_param:5.5,string_required_param:,int_required_para
| 1991 |*
| 1011 | NULL | 32768 |
float_required_param:4.5,smallint_required_param:1,smallint_optional_param:32768,boolean_required_param:true,double_required_param:5.5,string_required_param:,int_required_param:2,
| 1991 |
| 1012 | 1 | 1 |
,float_required_param:4.5,smallint_required_param:1,smallint_optional_param:32768,boolean_required_param:true,double_required_param:5.5,string_required_param:,int_required_param:2,b
| 1991 |
| 1013 | 1 | 1 |
float_required_param:4.5,smallint_required_param:1,smallint_optional_param:32768,boolean_required_param:true,double_required_param:5.5,string_required_param:,int_required_param:2,bigin
| 1991 |
| 1014 | 1 | 1 |
,float_required_param:4.5,smallint_required_param:1,smallint_optional_param:32768,boolean_required_param:true,double_required_param:5.5,string_required_param:,int_required_param:2,bigint_req
| 1991 |
| 1015 | 1 | 1 |
float_required_param:4.5,smallint_required_param:1,smallint_optional_param:32768,boolean_required_param:true,double_required_param:5.5,string_required_param:,int_required_param:2,bigint_require
| 1991 |
| 1016 | 1 | 1 |
,float_required_param:4.5,smallint_required_param:1,smallint_optional_param:32768,boolean_required_param:true,double_required_param:5.5,string_required_param:,int_required_param:2,bigint_required_par
| 1991 |
| 1017 | 1 | 1 |
float_required_param:4.5,smallint_required_param:1,smallint_optional_param:32768,boolean_required_param:true,double_required_param:5.5,string_required_param:,int_required_param:2,bigint_required_param:3
| 1991 |
+---------+-----------------------------+-----------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+
Here is the result set from simple query which works fine (select * from
event e, e.event_map):
+---------+----------+---------------+---------------+-----------+---------+-------------------------+----------------------+
| user_id | event_id | event_type_id | ts_bigint | server_id | date_id
| key | value |
+---------+----------+---------------+---------------+-----------+---------+-------------------------+----------------------+
| 1000 | 3 | 4 | 1445394419000 | 1 | 1992
| smallint_required_param | -32768 |
| 1000 | 3 | 4 | 1445394419000 | 1 | 1992
| float_required_param | 4.5 |
| 1000 | 3 | 4 | 1445394419000 | 1 | 1992
| boolean_required_param | true |
| 1000 | 3 | 4 | 1445394419000 | 1 | 1992
| double_optional_param | 55 |
| 1000 | 3 | 4 | 1445394419000 | 1 | 1992
| float_optional_param | 44 |
| 1000 | 3 | 4 | 1445394419000 | 1 | 1992
| bigint_required_param | -9223372036854775808 |
| 1000 | 3 | 4 | 1445394419000 | 1 | 1992
| string_optional_param | CBA |
| 1000 | 3 | 4 | 1445394419000 | 1 | 1992
| bigint_optional_param | 9223372036854775807 |
| 1000 | 3 | 4 | 1445394419000 | 1 | 1992
| smallint_optional_param | 32767 |
| 1000 | 3 | 4 | 1445394419000 | 1 | 1992
| int_optional_param | 2147483647 |
| 1000 | 3 | 4 | 1445394419000 | 1 | 1992
| double_required_param | 5.5 |
| 1000 | 3 | 4 | 1445394419000 | 1 | 1992
| string_required_param | abc |
| 1000 | 3 | 4 | 1445394419000 | 1 | 1992
| int_required_param | -2147483648 |
| 1000 | 3 | 4 | 1445394419000 | 1 | 1992
| boolean_optional_param | false |
| 999 | 3 | 4 | 1445354419000 | 1 | 1991
| smallint_required_param | -32768 |
| 999 | 3 | 4 | 1445354419000 | 1 | 1991
| float_required_param | 4.5 |
| 999 | 3 | 4 | 1445354419000 | 1 | 1991
| boolean_required_param | true |
| 999 | 3 | 4 | 1445354419000 | 1 | 1991
| double_optional_param | 55 |
| 999 | 3 | 4 | 1445354419000 | 1 | 1991
| float_optional_param | 44 |
| 999 | 3 | 4 | 1445354419000 | 1 | 1991
| bigint_required_param | -9223372036854775808 |
| 999 | 3 | 4 | 1445354419000 | 1 | 1991
| string_optional_param | CBA |
| 999 | 3 | 4 | 1445354419000 | 1 | 1991
| bigint_optional_param | 9223372036854775807 |
| 999 | 3 | 4 | 1445354419000 | 1 | 1991
| smallint_optional_param | 32767 |
| 999 | 3 | 4 | 1445354419000 | 1 | 1991
| int_optional_param | 2147483647 |
| 999 | 3 | 4 | 1445354419000 | 1 | 1991
| double_required_param | 5.5 |
| 999 | 3 | 4 | 1445354419000 | 1 | 1991
| string_required_param | abc |
| 999 | 3 | 4 | 1445354419000 | 1 | 1991
| int_required_param | -2147483648 |
| 999 | 3 | 4 | 1445354419000 | 1 | 1991
| boolean_optional_param | false |
| 1001 | 3 | NULL | 1445354419000 | 1 | 1991
| smallint_required_param | 1 |
| 1001 | 3 | NULL | 1445354419000 | 1 | 1991
| float_required_param | 4.5 |
| 1001 | 3 | NULL | 1445354419000 | 1 | 1991
| boolean_required_param | true |
| 1001 | 3 | NULL | 1445354419000 | 1 | 1991
| double_optional_param | 55 |
| 1001 | 3 | NULL | 1445354419000 | 1 | 1991
| float_optional_param | 44 |
| 1001 | 3 | NULL | 1445354419000 | 1 | 1991
| bigint_required_param | 3 |
| 1001 | 3 | NULL | 1445354419000 | 1 | 1991
| string_optional_param | CBA |
| 1001 | 3 | NULL | 1445354419000 | 1 | 1991
| bigint_optional_param | 33 |
| 1001 | 3 | NULL | 1445354419000 | 1 | 1991
| smallint_optional_param | 11 |
| 1001 | 3 | NULL | 1445354419000 | 1 | 1991
| int_optional_param | 22 |
| 1001 | 3 | NULL | 1445354419000 | 1 | 1991
| double_required_param | 5.5 |
| 1001 | 3 | NULL | 1445354419000 | 1 | 1991
| string_required_param | abc |
| 1001 | 3 | NULL | 1445354419000 | 1 | 1991
| int_required_param | 2 |
| 1001 | 3 | NULL | 1445354419000 | 1 | 1991
| boolean_optional_param | false |
| 1002 | 3 | 4 | 1445354419000 | 1 | 1991
| smallint_required_param | 1 |
| 1002 | 3 | 4 | 1445354419000 | 1 | 1991
| float_required_param | 4.5 |
| 1002 | 3 | 4 | 1445354419000 | 1 | 1991
| boolean_required_param | true |
| 1002 | 3 | 4 | 1445354419000 | 1 | 1991
| double_required_param | 5.5 |
| 1002 | 3 | 4 | 1445354419000 | 1 | 1991
| string_required_param | abc |
| 1002 | 3 | 4 | 1445354419000 | 1 | 1991
| int_required_param | 2 |
| 1002 | 3 | 4 | 1445354419000 | 1 | 1991
| bigint_required_param | 3 |
| 1003 | 3 | 4 | 1445354419000 | 1 | 1991
| smallint_required_param | 1 |
| 1003 | 3 | 4 | 1445354419000 | 1 | 1991
| float_required_param | 4.5 |
| 1003 | 3 | 4 | 1445354419000 | 1 | 1991
| boolean_required_param | true |
| 1003 | 3 | 4 | 1445354419000 | 1 | 1991
| non_mapped_param | not mapped |
| 1003 | 3 | 4 | 1445354419000 | 1 | 1991
| double_optional_param | 55 |
| 1003 | 3 | 4 | 1445354419000 | 1 | 1991
| float_optional_param | 44 |
| 1003 | 3 | 4 | 1445354419000 | 1 | 1991
| bigint_required_param | 3 |
| 1003 | 3 | 4 | 1445354419000 | 1 | 1991
| string_optional_param | CBA |
| 1003 | 3 | 4 | 1445354419000 | 1 | 1991
| bigint_optional_param | 33 |
| 1003 | 3 | 4 | 1445354419000 | 1 | 1991
| smallint_optional_param | 11 |
| 1003 | 3 | 4 | 1445354419000 | 1 | 1991
| int_optional_param | 22 |
| 1003 | 3 | 4 | 1445354419000 | 1 | 1991
| double_required_param | 5.5 |
| 1003 | 3 | 4 | 1445354419000 | 1 | 1991
| string_required_param | abc |
| 1003 | 3 | 4 | 1445354419000 | 1 | 1991
| int_required_param | 2 |
| 1003 | 3 | 4 | 1445354419000 | 1 | 1991
| boolean_optional_param | false |
| 1004 | 3 | 4 | 1445354419000 | 1 | 1991
| float_required_param | 4.5 |
| 1004 | 3 | 4 | 1445354419000 | 1 | 1991
| boolean_required_param | true |
| 1004 | 3 | 4 | 1445354419000 | 1 | 1991
| double_required_param | 5.5 |
| 1004 | 3 | 4 | 1445354419000 | 1 | 1991
| string_required_param | abc |
| 1004 | 3 | 4 | 1445354419000 | 1 | 1991
| int_required_param | 2 |
| 1004 | 3 | 4 | 1445354419000 | 1 | 1991
| bigint_required_param | 3 |
| 1005 | 3 | 4 | 1445354419000 | 1 | 1991
| float_required_param | 4.5 |
| 1005 | 3 | 4 | 1445354419000 | 1 | 1991
| smallint_required_param | 1 |
| 1005 | 3 | 4 | 1445354419000 | 1 | 1991
| server_id | abc |
| 1005 | 3 | 4 | 1445354419000 | 1 | 1991
| boolean_required_param | true |
| 1005 | 3 | 4 | 1445354419000 | 1 | 1991
| double_required_param | 5.5 |
| 1005 | 3 | 4 | 1445354419000 | 1 | 1991
| string_required_param | abc |
| 1005 | 3 | 4 | 1445354419000 | 1 | 1991
| int_required_param | 2 |
| 1005 | 3 | 4 | 1445354419000 | 1 | 1991
| bigint_required_param | 3 |
| 1006 | 3 | 4 | 1445354419000 | 1 | 1991
| float_required_param | 4.5 |
| 1006 | 3 | 4 | 1445354419000 | 1 | 1991
| smallint_required_param | 1 |
| 1006 | 3 | 4 | 1445354419000 | 1 | 1991
| server_id | 12 |
| 1006 | 3 | 4 | 1445354419000 | 1 | 1991
| boolean_required_param | true |
| 1006 | 3 | 4 | 1445354419000 | 1 | 1991
| double_required_param | 5.5 |
| 1006 | 3 | 4 | 1445354419000 | 1 | 1991
| string_required_param | abc |
| 1006 | 3 | 4 | 1445354419000 | 1 | 1991
| int_required_param | 2 |
| 1006 | 3 | 4 | 1445354419000 | 1 | 1991
| bigint_required_param | 3 |
| 1007 | 3 | 4 | 1445354419000 | 1 | 1991
| smallint_required_param | asd |
| 1007 | 3 | 4 | 1445354419000 | 1 | 1991
| float_required_param | 4.5 |
| 1007 | 3 | 4 | 1445354419000 | 1 | 1991
| boolean_required_param | true |
| 1007 | 3 | 4 | 1445354419000 | 1 | 1991
| double_required_param | 5.5 |
| 1007 | 3 | 4 | 1445354419000 | 1 | 1991
| string_required_param | abc |
| 1007 | 3 | 4 | 1445354419000 | 1 | 1991
| int_required_param | 2 |
| 1007 | 3 | 4 | 1445354419000 | 1 | 1991
| bigint_required_param | 3 |
| 1008 | 3 | 4 | 1445354419000 | 1 | 1991
| smallint_required_param | 1 |
| 1008 | 3 | 4 | 1445354419000 | 1 | 1991
| float_required_param | 4.5 |
| 1008 | 3 | 4 | 1445354419000 | 1 | 1991
| boolean_required_param | true |
| 1008 | 3 | 4 | 1445354419000 | 1 | 1991
| double_required_param | 5.5 |
| 1008 | 3 | 4 | 1445354419000 | 1 | 1991
| string_required_param | abc |
| 1008 | 3 | 4 | 1445354419000 | 1 | 1991
| int_required_param | 2 |
| 1008 | 3 | 4 | 1445354419000 | 1 | 1991
| bigint_required_param | 3 |
| 1009 | 3 | 4 | 1445354419000 | 1 | 1991
| smallint_required_param | 1 |
| 1009 | 3 | 4 | 1445354419000 | 1 | 1991
| float_required_param | 4.5 |
| 1009 | 3 | 4 | 1445354419000 | 1 | 1991
| boolean_required_param | true |
| 1009 | 3 | 4 | 1445354419000 | 1 | 1991
| double_required_param | 5.5 |
| 1009 | 3 | 4 | 1445354419000 | 1 | 1991
| string_required_param | |
| 1009 | 3 | 4 | 1445354419000 | 1 | 1991
| int_required_param | 2 |
| 1009 | 3 | 4 | 1445354419000 | 1 | 1991
| bigint_required_param | 3 |
| 1010 | 3 | 4 | 1445354419000 | 1 | 1991
| smallint_required_param | -32769 |
| 1010 | 3 | 4 | 1445354419000 | 1 | 1991
| float_required_param | 4.5 |
| 1010 | 3 | 4 | 1445354419000 | 1 | 1991
| boolean_required_param | true |
| 1010 | 3 | 4 | 1445354419000 | 1 | 1991
| double_required_param | 5.5 |
| 1010 | 3 | 4 | 1445354419000 | 1 | 1991
| string_required_param | asd |
| 1010 | 3 | 4 | 1445354419000 | 1 | 1991
| int_required_param | 2 |
| 1010 | 3 | 4 | 1445354419000 | 1 | 1991
| bigint_required_param | 3 |
| 1011 | 3 | 4 | 1445354419000 | 1 | 1991
| smallint_required_param | 32768 |
| 1011 | 3 | 4 | 1445354419000 | 1 | 1991
| float_required_param | 4.5 |
| 1011 | 3 | 4 | 1445354419000 | 1 | 1991
| boolean_required_param | true |
| 1011 | 3 | 4 | 1445354419000 | 1 | 1991
| double_required_param | 5.5 |
| 1011 | 3 | 4 | 1445354419000 | 1 | 1991
| string_required_param | asd |
| 1011 | 3 | 4 | 1445354419000 | 1 | 1991
| int_required_param | 2 |
| 1011 | 3 | 4 | 1445354419000 | 1 | 1991
| bigint_required_param | 3 |
| 1012 | 3 | 4 | 1445354419000 | 1 | 1991
| smallint_required_param | 1 |
| 1012 | 3 | 4 | 1445354419000 | 1 | 1991
| float_required_param | 4.5 |
| 1012 | 3 | 4 | 1445354419000 | 1 | 1991
| boolean_required_param | true |
| 1012 | 3 | 4 | 1445354419000 | 1 | 1991
| double_required_param | 5.5 |
| 1012 | 3 | 4 | 1445354419000 | 1 | 1991
| string_required_param | asd |
| 1012 | 3 | 4 | 1445354419000 | 1 | 1991
| int_required_param | -2147483649 |
| 1012 | 3 | 4 | 1445354419000 | 1 | 1991
| bigint_required_param | 3 |
| 1013 | 3 | 4 | 1445354419000 | 1 | 1991
| smallint_required_param | 1 |
| 1013 | 3 | 4 | 1445354419000 | 1 | 1991
| float_required_param | 4.5 |
| 1013 | 3 | 4 | 1445354419000 | 1 | 1991
| boolean_required_param | true |
| 1013 | 3 | 4 | 1445354419000 | 1 | 1991
| double_required_param | 5.5 |
| 1013 | 3 | 4 | 1445354419000 | 1 | 1991
| string_required_param | asd |
| 1013 | 3 | 4 | 1445354419000 | 1 | 1991
| int_required_param | 2147483648 |
| 1013 | 3 | 4 | 1445354419000 | 1 | 1991
| bigint_required_param | 3 |
| 1014 | 3 | 4 | 1445354419000 | 1 | 1991
| smallint_required_param | 1 |
| 1014 | 3 | 4 | 1445354419000 | 1 | 1991
| float_required_param | 4.5 |
| 1014 | 3 | 4 | 1445354419000 | 1 | 1991
| boolean_required_param | true |
| 1014 | 3 | 4 | 1445354419000 | 1 | 1991
| double_required_param | 5.5 |
| 1014 | 3 | 4 | 1445354419000 | 1 | 1991
| string_required_param | asd |
| 1014 | 3 | 4 | 1445354419000 | 1 | 1991
| int_required_param | 2 |
| 1014 | 3 | 4 | 1445354419000 | 1 | 1991
| bigint_required_param | -9223372036854775809 |
| 1015 | 3 | 4 | 1445354419000 | 1 | 1991
| smallint_required_param | 1 |
| 1015 | 3 | 4 | 1445354419000 | 1 | 1991
| float_required_param | 4.5 |
| 1015 | 3 | 4 | 1445354419000 | 1 | 1991
| boolean_required_param | true |
| 1015 | 3 | 4 | 1445354419000 | 1 | 1991
| double_required_param | 5.5 |
| 1015 | 3 | 4 | 1445354419000 | 1 | 1991
| string_required_param | asd |
| 1015 | 3 | 4 | 1445354419000 | 1 | 1991
| int_required_param | 2 |
| 1015 | 3 | 4 | 1445354419000 | 1 | 1991
| bigint_required_param | 9223372036854775808 |
| 1016 | 3 | 4 | 1445354419000 | 1 | 1991
| float_required_param | 4.5 |
| 1016 | 3 | 4 | 1445354419000 | 1 | 1991
| smallint_required_param | 1 |
| 1016 | 3 | 4 | 1445354419000 | 1 | 1991
| smallint_optional_param | -32769 |
| 1016 | 3 | 4 | 1445354419000 | 1 | 1991
| boolean_required_param | true |
| 1016 | 3 | 4 | 1445354419000 | 1 | 1991
| double_required_param | 5.5 |
| 1016 | 3 | 4 | 1445354419000 | 1 | 1991
| string_required_param | |
| 1016 | 3 | 4 | 1445354419000 | 1 | 1991
| int_required_param | 2 |
| 1016 | 3 | 4 | 1445354419000 | 1 | 1991
| bigint_required_param | 3 |
| 1017 | 3 | 4 | 1445354419000 | 1 | 1991
| float_required_param | 4.5 |
| 1017 | 3 | 4 | 1445354419000 | 1 | 1991
| smallint_required_param | 1 |
| 1017 | 3 | 4 | 1445354419000 | 1 | 1991
| smallint_optional_param | 32768 |
| 1017 | 3 | 4 | 1445354419000 | 1 | 1991
| boolean_required_param | true |
| 1017 | 3 | 4 | 1445354419000 | 1 | 1991
| double_required_param | 5.5 |
| 1017 | 3 | 4 | 1445354419000 | 1 | 1991
| string_required_param | |
| 1017 | 3 | 4 | 1445354419000 | 1 | 1991
| int_required_param | 2 |
| 1017 | 3 | 4 | 1445354419000 | 1 | 1991
| bigint_required_param | 3 |
+---------+----------+---------------+---------------+-----------+---------+-------------------------+----------------------+
*Dejan Prokić* | Data Engineer | Nordeus
2016-08-16 0:04 GMT+02:00 Tim Armstrong <ta...@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ć <de...@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
>>
>
>
Re: Bug with string in maps
Posted by Tim Armstrong <ta...@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ć <de...@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
>