Clickhouse: You need to add a custom value to the data when importing from CVS. Is it possible?

[bimkos]

I want to add a date (custom) when importing from CSV. I only need to take two values from the CSV: the product and the product number, and add a date to these values (from the CSV). Simple example:

The structure of the database:

CREATE TABLE db.database (
  product String,
  product_num String,
  date Date
) ENGINE = ReplacingMergeTree() PARTITION BY toMonday(date) 
ORDER BY (product, product_num) SETTINGS index_granularity = 8192;

I want to load the values product and product_num from CSV, and date separately for each row. (The date is the same for all rows)

I've already tried something like:

clickhouse-client --format_csv_delimiter="|" --query="INSERT INTO test.csv (product,product_num,date) VALUE (date='2020-01-01') FORMAT CSV" < data.csv

But it doesn't work

Helllpppp

[den-crane]

https://clickhouse.tech/docs/en/sql_reference/table_functions/input/

[bimkos]

Does this help me add a new column without changing the data file? I think this solution will only work if I have more columns in the file than in the table. Can you give an example, please?

[den-crane]

CREATE TABLE db.database (
  product String,
  product_num String,
  date Date
) ENGINE = ReplacingMergeTree() PARTITION BY toMonday(date) 
ORDER BY (product, product_num) SETTINGS index_granularity = 8192;

echo aaaa,bbbb|clickhouse-client --query="INSERT INTO db.database SELECT a, b, '2020-01-01' FROM input('a String, b String') FORMAT CSV";

select * from  db.database
┌─product─┬─product_num─┬───────date─┐
│ aaaa    │ bbbb        │ 2020-01-01 │
└─────────┴─────────────┴────────────┘

another solution (without CH) - awk or sed
for example:

echo cccc,dddd|awk '{print $0",2020-01-01"}'|clickhouse-client --query="INSERT INTO db.database FORMAT CSV";

SELECT * FROM db.database
┌─product─┬─product_num─┬───────date─┐
│ cccc    │ dddd        │ 2020-01-01 │
└─────────┴─────────────┴────────────┘