hive（2）

-- 复习
CREATE TABLE IF NOT EXISTS dept_partition
(
    deptno int,
    dname string,
    loc string
)
partitioned BY(month string)
row FORMAT delimited
fields terminated BY '\t'
;

DESC dept_partition;

show partitions dept_partition;

ALTER TABLE dept_partition ADD partition(month='201904');
ALTER TABLE dept_partition ADD partition(month='201905') partition(month='201906');

ALTER TABLE dept_partition drop partition(month='201904');
ALTER TABLE dept_partition drop partition(month='201905'),partition(month='201906');

LOAD data local inpath '/root/data/dept.txt' overwrite INTO TABLE `dept_partition` 
partition(`month`='201904',`month`='201905');

-- 静态分区之二级分区
CREATE TABLE IF NOT EXISTS dept_partition2
(
    deptno int,
    dname string,
    loc string
)
partitioned BY(month int,date int)
row FORMAT delimited
fields terminated BY '\t'
;

ALTER TABLE dept_partition2 ADD partition(month=201904,date=01);
ALTER TABLE dept_partition2 ADD partition(month=201904,date=02) partition(month=201904,date=03);

show partitions dept_partition2;

ALTER TABLE dept_partition2 drop partition(month=201904,date=01);
ALTER TABLE dept_partition2 drop partition(month=201904,date=02),partition(month=201904,date=03);

LOAD data local inpath '/root/data/dept.txt' overwrite INTO TABLE `dept_partition2` partition(month=201904,date=01);

SELECT * FROM dept_partition2;

-- 动态分区
-- 准备数据student_male_female.txt
001,tom,22,male
002,jack,23,male
003,rose,20,female
004,mike,25,male
005,lucy,21,female
006,andy,20,female
-- 建表
DROP TABLE IF EXISTS student_info;
CREATE TEMPORARY TABLE IF NOT EXISTS student_info
(
    id string,
    name string,
    age int,
    sex string
)
row FORMAT delimited
fields terminated BY ','
;
-- 加载数据
LOAD data local inpath '/root/data/student_male_female.txt' overwrite INTO TABLE `student_info`;
-- 查看数据
SELECT * FROM student_info;
-- 建分区表
DROP TABLE IF EXISTS student_partition;
CREATE TABLE IF NOT EXISTS student_partition
(
    id string,
    name string,
    age int
)
partitioned BY (sex string);
-- 检查是否已开启动态分区模式【默认为true】
set hive.exec.dynamic.partition=true;
-- 关闭严格模式
set hive.exec.dynamic.partition.mode=nonstrict;
-- 插入数据【注意：所有字段顺序对应，分区字段必须放在最后】
INSERT INTO TABLE `student_partition` partition(sex)
SELECT id,name,age,sex FROM student_info;
-- 查看数据
SELECT * FROM `student_partition`;
show partitions `student_partition`;

-- 示例2
-- 准备数据student_partition2.txt
01,tom,22,2019-09-08
02,jack,23,2020-08-09
03,rose,20,2019-09-08
04,smith,21,2020-07-09
05,john,22,2019-09-08
-- 建临时表
CREATE TEMPORARY TABLE IF NOT EXISTS student_info2
(
    id string,
    name string,
    age int,
    start_date date
)
row FORMAT delimited
fields terminated BY ','
;
-- 加载数据
LOAD data local inpath '/root/data/student_partition2.txt' overwrite INTO TABLE `student_info2`;
-- 查看数据
SELECT * FROM student_info2;
-- 建分区表
CREATE TABLE IF NOT EXISTS student_partition2
(
    id string,
    name string,
    age int,
    start_date date
)
partitioned BY(year string,month string);
-- 查看数据
DESC student_partition2;
-- 插入数据
INSERT INTO TABLE student_partition2 partition(year,month)
SELECT id,name,age,start_date,YEAR(start_date) year,MONTH(start_date) month
FROM student_info2;

-- 分桶表
-- 准备数据bucket.txt
1,aa
2,bb
3,cc
4,dd
5,ee
6,ff
7,gg
8,hh
9,ii
10,jj
11,kk
12,ll
13,mm
14,nn
15,oo
16,pp
-- 建表
CREATE TABLE IF NOT EXISTS bucket_table
(
    id int,
    name string
)
row FORMAT delimited
fields terminated BY ','
;
-- 加载数据
LOAD data local inpath '/root/data/bucket.txt' overwrite INTO TABLE `bucket_table`;
-- 查看数据
SELECT * FROM bucket_table;
-- 设置分桶功能开关【默认值false】
SET hive.enforce.bucketing=true;
-- 创建分桶表
CREATE TABLE IF NOT EXISTS bucket_table2
(
    id int,
    name string
)
CLUSTERED BY (id)
INTO 4 buckets
row FORMAT delimited
fields terminated BY ',';
-- 插入数据
INSERT INTO TABLE bucket_table2
SELECT id,name FROM bucket_table;
-- 查看数据
SELECT id,name FROM bucket_table2;

-- 侧视图
-- 建表
CREATE TABLE employee AS 
select * from managed_employee;
-- 查看数据
select * from employee;

SELECT work_place FROM employee;
+-------------------------+--+
|       work_place        |
+-------------------------+--+
| ["Montreal","Toronto"]  |
| ["Montreal"]            |
| ["New York"]            |
| ["Vancouver"]           |
+-------------------------+--+
SELECT explode(work_place) FROM employee;
+------------+--+
|    col     |
+------------+--+
| Montreal   |
| Toronto    |
| Montreal   |
| New York   |
| Vancouver  |
+------------+--+
SELECT name,work_place FROM employee;
+----------+-------------------------+--+
|   name   |       work_place        |
+----------+-------------------------+--+
| Michael  | ["Montreal","Toronto"]  |
| Will     | ["Montreal"]            |
| Shelley  | ["New York"]            |
| Lucy     | ["Vancouver"]           |
+----------+-------------------------+--+
SELECT name,explode(work_place) FROM employee;
-- 报错
FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested
 in expressionsError: Error while compiling statement: FAILED: SemanticException [Error 10081]: UDTF's are not support
ed outside the SELECT clause, nor nested in expressions (state=42000,code=10081)
-- 修正
SELECT name,wp
FROM employee lateral VIEW explode(work_place) t1 AS wp;
+----------+------------+--+
|   name   |     wp     |
+----------+------------+--+
| Michael  | Montreal   |
| Michael  | Toronto    |
| Will     | Montreal   |
| Shelley  | New York   |
| Lucy     | Vancouver  |
+----------+------------+--+

SELECT skills_score FROM employee;
+-----------------------+--+
|     skills_score      |
+-----------------------+--+
| {"DB":80}             |
| {"Perl":85}           |
| {"Python":80}         |
| {"Sales":89,"HR":94}  |
+-----------------------+--+
SELECT explode(skills_score) FROM employee;
+---------+--------+--+
|   key   | value  |
+---------+--------+--+
| DB      | 80     |
| Perl    | 85     |
| Python  | 80     |
| Sales   | 89     |
| HR      | 94     |
+---------+--------+--+
SELECT name,skills_score FROM employee;
+----------+-----------------------+--+
|   name   |     skills_score      |
+----------+-----------------------+--+
| Michael  | {"DB":80}             |
| Will     | {"Perl":85}           |
| Shelley  | {"Python":80}         |
| Lucy     | {"Sales":89,"HR":94}  |
+----------+-----------------------+--+
SELECT name,explode(skills_score) FROM employee;
-- 报错
FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested
 in expressionsError: Error while compiling statement: FAILED: SemanticException [Error 10081]: UDTF's are not support
ed outside the SELECT clause, nor nested in expressions (state=42000,code=10081)
-- 修正
SELECT name,skill,score
FROM employee lateral VIEW explode(skills_score) t1 AS skill,score;
+----------+---------+--------+--+
|   name   |  skill  | score  |
+----------+---------+--------+--+
| Michael  | DB      | 80     |
| Will     | Perl    | 85     |
| Shelley  | Python  | 80     |
| Lucy     | Sales   | 89     |
| Lucy     | HR      | 94     |
+----------+---------+--------+--+

SELECT sex_age FROM employee;
+----------------------------+--+
|          sex_age           |
+----------------------------+--+
| {"sex":"Male","age":30}    |
| {"sex":"Male","age":35}    |
| {"sex":"Female","age":27}  |
| {"sex":"Female","age":57}  |
+----------------------------+--+
SELECT explode(sex_age) FROM employee;
-- 报错
FAILED: UDFArgumentException explode() takes an array or a map as a parameter
Error: Error while compiling statement: FAILED: UDFArgumentException explode() takes an array or a map 
as a parameter (state=42000,code=40000)
-- 修正
SELECT array(sex_age) FROM employee;
+------------------------------+--+
|             _c0              |
+------------------------------+--+
| [{"sex":"Male","age":30}]    |
| [{"sex":"Male","age":35}]    |
| [{"sex":"Female","age":27}]  |
| [{"sex":"Female","age":57}]  |
+------------------------------+--+
SELECT inline(array(sex_age)) FROM employee;
+---------+------+--+
|   sex   | age  |
+---------+------+--+
| Male    | 30   |
| Male    | 35   |
| Female  | 27   |
| Female  | 57   |
+---------+------+--+
SELECT name,inline(array(sex_age)) FROM employee;
-- 报错
FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested
 in expressionsError: Error while compiling statement: FAILED: SemanticException [Error 10081]: UDTF's are not support
ed outside the SELECT clause, nor nested in expressions (state=42000,code=10081)
-- 修正
SELECT name,sex,age
FROM employee lateral VIEW inline(array(sex_age)) t1 AS sex,age;
+----------+---------+------+--+
|   name   |   sex   | age  |
+----------+---------+------+--+
| Michael  | Male    | 30   |
| Will     | Male    | 35   |
| Shelley  | Female  | 27   |
| Lucy     | Female  | 57   |
+----------+---------+------+--+

SELECT * FROM employee;
+----------------+-------------------------+----------------------------+------------------------+-----
---------------------------------------------+--+| employee.name  |   employee.work_place   |      employee.sex_age      | employee.skills_score  |     
          employee.dept_title                |+----------------+-------------------------+----------------------------+------------------------+-----
---------------------------------------------+--+| Michael        | ["Montreal","Toronto"]  | {"sex":"Male","age":30}    | {"DB":80}              | {"Pr
oduct":"Developer","Administration":"Lead"}  || Will           | ["Montreal"]            | {"sex":"Male","age":35}    | {"Perl":85}            | {"Pr
oduct":"Lead","Test":"Lead"}                 || Shelley        | ["New York"]            | {"sex":"Female","age":27}  | {"Python":80}          | {"Te
st":"Lead","COE":"Architect"}                || Lucy           | ["Vancouver"]           | {"sex":"Female","age":57}  | {"Sales":89,"HR":94}   | {"Sa
les":"Lead"}                                 |+----------------+-------------------------+----------------------------+------------------------+-----
---------------------------------------------+--+
SELECT name,wp,sex,age,skill,score,dept,title
FROM employee
lateral VIEW explode(work_place) t AS wp
lateral VIEW inline(array(sex_age)) t AS sex,age
lateral VIEW explode(skills_score) t AS skill,score
lateral VIEW explode(dept_title) t AS dept,title;
+----------+------------+---------+------+---------+--------+-----------------+------------+--+
|   name   |     wp     |   sex   | age  |  skill  | score  |      dept       |   title    |
+----------+------------+---------+------+---------+--------+-----------------+------------+--+
| Michael  | Montreal   | Male    | 30   | DB      | 80     | Product         | Developer  |
| Michael  | Montreal   | Male    | 30   | DB      | 80     | Administration  | Lead       |
| Michael  | Toronto    | Male    | 30   | DB      | 80     | Product         | Developer  |
| Michael  | Toronto    | Male    | 30   | DB      | 80     | Administration  | Lead       |
| Will     | Montreal   | Male    | 35   | Perl    | 85     | Product         | Lead       |
| Will     | Montreal   | Male    | 35   | Perl    | 85     | Test            | Lead       |
| Shelley  | New York   | Female  | 27   | Python  | 80     | Test            | Lead       |
| Shelley  | New York   | Female  | 27   | Python  | 80     | COE             | Architect  |
| Lucy     | Vancouver  | Female  | 57   | Sales   | 89     | Sales           | Lead       |
| Lucy     | Vancouver  | Female  | 57   | HR      | 94     | Sales           | Lead       |
+----------+------------+---------+------+---------+--------+-----------------+------------+--+

SELECT split('aa,bb,cc',',');
+-------------------+--+
|        _c0        |
+-------------------+--+
| ["aa","bb","cc"]  |
+-------------------+--+
SELECT explode(split('aa,bb,cc',','));
+------+--+
| col  |
+------+--+
| aa   |
| bb   |
| cc   |
+------+--+
SELECT explode(split(null,','));
+------+--+
| col  |
+------+--+
+------+--+
SELECT name,wp
FROM employee
lateral VIEW explode(split(null,',')) t AS wp;
+-------+-----+--+
| name  | wp  |
+-------+-----+--+
+-------+-----+--+
SELECT name,wp
FROM employee
lateral VIEW outer explode(split(null,',')) t AS wp;
+----------+-------+--+
|   name   |  wp   |
+----------+-------+--+
| Michael  | NULL  |
| Will     | NULL  |
| Shelley  | NULL  |
| Lucy     | NULL  |
+----------+-------+--+
-- 重点：
建库
建表（内，外）
加载数据
(1)load data ... 
(2)INSERT INTO TABLE ...
分区（静，动）
侧视图（数组，映射，结构）