I am trying to use dbt-athena-community in dbt core and while converting my merge into procedure to dbt syntax, getting this error: mismatched input ‘inserts’. Expecting: ‘(’, ‘ALL’, ‘DISTINCT’, ‘SELECT’, ‘TABLE’, ‘VALUES’.
I am trying to convert my DML stored procedure to a dbt syntax having Update/Inserts
What I’ve already tried
I have referred to this documentation and created my own model:
WITH
using_clause AS (
SELECT merchant_id, corporate_id, merchant_name, xls_terminalid_count, addr, last_update_by, last_update_date, status, merchant_no, geo_coord, lat, lon, marketing_desc, referencelogo, loyaltypartnerprogramid, loyaltypartnerprogramname, merchantprogramdescription, cpg_sub_forbid, is_active_for_redemption, extracted_date,cast(current_timestamp as timestamp(6)) as insert_date,file_name,cast(current_timestamp as timestamp(6)) as load_timestamp
from (
select * , ROW_NUMBER () over (partition by merchant_id order by extracted_date desc) rn
FROM xls_delta.merchant
) a where a.rn = 1
{% if is_incremental() %}
and load_timestamp > (SELECT max(load_timestamp) FROM {{ this }})
{% endif %}
),
updates AS (
SELECT
corporate_id, merchant_name, xls_terminalid_count, addr, last_update_by, cast(last_update_date as timestamp(6)) last_update_date, status, merchant_no, geo_coord, lat, lon, marketing_desc, referencelogo, loyaltypartnerprogramid, loyaltypartnerprogramname, merchantprogramdescription, cpg_sub_forbid, is_active_for_redemption, extracted_date,cast(current_timestamp as timestamp(6)) as insert_date,file_name,load_timestamp
FROM using_clause
{% if is_incremental() %}
WHERE merchant_id IN (SELECT merchant_id FROM {{ this }})
{% endif %}
),
inserts AS (
SELECT
merchant_id,corporate_id, merchant_name, xls_terminalid_count, addr, last_update_by, cast(last_update_date as timestamp(6)) last_update_date, status, merchant_no, geo_coord, lat, lon, marketing_desc, referencelogo, loyaltypartnerprogramid, loyaltypartnerprogramname, merchantprogramdescription, cpg_sub_forbid, is_active_for_redemption, extracted_date,cast(current_timestamp as timestamp(6)) as insert_date,file_name,load_timestamp
FROM using_clause
WHERE merchant_id NOT IN (SELECT merchant_id FROM updates)
)
SELECT * FROM updates UNION inserts
The error I am getting:
Failed to execute query.
dbt | Traceback (most recent call last):
dbt | File “/usr/local/lib/python3.10/site-packages/pyathena/common.py”, line 522, in _execute
dbt | query_id = retry_api_call(
dbt | File “/usr/local/lib/python3.10/site-packages/pyathena/util.py”, line 85, in retry_api_call
dbt | return retry(func, *args, **kwargs)
dbt | File “/usr/local/lib/python3.10/site-packages/tenacity/init.py”, line 379, in call
dbt | do = self.iter(retry_state=retry_state)
dbt | File “/usr/local/lib/python3.10/site-packages/tenacity/init.py”, line 314, in iter
dbt | return fut.result()
dbt | File “/usr/local/lib/python3.10/concurrent/futures/_base.py”, line 451, in result
dbt | return self.__get_result()
dbt | File “/usr/local/lib/python3.10/concurrent/futures/_base.py”, line 403, in __get_result
dbt | raise self._exception
dbt | File “/usr/local/lib/python3.10/site-packages/tenacity/init.py”, line 382, in call
dbt | result = fn(*args, **kwargs)
dbt | File “/usr/local/lib/python3.10/site-packages/botocore/client.py”, line 535, in _api_call
dbt | return self._make_api_call(operation_name, kwargs)
dbt | File “/usr/local/lib/python3.10/site-packages/botocore/client.py”, line 980, in _make_api_call
dbt | raise error_class(parsed_response, operation_name)
dbt | botocore.errorfactory.InvalidRequestException: An error occurred (InvalidRequestException) when calling the StartQueryExecution operation: line 47:29: mismatched input ‘inserts’. Expecting: ‘(’, ‘ALL’, ‘DISTINCT’, ‘SELECT’, ‘TABLE’, ‘VALUES’