note

The documentation provided relies on DuckDB version 0.10.x. To update to this version, please visit our migration page.

MotherDuck enables you to store your cloud storage credentials for convenience, using the familiar DuckDB CREATE SECRET syntax. See DuckDB CREATE SECRET documentation.

Make sure to add either PERSISTENT or IN MOTHERDUCK keyword to create MotherDuck secrets. Secrets stored in MotherDuck are fully encrypted.

note

You can use the PERSISTENT keyword to create a local file persistent secret in DuckDB as well. It gets stored unencrypted in the ~/.duckdb/stored_secrets directory.

When you've loaded the MotherDuck extension, PERSISTENT secrets are stored encrypted in MotherDuck. Locally persisted secrets are not impacted.

You can still create locally persisted secrets when using MotherDuck by specifying the secret storage backend: CREATE SECRET IN LOCAL_FILE.

When using MotherDuck, the statement below creates a cloud-persistent secret stored in MotherDuck.

Syntax

CREATE [OR REPLACE] PERSISTENT SECRET [secret_name] (
    TYPE <S3, GCS, R2, AZURE>, 
    <storage-specific properties>
);

CREATE [OR REPLACE] SECRET [secret_name] IN MOTHERDUCK (
    TYPE <S3, GCS, R2, AZURE>, 
    <storage-specific properties>
);

Example Usage

To manually create a S3 secret in MotherDuck:

CREATE SECRET IN MOTHERDUCK (
    TYPE S3,
    KEY_ID 's3_access_key',
    SECRET 's3_secret_key',
    REGION 'us-east-1'
);

This creates a new secret with a default name (i.e. __default_s3) and a default scope (i.e. [s3://, s3n://, s3a://]) used for path matching explained below.

Secret Providers

MotherDuck supports the same secret providers as DuckDB.

To create a secret by automatically fetching credentials using mechanisms provided by the AWS SDK, see AWS CREDENTIAL_CHAIN provider.

To create a secret by automatically fetching credentials using mechanisms provided by the Azure SDK, see Azure CREDENTIAL_CHAIN provider.

To store a secret from a given secret provider in MotherDuck, simply specify PERSISTENT or IN MOTHERDUCK keyword in addition.

Example Usage

To store a secret configured through aws configure:

CREATE PERSISTENT SECRET aws_secret (
      TYPE S3,
      PROVIDER CREDENTIAL_CHAIN
  );

To store a secret configured through az configure:

CREATE SECRET azure_secret IN MOTHERDUCK (
      TYPE AZURE,
      PROVIDER CREDENTIAL_CHAIN,
      ACCOUNT_NAME 'some-account'
  );

note

The DuckDB Azure extension is not autoloaded until v0.10.1. When using versions below v0.10.1, you need to manually specify install azure; load azure; to ensure that the Azure secret type is registered with the duck secret manager to create and list an Azure secret.

Querying with Secrets

Secret scope is supported in the same way as in DuckDB to allow multiple secrets of the same type to be stored in MotherDuck. When there are multiple local (i.e. in memory and store in local file) and remote (i.e. MotherDuck) secrets of the same type, scope matching (secret scope against the file path) happens to determine which secret to use to open a file. Both local and remote secrets are considered in scope matching.

In the case of multiple matching secrets, the secret with the longest matching scope prefix is chosen.

In the case of multiple secrets stored in different secret storages sharing the same scope (e.g. the default scope if not specified), matching secret is chosen based on the following order: local temp secret > local_file secret > MotherDuck secret.

To see which secret (either local or remote) is being used by MotherDuck, the md_which_secret table function can be used, which takes a path or an array of paths as parameter.

Example Usage

To see which secret is used to open a file:

FROM md_which_secret('s3://my-bucket/file.parquet')

To see which secrets are used to open an array of files:

FROM md_which_secret(['s3://bucket1/file.parquet', 's3://bucket2/file.parquet', 's3://bucket3/file.parquet']);

note

md_which_secret returns non-MotherDuck (i.e. local DuckDB) secrets as well. So when using MotherDuck, it's recommended to use it to inspect which local or remote secret is chosen to open a file.

DuckDB's which_secret scalar function can still be used. However, it has no access or knowledge of the MotherDuck Cloud secrets. So we recommend you to use the MotherDuck table function in the hybrid scenario.