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
Parameters
| Parameter | Description |
|---|---|
URL | HTTP or HTTPS server address, which can accept GET or POST requests (for SELECT or INSERT queries correspondingly). 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.
Inserting data from a URL into a table:
Globs in URL
Patterns in curly brackets { } 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.
Virtual Columns
_path— Path to theURL. Type:LowCardinality(String)._file— Resource name of theURL. Type:LowCardinality(String)._size— Size of the resource in bytes. Type:Nullable(UInt64). If the size is unknown, the value isNULL._time— Last modified time of the file. Type:Nullable(DateTime). If the time is unknown, the value isNULL._headers- HTTP response headers. Type:Map(LowCardinality(String), LowCardinality(String)).
Hive-style partitioning
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, but starting with _.
Example
Use virtual column, created with Hive-style partitioning
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.
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.
