1对于外部表 empl_ext
alter table empl_ext add partition (logdate=‘2015-02-26’) location ‘hdfs://nameservice1/vod_pb/’; 执行添加分区时 vod_pb/ 文件夹下的数据不会被移动。并且没有分区目录logdate=2015-02-26
alter table empl_ext drop partition (logdate=‘2015-02-26’); 执行删除分区目录时vod_pb/ 下的数据不会被删除
load data inpath 'hdfs://nameservice1/vod_pb/' overwrite into table empl_ext partition(logdate='2015-02-26');
执行加载数据添加分区时 vod_pb/ 文件夹下的数据会被移动，并创建分区目录logdate=2015-02-26，数据移动到此目录下
alter table empl_ext drop partition (logdate=‘2015-02-26’); 执行删除分区目录时，已经创建logdate=2015-02-26
分区目录不会被删除，其文件夹下的数据也不会被删除；、
2对于内部表 empl_inn
alter table empl_inn add partition (logdate=‘2015-02-26’) location ‘hdfs://nameservice1/vod_pb/’; 执行添加分区时 vod_pb/ 文件夹下的数据不会被移动。并且没有分区目录logdate=2015-02-26
alter table empl_inn drop partition (logdate=‘2015-02-26’); 执行删除分区时vod_pb/ 下的数据会被删除并且连同vod_pb/文件夹也会被删除
单个表每天数据有50亿左右。需用二级分区优化该表。
错误：
Java Heap Space。或者GC overhead limit exceeded。
原因：
Parquet和ORC是列式批处理文件格式。这些格式要求在写入文件之前将批次的行（batches of rows）缓存在内存中。在执行INSERT语句时，动态分区目前的实现是：至少为每个动态分区目录打开一个文件写入器（file writer）。由于这些缓冲区是按分区维护的，因此在运行时所需的内存量随着分区数量的增加而增加。所以经常会导致mappers或reducers的OOM，具体取决于打开的文件写入器（file writer）的数量。
通过INSERT语句插入数据到动态分区表中，也可能会超过HDFS同时打开文件数的限制。
如果没有join或聚合，INSERT SELECT语句会被转换为只有map任务的作业。mapper任务会读取输入记录然后将它们发送到目标分区目录。在这种情况下，每个mapper必须为遇到的每个动态分区创建一个新的文件写入器（file writer）。mapper在运行时所需的内存量随着它遇到的分区数量的增加而增加。
详细原因： >分区表将数据组织成分区，主要可以提高数据的查询速度。
如果把一年或者一个月的日志文件存放在一个表下，那么数据量会非常的大，当查询这个表中某一天的日志文件的时候，查询速度还非常的慢，这时候可以采用分区表的方式，把这个表根据时间点再划分为小表。这样划分后，查询某一个时间点的日志文件就会快很多，因为这是不需要进行全表扫描。
Hive中的分区是根据“分区列”的值对表的数据进行粗略的划分，Hive中一个表对应一个目录，再根据分区列在这个表目录下创建子目录，每个子目录名就是分区列的名字。分区列定义与表中字段相似，但是与表中的字段无关，是独立的列。这样就加快了数据查询的速度，因为不会对这个表中进行全盘扫描了。
（1）建表语句
create table if not exists latte_d_test
(
uid string comment "用户ID",
vld_flg string comment "该条记录是否有效，1-有效，0-无效"
)
COMMENT "test表"
PARTITIONED BY (day STRING);
（2）设置分区表参数
set hiveexecdynamicpartition = true;
设置为true表示开启动态分区功能（默认为false）。
set hiveexecdynamicpartitionmode = nonstrict;
设置为nonstrict，表示允许所有分区都是动态的（默认为strict）。
（3）插入数据
insert overwrite table latte_d_test partition(day)
select uid,
'1',
'2016-10-10'
from test
分桶与分区有所不同，它指定分桶表的某一列，让该列数据按照哈希取模的方式随机、均匀地分发到各个桶文件中。因为分桶 *** 作需要根据某一列具体数据来进行哈希取模 *** 作，故指定的分桶列必须基于表中的某一列（字段）。因为分桶改变了数据的存储方式，它会把哈希取模相同或者在某一区间的数据行放在同一个桶文件中。如此一来便可提高查询效率，比如我们要对两张在同一列上进行了分桶 *** 作的表进行JOIN *** 作的时候，只需要对保存相同列值的桶进行JOIN *** 作即可。同时分桶也可以提高采样率。
分区是为了对表进行合理的管理以及提高查询效率，Hive可以将表组织成“分区”。一个分区实际上就是表下的一个目录，一个表可以在多个维度上进行分区，分区之间的关系就是目录树的关系。Hive(Inceptor)分区分为静态分区跟动态分区，详细介绍如下：
静态分区在插入或者导入的时候需要指定具体的分区
[if !supportLists]1、 [endif]静态分区创建
需要在PARTITIONED BY后面跟上分区键，类型。例如：
CREATE TABLE p_table1(
id int
,name
string
)
PARTITIONED BY(date_day string)
stored as orc
;
这是一级分区，当然也可以创建多级分区。例如：
CREATE TABLE p_table1(
id int
,name string
)
PARTITIONED BY(date_day string, company
string,emp_no string)
stored as orc
;
下面的实例都是以一级分区为例。
[if !supportLists]2、 [endif]静态分区插入数据
insert overwrite table p_table1 partition(date_day='2019-07-14')
values(1,'lucy');
或者insert overwrite table p_table1 partition(date_day='2019-07-15')
select 2 as id,'lily' as name;
上面两个例子都是覆盖的形式，也就是插入这个分区之前，会将该分区数据删除，再插入新的数据，也可以写成追加的形式：
insert into p_table1
partition(date_day='2019-07-14') values(1,'lucy');
或者insert into e p_table1 partition(date_day='2019-07-15') select 2 as
id,'lily' as name;
[if !supportLists]3、 [endif]静态分区查看分区
查看所有分区show partitions p_table1
结果如下：
date_day=2019-07-14
date_day=2019-07-15
查看某个分区show partitions p_table1 partition(date_day='2019-07-14');
结果如下：
date_day=2019-07-14
[if !supportLists]4、 [endif]静态分区删除分区
删除某个分区alter table p_table1 drop partition(date_day='2019-07-14');
或者删除范围内的分区alter table p_table1 drop partition(date_day>='2019-07-14');
1、动态分区创建
创建方式与静态分区表完全一样，一张表可同时被静态和动态分区键分区，只是动态分区键需要放在静态分区建的后面（因为HDFS上的动态分区目录下不能包含静态分区的子目录）。
CREATE TABLE p_table2(
id int
,name string
)
PARTITIONED BY(date_day string,emp_no
string)
stored as orc
;
这是创建了二级分区表。
2、动态分区插入数据
插入数据时需要开启动态数据支持：
set hiveexecdynamicpartition=true;
set hiveexecdynamicpartitionmode=nostrict;
插入数据（覆盖）insert overwrite table p_table2 partition(date_day，emp_no)
select 2 as id,'lily' as name,'2019-07-14' as date_day, ‘a’ as emp_no;
分区并没有写死，而是根据查询到的值动态创建的两级分区。
3、动态分区查看分区、删除分区与静态分区 *** 作完全一致不再重述。
分桶字段是表内字段，默认是对分桶的字段进行hash值，然后模总的桶数，得到的值则是分区桶数，主要有以下两点好处：
（1）获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接（Map-side join）高效的实现。比如JOIN *** 作。对于JOIN *** 作两个表有一个相同的列，如果对这两个表都进行了桶 *** 作。那么将保存相同列值的桶进行JOIN *** 作就可以，可以大大较少JOIN的数据量。
（2）使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。
创建分桶表
先看一下创建分桶表的创建，分桶表的建表有三种方式：直接建表，CREATE TABLE LIKE 和 CREATE TABLE AS SELECT ，单值分区表不能用 CREATETABLE
AS SELECT 建表。这里以直接建表为例：
create table b_table1(id int,name string)
clustered by (id) sorted by(id) into 4 buckets stored as textfile;
使用CLUSTERED BY 子句来指定划分桶所用的列和要划分的桶的个数，当表分区时，每个分区下都会有4个桶。对于map端连接的情况，两个表以相同方式划分桶。处理左边表内某个桶的 mapper知道右边表内相匹配的行在对应的桶内。因此，mapper只需要获取那个桶 (这只是右边表内存储数据的一小部分)即可进行连接。这一优化方法并不一定要求两个表必须桶的个数相同，两个表的桶个数是倍数关系也可以。用HiveQL对两个划分了桶的表进行连接。
桶中的数据可以根据一个或多个列另外进行排序。由于这样对每个桶的连接变成了高效的归并排序(merge-sort), 因此可以进一步提升map端连接的效率。
向分桶表写入数据
如何保证表中的数据都划分成桶了呢？把在Hive外生成的数据加载到划分成桶的表中，当然是可以的。其实让Hive来划分桶更容易。这一 *** 作通常针对已有的表。
Hive并不检查数据文件中的桶是否和表定义中的桶一致(无论是对于桶的数量或用于划分桶的列）。如果两者不匹配，在査询时可能会碰到错误或未定义的结果。因此，建议让Hive来进行划分桶的 *** 作。
要向分桶表中填充成员，需要将 hiveenforcebucketing 属性设置为 true。这样Hive 就知道用表定义中声明的数量来创建桶。
下面有个未分桶的用户表b_user_test，数据如下：
1 a
2 b
3 c
4 d
5 e
6 f
7 g
插入语句
INSERT OVERWRITE TABLE b_table1 SELECT
FROM b_user_test;
查看文件结构
dfs -ls/user/hive/warehouse/bucketed_users;
文件结构如下所示：
/user/hive/warehouse/b_table1/000000_0
/user/hive/warehouse/b_table1/000001_0
/user/hive/warehouse/b_table1/000002_0
/user/hive/warehouse/b_table1/000003_0
查看文件000000_0
dfs -cat /user/hive/warehouse/bucketed_users/000000_0;
值为4 d说明文件000000_0存的是对分桶数求余等于0的那部分数据。
对桶中的数据进行采样
对分桶进行查询 tablesample(bucket x out of y on id)：
x:表示查询那个桶
y:表示建表指定的桶的总数,如果不是建表时指定的桶的总数，则会重新分桶。
x不能大于y。
取第一个桶的数据：
Sql：SELECT FROM
b_table1 TABLESAMPLE(BUCKET 2 OUT OF 4 ON id);
结果：
5 e
[if !supportLists]1 [endif]a
当桶数不等于建表指定的桶的总数时
Sql：SELECT FROM
b_table1 TABLESAMPLE(BUCKET 2 OUT OF 3 ON id);
结果：
4 d
1 a
7 g
由结果可知，进行了重新分桶，分成了三个桶，取出第二个桶的数据，也就是hash值对3求余等于1的那部分数据。
分桶比分区粒度更细，在每个分区了可以将数据进行分桶 *** 作。讲 MR输出数据到hive表的location分区目录，然后通过Sql添加分区即可。
ALTER TABLE table_name ADD PARTITION (partCol = 'value1') location 'location_path';
换成自己的表，分区字段和path。
基本语法：
例：将表table_change_new中的字段name移到addr字段后面name和addr都是string类型
注：移动列位置的坑
上面的这种位置转移涉及到类型强制转换问题，
比如int不能被转换成string
尝试将sex字段int类型移动到addr后面，addr后面现在是name字段string类型
报当前列sex类型和现有位置的列name不相容的错误
例：将name放到第一列，现在name在第二列
例：创建表city，字段分隔符为#
将一份以tab作为字段分隔符的数据导入city，数据都被作为第一个字段的值
将字段分隔符改为tab
再次查看city表，每个字段值都被正常显示
例：创建分区表city，字段分隔符为#
将一份以tab作为字段分隔符的数据导入city，数据都被作为第一个字段的值， 分区表导入数据时要指定分区
查看此时的city表，数据都被赋给第一个字段
将字段分隔符改为tab
查看此时的字段分隔符参数， 发现还是#
修改分区表序列参数的坑：
注：如果修改serdeproperties参数的时候不加分区，那么字段分隔符参数fielddelim会被改为\t，但是还是没法把该分区的数据分隔开
分区等于20191010的数据可以正常显示
再导入新的数据，设分区等于20191110
发现新分区的数据无法按照分隔符tab显示
再导入新的数据，分区还是等于20191010
此时的数据还是无法显示
再导入新数据，设分区等于20191110
基本语法：
例：将内表city的location修改到/home/yy
此时/home/yy目录下有两个文件
此时city显示的/home/yy目录下的所有文件内容
内表转外表
外表转内表
多分区插入，方便实用，但是有时候会忘记语法，网上搜到很多坑（故意留坑的还挺多的），特此记录。set hiveexecdynamicpartition=true;
set hiveexecdynamicpartitionmode=nonstrict;
INSERT OVERWRITE TABLE dw_tmptmp_test_20180514 partition ( dt )
select from dw_tmptmp_test_20180515
where dt >= '20180513'; Hive是基于Hadoop的一个数据仓库，可以将结构化的数据文件映射为一张表，并提供类sql查询功能，Hive底层将sql语句转化为mapreduce任务运行。Hive是一个数据仓库，不支持行级插入、更新以及删除 *** 作。Hive共有四种表，分别是内部表、外部表、分区表和桶表。
1 内部表
内部表有时也被称为管理表。这种表会控制着数据的生命周期，Hive默认情况下会将这些表的数据存储在由配置项hivemetastorewarehousedir所定义的目录的子目录下。当我们删除内部表时，同时会删除这个表中的数据。内部表不方便与其他工具共享数据。
创建一个内部表可以用如下语句：
2 外部表
外部表指向特定目录的一份数据，Hive并不对该数据具有所有权。当删除表时，不会删除该目录下的数据，数据可以被多个工具共享。即外部表只是对数据进行逻辑管理，创建一个外部表需要用external关键字：
3 分区表
分区表是一种内部表。分区表通过指定一个或多个partition key，决定数据存放方式，进而优化数据的查询。每个表可以指定多个partition key，每个partition在hive中以文件夹的形式存在。如下所示是对数据的年份进行分区，每个年份对应一个文件夹，年份相同的数据会放到同一个文件夹下：
创建分区表时要注意开启允许动态分区设置，必要的时候还需要设置允许创建的最大分区数。
创建分区表可以用以下语句：
4 桶表
桶表也是内部表，桶表是对某一列的数据进行哈希取值以将数据打散，然后放到不用文件中存储。在Hive分区表中，分区中的数据量过于庞大时，建议使用桶。在分桶时，对指定字段的值进行hash运算得到hash值，并使用hash值除以桶的个数取余进行分桶。分桶后的查 询效率比分区后的查询效率更高。下图是按照年份分区，再按id进行分桶后的结构：
建表语句：
end!
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）