Hive数据类型、DDL、DML操作、分区和分桶
1、Hive数据类型1.1、基本数据类型1.2、集合数据类型1.3、数据类型转换1.3.1、隐式数据类型转换1.3.1、使用 CAST 操作显示进行数据类型转换 2、DDL 数据定义2.1、创建数据库2.2、查询数据库2.3、创建表2.3.1、建表语法2.3.2、字段说明2.3.3、管理表（内部表）2.3.4、外部表2.3.5、内部表与外部表的互相转换 2.4、修改表2.5、删除表 3、DML 数据操作3.1、数据导入3.2、数据导出3.3、清除表数据 4、分区表和分桶表4.1、分区表4.1.1、建立分区表4.1.2、动态分区4.1.3、多级分区 4.2、分桶表4.2.1、建立分桶表4.2.2、 往分桶表中插入数据1、Hive数据类型
1.1、基本数据类型
1.2、集合数据类型
1.3、数据类型转换
1.3.1、隐式数据类型转换
任何整数类型都可以隐式地转换为一个范围更广的类型，如 TINYINT 可以转换成INT，INT 可以转换成 BIGINT。所有整数类型、FLOAT 和 STRING 类型都可以隐式地转换成 DOUBLE。TINYINT、SMALLINT、INT 都可以转换为 FLOAT。BOOLEAN 类型不可以转换为任何其它的类型。1.3.1、使用 CAST 操作显示进行数据类型转换
例如 ：CAST(‘1’ AS INT)将把字符串’1’ 转换成整数 1；
如果强制类型转换失败，如执行CAST(‘X’ AS INT)，表达式返回空值 NULL。
2、DDL 数据定义
2.1、创建数据库
CREATE DATABASE [IF NOT EXISTS] database_name//字段注释[COMMENT database_comment]//可以指定数据库在HDFS上的存储位置[LOCATION hdfs_path][WITH DBPROPERTIES (property_name=property_value, ...)];
2.2、查询数据库
//显示数据库show databases;//显示数据库信息desc database database_name;//显示数据库详细信息desc database extended database_name;//切换数据库use database_name;//修改数据库alter database db_hive set dbproperties('createtime'='0830');//删除空数据库drop database db_hive2;//如果数据库不为空，强制删除数据库drop database db_hive cascade;
2.3、创建表
2.3.1、建表语法
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]// 创建student示例create table students(id bigint,name string,age int,gender string,clazz string)ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; // 必选，指定列分隔符
2.3.2、字段说明
CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时可以指定一个指向实际数据的路径（LOCATION），在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。COMMENT：为表和列添加注释。PARTITIONED 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] 指定按什么分割，一般使用逗号进行分割。STORED AS 指定存储文件类型，常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）、ORCFILE。LOCATION ：指定表在 HDFS 上的存储位置。AS：后跟查询语句，根据查询结果创建表。LIKE 允许用户复制现有的表结构，但是不复制数据。2.3.3、管理表（内部表）
默认创建的表都是所谓的管理表，有时也被称为内部表。因为这种表，Hive 会（或多或少地）控制着数据的生命周期。Hive 默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如，/user/hive/warehouse)所定义的目录的子目录下。
当我们删除一个管理表时，Hive 也会删除这个表中数据。管理表不适合和其他工具共享数据。
2.3.4、外部表
因为表是外部表，所以 Hive 并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。
2.3.5、内部表与外部表的互相转换
//修改student2为外部表alter table student2 set tblproperties('EXTERNAL'='TRUE');//修改student2为内部表alter table student2 set tblproperties('EXTERNAL'='FALSE');
注意：(‘EXTERNAL’=‘TRUE’)和(‘EXTERNAL’=‘FALSE’)为固定写法，区分大小写！
2.4、修改表
//重命名ALTER TABLE table_name RENAME TO new_table_name//更新列ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]//增加和替换列ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
2.5、删除表
//删除表drop table dept;
3、DML 数据操作
3.1、数据导入
使用hdfs dfs -put '本地数据' 'hive表对应的HDFS目录下'使用 load data inpath// 将HDFS上的/input1目录下面的数据 移动至 students表对应的HDFS目录下，注意是 移动、移动、移动load data inpath '/input1/students.txt' into table students;// 加上 local 关键字 可以将Linux本地目录下的文件 上传到 hive表对应HDFS 目录下 文件不会被删除load data local inpath '/usr/local/soft/data/students.txt' into table students;// overwrite 覆盖加载load data local inpath '/usr/local/soft/data/students.txt' overwrite into table students;
create table xxx as SQL语句insert into table xxxx SQL语句 （没有as）
// 将 students表的数据插入到students2 这是复制 不是移动 students表中的表中的数据不会丢失insert into table students2 select * from students;// 覆盖插入 把into 换成 overwriteinsert overwrite table students2 select * from students;
3.2、数据导出
Insert 导出//将查询的结果导出到本地insert overwrite local directory '本地路径' select * from student;//将查询的结果格式化导出到本地insert overwrite local directory '本地路径' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' select * from student;//将查询的结果导出到 HDFS 上(没有 local)insert overwrite directory 'HDFS路径' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' select * from student;
Hadoop 命令导出到本地
hive (default)> dfs -get /user/hive/warehouse/student/student.txt/opt/module/data/export/student3.txt;
3.3、清除表数据
truncate table student;
注意：Truncate 只能删除管理表，不能删除外部表中数据
4、分区表和分桶表
分区（Distribute By）
Distribute By： 在有些情况下，我们需要控制某个特定行应该到哪个 reducer，通常是为了进行后续的聚集操作。distribute by 子句可以做这件事。distribute by 类似 MR 中partition（自定义分区），进行分区，结合 sort by 使用。对于 distribute by 进行测试，一定要分配多 reduce 进行处理，否则无法看到 distribute by 的效果。
例：先按照部门编号分区，再按照员工编号降序排序。
select * from emp distribute by deptno sort by empno desc;
distribute by 的分区规则是根据分区字段的 hash 码与 reduce 的个数进行模除后，余数相同的分到一个区。Hive 要求 DISTRIBUTE BY 语句要写在 SORT BY 语句之前。
Cluster By
当 distribute by 和 sorts by 字段相同时，可以使用 cluster by 方式。cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。但是排序只能是升序排序，不能指定排序规则为 ASC 或者 DESC。
例：两种写法等价
hive (default)> select * from emp cluster by deptno;hive (default)> select * from emp distribute by deptno sort by deptno;
按照部门编号分区，不一定就是固定死的数值，可以是 20 号和 30 号部门分到一个分区里面去。
4.1、分区表
分区表实际上是在表的目录下在以分区命名，建子目录
作用：
进行分区裁剪，避免全表扫描，减少MapReduce处理的数据量，提高效率一般在公司的hive中，所有的表基本上都是分区表，通常按日期分区、地域分区分区表在使用的时候记得加上分区字段分区也不是越多越好，一般不超过3级，根据实际业务衡量
4.1.1、建立分区表
create external table students_pt1(id bigint,name string,age int,gender string,clazz string)PARTITIONED BY(pt string)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
增加一个分区
alter table students_pt1 add partition(pt='0904');
删除一个分区
alter table students_pt drop partition(pt='0904');
查看某个表的所有分区
show partitions students_pt; // 推荐这种方式（直接从元数据中获取分区信息）select distinct pt from students_pt; // 不推荐
往分区中插入数据
insert into table students_pt partition(pt='0902') select * from students;load data local inpath '/usr/local/soft/data/students.txt' into table students_pt partition(pt='0902');
查询某个分区的数据
// 全表扫描，不推荐，效率低select count(*) from students_pt;// 使用where条件进行分区裁剪，避免了全表扫描，效率高select count(*) from students_pt where pt='0101';// 也可以在where条件中使用非等值判断select count(*) from students_pt where pt<='0112' and pt>='0110';
4.1.2、动态分区
开启动态分区
# 表示开启动态分区hive> set hive.exec.dynamic.partition=true;# 表示动态分区模式：strict（需要配合静态分区一起使用）、nostrict# strict： insert into table students_pt partition(dt='anhui',pt) select ......,pt from students;hive> set hive.exec.dynamic.partition.mode=nostrict;# 表示支持的最大的分区数量为1000，可以根据业务自己调整hive> set hive.exec.max.dynamic.partitions.pernode=1000;
建立原始表并加载数据
create table students_dt(id bigint,name string,age int,gender string,clazz string,dt string)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
建立分区表并加载数据
create table students_dt_p(id bigint,name string,age int,gender string,clazz string)PARTITIONED BY(dt string)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
使用动态分区插入数据
// 分区字段需要放在 select 的最后，如果有多个分区字段 同理，它是按位置匹配，不是按名字匹配insert into table students_dt_p partition(dt) select id,name,age,gender,clazz,dt from students_dt;// 比如下面这条语句会使用age作为分区字段，而不会使用student_dt中的dt作为分区字段insert into table students_dt_p partition(dt) select id,name,age,gender,dt,age from students_dt;
4.1.3、多级分区
//创建原始数据表create table students_year_month(id bigint,name string,age int,gender string,clazz string,year string,month string)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';//创建多级分区表create table students_year_month_pt(id bigint,name string,age int,gender string,clazz string)PARTITIONED BY(year string,month string)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';//从原始数据表中加载数据到分区表insert into table students_year_month_pt partition(year,month) select id,name,age,gender,clazz,year,month from students_year_month;
4.2、分桶表
分桶实际上是对文件（数据）的进一步切分
Hive默认关闭分桶
作用：
在往分桶表中插入数据的时候，会根据 clustered by 指定的字段 进行hash分区 对指定的buckets个数 进行取余，进而可以将数据分割成buckets个数个文件，以达到数据均匀分布，可以解决Map端的“数据倾斜”问题，方便我们取抽样数据，提高Map join效率。
分桶字段 需要根据业务进行设定
开启分桶开关
hive> set hive.enforce.bucketing=true;
4.2.1、建立分桶表
create table students_buks(id bigint,name string,age int,gender string,clazz string)CLUSTERED BY (clazz) into 12 BUCKETSROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
4.2.2、 往分桶表中插入数据
注意往分桶表中插入数据的方式
// 直接使用load data 并不能将数据打散load data local inpath '/usr/local/soft/data/students.txt' into table students_buks;// 需要使用下面这种方式插入数据，才能使分桶表真正发挥作用insert into students_buks select * from students;
