Using parameters in a SQL Notebook and COPY INTO statement

SamGreene
Contributor II

‎05-09-2024 09:49 AM

Hi,
 
My scenario is I have an export of a table being dropped in ADLS every day.  I would like to load this data into a UC table and then repeat the process every day, replacing the data.  This seems to rule out DLT as it is meant for incremental processing and if I remember correctly, it didn't detect the new file and even attempt to load it.  I switched to using a SQL notebook and got the code working with hardcoded 'catalog.schema' .  Now I suppose I need to parameterize this unless there is some other way to set the schema context through the workflow/job. We used the parameter markers/widgets and the first few statements work, but the COPY INTO statement throws this error: [DELTA_COPY_INTO_TARGET_FORMAT] COPY INTO target must be a Delta table.  Thanks for your help. 
 
CREATE TABLE IF NOT EXISTS IDENTIFIER(:catalog_name || '.' || :schema_name || '.' || 'my_table_raw');
 
DELETE FROM IDENTIFIER(:catalog_name || '.' || :schema_name || '.' || 'my_table_raw');

COPY INTO IDENTIFIER(:catalog_name || '.' || :schema_name || '.' || 'my_table_raw')
FROM '/Volumes/path/to/file/my_table_export.parquet'
FILEFORMAT = PARQUET
FORMAT_OPTIONS ('mergeSchema' = 'true')
COPY_OPTIONS ('mergeSchema' = 'true','force' = 'true');
 
[DELTA_COPY_INTO_TARGET_FORMAT] COPY INTO target must be a Delta table.
0 Kudos