备案控制台
探索云世界
开发者社区 开发与运维 文章 正文

Hive常见语句及函数

2020-12-05 849
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 本文主要介绍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;
image.png
2.2 过滤显示查询的数据库
show databases like 'd*';
image.png
2.3 显示数据库信息
desc database ods;
image.png
2.4 显示数据库详细信息，extended
desc database extended ods;
image.png
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;
image.png

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
image.png

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

SORT BY用于分区内排序，即每个Reduce任务内排序。
image.png
image.png

设定了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文件名；
image.png
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中字符”_”表示任意单个字符，而字符”%”表示任意数量的字符。
年龄表
image.png

查询年龄为1开头的数据：select * from ages where age like '1%';
image.png

查询年龄第二位是1的数据：select * from ages where age like '_1%';
image.png

RLIKE子句是Hive中LIKE功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。
2.3.2 常用函数简介
(1) 字符串连接函数，concat
语法: concat(string A, string B...)
返回值: string
说明：返回输入字符串连接后的结果，支持任意个输入字符串
举例：select concat('abc','def','gh') from ages ;
image.png

（2）带分隔符字符串连接函数：concat_ws
语法: concat_ws(string SEP, string A, string B...)
返回值: string
说明：返回输入字符串，连接后的结果，SEP表示各个字符串间的分隔符
举例：select concat_ws(',','abc','def','gh') from ages ;
image.png

除此之外还可以将数组的形式转换为字符串concat_ws(string SEP, array)
举例：select concat_ws('|',array('ads','des','ss')) from ages ;
image.png

（3）集合去重函数：collect_set
语法: collect_set(col)
返回值: array
说明: 将col字段进行去重，合并成一个数组。
举例：cookies表
image.png

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

(4) UDTF一进多出
列转行：explode
语法: explode(ARRAY)
返回值: 多行
说明：将数组转换为多行
举例：select explode(array('ads','des','ss'))from ages;
image.png

除此之外还可以将map拆分为多行。
（5）UDAF多进一出
聚合函数count，sum，avg等
（6）窗口函数
一般和聚合函数配合使用
OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。
举例：
orders表
image.png
image.png

总结：over是对分的组得到结果的count ，因为搜到了4个人，所以count数为4。直接count是分组后对组中的数据进行count。

image.png
image.png
image.png
总结：添加partition by name 表示按照name分组进行sum求和。
(7) 排序函数
RANK() 排序相同时会重复，总数不会变
DENSE_RANK() 排序相同时会重复，总数会减少
ROW_NUMBER() 会根据顺序计算
举例：scores表
image.png

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;
得到结果：
image.png

（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
    二、数学运算：
  14. 加法操作: +
  15. 减法操作: –
  16. 乘法操作: *
  17. 除法操作: /
  18. 取余操作: %
  19. 位与操作: &
  20. 位或操作: |
  21. 位异或操作: ^
    9．位取反操作: ~

三、逻辑运算：

  1. 逻辑与操作: AND 、&&
  2. 逻辑或操作: OR 、||
  3. 逻辑非操作: NOT、!
    四、复合类型构造函数
  4. map结构
  5. struct结构
  6. named_struct结构
  7. array结构
  8. create_union
    五、复合类型操作符
  9. 获取array中的元素
  10. 获取map中的元素
  11. 获取struct中的元素
    六、数值计算函数
  12. 取整函数: round
  13. 指定精度取整函数: round
  14. 向下取整函数: floor
  15. 向上取整函数: ceil
  16. 向上取整函数: ceiling
  17. 取随机数函数: rand
  18. 自然指数函数: exp
  19. 以10为底对数函数: log10
  20. 以2为底对数函数: log2
  21. 对数函数: log
  22. 幂运算函数: pow
  23. 幂运算函数: power
  24. 开平方函数: sqrt
  25. 二进制函数: bin
  26. 十六进制函数: hex
  27. 反转十六进制函数: unhex
  28. 进制转换函数: conv
  29. 绝对值函数: abs
  30. 正取余函数: pmod
  31. 正弦函数: sin
  32. 反正弦函数: asin
  33. 余弦函数: cos
  34. 反余弦函数: acos
  35. positive函数: positive
  36. negative函数: negative
    七、集合操作函数
  37. map类型大小：size
  38. array类型大小：size
  39. 判断元素数组是否包含元素：array_contains
  40. 获取map中所有value集合
  41. 获取map中所有key集合
  42. 数组排序
    八、类型转换函数
  43. 二进制转换：binary
  44. 基础类型之间强制转换：cast
    九、日期函数
  45. UNIX时间戳转日期函数: from_unixtime
  46. 获取当前UNIX时间戳函数: unix_timestamp
  47. 日期转UNIX时间戳函数: unix_timestamp
  48. 指定格式日期转UNIX时间戳函数: unix_timestamp
  49. 日期时间转日期函数: to_date
  50. 日期转年函数: year
  51. 日期转月函数: month
  52. 日期转天函数: day
  53. 日期转小时函数: hour
  54. 日期转分钟函数: minute
  55. 日期转秒函数: second
  56. 日期转周函数: weekofyear
  57. 日期比较函数: datediff
  58. 日期增加函数: date_add
  59. 日期减少函数: date_sub
    十、条件函数
  60. If函数: if
  61. 非空查找函数: COALESCE
  62. 条件判断函数：CASE
  63. 条件判断函数：CASE
    十一、字符串函数
  64. 字符ascii码函数：ascii
  65. base64字符串
  66. 字符串连接函数：concat
  67. 带分隔符字符串连接函数：concat_ws
  68. 数组转换成字符串的函数：concat_ws
  69. 小数位格式化成字符串函数：format_number
  70. 字符串截取函数：substr,substring
  71. 字符串截取函数：substr,substring
  72. 字符串查找函数：instr
  73. 字符串长度函数：length
  74. 字符串查找函数：locate
  75. 字符串格式化函数：printf
  76. 字符串转换成map函数：str_to_map
  77. base64解码函数：unbase64(string str)
  78. 字符串转大写函数：upper,ucase
  79. 字符串转小写函数：lower,lcase
  80. 去空格函数：trim
  81. 左边去空格函数：ltrim
  82. 右边去空格函数：rtrim
  83. 正则表达式替换函数：regexp_replace
  84. 正则表达式解析函数：regexp_extract
  85. URL解析函数：parse_url
  86. json解析函数：get_json_object
  87. 空格字符串函数：space
  88. 重复字符串函数：repeat
  89. 左补足函数：lpad
  90. 右补足函数：rpad
  91. 分割字符串函数: split
  92. 集合查找函数: find_in_set
  93. 分词函数：sentences
  94. 分词后统计一起出现频次最高的TOP-K
  95. 分词后统计与指定单词一起出现频次最高的TOP-K
    十二、混合函数
  96. 调用Java函数：java_method
  97. 调用Java函数：reflect
  98. 字符串的hash值：hash
    十三、XPath解析XML函数
  99. xpath
  100. xpath_string
  101. xpath_boolean
  102. xpath_short, xpath_int, xpath_long
  103. xpath_float, xpath_double, xpath_number
    十四、汇总统计函数（UDAF）
  104. 个数统计函数: count
  105. 总和统计函数: sum
  106. 平均值统计函数: avg
  107. 最小值统计函数: min
  108. 最大值统计函数: max
  109. 非空集合总体变量函数: var_pop
  110. 非空集合样本变量函数: var_samp
  111. 总体标准偏离函数: stddev_pop
  112. 样本标准偏离函数: stddev_samp
    10．中位数函数: percentile
  113. 中位数函数: percentile
  114. 近似中位数函数: percentile_approx
  115. 近似中位数函数: percentile_approx
  116. 直方图: histogram_numeric
  117. 集合去重数：collect_set
  118. 集合不去重函数：collect_list
    十五、表格生成函数Table-Generating Functions (UDTF)
  119. 数组拆分成多行：explode
  120. Map拆分成多行：explode
文章标签：
云解析DNS
Java
自然语言处理
SQL
Unix
HIVE
数据格式
数据库
存储
JSON
XML
袋鼠云
目录
相关文章
诸葛子房
|
4月前
|
SQL HIVE
Hive LAG函数分析
Hive LAG函数分析
诸葛子房
36 0
让线程再跑一会
|
4月前
|
SQL JSON Java
Hive【Hive（四）函数-单行函数】
Hive【Hive（四）函数-单行函数】
让线程再跑一会
25 0
爱吃糖的范同学
|
1天前
|
SQL Java 数据处理
【Hive】Hive的函数：UDF、UDAF、UDTF的区别？
【4月更文挑战第17天】【Hive】Hive的函数：UDF、UDAF、UDTF的区别？
爱吃糖的范同学
27 6
让线程再跑一会
|
4月前
|
SQL HIVE 索引
Hive【Hive（五）函数-高级聚合函数、炸裂函数】
Hive【Hive（五）函数-高级聚合函数、炸裂函数】
让线程再跑一会
78 0
Cool架构
|
22天前
|
SQL Unix 数据挖掘
bigdata-21-Hive基本函数
bigdata-21-Hive基本函数
Cool架构
17 0
建模sister
|
1月前
|
SQL HIVE
Hive中日期处理函数的使用（date_format、date_add、date_sub、next_day）
Hive中日期处理函数的使用（date_format、date_add、date_sub、next_day）
建模sister
68 3
三分钟热度的鱼
|
2月前
|
SQL 消息中间件 Apache
Flink报错问题之使用hive udf函数报错如何解决
Apache Flink是由Apache软件基金会开发的开源流处理框架，其核心是用Java和Scala编写的分布式流数据流引擎。本合集提供有关Apache Flink相关技术、使用技巧和最佳实践的资源。
三分钟热度的鱼
24 1
星光下的赶路人
|
8月前
|
SQL HIVE
Hive学习---4、函数（单行函数、高级聚合函数、炸裂函数、窗口函数）（二）
Hive学习---4、函数（单行函数、高级聚合函数、炸裂函数、窗口函数）（二）
星光下的赶路人
99 0
Maynor
|
4月前
|
SQL JSON Unix
❤️Hive的基本知识(三)Hive中的函数大全❤️
❤️Hive的基本知识(三)Hive中的函数大全❤️
Maynor
40 0
赵广陆
|
7月前
|
SQL 大数据 Java
大数据Hive URL解析函数及侧视图
大数据Hive URL解析函数及侧视图
赵广陆
78 0

热门文章

最新文章

  • 1
    阿里云数据库内核 Apache Doris 兼容 Presto、Trino、ClickHouse、Hive 等近十种 SQL 方言，助力业务平滑迁移
  • 2
    利用Hive与Hadoop构建大数据仓库：从零到一
  • 3
    bigdata-17-Hive部署与Hive基础
  • 4
    hive学习笔记
  • 5
    bigdata-18-Hive数据结构与存储格式
  • 6
    bigdata-21-Hive基本函数
  • 7
    【Hive】Hive有索引吗？
  • 8
    bigdata-20-Hive内外部表
  • 9
    Hive【Hive学习大纲】【数据仓库+简介+工作原理】【自学阶段整理的xmind思维导图分享】【点击可放大看高清】
  • 10
    【Hive】sort by 和 order by 的区别
  • 1
    DataWorks报错问题之集成hive数据源报错如何解决
    32
  • 2
    DataWorks报错问题之从hive到mysql报错如何解决
    22
  • 3
    Flink CDC + Hudi + Hive + Presto构建实时数据湖最佳实践
    135
  • 4
    Apache Hudi与Hive集成手册
    61
  • 5
    DataWorks常见问题之hive jdbc加载数据错误如何解决
    32
  • 6
    Hive中日期处理函数的使用（date_format、date_add、date_sub、next_day）
    68
  • 7
    Hive窗口函数案例总结
    24
  • 8
    Sqoop【付诸实践 01】Sqoop1最新版 MySQL与HDFS\Hive\HBase 核心导入导出案例分享+多个WRAN及Exception问题处理（一篇即可学会在日常工作中使用Sqoop）
    89
  • 9
    Hive Delegation Token 揭秘
    45
  • 10
    Flink依赖问题之connector hive依赖冲突如何解决
    63

    • 相关课程

    更多
  • 大数据Hive教程精讲
  • SQL进阶及查询

    • 相关电子书

    更多
  • Comparison of Spark SQL with Hive
  • Hive Bucketing in Apache Spark
  • 2019大数据技术公开课第五季—Hive迁移到MaxCompute最佳实践

    • 相关实验场景

    更多
  • 使用SQL语句实现数据表管理
  • 使用SQL语句实现数据查询操作
  • 使用SQL语句管理索引
  • SQL进阶之子句、关键字和操作符
  • 一小时快速掌握 SQL 语法
  • SQL的增删改查及函数应用
    • 下一篇
    阿里云oss简介和使用流程