Snowpark Container Services: SQL execution¶

Credential configuration options¶

Snowflake recommends that application containers use Snowflake-provided credentials to authenticate to Snowflake when executing SQL. Although it is possible to use other credentials by connecting through an external access integration (EAI), connecting through an EAI treats the service as if it were running outside Snowflake and connecting to Snowflake over the internet.

You have three options to connect to Snowflake from a service container:

• Use Snowflake-provided service user credentials: Snowflake provides every service with credentials, which are referred to as service credentials. A service uses these credentials to connect to Snowflake as the service user.

• Use Snowflake-provided caller credentials (caller’s rights): When you configure your service with caller’s rights, Snowflake also provides credentials for the service to connect to Snowflake as the calling user.

• Use other credentials: In this case, you use an external access integration (EAI) that allows your service to connect to Snowflake’s internet endpoint by using valid authentication credentials. This option requires an administrator to create the EAI, and then grant the USAGE privilege on the integration to the service owner role.

  Note

  If you use external access integrations to access Snowflake, you might send potentially sensitive information over the internet.

For examples of code that uses various Snowflake drivers to connect to Snowflake, see Snowflake Connection Samples.

SNOWFLAKE_ACCOUNT = os.getenv('SNOWFLAKE_ACCOUNT')
SNOWFLAKE_HOST = os.getenv('SNOWFLAKE_HOST')

def get_login_token():
  with open('/snowflake/session/token', 'r') as f:
    return f.read()

conn = snowflake.connector.connect(
  host = os.getenv('SNOWFLAKE_HOST'),
  account = os.getenv('SNOWFLAKE_ACCOUNT'),
  token = get_login_token(),
  authenticator = 'oauth'
)

ALTER USER my_user SET DEFAULT_SECONDARY_ROLES = ( 'ALL' );

-- Permissions to resolve the table's name.
GRANT CALLER USAGE ON DATABASE <db_name> TO ROLE <service_owner_role>;
GRANT CALLER USAGE ON SCHEMA <schema_name> TO ROLE <service_owner_role>;
-- Permissions to use a warehouse
GRANT CALLER USAGE ON WAREHOUSE <warehouse_name> TO ROLE <service_owner_role>;
-- Permissions to query the table.
GRANT CALLER SELECT ON TABLE T1 TO ROLE <service_owner_role>;

conn = snowflake.connector.connect(
  account = '<acct-name>',
  user = '<user-name>',
  password = '<password>'
)

Configuration of the database and schema context for executing SQL¶

In addition to providing credentials, Snowflake also provides the database and schema context in which the service is created. The container code can use this information to execute SQL in the same database and schema context as the service.

This section explains two concepts:

• The logic Snowflake uses to determine the database and schema in which to create your service.

• The method through which Snowflake conveys this information to your containers, thus enabling the container code to execute SQL in the same database and schema context.

Snowflake uses the service name to determine the database and schema in which to create a service:

• Example 1: In the following CREATE SERVICE and EXECUTE JOB SERVICE commands, the service name does not explicitly specify a database and schema name. Snowflake creates the service and the job service in the current database and schema.

  -- Create a service.
  CREATE SERVICE test_service IN COMPUTE POOL ...
  
  -- Execute a job service.
  EXECUTE JOB SERVICE
    IN COMPUTE POOL tutorial_compute_pool
    NAME = example_job_service ...
  

  Copy

• Example 2: In the following CREATE SERVICE and EXECUTE JOB SERVICE commands, the service name includes a database and schema name. Snowflake creates the service and job service in the specified database (test_db) and schema (test_schema), regardless of the current schema.

  -- Create a service.
  CREATE SERVICE test_db.test_schema.test_service IN COMPUTE POOL ...
  
  -- Execute a job service.
  EXECUTE JOB SERVICE
    IN COMPUTE POOL tutorial_compute_pool
    NAME = test_db.test_schema.example_job_service ...
  

  Copy

When Snowflake starts a service, it provides the database and schema information to the running containers using the following environment variables:

• SNOWFLAKE_DATABASE

• SNOWFLAKE_SCHEMA

Your container code can use environment variables in the connection code to determine which database and schema to use, as shown in this example:

conn = snowflake.connector.connect(
  host = os.getenv('SNOWFLAKE_HOST'),
  account = os.getenv('SNOWFLAKE_ACCOUNT'),
  token = get_login_token(),
  authenticator = 'oauth',
  database = os.getenv('SNOWFLAKE_DATABASE'),
  schema = os.getenv('SNOWFLAKE_SCHEMA')
)

Example

In Tutorial 2, you create a Snowflake job service that connects with Snowflake and executes SQL statements. The following steps summarize how the tutorial code uses the environment variables:

1. In the common setup (see the Common Setup section), you create resources, including a database and a schema. You also set the current database and schema for the session:

   USE DATABASE tutorial_db;
   ...
   USE SCHEMA data_schema;
   

   Copy

2. After you create a job service (by running EXECUTE JOB SERVICE), Snowflake starts the container and sets the following environment variables in the container to the current database and schema of the session:

   • SNOWFLAKE_DATABASE is set to “TUTORIAL_DB”

   • SNOWFLAKE_SCHEMA is set to “DATA_SCHEMA”

3. The job code (see main.py in Tutorial 2) reads these environment variables:

   SNOWFLAKE_DATABASE = os.getenv('SNOWFLAKE_DATABASE')
   SNOWFLAKE_SCHEMA = os.getenv('SNOWFLAKE_SCHEMA')
   

   Copy

4. The job code sets the database and schema as the context in which to execute the SQL statements (run_job() function in main.py):

   {
      "account": SNOWFLAKE_ACCOUNT,
      "host": SNOWFLAKE_HOST,
      "authenticator": "oauth",
      "token": get_login_token(),
      "warehouse": SNOWFLAKE_WAREHOUSE,
      "database": SNOWFLAKE_DATABASE,
      "schema": SNOWFLAKE_SCHEMA
   }
   ...
   

   Copy

   Note

   SNOWFLAKE_ACCOUNT, SNOWFLAKE_HOST, SNOWFLAKE_DATABASE, SNOWFLAKE_SCHEMA are environment variables that Snowflake generates for the application container, but SNOWFLAKE_WAREHOUSE is not (the Tutorial 2 application code created this variable because Snowflake does not pass a warehouse name to a container).

Specifying the warehouse for your container¶

If your service connects to Snowflake to execute a query in a Snowflake warehouse, you have the following options to specify a warehouse:

• Specify a warehouse in your application code. Specify a warehouse as part of the connection configuration when starting a Snowflake session to run queries in your code. For an example, see Tutorial 2.

• Specify a default warehouse when creating a service. Specify the optional QUERY_WAREHOUSE parameter in the CREATE SERVICE or EXECUTE JOB SERVICE command to provide a default warehouse. If your application code doesn’t provide a warehouse as part of connection configuration, Snowflake uses the default warehouse. Use the ALTER SERVICE command to change the default warehouse.

  Note

  The warehouse specified using the QUERY_WAREHOUSE parameter is the default only for the service user. When the service connects to Snowflake on behalf of another user — in the context of caller’s rights scenario, Snowflake uses the user’s default warehouse.

If you specify a warehouse by using both methods, the warehouse that is specified in the application code is used.

Access service user query history¶

You can find queries executed by your service as the service user by filtering the QUERY_HISTORY view or QUERY_HISTORY function where user_type is SNOWFLAKE_SERVICE.

Example 1: Fetch queries run by a service.

SELECT *
FROM snowflake.account_usage.query_history
WHERE user_type = 'SNOWFLAKE_SERVICE'
AND user_name = '<service_name>'
AND user_database_name = '<service_db_name>'
AND user_schema_name = '<service_schema_name>'
order by start_time;

In the WHERE clause:

• user_name = '<service_name>': You specify the service name as the user name because a service executes queries as the service user, and the service user’s name is the same as the service name.

• user_type = 'SNOWFLAKE_SERVICE' and user_name = '<service_name>': This limits the query result to retrieve only queries executed by a service.

• user_database_name and user_schema_name: For a service user, these are the service’s database and schema.

You can get the same results by calling the QUERY_HISTORY function.

SELECT *
FROM TABLE(<service_db_name>.information_schema.query_history())
WHERE user_database_name = '<service_db_name>'
AND user_schema_name = '<service_schema_name>'
AND user_type = 'SNOWFLAKE_SERVICE'
AND user_name = '<service_name>'
order by start_time;

In the WHERE clause:

• user_type = 'SNOWFLAKE_SERVICE' and user_name = '<service_name>' limit the query result to retrieve only queries executed by a service.

• user_database_name and user_schema_name names (for a service user) are the service’s database and schema.

Example 2: Fetch queries run by services and the corresponding service information.

SELECT query_history.*, services.*
FROM snowflake.account_usage.query_history
JOIN snowflake.account_usage.services
ON query_history.user_name = services.service_name
AND query_history.user_schema_id = services.service_schema_id
AND query_history.user_type = 'SNOWFLAKE_SERVICE'

The query joins the QUERY_HISTORY and SERVICES views to retrieve information about the queries and services that executed the queries. Note the following:

• For queries run by services, the query_history.user_name is the service user’s name, which is the same as the service name.

• The query joins the views using the schema IDs (not schema name) to ensure you refer to the same schema, because if you drop and recreate a schema, the schema ID changes but the name remains the same.

You can add optional filters to the query. For example:

• Filter query_history to retrieve only services that executed specific queries.

• Filter services to retrieve only queries executed by specific services.

Example 3: For every service, fetch service user information.

SELECT services.*, users.*
FROM snowflake.account_usage.users
JOIN snowflake.account_usage.services
ON users.name = services.service_name
AND users.schema_id = services.service_schema_id
AND users.type = 'SNOWFLAKE_SERVICE'

The query join SERVICES and USERS views in the ACCOUNT_USAGE schema to retrieve services and service user information. Note the following:

• When a service runs queries, it runs the queries as service user and the service user’s name is the same as the service name. Therefore, you specify the join condition: users.name = services.service_name.

• Service names are unique only within a schema. Therefore, the query specifies the join condition (users.schema_id = services.service_schema_id) to ensure each service user is matched against the specific service they belong to (and not any other same-named service running in different schemas).