- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
02-23-2024 04:51 AM
Hi all,
As part of an on-going exercise to refactor existing T-SQL code into Databricks, we've stumbled into an issue that we can't seem to overcome through Spark SQL.
Currently we use dynamic SQL to loop through a number of tables, where we use parameters to specify field names among other elements. We're attempting to use the EXECUTE IMMEDIATE functionality combined with session variables to achieve the same result.
However, when trying to achieve this in Databricks it does not seem to interpret the variables as expected. See example code below:
DECLARE varfield_names1 STRING;
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
02-26-2024 07:54 AM
DECLARE OR REPLACE varfield_names1 STRING;
SET VAR varfield_names1 = 'field1 STRING';
DECLARE OR REPLACE varsqlstring1 STRING;
SET VAR varsqlstring1 = 'CREATE TABLE table1 (PrimaryKey STRING, Table STRING, ' || varfield_names1 || ')';
EXECUTE IMMEDIATE varsqlstring1;
I took some guess on your intent. It helps if you "print" I.e. SELECT varsqlstrin1 before trying to execute it, so you know you glued together the right statement.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
02-23-2024 07:49 AM
@SergeRielau - appreciate you've been posting recently on EXECUTE IMMEDIATE - really insightful. Wonder if you'd be able to assist with the above!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
02-26-2024 07:54 AM
DECLARE OR REPLACE varfield_names1 STRING;
SET VAR varfield_names1 = 'field1 STRING';
DECLARE OR REPLACE varsqlstring1 STRING;
SET VAR varsqlstring1 = 'CREATE TABLE table1 (PrimaryKey STRING, Table STRING, ' || varfield_names1 || ')';
EXECUTE IMMEDIATE varsqlstring1;
I took some guess on your intent. It helps if you "print" I.e. SELECT varsqlstrin1 before trying to execute it, so you know you glued together the right statement.