url Table Function

url function creates a table from the URL with given format and structure.

url function may be used in SELECT and INSERT queries on data in URL tables.

Syntax

url(URL [,format] [,structure] [,headers])

Parameters

Parameter	Description
URL	A single-quoted URL whose scheme selects the backend. An http/https (or unrecognized) URL is a server address accepting GET or POST requests (for SELECT or INSERT queries correspondingly); a recognized non-HTTP scheme (file://, s3://, az://, hdfs://, …) is delegated to the matching table function — see Dispatching by URL scheme. Type: String.
format	Format of the data. Type: String.
structure	Table structure in 'UserID UInt64, Name String' format. Determines column names and types. Type: String.
headers	Headers in 'headers('key1'='value1', 'key2'='value2')' format. You can set headers for HTTP call.

Returned value

A table with the specified format and structure and with data from the defined URL.

Examples

Getting the first 3 lines of a table that contains columns of String and UInt32 type from HTTP-server which answers in CSV format.

SELECT * FROM url('http://127.0.0.1:12345/', CSV, 'column1 String, column2 UInt32', headers('Accept'='text/csv; charset=utf-8')) LIMIT 3;

Inserting data from a URL into a table:

CREATE TABLE test_table (column1 String, column2 UInt32) ENGINE=Memory;
INSERT INTO FUNCTION url('http://127.0.0.1:8123/?query=INSERT+INTO+test_table+FORMAT+CSV', 'CSV', 'column1 String, column2 UInt32') VALUES ('http interface', 42);
SELECT * FROM test_table;

Dispatching by URL scheme

The url function acts as a unified wrapper on top of the other file- and object-storage table functions: it dispatches to the right backend based on the URL scheme. This lets you read from any supported location with a single uniform syntax.

Scheme	Dispatches to
http, https (and any unrecognized scheme)	the URL engine itself (HTTP GET/POST)
file	the file function
s3, gs, gcs, oss	the s3 function
az, azure, abfss, abfs	the azureBlobStorage function
hdfs	the hdfs function

Only the S3 schemes that the S3 URI mapper resolves to a concrete endpoint without extra configuration (s3, plus gs/gcs/oss) are dispatched. Other S3-compatible vendor schemes (cos, obs, eos, …) are region-specific and have no default endpoint mapping, so a cos://… URL is treated as an unrecognized scheme and reported as an error; use the s3 function directly (with url_scheme_mappers configured) for those backends.

For file://, a relative path (file://data.csv) is resolved inside the user_files directory, and an absolute path (file:///home/user/data.csv) must point inside it as usual.

The format, structure and compression_method arguments and the url_base setting work the same regardless of the dispatch target.

SELECT * FROM url('file://data.csv', CSV, 'a UInt32, b String');
SELECT * FROM url('s3://clickhouse-public-datasets/hits_compatible/hits.csv');

Scheme dispatch is not yet wired through urlCluster: a non-http(s) scheme passed to urlCluster is rejected with an error. Use the corresponding cluster function (s3Cluster, azureBlobStorageCluster, hdfsCluster, …) for those backends instead.

Globs in URL

Patterns in { } are used to generate a set of shards or to specify failover addresses. Supported pattern types and examples see in the description of the remote function. Character | inside patterns is used to specify failover addresses. They are iterated in the same order as listed in the pattern. The number of generated addresses is limited by glob_expansion_max_elements setting. For path glob syntax in the URL path (such as *, {a,b}, {N..M}, and **), see Globs in path. Note that ? starts the query string in a URL and cannot be used as a wildcard in the path component.

Wildcards with HTTP index pages

For url and the URL table engine, ClickHouse can expand wildcards by fetching HTTP index pages (HTML or plaintext) and extracting URLs from the response body. This enables patterns like /**/ when the server exposes directory listings.

Notes:

• Relative URLs are resolved against the index page URL.
• URL templates are expanded before fetching index pages, including comma and numeric range shard expansion and | failover options outside the path component.
• | failover patterns inside the path component are not supported for HTTP index-page expansion.
• Wildcard matching is applied to the URL path component.
• If a listed URL already contains a query string or fragment, it takes precedence over the ones from the source URL. Otherwise, the query string and fragment from the source URL are used.
• An empty listing is allowed; HTTP errors (e.g. 404) for index pages raise exceptions.
• The maximum index page size is limited by max_http_index_page_size.
• The maximum number of directories read during recursive expansion is limited by url_wildcard_max_directories_to_read.

Example:

SELECT count()
FROM url('https://ftp.gnu.org/gnu/wget/wget-1.21*.tar.gz', 'RawBLOB')
SETTINGS max_threads = 1, allow_experimental_url_wildcard_from_index_pages = 1;

Virtual Columns

• _path — Path to the URL. Type: LowCardinality(String).
• _file — Resource name of the URL. Type: LowCardinality(String).
• _size — Size of the resource in bytes. Type: Nullable(UInt64). If the 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.
• _headers - HTTP response headers. Type: Map(LowCardinality(String), LowCardinality(String)).

use_hive_partitioning setting

When setting use_hive_partitioning is set to 1, ClickHouse will detect Hive-style partitioning in the path (/name=value/) and will allow to use partition columns as virtual columns in the query. These virtual columns will have the same names as in the partitioned path.

Example

Use virtual column, created with Hive-style partitioning

SELECT * FROM url('http://data/path/date=*/country=*/code=*/*.parquet') WHERE date > '2020-01-01' AND country = 'Netherlands' AND code = 42;

Resolving relative URLs

The url_base setting allows passing a relative URL to the url function. When url_base is set and the function argument is a relative reference, it is resolved against the base URL per RFC 3986.

The resolution rules are:

• Path-relative (e.g. data.csv): merged with the base URL path — everything after the last / of the base path is replaced. The trailing slash matters: https://example.com/dir/ + data.csv gives https://example.com/dir/data.csv, but https://example.com/dir + data.csv gives https://example.com/data.csv. Dot segments (./ and ../) are normalized.
• Host-relative (e.g. /test/data.csv): resolved using the scheme and host of the base URL.
• Scheme-relative (e.g. //other.com/test/data.csv): resolved using the scheme of the base URL.
• Query-only (e.g. ?x=1): appended to the full base path, replacing any existing query or fragment.
• Fragment-only (e.g. #frag): appended to the base URL, preserving the query, replacing any existing fragment.
• Empty: returns the base URL without fragment.
• Absolute URL: passed through unchanged; url_base is ignored.

Example

SET url_base = 'https://raw.githubusercontent.com/ClickHouse/ClickHouse/master/';
SELECT * FROM url('tests/queries/0_stateless/data_csv/data.csv', CSV) LIMIT 3;

Storage Settings

• engine_url_skip_empty_files - allows to skip empty files while reading. Disabled by default.
• enable_url_encoding - allows to enable/disable decoding/encoding path in uri. Enabled by default.
• url_base - base URL for resolving relative URLs passed to the url function.

Permissions

url function requires CREATE TEMPORARY TABLE permission. As such - it'll not work for users with readonly = 1 setting. At least readonly = 2 is required.

Related

• Virtual columns