hive分区表导入数据到mysql hive数据库分区

分区表

数据库分区是一种物理数据库设计技术，DBA和数据库建模人员对其相当熟悉。虽然分区技术可以实现很多效果，但其主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减响应时间。

hive中的分区表实际上就是对应一个 HDFS 文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive 中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过 WHERE 子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。

基本操作

1. 准备数据

# 准备三个数据文件，模拟请求日志，每天生成个文件
vim log1.txt
# 插入字段（id,clinet_ip,request_uri,response_time）
1,127.0.0.1,/test,200
2,127.0.0.1,/auth,500
#  同理准备另外两个
vim log2.txt
vim log3.txt

2. 创建分区表

hive (default)> create table if not exists log
              > (id int,clinet_ip string,request_uri string,response_time int)
              > partitioned by(day string)
              > row format delimited fields terminated by ',';

3. 导入数据

load data local inpath '/opt/soft/data/log1.txt' into table log partition(day='20110111');
load data local inpath '/opt/soft/data/log2.txt' into table log partition(day='20110112');
load data local inpath '/opt/soft/data/log3.txt' into table log partition(day='20110113');

4. 查询分区表中数据

# 查询单个分区中的数据
select * from log where day = '20110111';
# 查询多个分区数据
hive (default)> select * from log where day = '20110111'
              > union
              > select * from log where day = '20110112'
              > ;
#

5. 操作分区

# 查询分区列表
show partitions log;
# 添加单个分区
alter table log add partition(day='20110114');
# 添加多个分区
alter table log add partition(day='20110117') partition(day='20110116');
# 删除单个分区
alter table log drop partition(day='20110117');
# 删除多个分区
alter table log drop partition(day='20110115'),partition(day='20110116');

二级分区

二级分区就是在一级分区的文件夹在再创建分类文件。

# 创建二级分区表
hive (default)> create table if not exists log1
              > (id int,clinet_ip string,request_uri string,response_time int)
              >  partitioned by(day string,hour string)
              >  row format delimited fields terminated by ',';

# 导入数据
load data local inpath '/opt/soft/data/log1.txt' into table log1 partition(day='20110111',hour='12');
load data local inpath '/opt/soft/data/log2.txt' into table log1 partition(day='20110112',hour='11');
# 查看所有数据
select * from log1;
# 查看二级分区数据
select * from log1 where day='20110111' and hour='11';
# 查看HDFS，二级分区已建立

动态分区

hive 中支持两种类型的分区: 静态分区 SP(static partition) 动态分区 DP(dynamic partition)，动态分区可以自动根据分区字段的值，将数据插入到相应的分区中。

1. 配置

# 开启动态分区功能（默认 true，开启）
set hive.exec.dynamic.partition=true;
# 设置动态分区的模式，默认 strict，表示必须指定至少一个分区为静态分区，nonstrict 模式表示允许所有的分区字段都可以使用动态分区。
set hive.exec.dynamic.partition.mode=nonstrict;
# 设置所有执行MR的节点最大动态分区数，默认 1000
set hive.exec.max.dynamic.partitions=1000;
# 设置单个执行MR的节点最大动态分区数，默认100，根据实际设置
set hive.exec.max.dynamic.partitions.pernode=100;
# 整个 MR Job 中，最大可以创建多少个 HDFS 文件。默认 100000 
set hive.exec.max.created.files=100000;
# 当有空分区生成时，是否抛出异常。一般不需要设置。默认 false
set hive.error.on.empty.partition=false;

2. 案例

# 在log表中插入分区数据
insert into log partition(day) values(7,'192.168.5.56','/test/aaaa',500,'20110119');
# HDFS查看数据已被动态分配到分区中

分桶表

分桶是将数据集分解成更容易管理的若干部分的一个技术，是比表或分区更为细粒度的数据范围划分。针对某一列进行桶的组织，对列值哈希，然后除以桶的个数求余，决定将该条记录存放到哪个桶中。

分区针对的是数据的存储路径；分桶针对的是数据文件。对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围划分。

常用于：

• 获得更高的查询处理效率
• 抽样调查

案例

# 准备数据
cd /opt/soft/data
vim buket.txt
# 插入
1001,ss1
1002,ss2
1003,ss3
1004,ss4
1005,ss5
1006,ss6
1007,ss7
1008,ss8
1009,ss9
1010,ss10
1011,ss11
1012,ss12
1013,ss13
1014,ss14
1015,ss15
1016,ss16
# 创建分桶表
hive (default)> create table if not exists buket
              > (id int,name string)
              > clustered by(id)
              > into 5 buckets 
              > row format delimited fields terminated by ',';
# 查看结构
desc formatted buket;
# 导入数据
load data local inpath '/opt/soft/data/buket.txt' into table buket;
# 发生报错：Hive默认不支持通过load命令导入数据到分桶表(在Hive旧版本中默认可以使用LOAD命令导入,但仅仅是将数据复制或者移动到表目录下,并不会平均分配到各个桶中),
# 代替的是先将数据导入一张中间表(可以是普通表,也可以是临时表)中,然后通过”INSERT....SELECT”的方式,将普通表的数据导入到分桶表.
FAILED: SemanticException Please load into an intermediate table and use 'insert... select' to allow Hive to enforce bucketing. Load into bucketed tables are disabled for safety reasons. If you know what you are doing, please sethive.strict.checks.bucketing to false and that hive.mapred.mode is not set to 'strict' to proceed. Note that if you may get errors or incorrect results if you make a mistake while using some of the unsafe features.
# 创建临时表 并导入数据
hive (default)> create table if not exists tmp_bucket (id int ,name string)
              > row format delimited fields terminated by ',';
load data local inpath '/opt/soft/data/buket.txt' into table tmp_bucket;
# 插入
insert into buket select * from tmp_bucket;

HDFS中查看分桶:Hive 的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。

抽样查询

对于非常大的数据集,有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive可以通过对表进行分桶抽样来满足这个需求。

语法：

TABLESAMPLE (BUCKET x OUT OF y [ON colname]);

TABLESAMPLE查询语句可用于任何表中，桶编号从1开始，colname表明抽取样本的列，可以是非分区列中的任意一列；也可以是rand(),表示以整行为基准而不是以单个列进行采样,以上语句为例，根据colname分桶的行被随机“分配”到编号为1到y的桶中，存放在第x个桶中的数据将会被返回。下面的例子中，返回32个桶中的第3个桶中的行:

SELECT * FROM source TABLESAMPLE(BUCKET 3 OUT OF 32 ON rand()) s;