Hive 是基于 Hadoop 生态的一个重要组件, 是对数据仓库进行管理和分析数据的工具. 她提供了 SQL 查询方式来分析存储在 HDFS 分布式文件系统中的数据, 可以将结构化的数据文件映射为一张数据库表, 并提供完整的 SQL 查询功能.
这种 SQL 就是 Hive SQL, 她可以将 SQL 语句转换为 Map Reduce 任务运行, 通过特殊的 SQL 去查询分析需要的内容, 使不熟悉 map reduce 的用户很方便的利用 SQL 语言查询, 汇总, 分析数据.
一, 基础命令
1, 数据库操作
show databases; # 查看某个数据库
use 数据库; # 进入某个数据库
show tables; # 展示所有表
desc 表名; # 显示表结构
show partitions 表名; # 显示表名分区
show create table_name; # 显示创建表的结构
2, 表结构修改
- use xxdb; create table xxx; # 内部表
- create table xxx like xxx; # 创建一个表, 结构与其他一样
- use xxdb; create external table xxx; # 外部表
- use xxdb; create external table xxx (l int) partitoned by (d string); # 分区表
- alter table table_name set TBLPROPROTIES ('EXTERNAL'='TRUE'); # 内部表转外部表
- alter table table_name set TBLPROPROTIES ('EXTERNAL'='FALSE');# 外部表转内部表
3, 字段类型
基本类型: tinyint, smallint, int, bigint, float, decimal, boolean, string
复合类型: struct, array, map
二, 常用函数
- length() # 返回字符串长度
- trim() # 去除两边空格
- lower(), upper() # 大小写转换
- reverse() # 反转字符串
- cast(expr as type) # 类型转换
- substring(string A, int start, int len) # 字符串截取
- split(string str, string pat) # 按照 pat 字符串分割 str, 返回分割后的字符串数组
- coalesce(v1, v2, v3, ...) # 返回列表中第一个非空元素, 如果所有值都为空, 则返回 null
- from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') # 返回当前时间
- instr(string str, string search_str) # 返回第二个参数在待查找字符串中的位置(找不到返回 0)
- concat(string A, string B, string C, ...) # 字符串连接
- concat_ws(string sep, string A, string B, string C, ...) # 自定义分隔符 sep 的字符串连接
- str_to_map(string A, string item_pat, string dict_pat) # 将字符串转为 map
- map_keys(map m) # 提取出 map 的 key, 返回 key 的 array
- datediff(date1, date2) # 日期比较函数, 返回相差天数, datediff('${
- cur_date
- },d)
- explode(colname) # explode 就是将 hive 一行中复杂的 array 或者 map 结构拆分成多行
三, 相关概念
1,hive
hive 是基于 hadoop 的一个数据仓库工具, 可以将结构化的数据文件映射为一张数据库库表, 并提供类 SQL 查询功能.
2, 基本组成
用户接口: CLI,shell 命令行; JDBC/ODBC 是 hive 的 java 实现; webGUI 是通过浏览器访问 hive; 元数据存储: 通常是存储在关系数据库如 MySQL, derby 中; hive 的元数据包括表的名字, 表的列和分区及其属性, 表的属性(是否为外部表), 表的数据所在目录等.
解释器, 编译器, 优化器完成 HQL 查询语句从词法分析, 语法分析, 编译, 优化以及查询计划的生成. 生成的查询存储在 HDFS 中, 并随后有 mapreduce 调用执行. 因此, hive 与 Hadoop 的关系可以理解为用户发出 SQL 查询语句, hive 将查询存储在 HDFS 中, 然后由 mapreduce 调用执行.
3,table
Hive 中的 Table 和数据库中的 Table 在概念上是类似的, 每一个 Table 在 Hive 中都有一个相应的目录存储数据. 例如, 一个表 pvs, 它在 HDFS 中的路径为:/wh/pvs, 其中, wh 是在 hive-site.xml 中由 ${hive.metastore.warehouse.dir} 指定的数据仓库的目录, 所有的 Table 数据 (不包括 External Table) 都保存在这个目录中.
4,partition
Partition 对应于数据库中的 Partition 列的密集索引, 但是 Hive 中 Partition 的组织方式和数据库中的很不相同. 在 Hive 中, 表中的一个 Partition 对应于表下的一个目录, 所有的 Partition 的数据都存储在对应的目录中.
5,buckets
Buckets 对指定列计算 hash, 根据 hash 值切分数据, 目的是为了并行, 每一个 Bucket 对应一个文件. 将 user 列分散至 32 个 bucket, 首先对 user 列的值计算 hash, 对应 hash 值为 0 的 HDFS 目录为:/wh/pvs/ds=20090801/ctry=US/part-00000;hash 值为 20 的 HDFS 目录为:/wh/pvs/ds=20090801/ctry=US/part-00020
6,external table
External Table 指向已经在 HDFS 中存在的数据, 可以创建 Partition. 它和 Table 在元数据的组织上是相同的, 而实际数据的存储则有较大的差异.
Table 的创建过程和数据加载过程(这两个过程可以在同一个语句中完成), 在加载数据的过程中, 实际数据会被移动到数据仓库目录中; 之后对数据对访问将会直接在数据仓库目录中完成. 删除表时, 表中的数据和元数据将会被同时删除.
External Table 只有一个过程, 加载数据和创建表同时完成(CREATE EXTERNAL TABLE ......LOCATION), 实际数据是存储在 LOCATION 后面指定的 HDFS 路径中, 并不会移动到数据仓库目录中. 当删除一个 External Table 时, 仅删除元数据, 表中的数据不会真正被删除.
7, 全量数据和增量数据
查看分区信息: 如果分区的大小随时间增加而增加, 则最新的分区为全量数据. 如果分区的大小随时间增加而大小上下变化, 则每个分区都是增量数据.
四, HQL 和 SQL 的异同
1,HQL 和 SQL 常见不同,
select distinct 后必须指定字段名
join 条件仅支持等值关联且不支持 or 条件
子查询不能在 select 中使用;
HQL 中没有 UNION, 可使用 distinct+ union all 实现 UNION;
HQL 以分号分隔, 必须在每个语句结尾写上分号;
HQL 中字符串的比较比较严格, 区分大小写及空格, 因此在比较时建议 upper(trim(a))=upper(trim(b))
日期判断, 建议使用 to_date(), 如: to_date(orderdate)='2016-07-18'
关键字必须在字段名上加 `` 符号, 如 select `exchange` from xxdb.xxtb;
数据库和表 / 视图之间仅有 1 个点, 如 xx_db.xx_tb.
2,HQL 不支持 update, 采用 union all + left join (is null)变相实现 update.
取出增量数据;
使用昨日分区的全量数据通过主键左连接增量数据, 并且只取增量表中主键为空的数据(即, 取未发生变化的全量数据);
合并 1,2 的数据覆盖至最新的分区, 即实现了 update.
3,HQL 不支持 delete, 采用 not exists/left join(is null)的方法变相实现.
取出已删除的主键数据(表 B);
使用上一个分区的全量数据 (表 A) 通过主键左连接 A, 并且只取 A 中主键为空的数据, 然后直接 insert overwrite 至新的分区.
对于会 SQL 的人员, 转入 Hive SQL 还是比较容易的, 语法大部分是想通的, 少部分函数不太一致.
来源: http://bigdata.51cto.com/art/201908/600847.htm