CREATE CONNECTION

Creates a connection to external storage.

Syntax

CREATE [ OR REPLACE ] CONNECTION [ IF NOT EXISTS ] <connection_name>
    STORAGE_TYPE = '<type>'
    [ <storage_params> ]

Connection Parameters

Connections encapsulate the credentials and configuration for a specific storage backend. Choose the appropriate STORAGE_TYPE and provide the required parameters when creating the connection. The table highlights common options:

For parameter meanings, optional flags, and additional storage types, refer to Connection Parameters. Expand the tabs below to see storage-specific examples:

CREATE CONNECTION <connection_name>
    STORAGE_TYPE = 's3'
    ACCESS_KEY_ID = '<your-access-key-id>'
    SECRET_ACCESS_KEY = '<your-secret-access-key>';

CREATE CONNECTION <connection_name>
    STORAGE_TYPE = 's3'
    ROLE_ARN = '<your-role-arn>';

CREATE CONNECTION <connection_name>
    STORAGE_TYPE = 'azblob'
    ACCOUNT_NAME = '<account-name>'
    ACCOUNT_KEY = '<account-key>'
    ENDPOINT_URL = 'https://<account-name>.blob.core.windows.net';

CREATE CONNECTION <connection_name>
    STORAGE_TYPE = 'gcs'
    CREDENTIAL = '<base64-encoded-service-account>';

CREATE CONNECTION <connection_name>
    STORAGE_TYPE = 'oss'
    ACCESS_KEY_ID = '<your-ak>'
    ACCESS_KEY_SECRET = '<your-sk>'
    ENDPOINT_URL = 'https://<region-id>[-internal].aliyuncs.com';

CREATE CONNECTION <connection_name>
    STORAGE_TYPE = 'cos'
    SECRET_ID = '<your-secret-id>'
    SECRET_KEY = '<your-secret-key>'
    ENDPOINT_URL = '<your-endpoint-url>';

CREATE CONNECTION <connection_name>
    STORAGE_TYPE = 'hf'
    REPO_TYPE = 'dataset'
    REVISION = 'main'
    TOKEN = '<optional-access-token>';

Access control requirements

To create a connection, the user performing the operation or the current_role must have the CREATE CONNECTION privilege.

Update Table Connections

To switch an existing table to a new connection, use ALTER TABLE ... CONNECTION. This command rebinds external tables to a different connection without recreating the table.

Examples

Using Access Keys

This example creates a connection to Amazon S3 named 'toronto' and establishes an external stage named 'my_s3_stage' linked to the 's3://lake-toronto' URL, using the 'toronto' connection. For more practical examples about connection, see Usage Examples.

CREATE CONNECTION toronto
    STORAGE_TYPE = 's3'
    ACCESS_KEY_ID = '<your-access-key-id>'
    SECRET_ACCESS_KEY = '<your-secret-access-key>';

CREATE STAGE my_s3_stage
    URL = 's3://lake-toronto'
    CONNECTION = (CONNECTION_NAME = 'toronto');

Using AWS IAM Role

This example creates a connection to Amazon S3 using an IAM role and then creates a stage that uses this connection. This approach is more secure as it doesn't require storing access keys in TiDB Cloud Lake.

CREATE CONNECTION lake_test
    STORAGE_TYPE = 's3'
    ROLE_ARN = 'arn:aws:iam::987654321987:role/lake-test';

CREATE STAGE lake_test
    URL = 's3://test-bucket-123'
    CONNECTION = (CONNECTION_NAME = 'lake_test');

-- You can now query data from your S3 bucket
SELECT * FROM @lake_test/test.parquet LIMIT 1;