Hive(基础)数据类型、DDL、DML操作、分区和分桶

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'='20170830');

//删除空数据库
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、数据导入

1. 使用hdfs dfs -put '本地数据' 'hive表对应的HDFS目录下'
2. 使用 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;
   

3. create table xxx as SQL语句
4. insert into table xxxx SQL语句 （没有as）

   // 将 students表的数据插入到students2 这是复制 不是移动 students表中的表中的数据不会丢失
   insert into table students2 select * from students;
   
   // 覆盖插入 把into 换成 overwrite
   insert overwrite table students2 select * from students;
   

3.2、数据导出

1. 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;
   

2. 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='20210904');
  

• 删除一个分区

  alter table students_pt drop partition(pt='20210904');
  

• 查看某个表的所有分区

  show partitions students_pt; // 推荐这种方式（直接从元数据中获取分区信息）
  
  select distinct pt from students_pt; // 不推荐
  

• 往分区中插入数据

  insert into table students_pt partition(pt='20210902') select * from students;
  
  load data local inpath '/usr/local/soft/data/students.txt' into table students_pt partition(pt='20210902');
  

• 查询某个分区的数据

  // 全表扫描，不推荐，效率低
  select count(*) from students_pt;
  
  // 使用where条件进行分区裁剪，避免了全表扫描，效率高
  select count(*) from students_pt where pt='20210101';
  
  // 也可以在where条件中使用非等值判断
  select count(*) from students_pt where pt<='20210112' and pt>='20210110';
  

4.1.2、动态分区

1. 开启动态分区

   # 表示开启动态分区
   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;
   

2. 建立原始表并加载数据

   create table students_dt
   (
       id bigint,
       name string,
       age int,
       gender string,
       clazz string,
       dt string
   )
   ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
   

3. 建立分区表并加载数据

   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 ',';
   

4. 使用动态分区插入数据

   // 分区字段需要放在 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 BUCKETS
ROW 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;