Filtering on multiple array fields in a hive table

Question

I have a hive table "Records" with the following structure:

recordid int
addresses array<map<string,string>>
knownnames array<map<string,string>>

The addresses array contains the standard parts for an address (house number, street name, city, state) and may contain multiple of these elements (if a record has more than 1 address). The knownnames arrary contains first name, middle name, and last name and may contain multiple of each (if a record has akas).

How can I query my "records" table for all records that have any address in CA and a lastname of "Smith"?

I've tried exploding both arrays but it looks like hive doesn't like having elements from 2 different arrays in the where clause....

I got a similar problem and got my answer here, try it out : stackoverflow.com/questions/50370174/… — ramu

gobrewers14 gobrewers14 · Accepted Answer · 2015-07-17T22:14:55

Since you completely changed the question I'm not sure; I'll have to test this.

select recordid, cities, last_names
from (
  select recordid, cities
    , knownname.last_name as last_names
  from (
    select recordid, knownnames
      , address.city as cities
    from db.table
    lateral view explode(addresses) exptbl1 as address ) x
  lateral view explode(knownnames) exptbl2 as knownname
  where cities='CA' ) y
where last_names='Smith'

Filtering on multiple array fields in a hive table

1 Answers