Configure a gen 2 connector with SQL¶

This topic describes how to create and configure a gen 2 connector using SQL and stage commands—the programmatic alternative to the Configure a connector with the setup wizard. Use this workflow when you want to automate connector setup, manage configuration as code, or integrate with CI/CD.

The same connector objects can also be managed from the Openflow UI; changes made in one interface are visible in the other.

Prerequisites¶

Before you begin:

A gen 2 deployment and runtime. See Quickstart: gen 2 Openflow if you need to create them.
CREATE OPENFLOW CONNECTOR on the schema and USAGE on the runtime.
Complete any connector-specific source database prerequisites first: PostgreSQL CDC | MySQL and MariaDB CDC. For Snowflake account setup (destination database, warehouse, and secrets), follow the Snowflake account setup on those pages.
A client that supports GET and PUT on stages. Snowsight does not support GET/PUT on connector stages; use Snowflake CLI or another supported client.

Tip

For your first connector configuration, use the Configure a connector with the setup wizard. When you need a template for SQL or automation, create a draft with the wizard and download config.json from the live version (see Upload and commit configuration).

Create the connector¶

The SQL examples in this topic assume you created a database and schema (see Quickstart: gen 2 Openflow) and set the session context:

USE DATABASE my_db;
USE SCHEMA my_schema;

Connectors and runtimes are schema-level objects; the examples below use simple names. snow:// URI paths use the connector’s fully qualified name.

Create a connector in a gen 2 runtime from a catalog definition ID. Each gen 2 catalog entry maps to a definition ID—the value you pass to FROM DEFINITION.

To find definition IDs available in your account:

Run SHOW OPENFLOW CONNECTOR DEFINITIONS (optionally with LIKE to filter). The name column is the definition ID. See SHOW OPENFLOW CONNECTOR DEFINITIONS.
Browse gen 2 entries in the Openflow connector catalog (see Configure a connector with the setup wizard).

Example for PostgreSQL CDC:

CREATE OPENFLOW CONNECTOR my_connector
  IN RUNTIME my_runtime
  FROM DEFINITION OPENFLOW_POSTGRES_CDC
  DISPLAY_NAME = 'My Postgres CDC Connector';

The connector is created in STOPPED state with a Draft status: a live configuration version exists, but no committed default version yet. You must configure and commit before starting. See gen 2 connector configuration and versioning.

Upload and commit configuration¶

Gen 2 connectors store configuration in config.json on the connector’s internal versioned stage. The standard workflow is download → edit → upload → commit.

List files in the live version:

LS 'snow://openflow_connector/my_db.my_schema.my_connector/versions/live';

Download config.json to your local machine:

GET 'snow://openflow_connector/my_db.my_schema.my_connector/versions/live/config.json'
  file:///path/to/local/;

Or with Snowflake CLI:

snow stage copy 'snow://openflow_connector/my_db.my_schema.my_connector/versions/live/config.json' .

Edit config.json locally. Reference passwords and other secrets with valueType = SECRET_REFERENCE (see Secrets in configuration).
Upload the updated file to the live version:
```
PUT 'file:///path/to/config.json'
  'snow://openflow_connector/my_db.my_schema.my_connector/versions/live/'
  AUTO_COMPRESS = FALSE
  OVERWRITE = TRUE;
```
Warning
The destination is the live version directory, not the file path. PUT appends the source file name automatically. If you include config.json in the destination, it creates a nested config.json/config.json object that shadows the real file.

Commit the live version to create the default:

ALTER OPENFLOW CONNECTOR my_connector COMMIT;

SELECT SYSTEM$WAIT_FOR_STABLE_OPENFLOW_CONNECTORS(600, 'my_connector');

For full details on live, default, and LAST versions, see gen 2 connector configuration and versioning.

Start the connector¶

After configuration is committed:

ALTER OPENFLOW CONNECTOR my_connector START;

SELECT SYSTEM$WAIT_FOR_STABLE_OPENFLOW_CONNECTORS(600, 'my_connector');

To stop or remove the connector, see Manage the gen 2 Openflow connector lifecycle. To monitor connector health and ingestion status, see Monitor connectors using the Openflow Connectors Dashboard.

Edit an existing connector¶

To change configuration after a commit:

Create a new live version from the current default:

ALTER OPENFLOW CONNECTOR my_connector ADD LIVE VERSION FROM LAST;

Download, edit, and upload config.json as in Upload and commit configuration.

Commit or abort:

ALTER OPENFLOW CONNECTOR my_connector COMMIT;

-- Or discard changes:
ALTER OPENFLOW CONNECTOR my_connector ABORT;

Secrets in configuration¶

Do not store passwords as plain text in config.json. Create a Snowflake secret and reference it with valueType = SECRET_REFERENCE. Most connectors require secrets with TYPE = GENERIC_STRING. Check the setup topic for your connector to confirm the required type.

The setup wizard generates the correct SECRET_REFERENCE structure for you. For SQL workflows, create a connector draft with the wizard, download config.json from the live version, and reuse that structure when you edit secrets and connection settings for additional connectors.

Example SECRET_REFERENCE (PostgreSQL CDC):

"Source Database Password": {
  "valueType": "SECRET_REFERENCE",
  "fullyQualifiedSecretName": "openflow_db.openflow_schema.MY_SECRET"
}

Grant READ on each referenced secret to the runtime’s EXECUTE_AS_ROLE. The role also needs USAGE on the secret’s database and schema. Grant these privileges before you start the connector.

External secrets¶

You can also source secret values from an external secret provider (AWS Secrets Manager, Azure Key Vault, or Google Cloud Secret Manager). Use valueType = SECRET_REFERENCE and construct the fullyQualifiedSecretName as follows:

EXTERNAL_<integration_name>.<secret_name>.<value_name>

Where <value_name> depends on the secret’s format:

Plaintext secret: Use the secret name as the value name.
JSON secret: Use the name of the JSON field that contains the sensitive value.

Example (plaintext secret named db_password in integration my_secrets_int):

"Source Database Password": {
  "valueType": "SECRET_REFERENCE",
  "fullyQualifiedSecretName": "EXTERNAL_my_secrets_int.db_password.db_password"
}

Example (JSON secret named service_config in integration my_secrets_int, extracting the password field):

"Source Database Password": {
  "valueType": "SECRET_REFERENCE",
  "fullyQualifiedSecretName": "EXTERNAL_my_secrets_int.service_config.password"
}

For setup and prerequisites, see Use external secret providers with Openflow.

Create from a known configuration (optional)¶

For your first connector, use FROM DEFINITION (above) or the setup wizard. When you already have a validated config.json and want to stand up another connector with the same settings—for example in a different runtime or environment—see Create from a known configuration in gen 2 connector configuration and versioning.

Wizard vs SQL¶

Approach	When to use it
Configure a connector with the setup wizard	Interactive setup with step-by-step validation; best for first-time configuration of connector types that support the setup wizard.
SQL (this topic)	Automation, repeat deployments, CI/CD, and bulk connector creation; required for clients that manage Openflow programmatically.

Both approaches create the same gen 2 connector object and config.json format.

Next steps¶

CREATE OPENFLOW CONNECTOR, ALTER OPENFLOW CONNECTOR — Full CREATE and ALTER syntax for connectors.
gen 2 connector configuration and versioning — Version states, UI labels, and create from a known configuration.
Manage the gen 2 Openflow connector lifecycle — Start, stop, and remove gen 2 connectors.