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

Preview

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 [ OR REPLACE ] 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.us-west-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.us-west-2.rds.amazonaws.com',
       port '5432',
       user secret('secrets.r.us', 'postgresUser'),
       password secret('secrets.r.us', 'postgresPassword'));