azureBlobStorage Table Function

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/table-functions/azureBlobStorage.md)

Provides a table-like interface to select/insert files in [Azure Blob Storage](https://azure.microsoft.com/en-us/products/storage/blobs). This table function is similar to the [s3 function](/docs/sql-reference/table-functions/s3).

## Syntax[​](#syntax "Direct link to Syntax")

- Connection string- Storage account URL- Named collection

Credentials are embedded in the connection string, so no separate `account_name`/`account_key` is needed:

```
azureBlobStorage(connection_string, container_name, blobpath [, format, compression, structure])
```

Requires `account_name` and `account_key` as separate arguments:

```
azureBlobStorage(storage_account_url, container_name, blobpath, account_name, account_key [, format, compression, structure])
```

See [Named Collections](#named-collections) below for the full list of supported keys:

```
azureBlobStorage(named_collection[, option=value [,..]])
```

## Arguments[​](#arguments "Direct link to Arguments")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Argument Description|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `connection_string` A connection string that includes embedded credentials (account name + account key or SAS token). When using this form, `account_name` and `account_key` should **not** be passed separately. See [Configure a connection string](https://learn.microsoft.com/en-us/azure/storage/common/storage-configure-connection-string?toc=%2Fazure%2Fstorage%2Fblobs%2Ftoc.json&bc=%2Fazure%2Fstorage%2Fblobs%2Fbreadcrumb%2Ftoc.json#configure-a-connection-string-for-an-azure-storage-account).| `storage_account_url` The storage account endpoint URL, e.g. `https://myaccount.blob.core.windows.net/`. When using this form, you **must** also pass `account_name` and `account_key`.| `container_name` Container name.|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `blobpath` File path. Supports the following wildcards in read-only mode: `*`, `**`, `?`, `{abc,def}` and `{N..M}` where `N`, `M` — numbers, `'abc'`, `'def'` — strings.| `account_name` Storage account name. **Required** when using `storage_account_url` without SAS; must **not** be passed when using `connection_string`.| `account_key` Storage account key. **Required** when using `storage_account_url` without SAS; must **not** be passed when using `connection_string`.| `format` The [format](/docs/sql-reference/formats) of the file.| `compression` Supported values: `none`, `gzip/gz`, `brotli/br`, `xz/LZMA`, `zstd/zst`. By default, it will autodetect compression by file extension (same as setting to `auto`).| `structure` Structure of the table. Format `'column1_name column1_type, column2_name column2_type, ...'`.| `partition_strategy` Optional. Supported values: `WILDCARD` or `HIVE`. `WILDCARD` requires a `{_partition_id}` in the path, which is replaced with the partition key. `HIVE` does not allow wildcards, assumes the path is the table root, and generates Hive-style partitioned directories with Snowflake IDs as filenames and the file format as the extension. Defaults to `WILDCARD`.| `partition_columns_in_data_file` Optional. Only used with `HIVE` partition strategy. Tells ClickHouse whether to expect partition columns to be written in the data file. Defaults `false`.| `extra_credentials` Use `client_id` and `tenant_id` for authentication. If extra\_credentials are provided, they are given priority over `account_name` and `account_key`. | | | | | | | | | | | | | | | | | | | | | | | | | |

## Named Collections[​](#named-collections "Direct link to Named Collections")

Arguments can also be passed using [named collections](/docs/operations/named-collections). In this case the following keys are supported:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Key Required Description|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `container` Yes Container name. Corresponds to the positional argument `container_name`.| `blob_path` Yes File path (with optional wildcards). Corresponds to the positional argument `blobpath`.| `connection_string` No\* Connection string with embedded credentials. \*Either `connection_string` or `storage_account_url` must be provided.| `storage_account_url` No\* Storage account endpoint URL. \*Either `connection_string` or `storage_account_url` must be provided.| `account_name` No Required when using `storage_account_url`| `account_key` No Required when using `storage_account_url`| `format` No File format.|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `compression` No Compression type.|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `structure` No Table structure.|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `client_id` No Client ID for authentication.|  |  |  | | --- | --- | --- | | `tenant_id` No Tenant ID for authentication. | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

Note

Named collection key names differ from positional function argument names: `container` (not `container_name`) and `blob_path` (not `blobpath`).

**Example:**

```
CREATE NAMED COLLECTION azure_my_data AS
    storage_account_url = 'https://myaccount.blob.core.windows.net/',
    container = 'mycontainer',
    blob_path = 'data/*.parquet',
    account_name = 'myaccount',
    account_key = 'mykey...==',
    format = 'Parquet';

SELECT *
FROM azureBlobStorage(azure_my_data)
LIMIT 5;
```

You can also override named collection values at query time:

```
SELECT *
FROM azureBlobStorage(azure_my_data, blob_path = 'other_data/*.csv', format = 'CSVWithNames')
LIMIT 5;
```

## Returned value[​](#returned_value "Direct link to Returned value")

A table with the specified structure for reading or writing data in the specified file.

## Examples[​](#examples "Direct link to Examples")

### Reading with `storage_account_url` form[​](#reading-with-storage-account-url "Direct link to reading-with-storage-account-url")

```
SELECT *
FROM azureBlobStorage(
    'https://myaccount.blob.core.windows.net/',
    'mycontainer',
    'data/*.parquet',
    'myaccount',
    'mykey...==',
    'Parquet'
)
LIMIT 5;
```

### Reading with `connection_string` form[​](#reading-with-connection-string "Direct link to reading-with-connection-string")

```
SELECT *
FROM azureBlobStorage(
    'DefaultEndpointsProtocol=https;AccountName=myaccount;AccountKey=mykey...==;EndPointSuffix=core.windows.net',
    'mycontainer',
    'data/*.csv',
    'CSVWithNames'
)
LIMIT 5;
```

### Writing with partitions[​](#writing-with-partitions "Direct link to Writing with partitions")

```
INSERT INTO TABLE FUNCTION azureBlobStorage(
    'DefaultEndpointsProtocol=https;AccountName=myaccount;AccountKey=mykey...==;EndPointSuffix=core.windows.net',
    'mycontainer',
    'test_{_partition_id}.csv',
    'CSV',
    'auto',
    'column1 UInt32, column2 UInt32, column3 UInt32'
) PARTITION BY column3
VALUES (1, 2, 3), (3, 2, 1), (78, 43, 3);
```

Then read back a specific partition: