Snowflake procedure call through AWS Lambda function using Python code

Question

Is it possible to call Snowflake Procedure (It has a Merge statement which copies data from Stage to snowflake main table) through AWS Lambda function? I want the function to be triggered as soon as we push a file to the S3 stage. We have a Snowpile option to copy the data but I don't want another table like one to copy the data from the S3 bucket to the snowflake stage and then merge it to the master table rather I have a merge statement which directly merges the data from the file in S3 bucket to the snowflake master table.

I would recommend to use SnowPipe for this. AWS Lambda has a limitation on how long it can run, I think it is 15 minutes. If your processing takes more time, it will time out. — Rajib Deb
– Rajib Deb, Commented Sep 14, 2020 at 8:34
I believe snow pipe doesn't have flexibility to use MERGE operation as it only supports COPY operation from stage to the main table but to reduce extra computation and additional space, I am trying to use merge operation directly from S3 Stage to Snowflake master table. If time duration is an issue, I can think of splitting the file in small size. — Ashish Kumar
– Ashish Kumar, Commented Sep 14, 2020 at 9:25

Danny C · Accepted Answer · 2020-09-14 20:53:23Z

1

first I would create an external stage in snowflake:

CREATE STAGE YOUR_STAGE_NAME
  URL='s3://your-bucket-name'
  CREDENTIALS = (
    AWS_KEY_ID='xxx'
    AWS_SECRET_KEY='yyy'
  )
  FILE_FORMAT = (
    TYPE=CSV
    COMPRESSION=AUTO,
    FIELD_DELIMITER=',',
    SKIP_HEADER=0,
    ....
  );

then I would call a query on your csv file in the stage using your python lambda script (pulling the file name from the python event payload):

WITH file_data AS (
   select t.$1, t.$2,<etc..> from @YOUR_STAGE_NAME/path/your-file.csv t
)
MERGE INTO MASTER_TABLE USING FILE_DATA ON <......>

you should be able to wrap this into a stored procedure if you so desire. there is also syntax to skip creating the named external stage and reference the bucket name and credentials within the query call, but I wouldn't recommend this since it would expose your aws credentials in plaintext in your stored procedure definition

answered Sep 14, 2020 at 20:53

Danny C

3582 silver badges9 bronze badges

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

1 Comment

Ashish Kumar Over a year ago

Thanks Danny! Your solution seems to be fine. I also tried this with little different way -

Collectives™ on Stack Overflow

Snowflake procedure call through AWS Lambda function using Python code

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related