01-20-2019 01:13 PM - last edited on 01-22-2019 06:09 AM by cjervis
I created this:
CREATE TABLE schoolparquet (
) STORED AS
| name | type
| sesso | string
| anni | map<string,string>
I'm trying to write a query (Impala) that extract key/values from map too, starting from this:
select tipo, paese, per_paese.item.sesso
This works fine.
I want to do something like this:
select tipo, paese, per_paese.item.sesso,
But I have this error :
ERROR: AnalysisException: Illegal column/field reference 'per_paese.item.anni.value' with intermediate collection 'per_paese' of type 'ARRAY<STRUCT<sesso:STRING,anni:MAP<STRING,STRING>>>'
02-19-2019 10:57 AM
You will need to use both dot notation and join notation:
When complex types are nested inside each other, you use a combination of joins, pseudocolumn names, and dot notation to refer to specific fields at the appropriate level. This is the most frequent form of query syntax for complex columns, because the typical use case involves two levels of complex types, such as an ARRAY of STRUCT elements.
SELECT id, phone_numbers.area_code FROM contact_info_many_structs INNER JOIN contact_info_many_structs.phone_numbers phone_numbers LIMIT 3;
You can express relationships between ARRAY and MAP columns at different levels as joins. You include comparison operators between fields at the top level and within the nested type columns so that Impala can do the appropriate join operation.