I'm developing a AWS Lambda in python which will trigger by API Gateway and lambda will connect my snowflake. I'll process few CSV files via API Gateway to get some data from snowflake. Currently I'm using Python connector to connect Snowflake.

My issue is, if my csv has 100 records so it process the records recursively and it connects snowflake from lambda every time to process each record and its impacting on the performance.

Is there any method or mechanism that lambda can create a session for certain period of time and process all records in single connection.

CodePudding user response：

As far as I know, connect() will automatically create a session that will last for a period of time. Once connected, you can use the cursor to execute multiple commands without needing to call connect() every time. Docs here. But I'm guessing you know this, and what you want is a single command instead of having to call multiple INSERT.

This is also possible, using a STAGE and COPY INTO command instead of INSERT. You can find an example from Snowflake documentation for bulk loading from AWS S3 here.