本文详细介绍了Hive的基础概念，包括数据模型、数据类型、文件格式和压缩格式，以及数据操作分类。重点讲解了HiveSQL的DDL（如建表、分区、视图创建）和DML（数据加载、插入、更新、删除）。同时，文章讨论了Hive的数据加载，包括从本地和HDFS加载数据，以及动态分区模式。最后，文章提到了Hive的DQL查询，包括基本查询、JOIN操作和UNION用法，并给出了重要注意事项，如Hive支持等值连接和NULL值判断等。
一. Hive基础概念
1. hive数据模型
2. 数据类型
2.1 数值型
| 类型 | 说明 |
| TINYINT | 1-byte signed integer from -128 to 127 |
| SMALLINT | 2-byte signed integer from -32,768 to 32,767 |
| INT INTEGER | 4-byte signed integer from -2,147,483,648 to 2,147,483,647 |
| BIGINT | 8-byte signed integer from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 |
| FLOAT | 4-byte single precision floating point number |
| DOUBLE | 8-byte double precision floating point number PRECISION |
| DECIMAL | Decimal datatype was introduced in Hive0.11.0 (HIVE-2693) and revised in Hive 0.13.0 (HIVE-3976) |
2.2 日期类型
| 类型 | 说明 |
| TIMESTAMP | UNIX时间戳和可选的纳秒精度 |
| DATE | 描述特定的年/月/日，格式为YYYY-MM-DD |
2.3 字符串
| 类型 | 说明 |
| string | 最常用的字符串格式，等同于java String |
| varchar | 变长字符串，hive用的较多，最长为65535 |
| char | 定长字符串，比varchar更多一些，一般不要超过255个字符 |
2.4 布尔类型
| 类型 | 说明 |
| boolean | 等同于java的boolean用的很少 |
2.5 字节数组
| 类型 | 说明 |
| binary | 字节数组类型，可以存储任意类型的数据用的很少 |
2.6 复杂(集合)数据类型
| 数据类型 | 描述 | 字面语法示例 |
| STRUCT | 和C语言中的struct或者”对象”类似，都可以通过”点”符号访问元素内容。例如，如果某个列的数据类型是STRUCT{first STRING, lastdt STRING}，那么第1个元素可以通过字段名.first来引用 | struct( ‘John’, ‘Doe’) |
| MAP | MAP是一组键-值对元组集合，使用数组表示法(例如[‘key’])可以访问元素。例如，如果某个列的数据类型是MAP，其中键->值对是’first’->’John’和’last’->’Doe’，那么可以通过字段名[‘last’]获取值’Doe’ | map(‘first’, ‘John’, ‘last’, ‘Doe’) |
| ARRAY | 数组是一组具有相同类型的变量的集合。这些变量称为数组的元素，每个数组元素都有一个编号，编号从零开始。例如，数组值为[‘John’, ‘Doe’]，那么第1个元素可以通过数组名[0]进行引用 | ARRAY( ‘John’, ‘Doe’) |
3. hive数据文件格式和压缩格式
- 文件格式
文件格式按面向的存储形式不同，分为面向行和面向列两大类文件格式。
| 面向行/列类型 | 类型名称 | 是否可切割计算 | 优点 | 缺点 | 适用场景 |
| 面向行 | 文本文件格式(.txt) | 可以 | 查看、编辑简单 | 无压缩占空间大、传输压力大、数据解析开销大 | 学习练习使用 |
| 面向行 | SequenceFile序列文件格式(.seq) | 可以 | 自支持、二进制kv存储、支持行和块压缩 | 本地查看不方便：小文件合并成kv结构后不易查看内部数据 | 生产环境使用、map输出的默认文件格式 |
| 面向列 | rcfile文件格式(.rc) | 可以 | 数据加载快、查询快、空间利用率高、高负载能力 | 每一项都不是最高 | 学习、生产均可 |
| 面向列 | orcfile文件格式(.orc) | 可以 | 兼具rcfile优点、进一步提高了读取、存储效率、新数据类型的支持 | 每一项都不是最高 | 学习、生产均可 |
- 压缩格式
压缩格式按其可切分独立性，分成可切分和不可切分两种。
| 可切分性 | 类型名称 | 是否原生支持 | 优点 | 缺点 | 适用场景 |
| 可切分 | lzo(.lzo) | 否 | 压缩/解压速度快 合理的压缩率 | 压缩率比gzip低 不原生、需要native安装 | 单个文件越大，lzo优点越越明显。压缩完成后>=200M为宜 |
| 可切分 | bzip2(.bz2) | 是 | 高压缩率超过gzip 原生支持、不需要native安装、用linux bzip可解压操作 | 压缩/解压速率慢 | 处理速度要求不高、要求高压缩率（冷数据处理经常使用） |
| 不可切分 | gzip(.gz) | 是 | 压缩/解压速度快 原生/native都支持使用方便 | 不可切分、对CPU要求较高 | 压缩完成后<=130M的文件适宜 |
| 不可切分 | snappy(.snappy) | 否 | 高速压缩/解压速度 合理的压缩率 | 压缩率比gzip低 不原生、需要native安装 | 适合作为map->reduce或是job数据流的中间数据传输格式 |
4. 数据操作分类
| 操作分类 | 具体操作 | sql备注 |
| DDL | •创建数据库 •建/删除表 •修改表结构 •创建/删除视图 •显示命令 | Create/Drop/Alter Database Create/Drop/Truncate Table Alter Table/Partition/Column Create/Drop/Alter View Create/Drop Index Create/Drop Function Show functions; Describe function; |
| DML | •数据插入(insert,load) | load data...into table insert overwrite table |
| DQL | •数据查询(select) |
二. HiveSQL
按数据操作分类，来分别说明脚本的使用。
1. DDL
1.1 建表说明
- 元数据:描述数据的数据
- 表分类：主要分内表和外表
- 内表：元数据和数据本身均被hive管理。删除表则全部删除。
- 外表：元数据被hive管理，数据本身存储在hdfs,不受hive管理。删除表则只删除元数据，数据本身不变。
1.2 建表模板
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]
- 关键词解释
- external: 创建内部表还是外部表，此为内外表的唯一区分关键字。
- comment col_comment: 给字段添加注释
- comment table_comment: 给表本身添加注释
- partitioned by: 按哪些字段分区，可以是一个，也可以是多个
- clustered by col_name... into num_buckets BUCKETS:按哪几个字段做hash后分桶存储
- row format:用于设定行、列、集合的分隔符等设置
- stored as : 用于指定存储的文件类型，如text,rcfile等
- location : 设定该表存储的hdfs目录，如果不手动设定，则采用hive默认的存储路径
1.3 示例
创建学生表student,包括id,name,classid,classname及分区和注释信息。
CREATE TABLE student(
id string comment '学号',
username string comment '姓名',
classid int comment '班级id',
classname string comment '班级名称'
)
comment '学生信息主表'
partitioned by (come_date string comment '按入学年份分区')
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\001'
LINES TERMINATED BY '\n'
STORED AS textfile;
- 查看已存在表的详细信息
show create table或者desc tablename/desc formatted tablename
- 显示所有表
- 更改表
- 增加字段
再次通过：show create table student2;确认表结构。
- 创建视图(虚表)
视图：本身不存储实际数据，只存储表关系，使用时再去通过关系查找数据。
查看所有视图：show views;
- 删除视图
2. DML
2.1 加载数据脚本
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
2.2 加载本地数据文件(新版本的hive已不支持本地数据文件加载到表中)
- 创建一个文本文件存储的表，并以"\t"作为分隔符，方便构造和上传数据
CREATE TABLE student(
id string comment '学号',
username string comment '姓名',
classid int comment '班级id',
classname string comment '班级名称'
)
comment '学生信息主表'
partitioned by (come_date string comment '按入学年份分区')
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS textfile;
- 构造与表对应的输入文件
- 将本地数据文件加载到表中
LOAD DATA local INPATH './student.txt' OVERWRITE INTO TABLE student PARTITION (come_date=20170903);
- 通过SELECT查看表中是否加载了数据
2.3加载HDFS数据文件
- 将之前的本地文件上传至自己的hdfs目录中
hdfs dfs -copyFromLocal student.txt /tmp/tianliangedu/input_student_info/
- 加载HDFS数据文件的脚本
LOAD DATA INPATH '/tmp/tianliangedu/input_student_info/student.txt' OVERWRITE INTO TABLE student PARTITION (come_date=20170904);
注：原始的hdfs文件数据将被move到目标表的数据目录当中，注意源文件目录和目标文件目录的权限。
- 通过SELECT查看表中是否加载了数据
SELECT * FROM STUDENT;
2.4 将查询结果插入到数据表中
- 脚本模板
INSERT OVERWRITE TABLE tablename1
[PARTITION (partcol1=val1, partcol2=val2 ...)]
select_statement1 FROM from_statement
- 样例
insert overwrite table student partition(come_date='20170905')
select
id,username,classid,classname
from student
where come_date='20170904';
- 通过SELECT查看表中是否插入了数据
2.5 多插入模式（一次查询多次插入）
- 模板
FROM from_statement
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol=val)] select_statement1
[INSERT OVERWRITE TABLE tablename2 [PARTITION ...] select_statement2
...
- 样例
from student
insert overwrite table student partition(come_date='20170906') select id,username,classid,classname where come_date='20170905'
insert overwrite table student partition(come_date='20170907') select id,username,classid,classname where come_date='20170905'
insert overwrite table student partition(come_date='20170908') select id,username,classid,classname where come_date='20170905'
- 通过SELECT查看表中是否插入了数据
2.6 动态分区模式（让分区成为被查询出来的结果表的字段名称变量）
- 脚本模板
INSERT OVERWRITE TABLE tablename
PARTITION (col_name) select_statement FROM from_statement
- 设置非严格模式
set hive.exec.dynamic.partition.mode=nonstric;
- 样例
- 有新表student_outer，即新来的外部学生表，有若干学生数据，与student表结构完全相同。
- 将student_outer表的多个分区数据，一次性插入到student表中。
- 数据准备
- 创建新表student_outer与之前的student结构完全一致
CREATE TABLE student_outer(
id string comment '学号',
username string comment '姓名',
classid int comment '班级id',
classname string comment '班级名称'
)
comment '学生信息主表'
partitioned by (come_date string comment '按入学年份分区')
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
STORED AS textfile;
- 装载数据
LOAD DATA INPATH '/temp/input/student.txt' OVERWRITE INTO TABLE student_outer PARTITION (come_date=20171120);
LOAD DATA INPATH '/temp/input/student.txt' OVERWRITE INTO TABLE student_outer PARTITION (come_date=20171121);
LOAD DATA INPATH '/temp/input/student.txt' OVERWRITE INTO TABLE student_outer PARTITION (come_date=20171122);
- 查看分区列表
- 将student_outer表所有数据插入到student表
//先清空之前的student表，方便查看效果
truncate table student;
insert overwrite table student partition(come_date)
select id,username,classid,classname,come_date from student_outer
2.7 将查询结果写入hdfs目录
- 脚本模版
INSERT OVERWRITE DIRECTORY directory1 SELECT ... FROM ...
- 样例-默认
数据写入文件系统时进行文本序列化，且每列用^A来区分，\n换行
insert overwrite directory "/tmp/output2/"
select * from student where come_date='20170905';
- 样例-指定输分隔符
insert overwrite directory "/tmp/output2/"
row format delimited
fields terminated by '\t'
select * from student where come_date='20170905';
2.8 关于外表的说明和使用
- 内外表的唯一区分标识，即为external关键字。创建表时候有，则为外表，没有则默认均为内表。
- 内表和外表的hdfs目录，均可以自由指定location，如不指定，则数据存储在hive的默认hdfs目录中，且后续均可以自由改变，但改变的场景不多，一般为了省事都不会轻易改变存储目录。
- 应用场景
- 如果表数据是外部load过来的，即为不可hive内部自生成，此时的表必须是外表。
- 如果表数据是hive内部可以自生成的，即不需要依赖外部的load数据，则此时的表应该是内表。
- 示例
- 创建外表
CREATE external TABLE student_external(
id string comment 'stdno',
username string comment 'name',
classid int comment 'class id',
classname string comment 'class name'
)
comment '学生信息主表'
partitioned by
(come_date string comment 'come to school date')
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS textfile;
- 从hdfs路径装载数据
LOAD DATA INPATH '/tmp/input/student.txt'
OVERWRITE INTO TABLE student_external
PARTITION (come_date=20171120);
- 删除外部表后的数据变动情况（删除表后，hdfs文件依然存在）
3. DQL
3.1 脚本模板
SELECT [DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list [HAVING condition]]
[ CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]
]
[LIMIT number]
- DISTRIBUTE BY col_list
以指定字段作为key作hash partition,保证相同的key会到同一个reduce去处理。
- Sort By col_list
以指定字段作为单个reduce排序的key，保证单个reduce内的key有序排列输出。
- Order By col_list
只会生成一个reduce任务，对全部排序
- CLUSTER BY col_list
以指定字段作为key做hash partition，保证相同key会到同一个reduce去处理。该命令相当于distributed by col_list和sort by col_list的联合使用。
3.2 典型样例
- 查询所有记录
select * from student
- 加入where查询条件
select * from student where id='001';
- 加入limit限制
select * from student where id='001' limit 3;
- 升降序：desc,asc
3.3 join查询
将多个表通过字段关联在一起，形成查询结果
- 创建student_location表，存储学生的地理位置信息
CREATE TABLE student_location(
id string comment 'stdno',
province string comment 'province name',
city string comment 'city name',
region string comment 'region name'
)
comment 'student location info'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS textfile;
- 构造student_location表数据
001 河北省 石家庄市 高新区
002 河北省 石家庄市 桥西区
003 河南省 郑州市 高新区
004 湖南省 长沙市 高新区
005 北京市 北京市 朝阳区
006 北京市 北京市 海淀区
- 将数据加载进student_location表中
LOAD DATA INPATH '/tmp/input/student_location.txt'
OVERWRITE INTO TABLE student_location;
- join查询示例
- inner join
将左表和右表满足联接条件的数据，全部查询出来
select * from student
inner join student_location
on student.id=student_location.id;
- left outer join
以左表为主，将左表数据全部保留，右表没有关联上数据字段置成NULL
select * from student left
outer join student_location
on student.id=student_location.id;
- right outer join
以右表为主，将右表数据全部保留，左表没有关联上数据字段置成NULL
select * from student
right outer join student_location
on student.id=student_location.id;
- full outer join
- 没有关联上数据字段全部置成NULL
- full join=inner join+left join+right join
select * from student
full outer join student_location
on student.id=student_location.id;
3.4 union
- union all
将所有表数据，完全叠加在一起，不去重。
要求：所有表的字段和类型完全一致。
- 以student和student_outer表为例
select * from student
union all
select * from student_outer;
- union
将所有表数据，完全叠加在一起，总体去重。
要求：所有表的字段和类型完全一致。
- 以student和student_outer表为例
select * from student
union
select * from student_outer;
4. Hive重要说明
- hql不支持等值连接( 现在版本已经支持了! )
- rdb sql等值连接
select * from table1 t1,table2 t2
where t1.key=t2.key
- hive等值连接：
第1种：
select * from table1 t1,table2 t2
where t1.key=t2.key
第2种：
select * from table1 t1
inner join table2 t2
on t1.key=t2.key
- 对分号敏感性（新版本没有问题）
- rdb sql分号使用
select concat(key,concat(';',key))
from table1;
- hive分号使用
select concat(key,concat('\;',key))
from table1;
- hive当中的子查询，必须要给予别名。
- NULL值判断
hql中用is NULL或者is not NULL来判断字段是否是NULL值,与""没有直接关系
-
- 测试用例
- 首先构建一个有null值的表，并对表中的null值进行起别名字段。
- 测试用例
-
-
- 对该null值的字段进行”=null”测试结果
-
-
-
- 对该null值的字段进行”is null”测试结果
-
- 不支持update,delete操作
三. Hive系统函数
- 查看所有系统函数
show functions
- 函数分类
- 使用示例
使用help解决一个函数不知道怎么用的问题
-
- desc function split;
-
- count
统计纪录行数
//最常见，但效率低
select count(*) from student;
//推荐使用，效率高
select count(1) from student;
-
- if
IF( Test Condition, True Value, False Value )
-
- coalesce
COALESCE( value1,value2,... )将参数列表中第1个不为null的值作为最后的值
-
- case...when
CASE [ expression ]
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
WHEN conditionn THEN resultn
ELSE result
END
-
- split
将字符串拆分成一个数组
-
- explode：表成生成函数
将一个集合元素，打散成一行一行的组成，即将一行改成多行，换句话说行转列
-
- lateral view：
与explode联用，形成一张新表
- 创建用户得分表
create table user_score(
id int,
name string,
score_list string
);
一. Hive基础概念
1. hive数据模型
2. 数据类型
2.1 数值型
| 类型 | 说明 |
| TINYINT | 1-byte signed integer from -128 to 127 |
| SMALLINT | 2-byte signed integer from -32,768 to 32,767 |
| INT INTEGER | 4-byte signed integer from -2,147,483,648 to 2,147,483,647 |
| BIGINT | 8-byte signed integer from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 |
| FLOAT | 4-byte single precision floating point number |
| DOUBLE | 8-byte double precision floating point number PRECISION |
| DECIMAL | Decimal datatype was introduced in Hive0.11.0 (HIVE-2693) and revised in Hive 0.13.0 (HIVE-3976) |
2.2 日期类型
| 类型 | 说明 |
| TIMESTAMP | UNIX时间戳和可选的纳秒精度 |
| DATE | 描述特定的年/月/日，格式为YYYY-MM-DD |
2.3 字符串
| 类型 | 说明 |
| string | 最常用的字符串格式，等同于java String |
| varchar | 变长字符串，hive用的较多，最长为65535 |
| char | 定长字符串，比varchar更多一些，一般不要超过255个字符 |
2.4 布尔类型
| 类型 | 说明 |
| boolean | 等同于java的boolean用的很少 |
2.5 字节数组
| 类型 | 说明 |
| binary | 字节数组类型，可以存储任意类型的数据用的很少 |
2.6 复杂(集合)数据类型
| 数据类型 | 描述 | 字面语法示例 |
| STRUCT | 和C语言中的struct或者”对象”类似，都可以通过”点”符号访问元素内容。例如，如果某个列的数据类型是STRUCT{first STRING, lastdt STRING}，那么第1个元素可以通过字段名.first来引用 | struct( ‘John’, ‘Doe’) |
| MAP | MAP是一组键-值对元组集合，使用数组表示法(例如[‘key’])可以访问元素。例如，如果某个列的数据类型是MAP，其中键->值对是’first’->’John’和’last’->’Doe’，那么可以通过字段名[‘last’]获取值’Doe’ | map(‘first’, ‘John’, ‘last’, ‘Doe’) |
| ARRAY | 数组是一组具有相同类型的变量的集合。这些变量称为数组的元素，每个数组元素都有一个编号，编号从零开始。例如，数组值为[‘John’, ‘Doe’]，那么第1个元素可以通过数组名[0]进行引用 | ARRAY( ‘John’, ‘Doe’) |
3. hive数据文件格式和压缩格式
- 文件格式
文件格式按面向的存储形式不同，分为面向行和面向列两大类文件格式。
| 面向行/列类型 | 类型名称 | 是否可切割计算 | 优点 | 缺点 | 适用场景 |
| 面向行 | 文本文件格式(.txt) | 可以 | 查看、编辑简单 | 无压缩占空间大、传输压力大、数据解析开销大 | 学习练习使用 |
| 面向行 | SequenceFile序列文件格式(.seq) | 可以 | 自支持、二进制kv存储、支持行和块压缩 | 本地查看不方便：小文件合并成kv结构后不易查看内部数据 | 生产环境使用、map输出的默认文件格式 |
| 面向列 | rcfile文件格式(.rc) | 可以 | 数据加载快、查询快、空间利用率高、高负载能力 | 每一项都不是最高 | 学习、生产均可 |
| 面向列 | orcfile文件格式(.orc) | 可以 | 兼具rcfile优点、进一步提高了读取、存储效率、新数据类型的支持 | 每一项都不是最高 | 学习、生产均可 |
- 压缩格式
压缩格式按其可切分独立性，分成可切分和不可切分两种。
| 可切分性 | 类型名称 | 是否原生支持 | 优点 | 缺点 | 适用场景 |
| 可切分 | lzo(.lzo) | 否 | 压缩/解压速度快 合理的压缩率 | 压缩率比gzip低 不原生、需要native安装 | 单个文件越大，lzo优点越越明显。压缩完成后>=200M为宜 |
| 可切分 | bzip2(.bz2) | 是 | 高压缩率超过gzip 原生支持、不需要native安装、用linux bzip可解压操作 | 压缩/解压速率慢 | 处理速度要求不高、要求高压缩率（冷数据处理经常使用） |
| 不可切分 | gzip(.gz) | 是 | 压缩/解压速度快 原生/native都支持使用方便 | 不可切分、对CPU要求较高 | 压缩完成后<=130M的文件适宜 |
| 不可切分 | snappy(.snappy) | 否 | 高速压缩/解压速度 合理的压缩率 | 压缩率比gzip低 不原生、需要native安装 | 适合作为map->reduce或是job数据流的中间数据传输格式 |
4. 数据操作分类
| 操作分类 | 具体操作 | sql备注 |
| DDL | •创建数据库 •建/删除表 •修改表结构 •创建/删除视图 •显示命令 | Create/Drop/Alter Database Create/Drop/Truncate Table Alter Table/Partition/Column Create/Drop/Alter View Create/Drop Index Create/Drop Function Show functions; Describe function; |
| DML | •数据插入(insert,load) | load data...into table insert overwrite table |
| DQL | •数据查询(select) |
二. HiveSQL
按数据操作分类，来分别说明脚本的使用。
1. DDL
1.1 建表说明
- 元数据:描述数据的数据
- 表分类：主要分内表和外表
- 内表：元数据和数据本身均被hive管理。删除表则全部删除。
- 外表：元数据被hive管理，数据本身存储在hdfs,不受hive管理。删除表则只删除元数据，数据本身不变。
1.2 建表模板
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]
- 关键词解释
- external: 创建内部表还是外部表，此为内外表的唯一区分关键字。
- comment col_comment: 给字段添加注释
- comment table_comment: 给表本身添加注释
- partitioned by: 按哪些字段分区，可以是一个，也可以是多个
- clustered by col_name... into num_buckets BUCKETS:按哪几个字段做hash后分桶存储
- row format:用于设定行、列、集合的分隔符等设置
- stored as : 用于指定存储的文件类型，如text,rcfile等
- location : 设定该表存储的hdfs目录，如果不手动设定，则采用hive默认的存储路径
1.3 示例
创建学生表student,包括id,name,classid,classname及分区和注释信息。
CREATE TABLE student(
id string comment '学号',
username string comment '姓名',
classid int comment '班级id',
classname string comment '班级名称'
)
comment '学生信息主表'
partitioned by (come_date string comment '按入学年份分区')
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\001'
LINES TERMINATED BY '\n'
STORED AS textfile;
- 查看已存在表的详细信息
show create table或者desc tablename/desc formatted tablename
- 显示所有表
- 更改表
- 增加字段
再次通过：show create table student2;确认表结构。
- 创建视图(虚表)
视图：本身不存储实际数据，只存储表关系，使用时再去通过关系查找数据。
查看所有视图：show views;
- 删除视图
2. DML
2.1 加载数据脚本
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
2.2 加载本地数据文件(新版本的hive已不支持本地数据文件加载到表中)
- 创建一个文本文件存储的表，并以"\t"作为分隔符，方便构造和上传数据
CREATE TABLE student(
id string comment '学号',
username string comment '姓名',
classid int comment '班级id',
classname string comment '班级名称'
)
comment '学生信息主表'
partitioned by (come_date string comment '按入学年份分区')
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS textfile;
- 构造与表对应的输入文件
- 将本地数据文件加载到表中
LOAD DATA local INPATH './student.txt' OVERWRITE INTO TABLE student PARTITION (come_date=20170903);
- 通过SELECT查看表中是否加载了数据
2.3加载HDFS数据文件
- 将之前的本地文件上传至自己的hdfs目录中
hdfs dfs -copyFromLocal student.txt /tmp/tianliangedu/input_student_info/
- 加载HDFS数据文件的脚本
LOAD DATA INPATH '/tmp/tianliangedu/input_student_info/student.txt' OVERWRITE INTO TABLE student PARTITION (come_date=20170904);
注：原始的hdfs文件数据将被move到目标表的数据目录当中，注意源文件目录和目标文件目录的权限。
- 通过SELECT查看表中是否加载了数据
SELECT * FROM STUDENT;
2.4 将查询结果插入到数据表中
- 脚本模板
INSERT OVERWRITE TABLE tablename1
[PARTITION (partcol1=val1, partcol2=val2 ...)]
select_statement1 FROM from_statement
- 样例
insert overwrite table student partition(come_date='20170905')
select
id,username,classid,classname
from student
where come_date='20170904';
- 通过SELECT查看表中是否插入了数据
2.5 多插入模式（一次查询多次插入）
- 模板
FROM from_statement
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol=val)] select_statement1
[INSERT OVERWRITE TABLE tablename2 [PARTITION ...] select_statement2
...
- 样例
from student
insert overwrite table student partition(come_date='20170906') select id,username,classid,classname where come_date='20170905'
insert overwrite table student partition(come_date='20170907') select id,username,classid,classname where come_date='20170905'
insert overwrite table student partition(come_date='20170908') select id,username,classid,classname where come_date='20170905'
- 通过SELECT查看表中是否插入了数据
2.6 动态分区模式（让分区成为被查询出来的结果表的字段名称变量）
- 脚本模板
INSERT OVERWRITE TABLE tablename
PARTITION (col_name) select_statement FROM from_statement
- 设置非严格模式
set hive.exec.dynamic.partition.mode=nonstric;
- 样例
- 有新表student_outer，即新来的外部学生表，有若干学生数据，与student表结构完全相同。
- 将student_outer表的多个分区数据，一次性插入到student表中。
- 数据准备
- 创建新表student_outer与之前的student结构完全一致
CREATE TABLE student_outer(
id string comment '学号',
username string comment '姓名',
classid int comment '班级id',
classname string comment '班级名称'
)
comment '学生信息主表'
partitioned by (come_date string comment '按入学年份分区')
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
STORED AS textfile;
- 装载数据
LOAD DATA INPATH '/temp/input/student.txt' OVERWRITE INTO TABLE student_outer PARTITION (come_date=20171120);
LOAD DATA INPATH '/temp/input/student.txt' OVERWRITE INTO TABLE student_outer PARTITION (come_date=20171121);
LOAD DATA INPATH '/temp/input/student.txt' OVERWRITE INTO TABLE student_outer PARTITION (come_date=20171122);
- 查看分区列表
- 将student_outer表所有数据插入到student表
//先清空之前的student表，方便查看效果
truncate table student;
insert overwrite table student partition(come_date)
select id,username,classid,classname,come_date from student_outer
2.7 将查询结果写入hdfs目录
- 脚本模版
INSERT OVERWRITE DIRECTORY directory1 SELECT ... FROM ...
- 样例-默认
数据写入文件系统时进行文本序列化，且每列用^A来区分，\n换行
insert overwrite directory "/tmp/output2/"
select * from student where come_date='20170905';
- 样例-指定输分隔符
insert overwrite directory "/tmp/output2/"
row format delimited
fields terminated by '\t'
select * from student where come_date='20170905';
2.8 关于外表的说明和使用
- 内外表的唯一区分标识，即为external关键字。创建表时候有，则为外表，没有则默认均为内表。
- 内表和外表的hdfs目录，均可以自由指定location，如不指定，则数据存储在hive的默认hdfs目录中，且后续均可以自由改变，但改变的场景不多，一般为了省事都不会轻易改变存储目录。
- 应用场景
- 如果表数据是外部load过来的，即为不可hive内部自生成，此时的表必须是外表。
- 如果表数据是hive内部可以自生成的，即不需要依赖外部的load数据，则此时的表应该是内表。
- 示例
- 创建外表
CREATE external TABLE student_external(
id string comment 'stdno',
username string comment 'name',
classid int comment 'class id',
classname string comment 'class name'
)
comment '学生信息主表'
partitioned by
(come_date string comment 'come to school date')
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS textfile;
- 从hdfs路径装载数据
LOAD DATA INPATH '/tmp/input/student.txt'
OVERWRITE INTO TABLE student_external
PARTITION (come_date=20171120);
- 删除外部表后的数据变动情况（删除表后，hdfs文件依然存在）
3. DQL
3.1 脚本模板
SELECT [DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list [HAVING condition]]
[ CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]
]
[LIMIT number]
- DISTRIBUTE BY col_list
以指定字段作为key作hash partition,保证相同的key会到同一个reduce去处理。
- Sort By col_list
以指定字段作为单个reduce排序的key，保证单个reduce内的key有序排列输出。
- Order By col_list
只会生成一个reduce任务，对全部排序
- CLUSTER BY col_list
以指定字段作为key做hash partition，保证相同key会到同一个reduce去处理。该命令相当于distributed by col_list和sort by col_list的联合使用。
3.2 典型样例
- 查询所有记录
select * from student
- 加入where查询条件
select * from student where id='001';
- 加入limit限制
select * from student where id='001' limit 3;
- 升降序：desc,asc
3.3 join查询
将多个表通过字段关联在一起，形成查询结果
- 创建student_location表，存储学生的地理位置信息
CREATE TABLE student_location(
id string comment 'stdno',
province string comment 'province name',
city string comment 'city name',
region string comment 'region name'
)
comment 'student location info'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS textfile;
- 构造student_location表数据
001 河北省 石家庄市 高新区
002 河北省 石家庄市 桥西区
003 河南省 郑州市 高新区
004 湖南省 长沙市 高新区
005 北京市 北京市 朝阳区
006 北京市 北京市 海淀区
- 将数据加载进student_location表中
LOAD DATA INPATH '/tmp/input/student_location.txt'
OVERWRITE INTO TABLE student_location;
- join查询示例
- inner join
将左表和右表满足联接条件的数据，全部查询出来
select * from student
inner join student_location
on student.id=student_location.id;
- left outer join
以左表为主，将左表数据全部保留，右表没有关联上数据字段置成NULL
select * from student left
outer join student_location
on student.id=student_location.id;
- right outer join
以右表为主，将右表数据全部保留，左表没有关联上数据字段置成NULL
select * from student
right outer join student_location
on student.id=student_location.id;
- full outer join
- 没有关联上数据字段全部置成NULL
- full join=inner join+left join+right join
select * from student
full outer join student_location
on student.id=student_location.id;
3.4 union
- union all
将所有表数据，完全叠加在一起，不去重。
要求：所有表的字段和类型完全一致。
- 以student和student_outer表为例
select * from student
union all
select * from student_outer;
- union
将所有表数据，完全叠加在一起，总体去重。
要求：所有表的字段和类型完全一致。
- 以student和student_outer表为例
select * from student
union
select * from student_outer;
4. Hive重要说明
- hql不支持等值连接( 现在版本已经支持了! )
- rdb sql等值连接
select * from table1 t1,table2 t2
where t1.key=t2.key
- hive等值连接：
第1种：
select * from table1 t1,table2 t2
where t1.key=t2.key
第2种：
select * from table1 t1
inner join table2 t2
on t1.key=t2.key
- 对分号敏感性（新版本没有问题）
- rdb sql分号使用
select concat(key,concat(';',key))
from table1;
- hive分号使用
select concat(key,concat('\;',key))
from table1;
- hive当中的子查询，必须要给予别名。
- NULL值判断
hql中用is NULL或者is not NULL来判断字段是否是NULL值,与""没有直接关系
-
- 测试用例
- 首先构建一个有null值的表，并对表中的null值进行起别名字段。
- 测试用例
-
-
- 对该null值的字段进行”=null”测试结果
-
-
-
- 对该null值的字段进行”is null”测试结果
-
- 不支持update,delete操作
三. Hive系统函数
- 查看所有系统函数
show functions
- 函数分类
- 使用示例
使用help解决一个函数不知道怎么用的问题
-
- desc function split;
-
- count
统计纪录行数
//最常见，但效率低
select count(*) from student;
//推荐使用，效率高
select count(1) from student;
-
- if
IF( Test Condition, True Value, False Value )
-
- coalesce
COALESCE( value1,value2,... )将参数列表中第1个不为null的值作为最后的值
-
- case...when
CASE [ expression ]
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
WHEN conditionn THEN resultn
ELSE result
END
-
- split
将字符串拆分成一个数组
-
- explode：表成生成函数
将一个集合元素，打散成一行一行的组成，即将一行改成多行，换句话说行转列
-
- lateral view：
与explode联用，形成一张新表
- 创建用户得分表
create table user_score(
id int,
name string,
score_list string
);
- 插入相应数据
insert into table user_score values(1,'one','90,80,70');
insert into table user_score values(2,'two','65,75,85');
insert into table user_score values(3,'three','55,40,85');
- 查看内部数据
- 通过explode将一行转换成多行，通过lateral view将多行转换成一个表
- 标准使用
- 求分数大于等于80分的有多少人
扫一扫
433
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
