Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

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