Azure Storage¶

Azure Blob Storage is Microsoft’s object storage service for the cloud, optimized for storing large amounts of unstructured data. Resources are addressed using the az:// URL scheme.

Azure Data Lake Storage Gen2 is a set of capabilities built on Azure Blob Storage for big-data analytics. A Gen2 account is a storage account with a hierarchical namespace enabled; it shares the same underlying service and API as Azure Blob Storage, and uses the adls:// and abfss:// URL schemes.

omniload supports both Azure Blob Storage and Azure Data Lake Storage as both data source and destination.

URI format¶

The URI for connecting to Azure Blob Storage is structured as follows.

az://?account_name=<your_account_name>&account_key=<your_account_key>

The URIs for connecting to Azure Data Lake Storage are structured as follows.

adls://?account_name=<your_account_name>&account_key=<your_account_key>

abfss:// is accepted as well and behaves identically; it is the dominant Gen2 scheme in the Spark, Databricks, and Synapse ecosystems.

abfss://?account_name=<your_account_name>&account_key=<your_account_key>

URI parameters¶

account_name:: Your Azure storage account name (required).
account_key:: Your storage account access key (account-key auth).
sas_token:: A Shared Access Signature token (SAS auth), alternatively to account_key.
tenant_id, client_id, client_secret:: Azure AD service-principal credentials (service-principal auth). All three are required together.
account_host:: Custom storage endpoint host (optional, for sovereign clouds or Azurite).
layout:: Layout template (optional, destination only).

Supply one authentication mode: an account_key, a sas_token, or the full service-principal triplet (tenant_id + client_id + client_secret). Supplying both an account key/SAS and service-principal fields is rejected as ambiguous, and a partial service-principal triplet reports the missing field.

Warning

Account keys are base64 (containing +, /, =) and SAS tokens embed their own & and = characters. You must URL-encode credential values in the URI (+ becomes %2B, / becomes %2F, = becomes %3D, & becomes %26). Unencoded values are mangled when the query string is parsed.

Note

A Gen2 account differs from a plain Blob account only by having its hierarchical namespace enabled, so no separate configuration is required.

Set up an Azure Storage integration¶

To integrate omniload with Azure Blob or Data Lake Storage, you need a storage account and one of the supported credentials. For guidance on obtaining an account key or SAS token, refer to the Microsoft documentation on managing storage account access keys and shared access signatures. Service-principal credentials come from an Azure AD app registration.

Authenticate with SAS token¶

omniload ingest \
    --source-uri 'az://?account_name=mystorageacct&sas_token=sv%3D2023-01-03%26ss%3Db%26sig%3DaBcD1234%253D' \
    --source-table 'my-container/data.csv' \
    --dest-uri 'duckdb:///local.duckdb' \
    --dest-table 'public.my_data'

Authenticate with service principal¶

omniload ingest \
    --source-uri 'az://?account_name=mystorageacct&tenant_id=<tenant>&client_id=<client>&client_secret=<secret>' \
    --source-table 'my-container/data.csv' \
    --dest-uri 'duckdb:///local.duckdb' \
    --dest-table 'public.my_data'

File glob patterns¶

The --source-table parameter specifies the Azure Storage container name and the file glob pattern using the following format.

<container-name>/<file-glob-pattern>

The <file-glob-pattern> allows for flexible file selection. Here are some common patterns and their descriptions.

Pattern	Description
`container/*/.csv`	Retrieves all CSV files recursively from `az://container`.
`container/*.csv`	Retrieves all CSV files located at the root level of `az://container`.
`container/myFolder/*/.jsonl`	Retrieves all JSONL files recursively from the `myFolder` directory and its subdirectories in `az://container`.
`container/myFolder/mySubFolder/users.parquet`	Retrieves the specific `users.parquet` file from the `myFolder/mySubFolder/` path in `az://container`.
`container/employees.jsonl`	Retrieves the `employees.jsonl` file located at the root level of `az://container`.

File type hinting¶

If your files are properly encoded but lack the correct file extension (BSON, CSV, JSONL, or Parquet), you can provide a file type hint to inform omniload about the format of the files. This is done by appending a fragment identifier (#format) to the end of the path in your --source-table parameter.

For example, if you have JSONL-formatted log files stored in Azure with a non-standard extension, use an URI pattern like this.

--source-table "my-container/logs/event-data#jsonl"

See also the full list of Supported formats and their type hints.

Tip

File type hinting works with gzip compressed files as well.

Compressed files¶

omniload automatically detects and handles gzipped files in your container. You can load data from compressed files with the .gz extension without any additional configuration.

For example, to load data from a gzipped CSV file:

omniload ingest \
    --source-uri 'az://?account_name=mystorageacct&account_key=dGVzdA%3D%3D' \
    --source-table 'my-container/logs/event-data.csv.gz' \
    --dest-uri duckdb:///compressed_data.duckdb \
    --dest-table 'logs.events'

Examples¶

Load data from Azure Blob Storage¶

Let’s assume the following details:

account_name: mystorageacct
account_key: dGVzdA==
Container name: my-container
Path to files within the container: students/students_details.csv

The following command demonstrates how to copy data from the specified Azure location to a DuckDB database (the account key is URL-encoded, so == becomes %3D%3D):

omniload ingest \
    --source-uri 'az://?account_name=mystorageacct&account_key=dGVzdA%3D%3D' \
    --source-table 'my-container/students/students_details.csv' \
    --dest-uri duckdb:///azure_data.duckdb \
    --dest-table 'processed_students.student_details'

This command will create a table named student_details within the processed_students schema (or equivalent grouping) in the DuckDB database file located at azure_data.duckdb.

Upload data to Azure Blob Storage¶

For this example, we’ll assume that:

records.db is a duckdb database.
It has a table called public.users.
The Azure credentials are the same as the example above.

The following command demonstrates how to copy data from a local duckdb database to Azure Blob Storage:

omniload ingest \
    --source-uri 'duckdb:///records.db' \
    --source-table 'public.users' \
    --dest-uri 'az://?account_name=mystorageacct&account_key=dGVzdA%3D%3D' \
    --dest-table 'my-container/records'

This will result in a file structure like the following:

my-container/
└── records
    ├── _dlt_loads
    ├── _dlt_pipeline_state
    ├── _dlt_version
    └── users
        └── <load_id>.<file_id>.parquet

The value of load_id and file_id is determined at runtime. The default layout creates a folder with the same table name as the source and places the data inside a parquet file. This layout is configurable using the layout parameter. See the available layout placeholders for the full list.

Load data from ADLS Gen2¶

omniload ingest \
    --source-uri 'adls://?account_name=mystorageacct&account_key=dGVzdA%3D%3D' \
    --source-table 'my-filesystem/events/2024/*.parquet' \
    --dest-uri duckdb:///adls_data.duckdb \
    --dest-table 'analytics.events'

Upload data to ADLS Gen2¶

omniload ingest \
    --source-uri 'duckdb:///records.db' \
    --source-table 'public.users' \
    --dest-uri 'abfss://?account_name=mystorageacct&account_key=dGVzdA%3D%3D' \
    --dest-table 'my-filesystem/users'