Call Snowflake Stored Procedure using Python in AWS Glue

1
votes

I have a pipeline wherein i need to call Snowflake stored procedure in AWS Glue job using Python shell.

Can anyone please suggest the appropriate code for the same.

Thanks

2
pythonsnowflake-cloud-data-platform
What we ran into last year was the following: The Snowflake Python Connector depend on some cryptography packages on the OS, however Glue does not allow you to install those packages. So there is no way to install the required packages to use the snowflake connector. You can use Spark and connect to Snowflake, however that does not allow for stored procedure execution AFAIK. We moved away from Glue to Airflow. - Blokje5

2 Answers

0
votes

For DDL/DML statement executions, the Snowflake Spark Connector offers a utility function: net.snowflake.spark.snowflake.Utils.runQuery(…)

Since its a Java class not offered directly within the Python libraries, you'll need to call it through the Py4J interface within Spark driver's runtime:

# A direct call:

sc._gateway.jvm.net.snowflake.spark.snowflake.Utils.runQuery(sfOptions, "CALL SF_TEST_DB.STRUCTURED.TEST()")

# Alternatively, using an import style:

from py4j.java_gateway import java_import
java_import(sc._gateway.jvm, "net.snowflake.spark.snowflake.Utils")
Utils = sc._gateway.jvm.Utils

Utils.runQuery(sfOptions, "CALL SF_TEST_DB.STRUCTURED.TEST()")

There is a caveat though. Quoting from the documentation:

The runQuery method returns only TRUE or FALSE. It is intended for statements that do not return a result set, for example DDL statements like CREATE TABLE and DML statements like INSERT, UPDATE, and DELETE. It is not useful for statements that return a result set, such as SELECT or SHOW.

0
votes

Was able to connect to snowflake using python connector in AWS glue. Please refer to the link below to build external python library package for snowflake connector to be used in AWS glue https://github.com/snowflakedb/snowflake-connector-python/blob/HEAD/test/README.rst

Thanks