ClickHouse/clickhouse-docs-embeddings huggingface dataset:
title_wip

databases via ODBC.|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [PostgreSQL table Engine](/docs/engines/table-engines/integrations/postgresql) The PostgreSQL engine allows `SELECT` and `INSERT` queries on data stored on a remote PostgreSQL server.| [RabbitMQ table engine](/docs/engines/table-engines/integrations/rabbitmq) This engine allows integrating ClickHouse with RabbitMQ.|  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [Redis table engine](/docs/engines/table-engines/integrations/redis) This engine allows integrating ClickHouse with Redis.|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [S3 table engine](/docs/engines/table-engines/integrations/s3) This engine provides integration with the Amazon S3 ecosystem. Similar to the HDFS engine, but provides S3-specific features.|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [AzureQueue table engine](/docs/engines/table-engines/integrations/azure-queue) This engine provides an integration with the Azure Blob Storage ecosystem, allowing streaming data import.|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | [S3Queue table engine](/docs/engines/table-engines/integrations/s3queue) This engine provides integration with the Amazon S3 ecosystem and allows streaming imports. Similar to the Kafka and RabbitMQ engines, but provides S3-specific features.|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | [SQLite table engine](/docs/engines/table-engines/integrations/sqlite) The engine allows to import and export data to SQLite and supports queries to SQLite tables directly from ClickHouse.|  |  |  |  | | --- | --- | --- | --- | | [YTsaurus table engine](/docs/engines/table-engines/integrations/ytsaurus) Table engine that allows importing data from a YTsaurus cluster.|  |  | | --- | --- | | [ArrowFlight table engine](/docs/engines/table-engines/integrations/arrowflight) The engine allows querying remote datasets via Apache Arrow Flight. | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/engines/table-engines/integrations/ExternalDistributed.md)

The `ExternalDistributed` engine allows to perform `SELECT` queries on data that is stored on a remote servers MySQL or PostgreSQL. Accepts [MySQL](/docs/engines/table-engines/integrations/mysql) or [PostgreSQL](/docs/engines/table-engines/integrations/postgresql) engines as an argument so sharding is possible.

## Creating a table[​](#creating-a-table "Direct link to Creating a table")

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

See a detailed description of the [CREATE TABLE](/docs/sql-reference/statements/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](/docs/sql-reference/functions/type-conversion-functions#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[​](#implementation-details "Direct link to Implementation details")

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

```
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**

- [MySQL table engine](/docs/engines/table-engines/integrations/mysql)
- [PostgreSQL table engine](/docs/engines/table-engines/integrations/postgresql)
- [Distributed table engine](/docs/engines/table-engines/special/distributed)

- [Creating a table](#creating-a-table)- [Implementation details](#implementation-details)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/engines/table-engines/integrations/arrowflight.md)

The ArrowFlight table engine enables ClickHouse to query remote datasets via the [Apache Arrow Flight](https://arrow.apache.org/docs/format/Flight.html) protocol.
This integration allows ClickHouse to fetch data from external Flight-enabled servers in a columnar Arrow format with high performance.

## Creating a Table[​](#creating-a-table "Direct link to Creating a Table")

```
CREATE TABLE [IF NOT EXISTS] [db.]table_name (name1 [type1], name2 [type2], ...)
    ENGINE = ArrowFlight('host:port', 'dataset_name' [, 'username', 'password']);
```

**Engine Parameters**

- `host:port` — Address of the remote Arrow Flight server.
- `dataset_name` — Identifier of the dataset on the Flight server.
- `username` - Username to use with basic HTTP style authentication.
- `password` - Password to use with basic HTTP style authentication.
  If `username` and `password` are not specified, it means that authentication is not used
  (that will work only if the Arrow Flight server allows it).

## Usage Example[​](#usage-example "Direct link to Usage Example")

This example shows how to create a table that reads data from a remote Arrow Flight server:

```
CREATE TABLE remote_flight_data
(
    id UInt32,
    name String,
    value Float64
) ENGINE = ArrowFlight('127.0.0.1:9005', 'sample_dataset');
```

Query the remote data as if it were a local table:

```
SELECT * FROM remote_flight_data ORDER BY id;
```

```
┌─id─┬─name────┬─value─┐
│  1 │ foo     │ 42.1  │
│  2 │ bar     │ 13.3  │
│  3 │ baz     │ 77.0  │
└────┴─────────┴───────┘
```

## Notes[​](#notes "Direct link to Notes")

- The schema defined in ClickHouse must match the schema returned by the Flight server.
- This engine is suitable for federated queries, data virtualization, and decoupling storage from compute.

## See Also[​](#see-also "Direct link to See Also")

- [Apache Arrow Flight SQL](https://arrow.apache.org/docs/format/FlightSql.html)
- [Arrow format integration in ClickHouse](/docs/interfaces/formats/Arrow)

- [Creating a Table](#creating-a-table)- [Usage Example](#usage-example)- [Notes](#notes)- [See Also](#see-also)

Was this page helpful?
title_wip

#### Partition strategy[​](#partition-strategy "Direct link to Partition strategy")

`WILDCARD` (default): Replaces the `{_partition_id}` wildcard in the file path with the actual partition key. Reading is not supported.

`HIVE` implements hive style partitioning for reads & writes. Reading is implemented using a recursive glob pattern. Writing generates files using the following format: `<prefix>/<key1=val1/key2=val2...>/<snowflakeid>.<toLower(file_format)>`.

Note: When using `HIVE` partition strategy, the `use_hive_partitioning` setting has no effect.

Example of `HIVE` partition strategy:

```
arthur :) create table azure_table (year UInt16, country String, counter UInt8) ENGINE=AzureBlobStorage(account_name='devstoreaccount1', account_key='Eby8vdM02xNOcqFlqUwJPLlmEtlCDXJ1OUzFT50uSRZ6IFsuFq2UVErCz4I6tq/K1SZFPTOtr/KBHBeksoGMGw==', storage_account_url = 'http://localhost:30000/devstoreaccount1', container='cont', blob_path='hive_partitioned', format='Parquet', compression='auto', partition_strategy='hive') PARTITION BY (year, country);

arthur :) insert into azure_table values (2020, 'Russia', 1), (2021, 'Brazil', 2);

arthur :) select _path, * from azure_table;

   ┌─_path──────────────────────────────────────────────────────────────────────┬─year─┬─country─┬─counter─┐
1. │ cont/hive_partitioned/year=2020/country=Russia/7351305360873664512.parquet │ 2020 │ Russia  │       1 │
2. │ cont/hive_partitioned/year=2021/country=Brazil/7351305360894636032.parquet │ 2021 │ Brazil  │       2 │
   └────────────────────────────────────────────────────────────────────────────┴──────┴─────────┴─────────┘
```

## See also[​](#see-also "Direct link to See also")

[Azure Blob Storage Table Function](/docs/sql-reference/table-functions/azureBlobStorage)

- [Create table](#create-table)
  - [Engine parameters](#engine-parameters)- [Virtual columns](#virtual-columns)- [Authentication](#authentication)
      - [Data cache](#data-cache)- [PARTITION BY](#partition-by)- [See also](#see-also)

Was this page helpful?
title_wip

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
title_wip

3600 \* 1000|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | output\_write\_timeout 3600 \* 1000|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | output\_close\_timeout 3600 \* 1000|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | output\_packetpool\_size 1024|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | output\_heartbeat\_interval 10 \* 1000|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | dfs\_client\_failover\_max\_attempts 15|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | dfs\_client\_read\_shortcircuit\_streams\_cache\_size 256|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | dfs\_client\_socketcache\_expiryMsec 3000|  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | dfs\_client\_socketcache\_capacity 16|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | dfs\_default\_blocksize 64 \* 1024 \* 1024|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | dfs\_default\_uri "hdfs://localhost:9000"|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | hadoop\_security\_authentication "simple"|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | hadoop\_security\_kerberos\_ticket\_cache\_path ""|  |  |  |  | | --- | --- | --- | --- | | dfs\_client\_log\_severity "INFO"|  |  | | --- | --- | | dfs\_domain\_socket\_path "" | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
Hudi table engine

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/engines/table-engines/integrations/hudi.md)

This engine provides a read-only integration with existing Apache [Hudi](https://hudi.apache.org/) tables in Amazon S3.

## Create table[​](#create-table "Direct link to Create table")

Note that the Hudi table must already exist in S3, this command does not take DDL parameters to create a new table.

```
CREATE TABLE hudi_table
    ENGINE = Hudi(url, [aws_access_key_id, aws_secret_access_key,] [extra_credentials])
```

**Engine parameters**

- `url` — Bucket url with the path to an existing Hudi table.
- `aws_access_key_id`, `aws_secret_access_key` - Long-term credentials for the [AWS](https://aws.amazon.com/) account user. You can use these to authenticate your requests. Parameter is optional. If credentials are not specified, they are used from the configuration file.
- `extra_credentials` - Optional. Used to pass a `role_arn` for role-based access in ClickHouse Cloud. See [Secure S3](/docs/cloud/data-sources/secure-s3) for configuration steps.

Engine parameters can be specified using [Named Collections](/docs/operations/named-collections).

**Example**

```
CREATE TABLE hudi_table ENGINE=Hudi('http://mars-doc-test.s3.amazonaws.com/clickhouse-bucket-3/test_table/', 'ABC123', 'Abc+123')
```

Using named collections:

```
<clickhouse>
    <named_collections>
        <hudi_conf>
            <url>http://mars-doc-test.s3.amazonaws.com/clickhouse-bucket-3/</url>
            <access_key_id>ABC123<access_key_id>
            <secret_access_key>Abc+123</secret_access_key>
        </hudi_conf>
    </named_collections>
</clickhouse>
```

```
CREATE TABLE hudi_table ENGINE=Hudi(hudi_conf, filename = 'test_table')
```

## See also[​](#see-also "Direct link to See also")

- [hudi table function](/docs/sql-reference/table-functions/hudi)

- [Create table](#create-table)- [See also](#see-also)

Was this page helpful?
title_wip

Assuming MongoDB has [sample\_mflix](https://www.mongodb.com/docs/atlas/sample-data/sample-mflix) dataset loaded

Create a table in ClickHouse which allows to read data from MongoDB collection:

```
CREATE TABLE sample_mflix_table
(
    _id String,
    title String,
    plot String,
    genres Array(String),
    directors Array(String),
    writers Array(String),
    released Date,
    imdb String,
    year String
) ENGINE = MongoDB('mongodb://<USERNAME>:<PASSWORD>@atlas-sql-6634be87cefd3876070caf96-98lxs.a.query.mongodb.net/sample_mflix?ssl=true&authSource=admin', 'movies');
```

Query:

```
SELECT count() FROM sample_mflix_table
```

```
   ┌─count()─┐
1. │   21349 │
   └─────────┘
```

```
-- JSONExtractString cannot be pushed down to MongoDB
SET mongodb_throw_on_unsupported_query = 0;

-- Find all 'Back to the Future' sequels with rating > 7.5
SELECT title, plot, genres, directors, released FROM sample_mflix_table
WHERE title IN ('Back to the Future', 'Back to the Future Part II', 'Back to the Future Part III')
    AND toFloat32(JSONExtractString(imdb, 'rating')) > 7.5
ORDER BY year
FORMAT Vertical;
```

```
Row 1:
──────
title:     Back to the Future
plot:      A young man is accidentally sent 30 years into the past in a time-traveling DeLorean invented by his friend, Dr. Emmett Brown, and must make sure his high-school-age parents unite in order to save his own existence.
genres:    ['Adventure','Comedy','Sci-Fi']
directors: ['Robert Zemeckis']
released:  1985-07-03

Row 2:
──────
title:     Back to the Future Part II
plot:      After visiting 2015, Marty McFly must repeat his visit to 1955 to prevent disastrous changes to 1985... without interfering with his first trip.
genres:    ['Action','Adventure','Comedy']
directors: ['Robert Zemeckis']
released:  1989-11-22
```

```
-- Find top 3 movies based on Cormac McCarthy's books
SELECT title, toFloat32(JSONExtractString(imdb, 'rating')) AS rating
FROM sample_mflix_table
WHERE arrayExists(x -> x LIKE 'Cormac McCarthy%', writers)
ORDER BY rating DESC
LIMIT 3;
```

```
   ┌─title──────────────────┬─rating─┐
1. │ No Country for Old Men │    8.1 │
2. │ The Sunset Limited     │    7.4 │
3. │ The Road               │    7.3 │
   └────────────────────────┴────────┘
```

## Troubleshooting[​](#troubleshooting "Direct link to Troubleshooting")

You can see the generated MongoDB query in DEBUG level logs.

Implementation details can be found in [mongocxx](https://github.com/mongodb/mongo-cxx-driver) and [mongoc](https://github.com/mongodb/mongo-c-driver) documentations.

- [Creating a table](#creating-a-table)- [Types mappings](#types-mappings)
    - [OID](#oid)- [Supported clauses](#supported-clauses)- [Usage example](#usage-example)- [Troubleshooting](#troubleshooting)

Was this page helpful?
S3 table engine

Extracting data from these archives is possible using ::. Globs can be used both in the url part as well as in the part after :: (responsible for the name of a file inside the archive).

```
SELECT *
FROM s3(
   'https://s3-us-west-1.amazonaws.com/umbrella-static/top-1m-2018-01-1{0..2}.csv.zip :: *.csv'
);
```

Note

ClickHouse supports three archive formats:
ZIP
TAR
7Z
While ZIP and TAR archives can be accessed from any supported storage location, 7Z archives can only be read from the local filesystem where ClickHouse is installed.

## Accessing public buckets[​](#accessing-public-buckets "Direct link to Accessing public buckets")

ClickHouse tries to fetch credentials from many different types of sources.
Sometimes, it can produce problems when accessing some buckets that are public causing the client to return `403` error code.
This issue can be avoided by using `NOSIGN` keyword, forcing the client to ignore all the credentials, and not sign the requests.

```
CREATE TABLE big_table (name String, value UInt32)
    ENGINE = S3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/aapl_stock.csv', NOSIGN, 'CSVWithNames');
```

## Optimizing performance[​](#optimizing-performance "Direct link to Optimizing performance")

For details on optimizing the performance of the s3 function see [our detailed guide](/docs/integrations/s3/performance).

## Role-based access[​](#role-based-access "Direct link to Role-based access")

In ClickHouse Cloud, you can use role-based access to authenticate with S3 instead of using access keys. See [Secure S3](/docs/cloud/data-sources/secure-s3) for configuration steps.

Once configured, a `roleARN` can be passed via an `extra_credentials` parameter:

```
CREATE TABLE my_s3_table(name String, value UInt32)
ENGINE = S3('https://my-bucket.s3.amazonaws.com/data/*.csv', extra_credentials(role_arn = 'arn:aws:iam::111111111111:role/ClickHouseAccessRole-001'), 'CSV')
```

## See also[​](#see-also "Direct link to See also")

- [s3 table function](/docs/sql-reference/table-functions/s3)
- [Integrating S3 with ClickHouse](/docs/integrations/s3)

- [Example](#example)- [Create a table](#creating-a-table)
    - [Engine parameters](#parameters)- [Data cache](#data-cache)- [PARTITION BY](#partition-by)- [Querying partitioned data](#querying-partitioned-data)- [Insert data](#inserting-data)- [Virtual columns](#virtual-columns)- [Implementation details](#implementation-details)- [Wildcards in path](#wildcards-in-path)- [Storage settings](#storage-settings)- [S3-related settings](#settings)- [Endpoint-based settings](#endpoint-settings)- [Working with archives](#working-with-archives)- [Accessing public buckets](#accessing-public-buckets)- [Optimizing performance](#optimizing-performance)- [Role-based access](#role-based-access)- [See also](#see-also)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/engines/table-engines/integrations/sqlite.md)

Not supported in ClickHouse Cloud

The engine allows to import and export data to SQLite and supports queries to SQLite tables directly from ClickHouse.

## Creating a table[​](#creating-a-table "Direct link to Creating a table")

```
    CREATE TABLE [IF NOT EXISTS] [db.]table_name
    (
        name1 [type1],
        name2 [type2], ...
    ) ENGINE = SQLite('db_path', 'table')
```

**Engine Parameters**

- `db_path` — Path to SQLite file with a database.
- `table` — Name of a table in the SQLite database.

## Data types support[​](#data-types-support "Direct link to Data types support")

When you explicitly specify ClickHouse column types in the table definition, the following ClickHouse types can be parsed from SQLite TEXT columns:

- [Date](/docs/sql-reference/data-types/date), [Date32](/docs/sql-reference/data-types/date32)
- [DateTime](/docs/sql-reference/data-types/datetime), [DateTime64](/docs/sql-reference/data-types/datetime64)
- [UUID](/docs/sql-reference/data-types/uuid)
- [Enum8, Enum16](/docs/sql-reference/data-types/enum)
- [Decimal32, Decimal64, Decimal128, Decimal256](/docs/sql-reference/data-types/decimal)
- [FixedString](/docs/sql-reference/data-types/fixedstring)
- All integer types ([UInt8, UInt16, UInt32, UInt64, Int8, Int16, Int32, Int64](/docs/sql-reference/data-types/int-uint))
- [Float32, Float64](/docs/sql-reference/data-types/float)

See [SQLite database engine](/docs/engines/database-engines/sqlite#data_types-support) for the default type mapping.

## Usage example[​](#usage-example "Direct link to Usage example")

Shows a query creating the SQLite table:

```
SHOW CREATE TABLE sqlite_db.table2;
```

```
CREATE TABLE SQLite.table2
(
    `col1` Nullable(Int32),
    `col2` Nullable(String)
)
ENGINE = SQLite('sqlite.db','table2');
```

Returns the data from the table:

```
SELECT * FROM sqlite_db.table2 ORDER BY col1;
```

```
┌─col1─┬─col2──┐
│    1 │ text1 │
│    2 │ text2 │
│    3 │ text3 │
└──────┴───────┘
```

**See Also**

- [SQLite](/docs/engines/database-engines/sqlite) engine
- [sqlite](/docs/sql-reference/table-functions/sqlite) table function

- [Creating a table](#creating-a-table)- [Data types support](#data-types-support)- [Usage example](#usage-example)

Was this page helpful?