Hive: dynamic partitioning

Question

I'm trying to implement dynamic partitioning to update date in the recent 30 partitions:

set hive.exec.dynamic.partition=true;
insert overwrite tmp_ol.user_status_aggre partition(`day`)
select 
uuid,
uv+(case when b.uuid is not null then 1 else 0 end) as uv,
`date` as `day`
from 
(select uuid,uv,`date` from user_status_aggre where `day` between `2017-05-15` and `2017-05-22`) a
left join 
(select uuid from tabledemo where `day`='2017-05-22') b
on a.uuid=b.uuid

But I'm getting an error:

FAILED: ParseException line 1:17 cannot recognize input near 'tmp_ol' '.' 'user_status_aggre' in destination specification

The query to create the table is as follows:

create table tmp_ol.user_status_aggre (
uuid string,
uv string,
`date` date)
PARTITIONED BY (`day` string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
STORED AS textfile;

I'm wondering if dynamic partitioning cannot be applied to itself..Thanks for your help.

Looks like you forgot the TABLE keyword in the INSERT statement. It is INSERT OVERWRITE TABLE <TableName> PARTITION ... — Nazar Merza

Satish Kumar Reddy Satish Kumar Reddy · Accepted Answer · 2017-05-23T18:21:35

Try below one:

SET hive.exec.dynamic.partition=true;
INSERT
    overwrite TABLE tmp_ol.user_status_aggre partition
    (
        day 
    )
SELECT
    uuid,
    uv+(
        CASE
            WHEN b.uuid IS NOT NULL
            THEN 1
            ELSE 0
        END) AS uv,
    date   AS day
FROM
    (
        SELECT
            uuid,
            uv,
            date
        FROM
            user_status_aggre
        WHERE
            day BETWEEN '2017-05-15' AND '2017-05-22') a
LEFT JOIN
    (
        SELECT
            uuid
        FROM
            tabledemo
        WHERE
            day='2017-05-22') b
ON
    a.uuid=b.uuid

Hive: dynamic partitioning

1 Answers