1. 数据库操作
- 创建数据库
create database if not exists myhive; - 查看数据库详细信息
desc database myhive; - 数据库本质上就是在HDFS之上的文件夹
- 默认数据库的存放路径是HDFS的：/user/hive/warehouse内
- 创建数据库并指定hdfs存储位置
create database myhive2 location '/myhive2'; - 删除一个空数据库
drop database myhive; - 强制删除数据库
drop database myhive2 cascade; 2. 数据表操作
2.1 表操作语法和数据类型
略
2.2 内部表操作
- 内部表和外部表
内部表（CREATE TABLE table_name …）
未被external关键字修饰的即是内部表， 即普通表。 内部表又称管理表,内部表数据存储的位置由hive.metastore.warehouse.dir参数决定（默认：/user/hive/warehouse），删除内部表会直接删除元数据（metadata）及存储数据，因此内部表不适合和其他工具共享数据。
外部表（CREATE EXTERNAL TABLE table_name …LOCATION…）
被external关键字修饰的即是外部表， 即关联表。
外部表是指表数据可以在任何位置，通过LOCATION关键字指定。 数据存储的不同也代表了这个表在理念是并不是Hive内部管理的，而是可以随意临时链接到外部数据上的。
- 内部表和外部表对比
- 创建内部表
- 创建表
create table table_name…… - 查看表的数据
- 自定义指定分隔符
create table if not exists stu2(id int ,name string) row format delimited fields terminated by '\t'; - 删除内部表
drop table table_name 2.3 外部表操作
- 创建外部表语法
- create external table ……
- 必须使用row format delimited fields terminated by指定列分隔符
- 必须使用location指定数据路径
- 外部表和其数据相互独立
- 可以先有表后有数据
- 也可以先有数据，后有表
- 表和数据只是一个链接关系
- 所以删除表，表不存在了但数据保留。
- 内外部表转换
- 内转外：stu set tblproperties(‘EXTERNAL’=‘TRUE’)
- 外转内：stu set tblproperties(‘EXTERNAL’=‘FALSE’)
2.4 数据加载和导出
- 外部表是临时使用的，重要数据建议存入内部表进行管理
- 加载数据的语法
- load data [local] inpath ‘filepath’ [overwrite] into table tablename;
- insert into | overwrite select ……
- 注意事项
- 数据来源本地，本地数据会保留，本质是本地文件上传到HDFS
- 数据来自HDFS，加载后文件不存在，本质是在HDFS上进行文件移动
- Hive数据导出的方式
- insert [local] select
- bin/hive -e ‘SQL’ > file
- bin/hive -f ‘sql file’ > file
2.5 分区表
- 分区表的定义
- 可以选择字段作为表分区
- 分区其实就是HDFS上的不同文件夹
- 分区表可以极大的提高特定场景下Hive的操作性能
- 分区表的语法
- create table tablename(…) partitioned by (分区列 列类型)
- 分区表的分区列，在partitioned by中定义，不在普通列中定义
2.6 分桶表
- 分桶表定义
- 可以选择字段作为分桶字段
- 分桶表本质上是数据分开在不同的文件中
- 分区和分桶可以同时使用
- 分桶表的语法
- 通过clustered by(c_id) into 3 buckets
- clustered by 指定分桶字段
- into num buckets指定分桶数量
- 分桶表能带来什么性能提升
- 单值过滤
- JOIN
- GROUP BY
2.7 修改表
- 表重命名
alter table old_table_name rename to new_table_name; - 修改表属性值
ALTER TABLE table_name SET TBLPROPERTIES table_properties; - 添加分区
ALTER TABLE tablename ADD PARTITION (month='201101'); - 修改分区值
ALTER TABLE tablename PARTITION (month='202005') RENAME TO PARTITION (month='201105'); - 删除分区
ALTER TABLE tablename DROP PARTITION (month='201105'); - 添加列
ALTER TABLE table_name ADD COLUMNS (v1 int, v2 string); - 修改列名
ALTER TABLE test_change CHANGE v1 v1new INT; - 删除表
DROP TABLE tablename; - 清空表
TRUNCATE TABLE tablename; 2.8 复杂类型操作
2.8.1 Array数组
- array类型，主要存储数组格式
- 定义格式
- array<类型>
- 数组元素之间的分隔符：collection items terminated by ‘分隔符’
- 在查询中使用
- 数组[数字序号]，可以取出指定需要元素
- size(数组)，可以统计数组元素个数
- array contains(数组，数据)，可以查看指定数据是否在数组中存在
2.8.2 Map映射
- map类型，主要存储：K-V键值对类型数据
- 定义格式
- map<key类型，value类型>
- 不同键值对之间：collection items terminated by ‘分隔符’
- 一个键值对内：map keys terminated by ‘分隔符’
- 在查询中使用
- map[key]来获取指定key的值
- map_keys(map)取到全部的key作为array返回，map_values(map)取到全部的values
- size(map)可以统计K-V对的个数
array_contains(map_values(map)，数据)可以统计map是否包含指定数据
2.8.3 Struct结构
struct类型，主要存储：复合格式
定义格式
structname:string，age:int
struct的分隔符只需要：collection items terminated by ‘分隔符’
- 在查询中使用
- struct.key即可取得对应的value
3. 数据查询
3.1 基本查询
Hive中使用基本查询SELECT、WHERE、GROUP BY、聚合函数、HAVING、JOIN和普通的SQL语句没有区别
3.2 RLIKE 正则匹配
可以基于正则表达式，对数据内容进行匹配
3.3 UNION联合
- 将多个SELECT的结果集合并成一个
- 多个SELECT的结果集需要架构一致
- 自带去重效果，如无需去重，使用UNION ALL 即可
3.4 Sampling采样
桶抽样方式：tablesample(bucket x out of y on (colname | rand()))
块抽样方式：tablesample(num rows | num percent | num(K|M|G))
3.5 Virtual Columns虚拟列
- 虚拟列
- INPUT__FILE__NAME，显示数据行所在的具体文件
- BLOCK__OFFSET__INSIDE__FILE，显示数据行所在文件的偏移量
- ROW__OFFSET__INSIDE__BLOCK，显示数据所在HDFS块的偏移量（此虚拟列需要设置：SET hive.exec.rowoffset=true ）才可使用
- 作用
- 查看行级别的数据详细参数
- 可以用于where、group by等
- 可以协助进行错误排查工作
4. 函数
略
5. 案例
略
