Answer

Using SQL Variables in Transformations

Forum|Forum|2 years ago
February 14, 2023
4 replies
128 views

Glen Accardo
Active

I would like to use variables in a SQL transformations, trying the syntax shown in this StackOverflow article. However, the following SQL fails with the message “Query is not safe for execution. It must not contain commands like CREATE, DROP, INSERT, etc., but found node: SetCommand.” How can I use SQL variables with this Spark implementation?

SET startDate="2023-01-01" SET endDate="2023-12-31"

SELECT explode(sequence(to_timestamp(${startDate}), to_timestamp(${endDate}), interval 12 hours)) AS timestamp

Best answer by Vu Hai Nguyen

Hi,

Unfortunately we don’t support SET command yet.
I can suggest you a workaround for this using CTE as below:

with myTime(startDate,endDate) as (select "2023-01-01", "2023-12-31")
SELECT explode(sequence(to_timestamp(startDate), to_timestamp(endDate), interval 12 hours)) AS timestamp from myTime

V

Vu Hai Nguyen
Practitioner
Answer
Forum|Forum|2 years ago
February 15, 2023

Hi,

Unfortunately we don’t support SET command yet.
I can suggest you a workaround for this using CTE as below:

with myTime(startDate,endDate) as (select "2023-01-01", "2023-12-31")
SELECT explode(sequence(to_timestamp(startDate), to_timestamp(endDate), interval 12 hours)) AS timestamp from myTime

K

Like

Glen Accardo
Author
Active
Forum|Forum|2 years ago
February 20, 2023

For this case, the subquery is a good solution. Thank you. Some of the SQL transforms I’m planning would benefit from stored procedures, functions, and scalar values, just to make them easier to debug/read. Allowing a little bit more SQL would be appreciated.