Querying external data in Managed ClickHouse®
Discover federated queries and their capabilities in Managed ClickHouse® and how they simplify and speed up migrating into Exoscale from external data sources.
Federated queries allow communication between Managed ClickHouse and S3-compatible object storages and web resources. The federated queries feature in Managed ClickHouse enables you to read and pull data from an external object storage that uses the S3 integration engine, or any web resource accessible over HTTP.
Note
The federated queries feature in Managed ClickHouse is enabled by default.
Why use federated queries
There are a few reasons why you might want to use federated queries:
- Query remote data from your ClickHouse service. Ingest it into Managed ClickHouse® or only reference external data sources as part of an analytics query. In the context of an increasing footprint of connected data sources, federated queries can help you better understand how your customers use your products.
- Simplify and speed up the import of your data into the Managed ClickHouse instance from a legacy data source, avoiding a long and sometimes complex migration path.
- Improve the migration of data in Managed ClickHouse, and extend analysis over external data sources with a relatively low effort in comparison to enabling distributed tables and the remote and remoteSecure functionalities.
Note
The remote() and remoteSecure() features are designed to read from
remote data sources or provide the ability to create a distributed table
across remote data sources but they are not designed to read from an
external S3 storage.
How it works
To run a federated query, the ClickHouse service user connecting to the cluster requires grants to the S3 and/or URL sources. The main service user is granted access to the sources by default, and new users can be allowed to use the sources via the CREATE TEMPORARY TABLE grant, which is required for both sources.
For more information on how to enable new users to use the sources, see Prerequisites.
Federated queries read from external S3-compatible object storage utilizing the ClickHouse S3 engine. Once you read from a remote S3-compatible storage, you can select from that storage and insert into a table in the Exoscale local instance, enabling migration of data into Exoscale.
For more details on how to run federated queries in Managed ClickHouse, see Run federated queries in Managed ClickHouse®.
Supported sources
Managed ClickHouse queries S3-compatible object storage, Azure Blob Storage, HTTP resources
and external databases such as PostgreSQL and MySQL. You can use them through table
functions, or create persistent tables with the matching table engine (S3, URL,
AzureBlobStorage, PostgreSQL, MySQL).
Reading from an external source requires the privilege of that source, for example
GRANT S3 or GRANT URL, in addition to the CREATE TEMPORARY TABLE grant.