Inserting ClickHouse data

See [Clients and Drivers](/docs/interfaces/cli) for a full list of available ClickHouse clients and drivers.

### Prefer the native format[​](#prefer-the-native-format "Direct link to Prefer the native format")

ClickHouse supports many [input formats](/docs/interfaces/formats) at insert (and query) time.
This is a significant difference with OLTP databases and makes loading data from external sources much easier - especially when coupled with [table functions](/docs/sql-reference/table-functions) and the ability to load data from files on disk.
These formats are ideal for ad hoc data loading and data engineering tasks.

For applications looking to achieve optimal insert performance, you should insert using the [Native](/docs/interfaces/formats/Native) format.
This is supported by most clients (such as Go and Python) and ensures the server has to do a minimal amount of work since this format is already column-oriented.
By doing so the responsibility for converting data into a column-oriented format is placed on the client side. This is important for scaling inserts efficiently.

Alternatively, you can use [RowBinary format](/docs/interfaces/formats/RowBinary) (as used by the Java client) if a row format is preferred - this is typically easier to write than the Native format.
This is more efficient, in terms of compression, network overhead, and processing on the server, than alternative row formats such as [JSON](/docs/interfaces/formats/JSON).
The [JSONEachRow](/docs/interfaces/formats/JSONEachRow) format can be considered if you have lower write throughput looking to integrate quickly. You should be aware this format will incur a CPU overhead in ClickHouse for parsing.

### Use the HTTP interface[​](#use-the-http-interface "Direct link to Use the HTTP interface")

Unlike many traditional databases, ClickHouse supports an HTTP interface.
You can use this for both inserting and querying data, using any of the above formats.
This is often preferable to ClickHouse's native protocol as it allows traffic to be easily switched with load balancers.
We expect small differences in insert performance with the native protocol, which incurs a little less overhead.
Existing clients use either of these protocols (in some cases both e.g. the Go client).
The native protocol does allow query progress to be easily tracked.

See [HTTP Interface](/docs/interfaces/http) for further details.

## Basic example[​](#basic-example "Direct link to Basic example")

You can use the familiar `INSERT INTO TABLE` command with ClickHouse. Let's insert some data into the table that we created in the start guide ["Creating Tables in ClickHouse"](/docs/guides/creating-tables).

```
INSERT INTO helloworld.my_first_table (user_id, message, timestamp, metric) VALUES
    (101, 'Hello, ClickHouse!',                                 now(),       -1.0    ),
    (102, 'Insert a lot of rows per batch',                     yesterday(), 1.41421 ),
    (102, 'Sort your data based on your commonly-used queries', today(),     2.718   ),
    (101, 'Granules are the smallest chunks of data read',      now() + 5,   3.14159 )
```

To verify that worked, we'll run the following `SELECT` query:

```
SELECT * FROM helloworld.my_first_table
```

Which returns:

```
user_id message                                             timestamp           metric
101         Hello, ClickHouse!                                  2024-11-13 20:01:22     -1
101         Granules are the smallest chunks of data read           2024-11-13 20:01:27 3.14159
102         Insert a lot of rows per batch                          2024-11-12 00:00:00 1.41421
102         Sort your data based on your commonly-used queries  2024-11-13 00:00:00     2.718
```

## Loading data from Postgres[​](#loading-data-from-postgres "Direct link to Loading data from Postgres")

For loading data from Postgres, you can use:

- `ClickPipes`, an ETL tool specifically designed for PostgreSQL database replication. This is available in both:
  - ClickHouse Cloud - available through our [managed ingestion service](/docs/integrations/clickpipes/postgres) in ClickPipes.
  - Self-managed - via the [PeerDB open-source project](https://github.com/PeerDB-io/peerdb).
- The [PostgreSQL table engine](/docs/integrations/postgresql#using-the-postgresql-table-engine) to read data directly as shown in previous examples. Typically appropriate if batch replication based on a known watermark, e.g., timestamp, is sufficient or if it's a one-off migration. This approach can scale to 10's of millions of rows. Users looking to migrate larger datasets should consider multiple requests, each dealing with a chunk of the data. Staging tables can be used for each chunk prior to its partitions being moved to a final table. This allows failed requests to be retried. For further details on this bulk-loading strategy, see here.
- Data can be exported from PostgreSQL in CSV format. This can then be inserted into ClickHouse from either local files or via object storage using table functions.

Need help inserting large datasets?

If you need help inserting large datasets or encounter any errors when importing data into ClickHouse Cloud, please contact us at [[email protected]](/cdn-cgi/l/email-protection#592a2c2929362b2d193a35303a3231362c2a3c773a3634) and we can assist.

## Inserting data from the command line[​](#inserting-data-from-command-line "Direct link to Inserting data from the command line")

**Prerequisites**

- You have [installed](/docs/install) ClickHouse
- `clickhouse-server` is running
- You have access to a terminal with `wget`, `zcat` and `curl`

In this example you'll see how to insert a CSV file into ClickHouse from the command line using clickhouse-client in batch mode. For more information and examples of inserting data via command line using clickhouse-client in batch mode, see ["Batch mode"](/docs/interfaces/client#batch-mode).

We'll be using the [Hacker News dataset](/docs/getting-started/example-datasets/hacker-news) for this example, which contains 28 million rows of Hacker News data.

Download the CSV

### Download the CSV[​](#download-csv "Direct link to Download the CSV")

Run the following command to download a CSV version of the dataset from our public S3 bucket:

```
wget https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.csv.gz
```

At 4.6GB, and 28m rows, this compressed file should take 5-10 minutes to download.

Create the table

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

With `clickhouse-server` running, you can create an empty table with the following schema directly from the command line using `clickhouse-client` in batch mode:

```
clickhouse-client <<'_EOF'
CREATE TABLE hackernews(
    `id` UInt32,
    `deleted` UInt8,
    `type` Enum('story' = 1, 'comment' = 2, 'poll' = 3, 'pollopt' = 4, 'job' = 5),
    `by` LowCardinality(String),
    `time` DateTime,
    `text` String,
    `dead` UInt8,
    `parent` UInt32,
    `poll` UInt32,
    `kids` Array(UInt32),
    `url` String,
    `score` Int32,
    `title` String,
    `parts` Array(UInt32),
    `descendants` Int32
)
ENGINE = MergeTree
ORDER BY id
_EOF
```

If there are no errors, then the table has been successfully created. In the command above single quotes are used around the heredoc delimiter (`_EOF`) to prevent any interpolation. Without single quotes it would be necessary to escape the backticks around the column names.

Insert the data from the command line

### Insert the data from the command line[​](#insert-data-via-cmd "Direct link to Insert the data from the command line")

Next run the command below to insert the data from the file you downloaded earlier into your table:

```
zcat < hacknernews.csv.gz | ./clickhouse client --query "INSERT INTO hackernews FORMAT CSV"
```

As our data is compressed, we need to first decompress the file using a tool like `gzip`, `zcat`, or similar, and then pipe the decompressed data into `clickhouse-client` with the appropriate `INSERT` statement and `FORMAT`.

Note