三，分区表
1.静态分区
CREATE TABLE IF NOT EXISTS salgrade2 ( GRADE int, LOSAL int, HISAL int ) partitioned by (day string) row format delimited fields terminated by 't' location '/data/inner/ODS/01/salgrade2';
CREATE TABLE IF NOT EXISTS salgrade3 ( GRADE int, LOSAL int, HISAL int ) partitioned by (day string,code string) row format delimited fields terminated by 't' location '/data/inner/ODS/01/salgrade3';
// 加载一次，文件就要put一次，每次加载都会直接移动源文件 hadoop fs -put ./salgrade.txt /data/inner/RAW/01/salgrade
//另外指定分区名 load data inpath '/data/inner/RAW/01/salgrade/salgrade.txt' into table salgrade2 partition (day='99990101'); load data inpath '/data/inner/RAW/01/salgrade/salgrade.txt' into table salgrade3 partition (day='99990101',code='01'); load data inpath '/data/inner/RAW/01/salgrade/salgrade.txt' into table salgrade2 partition (day='20200703');
// 先进行分区，再执行脚本导入表的数据 alter table salgrade2 add partiton (day='99990102'); load data inpath '/data/inner/RAW/01/salgrade/salgrade.txt' into table salgrade2 partition (day='99990102');
// 需要加上hivevar select * from salgrade2 where day='${YYYYMMDD}'; // 查询具体分区下的数据 select * from salgrade2 where day='99990101';
2.动态分区
set hive.exec.dynamic.partition=true; set hive.exec.dynamic.partition.mode=nonstrict;
create table if not exists salgrade4 like salgrade2;
insert overwrite table salgrade4 partition (day='20200520') select GRADE, LOSAL, HISAL from salgrade2 where day='99990101';
// 显示如下内容： INFO : Number of reduce tasks is set to 0 since there is no reduce operator INFO : number of splits:1 INFO : Submitting tokens for job: job_1593741356151_0001 INFO : The url to track the job: http://bd1601:8088/proxy/application_1593741356151_0001/ INFO : Starting Job = job_1593741356151_0001, Tracking URL = http://bd1601:8088/proxy/application_1593741356151_0001/ INFO : Kill Command = /opt/bdp/hadoop-2.6.5/bin/hadoop job -kill job_1593741356151_0001 INFO : Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0 INFO : 2020-07-03 08:09:51,082 Stage-1 map = 0%, reduce = 0% INFO : 2020-07-03 08:10:12,868 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 3.06 sec INFO : MapReduce Total cumulative CPU time: 3 seconds 60 msec INFO : Ended Job = job_1593741356151_0001 INFO : Stage-4 is selected by condition resolver. INFO : Stage-3 is filtered out by condition resolver. INFO : Stage-5 is filtered out by condition resolver. INFO : Moving data to: hdfs://bdp/bdp/hive/metastore/warehouse/salgrade4/day=20200520/.hive-staging_hive_2020-07-03_08-09-17_320_8093467842238301801-2/-ext-10000 from hdfs://bdp/bdp/hive/metastore/warehouse/salgrade4/day=20200520/.hive-staging_hive_2020-07-03_08-09-17_320_8093467842238301801-2/-ext-10002 INFO : Loading data to table default.salgrade4 partition (day=20200520) from hdfs://bdp/bdp/hive/metastore/warehouse/salgrade4/day=20200520/.hive-staging_hive_2020-07-03_08-09-17_320_8093467842238301801-2/-ext-10000 INFO : Partition default.salgrade4{day=20200520} stats: [numFiles=1, numRows=5, totalSize=59, rawDataSize=54] No rows affected (60.04 seconds)
set hive.support.quoted.identifiers=none; `(membership_level|extra_info)? . insert overwrite table salgrade4 partition (day) select * from salgrade2 where day='99990101';
CREATE EXTERNAL TABLE IF NOT EXISTS emp2 ( EMPNO int, ENAME varchar(255), JOB varchar(255), MGR int, HIREDATE date, SAL decimal(10,0), COMM decimal(10,0), DEPTNO int ) partitioned by (id string) row format delimited fields terminated by 't' location '/data/inner/ODS/01/emp2';
// 报错 insert overwrite table emp2 partition(id) select * from emp; insert overwrite table emp2 partition(id) select *,EMPNO id from emp;
创建表：
CREATE TABLE IF NOT EXISTS salgrade_test (
GRADE int,
LOSAL int,
HISAL int,
day string
) row format delimited fields terminated by 't'
location '/data/inner/ODS/01/salgrade_test';
在本地有一个文件/data/a.txt其中有如下文本内容： 1 700 1200 10100101 2 1201 1400 10100101 3 1401 2000 10100101 4 2001 3000 10100102 5 3001 9999 10100102
加载数据： load data inpath '/data/inner/ODS/01/salgrade_test/a.txt' into table salgrade_test;
插入数据到salgrade_test： insert into table salgrade4 partition(day) select * from salgrade_test;
分区、分桶的作用：
我们知道在传统的DBMs系统中，一般都具有表分区的功能，通过表分区能够在特定的区域检索数据，减少扫描成本，在一定程度上提高了查询效率，当然我们还可以通过进一步在分区上建立索引，进一步提高查询效率。
在Hive中的数据仓库中，也有分区分桶的概念，在逻辑上，分区表与未分区表没有区别，在物理上分区表会将数据按照分区间的列值存储在表目录的子目录中，目录名=“分区键=键值”。其中需要注意的是分区键的列值存储在表目录的子目录中，目录名=“分区键=键值”。其中需要注意的是分区键的值不一定要基于表的某一列（字段），它可以指定任意值，只要查询的时候指定相应的分区键来查询即可。我们可以对分区进行添加、删除、重命名、清空等操作。
分桶则是指定分桶表的某一列，让该列数据按照哈希取模的方式随机、均匀的分发到各个桶文件中。因为分桶操作需要根据某一列具体数据来进行哈希取模操作，故指定的分桶列必须基于表中的某一列（字段）。分桶改变了数据的存储方式，它会把哈希取模相同或者在某一个区间的数据行放在同一个桶文件中。如此一来便可以提高查询效率。如果我们需要对两张在同一个列上进行了分桶操作的表进行JOIN操作的时候，只需要对保存相同列值的通进行JOIN操作即可。
还有一点需要点一下：在hive中的数据是存储在hdfs中的，我们知道hdfs中的数据是不允许修改只能追加的，那么在hive中执行数据修改的命令时，就只能先找到对应的文件，读取后执行修改操作，然后重新写一份文件。如果文件比较大，就需要大量的IO读写。在hive中采用了分桶的策略，只需要找到文件存放对应的桶，然后读取再修改写入即可。
分区：
hive中分区分为 ： 单值分区、范围分区。 单值分区： 静态分区 动态分区
如下所示，现在有一张persionrank表，记录每个人的评级，有id、name、score字段。我们可以创建分区rank（rank不是表中的列，我们可以把它当做虚拟列），并将相应的数据导入指定分区（将数据插入指定目录）。
单值分区：
单值静态分区：导入数据时需要手动指定分区 单值动态分区：导入数据时，系统可以动态判断目标分区
1.静态分区创建： 直接在PARTITI1ONED BY后面跟上分区键、类型即可（指定的分区键不能出现在定义列名中）
CREATE [EXTERNAL] TABLE <table_name> (<col_name> <data_type> [, <col_name> <data_type> ...]) -- 指定分区键和数据类型 PARTITIONED BY (<partition_key> <data_type>, ...) [CLUSTERED BY ...] [ROW FORMAT <row_format>] [STORED AS TEXTFILE|ORC|CSVFILE] [LOCATION '<file_path>'] [TBLPROPERTIES ('<property_name>'='<property_value>', ...)];
2.静态分区写入： -- 覆盖写入 INSERT OVERWRITE TABLE <table_name> PARTITION (<partition_key>=<partition_value>[, <partition_key>=<partition_value>, ...]) SELECT <select_statement>;
-- 追加写入 INSERT INTO TABLE <table_name> PARTITION (<partition_key>=<partition_value>[, <partition_key>=<partition_value>, ...]) SELECT <select_statement>;
3.添加分区： //只能添加分区列的值，不能添加分区列，如果是多个分区列，不能单独添加其中一个 alter table tablename add partition(col=value)
4.删除分区： //可以删除一个分区列，但是会把表中所有包含当前分区列的数据全部删除 alter table tablename drop partition(col=value)
5.修复分区： //手动向hdfs中创建分区目录，添加数据，创建好hive的外表之后，无法加载数据， //元数据中没有相应的记录 msck repair table tablename
6.动态分区创建： 创建方式与静态分区表完全一样，一张表可同时被静态分区和动态分区键分区，只是动态分区键需要放在静态分区键的后面（HDFS上的动态分区目录下不能包含静态分区的子目录），如下spk即static partition key（静态分区键），dpk为dynamic partition key（动态分区键）
CREATE TABLE <table_name> PARTITIONED BY ([<spk> <data_type>, ... ,] <dpk> <data_type>, [<dpk> <data_type>,...]);
7.动态分区写入： 根据表中的某一个列值来确定hdfs存储的目录：
优点： 动态可变，不需要人为控制。假如设定的是日期，那么每一天的数据会单独存储在一个文件夹中
缺点： 需要依靠MR完成，执行比较慢
静态分区键要用 <spk>=<value> 指定分区值；动态分区只需要给出分出分区键名称 <dpk>。
-- 开启动态分区支持，并设置最大分区数 set hive.exec.dynamic.partition=true; //set hive.exec.dynamic.partition.mode=nostrict; set hive.exec.max.dynamic.partitions=2000;
insert into table1 select 普通字段 分区字段 from table2 范围分区：
单值分区每个分区对应于分区键的一个取值，而每个范围分区则对应分区键的一个区间，只要落在指定区间内的记录都被存储在对应的分区下。分区范围需要手动指定，分区的范围为前闭后开区间 [最小值, 最大值)。最后出现的分区可以使用 MAXVALUE 作为上限，MAXVALUE 代表该分区键的数据类型所允许的最大值。
CREATE [EXTERNAL] TABLE <table_name> (<col_name> <data_type>, <col_name> <data_type>, ...) PARTITIONED BY RANGE (<partition_key> <data_type>, ...) (PARTITION [<partition_name>] VALUES LESS THAN (<cutoff>), [PARTITION [<partition_name>] VALUES LESS THAN (<cutoff>), ... ] PARTITION [<partition_name>] VALUES LESS THAN (<cutoff>|MAXVALUE) ) [ROW FORMAT <row_format>] [STORED AS TEXTFILE|ORC|CSVFILE] [LOCATION '<file_path>'] [TBLPROPERTIES ('<property_name>'='<property_value>', ...)]; 多个范围分区键的情况：
DROP TABLE IF EXISTS test_demo; CREATE TABLE test_demo (value INT) PARTITIONED BY RANGE (id1 INT, id2 INT, id3 INT) ( -- id1在(--∞,5]之间，id2在(-∞,105]之间，id3在(-∞,205]之间 PARTITION p5_105_205 VALUES LESS THAN (5, 105, 205), -- id1在(--∞,5]之间，id2在(-∞,105]之间，id3在(205,215]之间 PARTITION p5_105_215 VALUES LESS THAN (5, 105, 215), PARTITION p5_115_max VALUES LESS THAN (5, 115, MAXVALUE), PARTITION p10_115_205 VALUES LESS THAN (10, 115, 205), PARTITION p10_115_215 VALUES LESS THAN (10, 115, 215), PARTITION pall_max values less than (MAXVALUE, MAXVALUE, MAXVALUE) ); 分桶：
对Hive(Inceptor)表分桶可以将表中记录按分桶键的哈希值分散进多个文件中，这些小文件称为桶。
1.创建分桶表： 分桶表的建表有三种方式：直接建表，CREATE TABLE LIKE 和 CREATE TABLE AS SELECT ，单值分区表不能用 CREATE TABLE AS SELECT 建表。这里以直接建表为例：
CREATE [EXTERNAL] TABLE <table_name> (<col_name> <data_type> [, <col_name> <data_type> ...])] [PARTITIONED BY ...] CLUSTERED BY (<col_name>) [SORTED BY (<col_name> [ASC|DESC] [, <col_name> [ASC|DESC]...])]
代码语言：javascript复制 INTO <num_buckets> BUCKETS
[ROW FORMAT <row_format>]
[STORED AS TEXTFILE|ORC|CSVFILE]
[LOCATION '<file_path>']
[TBLPROPERTIES ('<property_name>'='<property_value>', ...)];分桶键只能有一个即<col_name>。表可以同时分区和分桶，当表分区时，每个分区下都会有<num_buckets> 个桶。我们也可以选择使用 SORTED BY … 在桶内排序，排序键和分桶键无需相同。ASC 为升序选项，DESC 为降序选项，默认排序方式是升序。<num_buckets> 指定分桶个数，也就是表目录下小文件的个数。
2.向分桶表中写数据： 因为分桶表在创建的时候只会定义Scheme，且写入数据的时候不会自动进行分桶、排序，需要人工先进行分桶、排序后再写入数据。确保目标表中的数据和它定义的分布一致。
目前有两种方式往分桶表中插入数据：
方法一：打开enforce bucketing开关。 SET hive.enforce.bucketing=true; INSERT (INTO|OVERWRITE) TABLE <bucketed_table> SELECT <select_statement> [SORT BY <sort_key> [ASC|DESC], [<sort_key> [ASC|DESC], ...]];
方法二：将reducer个数设置为目标表的桶数，并在 SELECT 语句中用 DISTRIBUTE BY <bucket_key>对查询结果按目标表的分桶键分进reducer中。 SET mapred.reduce.tasks = <num_buckets>; INSERT (INTO|OVERWRITE) TABLE <bucketed_table> SELECT <select_statement> DISTRIBUTE BY <bucket_key>, [<bucket_key>, ...] [SORT BY <sort_key> [ASC|DESC], [<sort_key> [ASC|DESC], ...]]; 如果分桶表创建时定义了排序键，那么数据不仅要分桶，还要排序
如果分桶键和排序键不同，且按降序排列，使用Distribute by … Sort by分桶排序 如果分桶键和排序键相同，且按升序排列（默认），使用 Cluster by 分桶排序，即如下：
SET mapred.reduce.tasks = <num_buckets>; INSERT (INTO|OVERWRITE) TABLE <bucketed_table> SELECT <select_statement> CLUSTER BY <bucket_sort_key>, [<bucket_sort_key>, ...]; 抽样语句 ：tablesample（bucket x out of y）
tablesample是抽样语句，语法：tablesample(bucket x out of y)，y必须是table总共bucket数的倍数或者因子。Hive根据y的大小，决定抽样的比例。例如：table总共分了64份，当y=32时，抽取2(64/32)个bucket的数据，当y=128时，抽取1/2(64/128)个bucket的数据。x表示从哪个bucket开始抽取。例如：table总共bucket数为32，tablesample(bucket 3 out of 16)表示总共抽取2(32/16)个bucket的数据，分别为第三个bucket和第19(3 16)个bucket的数据。
