The problem I’m having
I would like to create a python model (which turns into stored procedure in Snowflake) and in that model access external integration and uses secrets.
The context of why I’m trying to do this
Trying to setup a python model what will use external data. Following External Access: Securely Connect to External Endpoints from Snowpark
What I’ve already tried
This is the definition of procedure - and it works:
CREATE OR REPLACE PROCEDURE test_salesforce(table_name STRING)
returns STRING
LANGUAGE PYTHON
RUNTIME_VERSION = 3.9
HANDLER = 'handler'
EXTERNAL_ACCESS_INTEGRATIONS = (SALESFORCE_TEST_INTEGRATION)
PACKAGES = ('snowflake-snowpark-python','simple-salesforce')
SECRETS = ('user_name'=SALESFORCE_TEST_USER_NAME, 'password'=SALESFORCE_TEST_PASSWORD)
AS
$$
import _snowflake
import logging
import sys
from snowflake.snowpark.functions import listagg
from simple_salesforce import SalesforceLogin
from simple_salesforce import Salesforce
def handler(session,table_name)->str:
logging.basicConfig(level=logging.DEBUG)
logger = logging.getLogger(__name__)
df_table = session.table(table_name)
user_name = _snowflake.get_generic_secret_string('user_name')
password = _snowflake.get_generic_secret_string('password')
...
and this is the model I tried to use:
def model(dbt, session):
dbt.config(
packages=['simple-salesforce'],
external_access_integrations=['SALESFORCE_TEST_INTEGRATION'],
secrets={'user_name': 'UTIL.SALESFORCE_TEST_USER_NAME', 'password': 'UTIL.SALESFORCE_TEST_PASSWORD'}
)
import logging
import sys
from snowflake.snowpark.functions import listagg
from simple_salesforce import SalesforceLogin
from simple_salesforce import Salesforce
import _snowflake
import pandas as pd
logging.basicConfig(level=logging.DEBUG)
...
But I cannot pass the secrets and it seems to me that also the external access integration is not passed as an argument to the procedure.