ClickHouse DB::Exception: Unsupported URI scheme

The "DB::Exception: Unsupported URI scheme" error in ClickHouse appears when you provide a URL with a protocol that ClickHouse does not recognize or support. The error code for this issue is UNSUPPORTED_URI_SCHEME. ClickHouse expects URLs to use specific schemes like http, https, or s3, and any other protocol in the URI will trigger this exception.

Impact

Queries that reference external data sources using an unsupported protocol will fail immediately. This prevents data from being read from or written to the specified remote location. Any pipeline or application relying on the affected query will be blocked until the URL is corrected.

Common Causes

1. Using a protocol scheme that ClickHouse does not support, such as ftp://, sftp://, or file:// in a remote table function.
2. Typos in the URL scheme, for example htps:// instead of https://.
3. Copy-pasting a URL from documentation or another system that uses a different protocol convention.
4. Attempting to use a custom or proprietary protocol that requires a dedicated connector.
5. Confusing ClickHouse table functions -- for example, using url() with an s3:// scheme instead of the s3() table function.

Troubleshooting and Resolution Steps

1. Check the URL in your query and verify the protocol scheme. ClickHouse supports http and https for the url() function, and s3 or https for the s3() function:

   -- Correct: using http or https with url()
   SELECT * FROM url('https://example.com/data.csv', CSV);
   
   -- Correct: using s3() for S3 paths
   SELECT * FROM s3('https://s3.amazonaws.com/bucket/file.parquet', 'key', 'secret', 'Parquet');
   

2. If you intended to access an S3 resource, use the s3() table function rather than url():

   -- Wrong: s3:// scheme with url()
   -- SELECT * FROM url('s3://my-bucket/data.csv', CSV);
   
   -- Right: use the s3() function
   SELECT * FROM s3('https://s3.amazonaws.com/my-bucket/data.csv', 'Parquet');
   

3. For HDFS access, use the hdfs() table function with the proper hdfs:// scheme, which is handled by its own integration:

   SELECT * FROM hdfs('hdfs://namenode:8020/path/to/file.parquet', 'Parquet');
   

4. Verify that the ClickHouse version you are running supports the protocol you need. Newer table functions and engines are added in later releases.

5. If you need to access a resource via FTP or another unsupported protocol, consider downloading the data to local or S3 storage first, then pointing ClickHouse at that intermediate location.

Best Practices

• Always use the appropriate ClickHouse table function for the storage type you are accessing (e.g., s3() for S3, hdfs() for HDFS, url() for HTTP/HTTPS).
• Double-check URLs for typos before running queries, especially the scheme portion.
• Standardize URL patterns in your organization's configuration management to prevent ad-hoc protocol mismatches.
• Keep ClickHouse updated to take advantage of new integrations and protocol support as they become available.

Frequently Asked Questions

Q: Which URI schemes does ClickHouse support?
A: The url() table function supports http and https. The s3() function works with S3-compatible endpoints over https. The hdfs() function supports hdfs://. Other protocols are not natively supported.

Q: Can I add support for a custom URI scheme?
A: ClickHouse does not provide a plugin mechanism for adding custom URI schemes. If you need to access data over an unsupported protocol, use an intermediate step to stage the data in a format and location ClickHouse can read.

Q: I am getting this error with an s3:// URL. What went wrong?
A: The url() table function does not understand the s3:// scheme. Use the s3() table function instead, passing the full HTTPS URL of your S3 endpoint or using the appropriate S3 path format.

Q: Does ClickHouse support reading from local files using file://?
A: The file:// scheme is not used with the url() function. To read local files, use the file() table function with a filesystem path instead of a URL.