Copying files from AWS S3 to Snowflake Table using Snowflake Stored Procedure

Question

When I try to execute the procedure to copy the files from S3 bucket to Snowflake table dynamically using the stored procedure, it is throwing a below error :

SQL compilation error:
Unknown function UDF_GET_COPY_COLUMNS
At Statement.execute, line 4 position 20

Code:

CREATE OR REPLACE PROCEDURE SF_COPY_LEAD_COLLECT_STAGE()
RETURNS VARIANT NOT NULL
LANGUAGE JAVASCRIPT
AS
$$
var row_as_json = {};
var udfstmt = snowflake.createStatement({sqlText: "SELECT UDF_GET_COPY_COLUMNS();"})
var udfrs = udfstmt.execute();
udfrs.next();
var COL_NAMES = udfrs.getColumnValue(1);
var command = "COPY INTO SF_ADVEN_COLLECT_DB.PRODUCT_HUB.PRODUCT FROM @DEV_NP_ADV_RAW_DB.PRODUCT_HUBS.NP_ADVEN_EXT_STG/PRODUCT/PRODUCT_202401121_231143.txt; ";
var stmt = snowflake.createStatement({sqlText: command});
var rs = stmt.execute();
//Move to the First Row Returned 
rs.next();
//check for error 
var errors_seen = rs.getColumnValue(6);
if (errors_seen > 0) 
{
throw new Error("Copy Command encountered Errors: "+errors_seen);
}
// Loop through the columns retured by the copy command.push each as key-value pair into a json object to return
for (var col_num = 0; col_num < COL_NAMES.length; col_num = col_num + 1)
{
var col_name = COL_NAMES[col_num];
row_as_json[col_name] = rs.getColumnValue(col_num + 1);
}
return row_as_json;
$

$

samhita · Accepted Answer · 2024-11-22 20:23:07Z

0

There could be few possible causes causing the function UDF_GET_COPY_COLUMNS() to error out

Function Scope: The function UDF_GET_COPY_COLUMNS() may be defined in a different schema, and you're not referencing it with the appropriate schema name.

Context Issue: If the function is defined in a different role or if it's a different type of object (e.g., a stored procedure or view), Snowflake might not be able to resolve the function properly.

You can check few things :

Check if the function exists: Run the following query to check if the function UDF_GET_COPY_COLUMNS() exists in the current database/schema:

SHOW FUNCTIONS LIKE 'UDF_GET_COPY_COLUMNS';

If the function doesn't exist, you may need to define it or ensure it's available in the correct schema. If it does exist, ensure the schema is correct.

Explicitly reference the schema: If the function is in a different schema, ensure you're explicitly referencing it by its schema name. For example:

var udfstmt = snowflake.createStatement({sqlText: "SELECT <schema_name>.UDF_GET_COPY_COLUMNS();"});

Check for access privileges: Ensure the role you're using has the necessary permissions to access the function UDF_GET_COPY_COLUMNS().

Alternate option :

If you don't need the UDF and simply want to dynamically get column names for your COPY INTO operation, you could alternatively query the metadata for the table you're copying into. For example, you could dynamically retrieve column names using the INFORMATION_SCHEMA.COLUMNS table:

var udfstmt = snowflake.createStatement({
    sqlText: `SELECT COLUMN_NAME 
              FROM INFORMATION_SCHEMA.COLUMNS 
              WHERE TABLE_NAME = 'PRODUCT' 
              AND TABLE_SCHEMA = 'PRODUCT_HUB' 
              AND TABLE_CATALOG = 'SF_ADVEN_COLLECT_DB'`
});

var udfrs = udfstmt.execute();
var COL_NAMES = [];
while (udfrs.next()) {
    COL_NAMES.push(udfrs.getColumnValue(1));
}

answered Nov 22, 2024 at 20:23

samhita

4,2512 gold badges12 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Abhishek Mitra Jan 5 at 4:21

Thanks Samhita. I have used the alternate the option, it worked and successfully processed the records.

samhita Jan 5 at 9:57

Glad to know, since the question was regarding the UDF error and it got fixed using alternate option this should be accepted as an answer as your answer has used the alternate option to fix the error.

Abhishek Mitra · Accepted Answer · 2025-01-05 04:28:10Z

Correct Version of the Code

CREATE OR REPLACE PROCEDURE ORDERDETAILS_COPY_SF_SP()
RETURNS VARIANT NOT NULL
LANGUAGE JAVASCRIPT
AS
$$
var row_as_json = {};
// Fetch column names from ORDERDETAILS
var udfstmt = snowflake.createStatement({
    sqlText: "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ORDERDETAILS' AND TABLE_SCHEMA = 'SALES' AND TABLE_CATALOG = 'DEV_SALES_COLLECT_DB';"
});
var udfrs = udfstmt.execute();
var COL_NAMES = [];
while (udfrs.next()) {
    COL_NAMES.push(udfrs.getColumnValue(1)); // Collect column names into an array
}

// Construct the COPY INTO command
var command = "COPY INTO DEV_SALES_COLLECT_DB.SALES.ORDERDETAILS(ORDERNUMBER,QUANTITYORDERED,PRICEEACH,ORDERLINENUMBER,SALES,ORDERDATE,STATUS,QTR_ID,MONTH_ID,YEAR_ID,PRODUCTLINE,MSRP,PRODUCTCODE,CUSTOMERNAME,PHONE,ADDRESSLINE1,ADDRESSLINE2,CITY,STATE,POSTALCODE,COUNTRY,TERRITORY,CONTACTLASTNAME,CONTACTFIRSTNAME,DEALSIZE,LOADDATE) " + 
"FROM (SELECT $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, REPLACE($17,'�',','), $18, $19, $20, $21, $22, $23, $24, $25, CURRENT_TIMESTAMP " + 
"FROM @NP_SALES_EXT_STG/SalesAnalysis/) " +
"PATTERN = '.*SalesAnalysis_+.*[_].*.txt' ON_ERROR = 'SKIP_FILE';";

// Execute the COPY INTO command
var stmt = snowflake.createStatement({sqlText: command});
var rs = stmt.execute();
rs.next();

// Check for errors in the COPY INTO command result
var errors_seen = rs.getColumnValue(1); // Get the error count
if (errors_seen > 0) {
    throw new Error("COPY INTO command encountered errors: " + errors_seen);
}

// Fetch the data from the ORDERDETAILS table to return as JSON (after copy)
var select_stmt = snowflake.createStatement({
    sqlText: "SELECT " + COL_NAMES.join(", ") + " FROM DEV_SALES_COLLECT_DB.SALES.ORDERDETAILS ORDER BY LOADDATE DESC LIMIT 1"
});
var select_rs = select_stmt.execute();
select_rs.next();

// Populate the JSON object with the data from the ORDERDETAILS table
for (var col_num = 0; col_num < COL_NAMES.length; col_num++) {
    var col_name = COL_NAMES[col_num];
    row_as_json[col_name] = select_rs.getColumnValue(col_num + 1);
}

return row_as_json;
$$;

Collectives™ on Stack Overflow

Copying files from AWS S3 to Snowflake Table using Snowflake Stored Procedure

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related