Snowflake SQL - Dynamically Create Table based on File Upload - Error

[abretten]

• Goal: <!--- I want to be able to upload a file, then dynamically look at what the columns are called and design a snowflake table based off the column name (hence building the query in JavaScript). A user uploads a file and has a table automatically made for them in snowflake without them having to do anything. If you can suggest another way to do this that would also be great.

• Steps: <!---

1. I created a snowflake query using javascript : “CREATE OR REPLACE TABLE SN_DEV_ALEX.INGEST.RETOOL_UPLOAD (Name VARCHAR, DOB VARCHAR);”

2. When I try to run this by using {{CreateSnowflakeTableQuery.data}} in the Snowflake SQL resource, I get an error saying there “unexpected :”

[abretten]

to show the statement does work, the issue is around the Snowflake Resource understanding the string from the JS object

[abretten]

Further, if i try statement {{ 'CREATE TABLE .......' }} i get the same error. So supply {{ }} to Snowflake results in the process somehow seeing ":".

[briannguyen]

Hey abretten,

Welcome to to the community!

There are 2 things I would try
Previous Context

1. [Try First] Once the file is uploaded, iterate over the columns and create a string like you were doing previously but instead of using the column names directly in your sql statement, create variables and reference those.
   Example:

-- Set the table name and columns dynamically
SET table_name = 'YOUR_TABLE_NAME_HERE';
SET columns = 'col_1 STRING, col_2 STRING, col_3 INT';  -- Define columns as a string

-- Use EXECUTE IMMEDIATE to create or replace the table dynamically
EXECUTE IMMEDIATE 
$
CREATE OR REPLACE TABLE IDENTIFIER($table_name) ($columns);
$;

2. [Not Recommended] If the first option doesn't work, try Disabling prepared statements on your Snowflake Resource. This is not recommended depending on the sensitivity of your data due to the loss of protection of sql injection, but can be used to verify if the dynamic statement works.
   
   

Give those a try and keep us updated!