Shulou(Shulou.com)06/03 Report--

Explode (official website link)

Explode is a UDTF (table generation function) that converts a single input line into multiple output lines. Generally used in combination with lateral view, there are two main uses:

Input Typ

Usage

Description

T

Explode (ARRAY a)

Decompose the array into multiple rows, returning multiple rows in a single column, each row representing an element of the array

Tkey,Tvalue

Explode (MAP m)

The MAP is decomposed into multiple rows, and the returned row has two columns (key-value), each representing a key-value pair in the input

Example:

Explode (array)

Hive (default) > select explode (array); OKcolABCTime taken: 0.402 seconds, Fetched: 3 row (s) hive (default) > select explode (array) as col1;OKcol1ABCTime taken: 0.145 seconds, Fetched: 3 row (s) hive (default) > select tf.* from (select 0) t lateral view explode OKtf.colABCTime taken: 0.191 seconds, Fetched: 3 row (s) hive (default) > select tf.* from (select 0) t lateral view explode (array)) tf as col1;OKtf.col1ABC

Explode (map)

Hive (default) > select explode (map); OKkey valueA 10B 20C 30Time taken: 0.153 seconds, Fetched: 3 row (s) hive (default) > select explode (map)) as (my_key,my_value) OKmy_key my_valueA 10B 20C 30Time taken: 0.137 seconds, Fetched: 3 row (s) hive (default) > select tf.* from (select 0) t lateral view explode (map)) tf OKtf.key tf.valueA 10B 20C 30Time taken: 0.128 seconds, Fetched: 3 row (s) hive (default) > select tf.* from (select 0) t lateral view explode (map)) tf as my_key,my_value;OKtf.my_key tf.my_valueA 10B 20C 30Time taken: 0.109 seconds, Fetched: 3 row (s)

LateralView (official website link)

Grammar

LateralView: LATERAL VIEW udtf (expression) tableAlias AS columnAlias (', 'columnAlias) *

FromClause: FROM baseTable (lateralView) *

Description

Lateralview is used in conjunction with a user-defined table generator function (UDTF), such as explode, where UDTF generates zero or more output rows for each input row. Lateralview first applies UDTF to each row of the underlying table, and then joins the resulting output row with the input row to form a virtual table with the provided table alias.

Example

The underlying table pageads has two columns: pageid (page name) and add_list (array of advertisements displayed on the page).

Hive (test) >! cat pageads >; front_page 1 collection items terminated by > create table pageads (> pageid string, > add_list array) > ROW FORMAT delimited > fields terminated by'\ t'> collection items terminated by','> lines terminated by'\ n'>; OKTime taken: 0.099 secondshive (test) > load data local inpath'/ home/hadoop/pageads' into table pageads Loading data to table test.pageadsOKTime taken: 0.331 secondshive (test) > select * from pageads;OKpageads.pageid pageads.add_listfront_page contact_page [3 row 4 hive 5] Time taken: 0.106 seconds, Fetched: 2 row (s) hive (test) > select pageid,addid from pageads lateral view explode (add_list) adTable as addid OKpageid addidfront_page 1front_page 2front_page 3contact_page 3contact_page 4contact_page 5Time taken: 0.105 seconds, Fetched: 6 row (s) hive (test) > select addid,count (1) from pageads lateral view explode (add_list) adTable as addid group by addid;.OKaddid _ c11 12 13 24 15 1

Multiple lateralview

The from clause can have multiple lateralview clauses. Subsequent lateralview clauses can refer to any column in the left-hand table of lateralview. For example, the following query:

SELECT * FROM exampleTable

LATERAL VIEW explode (col1) myTable1 AS myCol1

LATERAL VIEW explode (myCol1) myTable2 AS myCol2

Notice that the lateralview clause is applied in the order in which it appears.

Example

Hive (test) >! cat basetable;1,2 a dagger b3je 4d recorder ejournal fhive (test) > create table basetable (> col1 array, > col2 array) > ROW FORMAT delimited > fields terminated by'\ t'> collection items terminated by','> lines terminated by'\ n'>; OKTime taken: 0.113 secondshive (test) > load data local inpath'/ home/hadoop/basetable' into table basetable Loading data to table test.basetableOKTime taken: 0.329 secondshive (test) > select * from basetable;OKbasetable.col1 basetable.col2 [1J2] ["a", "b", "c"] [3Jing 4] ["d", "e", "f"] Time taken: 0.104 seconds, Fetched: 2 row (s) hive (test) > SELECT myCol1, col2 FROM basetable > LATERAL VIEW explode (col1) myTable1 AS myCol1 OKmycol1 col21 ["a", "b", "c"] 2 ["a", "b", "c"] 3 ["d", "e", "f"] 4 ["d", "e", "f"] Time taken: 0.089 seconds, Fetched: 4 row (s) hive (test) > SELECT myCol1, myCol2 FROM baseTable > LATERAL VIEW explode (col1) myTable1 AS myCol1 > LATERAL VIEW explode (col2) myTable2 AS myCol2 OKmycol1 mycol21 a1 b1 c2 a2 b2 c3 d3 e3 f4 d4 e4 fTime taken: 0.093 seconds, Fetched: 12 row (s) Outer Lateral Viewshive (test) > SELECT * FROM basetable LATERAL VIEW explode (array ()) C AS a limit 10 X OKbasetable.col1 basetable.col2 c.aTime taken: 0.063 secondshive (test) > SELECT * FROM basetable LATERAL VIEW OUTER explode (array ()) C AS a limit 10 OKbasetable.col1 basetable.col2 c.a [1seconds 2] ["a", "b", "c"] NULL [3pr 4] ["d", "e", "f"] NULLTime taken: 0.092 seconds, Fetched: 2 row (s)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.