Move S3 files to Snowflake stage using Airflow PUT command

0
votes

I am trying to find a solution to move files from an S3 bucket to Snowflake internal stage (not table directly) with Airflow but it seems that the PUT command is not supported with current Snowflake operator.

I know there are other options like Snowpipe but I want to showcase Airflow's capabilities. COPY INTO is also an alternative solution but I want to load DDL statements from files, not run them manually in Snowflake.

This is the closest I could find but it uses COPY INTO table:

https://artemiorimando.com/2019/05/01/data-engineering-using-python-airflow/

Also : How to call snowsql client from python

Is there any way to move files from S3 bucket to Snowflake internal stage through Airflow+Python+Snowsql?

Thanks!

1
amazon-s3airflowsnowflake-cloud-data-platformsnowsql
This is an unusual request. Why are you trying to move files from S3 to internal stage without the intent to load them into Snowflake? – Mike Walton
@MikeWalton I do have that intent but I want to do it in 2 steps: 1. Move files to stage with PUT 2. COPY INTO I know things can be done easily but the key here is showcasing Airflow orchestration. – costinrio

1 Answers

2
votes

I recommend you execute the COPY INTO command from within Airflow to load the files directly from S3, instead. There isn't a great way to get files to internal stage from S3 without hopping the files to another machine (like the Airflow machine). You'd use SnowSQL to GET from S3 to local, and the PUT from local to S3. The only way to execute a PUT to Internal Stage is through SnowSQL.