Skip to main content
Solved

Using SQL Variables in Transformations


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

 

View original
Did this topic help you find an answer to your question?

Forum|alt.badge.img

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
Active

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.


Anita Hæhre
Seasoned Practitioner
Forum|alt.badge.img+1
  • Head of Academy and Community
  • February 20, 2023

Hi @Glen Accardo may I suggest you share this as a request in Product Ideas as upvotes from peers might make our product team deliver this for you :)


Forum|alt.badge.img

Hi,

I also have a requirement for stored procedures, and scalar values. Has a product Idea been requested?


Reply


Cookie Policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie Settings