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.

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.

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.

perhaps related to the data being loaded since commenting https://github.com/ClickHouse/examples/tree/main/docker-compose-recipes/recipes/ch-and-minio-S3/fs/volumes/clickhouse/docker-entrypoint-initdb.d will allow to connect fine to clickhouse

Please provide a simple docker-compose example where one can

• Spin up a Clickhouse server locally
• Database should be accessible using database name, username, password
• User should be able to connect to it using a general-purpose database client, e.g. DBeaver or DbVisualizer

Currently, I am able to spin up the server using docker compose up but do not know how to configure database name, username, password. Even editing users.xml and re-running docker compose up does not help.

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.

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