CREATE CONNECTION

  • Article

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

Important

This feature is in Public Preview.

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
    • 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.

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'));