Re: Hive Avro union data access
Sathish,
Curent version Hive ONLY support union type has two primitive/struct types,
i.e.. {“name”: “alias_host”, “type”: [ “string”, “null” ]} , or {“name”:
“alias_host”, “type”: [{“type”: “array”, “items”: “string”]}, “null”}.
You can consider JSON string as a column type when the column has smart data
structure. and then use Hive built-in JSON function to query data.
Regards,
Link
On May 29, 2014, at 9:21 PM, Valluri, Sathish wrote:
>
> Hi,
>
> I have an Hive table created with 3 different union data types for alias_host
> column name as shown. (array,string, null).
>
> CREATE EXTERNAL TABLE array_tests ROW FORMAT SERDE
> 'org.apache.hadoop.hive.serde2.avro.AvroSerDe' WITH SERDEPROPERTIES
> ('avro.schema.literal'='{"name":"sessions","type":"record","fields":[{"default":null,"name":"alias_host","type":
> [{
> "type" : "array",
> "items" : "string"
> },"string","null"]}]}
> ') STORED AS INPUTFORMAT
> 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat' OUTPUTFORMAT
> 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat' LOCATION
> '/arrayTests';
>
> How to access and query the contents of this table in where clause.
> The queries below like these can be possible if the datatype is not union but
> when once I set the datatype as union the following queries are failing.
>
> Eg: select alias_host from array_tests where alias_host like ‘%test%’ limit
> 1000;
> Error: Error while processing statement: FAILED: SemanticException [Error
> 10016]: Line 1:32 Argument type mismatch 'alias_host': The 1st argument of
> EQUAL is expected to a primitive type, but union is found
> (state=42000,code=10016)
>
> Can anyone suggest how to access and query the contents of union data types.
>
> Regards
> Sathish Valluri
RE: Hive Avro union data access
Your "alias_host" column is an array, from your Avro specification, right?
If so, just use [] to access the specified element in the array
select alias_host[0] from array_tests where aliat_host[0] like '%test%'
If you want to query all the elements in the array, google "explode lateral
view" of hive.
Yong
From: sathish.vall...@emc.com
To: user@hive.apache.org
Subject: Hive Avro union data access
Date: Fri, 30 May 2014 06:21:19 +
Hi, I have an Hive table created with 3 different union data types for
alias_host column name as shown. (array,string, null). CREATE EXTERNAL
TABLE array_tests ROW FORMAT SERDE
'org.apache.hadoop.hive.serde2.avro.AvroSerDe' WITH SERDEPROPERTIES
('avro.schema.literal'='{"name":"sessions","type":"record","fields":[{"default":null,"name":"alias_host","type":
[{ "type" : "array", "items" : "string"},"string","null"]}]}')
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat' OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat' LOCATION
'/arrayTests'; How to access and query the contents of this table in where
clause.The queries below like these can be possible if the datatype is not
union but when once I set the datatype as union the following queries are
failing. Eg: select alias_host from array_tests where alias_host like ‘%test%’
limit 1000;Error: Error while processing statement: FAILED: SemanticException
[Error 10016]: Line 1:32 Argument type mismatch 'alias_host': The 1st argument
of EQUAL is expected to a primitive type, but union is found
(state=42000,code=10016) Can anyone suggest how to access and query the
contents of union data types. RegardsSathish Valluri
