Skip to main content
Skip to main content
Edit this page

Delta Lake integration

Users can integrate with the Delta lake table format via the table function.

deltaLake Table Function

Provides a read-only table-like interface to Delta Lake tables in Amazon S3, Azure Blob Storage, or a locally mounted file system.

Syntax​

deltaLake is an alias of deltaLakeS3, its supported for compatibility.

deltaLake(url [,aws_access_key_id, aws_secret_access_key] [,format] [,structure] [,compression])

deltaLakeS3(url [,aws_access_key_id, aws_secret_access_key] [,format] [,structure] [,compression])

deltaLakeAzure(connection_string|storage_account_url, container_name, blobpath, [,account_name], [,account_key] [,format] [,compression_method])

deltaLakeLocal(path, [,format])

Arguments​

Description of the arguments coincides with description of arguments in table functions s3, azureBlobStorage, HDFS and file correspondingly. format stands for the format of data files in the Delta lake table.

Returned value​

A table with the specified structure for reading data in the specified Delta Lake table.

Examples​

Selecting rows from the table in S3 https://clickhouse-public-datasets.s3.amazonaws.com/delta_lake/hits/:

SELECT
    URL,
    UserAgent
FROM deltaLake('https://clickhouse-public-datasets.s3.amazonaws.com/delta_lake/hits/')
WHERE URL IS NOT NULL
LIMIT 2

┌─URL───────────────────────────────────────────────────────────────────┬─UserAgent─┐
│ http://auto.ria.ua/search/index.kz/jobinmoscow/detail/55089/hasimages │         1 │
│ http://auto.ria.ua/search/index.kz/jobinmoscow.ru/gosushi             │         1 │
└───────────────────────────────────────────────────────────────────────┴───────────┘

Virtual Columns​

  • _path — Path to the file. Type: LowCardinality(String).
  • _file — Name of the file. Type: LowCardinality(String).
  • _size — Size of the file in bytes. Type: Nullable(UInt64). If the file size is unknown, the value is NULL.
  • _time — Last modified time of the file. Type: Nullable(DateTime). If the time is unknown, the value is NULL.
  • _etag — The etag of the file. Type: LowCardinality(String). If the etag is unknown, the value is NULL.