Use csv-data in clickhouse table

Question

I have a problem when I want to use data from a csv-file in a table I have created. The database I created is called "test" and the table is created as following:

CREATE TABLE testing
(
    `year` Int16, 
    `amount` Int16, 
    `rate` Float32, 
    `number` Int16
)
ENGINE = Log

Ok.

0 rows in set. Elapsed: 0.033 sec.

I created all these columns to be able to cover all the data in the csv-file. I've read through the clickhouse documentation but just can't figure out how to get the data into my database.

I tested to do this:

$ cat test.csv | clickhouse-client \ >-- database =test\ >--query='INSERT test FORMAT CSV'

Code: 62. DB::Exception: Syntax error: failed at position 1 (line 1, col 1): 2010,646,1.00,13
2010,2486,1.00,19
2010,8178,1.00,10
2010,15707,1.00,4
2010,15708,1.00,10
2010,15718,1.00,4
2010,16951,1.00,8
2010,17615,1.00,13
2010. Unrecognized token

Link: https://yadi.sk/d/ijJlmnBjsjBVc

Welcome to Stack Overflow! I'm not sure what your link leads to, but... all relevant information should be contained within your question itself, without requiring someone to visit another site. — David Makogon
You might be right, the link is to the csv-file I'm using. Just some test-data from yandex. — qer
I would suggest including a small sample in your question (certainly doesn't need to be more than a few records for illustration purposes) — David Makogon
Looks like your command line call to clickhouse-client has errors in it. --database =test <= space allowed or not? --query='INSERT test FORMAT CSV' <= missing "INTO" I'm assuming the other characters (e.g. all the \ >) are some artifact of how you typed them into your shell? — David Makogon

Denny Crane Denny Crane · Accepted Answer · 2020-04-22T20:15:18

cat test.csv |clickhouse-client -d test -q 'INSERT into testing FORMAT CSV'

SELECT *
FROM test.testing

┌─year─┬─amount─┬─rate─┬─number─┐
│ 2010 │    646 │    1 │     13 │
│ 2010 │   2486 │    1 │     19 │
│ 2010 │   8178 │    1 │     10 │
│ 2010 │  15707 │    1 │      4 │
│ 2010 │  15708 │    1 │     10 │
│ 2010 │  15718 │    1 │      4 │
│ 2010 │  16951 │    1 │      8 │
│ 2010 │  17615 │    1 │     13 │
│ 2010 │  17616 │    1 │      4 │
│ 2010 │  17617 │    1 │      8 │
│ 2010 │  17618 │    1 │      9 │

Use csv-data in clickhouse table

1 Answers