Hive-02 DDL| DML
1. Hive数据类型
基本数据类型
|
Hive数据类型 |
Java数据类型 |
长度 |
例子 |
|
TINYINT |
byte |
1byte有符号整数 |
20 |
|
SMALINT |
short |
2byte有符号整数 |
20 |
|
INT |
int |
4byte有符号整数 |
20 |
|
BIGINT |
long |
8byte有符号整数 |
20 |
|
BOOLEAN |
boolean |
布尔类型，true或者false |
TRUE FALSE |
|
FLOAT |
float |
单精度浮点数 |
3.14159 |
|
DOUBLE |
double |
双精度浮点数 |
3.14159 |
|
STRING |
string |
字符系列。可以指定字符集。可以使用单引号或者双引号。 |
‘now is the time’ “for all good men” |
|
TIMESTAMP |
|
时间类型 |
|
|
BINARY |
|
字节数组 |
|
对于Hive的String类型相当于数据库的varchar类型，该类型是一个可变的字符串，不过它不能声明其中最多能存储多少个字符，理论上它可以存储2GB的字符数。
集合数据类型
|
数据类型 |
描述 |
语法示例 |
|
STRUCT |
和c语言中的struct类似，都可以通过“点”符号访问元素内容。例如，如果某个列的数据类型是STRUCT{first STRING, last STRING},那么第1个元素可以通过字段.first来引用。 |
struct() 例如struct<street:string, city:string> |
|
MAP |
MAP是一组键-值对元组集合，使用数组表示法可以访问数据。例如，如果某个列的数据类型是MAP，其中键->值对是’first’->’John’和’last’->’Doe’，那么可以通过字段名[‘last’]获取最后一个元素 |
map() 例如map<string, int> |
|
ARRAY |
数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素，每个数组元素都有一个编号，编号从零开始。例如，数组值为[‘John’, ‘Doe’]，那么第2个元素可以通过数组名[1]进行引用。 |
Array() 例如array<string> |
Hive有三种复杂数据类型ARRAY、MAP 和 STRUCT。
ARRAY和MAP与Java中的Array和Map类似，而STRUCT与C语言中的Struct类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套。
用JSON格式来表示其数据结构。在Hive下访问的格式为
{
"name": "kris",
"friends": ["shero" , "alex"] , //列表Array,
"children": { //键值Map,
"lili": 18 ,
"yan": 19
}
"address": { //结构Struct,
"street": "chaoyang",
"city": "beijing"
}
}
注意：MAP，STRUCT和ARRAY里的元素间关系都可以用同一个字符表示，这里用“_”。
Hive上创建测试表test
create table test(
name string,
friends array<string>,
children map<string, int>,
address struct<street:string, city:string>
)
row format delimited fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by '\n';
字段解释：
row format delimited fields terminated by ',' -- 列分隔符
collection items terminated by '_' --MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
map keys terminated by ':' -- MAP中的key与value的分隔符
lines terminated by '\n'; -- 行分隔符
hive> load data local inpath '/var/lib/hadoop-hdfs/test.txt' into table test;
访问三种集合列里的数据，以下分别是ARRAY，MAP，STRUCT的访问方式
select * from test;
name friends children address
kris ["shero","alex"] {"lili":18,"yan":19} {"street":"chaoyang","city":"beijing"}
select friends[1],children['lili'],address.city from test where name="kris";
alex 18 beijing
类型转换
Hive的原子数据类型是可以进行隐式转换的，类似于Java的类型转换，例如某表达式使用INT类型，TINYINT会自动转换为INT类型，但是Hive不会进行反向转化，例如，某表达式使用TINYINT类型，INT不会自
动转换为TINYINT类型，它会返回错误，除非使用CAST操作。
1）隐式类型转换规则如下
（1）任何整数类型都可以隐式地转换为一个范围更广的类型，如TINYINT可以转换成INT，INT可以转换成BIGINT。
（2）所有整数类型、FLOAT和STRING类型都可以隐式地转换成DOUBLE。
（3）TINYINT、SMALLINT、INT都可以转换为FLOAT。
（4）BOOLEAN类型不可以转换为任何其它的类型。
2）可以使用CAST操作显示进行数据类型转换
例如CAST('1' AS INT)将把字符串'1' 转换成整数1；如果强制类型转换失败，如执行CAST('X' AS INT)，表达式返回空值 NULL。
0: jdbc:hive2://hadoop101:10000> select '1'+2, cast('1'as int) + 2; +------+------+--+ | _c0 | _c1 | +------+------+--+ | 3.0 | 3 |
对于Hive的String类型相当于数据库的varchar类型，该类型是一个可变的字符串，不过它不能声明其中最多能存储多少个字符，理论上它可以存储2GB的字符数。
2. DDL数据定义
创建数据库
创建一个数据库，数据库在HDFS上的默认存储路径是/user/hive/warehouse/*.db。
修改
用户可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值，来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。
① 创建数据库
create database if not exists db_hive; 避免要创建的数据库已经存在错误，增加if not exists判断。（标准写法） create database if not exists db_hive2 location '/db_hive2.db'; 指定数据库在HDFS上存放的位置
② 修改数据库
alter database db_hive set dbproperties('createtime'='20190215'); //为某个数据库设置键值对属性值，来描述数据库的属性信息。
③ 查看数据库|
切换数据库 use xx;
显示数据库
show databases; //show databases like 'db_hive';
查看数据库详情
desc database db_hive;
desc database extended db_hive; 显示数据库详细信息； 也可以去掉extended即显示数据库信息；
db_name comment location owner_name owner_type parameters
db_hive hdfs://hadoop101:9000/user/hive/warehouse/db_hive.db kris USER {createtime=20190215}
④ 删除数据库
drop database db_hive2;
drop database if exists db_hive2;
drop database db_hive cascade; ##若数据库不为空，则强制删除用cascade；
创建表
建表语法
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允许用户复制现有的表结构，但是不复制数据。
hive (default)> create table if not exists student2( > id int, name string) > row format delimited fields terminated by '\t' > stored as textfile > location '/user/hive/warehouse/student2'; OK
管理表| 外部表
管理表，有时也被称为内部表。因为这种表，Hive会（或多或少地）控制着数据的生命周期。Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如，/user/hive/warehouse)所定
义的目录的子目录下。 当我们删除一个管理表时，Hive也会删除这个表中数据。管理表不适合和其他工具共享数据。
外部表，因为表是外部表，Hive并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。
管理部和内部表的使用场景：
每天将收集到的网站日志定期流入HDFS文本文件。在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过SELECT+INSERT进入内部表。
内部表数据可进可出 元数据+hdfs
外部表元数据---HDFS，只包含元数据； 不会删hdfs数据
① 普通创建表
create table if not exists student3 as select id, name from student; //创建表并导入数据 create table if not exists student4 like student; //根据已经存在的表机构创建表 desc formatted student2; #查询表的类型；查看格式化数据 ② 外部表 hive (default)> dfs -mkdir /student; hive (default)> dfs -put /opt/module/datas/student.txt /student; hive (default)> create external table stu_external( //创建外部表 id int, name string) row format delimited fields terminated by '\t' location '/student';
select * from stu_external; desc formatted stu_external; drop table stu_external; 外部表删除后，hdfs中的数据还在，但是metadata中stu_external的元数据已被删除
③ 内部表和外部表的互相转换 desc formatted student2; //查看表的类型 alter table student2 set tblproperties('EXTERNAL'='TRUE'); //将student2变成外部表alter table student2 set tblproperties('EXTERNAL'='FALSE'); //将student2变成内部表
('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE') 为固定写法，区分大小写。
分区表
分区表实际上就是对应一个HDFS文件系统上独立的文件夹，该文件夹下是该分区所有的数据文件。Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE子句
中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。
① 创建分区表
hive (default)> create table dept_partition( > deptno int, dname string, loc string) > partitioned by (month string) > row format delimited fields terminated by '\t';
加载数据 hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201709'); Loading data to table default.dept_partition partition (month=201709) Partition default.dept_partition{month=201709} stats: [numFiles=1, numRows=0, totalSize=71, rawDataSize=0] OK load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201708'); load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201707'); ② 单分区查询 select * from dept_partition where month='201708'; +------------------------+-----------------------+---------------------+-----------------------+--+ | dept_partition.deptno | dept_partition.dname | dept_partition.loc | dept_partition.month | +------------------------+-----------------------+---------------------+-----------------------+--+ | 10 | ACCOUNTING | 1700 | 201708 | | 20 | RESEARCH | 1800 | 201708 | | 30 | SALES | 1900 | 201708 | | 40 | OPERATIONS | 1700 | 201708 | +------------------------+-----------------------+---------------------+-----------------------+-- 多分区联合查询 select * from dept_partition where month='201707' union select * from dept_partition where month='201708' union select * from dept_partition where month='201709';
③ 增加分区| 增加单个、增加多个分区 alter table dept_partition add partition(month='201705') partition(month='201704');
④ 删除分区| 单个、删多个用，连接 alter table dept_partition drop partition(month='201705'), partition(month='201706');
⑤ 查看分区有多少分区 show partitions dept_partition; +---------------+--+ | partition | +---------------+--+ | month=201707 | | month=201708 | | month=201709 | +---------------+--+
⑥ 查看分区表结构 desc formatted dept_partition;
⑦ 创建二级分区 如果一个月的日志数据量很大，如何将数据拆分？
hive (default)> create table dept_partition2(
deptno int, dname string, loc string)
partitioned by (month string, day string)
row format delimited fields terminated by '\t';
加载数据到二级分区
load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition2 partition(month='201709', day='13');
select * from dept_partition2 where month='201709' and day='13'; 查看分区数据
把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式
方式一：上传数据后修复
dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201709/day=12;
dfs -put /opt/module/datas/dept.txt /user/hive/warehouse/dept_partition2/month=201709/day=12;
msck repair table dept_partition2; //执行修复命令
select * from dept_partition2 where month='201709' and day='12';
alter table dept_partition2 drop partition(month='201709', day='11'); 删除
方式二：上传数据后添加分区
dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201709/day=11; 不能加引号
dfs -put /opt/module/datas/dept.txt /user/hive/warehouse/dept_partition2/month=201709/day=11;
alter table dept_partition2 add partition(month='201709', day='11'); //执行添加分区
select * from dept_partition2 where month='201709' and day='11';
方式三：创建文件夹后load数据到分区
dfs -mkdir -p /user/hive/warehouse/dept_partition2/month='201709'/day='10';
load data local inpath '/opt/module/datas/dept.txt' into table dept_partition2 partition(month='201709',day='10');
select * from dept_partition2 where month='201709' and day='10';
动态分区调整
关系型数据库中，对分区表Insert数据时候，数据库自动会根据分区字段的值，将数据插入到相应的分区中，Hive中也提供了类似的机制，即动态分区(Dynamic Partition)，只不过，使用Hive的动态分区，需要进
行相应的配置。
开启动态分区参数设置
（1）开启动态分区功能（默认true，开启）
hive.exec.dynamic.partition=true
（2）设置为非严格模式（动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区。）
hive.exec.dynamic.partition.mode=nonstrict
（3）在所有执行MR的节点上，最大一共可以创建多少个动态分区。默认1000
hive.exec.max.dynamic.partitions=1000
（4）在每个执行MR的节点上，最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即day字段有365个值，那么该参数就需要设置成大于365，如果使用默认
值100，则会报错。
hive.exec.max.dynamic.partitions.pernode=100
（5）整个MR Job中，最大可以创建多少个HDFS文件。默认100000
hive.exec.max.created.files=100000
（6）当有空分区生成时，是否抛出异常。一般不需要设置。默认false
hive.error.on.empty.partition=false
将dept表中的数据按照地区（loc字段），插入到目标表dept_partition的相应分区中。
（1）创建目标分区表
create table dept_partition_dy(id int, name string)
partitioned by (loc int)
row format delimited fields terminated by '\t';
（2）设置动态分区
set hive.exec.dynamic.partition.mode = nonstrict;
insert into table dept_partition_dy partition(loc) select deptno, dname, loc from dept;
（3）查看目标分区表的分区情况
show partitions dept_partition;
目标分区表是如何匹配到分区字段的？
分桶表
分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区。对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围划分。
分桶是将数据集分解成更容易管理的若干部分的另一个技术。
分区针对的是数据的存储路径；分桶针对的是数据文件。
开启支持分桶
set hive.enforce.bucketing=true;
默认false，设置为true后，mr运行时会根据bucket的个数自动分配reduce task个数。一次作业产生的桶（文件数量）和reduce task个数一致。
创建分桶表
create table stu_buck(id int, name string)
clustered by(id) into 4 buckets
row format delimited fields terminated by '\t';
查看表结构
hive (default)> desc formatted stu_buck;
Num Buckets: 4
导入数据到分桶表中，load的方式
hive (default)> load data local inpath '/opt/module/datas/student.txt' into table stu_buck;
查看创建的分桶表中是并没有分成4个桶；没有经过mapreduce
方式二：
创建分桶表时，数据通过子查询的方式导入
（1）先建一个普通的stu表
create table stu(id int, name string)
row format delimited fields terminated by '\t';
（2）向普通的stu表中导入数据
load data local inpath '/opt/module/datas/student.txt' into table stu;
（3）清空stu_buck表中数据
truncate table stu_buck;
select * from stu_buck;
（4）导入数据到分桶表，通过子查询的方式
insert into table stu_buck select id, name from stu;
发现还是只有一个分桶; 因为数据量太小了；
（5）需要设置一个属性；再来一遍！
hive (default)> set hive.enforce.bucketing=true;
hive (default)> set mapreduce.job.reduces=-1;
hive (default)> truncate table stu_buck;
hive (default)> insert into table stu_buck select id, name from stu; //insert方式将数据导入分桶表。
查询分桶的数据
select * from stu_buck; //从结果中看到分成了4个桶；
+--------------+----------------+--+
| stu_buck.id | stu_buck.name |
+--------------+----------------+--+
| 1016 | ss16 |
| 1012 | ss12 |
| 1008 | ss8 |
| 1004 | ss4 |
| 1009 | ss9 |
| 1005 | ss5 |
| 1001 | ss1 |
| 1013 | ss13 |
| 1010 | ss10 |
| 1002 | ss2 |
| 1006 | ss6 |
| 1014 | ss14 |
| 1003 | ss3 |
| 1011 | ss11 |
| 1007 | ss7 |
| 1015 | ss15 |
+--------------+----------------+--+
16 rows selected (0.09 seconds)
分桶规则：
根据结果可知：Hive的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中
分桶表操作需要注意的事项:
- （1）reduce的个数设置为-1,让Job自行决定需要用多少个reduce或者将reduce的个数设置为大于等于分桶表的桶数
- （2）从hdfs中load数据到分桶表中，避免本地文件找不到问题
- （3）不要使用本地模式
修改表
重命名表
jdbc:hive2://hadoop101:10000> alter table teacher rename to new_teacher;
添加列
0: jdbc:hive2://hadoop101:10000> alter table dept_partition add columns(deptdesc string);
更新列
0: jdbc:hive2://hadoop101:10000> alter table dept_partition change column deptdesc desc int;
No rows affected (0.112 seconds)
0: jdbc:hive2://hadoop101:10000> desc dept_partition;
替换列
0: jdbc:hive2://hadoop101:10000> alter table dept_partition replace columns(deptid int, name string, loc string);
删除表
0: jdbc:hive2://hadoop101:10000> drop table new_teacher;
3. DML数据操作
数据导入
① 向表中装载数据（Load）| 从本地 or HDFS到 Hive
hive> load data [local] inpath '数据的path' [overwrite] into table student [partition (partcol1=val1,…)];
（1）load data:表示加载数据
（2）local:表示从本地加载数据到hive表；否则从HDFS加载数据到hive表
（3）inpath:表示加载数据的路径
（4）overwrite:表示覆盖表中已有数据，否则表示追加
（5）into table:表示加载到哪张表
（6）student:表示具体的表
（7）partition:表示上传到指定分区
向表中装载数据：
从本地到hive
create table student(id int, name string) row format delimited fields terminated by '\t';
load data local inpath '/opt/module/datas/student.txt' into table default.student; 加载本地文件到hive
从HDFS到hive
0: jdbc:hive2://hadoop101:10000> dfs -mkdir -p /user/kris/hive;
0: jdbc:hive2://hadoop101:10000> dfs -put /opt/module/datas/student.txt /user/kris/hive;
load data inpath '/user/kris/hive/student.txt' into table default.student; //移动hdfs上的文件；加载HDFS上的数据
load data inpath '/user/kris/hive/student.txt' overwrite into table default.student; //加载数据覆盖表中已有的数据
② 通过查询语句向表中插入数据Insert
create table student(id int, name string) partitioned by (month string) row format delimited fields terminated by '\t'; 创建一张分区表
insert into table student partition(month='201902') values (1, "kris"), (2, "egon"); 插入数据
根据单张表查询结果来插入insert into是追加数据的方式插入表或分区，原有数据不会被删除；
insert overwrite是会覆盖表或分区中已有数据；
insert overwrite table student partition(month="201905") select id,name from student where month='201902'; 在原本基础上追加
select * from student;
+-------------+---------------+----------------+--+
| student.id | student.name | student.month |
+-------------+---------------+----------------+--+
| 1 | kris | 201902 |
| 2 | egon | 201902 |
| 1 | kris | 201905 |
| 2 | egon | 201905 |
+-------------+---------------+----------------+--+
多表(多分区)插入模式（根据多张表查询结果插入）
hive (default)> from student
> insert overwrite table student partition(month="201904")
> select id, name where month="201905"
> insert overwrite table student partition(month="201903")
> select id, name where month="201905";
0: jdbc:hive2://hadoop101:10000> select * from student;
+-------------+---------------+----------------+--+
| student.id | student.name | student.month |
+-------------+---------------+----------------+--+
| 1 | kris | 201902 |
| 2 | egon | 201902 |
| 1 | kris | 201903 |
| 2 | egon | 201903 |
| 1 | kris | 201904 |
| 2 | egon | 201904 |
| 1 | kris | 201905 |
| 2 | egon | 201905 |
+-------------+---------------+----------------+-
③ 查询语句中创建并加载数据 AS Select
create table if not exists student3 as select id, name from student;
create table if not exists student4 like student;
④ 创建表时通过Location指定加载数据路径
create external table if not exists stu(id int, name string) row format delimited fields terminated by '\t' location '/student';
⑤ Import数据到指定Hive表中；
要先使用export导出后，才能将数据import导入
export table student to '/hive_data/student';
import table student from '/hive_data/student';
create table student22(
id int, name string)
partitioned by (month string)
row format delimited fields terminated by '\t';
import table student22 partition(month='201904') from //student22必须要有分区才能导入成功
'/user/hive/warehouse/export/student';
数据导出（Impala都不支持）
① Insert导出
将输出文件导出到本地/opt/module/datas/export/student中；
0: jdbc:hive2://hadoop101:10000> insert overwrite local directory '/opt/module/datas/export/student' select * from student;
结果格式化导出到本地
hive (default)> insert overwrite local directory '/opt/module/datas/export/student1'
> ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' select * from student;
结果导出到HDFS；只能用overwrite，不能用into
hive (default)> insert overwrite directory '/user/kris/student2'
> row format delimited fields terminated by '\t'
> select * from student;
② Hadoop命令导出到本地
hive (default)> dfs -get /user/hive/warehouse/student/month=201902/000000_0 /opt/module/datas/export/student3.txt;
[kris@hadoop101 export]$ cat student3.txt
1 kris
2 egon
[kris@hadoop101 export]$ pwd
/opt/module/datas/export
③ Shell命令导出到本地
[kris@hadoop101 hive]$ bin/hive -e 'select * from default.student;' > /opt/module/datas/export/student4.txt
④ Export导出到HDFS上
hive (default)> export table default.student to '/user/hive/warehouse/export/student';
⑤ Sqoop导出（导入）
https://www.cnblogs.com/shengyang17/p/10512510.html
Hive表导出成csv文件
hive -e "
set hive.cli.print.header=true;
select * from student where sex = 'male';
" | sed 's/[\t]/,/g' > /opt/module/student.csv
清除表中数据（Truncate）
注意：Truncate只能删除管理表，不能删除外部表中数据
hive (default)> truncate table student;
