topic Re: SET configuration in SQL DLT pipeline does not work in Data Engineering

SET configuration in SQL DLT pipeline does not work

MarkD — Fri, 10 May 2024 16:25:34 GMT

Hi,

I'm trying to set a dynamic value to use in a DLT query, and the code from the example documentation does not work.

SET startDate='2020-01-01'; CREATE OR REFRESH LIVE TABLE filtered AS SELECT * FROM my_table WHERE created_at > ${startDate};

It is getting an error because it plugs in an empty value for the variable in the query.

[PARSE_SYNTAX_ERROR] Syntax error at or near end of input. SQLSTATE: 42601 (line 3, pos 19) == SQL == CREATE OR REFRESH LIVE TABLE filtered AS SELECT * FROM my_table WHERE created_at > -------------------^^^

It seems like the documentation is wrong. Is there a different way to do this?

Thanks!

Re: SET configuration in SQL DLT pipeline does not work

anardinelli — Thu, 16 May 2024 19:34:29 GMT

Hi @MarkD how are you?

The `SET` command alone is typically used to change the settings of a session, such as configuration parameters.

To define a variable inside a SQL statement, you can use SET VAR <variablename> = <variablevalue> or SET VARIABLE <variablename> = <variablevalue> to define, as on the example below:

-- Set a SQL variable to a value using SET VAR
> SET VAR myvar1 = 5;
> VALUES (myvar1);
  5

-- Set a SQL variable to 7 using the SET VARIABLE command
> SET VARIABLE myvar1 = 7;
> VALUES (myvar1);
  7

Please relate to the SET VAR documentation for further reference: https://docs.databricks.com/ja/sql/language-manual/sql-ref-syntax-aux-set-variable.html

Take care!

Re: SET configuration in SQL DLT pipeline does not work

MarkD — Thu, 16 May 2024 19:42:49 GMT

The example I gave is directly from Databrick's DLT documentation, but as you say does not appear the be the right way to set a variable and doesn't work.

Unfortunately SET VAR is not supported in Delta Live Tables.

Re: SET configuration in SQL DLT pipeline does not work

anardinelli — Thu, 16 May 2024 20:39:43 GMT

For DLT pipelines, please use the "Advanced" panel, on the pipeline menu, to set your variables:

That will set up a new configuration value that can be referenced inside your code as a variable:

CREATE OR REFRESH LIVE TABLE filtered
AS SELECT * FROM my_table
WHERE created_at > ${startDate};

Would this solve your issue? Also, can you please share the documentation from DLT that states that the SET command can be used to create variables?

Alessandro

Re: SET configuration in SQL DLT pipeline does not work

MarkD — Thu, 16 May 2024 21:33:18 GMT

I've used the advanced configuration option for other variables, but in this case I wanted something dynamic. I want to `SET startDate = current_time()` and then use `startDate` through all my queries to make sure they are all using the same time comparison in their queries.

Here's the documentation that shows using "SET" as I did in the initial question. https://docs.databricks.com/en/delta-live-tables/sql-ref.html#set-configuration-values-for-a-table-or-view

Re: SET configuration in SQL DLT pipeline does not work

Icassatti — Fri, 17 May 2024 00:12:38 GMT

@MarkD I beleve that you don't need use a var like that.

What do you think about use only a function current_time(), like that:

CREATE OR REFRESH LIVE TABLE filtered AS SELECT * FROM my_table WHERE created_at > current_time();

Re: SET configuration in SQL DLT pipeline does not work

MarkD — Fri, 17 May 2024 01:18:22 GMT

The reason I want to use a variable is so that I can use the same cut off time in multiple queries to make sure to make sure that I don't get records in one query with a later date than another.

For example

CREATE OR REFRESH LIVE TABLE filtered_one AS SELECT * FROM my_table_one WHERE created_at > current_time(); CREATE OR REFRESH LIVE TABLE filtered_two AS SELECT * FROM my_table_two WHERE created_at > current_time();

If I want to compare records in `filtered_one` to the results in `filtered_two` I can't rely on them having used the same value for `current_time()`.

Re: SET configuration in SQL DLT pipeline does not work

Hkesharwani — Fri, 17 May 2024 13:29:58 GMT

Hi @MarkD ,
You may use

set variable_name.var= '1900-01-01'

to set the value of variable and in order to use the value of variable
use

${automated_date.var}

Example:

set automated_date.var= '1800-01-01' select * from my table where date = CAST(${automated_date.var} as DATE)

Re: SET configuration in SQL DLT pipeline does not work

Icassatti — Fri, 17 May 2024 17:16:23 GMT

Have you considered use checkpoint?
If you do this maybe empty dataset comes becouse there are no date in future... created_at > current_time().

Read more about [Change Data Feed] Enable it in your base table and then capture only new changes feed.

DLT Doc

Stream Apply Changes

Re: SET configuration in SQL DLT pipeline does not work

smit_tw — Thu, 28 Nov 2024 17:16:43 GMT

@anardinelli Can you please help with a solution? I am am having issue with setting a variable in delta live table pipeline and use it with APPLY CHANGES INTO syntax.

Re: SET configuration in SQL DLT pipeline does not work

anardinelli — Thu, 05 Dec 2024 14:15:56 GMT

@smit_tw Have you tried setting it on the "Advanced" tab as my previous suggests?

Re: SET configuration in SQL DLT pipeline does not work

SFDataEng — Fri, 25 Jul 2025 22:02:27 GMT

This ${configuration_key} syntax works great in DLT pipelines. For a DLT pipeline, the Advanced panel navigation is as follows: Click Jobs & Pipelines (left panel) > Search or find Pipeline name and click Pipeline name (middle) > Settings (top right) > Advanced (bottom of page) > Save (extreme bottom right).