ExternalDistributed

The ExternalDistributed engine allows to perform SELECT queries on data that is stored on a remote servers MySQL or PostgreSQL. Accepts MySQL or PostgreSQL engines as an argument so sharding is possible.

Creating a Table

  1. CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
  2. (
  3.     name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
  4.     name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
  5.     ...
  6. ) ENGINE = ExternalDistributed('engine', 'host:port', 'database', 'table', 'user', 'password');

See a detailed description of the CREATE TABLE query.

The table structure can differ from the original table structure:

  • Column names should be the same as in the original table, but you can use just some of these columns and in any order.
  • Column types may differ from those in the original table. ClickHouse tries to cast values to the ClickHouse data types.

Engine Parameters

  • engine — The table engine MySQL or PostgreSQL.
  • host:port — MySQL or PostgreSQL server address.
  • database — Remote database name.
  • table — Remote table name.
  • user — User name.
  • password — User password.

Implementation Details

Supports multiple replicas that must be listed by | and shards must be listed by ,. For example:

  1. CREATE TABLE test_shards (id UInt32, name String, age UInt32, money UInt32) ENGINE = ExternalDistributed('MySQL', `mysql{1|2}:3306,mysql{3|4}:3306`, 'clickhouse', 'test_replicas', 'root', 'clickhouse');

When specifying replicas, one of the available replicas is selected for each of the shards when reading. If the connection fails, the next replica is selected, and so on for all the replicas. If the connection attempt fails for all the replicas, the attempt is repeated the same way several times.

You can specify any number of shards and any number of replicas for each shard.

See Also