Skip to main content

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

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

 

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.

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 :)

Hi,

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

Reply


Terms & ConditionsCookie Settings