The azure extension lets DuckDB read and write files in Azure Blob Storage and Azure Data Lake Storage (ADLS) Gen2.

Installing and Loading

The azure extension will be transparently autoloaded on first use from the official extension repository. If you would like to install and load it manually, run:

INSTALL AND LOAD azure;

Usage

Queries need a secret; for a public container, a secret with the storage account name is enough (see the CONFIG provider). Then you can query Azure storage as follows:

Azure Blob Storage

Allowed URI schemes: az or azure

SELECT count(*)
FROM 'az://my_container/path/my_file.parquet_or_csv';

Globs are also supported:

SELECT *
FROM 'az://my_container/path/*.csv';

SELECT *
FROM 'az://my_container/path/**';

Or with a fully qualified path syntax:

SELECT count(*)
FROM 'az://my_storage_account.blob.core.windows.net/my_container/path/my_file.parquet_or_csv';

SELECT *
FROM 'az://my_storage_account.blob.core.windows.net/my_container/path/*.csv';

Azure Data Lake Storage (ADLS)

Allowed URI schemes: abfss or abfs (both connect over HTTPS)

SELECT count(*)
FROM 'abfss://my_filesystem/path/my_file.parquet_or_csv';

Globs are also supported:

SELECT *
FROM 'abfss://my_filesystem/path/*.csv';

SELECT *
FROM 'abfss://my_filesystem/path/**';

Or with a fully qualified path syntax:

SELECT count(*)
FROM 'abfss://my_storage_account.dfs.core.windows.net/my_filesystem/path/my_file.parquet_or_csv';

SELECT *
FROM 'abfss://my_storage_account.dfs.core.windows.net/my_filesystem/path/*.csv';

The fully qualified path can also use the my_filesystem@my_storage_account form:

SELECT count(*)
FROM 'abfss://my_filesystem@my_storage_account.dfs.core.windows.net/path/my_file.parquet_or_csv';

Writing to Azure Blob Storage

You can write data directly to Azure Blob or ADLSv2 Storage using the COPY statement.

-- Write query results to a Parquet file on Blob Storage
COPY (SELECT * FROM my_table)
TO 'az://my_container/path/output.parquet';

-- Write a table to a CSV file on ADLSv2 Storage
COPY my_table
TO 'abfss://my_filesystem/path/output.csv';

You can also use fully qualified paths:

COPY my_table
TO 'az://my_storage_account.blob.core.windows.net/my_container/path/output.parquet';

Since v1.5.4, the extension buffers written data locally and uploads it in blocks of azure_write_block_size bytes (default: 8 MiB). By default, the uploaded blocks are committed when the file is closed, so other readers see the new content only after the write completes. To commit earlier, set azure_write_staged_blocks_per_commit to the number of blocks after which an intermediate commit happens.

Warning An intermediate commit makes an incomplete file visible to readers. A Parquet file cannot be read until its footer is written at the end, so keep the default 0 for Parquet files and for Delta, Iceberg or DuckLake tables.

Writing Large Files

Azure limits a blob to 50,000 blocks. With the default block size of 8 MiB, the largest file you can write is about 390 GiB. To write larger files, increase azure_write_block_size (maximum: 4000 MiB, about 3.9 GiB). For example, to write files up to about 3 TiB:

SET azure_write_block_size = 64 * 1024 * 1024;

Configuration

Use the following configuration options to control how the extension reads and writes remote files:

Since v1.5.4, azure_read_transfer_chunk_size and azure_read_buffer_size default to 8 MiB (previously 1 MiB).

Tip Setting azure_transport_option_type to curl has the following effects:

• On Linux, it may fix certificate errors such as Problem with the SSL CA cert (path? access rights?): the extension then looks for the CA bundle in the usual paths, which curl does not do by default and which static linking may get wrong.
• On Windows, it replaces the default adapter (WinHTTP), so you can use all curl features, e.g., SOCKS proxies.
• On all operating systems, it honors these environment variables:
  • CURL_CA_INFO: path to a PEM encoded file with the certificate authorities for libcurl. It is known to work only on Linux and may fail on other platforms.
  • CURL_CA_PATH: path to a directory of PEM encoded files with the certificate authorities for libcurl.

Example:

SET azure_http_stats = false;
SET azure_read_transfer_concurrency = 5;
SET azure_read_transfer_chunk_size = 8_388_608;
SET azure_read_buffer_size = 8_388_608;

Authentication

Authenticate with a secret. The SET variables in Authentication with Variables still work but are deprecated.

Authentication with Secret

Several Secret Providers are available, described below. These rules apply to every provider:

• If you need to define different secrets for different storage accounts, use the SCOPE configuration. Note that the SCOPE requires a trailing slash (SCOPE 'az://my_storage_account.blob.core.windows.net/').
• With fully qualified paths, ACCOUNT_NAME is optional.
• For paths that are not fully qualified, the ENDPOINT attribute overrides the default endpoint (blob.core.windows.net for az:// and azure://, dfs.core.windows.net for abfss:// and abfs://).

CONFIG Provider

The default provider, CONFIG (i.e., user-configured), allows access to the storage account using a connection string or anonymously. For example:

CREATE SECRET secret1 (
    TYPE azure,
    CONNECTION_STRING 'connection_string'
);

For anonymous access, give the storage account name:

CREATE SECRET secret2 (
    TYPE azure,
    PROVIDER config,
    ACCOUNT_NAME 'storage_account_name'
);

credential_chain Provider

The credential_chain provider allows connecting using credentials automatically fetched by the Azure SDK via the Azure credential chain. By default, the provider uses the Azure SDK's DefaultAzureCredential chain. The token is fetched at query time, not when the secret is created. For example:

CREATE SECRET secret3 (
    TYPE azure,
    PROVIDER credential_chain,
    ACCOUNT_NAME 'storage_account_name'
);

DuckDB also allows specifying a specific chain using the CHAIN keyword. This takes a semicolon-separated list (a;b;c) of providers that will be tried in order. For example:

CREATE SECRET secret4 (
    TYPE azure,
    PROVIDER credential_chain,
    CHAIN 'cli;env',
    ACCOUNT_NAME 'storage_account_name'
);

The possible values are:

• cli: the Azure CLI login
• managed_identity
• workload_identity, e.g., on AKS
• env: environment variables
• default: the full default chain, used when no CHAIN is given

MANAGED_IDENTITY Provider

If the machine has one managed identity, the credential_chain provider uses it with no extra options. If it has several, use the MANAGED_IDENTITY provider and pick one with CLIENT_ID, OBJECT_ID or RESOURCE_ID:

CREATE SECRET azure_mi (
    TYPE azure,
    PROVIDER managed_identity,
    ACCOUNT_NAME 'storage_account_name',
    CLIENT_ID 'client_id'
);

Without an ID, the provider works like credential_chain. With several identities and no ID, the Azure SDK decides which one is used, so set an ID.

SERVICE_PRINCIPAL Provider

The SERVICE_PRINCIPAL provider allows connecting using an Azure Service Principal (SPN).

With a client secret:

CREATE SECRET azure_spn (
    TYPE azure,
    PROVIDER service_principal,
    TENANT_ID 'tenant_id',
    CLIENT_ID 'client_id',
    CLIENT_SECRET 'client_secret',
    ACCOUNT_NAME 'storage_account_name'
);

Or with a certificate:

CREATE SECRET azure_spn_cert (
    TYPE azure,
    PROVIDER service_principal,
    TENANT_ID 'tenant_id',
    CLIENT_ID 'client_id',
    CLIENT_CERTIFICATE_PATH 'client_cert_path',
    ACCOUNT_NAME 'storage_account_name'
);

ACCESS_TOKEN Provider

The ACCESS_TOKEN provider allows connecting with an OAuth access token (bearer token) that you obtained outside of DuckDB:

CREATE SECRET azure_token (
    TYPE azure,
    PROVIDER access_token,
    ACCESS_TOKEN 'access_token',
    ACCOUNT_NAME 'storage_account_name'
);

Warning The extension does not refresh the token. When it expires, run CREATE OR REPLACE SECRET with a new token; it takes effect on the next query.

Configuring a Proxy

To configure proxy information when using secrets, you can add HTTP_PROXY, PROXY_USER_NAME and PROXY_PASSWORD in the secret definition. For example:

CREATE SECRET secret5 (
    TYPE azure,
    CONNECTION_STRING 'connection_string',
    HTTP_PROXY 'http://localhost:3128',
    PROXY_USER_NAME 'proxy_user_name',
    PROXY_PASSWORD 'proxy_password'
);

Note When you use secrets:

• The HTTP_PROXY environment variable is still honored, unless the secret sets HTTP_PROXY.
• The SET variables of Authentication with Variables are ignored.

Authentication with Variables

Deprecated Use secrets instead.

SET variable_name = value;

Where variable_name can be one of the following:

Additional Information

Logging

Since v1.5.2, the Azure extension writes its HTTP requests to the DuckDB HTTP log. To inspect them:

CALL enable_logging('HTTP');
SELECT * FROM 'az://my_container/path/my_file.parquet';
SELECT request.type, request.url, response.status FROM duckdb_logs_parsed('HTTP');

Logged requests show the DuckDB User-Agent header. The values of the sig query parameter and the Authorization header are replaced by REDACTED. To change what is redacted, set azure_http_logging_redact_query_params and azure_http_logging_redact_headers. A new value replaces the default list, so include sig or Authorization if you still want them redacted:

SET azure_http_logging_redact_headers = 'Authorization;x-ms-date';

Warning The Azure extension applies only these settings, not DuckDB's redact_http_logs setting. To keep Azure requests out of the HTTP log, run SET azure_http_logging = false;.

The Azure extension relies on the Azure SDK to connect to Azure Blob storage and supports printing the SDK logs to the console. To control the log level, set the AZURE_LOG_LEVEL environment variable.

For instance, to start the DuckDB CLI with verbose logs:

AZURE_LOG_LEVEL=verbose duckdb

CREATE SECRET myaccount (
    TYPE azure,
    PROVIDER credential_chain,
    SCOPE 'az://my_storage_account.blob.core.windows.net/'
);
SELECT count(*) FROM 'az://my_storage_account.blob.core.windows.net/my_container/path/my_file.parquet';

Difference between ADLS and Blob Storage

For glob patterns over many directories, prefer abfss://: the ADLS endpoint lists only the directories that match, while the Blob endpoint lists every file under the prefix and filters afterwards. The following example shows how a glob is performed through each endpoint.

Using the following filesystem:

root
├── l_receiptmonth=1997-10
│   ├── l_shipmode=AIR
│   │   └── data_0.csv
│   ├── l_shipmode=SHIP
│   │   └── data_0.csv
│   └── l_shipmode=TRUCK
│       └── data_0.csv
├── l_receiptmonth=1997-11
│   ├── l_shipmode=AIR
│   │   └── data_0.csv
│   ├── l_shipmode=SHIP
│   │   └── data_0.csv
│   └── l_shipmode=TRUCK
│       └── data_0.csv
└── l_receiptmonth=1997-12
    ├── l_shipmode=AIR
    │   └── data_0.csv
    ├── l_shipmode=SHIP
    │   └── data_0.csv
    └── l_shipmode=TRUCK
        └── data_0.csv

The following query is performed through the Blob endpoint:

SELECT count(*)
FROM 'az://root/l_receiptmonth=1997-*/l_shipmode=SHIP/*.csv';

It will perform the following steps:

• List all the files with the prefix root/l_receiptmonth=1997-
  • root/l_receiptmonth=1997-10/l_shipmode=SHIP/data_0.csv
  • root/l_receiptmonth=1997-10/l_shipmode=AIR/data_0.csv
  • root/l_receiptmonth=1997-10/l_shipmode=TRUCK/data_0.csv
  • root/l_receiptmonth=1997-11/l_shipmode=SHIP/data_0.csv
  • root/l_receiptmonth=1997-11/l_shipmode=AIR/data_0.csv
  • root/l_receiptmonth=1997-11/l_shipmode=TRUCK/data_0.csv
  • root/l_receiptmonth=1997-12/l_shipmode=SHIP/data_0.csv
  • root/l_receiptmonth=1997-12/l_shipmode=AIR/data_0.csv
  • root/l_receiptmonth=1997-12/l_shipmode=TRUCK/data_0.csv
• Filter the result with the requested pattern root/l_receiptmonth=1997-*/l_shipmode=SHIP/*.csv
  • root/l_receiptmonth=1997-10/l_shipmode=SHIP/data_0.csv
  • root/l_receiptmonth=1997-11/l_shipmode=SHIP/data_0.csv
  • root/l_receiptmonth=1997-12/l_shipmode=SHIP/data_0.csv

Meanwhile, the same query can be performed through the datalake endpoint as follows:

SELECT count(*)
FROM 'abfss://root/l_receiptmonth=1997-*/l_shipmode=SHIP/*.csv';

This will perform the following steps:

• List all directories in root/
  • root/l_receiptmonth=1997-10
  • root/l_receiptmonth=1997-11
  • root/l_receiptmonth=1997-12
• Filter and list subdirectories: root/l_receiptmonth=1997-10, root/l_receiptmonth=1997-11, root/l_receiptmonth=1997-12
  • root/l_receiptmonth=1997-10/l_shipmode=SHIP
  • root/l_receiptmonth=1997-10/l_shipmode=AIR
  • root/l_receiptmonth=1997-10/l_shipmode=TRUCK
  • root/l_receiptmonth=1997-11/l_shipmode=SHIP
  • root/l_receiptmonth=1997-11/l_shipmode=AIR
  • root/l_receiptmonth=1997-11/l_shipmode=TRUCK
  • root/l_receiptmonth=1997-12/l_shipmode=SHIP
  • root/l_receiptmonth=1997-12/l_shipmode=AIR
  • root/l_receiptmonth=1997-12/l_shipmode=TRUCK
• Filter and list subdirectories: root/l_receiptmonth=1997-10/l_shipmode=SHIP, root/l_receiptmonth=1997-11/l_shipmode=SHIP, root/l_receiptmonth=1997-12/l_shipmode=SHIP
  • root/l_receiptmonth=1997-10/l_shipmode=SHIP/data_0.csv
  • root/l_receiptmonth=1997-11/l_shipmode=SHIP/data_0.csv
  • root/l_receiptmonth=1997-12/l_shipmode=SHIP/data_0.csv

Because the Blob endpoint does not support the notion of directories, the filter can only be performed after the listing, whereas the ADLS endpoint will list files recursively. Especially with higher partition/directory counts, the performance difference can be very significant.