ClickHouse/clickhouse-docs-embeddings huggingface dataset:
Loading data from BigQuery to ClickHouse

Alternatively, you can `SET input_format_null_as_default=1` and any missing or NULL values will be replaced by default values for their respective columns, if those defaults are specified.

Testing successful data export

## Testing successful data export[​](#3-testing-successful-data-export "Direct link to Testing successful data export")

To test whether your data was properly inserted, simply run a `SELECT` query on your new table:

```
SELECT * FROM mytable LIMIT 10;
```

To export more BigQuery tables, simply redo the steps above for each additional table.

## Further reading and support[​](#further-reading-and-support "Direct link to Further reading and support")

In addition to this guide, we also recommend reading our blog post that shows [how to use ClickHouse to speed up BigQuery and how to handle incremental imports](https://clickhouse.com/blog/clickhouse-bigquery-migrating-data-for-realtime-queries).

If you're having issues transferring data from BigQuery to ClickHouse, please feel free to contact us at [[email protected]](/cdn-cgi/l/email-protection#d6a5a3a6a6b9a4a296b5babfb5bdbeb9a3a5b3f8b5b9bb).

- [How long will exporting data to ClickHouse take?](#how-long-will-exporting-data-to-clickhouse-take)- [Export table data to GCS](#1-export-table-data-to-gcs)- [Importing data into ClickHouse from GCS](#2-importing-data-into-clickhouse-from-gcs)- [Testing successful data export](#3-testing-successful-data-export)- [Further reading and support](#further-reading-and-support)

Was this page helpful?
How to Test Your Hardware with ClickHouse

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/performance-test.md)

Not supported in ClickHouse Cloud

Note

This page isn't applicable to [ClickHouse Cloud](https://clickhouse.com/cloud). The feature documented here isn't available in ClickHouse Cloud services.
See the ClickHouse [Cloud Compatibility](/docs/whats-new/cloud-compatibility) guide for more information.

You can run a basic ClickHouse performance test on any server without installation of ClickHouse packages.

## Automated run[​](#automated-run "Direct link to Automated run")

You can run the benchmark with a single script.

1. Download the script.

```
wget https://raw.githubusercontent.com/ClickHouse/ClickBench/main/hardware/hardware.sh
```

2. Run the script.

```
chmod a+x ./hardware.sh
./hardware.sh
```

3. Copy the output and send it to [[email protected]](/cdn-cgi/l/email-protection#ec8a8989888e8d8f87ac8f80858f878483999f89c28f8381)

All the results are published here: <https://clickhouse.com/benchmark/hardware/>

- [Automated run](#automated-run)

Was this page helpful?
Server Settings

Note

The amount of data in mapped files does not consume memory directly and is not accounted for in query or server memory usage — because this memory can be discarded similar to the OS page cache. The cache is dropped (the files are closed) automatically on the removal of old parts in tables of the MergeTree family, also it can be dropped manually by the `SYSTEM DROP MMAP CACHE` query.

This setting can be modified at runtime and will take effect immediately.

## mutation\_workload[​](#mutation_workload "Direct link to mutation_workload")

Used to regulate how resources are utilized and shared between mutations and other workloads. Specified value is used as `workload` setting value for all background mutations. Can be overridden by a merge tree setting.

**See Also**

- [Workload Scheduling](/docs/operations/workload-scheduling)

## mysql\_port[​](#mysql_port "Direct link to mysql_port")

Port for communicating with clients over MySQL protocol.

Note

- Positive integers specify the port number to listen to
- Empty values are used to disable communication with clients over MySQL protocol.

**Example**

```
<mysql_port>9004</mysql_port>
```

## mysql\_require\_secure\_transport[​](#mysql_require_secure_transport "Direct link to mysql_require_secure_transport")

If set to true, secure communication is required with clients over [mysql\_port](/docs/operations/server-configuration-parameters/settings#mysql_port). Connection with option `<--ssl-mode=none>` will be refused. Use it with [OpenSSL](/docs/operations/server-configuration-parameters/settings#openssl) settings.

## oom\_score[​](#oom_score "Direct link to oom_score")

On Linux systems this can control the behavior of OOM killer.

## openSSL[​](#openssl "Direct link to openSSL")

SSL client/server configuration.

Support for SSL is provided by the `libpoco` library. The available configuration options are explained in [SSLManager.h](https://github.com/ClickHouse-Extras/poco/blob/master/NetSSL_OpenSSL/include/Poco/Net/SSLManager.h). Default values can be found in [SSLManager.cpp](https://github.com/ClickHouse-Extras/poco/blob/master/NetSSL_OpenSSL/src/SSLManager.cpp).

Keys for server/client settings:
TCP connection limits

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/settings/tcp-connection-limits.md)

## Overview[​](#overview "Direct link to Overview")

You may have a ClickHouse TCP connection (i.e., one through the [command-line client](https://clickhouse.com/docs/interfaces/client))
disconnect automatically after some number of queries or duration.
After disconnecting, no automatic reconnection occurs (unless triggered through something else,
such as sending another query in the command-line client).

Connection limits are enabled by setting the server settings
`tcp_close_connection_after_queries_num` (for the query limit)
or `tcp_close_connection_after_queries_seconds` (for the duration limit) to greater than 0.
If both limits are enabled, the connection closes when either limit is hit first.

Upon hitting a limit and disconnecting, the client receives a
`TCP_CONNECTION_LIMIT_REACHED` exception, and **the query that causes the disconnect is never processed**.

## Query limits[​](#query-limits "Direct link to Query limits")

Assuming `tcp_close_connection_after_queries_num` is set to N, then the connection allows
N successful queries. Then on query N + 1, the client disconnects.

Every query processed counts towards the query limit. So when connecting a command-line client,
there may be an automatic initial system warnings query which counts towards the limit.

When a TCP connection is idle (i.e., has not processed queries for some duration of time,
specified by the session setting `poll_interval`), the number of queries counted so far resets to 0.
This means the number of total queries in a single connection can exceed
`tcp_close_connection_after_queries_num` if idle occurs.

## Duration limits[​](#duration-limits "Direct link to Duration limits")

The connection duration is measured starting as soon as the client connects.
The client is disconnected on the first query after `tcp_close_connection_after_queries_seconds` seconds has passed.

- [Overview](#overview)- [Query limits](#query-limits)- [Duration limits](#duration-limits)

Was this page helpful?
system.query_log

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/system-tables/query_log.md)

Querying in ClickHouse Cloud

The data in this system table is held locally on each node in ClickHouse Cloud. Obtaining a complete view of all data, therefore, requires the `clusterAllReplicas` function. See [here](/docs/operations/system-tables/overview#system-tables-in-clickhouse-cloud) for further details.

Stores metadata and statistics about executed queries, such as start time, duration, error messages, resource usage, and other execution details. It does not store the results of queries.

You can change settings of queries logging in the [query\_log](/docs/operations/server-configuration-parameters/settings#query_log) section of the server configuration.

You can disable queries logging by setting [log\_queries = 0](/docs/operations/settings/settings#log_queries). We do not recommend to turn off logging because information in this table is important for solving issues.

The flushing period of data is set in `flush_interval_milliseconds` parameter of the [query\_log](/docs/operations/server-configuration-parameters/settings#query_log) server settings section. To force flushing, use the [SYSTEM FLUSH LOGS](/docs/sql-reference/statements/system#flush-logs) query.

ClickHouse does not delete data from the table automatically. See [Introduction](/docs/operations/system-tables/overview#system-tables-introduction) for more details.

The `system.query_log` table registers two kinds of queries:

1. Initial queries that were run directly by the client.
2. Child queries that were initiated by other queries (for distributed query execution). For these types of queries, information about the parent queries is shown in the `initial_*` columns.

Each query creates one or two rows in the `query_log` table, depending on the status (see the `type` column) of the query:

1. If the query execution was successful, two rows with the `QueryStart` and `QueryFinish` types are created.
2. If an error occurred during query processing, two events with the `QueryStart` and `ExceptionWhileProcessing` types are created.
3. If an error occurred before launching the query, a single event with the `ExceptionBeforeStart` type is created.

You can use the [log\_queries\_probability](/docs/operations/settings/settings#log_queries_probability) setting to reduce the number of queries, registered in the `query_log` table.

You can use the [log\_formatted\_queries](/docs/operations/settings/settings#log_formatted_queries) setting to log formatted queries to the `formatted_query` column.

## Columns[​](#columns "Direct link to Columns")
system.query_thread_log

**Example**

```
 SELECT * FROM system.query_thread_log LIMIT 1 \G
```

```
Row 1:
──────
hostname:                      clickhouse.eu-central1.internal
event_date:                    2020-09-11
event_time:                    2020-09-11 10:08:17
event_time_microseconds:       2020-09-11 10:08:17.134042
query_start_time:              2020-09-11 10:08:17
query_start_time_microseconds: 2020-09-11 10:08:17.063150
query_duration_ms:             70
read_rows:                     0
read_bytes:                    0
written_rows:                  1
written_bytes:                 12
memory_usage:                  4300844
peak_memory_usage:             4300844
thread_name:                   TCPHandler
thread_id:                     638133
master_thread_id:              638133
query:                         INSERT INTO test1 VALUES
is_initial_query:              1
user:                          default
query_id:                      50a320fd-85a8-49b8-8761-98a86bcbacef
address:                       ::ffff:127.0.0.1
port:                          33452
initial_user:                  default
initial_query_id:              50a320fd-85a8-49b8-8761-98a86bcbacef
initial_address:               ::ffff:127.0.0.1
initial_port:                  33452
interface:                     1
os_user:                       bharatnc
client_hostname:               tower
client_name:                   ClickHouse
client_revision:               54437
client_version_major:          20
client_version_minor:          7
client_version_patch:          2
http_method:                   0
http_user_agent:
quota_key:
revision:                      54440
ProfileEvents:        {'Query':1,'SelectQuery':1,'ReadCompressedBytes':36,'CompressedReadBufferBlocks':1,'CompressedReadBufferBytes':10,'IOBufferAllocs':1,'IOBufferAllocBytes':89,'ContextLock':15,'RWLockAcquiredReadLocks':1}
```

**See Also**

- [system.query\_log](/docs/operations/system-tables/query_log) — Description of the `query_log` system table which contains common information about queries execution.
- [system.query\_views\_log](/docs/operations/system-tables/query_views_log) — This table contains information about each view executed during a query.

Was this page helpful?
system.quotas

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/system-tables/quotas.md)

Contains information about [quotas](/docs/operations/system-tables/quotas).

Columns:

- `name` ([String](/docs/sql-reference/data-types/string)) — Quota name.
- `id` ([UUID](/docs/sql-reference/data-types/uuid)) — Quota ID.
- `storage`([String](/docs/sql-reference/data-types/string)) — Storage of quotas. Possible value: "users.xml" if a quota configured in the users.xml file, "disk" if a quota configured by an SQL-query.
- `keys` ([Array](/docs/sql-reference/data-types/array)([Enum8](/docs/sql-reference/data-types/enum))) — Key specifies how the quota should be shared. If two connections use the same quota and key, they share the same amounts of resources. Values:
  - `[]` — All users share the same quota.
  - `['user_name']` — Connections with the same user name share the same quota.
  - `['ip_address']` — Connections from the same IP share the same quota.
  - `['client_key']` — Connections with the same key share the same quota. A key must be explicitly provided by a client. When using [clickhouse-client](/docs/interfaces/client), pass a key value in the `--quota_key` parameter, or use the `quota_key` parameter in the client configuration file. When using HTTP interface, use the `X-ClickHouse-Quota` header.
  - `['user_name', 'client_key']` — Connections with the same `client_key` share the same quota. If a key isn't provided by a client, the quota is tracked for `user_name`.
  - `['client_key', 'ip_address']` — Connections with the same `client_key` share the same quota. If a key isn't provided by a client, the quota is tracked for `ip_address`.
  - `['normalized_query_hash']` — Each distinct normalized query gets its own quota bucket. Queries that differ only in literal values (e.g. `SELECT 1` and `SELECT 2`) share the same bucket.
- `durations` ([Array](/docs/sql-reference/data-types/array)([UInt64](/docs/sql-reference/data-types/int-uint))) — Time interval lengths in seconds.
- `apply_to_all` ([UInt8](/docs/sql-reference/data-types/int-uint#integer-ranges)) — Logical value. It shows which users the quota is applied to. Values:
  - `0` — The quota applies to users specify in the `apply_to_list`.
  - `1` — The quota applies to all users except those listed in `apply_to_except`.
- `apply_to_list` ([Array](/docs/sql-reference/data-types/array)([String](/docs/sql-reference/data-types/string))) — List of user names/[roles](/docs/operations/access-rights#role-management) that the quota should be applied to.
- `apply_to_except` ([Array](/docs/sql-reference/data-types/array)([String](/docs/sql-reference/data-types/string))) — List of user names/roles that the quota should not apply to.

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

- [SHOW QUOTAS](/docs/sql-reference/statements/show#show-quotas)

- [See Also](#see-also)

Was this page helpful?
DateTime

## Handling daylight saving time (DST)[​](#handling-daylight-saving-time-dst "Direct link to Handling daylight saving time (DST)")

ClickHouse's DateTime type with time zones can exhibit unexpected behavior during Daylight Saving Time (DST) transitions, particularly when:

- [`date_time_output_format`](/docs/operations/settings/formats#date_time_output_format) is set to `simple`.
- Clocks move backward ("Fall Back"), causing a one-hour overlap.
- Clocks move forward ("Spring Forward"), causing a one-hour gap.

By default, ClickHouse always picks the earlier occurrence of an overlapping time and may interpret nonexistent times during forward shifts.

For example, consider the following transition from Daylight Saving Time (DST) to Standard Time.

- On October 29, 2023, at 02:00:00, clocks move backward to 01:00:00 (BST → GMT).
- The hour 01:00:00 – 01:59:59 appears twice (once in BST and once in GMT)
- ClickHouse always picks the first occurrence (BST), causing unexpected results when adding time intervals.

```
SELECT '2023-10-29 01:30:00'::DateTime('Europe/London') AS time, time + toIntervalHour(1) AS one_hour_later

┌────────────────time─┬──────one_hour_later─┐
│ 2023-10-29 01:30:00 │ 2023-10-29 01:30:00 │
└─────────────────────┴─────────────────────┘
```

Similarly, during the transition from Standard Time to Daylight Saving Time, an hour can appear to be skipped.

For example:

- On March 26, 2023, at `00:59:59`, clocks jump forward to 02:00:00 (GMT → BST).
- The hour `01:00:00` – `01:59:59` does not exist.

```
SELECT '2023-03-26 01:30:00'::DateTime('Europe/London') AS time, time + toIntervalHour(1) AS one_hour_later

┌────────────────time─┬──────one_hour_later─┐
│ 2023-03-26 00:30:00 │ 2023-03-26 02:30:00 │
└─────────────────────┴─────────────────────┘
```

In this case, ClickHouse shifts the non-existent time `2023-03-26 01:30:00` back to `2023-03-26 00:30:00`.

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

- [Type conversion functions](/docs/sql-reference/functions/type-conversion-functions)
- [Functions for working with dates and times](/docs/sql-reference/functions/date-time-functions)
- [Functions for working with arrays](/docs/sql-reference/functions/array-functions)
- [The `date_time_input_format` setting](/docs/operations/settings/formats#date_time_input_format)
- [The `date_time_output_format` setting](/docs/operations/settings/formats#date_time_output_format)
- [The `timezone` server configuration parameter](/docs/operations/server-configuration-parameters/settings#timezone)
- [The `session_timezone` setting](/docs/operations/settings/settings#session_timezone)
- [Operators for working with dates and times](/docs/sql-reference/operators#operators-for-working-with-dates-and-times)
- [The `Date` data type](/docs/sql-reference/data-types/date)

- [Speed](#speed)- [Usage Remarks](#usage-remarks)- [Examples](#examples)- [Limitations on time zones support](#limitations-on-time-zones-support)- [Handling daylight saving time (DST)](#handling-daylight-saving-time-dst)- [See Also](#see-also)

Was this page helpful?
Formats for input and output data

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/formats.mdx)

ClickHouse supports most of the known text and binary data formats. This allows easy integration into almost any working
data pipeline to leverage the benefits of ClickHouse.

## Input formats[​](#input-formats "Direct link to Input formats")

Input formats are used for:

- Parsing data provided to `INSERT` statements
- Performing `SELECT` queries from file-backed tables such as `File`, `URL`, or `HDFS`
- Reading dictionaries

Choosing the right input format is crucial for efficient data ingestion in ClickHouse. With over 70 supported formats,
selecting the most performant option can significantly impact insert speed, CPU and memory usage, and overall system
efficiency. To help navigate these choices, we benchmarked ingestion performance across formats, revealing key takeaways:

- **The [Native](/docs/interfaces/formats/Native) format is the most efficient input format**, offering the best compression, lowest
  resource usage, and minimal server-side processing overhead.
- **Compression is essential** - LZ4 reduces data size with minimal CPU cost, while ZSTD offers higher compression at the
  expense of additional CPU usage.
- **Pre-sorting has a moderate impact**, as ClickHouse already sorts efficiently.
- **Batching significantly improves efficiency** - larger batches reduce insert overhead and improve throughput.

For a deep dive into the results and best practices,
read the full [benchmark analysis](https://www.clickhouse.com/blog/clickhouse-input-format-matchup-which-is-fastest-most-efficient).
For the full test results, explore the [FastFormats](https://fastformats.clickhouse.com/) online dashboard.

## Output formats[​](#output-formats "Direct link to Output formats")

Formats supported for output are used for:

- Arranging the results of a `SELECT` query
- Performing `INSERT` operations into file-backed tables

## Formats overview[​](#formats-overview "Direct link to Formats overview")

The supported formats are:
Functions for working with dates and times

**Arguments**

- `unit` —
  The type of interval to truncate the result. Possible values: `nanosecond` (only DateTime64), `microsecond` (only DateTime64), `millisecond` (only DateTime64), `second`, `minute`, `hour`, `day`, `week`, `month`, `quarter`, `year`.
  [`String`](/docs/sql-reference/data-types/string)
- `datetime` — Date and time. [`Date`](/docs/sql-reference/data-types/date) or [`Date32`](/docs/sql-reference/data-types/date32) or [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)
- `timezone` — Optional. Timezone name for the returned datetime. If not specified, the function uses the timezone of the `datetime` parameter. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the truncated date and time value.

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Unit Argument `datetime` Argument Return Type|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Year, Quarter, Month, Week `Date32` or `DateTime64` or `Date` or `DateTime` [`Date32`](/docs/sql-reference/data-types/date32) or [`Date`](/docs/sql-reference/data-types/date)| Day, Hour, Minute, Second `Date32`, `DateTime64`, `Date`, or `DateTime` [`DateTime64`](/docs/sql-reference/data-types/datetime64) or [`DateTime`](/docs/sql-reference/data-types/datetime)| Millisecond, Microsecond, Any [`DateTime64`](/docs/sql-reference/data-types/datetime64)| Nanosecond with scale 3, 6, or 9 | | | | | | | | | | | | | | |

**Examples**

**Truncate without timezone**

```
SELECT now(), dateTrunc('hour', now());
```

```
┌───────────────now()─┬─dateTrunc('hour', now())──┐
│ 2020-09-28 10:40:45 │       2020-09-28 10:00:00 │
└─────────────────────┴───────────────────────────┘
```

**Truncate with specified timezone**

```
SELECT now(), dateTrunc('hour', now(), 'Asia/Istanbul');
```

```
┌───────────────now()─┬─dateTrunc('hour', now(), 'Asia/Istanbul')──┐
│ 2020-09-28 10:46:26 │                        2020-09-28 13:00:00 │
└─────────────────────┴────────────────────────────────────────────┘
```

## formatDateTime[​](#formatDateTime "Direct link to formatDateTime")

Introduced in: v1.1.0

Formats a date or date with time according to the given format string. `format` is a constant expression, so you cannot have multiple formats for a single result column.

`formatDateTime` uses MySQL datetime format style, refer to the [mysql docs](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format).

The opposite operation of this function is [`parseDateTime`](/docs/sql-reference/functions/type-conversion-functions#parseDateTime).

Using replacement fields, you can define a pattern for the resulting string.
The example column in the table below shows formatting result for `2018-01-02 22:33:44`.

**Replacement fields:**