Monday, July 8, 2019

Explode

Explode: it's a UDTF which can be used outside the select statement with "Lateral" keyword for flattening the collection objects.

hive> desc T_UNSTRUCTURE;
OK
col_name        data_type       comment
emp_id                  int
name                    map<string,string>
addr                    struct<City:string,Pin:int>
skill_set               array<string>
Time taken: 0.027 seconds, Fetched: 4 row(s)


hive> select * from T_UNSTRUCTURE;
OK
t_unstructure.emp_id    t_unstructure.name      t_unstructure.addr      t_unstructure.skill_set
10      {"first":"Amit","Last":"Mishra"}        {"city":"Blr","pin":1}  ["'Hadoop'","'OBIEE'"]
20      {"first":"Ramesh","Last":"Nayak"}       {"city":"Mas","pin":2}  ["'oracle'","'Chocolate'"]
Time taken: 0.32 seconds, Fetched: 2 row(s)


hive> select emp_id,skill from T_UNSTRUCTURE Lateral view explode(skill_set) temp_table as skill;
OK
emp_id  skill
10      'Hadoop'
10      'OBIEE'
20      'oracle'
20      'Chocolate'
Time taken: 0.104 seconds, Fetched: 4 row(s)

No comments:

Post a Comment