一、Hive 概述
二、Hive 数据类型和文件格式
2.1 数据类型
2.1.1 基本数据类型
| 类别 | 类型 |
|---|---|
| 整型 Integers | TINYINT - 1 个字节的有符号整型 SAMLINT - 2 个字节有符号整型 INT - 4 个字节有符号整型 BIGINT - 8 个字节有符号整型 |
| 浮点数 Floating point numbers | FLOAT - 单精度浮点数 DOUBLE - 双精度浮点数 |
| 定点数 Fixed point numbers | DECIMAL - 17 字节，任意精度数字 |
| 字符串 String | STRING - 不定长字符串 VARCHAR - (1 - 65535)长度的不定长字符串 CHAR - (1 - 255) 定长字符串 |
| 时间日期 Datetime | TIMESTAMP - 时间戳，纳秒级 DATE - 时间日期类型 |
| 布尔类型 Boolean | BOOLEAN |
| 二进制类型 Binary types | BINARY 二进制字节序列 |
Hive 中的基本数据类型都是通过 java 中的接口实现的，各类型与 java 数据类型的对应关系：
| Hive 数据类型 | java 数据类型 |
|---|---|
| TINYINT | byte |
| SAMLINT | short |
| INT | int |
| BIGINT | long |
| FLOAT | float |
| DOUBLE | double |
| DECIMAL | |
| STRING | String |
| VARCHAR | |
| CHAR | |
| TIMESTAMP | |
| DATE | |
| BOOLEAN | boolean |
| BINARY |
2.1.2 数据类型隐式转化
规律：
-
底层逻辑：转换后数据精度不丢失
-
任何 整数类型 都可以隐式转换为范围更广的类型
-
所有 整数类型、float、string(数字), 都可以隐式转换为 double
-
整型 都能隐式转换为 float
-
boolean 不能隐式转换
2.1.3 集合数据类型
-
array
-
map
-
struct
-
union
| 集合类型 | 描述 | 示例 | 索引方式 |
|---|---|---|---|
| array | 有序的，数据类型相同的，集合 | array(1, 2) | [index] |
| map | kv 键值对，key 必须是基本数据类型，value 不限制 | map(‘a’, 1, ‘b’, 2) | [key] |
| struct | 不同类型的, 集合, 类似 c 语言的结构体 | struct名.字段名 | |
| union | 不同类型的数据，但存储在同一字段的不同行中 |
hive (default)> select array(1, 2, 3);
OK
_c0
[1,2,3]
-- 通过 [下标] 索引
hive (default)> select arr[1] from (select array(1, 2, 3) arr) tmp;
OK
_c0
2
hive (default)> select map('a', 1, 'b', 2);
OK
_c0
{"a":1,"b":2}
-- 通过 [key] 索引
hive (default)> select tmap['b'] from (select map('a', 1, 'b', 2) tmap) tmp;
OK
_c0
2
-- struct 没有字段名时，默认字段名为 col_x
hive (default)> select struct('username1', 7, 1288.68);
OK
_c0
{"col1":"username1","col2":7,"col3":1288.68}
-- name_struct() 可以指定字段名
hive (default)> select named_struct("name", "username1", "id", 7, "salary", 12880.68);
OK
_c0
{"name":"username1","id":7,"salary":12880.68}
-- 通过 列名.字段名 的方式 索引
hive (default)> select userinfo.id, userinfo.name from (select named_struct("name", "username1", "id", 7, "salary", 12880.68) userinfo) tmp;
OK
id name
7 username1
2.2 文本文件的数据编码
Hive 表中的数据都存储在 HDFS 上，其定义了默认的存储格式，也支持自定义存储格式
用户定义数据格式需要指定三个属性:
-
列分隔符(通常为空格、"\t"、"\x001")
-
行分隔符("\n")
-
读取文件数据的方法。
2.2.1 默认存储格式的默认分隔符
| 分隔符 | 名称 | 说明 |
|---|---|---|
| \n | 换行符 | 分割行，每行一条记录 |
| ^A | 字段分割符 | 分割字段 |
| ^B | 元素分隔符 | 分割 array, map, struct 中的元素 |
| ^C | kv分隔符 | 分割 map 中的 key 和 value |
案例：
学生表
-- 字段和数据
id name age hobby(array) score(map)
666 thomas 18 read game java 90 hadoop 95
-- 加上分隔符
666 ^A thomas ^A 18 ^A read ^B game ^A java ^C 90 ^B hadoop ^C 95
-- 建表语句
create table s1 (
id int,
name string,
age int,
hobby array<string>,
score map<string, int>
);
-- 加载数据命令
load data local inpath '/home/hadoop/data/s1.data' into table s1;
2.2.2 读时模式
读取数据时才检查数据是否符合表的定义
Hive 采用 读时模式 schema on read , 加载数据时不进行数据格式的校验，读取数据时，如果不合法，则显示 Null
该模式的优点是写入、加载数据迅速
三、DDL
3.1 数据库操作
创建数据库
– 创建数据库，在HDFS上存储路径为 /user/hive/warehouse/*.db
-- 语法
CREATE DATABASE database_name IF NOT EXISTS database_name
COMMENT 'comments'
LOCATION 'hdfs_path'
;
查看数据库
-- 查看所有数据库
SHOW DATABASES;
-- 查看数据库信息
DESC DATABASE database_name;
DESC DATABASE EXTENDED database_name;
使用数据库
USE database_name
删除数据库
-- 删除空数据库
DROP DATABASE database_name;
-- 删除不为空的数据库
DROP DATABASE database_name CASCADE;
3.2 建表语句
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
[(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])]
[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];
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
LIKE existing_table_or_view_name
[LOCATION hdfs_path];
-
EXTERNAL 关键字，创建外部表，否则创建内部表
删除内部表时，数据和表同时被删除
删除外部表时，只删除表
-
COMMENT 表注释
-
PARTITION BY 分区
-
CLUSTERED BY 分桶
-
SORTED BY 对桶表中的一个或多个字段排序
-
存储语句
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, ...)] -
SRORED AS SEQUENCEFILE(二进制序列文件，用于压缩) | TEXTFILE(默认) | RCFILE
-
LOCATION 表在 HDFS 上的存放位置
-
TBLPROPERTIES 表的属性
-
AS 表示根据其后面的查询语句的查询结果创建表
-
LIKE 用于复制现有的表结构，但是不复制数据
3.3 内部表 与 外部表
-
默认情况下，创建内部表
-
EXTERNAL 关键字，用于创建外部表
-
内部表可以转换为外部表
-
删除内部表时，表和数据同时被删除
-
删除外部表时，只删除表
-
生产环境中，多使用外部表
案例：
数据
2;zhangsan;book,TV,code;beijing:chaoyang,shagnhai:pudong
3;lishi;book,code;nanjing:jiangning,taiwan:taibei
4;wangwu;music,book;heilongjiang:haerbin
内部表
-- 创建内部表
CREATE TABLE t1 (
id INT,
name STRING,
hobby ARRAY<STRING>,
addr MAP<STRING, STRING>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ";"
COLLECTION ITEMS TERMINATED BY ","
MAP KEYS TERMINATED BY ":"
;
-- 显示表的定义
DESC t1;
-- 显示详细信息
DESC FORMATTED t1;
-- 加载数据
LOAD DATA LOCAL INPATH '/home/hadoop/data/t1.dat' INTO TABLE t1;
-- 查询数据文件
dfs -ls /user/hive/warehouse/mydb.db/t1;
-- 删除表, 由于是内部表，表和数据同时被删除
DROP TABLE t1;
外部表
-- 创建内部表
CREATE EXTERNAL TABLE t2 (
id INT,
name STRING,
hobby ARRAY<STRING>,
addr MAP<STRING, STRING>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ";"
COLLECTION ITEMS TERMINATED BY ","
MAP KEYS TERMINATED BY ":"
;
-- 显示表的定义
DESC t2;
-- 显示详细信息
DESC FORMATTED t2;
-- 加载数据
LOAD DATA LOCAL INPATH '/home/hadoop/data/t1.dat' INTO TABLE t2;
-- 查询数据文件
dfs -ls /user/hive/warehouse/mydb.db/t2;
-- 删除表, 由于是外部表，表被删除, 数据还在
DROP TABLE t2;
内部表 与 外部表的转换
-- 内部表转换为外部表
ALTER TABLE t1 SET TBLPROPERTIES('EXTERNAL'='TRUE');
-- 外部表转换为内部表
ALTER TABLE t1 SET TBLPROPERTIES('EXTERNAL'='FALSE');
3.4 分区表
Hive 查询时，会扫描整个表的数据，但是表的数据量大会导致全表扫描消耗资源大，效率低
Hive 中的分区表，将表的数据存储在不同的子目录中，每个目录对应一个分区，当查询只需要扫描部分数据时，可以使用分区表，提高查询效率
通常根据时间、地区等信息进行分区
创建分区表
CREATE TABLE IF NOT EXISTS t3 (
id INT,
name STRING,
hobby ARRAY<STRING>,
addr MAP<STRING, STRING>
)
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ";"
COLLECTION ITEMS TERMINATED BY ","
MAP KEYS TERMINATED BY ":"
;
-- 加载数据
LOAD DATA LOCAL INPATH '/home/hadoop/data/t1.dat' INTO TABLE T3 PARTITION(dt='2020-06-01');
LOAD DATA LOCAL INPATH '/home/hadoop/data/t1.dat' INTO TABLE T3 PARTITION(dt='2020-06-02');
分区字段不是表中已经存在的数据，可以将分区字段看成虚拟列
查看分区
SHOW PARTITIONS t3;
新增分区并设置数据
-- 增加分区，不加载数据
ALTER TABLE t3 ADD PARTITION(dt='2020-06-03');
-- 增加多个分区
ALTER TABLE t3 ADD PARTITION(dt='2020-06-04') PARTITION(dt='2020-06-05');
-- 增加分区，并加载数据
-- 准备数据
hdfs dfs -cp /user/hive/warehouse/mydb.db/t3/dt=2020-06-01 /user/hive/warehouse/mydb.db/t3/dt=2020-06-07
hdfs dfs -cp /user/hive/warehouse/mydb.db/t3/dt=2020-06-01 /user/hive/warehouse/mydb.db/t3/dt=2020-06-08
hdfs dfs -cp /user/hive/warehouse/mydb.db/t3/dt=2020-06-01 /user/hive/warehouse/mydb.db/t3/dt=2020-06-06
-- 增加分区，指定分区位置，完成数据加载
ALTER TABLE t3 ADD
PARTITION(dt='2020-06-07') LOCATION '/user/hive/warehouse/mydb.db/t3/dt=2020-06-07'
PARTITION(dt='2020-06-08') LOCATION '/user/hive/warehouse/mydb.db/t3/dt=2020-06-08'
;
修改分区的 HDFS 路径
ALTER TABLE t3 PARTITION(dt='2020-06-04') SET LOCATION '/user/hive/warehouse/mydb.db/t3/dt=2020-06-08';
删除分区
-- 删除一个 或者 多个，用逗号隔开
ALTER TABLE t3 DROP
PARTITION(dt='2020-06-03'),
PARTITION(dt='2020-06-05')
;
3.5 分桶表
当单个的分区或者表的数据量过大，分区不能更细粒度的划分数据，就需要使用分桶 技术将数据划分成更细的粒度。将数据按照指定的字段进行分成多个桶中去，即将数 据按照字段进行划分，数据按照字段划分到多个文件当中去
-
MR 中: key.hashCode % reductTask
-
Hive 中: 分桶字段.hashCode % 分桶个数
测试数据
java 90
1 c 78
1 python 91
1 hadoop 80
2 java 75
2 c 76
2 python 80
2 hadoop 93
3 java 98
3 c 74
3 python 89
3 hadoop 91
5 java 93
6 c 76
7 python 87
8 hadoop 88
创建分桶表
CREATE TABLE course (
id INT,
name STRING,
score INT
)
CLUSTERED BY (id) INTO 3 BUCKETS
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
;
-- 创建普通表
CREATE TABLE course_common (
id INT,
name STRING,
score INT
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
;
-- 普通表加载数据
LOAD DATA LOCAL INPATH '/home/hadoop/data/course.dat' INTO TABLE course_common;
-- 分桶表加载数据, 得通过普通表加载
INSERT INTO TABLE course SELECT * FROM course_common;
分桶规则
-
分桶字段.hashcode % 分桶数
-
分桶加载数据是通过普通表加载的，INSERT … SELECT …
3.6 修改表 与 删除表
表名重命名
ALTER TABLE course_common RENAME TO course_common1;
字段名重命名
ALTER TABLE course_common CHANGE COLUMN id cid INT;
-- 也可以转换字段类型，但是注意类型转换原则
ALTER TABLE course_common CHANGE COLUMN id cid STRING;
增加字段
ALTER TABLE course_common ADD COLUMNS (common STRING);
删除字段
ALTER TABLE course_common REPLACE COLUMNS (id STRING, cname STRING, score INT)
删除表
DROP TABLE course_common;
四、数据操作
4.1 数据导入
语法:
LOAD DATA [LOCAL] INPATH 'filepath'
[OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
-
LOCAL 从本地文件上传到 HDFS 再添加进表中，否则从 HDFS 上移动到 Hive 指定的目录
-
OVERWRITE 覆盖原有数据，否则追加数据
-
PARTITION 分区
或者在创建表时，添加 LOCATION 参数，指定数据路径，完成创建表时导入数据
插入数据
-- 插入数据
INSERT INTO TABLE tabC
PARTITION(month='202001')
VALUES (5, 'wangwu', 'BJ'), (4, 'lishi', 'SH'), (3, 'zhangsan', 'TJ');
-- 插入查询的结果数据
INSERT INTO TABLE tabC
PARTITION(month='202002')
SELECT id, name, area FROM tabC WHERE month='202001';
-- 多表，多分区 插入
FROM tabC
INSERT TABLE tabC
PARTITION(month='202003')
SELECT id, name, area WHERE month='202002'
INSERT TABLE tabC
PARTITION(month='202004')
SELECT id, name, area WHERE month='202002'
;
创建表 并插入数据
-- 根据查询结果创建表
CREATE TABLE IF NOT EXISTS tabD
AS SELECT * FROM tabC;
import 导入数据
IMPORT TABLE student2 PARTITION(month='202009')
FROM '/user/hive/warehouse/export/student';
4.2 数据导出
查询结果导出到本地
INSERT OVERWRITE LOCAL DIRECTORY '/home/hadoop/data/tabC'
SELECT * FROM tabC;
查询结果格式化后 导出到本地
INSERT OVERWRITE LOCAL DIRECTORY '/home/hadoop/data/tabC'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ' '
SELECT * FROM tabC;
查询结果格式化后 导出到HDFS
去掉 LOCAL 关键字, 将输出路径改为 HDFS 文件路径
INSERT OVERWRITE DIRECTORY '/user/hadoop/data/course'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ' '
SELECT * FROM course;
dfs 命令到处数据导本地，其实是 hdfs 文件拷贝到本地的 get 命令
dfs -get 'hdfs_path' 'local_path';
hive 命令，导出数据到本地, 查询结果重定向到文件
hive -e "SELECT * FROM course" > a.log
-- 如果没有开启 Hive, 则需要指定数据库名
hive -e "SELECT * FROM mydb.course" > a.log
export 导出数据到 HDFS, 此方式还包含表的元数据信息
EXPORT TABLE tabC TO 'hdfs_path';
export 导出的数据，可以用 import 命令导入到 表中
-- LIKE 让 表的结构一致
CREATE TABLE tabE LIKE tabC;
IMPORT TABLE tabE 'hdfs_path';
清空表，仅限内部表
```sql
TRUNCATE TABLE table_name;
```
五、DQL
语法：
[WITH CommonTableExpression (, CommonTableExpression)*] (Note: Only available starting with Hive 0.13.0)
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT [offset,] rows]
注意
-
WHERE 子句中不能使用列的别名
-
WHERE 子句紧随 FROM 子句
-
正则表达式用 RLIKE 或者 REGEXP
-
WHERE 子句不能有分组函数, HAVING 子句可以有分组函数
-
HAVING 只能用于 GROUP BY 分组统计后
WHERE 子句针对表中的数据发挥作用
HAVING 针对查询结果(聚组以后的结果) 发挥作用
-
笛卡尔积
在以下条件会产生笛卡尔积
-
没有连接条件
-
连接条件无效
-
所有表中的所有行互相连接
-
-
ORDER BY - 全局排序，对最终的结果进行排序，所以出现在 SELECT 语句的结尾
只有一个 reduce
最低0.47元/天 解锁文章
453
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
