CREATE CONNECTION

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 13.3 LTS and above check marked yes Unity Catalog only

This command creates a foreign connection (or server), which represents a remote data system of a specific type, using system specific options that provide the location of the remote system and authentication details.

Foreign connections enable federated queries.

Syntax

CREATE CONNECTION [IF NOT EXISTS] connection_name
  TYPE connection_type
  OPTIONS ( option value [, ...] )
  [ COMMENT comment ]

For standards compliance you can also use SERVER instead of CONNECTION.

Parameters

  • connection_name

    A unique identifier of the connection at the Unity Catalog metastore level.

  • connection_type

    Identifies the type of the connection and must be one of:

    • DATABRICKS
    • HTTP Applies to: Databricks SQL check marked yes Databricks Runtime 16.2 and above
    • MYSQL
    • POSTGRESQL
    • REDSHIFT
    • SNOWFLAKE
    • SQLDW (Synapse)
    • SQLSERVER

  • OPTIONS

    Sets connection_type specific parameters needed to establish the connection.

    • option

      The property key. The key can consist of one or more identifiers separated by a dot, or a STRING literal.

      Property keys must be unique and are case-sensitive.

    • value

      The value for the property. The value must be a BOOLEAN, STRING, INTEGER, or DECIMAL constant expression. The value may also be a call to the SECRET SQL function. For example, the value for password may comprise secret('secrets.r.us', 'postgresPassword') as opposed to entering the literal password.

HTTP options

Applies to: Databricks SQL check marked yes Databricks Runtime 16.2 and above

The HTTP connection type supports the following option keys and values:

  • host

    A STRING literal. Specifies the host_name for the external service. An exception will be thrown if the host path is a not a normalized URL.

  • bearer_token

    A STRING literal or invocation of the SECRET function. The authentication token to be used when making the external service call. For example, the value may comprise secret('secrets.r.us', 'httpPassword') as opposed to entering the literal password.

  • port An optional INTEGER literal specifying the port. The default is 443;

  • base_path

    An optional STRING literal. The default is /. An exception is thrown if the path contains an empty string, or an incorrect path with spaces or special characters.

Example

-- Create a postgresql connection
> CREATE CONNECTION postgresql_connection
    TYPE POSTGRESQL
    OPTIONS (
      host 'qf-postgresql-demo.xxxxxx.cn-north-2.rds.amazonaws.com',
      port '5432',
      user 'postgresql_user',
      password 'password123');

-- Create a postgresql connection with secret scope
> CREATE CONNECTION postgresql_connection
    TYPE POSTGRESQL
    OPTIONS (
       host 'qf-postgresql-demo.xxxxxx.cn-north-2.rds.amazonaws.com',
       port '5432',
       user secret('secrets.r.us', 'postgresUser'),
       password secret('secrets.r.us', 'postgresPassword'));

-- Set up a connect to Slack.
> CREATE CONNECTION slack_conn
  TYPE HTTP
  OPTIONS (
    host 'https://slack.com',
    port '443',
    base_path '/api/',
    bearer_token 'xoxb-xxxxx'
  );

-- Request to the external service
> SELECT http_request(
    conn => 'slack_conn',
    method => 'POST',
    path => '/chat.postMessage',
    json => to_json(named_struct(
      'channel', channel,
      'text', text
    ))
    headers => map(
       'Accept', "application/vnd.github+json",
    )
  );