https://prefect.io logo
#prefect-community
Title
# prefect-community
j

Jonathan Mathews

05/04/2022, 3:43 PM
Hi, I’m trying to use the Snowflake Task to execute a “COPY INTO” query. It works fine with SELECT queries, but it just isn’t running the query when I switch it to a copy into and the task is coming back with TriggerFailed
I’m not 100% sure how to get any logs out on the prefect local end, but I can see from the snowflake logs that the query isn’t executing
I’ve tried setting
autocommit
to true
k

Kevin Kho

05/04/2022, 3:47 PM
I am looking at this
Could you try without Prefect using
snowflake.connector
and seeing if there is no error too?
j

Jonathan Mathews

05/04/2022, 3:50 PM
thanks, you mean using the native Snowflake Python connector within a prefect task?
I’ve tried the same command in the snowflake console and it works
k

Kevin Kho

05/04/2022, 3:52 PM
No I mean Snowflake Python connector but no task first just to see it works
j

Jonathan Mathews

05/04/2022, 4:03 PM
ok just tried. that works fine
a

Andrew Huang

05/04/2022, 4:04 PM
Can you share your query?
j

Jonathan Mathews

05/04/2022, 4:07 PM
Copy code
"copy into raw.azure.all_tables_all_clients from (select $1::variant as value, cast(split_part(split_part(metadata$filename,  '/', -1),  '__', 1) as varchar(100)) as client_name, cast(split_part(split_part(metadata$filename,  '/', -1),  '__', 2) as varchar(100)) as table_name, cast(ltrim(split_part(split_part(metadata$filename,  '/', -1),  '__', -1),  '.parquet' ) as datetime) as export_time, cast(split_part(metadata$filename,  '/', -1) as varchar(200)) as filename, cast(metadata$filename as varchar(500)) as filepath from @reporting_raw/test/test.parquet )"
a

Andrew Huang

05/04/2022, 4:09 PM
I wonder if it’s because the Prefect task tries to “fetchall” after
Copy code
executed = cursor.execute(query, params=data).fetchall()
j

Jonathan Mathews

05/04/2022, 4:46 PM
Ok, I think the issue was actually nothing to do with this task, I had another problem with my code that I’ve now resolved. I’m now using the python connector so haven’t verified, but will do later. Thanks for your help!
Confirmed it works!
1
Sorry about that
k

Kevin Kho

05/04/2022, 5:04 PM
No worries!
11 Views