topic Re: Using parameters in a SQL Notebook and COPY INTO statement in Data Engineering

Using parameters in a SQL Notebook and COPY INTO statement

SamGreene — Thu, 09 May 2024 16:49:03 GMT

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.

Re: Using parameters in a SQL Notebook and COPY INTO statement

daniel_sahal — Fri, 10 May 2024 05:57:20 GMT

@SamGreene
Simply write your sql queries as a python variables and then run them through

spark.sql(qry)

Re: Using parameters in a SQL Notebook and COPY INTO statement

SamGreene — Fri, 10 May 2024 23:31:52 GMT

Thanks for the suggestion, but we are using SQL in these notebooks and databricks documentation says COPY INTO supports using the IDENTIFIER function. I need to find a way to parameterize sql notebooks to run them against different catalog/schema.

Re: Using parameters in a SQL Notebook and COPY INTO statement

Cary — Wed, 15 May 2024 18:05:36 GMT

I would use widgets in the notebook which will process in Jobs. SQL in Notebooks can use parameters, as would the SQL in the jobs with parameterized queries now supported.

Re: Using parameters in a SQL Notebook and COPY INTO statement

SamGreene — Wed, 05 Jun 2024 17:33:34 GMT

The solution that worked what adding this python cell to the notebook:

%python

from pyspark.dbutils import DBUtils

dbutils = DBUtils(spark)

dbutils.widgets.text("catalog", "my_business_app")

dbutils.widgets.text("schema", "dev")

Then in the SQL Cell:

CREATE TABLE IF NOT EXISTS ${catalog}.${schema}.my_table_name;