Insert into hive table with dynamic partition only writing first partition to disk and not all

Question

I am trying to write data into a hive table and failing. I get a error at the end of Cycle_dt =null and only one partition being writing. It is the first day's.

set hive.auto.convert.join=true;
set hive.optimize.mapjoin.mapreduce=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.dynamic.partition=true; 
set mapred.map.tasks = 100;



Insert into table dynamic.dynamic_test_avro_v1 partition(cycle_dt)
Select date_time as CYCLE_TS, case when evar1 is not null or length(trim(evar1)) > 0 then cast(unbase64(substring(evar1,6,12)) as string) end NRNM ,
prop14 as state, evar8 as FLOW_TYPE, prop25 as KEY, pagename PAGE_NM,
partition_dt as cycle_dt from source.std_avro_v1 WHERE
(partition_dt = '2016-10-02' AND  partition_dt < '2016-10-07')
AND (
evar8='google');

I am unsure what is going on here. I have a date range setup to only get those date's as partitions.

but it's also the destination partition since it's the last column - so you are creating one partition in this query. — Alex Libov, Nov 03 '16 at 18:21
overall, `partition_dt = '2016-10-02' AND partition_dt < '2016-10-07'` doesn't make much sense since it'll always be `partition_dt = '2016-10-02'` — Alex Libov, Nov 03 '16 at 18:22
So changing it to partition_dt => '2016-10-02' will make it the range of partitions and not the single. — Dalton L. Heiland, Nov 03 '16 at 18:24
Sweet Thank you, I will rerun my query now. And Let you know how it goes. — Dalton L. Heiland, Nov 03 '16 at 18:24

score 2 · Accepted Answer · answered Nov 03 '16 at 18:34

From hive documentation:

In the dynamic partition inserts, users can give partial partition specifications, which means just specifying the list of partition column names in the PARTITION clause. The column values are optional. If a partition column value is given, we call this a static partition, otherwise it is a dynamic partition. Each dynamic partition column has a corresponding input column from the select statement. This means that the dynamic partition creation is determined by the value of the input column. The dynamic partition columns must be specified last among the columns in the SELECT statement and in the same order in which they appear in the PARTITION() clause.

So, in your query, partition_dt is the value of the dynamic partition. However, you impose the following constraints: (partition_dt = '2016-10-02' AND partition_dt < '2016-10-07') which translate to partition_dt = '2016-10-02' and eventually it creates a single partition.

You probably wanted a range of dates: (partition_dt >= '2016-10-02' AND partition_dt < '2016-10-07')

Insert into hive table with dynamic partition only writing first partition to disk and not all

1 Answers1