Hive SQL学习笔记

语法顺序

1. 写的顺序 https://blog.csdn.net/dufufd/article/details/103797568

select ... from … join … on ... where ... group by ... having... order by … limit … offset

2. 执行顺序

1) 先运行子查询

2) 每个查询语句里运行顺序

from > join > on > where > group by(开始使用select中的别名，后面的语句中都可以使用) > WITH {CUBE | ROLLUP} > avg,sum.... > having > select > distinct > order by > limit

一、DDL

ddl英文缩写是data definition language（数据定义语言），主要的命令有alter、create、drop、truncate，ddl主要用在定义或者改变表的结构。

1. 创建数据库

create database if not exists 数据库名 location 'hdfs路径';

2. 查询数据库

1）显示数据库

show databases like '包含的字段';

2）显示数据库信息或者数据库详细信息(extended)

desc database extended 数据库名;

3) 切换当前数据库

use 数据库名

3. 修改数据库：ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值

alter database 数据库名

set dbproperties('createtime'='20170830');

4. 删除数据库

1) 删除空的数据库

drop database if exists 数据库名;

2)删除不为空的数据库

drop database 数据库名 cascade;

5. 创建表

1)普通创建表

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name

[(col_name data_type [COMMENT col_comment], ...)]

[COMMENT table_comment]

[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]

[CLUSTERED BY (col_name, col_name, ...)

[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]

[ROW FORMAT row_format]

[STORED AS file_format]

[LOCATION hdfs_path]

[TBLPROPERTIES (property_name=property_value, ...)]

[AS select_statement]

① EXTERNAL是外部表的关键字

② COMMENT：为表和列添加注释

③ PARTITIONED BY创建分区表

④ CLUSTERED BY创建分桶表

⑤ SORTED BY不常用，对桶中的一个或多个列另外排序

⑥ ROW FORMAT

    row format delimited fields terminated by ',' -- 列分隔符

    collection items terminated by '_' --MAP STRUCT 和ARRAY 的分隔符(数据分割符号)

    map keys terminated by ':' -- MAP中的key与value的分隔符

    lines terminated by '\n'; -- 行分隔符

⑦ STORED AS指定存储文件类型

⑧ LOCATION ：指定表在HDFS上的存储位置。

⑨ AS：后跟查询语句，根据查询结果创建表。

⑩ LIKE允许用户复制现的表结构，但是不复制数据。

2)根据查询结果创建表（查询的结果会添加到新创建的表中)

create table if not exists 新表名 [select statement];

3) 根据已经存在的表结构创建表

create table if not exists 新表名 like 旧表名;

6. 查询表

1）查看创建的表

show tables

2) 查询表的类型

desc formatted 表名;

3) 查询表的结构

desc 表名

7. 管理表与外部表的互相转换 => True为外部表; False为内部表

alter table 表名 set tblproperties('EXTERNAL'='TRUE');

注意：('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法，区分大小写！

8. 修改表

1) 修改表名

ALTER TABLE 旧表名 RENAME TO 新表名

2) 增加列

alter table 表名 add columns(新列名 列类型 [COMMENT col_comment]);

     3) 更新列

alter table 表名 change column 旧表名 新表名 列类型 column_type [COMMENT col_comment] [FIRST|AFTER column_name];

注意：小(int)的往大(string)的改（反过来不可以【语法不支持只改列名,但是类型改之前和之后一样】

隐式类型转换规则如下

①任何整数类型都可以隐式地转换为一个范围更广的类型，如TINYINT可以转换成INT，INT可以转换成BIGINT。

②所整数类型、FLOAT和STRING类型都可以隐式地转换成DOUBLE。

③TINYINT、SMALLINT、INT都可以转换为FLOAT。

④BOOLEAN类型不可以转换为任何其它的类型

4) 替换列

alter table 表名 replace columns(列名1 列类型, 列名2, 列类型);

注：ADD是代表新增一字段，字段位置在所列后面(partition列前)，REPLACE则是表示替换表中所字段。、

二、DML

dml英文缩写是data manipulation language（数据操纵语言），主要的命令有select、update、delete、insert，dml主要用来操作数据库中的数据

1. 数据导入

1) 向表中装在数据(load)

① 加载本地文件到hive (本地存在这张表) 【load会修改原数据】

     load data local inpath '本地路径' into table default.表名 partition(字段='分区规则')partition(day='yyyy-mm-dd', hour='hh');

② 加载HDFS文件到hive中 / 加载数据覆盖表中已的数据(HDFS上要有这个表, 没有需要上传)

 dfs -put 本地绝对路径 HDFS绝对路径;

     load data inpath 'hdfs绝对路径' overwrite into table default.表名;

注意：overwrite为可选关键词, 表示载数据覆盖表中已的数据

2) 通过查询语句向表中插入数据（Insert)

① 基本插入数据 (多个值用逗号隔开)

insert into table 表名 values(1,'wangwu'),(2,'zhaoliu');

② 基本模式插入（根据单张表查询结果)

insert overwrite table 表名

[select 列名 from 表名 where 条件]

insert into：以追加数据的方式插入到表或分区，原数据不会删除         

insert overwrite：会覆盖表中已存在的数据

注意：insert不支持插入部分字段

单表指的是最终接受表的是单表, 而查询的表可以多表(join)

③ 多表（多分区插入模式（根据多张表查询结果）

from student

insert overwrite table student partition(month='201707')

select id, name where month='201709'

insert overwrite table student partition(month='201706')

select id, name where month='201709';

④ 查询语句中创建表并加载数据（As Select) # create table后面不用加字段和类型

create table if not exists 新表名

as [select statement];

⑤ 创建表时通过Location指定加载数据路径

1) 上传数据到hdfs上（先在hdfs创建目录，然后将本地数据上传到该目录）

dfs -mkdir /hdfs目录名;

dfs -put 本地数据绝对路径 /hdfs目录名;

2) 创建表，并指定在hdfs上创建目录的的位置

create external table if not exists 新表名(

列名1 数据类型, 列名2 数据类型

)

row format delimited fields terminated by '分隔符'

location 'hdfs目录';

注意：创建表格默认路径在/user/hive/warehouse, 如果不写location, 就默认在这个路径

3) 查询数据

select * from student5;

⑥ Import 数据到指定 Hive 表中

。 (路径是export导出路径,否则invalid path)

2. 数据导出

1) insert 导出

将查询的结果【格式化:row format】导出到本地 【导出到HDFS上(没local关键字)】

insert overwrite local directory '本地绝对路径/HDFS绝对路径'

ROW FORMAT DELIMITED FIELDS TERMINATED BY '分隔符'

select * from 表名;

2) Hadoop 命令导出到本地 (表名不能同名)

dfs -get /user/hive/warehouse/student/student.txt

/opt/module/data/export/student3.txt;

3) Hive Shell 命令导出 (>写入到file里并打印出来, >>写入到file不打印出来)

bin/hive -f/-e '执行语句或者脚本'>  写入file绝对路径

bin/hive -f/-e '执行语句或者脚本'>>  写入file绝对路径

     4) Export 导出到 HDFS 上 （export 和 import一起使用主要用于两个 Hadoop 平台集群之间 Hive 表迁移。）

export table 数据库.表名 to 'hdfs绝对路径;

注意：如果hdfs没有某个路径, export会在hdfs创建这个路径; 这个路径和普通路径区别在于多导出了原数据信息

import table 表名 'hdfs绝对路径'

import导入数据的要求包括:

① import输入数据必须是export导出的路径

② 写入数据的表不存在,或者存在但没数据(空表)

     5) Sqoop导出

3. 清除表中数据（Truncate)

    truncate table 表名;

注意：Truncate 只能删除管理表(删除表中数据,表还在)，不能删除外部表中数据

三、DCL

dcl英文缩写是 data control language（数据控制语言），主要的命令有grant、deny、revoke等，dcl主要用来设置或者更新数据库用户或者角色权限

四、TCL

tcl英文缩写是 Transaction Control language，主要的作用是用来控制事务的，常用的关键字有commit、rollback、setpoint等

五、排序

1. order by (一个reducer)

2. sort by & distribute by

1) 先设置reducer个数

set mapreduce.job.reduces=3;

2) 查询语句写到本地分区路径

insert overwrite local directory '本地分区绝对路径' select * from 表名 distribute by 分区字段 sort by 排序字段 desc/asc;

3. cluster by (分区&排序字段相同; 必须升序)

select * from 表名 cluster by 分区&排序字段;

select * from 表名 distribute by 分区字段 sort by 排序字段;

六、分区 (避免全表扫描)

1. 静态分区

1) 创建分区表(包括一级、二级)

create table 表名_partition(

列名1 类型, 列名2 类型

partitioned by (day string, hour string)

row format delimited fields terminated by '\t';

注意:分区字段不能是表中已经存在的数据，可以将分区字段看作表的伪列。

2) 加载数据

load data local inpath '分区表本地绝对路径' into table 表名_partition partition(day='', hour='');

3) 查询分区数据

select * from 表名_partition where day=''and hour='';

4) 增加分区 (多个分区用空格) (二级分区多了一个子目录)

alter table 表名_partition add partition(day='') partition(day='');

5) 删除分区 (多个分区用逗号)

alter table 表名_partition drop partition (day=''),partition(day='');

6) 查询分区表结构

show partitions 表名_partition;

7) 查询分区表有多少分区

desc formatted 表名_partition;

8) 将数据用hdfs命令直接上传到hdfs的分区目录上, 本地原数据是看不到分区目录和数据, 三种方式解决：

dfs -mkdir -p /user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=13;

dfs -put /opt/module/datas/dept_20200401.log /user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=13;

① 执行修复命令

msck repair table dept_partition2;

② 添加分区

alter table dept_partition2 add partition(day='201709',hour='14');

③ 创建文件夹后load数据到分区

load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table dept_partition2 partition(day='20200401',hour='15');

注意： 如果不加分区字段, 会报错；不指定分区就会走MR, , hive默认走yarn, yarn把任务提交到resource manager, 跑MR是由resource manager随机选个机器执行, 找个有数据的机器执行就不会报错, 但是没有数据的机器执行就会报错；通过分发数据目录到各个集群, 所有机器都有数据，因此就不会报错。

2. 动态分区

1) 创建目标分区表 # 分区字段和table里面数据不一样

create table dept_partition_dy(id int, name string)

partitioned by (loc int)

row format delimited fields terminated by '\t';

2) 设置动态分区

set hive.exec.dynamic.partition.mode = nonstrict;

insert into table dept_partition_dy partition(loc)

select deptno, dname, loc from dept;

注意：默认按select语句最后一个字段分区并且创建表格的二级分区顺序partitioned by (day string, hour string) 和select最后两个字段顺序保持一致day, hour，hive3.0版本可以省略partition(loc)不用指定动态分区模式, 不会报错, strict也可以, 因为内部转为nonstrict

七、分桶表

# 分桶字段和table里面数据不一样

1. 创建分桶表 （指定分桶字段和个数）

create table stu_buck(id int, name string)

clustered by(id)

into 4 buckets

row format delimited fields terminated by '\t';

2. 导入数据到分桶表中 (走MR)

load data inpath'/student.txt' into table stu_buck;

八、函数分类

UDF: 一进一出 【CONCAT_WS(separator, str1, str2,...) - 多列变一列】

UDAF: 多进一出

UDTF: 一进多出 【lateral view EXPLODE(col) 一行(array或map结构)变多行】

多指的是输入数据的行数.

比如：

将一个单元格的多个文字, 用split函数变成一个数组

select split(category,',') from movie_into

["悬疑, "动作" , 科幻" ,"剧情" ]

["悬疑"，"警匪", "动作","心理" ,"剧情" ]

["战争", "动作", "灾难”]

如果只需要炸裂一列, 不需要与原数据列做关联

select explode(split(category,',')) from movie_into

 电影只行, 炸卡的列数多比较多, 用侧写lateral view 就会关联在一起movie_info_tmp 炸裂字段和原表字段关联

 SELECT

 movie,

 category_name

 FROM

 movie_info

 lateral VIEW

 explode(split(category,",")) movie_info_tmp  AS category_name;

查看函数用法

desc function 函数名

九、窗口函数

关键字：

①CURRENT ROW：当前行

②nPRECEDING：往前n行数据

③nFOLLOWING：往后n行数据

④UNBOUNDED：起点，

⑤UNBOUNDED PRECEDING 表示从前面的起点，

⑥UNBOUNDEDFOLLOWING表示到后面的终点

⑦LAG(col,n,default_val)：往前第n行数据

⑧LEAD(col,n,default_val)：往后第n行数据

⑨NTILE(n)：把序窗口的行分发到指定数据的组中，各个组编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。

应用举例：

jack,2017-01-01,10

tony,2017-01-02,15

jack,2017-02-03,23

tony,2017-01-04,29

jack,2017-01-05,46

jack,2017-04-06,42

tony,2017-01-07,50

jack,2017-01-08,55

mart,2017-04-08,62

mart,2017-04-09,68

neil,2017-05-10,12

mart,2017-04-11,75

neil,2017-06-12,80

mart,2017-04-13,94

创建表格

create table business(

name string,

orderdate string,
    cost int

) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

load data local inpath "/opt/module/data/business.txt" into table business;

1. (over代表开窗范围)

1) 查询在2017年4月份购买过的顾客及总人数

select name,count(*) over () from business

where substring(orderdate,1,7) = '2017-04' group by name;

2) 查询顾客的购买明细及顾客月购买总额

select name,orderdate,cost,sum(cost) over(partition by name, month(orderdate)) from business;

   查询顾客的购买明细及月购买总额

select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) from business;

3) 上述的场景, 将每个顾客的cost照日期进行累加

selectname,orderdate,cost,

sum(cost) over() as sample1,--所有行相加

sum(cost) over(partition by name) as sample2,--name分组，组内数据相加

sum(cost) over(partition by name order by orderdate) as sample3,--name分组，组内数据累加 - 没有指定开窗大小, 默认就是从起点到当前行, 所以和sample3一样

sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as sample4 ,--和sample3一样,由起点到当前行的聚合

sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5, --当前行和前面一行做聚合

sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行(相邻三行)

sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所行

from business;

sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING AND 1 PRECEDING ) as sample8,--从起点到当前行的上一行 （对于第一行到当前行上面一行找不到返回null）

sum(cost) over(order by orderdate)

select id, sum(id) over(order by id) from num;

遇到两个id一样，开的窗口是一样的都是9

4) 查询每个顾客上次的购买时间

select name,orderdate,

lag(orderdate,1) over(partition by name order by orderdate)

from business; # 如果是lag是第一行为null(没有上一行), 如果是lead是最后一行为null(没有下一行)

select name,orderdate,

lag(orderdate,1,'1970-01-01') over(partition by name order by orderdate)

from business; # null的部分 默认值为1970-01-01

select name,orderdate,

lag(orderdate,1, orderdate) over(partition by name order by orderdate)

from business; # null的部分 默认值为当前行 lag是网下移

select name,orderdate,

lead(orderdate,1, orderdate) over(partition by name order by orderdate)

from business; # null的部分 默认值为当前行 lead是网上移

应用: 求单跳转化率(可以用lag函数把相邻的放在一列, 进行拼接能求出A跳转B的次数)

user_id A

user_id B  A

user_id D

user_id B

上面的变为下面的

user_id A - B

user_id B - D

user_id D - B

A->B/A

5) 查询前20%时间的订单信息 (分五组)

select * from (

select name,orderdate,cost, ntile(5) over(order by orderdate) sorted

from business

) t

where sorted = 1;

十、Rank函数

十一、其他函数

1.unix_timestamp

2. select current_stamp

十二、常用函数

1. cast(timestamp as date)  【转换成日期格式】

2. extract(year from 列名) 【提取年】

3. ifnull(check_expression , replacement_value)【if null, it is replacement_value 】

4. offset [Number of rows to skip]

5. delete a from a,b where 【删除满足某一条件table a中相应的记录】

6. Update 语句

用法1：

update table_name

set column1 = value1, column2 = value2, ...

where condition;

用法2：

update table_name set column1 = if(column1=”value1”,”value2”,”value1”)

7. sum(case when column=”value” then 数值列 else null end as) 新列名

sum聚合函数会忽略null值，将数字字段进行累加

8. datediff(date1,date2) 【计算日期间隔】

9. month(日期列) 【取月 “6”】

left(日期列,7) 【取年月 “2020-06”】

日期列 between “日期1” and “日期2” 【取时间区间】

10.  least(arg1, arg2, arg3, ...) 【返回最小值】

greatest(arg1, arg2, arg3, ...) 【返回最大值】

11. 判断奇数：emp_no % 2 = 1

12. 拼接字符串或

MySQL(concat): concat(字段1, “ ”, 字段3) as 新字段

Sqlite(||): last_name||" "||first_name as name 【中间连着空格】

13. 同一个部门的emp_no按照逗号进行连接

group_concat(X,Y)，其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号。此函数必须与 GROUP BY 配合使用。

14. 截取字符串

方法1：

substr(string,start,length)

string - 指定的要截取的字符串。

start - 必需，规定在字符串的何处开始。正数 - 在字符串的指定位置开始，负数 - 在从字符串结尾的指定位置开始，0 - 在字符串中的          第一个字符处开始。

length - 可选，指定要截取的字符串长度，缺省时返回字符表达式的值结束前的全部字符。

例如：select substr('abcdefg',3,4) from dual; 结果是cdef

select substr('abcdefg',-3,4) from dual; 结果efg

注意：字符串中的第一个位置始终为1。以下两个sql查询的结果相同：

例如：select substr('abcdefg',0,3) from dual; 结果是abc

select substr('abcdefg',1,3) from dual; 结果是abc

方法2：

left/right(字符串,位数)

15. 包含某个字段

like ‘%%’

16. 求长度函数length()

17. 替换字符串

replace(X,Y,Z)

X为要替换的字符串, Y要替换的字符串, Z用什么替换

18. Limit

LIMIT m,n : 表示从第m+1条开始，取n条数据；

LIMIT n ： 表示从第0条开始，取n条数据，是limit(0,n)的缩写。