Integrating Azure Blob Storage with ClickHouse Cloud

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/clickpipes/object-storage/azure-blob-storage/01_overview.md)

The ABS ClickPipe provides a fully-managed and resilient way to ingest data from Azure Blob Storage into ClickHouse Cloud. It supports both **one-time** and **continuous ingestion** with exactly-once semantics.

ABS ClickPipes can be deployed and managed manually using the ClickPipes UI, as well as programmatically using [OpenAPI](https://clickhouse.com/docs/cloud/manage/api/swagger#tag/ClickPipes/paths/~1v1~1organizations~1%7BorganizationId%7D~1services~1%7BserviceId%7D~1clickpipes/post) and [Terraform](https://registry.terraform.io/providers/ClickHouse/clickhouse/latest/docs/resources/clickpipe).

## Supported formats[​](#supported-formats "Direct link to Supported formats")

- [JSON](/docs/interfaces/formats/JSON)
- [CSV](/docs/interfaces/formats/CSV)
- [TSV](/docs/interfaces/formats/TabSeparated)
- [Parquet](/docs/interfaces/formats/Parquet)
- [Avro](/docs/interfaces/formats/Avro)

## Features[​](#features "Direct link to Features")

### One-time ingestion[​](#one-time-ingestion "Direct link to One-time ingestion")

The ABS ClickPipe will load all files matched by a pattern from the specified container into the ClickHouse destination table in a single batch operation. Once the ingestion task completes, the ClickPipe stops automatically. This one-time ingestion mode provides exactly-once semantics, ensuring that each file is processed reliably without duplicates.

### Continuous ingestion[​](#continuous-ingestion "Direct link to Continuous ingestion")

When continuous ingestion is enabled, ClickPipes continuously ingests data from the specified path. To determine ingestion order, the ABS ClickPipe relies on the implicit [lexicographical order](#continuous-ingestion-lexicographical-order) of files.

#### Lexicographical order[​](#continuous-ingestion-lexicographical-order "Direct link to Lexicographical order")

The ABS ClickPipe assumes files are added to a container in lexicographical order, and relies on this implicit order to ingest files sequentially. This means that any new file **must** be lexically greater than the last ingested file. For example, files named `file1`, `file2`, and `file3` will be ingested sequentially, but if a new `file 0` is added to the container, it will be **ignored** because the file name isn't lexically greater than the last ingested file.

In this mode, the ABS ClickPipe does an initial load of **all files** in the specified path, and then polls for new files at a configurable interval (by default, 30 seconds). It is **not possible** to start ingestion from a specific file or point in time — ClickPipes will always load all files in the specified path.

### File pattern matching[​](#file-pattern-matching "Direct link to File pattern matching")

Object Storage ClickPipes follow the POSIX standard for file pattern matching. All patterns are **case-sensitive** and match the **full path** after the container name. For better performance, use the most specific pattern possible (e.g., `data-2024-*.csv` instead of `*.csv`).

#### Supported patterns[​](#supported-patterns "Direct link to Supported patterns")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Pattern Description Example Matches|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `?` Matches exactly **one** character (excluding `/`) `data-?.csv` `data-1.csv`, `data-a.csv`, `data-x.csv`| `*` Matches **zero or more** characters (excluding `/`) `data-*.csv` `data-1.csv`, `data-001.csv`, `data-report.csv`, `data-.csv`| `**`   Recursive Matches **zero or more** characters (including `/`). Enables recursive directory traversal. `logs/**/error.log` `logs/error.log`, `logs/2024/error.log`, `logs/2024/01/error.log` | | | | | | | | | | | | | | | |

**Examples:**

- `https://storageaccount.blob.core.windows.net/container/folder/*.csv`
- `https://storageaccount.blob.core.windows.net/container/logs/**/data.json`
- `https://storageaccount.blob.core.windows.net/container/file-?.parquet`
- `https://storageaccount.blob.core.windows.net/container/data-2024-*.csv.gz`

#### Unsupported patterns[​](#unsupported-patterns "Direct link to Unsupported patterns")

|  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Pattern Description Example Alternatives|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | `{abc,def}` Brace expansion - alternatives `{logs,data}/file.csv` Create separate ClickPipes for each path.|  |  |  |  | | --- | --- | --- | --- | | `{N..M}` Numeric range expansion `file-{1..100}.csv` Use `file-*.csv` or `file-?.csv`. | | | | | | | | | | | |

**Examples:**

- `https://storageaccount.blob.core.windows.net/container/{documents-01,documents-02}.json`
- `https://storageaccount.blob.core.windows.net/container/file-{1..100}.csv`
- `https://storageaccount.blob.core.windows.net/container/{logs,metrics}/data.parquet`

### Exactly-once semantics[​](#exactly-once-semantics "Direct link to Exactly-once semantics")

Various types of failures can occur when ingesting large dataset, which can result in a partial inserts or duplicate data. Object Storage ClickPipes are resilient to insert failures and provides exactly-once semantics. This is accomplished by using temporary "staging" tables. Data is first inserted into the staging tables. If something goes wrong with this insert, the staging table can be truncated and the insert can be retried from a clean state. Only when an insert is completed and successful, the partitions in the staging table are moved to target table. To read more about this strategy, check-out [this blog post](https://clickhouse.com/blog/supercharge-your-clickhouse-data-loads-part3).

### Virtual columns[​](#virtual-columns "Direct link to Virtual columns")

To track which files have been ingested, include the `_file` virtual column to the column mapping list. The `_file` virtual column contains the filename of the source object, which can be used to query which files have been processed.

## Access control[​](#access-control "Direct link to Access control")

### Permissions[​](#permissions "Direct link to Permissions")

The ABS ClickPipe only supports private containers. Public containers are **not** supported.

Containers must allow the [`s3:GetObject`](https://docs.aws.amazon.com/AmazonS3/latest/API/API_GetObject.html) and [`s3:ListBucket`](https://docs.aws.amazon.com/AmazonS3/latest/API/API_ListObjectsV2.html) actions in the bucket policy.

### Authentication[​](#authentication "Direct link to Authentication")

Note

Microsoft Entra ID authentication (including Managed Identities) isn't currently supported.

Azure Blob Storage authentication uses a [connection string](https://docs.microsoft.com/en-us/azure/storage/common/storage-configure-connection-string), which supports both access keys and shared access signatures (SAS).

#### Access key[​](#access-key "Direct link to Access key")

To authenticate using an [account access key](https://docs.microsoft.com/en-us/azure/storage/common/storage-account-keys-manage), provide a connection string in the following format:

```
DefaultEndpointsProtocol=https;AccountName=storage-account-name;AccountKey=account-access-key;EndpointSuffix=core.windows.net
```

You can find your storage account name and access key in the Azure Portal under **Storage Account > Access keys**.

#### Shared Access Signature (SAS)[​](#sas "Direct link to Shared Access Signature (SAS)")

To authenticate using a [Shared Access Signature (SAS)](https://docs.microsoft.com/en-us/azure/storage/common/storage-sas-overview), provide a connection string that includes the SAS token:

```
BlobEndpoint=https://storage-account-name.blob.core.windows.net/;SharedAccessSignature=sas-token
```