Hive table generation functions

SQL > SELECT * FROM table_type WHERE (col); SQL > SELECT * FROM table_type WHERE (col); SELECT * FROM table_type WHERE (col); If you want to explode a key or value pair on a MAP, you will end up on a row. If you want to explode a key or value pair, you will end up on a row

Page game data:

10 CLARK|KING|MILLER

20 SMITH|JONES|SCOTT|ADAMS|FORD

30 ALLEN|WARD|MARTIN|BLAKE|TURNER|JAMES

Build tables:

create table emp2(

deptno int,

names array<string>

)

row format delimited fields terminated by ‘\t’

collection items terminated by ‘|’;

load data local inpath ‘/export/data/hivedatas/emp2.txt’ into table emp2;

select * from emp;

Use Expload to query

select explode(names) as name from emp;

Lateral View www.pizei.comudtf(Expression) Tablealias AS Columnalias Used with UDTFs such as Split and Split, it splits a column of data into rows and then aggregates the split data.

Column turned

select deptno,name from emp2 lateral view explode(names) tmp_tb as name;

1

The Reflect function supports the use of SQL to call built-in functions in Java

1. Create a Hive table using Max in java.lang.Math to find the maximum of two columns

create table test_udf(col1 int,col2 int) row format delimited fields terminated by ‘,’; 1 — Prepare data test_udf.txt

— Load data

load data local inpath ‘/export/data/hivedatas/test_udf.txt’ into table test_udf; 1 — Find the maximum value of the two columns using Max in java.lang.Math

select reflect(“java.lang.Math”,”max”,col1,col2) from test_udf;