关于hive:Hive常见语句及函数

一、数据库

1 创立库语句

CREATE DATABASE [IF NOT EXISTS] database_name [COMMENT database_comment] [LOCATION hdfs_path] [WITH DBPROPERTIES (property_name=property_value, …)];

2 查询数据库

2.1 显示数据库

show databases;

2.2 过滤显示查问的数据库

show databases like ‘d*’;

2.3 显示数据库信息

desc database ods;

2.4 显示数据库详细信息，extended

desc database extended ods;

2.5 切换数据库详细信息，use

use dwd;

3 批改数据库

3.1 批改数据库属性，Alter

ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, …);

3.2 删除数据库，drop

3.2.1 删除空的数据库

drop database dwd;

3.2.2 删除非空的数据库，应用 cascade 强制删除。

drop database dwd cascade;

二、表

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]

（1）CREATE TABLE 创立一个指定名字的表。如果雷同名字的表曾经存在，则抛出异样；用户能够用 IF NOT EXISTS 选项来疏忽这个异样。

（2）EXTERNAL 关键字能够让用户创立一个内部表，在建表的同时能够指定一个指向理论数据的门路（LOCATION），在删除表的时候，外部表的元数据和数据会被一起删除，而内部表只删除元数据，不删除数据。

（3）COMMENT：为表和列增加正文。

（4）PARTITIONED BY 创立分区表

（5）CLUSTERED BY 创立分桶表

（6）SORTED BY 不罕用，对桶中的一个或多个列另外排序

（7）ROW FORMAT

DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]

        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]

   | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, …)]

用户在建表的时候能够自定义 SerDe 或者应用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会应用自带的 SerDe。在建表的时候，用户还须要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。

SerDe 是 Serialize/Deserilize 的简称，hive 应用 Serde 进行行对象的序列与反序列化。

（8）STORED AS 指定存储文件类型

罕用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）

如果文件数据是纯文本，能够应用 STORED AS TEXTFILE。如果数据须要压缩，应用 STORED AS SEQUENCEFILE。

（9）LOCATION：指定表在 HDFS 上的存储地位。

（10）AS：后跟查问语句，依据查问后果创立表。

（11）LIKE 容许用户复制现有的表构造，然而不复制数据。

（12）TBLPROPERTIES 设置表的属性，在表明压缩类型时用过。（例如”orc.compress”=”SNAPPY”）

2 查问表信息

2.1 查看表的元数据信息 desc

DESCRIBE  FORMATTED orders;

2.2 批改 alter

2.2.1 批改表名：alter table dept rename to depts;

2.2.2 批改表属性：

alter table depts set tblproperties (‘EXTERNAL’=’TRUE’) alter table depts set tblproperties (‘EXTERNAL’=’FALSE’)

2.2.3 批改列, 留神如果批改的字段类型和之前的字段类型不统一，之前的数据就无奈显示

批改列名和列数据类型：alter table depts change dept_name dname string ; 批改地位搁置第一位：alter table depts change id did string first; 批改地位指定某一列前面：alter table depts change dname dname string after did;

2.2.4 增加列 (慎用)

alter table depts add columns(daddress string);

2.2.5 增加分区

alter table depts add partition(dt=20200713); alter table depts add partition(dt=20200713) location ‘/user/test/20200713.txt’;

2.2.6 批改分区

alter table depts partition(dt=20170404) rename to partition(dt=20170405); alter table depts partition(dt=20170404) set location ‘/user/test/depts.txt’;

2.2.7 删除分区

alter table depts drop if exists partition(dt=20170404);

2.3 查问表数据

Hive 中的 SELECT 根底语法和规范 SQL 语法基本一致，反对 WHERE、DISTINCT、GROUP BY、ORDER BY、HAVING、LIMIT、子查问等；

具体语法：

[WITH CommonTableExpression (, CommonTableExpression)*] SELECT [ALL | DISTINCT] select_expr, select_expr, … FROM table_reference [WHERE where_condition] [GROUP BY col_list] [CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list] ] [LIMIT number]

2.3.1 罕用关键词简介

(1) ORDER BY 和 SORT BY

ORDER BY 用于全局排序，就是对指定的所有排序键进行全局排序，应用 ORDER BY 的查问语句，最初会用一个 Reduce Task 来实现全局排序。

explain select id,name from emp where deptid = 1001

explain select id,name from emp where deptid = 1001 order by id

SORT BY 用于分区内排序，即每个 Reduce 工作内排序。

设定了 2 个 reduce，从后果能够看出，每个 reduce 内做了排序。设定一个 reduce，从后果看和 order by 统一。

（2）DISTRIBUTE BY 和 CLUSTER BY

distribute by：依照指定的字段或表达式对数据进行划分，输入到对应的 Reduce 或者文件中。

cluster by：除了兼具 distribute by 的性能，还兼具 sort by 的排序功能。

(3)GROUP BY 语句通常会和聚合函数一起应用，依照一个或者多个列队后果进行分组，而后对每个组执行聚合操作。

（4）子查问

子查问和规范 SQL 中的子查问语法和用法基本一致，须要留神的是，Hive 中如果是从一个子查问进行 SELECT 查问，那么子查问必须设置一个别名。

SELECT col FROM (SELECT a+b AS col FROM t1) t2

where 语句中也反对子查问。

SELECT * FROM A WHERE A.a IN (SELECT foo FROM B); SELECT A FROM T1 WHERE EXISTS (SELECT B FROM T2 WHERE T1.X = T2.Y)

WITH..AS.. 也叫做子查问局部, 语句容许 hive 定义一个 sql 片段, 供整个 sql 应用，行将子查问作为一个表的语法，叫做 Common Table Expression（CTE）

with q1 as (select from src where key= ‘5’), q2 as (select from src s2 where key = ‘4’) select from q1 union all select from q2; with q1 as (select key, value from src where key = ‘5’) from q1 insert overwrite table s1 select * from q1;

(5)Hive 查问中有两个虚构列：INPUT__FILE__NAME：数据对应的 HDFS 文件名；BLOCK__OFFSET__INSIDE__FILE：该行记录在文件中的偏移量；

(6)HAVING 语句

having 与 where 不同点

·where 前面不能写分组函数，而 having 前面能够应用分组函数。

·having 只用于 group by 分组统计语句。

(7) JOIN 语句

Hive 中除了反对和传统数据库中一样的内关联、左关联、右关联、全关联，还反对 LEFT SEMI JOIN 和 CROSS JOIN，但这两种 JOIN 类型也能够用后面的代替。

·内关联（JOIN）

·左外关联（LEFT [OUTER] JOIN）

·右外关联（RIHGHT [OUTER] JOIN）

（8）LIKE 语句

如果字符串 A 或者字符串 B 为 NULL，则返回 NULL；如果字符串 A 合乎表达式 B 的正则语法，则为 TRUE；否则为 FALSE。B 中字符”_”示意任意单个字符，而字符”%”示意任意数量的字符。

年龄表

查问年龄为 1 结尾的数据：select * from ages where  age like ‘1%’;

查问年龄第二位是 1 的数据：select * from ages where  age like ‘_1%’;

RLIKE 子句是 Hive 中 LIKE 性能的一个扩大，其能够通过 Java 的正则表达式这个更弱小的语言来指定匹配条件。

2.3.2 罕用函数简介

(1) 字符串连贯函数，concat

语法: concat(string A, string B…)

返回值: string

阐明：返回输出字符串连贯后的后果，反对任意个输出字符串

举例：select concat(‘abc’,’def’,’gh’) from ages ;

（2）带分隔符字符串连贯函数：concat_ws

语法: concat_ws(string SEP, string A, string B…)

返回值: string

阐明：返回输出字符串，连贯后的后果，SEP 示意各个字符串间的分隔符

举例：select concat_ws(‘,’,’abc’,’def’,’gh’) from ages ;

除此之外还能够将数组的模式转换为字符串 concat_ws(string SEP, array)

举例：select concat_ws(‘|’,array(‘ads’,’des’,’ss’)) from ages ;

（3）汇合去重函数：collect_set

语法: collect_set(col)

返回值: array

阐明: 将 col 字段进行去重，合并成一个数组。

举例：cookies 表

select cookieid,collect_set(name) from cookies group by cookieid ;

(4) UDTF 一进多出

列转行：explode

语法: explode(ARRAY)

返回值: 多行

阐明：将数组转换为多行

举例：select explode(array(‘ads’,’des’,’ss’))from ages;

除此之外还能够将 map 拆分为多行。

（5）UDAF 多进一出

聚合函数 count，sum，avg 等

（6）窗口函数

个别和聚合函数配合应用

OVER()：指定剖析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变动。

举例：

orders 表

总结：over 是对分的组失去后果的 count，因为搜到了 4 集体，所以 count 数为 4。间接 count 是分组后对组中的数据进行 count。

总结：增加 partition by name 示意依照 name 分组进行 sum 求和。

(7) 排序函数

RANK() 排序雷同时会反复，总数不会变

DENSE_RANK() 排序雷同时会反复，总数会缩小

ROW_NUMBER() 会依据程序计算

举例：scores 表

select name, subject, score, rank() over(partition by subject order by score desc) rp, dense_rank() over(partition by subject order by score desc) drp, row_number() over(partition by subject order by score desc) rmp from scores;

失去后果：

（8）函数总结大全

一、关系运算：

1. 等值比拟: =
2. 等值比拟:<=>
3. 不等值比拟: <> 和!=
4. 小于比拟: <
5. 小于等于比拟: <=
6. 大于比拟: >
7. 大于等于比拟: >=
8. 区间比拟
9. 空值判断: IS NULL
10. 非空判断: IS NOT NULL
11. LIKE 比拟: LIKE
12. JAVA 的 LIKE 操作: RLIKE
13. REGEXP 操作: REGEXP

二、数学运算：

1. 加法操作: +
2. 减法操作: –
3. 乘法操作: *
4. 除法操作: /
5. 取余操作: %
6. 位与操作: &
7. 位或操作: |
8. 位异或操作: ^

9．位取反操作: ~

三、逻辑运算：

1. 逻辑与操作: AND、&&
2. 逻辑或操作: OR、||
3. 逻辑非操作: NOT、!

四、复合类型构造函数

1. map 构造
2. struct 构造
3. named_struct 构造
4. array 构造
5. create_union

五、复合类型操作符

1. 获取 array 中的元素
2. 获取 map 中的元素
3. 获取 struct 中的元素

六、数值计算函数

1. 取整函数: round
2. 指定精度取整函数: round
3. 向下取整函数: floor
4. 向上取整函数: ceil
5. 向上取整函数: ceiling
6. 取随机数函数: rand
7. 天然指数函数: exp
8. 以 10 为底对数函数: log10
9. 以 2 为底对数函数: log2
10. 对数函数: log
11. 幂运算函数: pow
12. 幂运算函数: power
13. 开平方函数: sqrt
14. 二进制函数: bin
15. 十六进制函数: hex
16. 反转十六进制函数: unhex
17. 进制转换函数: conv
18. 绝对值函数: abs
19. 正取余函数: pmod
20. 正弦函数: sin
21. 反正弦函数: asin
22. 余弦函数: cos
23. 反余弦函数: acos
24. positive 函数: positive
25. negative 函数: negative

七、汇合操作函数

1. map 类型大小：size
2. array 类型大小：size
3. 判断元素数组是否蕴含元素：array_contains
4. 获取 map 中所有 value 汇合
5. 获取 map 中所有 key 汇合
6. 数组排序

八、类型转换函数

1. 二进制转换：binary
2. 根底类型之间强制转换：cast

九、日期函数

1. UNIX 工夫戳转日期函数: from_unixtime
2. 获取以后 UNIX 工夫戳函数: unix_timestamp
3. 日期转 UNIX 工夫戳函数: unix_timestamp
4. 指定格局日期转 UNIX 工夫戳函数: unix_timestamp
5. 日期工夫转日期函数: to_date
6. 日期转年函数: year
7. 日期转月函数: month
8. 日期转天函数: day
9. 日期转小时函数: hour
10. 日期转分钟函数: minute
11. 日期转秒函数: second
12. 日期转周函数: weekofyear
13. 日期比拟函数: datediff
14. 日期减少函数: date_add
15. 日期缩小函数: date_sub

十、条件函数

1. If 函数: if
2. 非空查找函数: COALESCE
3. 条件判断函数：CASE
4. 条件判断函数：CASE

十一、字符串函数

1.    字符 ascii 码函数：ascii

2.    base64 字符串

1. 字符串连贯函数：concat

4.    带分隔符字符串连贯函数：concat_ws

1. 数组转换成字符串的函数：concat_ws
2. 小数位格式化成字符串函数：format_number
3. 字符串截取函数：substr,substring
4. 字符串截取函数：substr,substring
5. 字符串查找函数：instr
6. 字符串长度函数：length
7. 字符串查找函数：locate
8. 字符串格式化函数：printf
9. 字符串转换成 map 函数：str_to_map
10. base64 解码函数：unbase64(string str)
11. 字符串转大写函数：upper,ucase
12. 字符串转小写函数：lower,lcase
13. 去空格函数：trim
14. 右边去空格函数：ltrim
15. 左边去空格函数：rtrim
16. 正则表达式替换函数：regexp_replace
17. 正则表达式解析函数：regexp_extract
18. URL 解析函数：parse_url
19. json 解析函数：get_json_object
20. 空格字符串函数：space
21. 反复字符串函数：repeat
22. 左补足函数：lpad
23. 右补足函数：rpad
24. 宰割字符串函数: split
25. 汇合查找函数: find_in_set

30.    分词函数：sentences

1. 分词后统计一起呈现频次最高的 TOP-K
2. 分词后统计与指定单词一起呈现频次最高的 TOP-K

十二、混合函数

1. 调用 Java 函数：java_method
2. 调用 Java 函数：reflect
3. 字符串的 hash 值：hash

十三、XPath 解析 XML 函数

1. xpath
2. xpath_string
3. xpath_boolean
4. xpath_short, xpath_int, xpath_long
5. xpath_float, xpath_double, xpath_number

十四、汇总统计函数（UDAF）

1. 个数统计函数: count
2. 总和统计函数: sum
3. 平均值统计函数: avg
4. 最小值统计函数: min
5. 最大值统计函数: max
6. 非空集合总体变量函数: var_pop
7. 非空集合样本变量函数: var_samp
8. 总体规范偏离函数: stddev_pop
9. 样本规范偏离函数: stddev_samp

10．中位数函数: percentile

1. 中位数函数: percentile
2. 近似中位数函数: percentile_approx
3. 近似中位数函数: percentile_approx
4. 直方图: histogram_numeric
5. 汇合去重数：collect_set
6. 汇合不去重函数：collect_list

十五、表格生成函数 Table-Generating Functions (UDTF)

1. 数组拆分成多行：explode
2. Map 拆分成多行：explode