dynamic - Copying files from AWS S3 to Snowflake Table using Snowflake Stored Procedure - Stack Overflow

admin管理员组

文章数量:1122832

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;
$

$

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;
$

$

• dynamic
• snowflake-cloud-data-platform
• copy
• load

Share Improve this question Follow edited Nov 22, 2024 at 20:32 marc_s 754k183183 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Nov 22, 2024 at 17:53 Abhishek MitraAbhishek Mitra 8344 bronze badges 0

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 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));
}

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;
$$;

本文标签： dynamicCopying files from AWS S3 to Snowflake Table using Snowflake Stored ProcedureStack Overflow