时间戳

首页 > 解决方案 > Unable to create Hive unique paritions

date - Unable to create Hive unique paritions

问题描述

I am unable to create unique partitions. when i am uploading data, it's creating all the dates as partition again and again, even the dates are same

create table product_order1(id int,user_id int,amount int,product string, city string, txn_date string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

OK Time taken: 0.133 seconds

    LOAD DATA LOCAL INPATH 'txn' INTO TABLE product_order1;
    Loading data to table oct19.product_order1
    Table oct19.product_order1 stats: [numFiles=1, totalSize=303]
OK

Time taken: 0.426 seconds

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

    hive> 
    > create table dyn_part(id int,user_id int,amount int,product string,city string) PARTITIONED BY(txn_date string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
OK

Time taken: 0.14 seconds

    hive >
INSERT OVERWRITE TABLE dyn_part PARTITION(txn_date) select id,user_id,amount,product,city,txn_date from product_order1;

Result which i have received :- 

    Loading data to table oct19.dyn_part partition (txn_date=null)
     Time taken for load dynamic partitions : 944
    Loading partition {txn_date=04-02-2015}
    Loading partition {txn_date= 03-04-2015}
    Loading partition {txn_date=01-02-2015}
    Loading partition {txn_date=03-04-2015}
    Loading partition {txn_date= 01-01-2015}
    Loading partition {txn_date=01-01-2015}
    Loading partition {txn_date= 01-02-2015}
     Time taken for adding to write entity : 5
Partition oct19.dyn_part{txn_date= 01-01-2015} stats: [numFiles=1, numRows=1, totalSize=25, rawDataSize=24]
Partition oct19.dyn_part{txn_date= 01-02-2015} stats: [numFiles=1, numRows=1, totalSize=25, rawDataSize=24]
Partition oct19.dyn_part{txn_date= 03-04-2015} stats: [numFiles=1, numRows=2, totalSize=50, rawDataSize=48]
Partition oct19.dyn_part{txn_date=01-01-2015} stats: [numFiles=1, numRows=1, totalSize=26, rawDataSize=25]
Partition oct19.dyn_part{txn_date=01-02-2015} stats: [numFiles=1, numRows=1, totalSize=26, rawDataSize=25]
Partition oct19.dyn_part{txn_date=03-04-2015} stats: [numFiles=1, numRows=1, totalSize=26, rawDataSize=25]
Partition oct19.dyn_part{txn_date=04-02-2015} stats: [numFiles=1, numRows=1, totalSize=25, rawDataSize=24]
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1   Cumulative CPU: 4.03 sec   HDFS Read: 4166 HDFS Write: 614 SUCCESS
Total MapReduce CPU Time Spent: 4 seconds 30 msec

标签： datehadoophivehive-partitions

解决方案

我注意到有些日期包含空格，有些没有空格：

txn_date= 03-04-2015和txn_date=03-04-2015

尝试添加trim：

INSERT OVERWRITE TABLE dyn_part PARTITION(txn_date) 
select id, user_id, amount, product, city, trim(txn_date) as txn_date 
from product_order1;

也更好地使用 Hive 兼容的日期格式yyyy-MM-dd，它是可排序的。

要同时格式化日期和删除空格，您可以使用 regexp_replace。如果您当前的格式是MM-dd-yyyy，那么您可以像这样格式化它：

select regexp_replace(' 03-04-2015','.*?(\\d{2})-(\\d{2})-(\\d{4})','$3-$1-$2') --fix accordingly if it is dd-MM-yyyy. In this case it should be '$3-$2-$1' in the replacement template.

回报：

2015-03-04

或者像这样加载：

INSERT OVERWRITE TABLE dyn_part PARTITION(txn_date) 
select id, user_id, amount, product, city, 
       regexp_replace(txn_date,'.*?(\\d{2})-(\\d{2})-(\\d{4})','$3-$1-$2') as txn_date 
  from product_order1;

正则表达式意味着：

'.*?- 任何字符零次或多次

(\\d{2})- 第一组 2 位数字，将在替换中作为$1

-逐字逐句

(\\d{2})- 第二组 2 位数字，将在替换中作为$2

-逐字逐句

(\\d{4})- 第三组 4 位数字，将在替换中作为$3

作为替代'$3-$1-$2'，我们以正确的顺序从正则表达式中获取组，用破折号分隔。假设 $3 是年，$1 是月，$2 是日期中的日。您按正确顺序放置组以获取yyyy-MM-dd，因为无法理解您使用的是哪种格式：MM-dd-yyyy或dd-MM-yyyy

推荐阅读