如何从字段中动态配置单元中插入? [英] How to make dynamic insert in hive from a field?

问题描述

我有一列有几个日期，如下所示:

I have a column where I have several dates, as follows:

Sun Oct 22 05:35:03 2017
Mon Apr 16 14:33:43 2018
Fri Apr 13 10:41:43 2018

我创建了一个过滤这些日期并将其转换为YYYYMMDD的过程，如下所示.

I've created a process to filter these dates and convert to YYYYMMDD, as below.

20171022
20180416
20180413

此结果将用于将数据分配到它们各自的分区中，这些分区是每天的.

This result will be used to distribute the data in their respective partitions, which are daily.

我试图这样做，但没有成功:

I tried to do it this way but I did not succeed:

insert into table tab2
PARTITION (REFERENCE_DATE = from_unixtime (unix_timestamp ('Sun Oct 22 05:35:03 2017', 'E MMM dd HH: mm: ss yyyy'), 'yyyyMMdd'))
SELECT
from_unixtime (unix_timestamp ('Sun Oct 22 05:35:03 2017', 'E MMM dd HH: mm: ss yyyy'), 'yyyyMMdd') as reference_date
FROM tab1 LIMIT 100;

如何插入?

推荐答案

使用动态分区:

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

insert into table tab2
PARTITION (REFERENCE_DATE)
SELECT
from_unixtime (unix_timestamp ('Sun Oct 22 05:35:03 2017', 'E MMM dd HH: mm: ss yyyy'), 'yyyyMMdd') as reference_date
FROM tab1 LIMIT 100;

最好使用yyyy-MM-dd日期格式，因为这是原始的Hive日期格式:

Better use yyyy-MM-dd date format because this is native Hive date format:

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

insert into table tab2
PARTITION (REFERENCE_DATE)
SELECT
      from_unixtime (unix_timestamp('Sun Oct 22 05:35:03 2017', 'E MMM dd HH: mm: ss yyyy'), 'yyyy-MM-dd') as reference_date
FROM tab1 LIMIT 100;

这篇关于如何从字段中动态配置单元中插入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！