Can a connection to an on-prem database (e.g. Sybase) be made directly from Snowflake, or does it need to be made external to Snowflake? E.g., I know I can make a connection to my DB in some AWS compute (EMR, Lambda, EC2) and then load that data into Snowflake by opening a connection to Snwoflake, but I'm not sure I can open the connection from within a Python script that runs in Snowflake itself (a Python stored procedure).
I know that 3rd party tools like Talend, Informatica, and Fivetran are often used for this, but I want to know if I can just use Python in Snowflake.
Thanks!
You will need to use External Access Integration to access a database other than Snowflake.
See Ingest External Data into Snowflake with Snowpark and JDBC for an example.
From the article
- Create a network rule
- Create an external access integration
- For this use case a Java Tabular function will be very useful.This function will just leverage very standard JDBC apis, the steps are:
- Load the JDBC driver
- Load the JDBC driver
- Create a JDBC connection
- Create an statement
- Execute the query
- Get the metadata
- Retrieve and return the results