clickhouse/examples

ClickHouse Examples

examples's Issues

Upgrade clickhouse connect version

There is a need to upgrade clickhouse-connect version from confluent-hub install --no-prompt clickhouse/clickhouse-kafka-connect:0.0.17 to confluent-hub install --no-prompt clickhouse/clickhouse-kafka-connect:v1.0.14
Because now it seems current version is no longer present

large data loads - Parallel script instances

Performance of the data load mechanism could be (drastically) improved by having an orchestrator that runs parallel instances of the script, each handling distinct subsets of the files This would require separate staging tables per script instance, and in a multi-server cluster, ideally, a different server is used per subset of files to utilize all available CPU cores fully.

large data loads - Format-specific variants

While the approach of the initial version of the data load script is generic, robust, and works for all formats, we can optimize performance by exploiting file type-specific knowledge and available metadata and, with that, avoiding unnecessary reads.

For example, ClickHouse SQL queries can access (and potentially utilize for our script) an exhaustive list of Parquet metadata. All numeric columns in a parquet file have metadata describing the minimum and maximum values per row group. From 23.8, ClickHouse automatically exploits this metadata at query time to speed up queries filtering on numeric columns in parquet files. Our script could utilize this as an alternative to rowNumberInAllBlocks by allowing parallel reading within Parquet.

large data loads - Variants without rowNumberInAllBlocks

In the initial version of the data load script, we explicitly limit the level of parallel processing to guarantee idempotence for the rowNumberInAllBlocks function. There are also approaches to split a large file's rows into evenly sized repeatable batches without the rowNumberInAllBlocks function. The rows could be dynamically assigned to n buckets based on applying a modulo operation with n to (1) an existing unique key column or, more generic, to (2) the hash value of all columns (e.g., WHERE halfMD5(*) % n = bucket_nr). The former requires explicit knowledge about the stored data values, while the latter can be slower than the rowNumberInAllBlocks-based approach.

large data loads - Record and filter on past successful batches

A possible improvement for the data load script could be to make the data load even more robust for interruptions and able to continue where it failed by recording a state containing all successfully processed and failed batches, allowing these to be excluded or included on a rerun of the script.