How to query csv data in ClickHouse from HDFS with some omitted columns

user_2067978 0
Viewed 352

I want to create a table with engine=hdfs and copy data into a table with engine=MergeTree. Here my DDL for hdfs table:

CREATE TABLE price_hdfs
(
   product_id String,
   price Decimal(16,2),
   regular_price Decimal(16,2),
   promocode_price Decimal(16,2),
   discount Decimal(16,2),
   saved_at DateTime,
   source String,
   bucket_id UInt64
)
ENGINE=HDFS('hdfs://namenode:9000/wdm/price_20????.csv', 'CSV');

But csv files on HDFS do not have all trailing columns on all rows:

14694303,3078,5400,,2322,2021-08-03 22:15:31,wdm
9201607,2940,4900,,1960,2021-08-03 22:15:31,wdm
...
9201609,2941,4901,2,1961,2021-08-03 22:15:32,wdm,1234 <- this new field bucket_id

I get error when query this table:

[2021-12-16 11:47:05] Code: 27. DB::ParsingException: Cannot parse input: expected ',' before: '\n9201607,2940,4900,,1960,2021-08-03 22:15:31,wdm\n19364027,2360,5900,,3540,2021-08-03 22:15:32,wdm\n10147914,498,1899,,1401,2021-08-03 22:15:33,wdm\n12333579,2205,':
[2021-12-16 11:47:05]   Row 1:
[2021-12-16 11:47:05]   Column 0,   name: product_id,      type: String,           parsed text: "14694303"
[2021-12-16 11:47:05]   Column 1,   name: price,           type: Decimal(16, 2),   parsed text: "3078"
[2021-12-16 11:47:05]   Column 2,   name: regular_price,   type: Decimal(16, 2),   parsed text: "5400"
[2021-12-16 11:47:05]   Column 3,   name: promocode_price, type: Decimal(16, 2),   parsed text: <EMPTY>
[2021-12-16 11:47:05]   Column 4,   name: discount,        type: Decimal(16, 2),   parsed text: "2322"
[2021-12-16 11:47:05]   Column 5,   name: saved_at,        type: DateTime,         parsed text: "2021-08-03 22:15:31"
[2021-12-16 11:47:05]   Column 6,   name: source,          type: String,           parsed text: "wdm"
[2021-12-16 11:47:05]   ERROR: Line feed found where delimiter (,) is expected. It's like your file has less columns than expected.
[2021-12-16 11:47:05]   And if your file has the right number of columns, maybe it has unescaped quotes in values.
[2021-12-16 11:47:05]   : While executing ParallelParsingBlockInputFormat: While executing HDFS: (at row 1)
[2021-12-16 11:47:05]   . (CANNOT_PARSE_INPUT_ASSERTION_FAILED) (version 21.11.7.9 (official build))

How to replace omitted fields (the last one on the example - bucket_id) with null or 0 when query?

clickhouse
1 Answers

I've used a workaround with Hive+Trino to query data from HDFS and import it to ClickHouse.

insert into price SELECT * FROM jdbc(
        'jdbc:trino://trino.server:8080/hive/default',
        'select product_id, ... from price'
    );

It requires Hive with metadata conntected to HDFS (I had), Trino connected to HDFS and Hive metadata (I had) and jdbc bridge - https://github.com/ClickHouse/clickhouse-jdbc-bridge with Trino driver.

This way provides method to add where col is not null to select query in jdbc or make some columns nullable in destination table in ClickHouse.

user_20679780
Related