Control-M for Snowflake

Snowflake is a cloud-computing platform that enables you to process, analyze, and store your data.

Control-M for Snowflake enables you to do the following:

• Execute any of the following Snowflake job operations:

  • Execute a Snowflake-supported SQL statement.

  • Copy a queried database and schema into an existing or new file in cloud storage.

  • Copy from an existing table.

  • Create a table, populated by a query, in a specified database and schema.

  • Copy data from a cloud storage location into an existing table in Snowflake.

  • Start or pause an existing Snowpipe.

  • Call an existing procedure and its arguments.

  • Monitor the status of a Snowpipe for a set period of time.

• Manage Snowflake credentials in a secure connection profile.

• Connect to any Snowflake endpoint.

• Introduce all Control-M capabilities to Control-M for Snowflake, including advanced scheduling criteria, complex dependencies, Resource Pools, Lock Resources, and variables.

• Integrate Snowflake jobs with other Control-M jobs into a single scheduling environment.

• Monitor the status, results, and output of Snowflake jobs.

• Attach an SLA job to the Snowflake jobs.

• Run 50 Snowflake jobs simultaneously per Agent.

Setting up the API Connection with Snowflake

To set up the API connection with Snowflake and enable the use of the Control-M for Snowflake plug-in, you must perform several setup tasks, as described in KA000437810.

Setting up Control-M for Snowflake

This procedure describes how to deploy the Snowflake plug-in, create a connection profile, and define a Snowflake job in Control-M SaaS and Automation API.

Before You Begin

• Create a Snowflake security integration

• Obtain security integration connection details

• Obtain the security code

• Generate a refresh token

• Verify that Automation API is installed, as described in Setting Up the API.

• Verify that Agent version 9.0.21.080 or later is installed.

1. On the Agent host, run one of the following commands to set the Java environment variable:

   • Linux:

     • Bourne shell/bash: export BMC_INST_JAVA_HOME=<java_11_directory>

     • csh/tcsh: setenv BMC_INST_JAVA_HOME <java_11_directory>

   • Windows: set BMC_INST_JAVA_HOME="<java_11_directory>"

2. Run one of the following API commands:

   • To install Control-M for Snowflake, which utilizes token-based authentication, type one of the following provision image commands:

     • Linux: ctm provision image SNF_plugin.Linux

     • Windows: ctm provision image SNF_plugin.Windows

   • To install Control-M for Snowflake IdP, which authenticates with an Identity Provider (IdP), type one of the following provision image commands:

     • Linux: ctm provision image SFI_plugin.Linux

     • Windows: ctm provision image SFI_plugin.Windows

   • To upgrade, type the following command:

     ctm provision agent::update

3. Create a Snowflake connection profile in Control-M SaaS or Automation API, as follows:

   • Control-M SaaS: Create a Centralized Connection Profile with the following relevant parameters:

     • Token-based authentication: Snowflake Connection Profile Parameters

     • Authentication with an Identity Provider (IdP): Snowflake IdP Connection Profile Parameters

   • Automation API:

     • ConnectionProfile:Snowflake

     • ConnectionProfile:Snowflake IdP

4. Define a Snowflake job in Control-M SaaS or Automation API, as follows:

   • Control-M SaaS: Create a Job with Snowflake Job parameters

   • Automation API: Job:Snowflake

Change Log

The following table provides details about changes that were introduced in new versions of the Snowflake plug-in:

Plug-in Version	Details
1.0.05	Added vendor identifier (CSID) to the backend
1.0.04	Added new Run SQL File action in the job, which enables you to upload a file that contains Snowflake-supported SQL commands Added new Load SQL File parameter for this action
1.0.02	Added new Snowpipe Load Status action in the job, which monitors the status of a Snowpipe for a set period of time and checks whether the Snowpipe data pipeline action ran successfully Removed Create Snowpipe action Fixed issue of missing Bearer Token Enabled asynchronous requests
1.0.00	Initial version

The following table provides details about changes that were introduced in new versions of the Snowflake IdP plug-in:

Plug-in Version	Details
1.0.06	Added vendor identifier (CSID) to the backend
1.0.05	(Snowflake IdP only) Fixed bug in job fields under Show More Options
1.0.04	Added new Run SQL File action in the job, which enables you to upload a file that contains Snowflake-supported SQL commands Added new Load SQL File parameter for this action (Snowflake IdP only) Added an Action to the Job settings to run a job with an SQL file upload
1.0.03	(Snowflake IdP only) Added support for Oauth Access Token extensions
1.0.02	Added new Snowpipe Load Status action in the job, which monitors the status of a Snowpipe for a set period of time and checks whether the Snowpipe data pipeline action ran successfully Removed Create Snowpipe action Fixed Issue of missing Bearer Token Enabled asynchronous requests
1.0.00	Initial version