Support Questions

Find answers, ask questions, and share your expertise

MERGE INTO hive table from source partition

avatar
New Contributor

I'm trying to script a beeline command that goes over multiple partitions from a source table, to merge into a destination table. I'm not able to get the syntax right it seems. I removed the dynamic partition entries and just trying with a single partition from the source. Here's what I'm trying:

MERGE INTO
dest.cust_table as dst
USING
source.cust_stg_part PARTITION ("201610") as src
ON
dst.cust_nbr = src.cust_nbr
WHEN MATCHED AND dst.process_date < src.process_date

do stuff...

 

The error: FAILED: ParseException line 4:37 mismatched input 'PARTITION' expecting ON ...

 

I cannot use a where clause, cause the source doesnt have a column of the partition. How can I Merge into a table from a source partition by partition?

2 REPLIES 2

avatar
New Contributor

Can we not edit our posts?

Of course i was mistaken on using partition column in where clause. I tried that as well, but it failed. 

MERGE INTO
dest.cust_table as dst
USING
source.cust_stg_part as src
ON
dst.cust_nbr = src.cust_nbr
WHERE src.cal_month = 201610

WHEN MATCHED AND dst.process_date < src.process_date

do stuff...

And the error: Error: Error while compiling statement: FAILED: RewriteEmptyStreamException rule whenClauses

avatar
New Contributor

OK, i just had to change the USING statement as a select statement:

USING (
select * from source.cust_stg_part where cal_month=${part}
)
as src