hive 取表最新分区 hive分区表查询

转载

代码匠人之心 2023-09-07 21:27:10

文章标签 hive 取表最新分区 mapreduce date table insert 文章分类 Hive 大数据

hive中简单介绍分区表

hive中创建分区表没有什么复杂的分区类型(范围分区、列表分区、hash分区、混合分区等)。分区列也不是表中的一个实际的字段，而是一个或者多个伪列。意思是说在表的数据文件中实际上并不保存分区列的信息与数据。
下面的语句创建了一个简单的分区表：

create table partition_test
 (member_id string,
 name string
 )
 partitioned by (
 stat_date string,
 province string)
 row format delimited fields terminated by ',';

这个例子中创建了stat_date和province两个字段作为分区列。通常情况下需要先预先创建好分区，然后才能使用该分区，例如：

alter table partition_test add partition (stat_date='20110728',province='zhejiang');

这样就创建好了一个分区。这时我们会看到hive在HDFS存储中创建了一个相应的文件夹：

$ hadoop fs -ls /user/hive/warehouse/partition_test/stat_date=20110728
 Found 1 items
 drwxr-xr-x - admin supergroup 0 2011-07-29 09:53 /user/hive/warehouse/partition_test/stat_date=20110728/province=zhejiang

每一个分区都会有一个独立的文件夹，下面是该分区所有的数据文件。在这个例子中stat_date是主层次，province是副层次，所有stat_date='20110728'，而province不同的分区都会在/user/hive/warehouse/partition_test/stat_date=20110728 下面，而stat_date不同的分区都会在/user/hive/warehouse/partition_test/ 下面，如：

$ hadoop fs -ls /user/hive/warehouse/partition_test/
 Found 2 items
 drwxr-xr-x - admin supergroup 0 2011-07-28 19:46 /user/hive/warehouse/partition_test/stat_date=20110526
 drwxr-xr-x - admin supergroup 0 2011-07-29 09:53 /user/hive/warehouse/partition_test/stat_date=20110728

注意，因为分区列的值要转化为文件夹的存储路径，所以如果分区列的值中包含特殊值，如 '%', ':', '/', '#',它将会被使用%加上2字节的ASCII码进行转义，如：

hive> alter table partition_test add partition (stat_date='2011/07/28',province='zhejiang');
 OK
 Time taken: 4.644 seconds

 $hadoop fs -ls /user/hive/warehouse/partition_test/
 Found 3 items
 drwxr-xr-x - admin supergroup 0 2011-07-29 10:06 /user/hive/warehouse/partition_test/stat_date=2011/07/28
 drwxr-xr-x - admin supergroup 0 2011-07-28 19:46 /user/hive/warehouse/partition_test/stat_date=20110526
 drwxr-xr-x - admin supergroup 0 2011-07-29 09:53 /user/hive/warehouse/partition_test/stat_date=20110728

我使用一个辅助的非分区表partition_test_input准备向partition_test中插入数据：

hive> desc partition_test_input;
 OK
 stat_date string
 member_id string
 name string
 province string

 hive> select * from partition_test_input;
 OK
 20110526 1 liujiannan liaoning
 20110526 2 wangchaoqun hubei
 20110728 3 xuhongxing sichuan
 20110728 4 zhudaoyong henan
 20110728 5 zhouchengyu heilongjiang

然后我向partition_test的分区中插入数据：

hive> insert overwrite table partition_test partition(stat_date='20110728',province='henan') select member_id,name from partition_test_input where stat_date='20110728' and province='henan';
 Total MapReduce jobs = 2
 ...
 1 Rows loaded to partition_test
 OK

还可以同时向多个分区插入数据，0.7版本以后不存在的分区会自动创建，0.6之前的版本官方文档上说必须要预先创建好分区：

hive>
 > from partition_test_input
 > insert overwrite table partition_test partition (stat_date='20110526',province='liaoning')
 > select member_id,name where stat_date='20110526' and province='liaoning'
 > insert overwrite table partition_test partition (stat_date='20110728',province='sichuan')
 > select member_id,name where stat_date='20110728' and province='sichuan'
 > insert overwrite table partition_test partition (stat_date='20110728',province='heilongjiang')
 > select member_id,name where stat_date='20110728' and province='heilongjiang';
 Total MapReduce jobs = 4
 ...
 3 Rows loaded to partition_test
 OK

特别要注意，在其他数据库中，一般向分区表中插入数据时系统会校验数据是否符合该分区，如果不符合会报错。而在hive中，向某个分区中插入什么样的数据完全是由人来控制的，因为分区键是伪列，不实际存储在文件中，如：

hive> insert overwrite table partition_test partition(stat_date='20110527',province='liaoning') select member_id,name from partition_test_input;
 Total MapReduce jobs = 2
 ...
 5 Rows loaded to partition_test
 OK

 hive> select * from partition_test where stat_date='20110527' and province='liaoning';
 OK
 1 liujiannan 20110527 liaoning
 2 wangchaoqun 20110527 liaoning
 3 xuhongxing 20110527 liaoning
 4 zhudaoyong 20110527 liaoning
 5 zhouchengyu 20110527 liaoning

可以看到在partition_test_input中的5条数据有着不同的stat_date和province，但是在插入到partition(stat_date='20110527',province='liaoning')这个分区后，5条数据的stat_date和province都变成相同的了，因为这两列的数据是根据文件夹的名字读取来的，而不是实际从数据文件中读取来的：

$ hadoop fs -cat /user/hive/warehouse/partition_test/stat_date=20110527/province=liaoning/000000_0
 1,liujiannan
 2,wangchaoqun
 3,xuhongxing
 4,zhudaoyong
 5,zhouchengyu

下面介绍一下动态分区，因为按照上面的方法向分区表中插入数据，如果源数据量很大，那么针对一个分区就要写一个insert，非常麻烦。况且在之前的版本中，必须先手动创建好所有的分区后才能插入，这就更麻烦了，你必须先要知道源数据中都有什么样的数据才能创建分区。
使用动态分区可以很好的解决上述问题。动态分区可以根据查询得到的数据自动匹配到相应的分区中去。
使用动态分区要先设置hive.exec.dynamic.partition参数值为true，默认值为false，即不允许使用：

hive> set hive.exec.dynamic.partition;
 hive.exec.dynamic.partition=false
 hive> set hive.exec.dynamic.partition=true;
 hive> set hive.exec.dynamic.partition;
 hive.exec.dynamic.partition=true

动态分区的使用方法很简单，假设我想向stat_date='20110728'这个分区下面插入数据，至于province插入到哪个子分区下面让数据库自己来判断，那可以这样写：

hive> insert overwrite table partition_test partition(stat_date='20110728',province)
 > select member_id,name,province from partition_test_input where stat_date='20110728';
 Total MapReduce jobs = 2
 ...
 3 Rows loaded to partition_test
 OK

stat_date叫做静态分区列，province叫做动态分区列。select子句中需要把动态分区列按照分区的顺序写出来，静态分区列不用写出来。这样stat_date='20110728'的所有数据，会根据province的不同分别插入到/user/hive/warehouse/partition_test/stat_date=20110728/下面的不同的子文件夹下，如果源数据对应的province子分区不存在，则会自动创建，非常方便，而且避免了人工控制插入数据与分区的映射关系存在的潜在风险。

注意，动态分区不允许主分区采用动态列而副分区采用静态列，这样将导致所有的主分区都要创建副分区静态列所定义的分区：

hive> insert overwrite table partition_test partition(stat_date,province='liaoning')
 > select member_id,name,province from partition_test_input where province='liaoning';
 FAILED: Error in semantic analysis: Line 1:48 Dynamic partition cannot be the parent of a static partition 'liaoning'

动态分区可以允许所有的分区列都是动态分区列，但是要首先设置一个参数hive.exec.dynamic.partition.mode ：

hive> set hive.exec.dynamic.partition.mode;
 hive.exec.dynamic.partition.mode=strict

它的默认值是strick，即不允许分区列全部是动态的，这是为了防止用户有可能原意是只在子分区内进行动态建分区，但是由于疏忽忘记为主分区列指定值了，这将导致一个dml语句在短时间内创建大量的新的分区（对应大量新的文件夹），对系统性能带来影响。
所以我们要设置：

hive> set hive.exec.dynamic.partition.mode=nostrick;

删除分区语法

用户可以用 ALTER TABLE DROP PARTITION 来删除分区。分区的元数据和数据将被一并删除。例：

ALTER TABLE day_hour_table DROP PARTITION (dt='2008-08-08', hour='09');

数据加载进分区表中语法：

例：

LOAD DATA INPATH '/user/pv.txt' INTO TABLE day_hour_table PARTITION(dt='2008-08- 08', hour='08'); 
/*' INTO TABLE day_hour partition(dt='2010-07- 07');

当数据被加载至表中时，不会对数据进行任何转换。Load操作只是将数据复制至Hive表对应的位置。数据加载时在表下自动创建一个目录

再介绍3个参数：

hive.exec.max.dynamic.partitions.pernode （缺省值100）：每一个mapreduce job允许创建的分区的最大数量，如果超过了这个数量就会报错

hive.exec.max.dynamic.partitions （缺省值1000）：一个dml语句允许创建的所有分区的最大数量

hive.exec.max.created.files （缺省值100000）：所有的mapreduce job允许创建的文件的最大数量

当源表数据量很大时，单独一个mapreduce job中生成的数据在分区列上可能很分散，举个简单的例子，比如下面的表要用3个map：

如果数据这样分布，那每个mapreduce只需要创建1个分区就可以了：

|1 
 
 map1 --> |1  
 
          |1  
 

          |2 
 
 map2 --> |2  
 
          |2  
 

          |3 
 
 map3 --> |3  
 
          |3

但是如果数据按下面这样分布，那第一个mapreduce就要创建3个分区：

下面给出了一个报错的例子：

|1 
 
 map1 --> |2  
 
          |3  
 

          |1 
 
 map2 --> |2  
 
          |3  
 

          |1 
 
 map3 --> |2  
 
          |3

hive> set hive.exec.max.dynamic.partitions.pernode=4; 
 
 hive> insert overwrite table partition_test partition(stat_date,province) 
 
 > select member_id,name,stat_date,province from partition_test_input distribute by stat_date,province; 
 
 Total MapReduce jobs = 1 
 
 ... 
 
 [Fatal Error] Operator FS_4 (id=4): Number of dynamic partitions exceeded hive.exec.max.dynamic.partitions.pernode.. Killing the job. 
 
 Ended Job = job_201107251641_0083 with errors 
 
 FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.MapRedTask

为了让分区列的值相同的数据尽量在同一个mapreduce中，这样每一个mapreduce可以尽量少的产生新的文件夹，可以借助distribute by的功能，将分区列值相同的数据放到一起：

hive> insert overwrite table partition_test partition(stat_date,province) 
 
 > select member_id,name,stat_date,province from partition_test_input distribute by stat_date,province; 
 
 Total MapReduce jobs = 1 
 
 ... 
 
 18 Rows loaded to partition_test 
 
OK

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。