ClickHouse/clickhouse-docs-embeddings huggingface dataset:
Working with CSV and TSV data in ClickHouse

ClickHouse introduces support for many formats, both text, and binary, to cover various scenarios and platforms. Explore more formats and ways to work with them in the following articles:

- **CSV and TSV formats**
- [Parquet](/docs/integrations/data-formats/parquet)
- [JSON formats](/docs/integrations/data-formats/json/overview)
- [Regex and templates](/docs/integrations/data-formats/templates-regexp)
- [Native and binary formats](/docs/integrations/data-formats/binary-native)
- [SQL formats](/docs/integrations/data-formats/sql)

And also check [clickhouse-local](https://clickhouse.com/blog/extracting-converting-querying-local-files-with-sql-clickhouse-local) - a portable full-featured tool to work on local/remote files without the need for Clickhouse server.

- [Importing data from a CSV file](#importing-data-from-a-csv-file)
  - [CSV files with headers](#csv-files-with-headers)- [CSV files with custom delimiters](#csv-files-with-custom-delimiters)- [Skipping lines in a CSV file](#skipping-lines-in-a-csv-file)- [Treating NULL values in CSV files](#treating-null-values-in-csv-files)- [TSV (tab-separated) files](#tsv-tab-separated-files)
    - [Raw TSV](#raw-tsv)- [Exporting to CSV](#exporting-to-csv)
      - [Saving exported data to a CSV file](#saving-exported-data-to-a-csv-file)- [Exporting CSV with custom delimiters](#exporting-csv-with-custom-delimiters)- [Exporting CSV for Windows](#exporting-csv-for-windows)- [Schema inference for CSV files](#schema-inference-for-csv-files)
        - [Exporting and importing CSV with explicit column types](#exporting-and-importing-csv-with-explicit-column-types)- [Custom delimiters, separators, and escaping rules](#custom-delimiters-separators-and-escaping-rules)- [Working with large CSV files](#working-with-large-csv-files)- [Other formats](#other-formats)

Was this page helpful?
Loading JSON

SELECT *
FROM pypi_with_tags
LIMIT 2

┌───────date─┬─country_code─┬─project────────────┬─type──┬─installer────┬─python_minor─┬─system─┬─version─┬─tags─────────────────────────────────────────────────────┐
│ 2022-05-26 │ CN           │ clickhouse-connect │ sdist │ bandersnatch │              │        │ 0.0.7 │ {"nsBM":"5194603446944555691"}                           │
│ 2022-05-26 │ CN           │ clickhouse-connect │ sdist │ bandersnatch │              │        │ 0.0.7 │ {"4zD5MYQz4JkP1QqsJIS":"0","name":"8881321089124243208"} │
└────────────┴──────────────┴────────────────────┴───────┴──────────────┴──────────────┴────────┴─────────┴──────────────────────────────────────────────────────────┘

2 rows in set. Elapsed: 0.149 sec.
```

Notice the performance difference here on loading data. The JSON column requires type inference at insert time as well as additional storage if columns exist that have more than one type. Although the JSON type can be configured (see [Designing JSON schema](/docs/integrations/data-formats/json/schema)) for equivalent performance to explicitly declaring columns, it is intentionally flexible out-of-the-box. This flexibility, however, comes at some cost.

### When to use the JSON type[​](#when-to-use-the-json-type "Direct link to When to use the JSON type")

Use the JSON type when your data:

- Has **unpredictable keys** that can change over time.
- Contains **values with varying types** (e.g., a path might sometimes contain a string, sometimes a number).
- Requires schema flexibility where strict typing isn't viable.

If your data structure is known and consistent, there is rarely a need for the JSON type, even if your data is in JSON format. Specifically, if your data has:

- **A flat structure with known keys**: use standard column types e.g. String.
- **Predictable nesting**: use Tuple, Array, or Nested types for these structures.
- **Predictable structure with varying types**: consider Dynamic or Variant types instead.

You can also mix approaches as we have done in the above example, using static columns for predictable top-level keys and a single JSON column for a dynamic section of the payload.

- [Loading structured JSON](#loading-structured-json)- [Loading semi-structured JSON](#loading-semi-structured-json)
    - [When to use the JSON type](#when-to-use-the-json-type)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/data-formats/json/intro.md)

ClickHouse provides several approaches for handling JSON, each with its respective pros and cons and usage. In this guide, we will cover how to load JSON and design your schema optimally. This guide consists of the following sections:

- [Loading JSON](/docs/integrations/data-formats/json/loading) - Loading and querying structured and semi-structured JSON in ClickHouse with simple schemas.
- [JSON schema inference](/docs/integrations/data-formats/json/inference) - Using JSON schema inference to query JSON and create table schemas.
- [Designing JSON schema](/docs/integrations/data-formats/json/schema) - Steps to design and optimize your JSON schema.
- [Exporting JSON](/docs/integrations/data-formats/json/exporting) - How to export JSON.
- [Handling other JSON Formats](/docs/integrations/data-formats/json/other-formats) - Some tips on handling JSON formats other than newline-delimited (NDJSON).
- [Other approaches to modeling JSON](/docs/integrations/data-formats/json/other-approaches) - Legacy approaches to modeling JSON. **Not recommended.**

Was this page helpful?
Importing and exporting custom text data using Templates and Regex in ClickHouse

```
CREATE TABLE error_log
(
    `time` DateTime,
    `ip` String,
    `host` String,
    `file` String,
    `protocol` String
)
ENGINE = MergeTree
ORDER BY (host, file, time)
```

Now we can import data based on a regular expression:

```
INSERT INTO error_log FROM INFILE 'error.log'
SETTINGS
  format_regexp = '(.+?) \\[error\\]  client: (.+), server: (.+?) "GET .+?([^/]+\\.[^ ]+) (.+?)"'
FORMAT Regexp
```

ClickHouse will insert data from each capture group into the relevant column based on its order. Let's check the data:

```
SELECT * FROM error_log LIMIT 5
```

```
┌────────────────time─┬─ip──────┬─host────────┬─file─────────────────────────┬─protocol─┐
│ 2023-01-15 13:00:01 │ 3.5.9.2 │ example.com │ apple-touch-icon-120x120.png │ HTTP/1.1 │
│ 2023-01-15 13:01:40 │ 3.7.2.5 │ example.com │ apple-touch-icon-120x120.png │ HTTP/1.1 │
│ 2023-01-15 13:16:49 │ 9.2.9.2 │ example.com │ apple-touch-icon-120x120.png │ HTTP/1.1 │
│ 2023-01-15 13:21:38 │ 8.8.5.3 │ example.com │ apple-touch-icon-120x120.png │ HTTP/1.1 │
│ 2023-01-15 13:31:27 │ 9.5.8.4 │ example.com │ apple-touch-icon-120x120.png │ HTTP/1.1 │
└─────────────────────┴─────────┴─────────────┴──────────────────────────────┴──────────┘
```

By default, ClickHouse will raise an error in case of unmatched rows. If you want to skip unmatched rows instead, enable it using [format\_regexp\_skip\_unmatched](/docs/operations/settings/formats#format_regexp_skip_unmatched) option:

```
SET format_regexp_skip_unmatched = 1;
```

## Other formats[​](#other-formats "Direct link to Other formats")

ClickHouse introduces support for many formats, both text, and binary, to cover various scenarios and platforms. Explore more formats and ways to work with them in the following articles:

- [CSV and TSV formats](/docs/integrations/data-formats/csv-tsv)
- [Parquet](/docs/integrations/data-formats/parquet)
- [JSON formats](/docs/integrations/data-formats/json/overview)
- **Regex and templates**
- [Native and binary formats](/docs/integrations/data-formats/binary-native)
- [SQL formats](/docs/integrations/data-formats/sql)

And also check [clickhouse-local](https://clickhouse.com/blog/extracting-converting-querying-local-files-with-sql-clickhouse-local) - a portable full-featured tool to work on local/remote files without the need for Clickhouse server.

- [Importing based on a template](#importing-based-on-a-template)
  - [Skipping whitespaces](#skipping-whitespaces)- [Exporting data using templates](#exporting-data-using-templates)
    - [Exporting to HTML files](#exporting-to-html-files)- [Exporting to XML](#exporting-to-xml)- [Importing data based on regular expressions](#importing-data-based-on-regular-expressions)- [Other formats](#other-formats)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/insert-local-files.md)

You can use `clickhouse-client` to stream local files into your ClickHouse service. This allows you the ability to preprocess
the data using the many powerful and convenient ClickHouse functions. Let's look at an example...

1. Suppose we have a TSV file named `comments.tsv` that contains some Hacker News comments, and the header row contains column names. You need to specify an [input format](/docs/interfaces/formats) when you insert the data, which in our case is `TabSeparatedWithNames`:
title_wip

2. Let's create the table for our Hacker News data:

```
CREATE TABLE hackernews (
    id UInt32,
    type String,
    author String,
    timestamp DateTime,
    comment String,
    children Array(UInt32),
    tokens Array(String)
)
ENGINE = MergeTree
ORDER BY toYYYYMMDD(timestamp)
```

3. We want to lowercase the `author` column, which is easily done with the [`lower` function](/docs/sql-reference/functions/string-functions#lower). We also want to split the `comment` string into tokens and store the result in the `tokens` column, which can be done using the [`extractAll` function](/docs/sql-reference/functions/string-search-functions#extractAll). You do all of this in one `clickhouse-client` command - notice how the `comments.tsv` file is piped into the `clickhouse-client` using the `<` operator:

```
clickhouse-client \
    --host avw5r4qs3y.us-east-2.aws.clickhouse.cloud \
    --secure \
    --port 9440 \
    --password Myp@ssw0rd \
    --query "
    INSERT INTO hackernews
    SELECT
        id,
                type,
                lower(author),
                timestamp,
                comment,
                children,
                extractAll(comment, '\\w+') as tokens
    FROM input('id UInt32, type String, author String, timestamp DateTime, comment String, children Array(UInt32)')
    FORMAT TabSeparatedWithNames
" < comments.tsv
```

Note

The `input` function is useful here as it allows us to convert the data as it's being inserted into the `hackernews` table. The argument to `input` is the format of the incoming raw data, and you will see this in many of the other table functions (where you specify a schema for the incoming data).

4. That's it! The data is up in ClickHouse:

```
SELECT *
FROM hackernews
LIMIT 7
```

The result is:
title_wip

│ 4568 │ comment │ jwecker      │ 2007-03-16 13:08:04 │ I know the feeling.  The same feeling I had back when people were still writing java applets.  Maybe a normal user doesn't feel it- maybe it's the programmer in us knowing that there's a big layer running between me and the browser...                 │ []       │ ['I','know','the','feeling','The','same','feeling','I','had','back','when','people','were','still','writing','java','applets','Maybe','a','normal','user','doesn','t','feel','it','maybe','it','s','the','programmer','in','us','knowing','that','there','s','a','big','layer','running','between','me','and','the','browser'] │
│ 4900 │ comment │ lupin_sansei │ 2007-03-19 00:26:30 │ "The essence of Ajax is getting Javascript to communicate with the server without reloading the page. Although XmlHttpRequest is most convenient, there were other methods of doing this before XmlHttpRequest such as <p>- loading a 1 pixel image and sending data in the image's cookie<p>- loading server data through a tiny frame which contained XML or javascipt data<p>- Using a java applet to fetch the data on behalf of javascript" │ []       │ ['The','essence','of','Ajax','is','getting','Javascript','to','communicate','with','the','server','without','reloading','the','page','Although','XmlHttpRequest','is','most','convenient','there','were','other','methods','of','doing','this','before','XmlHttpRequest','such','as','p','loading','a','1','pixel','image','and','sending','data','in','the','image','s','cookie','p','loading','server','data','through','a','tiny','frame','which','contained','XML','or','javascipt','data','p','Using','a','java','applet','to','fetch','the','data','on','behalf','of','javascript'] │
│ 5102 │ comment │ staunch      │ 2007-03-20 02:42:47 │ "Well this is exactly the kind of thing that isn't very obvious. It sounds like once you're wealthy there's a new set of rules you have to live by. It's a shame everyone has had to re-learn these things for themselves because a few bad apples can control their jealousy.<p>Very good to hear it's somewhere in your essay queue though. I'll try not to get rich before you write it, so I have some idea of what to expect :-)" │ []       │ ['Well','this','is','exactly','the','kind','of','thing','that','isn','t','very','obvious','It','sounds','like','once','you','re','wealthy','there','s','a','new','set','of','rules','you','have','to','live','by','It','s','a','shame','everyone','has','had','to','re','learn','these','things','for','themselves','because','a','few','bad','apples','can','control','their','jealousy','p','Very','good','to','hear','it','s','somewhere','in','your','essay','queue','though','I','ll','try','not','to','get','rich','before','you','write','it','so','I','have','some','idea','of','what','to','expect'] │
```
title_wip

5. Another option is to use a tool like `cat` to stream the file to `clickhouse-client`. For example, the following command has the same result as using the `<` operator:

```
cat comments.tsv | clickhouse-client \
    --host avw5r4qs3y.us-east-2.aws.clickhouse.cloud \
    --secure \
    --port 9440 \
    --password Myp@ssw0rd \
    --query "
    INSERT INTO hackernews
    SELECT
        id,
                type,
                lower(author),
                timestamp,
                comment,
                children,
                extractAll(comment, '\\w+') as tokens
    FROM input('id UInt32, type String, author String, timestamp DateTime, comment String, children Array(UInt32)')
    FORMAT TabSeparatedWithNames
"
```

Visit the [docs page on `clickhouse-client`](/docs/interfaces/cli) for details on how to install `clickhouse-client` on your local operating system.

Was this page helpful?
title_wip

```
SELECT * FROM first_replicated_table LIMIT 10;
```

```
SELECT * FROM second_replicated_table LIMIT 10;
```

### Note[​](#note "Direct link to Note")

In this guide, the data ingested in both Kafka topics is the same. In your case, they would differ. You can add as many Kafka clusters as you want.

Example output:

```
┌─id─┬─first_name─┬─last_name─┐
│  0 │ FirstName0 │ LastName0 │
│  1 │ FirstName1 │ LastName1 │
│  2 │ FirstName2 │ LastName2 │
└────┴────────────┴───────────┘
```

This completes the setup for integrating ClickHouse with Kafka using named collections. By centralizing Kafka configurations in the ClickHouse `config.xml` file, you can manage and adjust settings more easily, ensuring a streamlined and efficient integration.

- [Introduction](#introduction)- [Assumptions](#assumptions)- [Prerequisites](#prerequisites)- [Configuration](#configuration)
        - [Configuration notes](#configuration-notes)- [Creating tables and databases](#creating-tables-and-databases)
          - [Create the database](#create-the-database)- [Create Kafka tables](#create-kafka-tables)- [Create replicated tables](#create-replicated-tables)- [Create materialized views](#create-materialized-views)- [Verifying the setup](#verifying-the-setup)
            - [Note](#note)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/data-sources-index.md)

ClickHouse allows you to easily ingest data into your database from a variety of sources.
For further information see the pages listed below:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Data Source|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [AWS S3](/docs/integrations/s3)| [PostgreSQL](/docs/integrations/postgresql)| [Kafka](/docs/integrations/kafka)| [MySQL](/docs/integrations/mysql)| [Cassandra](/docs/integrations/cassandra)| [Redis](/docs/sql-reference/table-functions/redis)| [RabbitMQ](/docs/engines/table-engines/integrations/rabbitmq)| [MongoDB](/docs/engines/table-engines/integrations/mongodb)| [Google Cloud Storage (GCS)](/docs/integrations/gcs)| [Hive](/docs/engines/table-engines/integrations/hive)| [Hudi](/docs/engines/table-engines/integrations/hudi)| [Iceberg](/docs/sql-reference/table-functions/iceberg)| [MinIO](/docs/integrations/minio)| [Delta Lake](/docs/sql-reference/table-functions/deltalake)| [RocksDB](/docs/engines/table-engines/integrations/embedded-rocksdb)| [Splunk](/docs/integrations/splunk)| [SQLite](/docs/engines/table-engines/integrations/sqlite)| [NATS](/docs/engines/table-engines/integrations/nats)| [EMQX](/docs/integrations/emqx)| [Insert Local Files](/docs/integrations/data-ingestion/insert-local-files)| [JDBC](/docs/integrations/jdbc/jdbc-with-clickhouse)| [ODBC](/docs/integrations/data-ingestion/dbms/odbc-with-clickhouse) | | | | | | | | | | | | | | | | | | | | | | |

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-visualization/index.md)

Now that your data is in ClickHouse, it's time to analyze it, which often involves building visualizations using a BI tool. Many of the popular BI and visualization tools connect to ClickHouse. Some connect to ClickHouse out-of-the-box, while others require a connector to be installed. We have docs for some of the tools, including:

- [Apache Superset](/docs/integrations/superset)
- [Astrato](/docs/integrations/astrato)
- [Chartbrew](/docs/integrations/chartbrew-and-clickhouse)
- [Databrain](/docs/integrations/databrain)
- [Deepnote](/docs/integrations/deepnote)
- [Dot](/docs/integrations/dot)
- [Draxlr](/docs/integrations/draxlr)
- [Embeddable](/docs/integrations/embeddable)
- [Explo](/docs/integrations/explo)
- [Fabi.ai](/docs/integrations/fabi.ai)
- [Grafana](/docs/integrations/grafana)
- [Lightdash](/docs/integrations/lightdash)
- [Looker](/docs/integrations/looker)
- [Luzmo](/docs/integrations/luzmo)
- [Metabase](/docs/integrations/metabase)
- [Mitzu](/docs/integrations/mitzu)
- [Omni](/docs/integrations/omni)
- [Querio](/docs/integrations/querio)
- [Rill](https://docs.rilldata.com/reference/olap-engines/clickhouse)
- [Rocket BI](/docs/integrations/rocketbi)
- [Tableau](/docs/integrations/tableau)
- [Zing Data](/docs/integrations/zingdata)
- [Holistics BI](/docs/integrations/holistics)

## ClickHouse Cloud compatibility with data visualization tools[​](#clickhouse-cloud-compatibility-with-data-visualization-tools "Direct link to ClickHouse Cloud compatibility with data visualization tools")
title_wip

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
Connecting Databrain to ClickHouse

## Learn more[​](#learn-more "Direct link to Learn more")

For more information about Databrain features and how to build powerful analytics:

- [Databrain Documentation](https://docs.usedatabrain.com/)
- [ClickHouse Integration Guide](https://docs.usedatabrain.com/guides/datasources/connecting-data-sources-to-databrain/clickhouse)
- [Creating Dashboards](https://docs.usedatabrain.com/guides/dashboards/create-a-dashboard)
- [Building Metrics](https://docs.usedatabrain.com/guides/metrics/create-metrics)

- [Pre-requisites](#pre-requisites)- [Steps to connect Databrain to ClickHouse](#steps-to-connect-databrain-to-clickhouse)
    - [1. Gather your connection details](#1-gather-your-connection-details)- [2. Allow Databrain IP addresses (if required)](#2-allow-databrain-ip-addresses)- [3. Add ClickHouse as a data source in Databrain](#3-add-clickhouse-as-a-data-source)- [4. Configure user permissions](#4-configure-user-permissions)- [Using Databrain with ClickHouse](#using-databrain-with-clickhouse)
      - [Explore your data](#explore-your-data)- [Create metrics and visualizations](#create-metrics-and-visualizations)- [Build dashboards](#build-dashboards)- [Advanced features](#advanced-features)- [Troubleshooting](#troubleshooting)
        - [Connection fails](#connection-fails)- [Slow query performance](#slow-query-performance)- [Learn more](#learn-more)

Was this page helpful?