clickhouse_loader.py versus direct load from S3

[Bens-ct]

I'm at the stage where I think the sink connector is absolutely the right choice for me, but the one downside I've encountered is the initial sync, with not dumping the data to kafka and having it picked up at leisure if the initial snapshot fails for any reason, essentially the whole process starts again.

I have ~ 500 mysql databases with ~75 tables in each to sync and I am breaking them down into batches of 50 with a connector for each so the need to get them all done as an initial sync is proving quite challenging.

The clickhouse_loader script looks like a winner here, however I also see that clickhouse supports direct import from S3 so was wondering if anyone had any insights as to whether one option is better than the other?

Ideally I'll be scripting the whole process to get the initial syncs done from a registry of databases stored in clickhouse and then automatically switch on the cdc once the sync is completed.

The scripts here look like they are probably tested reasonably well (although I don't see an obvious way to capture the bin log pos in a single transaction from the mysql dumper script, although I did only have a quick look).

In theory dumping the dbs to s3 should be reasonably straightforward so in absence of suggestions, I'm probably at the point of tossing a coin.

[aadant]

the binary log position is included in the dump if you use the mysqlshell dumper.
You can find the binary log position here

# set up the replication position found in $DUMP_DIR/@.json

clickhouse_loader.py uses the clickhouse-client from CSV and it does all the schema conversion for you (schema + data from tsv.zstd). so more advanced than loading data from s3. The client can probably be adapted to load from S3 if you dump to S3.
that would be a nice FR / PR !

I would recommend to start with the schema then the data :

check your requirements in terms of timezones, you would need to specify them in the connector config unless everything is UTC

schema :

# -- setup as above but run into a different setup (source / target ) environment.
python db_dump/mysql_dumper.py --mysql_host ... --mysql_user ... --dump_dir $DUMP_DIR --mysql_database $DATABASE --schema_only --debug
# --
time python db_load/clickhouse_loader.py --clickhouse_host  ...  --clickhouse_database $DATABASE --dump_dir $DUMP_DIR  --mysql_source_database $DATABASE --mysqlshell  --rmt_delete_support --clickhouse_datetime_timezone UTC --threads=1

data :

• remove --schema_only from the dumper
• add --data_only to the loader

[aadant]

both the loader and dumper are multi-threaded, the only difficulty is to make sure you give CH the time to merge if you load TB of data. We did up to 100TB.

[Bens-ct]

I meant to come back and give an update, so far I've only tried S3 and it works well.

Good point on the schemas though, however I am pre creating my schemas as I am ultimately planning to replicate ~ 500 databases with perhaps 70 tables from the 800 tables in the mysql dbs). Some of those databases will be small and some, relatively, large so I have a create tables script that checks the volume of data in the source tables and creates the tables in CH with an appropriate partition range for the volume of data.

Loading the data in via s3 seems like it will meet my needs and is easily scriptable.

I will revisit the mysql_dumper script though as the mysq lshell looks like it will solve the single threaded dump issue my custom script has (provided I can dump the specific tables I want in a single transaction rather than the complete database).

[aadant]

@Bens-ct it would be nice to extend those scripts to export to S3 compatible storag (mysql shell supports that I think) then restore from S3 using clickhouse-client. Is it something you want to contribute back to the project ?

[Bens-ct]

I can certainly have a look and see what I can do, It would be good to give something back as this project has saved me from Kafka hell :).