Hive：Hive基础知识 02

Hive基础知识

一、Hive表结构

创建表的格式：

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
(
    col1Name col1Type [COMMENT col_comment],
    co21Name col2Type [COMMENT col_comment],
    co31Name col3Type [COMMENT col_comment],
    co41Name col4Type [COMMENT col_comment],
    co51Name col5Type [COMMENT col_comment],
    ……
    coN1Name colNType [COMMENT col_comment]

)

[PARTITIONED BY (col_name data_type ...)]		--分区表结构

[CLUSTERED BY (col_name...) [SORTED BY (col_name ...)] INTO N BUCKETS] --分桶表结构

[ROW FORMAT row_format] -- 指定数据文件的分隔符
	row format delimited fields terminated by '列的分隔符' -- 列的分隔符，默认为\001
	lines terminated by '行的分隔符' --行的分隔符，默认\n
[STORED AS file_format] -- 指定文件的存储格式
[LOCATION hdfs_path] -- 用于指定表的目录所在位置，默认表的目录在数据库的目录下面

1.普通表结构

MapReduce处理的规则：
step1：检索元数据，找到表对应的HDFS目录
step2：将表的最后一级目录作为MapReduce程序的输入

结构：

Hive数据仓库目录/数据库目录/表的目录/数据文件

特点：
表的最后一级的目录是表的目录

问题：如果是一张普通表的结构，手动将文件通过HDFS命令放入表的目录下，在表中能否读到？

可以，由于表的最后一级目录是普通目录，所以在表中可以读取到

应用：
默认创建的表都是普通表结构的
一般用于将原始的数据文件构建成表的结构

2.分区表结构

普通表结构的问题？
Map阶段要做大量无意义的过滤操作，导致浪费大量资源

2.1分区表结构的设计

设计思想：
将数据按照一定规则条件划分不同的目录进行分区存储
在查询时，可以根据查询条件在目录层次进行过滤，直接由MapReduce加载需要处理的数据的目录

本质：
提前将数据划分到不同的目录中存储
通过查询条件减少底层的MapReduce的输入的数据量，避免无用的过滤，提高性能

结构：

数据仓库目录/数据库目录/表的目录/分区目录/分区数据文件

特点：
表的最后一级目录就是分区目录

应用：
最常用的表：分区外部表
分区的划分：一般都是按照时间划分的

2.2分区表结构的实现：

1.静态分区

数据文件本身就是就是按照分区规划好的，直接创建分区表，加载每个分区的数据即可
step1：直接创建分区表
step2：加载每一个文件到对应的分区中即可

创建分区表：

create table tb_emp_part1(
 empno string,
 ename string,
 job string,
 managerno string,
 hiredate string,
 salary double,
 jiangjin double,
 deptno string
) 
partitioned by (department int)
row format delimited fields terminated by '\t';

加载对应数据文件到对应的分区

load data local inpath '/export/data/emp10.txt' into table tb_emp_part1 partition (department=10);
load data local inpath '/export/data/emp10.txt' into table tb_emp_part1 partition (department=20);
load data local inpath '/export/data/emp10.txt' into table tb_emp_part1 partition (department=30);

测试SQL的执行计划：explain
普通表：

explain extended select count(*) as numb from tb_emp where deptno = 20;

分区表：

explain extended select count(*) as numb from tb_emp_part1 where department = 20; 

查看元数据：
PARTITIONS

SDS

2.动态分区

数据本身没有按照分区的规则划分，需要通过程序实现自动动态划分
步骤：
step1：先创建一个普通表，加载整体的数据
tb_emp：普通表，所有部门的员工信息都在一个目录文件中

#1.创建员工表
create database db_emp;
use db_emp;
create table tb_emp(
empno string,
ename string,
job string,
managerid string,
hiredate string,
salary double,
jiangjin double,
deptno string
) row format delimited fields terminated by '\t';
#2.加载数据
load data local inpath '/export/data/emp.txt' into table tb_emp;

step2：创建分区表

create table tb_emp_part2(
  empno string,
  ename string,
  job string,
  managerno string,
  hiredate string,
  salary double,
  jiangjin double
) 
partitioned by (dept string)
row format delimited fields terminated by '\t';

开启动态分区：

set hive.exec.dynamic.partition.mode=nonstrict;

step3：将普通表的数据写入分区表，实现动态分区

insert into table tb_emp_part2 partition(dept) 
select ……,deptno from tb_emp ;

要求：查询语句一般不用select *，强制要求查询语句的最后一个字段作为分区字段的

3