Using a string variable in sql script
If we use a string variable in PQL editor while creating a SQL Script node, the variable is being enclosed in quotes that is causing the sql to fail.
Example:
@ColumnList is set to: column1,column2,column3
SQL Script node has: Select @ColumnList FROM TABLE
The sql that seems being sent to database (and failing) is: Select 'column1,column2,column3' FROM TABLE
How can we avoid sending single quotes around @ColumnList.
The database is snowflake.
4 replies
-
Injecting SQL this way is not supported.
Variables are meant to pass values, not SQL syntax.
Even if SQL injection was supported (it's not too complex to add this option), what you are trying to achieve is a dynamic column list, which would break the whole way ETLs and Data Models work, where columns must be well defined in design time.
-
We have several files to be processed. There is a Python script that preprocesses the file and gives column list apart from placing the file in an external stage in snowflake. We just need to run the copy command where the column list is injected from the python script. It is working all the way except when sql script adding the quote for string variable. Pyramid should have a way to do this. Otherwise we have to create 40 different flows which is not really scaleable for developer.
-
Pyramid just have to run this copy command.
COPY INTO "Schema"."Table" (@headers)
FROM '@" Schema "."EXTERNAL_STAGE"'
PATTERN = '*.gz'
FILE_FORMAT = (TYPE = CSV FIELD_DELIMITER = '|' skip_header=@start_of_data ERROR_ON_COLUMN_COUNT_MISMATCH = false)
ON_ERROR=CONTINUE;
The variables @headers and @start_of_data are to be replaced in the sql script node.
-
We had to run COPY/MERGE commands for better performance. Pyramid seems using JDBC inserts (may be batched) which will be much slower compared to Bulk copy of the data using COPY/MERGE. The files are very large (7mn+ records in each) and about 30 in number.