Insert Local Files

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 when you insert the data, which in our case is TabSeparatedWithNames:

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

3. We want to lowercase the author column, which is easily done with the lower function. 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. 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:

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:

The result is:

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:

Visit the docs page on clickhouse-client for details on how to install clickhouse-client on your local operating system.