在配置单元分区中写入子目录

问题陈述
我有如下文件，其中包含模式事件\u time，ad\u id

file_20170102-May have records with event_time for 20170101,20170102,20170103
file_20170103-May have records with event_time for 20170102,20170103,20170104

这里event time是事件发生的时间，filename上的时间戳是收集事件的时间。因此filename上的时间戳和文件中的事件时间不同步。
当我将这些数据写入hive时，我肯定需要基于事件时间分区来编写数据，因为用户对基于事件时间的查询感兴趣。
所以我的输出如下

/path/to/output/event_time=20170102/....parquet
/path/to/output/event_time=20170103/....parquet

但是，我需要能够跟踪文件的时间戳，因为有时一个文件被重新发布，我们想去删除已处理的文件的基础上文件的时间戳。
有没有办法写这个/path/to/output/event\u time=20170101/20170202（文件\u tiemstamp）
请注意，在上面的20170102（文件\时间戳）是一个目录，而不是配置单元分区。
或者，我可以控制Parquet文件的名称，这样当我想删除一个文件名它很容易找出哪些文件删除

演示

下的文件 /home/dmarkovitz/myfiles 我的文件\u 1 \u 20161204.csv

20161204,1
20161203,2

我的文件\u 2 \u 20161205.csv

我的文件\u 3 \u 20161205.csv

20161205,7
20161205,8
20161203,9

Hive

create external table myfiles
(
    Event_Time  string
   ,AD_id       int
)
row format delimited
fields terminated by ','
stored as textfile
location 'file:///home/dmarkovitz/myfiles'
;

select  * 
       ,input__file__name

from    myfiles 
;

+------------+-------+-----------------------------------------------------+
| event_time | ad_id |                  input__file__name                  |
+------------+-------+-----------------------------------------------------+
|   20161204 |     1 | file:/home/dmarkovitz/myfiles/myfile_1_20161204.csv |
|   20161203 |     2 | file:/home/dmarkovitz/myfiles/myfile_1_20161204.csv |
|   20161205 |     7 | file:/home/dmarkovitz/myfiles/myfile_3_20161205.csv |
|   20161205 |     8 | file:/home/dmarkovitz/myfiles/myfile_3_20161205.csv |
|   20161203 |     9 | file:/home/dmarkovitz/myfiles/myfile_3_20161205.csv |
|   20161203 |     3 | file:/home/dmarkovitz/myfiles/myfile_2_20161205.csv |
|   20161204 |     4 | file:/home/dmarkovitz/myfiles/myfile_2_20161205.csv |
|   20161205 |     5 | file:/home/dmarkovitz/myfiles/myfile_2_20161205.csv |
|   20161203 |     6 | file:/home/dmarkovitz/myfiles/myfile_2_20161205.csv |
+------------+-------+-----------------------------------------------------+

create table mytable
(
    AD_id   int
)
partitioned by (file_dt date,Event_Time date)
stored as parquet
;

set hive.exec.dynamic.partition.mode=nonstrict;

insert into mytable partition (file_dt,Event_Time)

select  ad_id
       ,from_unixtime(unix_timestamp(split(input__file__name,'[_.]')[2],'yyyyMMdd'),'yyyy-MM-dd')
       ,from_unixtime(unix_timestamp(Event_Time,'yyyyMMdd'),'yyyy-MM-dd')

from    myfiles
;

show partitions mytable
;

+------------------------------------------+
|                partition                 |
+------------------------------------------+
| file_dt=2016-12-04/event_time=2016-12-03 |
| file_dt=2016-12-04/event_time=2016-12-04 |
| file_dt=2016-12-05/event_time=2016-12-03 |
| file_dt=2016-12-05/event_time=2016-12-04 |
| file_dt=2016-12-05/event_time=2016-12-05 |
+------------------------------------------+

select  *
       ,input__file__name 

from    mytable
;

+-------+------------+------------+----------------------------------------------------------------------+
| ad_id |  file_dt   | event_time |                          input__file__name                           |
+-------+------------+------------+----------------------------------------------------------------------+
|     2 | 2016-12-04 | 2016-12-03 | file:/mydb/mytable/file_dt=2016-12-04/event_time=2016-12-03/000000_0 |
|     1 | 2016-12-04 | 2016-12-04 | file:/mydb/mytable/file_dt=2016-12-04/event_time=2016-12-04/000000_0 |
|     9 | 2016-12-05 | 2016-12-03 | file:/mydb/mytable/file_dt=2016-12-05/event_time=2016-12-03/000000_0 |
|     3 | 2016-12-05 | 2016-12-03 | file:/mydb/mytable/file_dt=2016-12-05/event_time=2016-12-03/000000_0 |
|     6 | 2016-12-05 | 2016-12-03 | file:/mydb/mytable/file_dt=2016-12-05/event_time=2016-12-03/000000_0 |
|     4 | 2016-12-05 | 2016-12-04 | file:/mydb/mytable/file_dt=2016-12-05/event_time=2016-12-04/000000_0 |
|     7 | 2016-12-05 | 2016-12-05 | file:/mydb/mytable/file_dt=2016-12-05/event_time=2016-12-05/000000_0 |
|     8 | 2016-12-05 | 2016-12-05 | file:/mydb/mytable/file_dt=2016-12-05/event_time=2016-12-05/000000_0 |
|     5 | 2016-12-05 | 2016-12-05 | file:/mydb/mytable/file_dt=2016-12-05/event_time=2016-12-05/000000_0 |
+-------+------------+------------+----------------------------------------------------------------------+

explain dependency
select  *       
from    mytable
where   Event_Time = date '2016-12-04'        
;

{“input_tables”：[{“tablename”：local_db@mytable“，”tabletype“：”managed\u table“}]，”input\u partitions“：[{”partitionname“：”local_db@mytable@file_dt=2016-12-04/event_time=2016-12-04“}，{“分区名称”：local_db@mytable@file_dt=2016-12-05/event_time=2016-12-04"}]}
猛击

tree mytable

mytable
├── file_dt=2016-12-04
│   ├── event_time=2016-12-03
│   │   └── 000000_0
│   └── event_time=2016-12-04
│       └── 000000_0
└── file_dt=2016-12-05
    ├── event_time=2016-12-03
    │   └── 000000_0
    ├── event_time=2016-12-04
    │   └── 000000_0
    └── event_time=2016-12-05
        └── 000000_0

在配置单元分区中写入子目录

1条答案

演示

相关问题

热门标签

最新问答