Using SQL Variables in Transformations

Question

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

icon

Best answer by Vu Hai Nguyen 15 February 2023, 10:23

View original

Vu Hai Nguyen · Accepted Answer

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

Glen Accardo · Answer

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.

Using SQL Variables in Transformations

3 replies

Reply

Reply

Sign up

Log in to the community

Scanning file for viruses.

This file cannot be downloaded