Summary

Every day and every month we copy tables out of the MediaWiki, CentralAuth and Wikifunctions databases into the Data Lake. Datasets such as MediaWiki History are built from those copies. The tool that does the copying is Apache Sqoop.

Sqoop needs replacing for two reasons.

• Apache retired the project in 2021, so it receives no updates.
• It writes to HDFS, which we are moving away from in T435461: [Epic] Migrate the Data Lake from Hadoop to Kubernetes and Ceph/S3.

This task is an evaluation of replacements. It compares the tools that could do this job against criteria we agree first, and ends in a recommendation. Making the change is the parent task, T435489: Data Lake ingestion after Hadoop: decide the future of Sqoop and Gobblin.

There are two quite different ways to do this job, and both may be considered the comparison. Even a combination of methods may be deemed appropriate.

• Batch processing: Copy the tables on a schedule, as we do today. The data is as fresh as the last run.
• Change Data Capture (CDC): This mean's following the database's change log. The tool reads the record of changes that the database already writes for its own replicas, and applies them to the lake as they happen. Fresher data, but a larger change to how the pipeline works.

One tool might handle both this job and the Kafka stream ingestion evaluated in T437080: [Tech evaluation] How we ingest the Kafka streams into the S3 data lake.

Candidates

• Apache Spark, reading over JDBC. No new tool. Spark reads the tables, writes them to the data lake, and Airflow runs the schedule. Its weakness is that the per-wiki and per-table logic in today's Sqoop management script becomes code that we have to we write and maintain.
• Apache SeaTunnel, release 2.3.13, an Apache top-level project. A job here is a configuration file that names a source and a destination. The same file can run on SeaTunnel's own engine, or on Flink, or on Spark. It can pick tables by pattern and write to many destination tables in one job. This seems like the closest available match in shape to what the combination of the sqoop binary and it home-grown management script currently does for us, but within a single tool.
• Flink CDC. The change-log approach, configured in YAML files. It can copy a whole database instance in one job, and can apply the source's schema changes to the destination as it goes.
• Debezium. The component that actually reads the database change logs. SeaTunnel and Flink CDC both use it underneath, so it is worth understanding in its own right, and it can be run on its own too.
• Sling and dlt, two lighter tools suggested in review. It may be worth checking to see whether they would cope with our table sizes, or whether they would suit smaller imports only.

Other tools we looked at

• Airbyte is well known, but it brings its own control plane and not all of it is free software.
• Meltano and the Singer ecosystem are light to run, but aimed more at software-as-a-service sources than at large database replicas.
• Apache NiFi is capable, but heavy to operate and configured mostly through its own interface.

What we need to find out

Does it work with MariaDB? We run MariaDB, and many of these tools are written and tested against MySQL. SeaTunnel's change-log connector lists MySQL 5.5 to 8.0 and Amazon RDS, and does not mention MariaDB at all. Flink CDC shares that same definition, because both use Debezium underneath. Reading tables over JDBC should be fine. Reading the change log is where MySQL and MariaDB have grown apart. So we should try a real capture against a real analytics replica in order to gain confidence in a solution.

How much of the management script goes away? Sqoop is only part of what we run. Around it sits a script that chooses the wiki list, decides which tables come from where, and handles retries and logging. A tool that absorbs some of that into configuration is worth more to us than one that only replaces the copying.

How many jobs would this actually be? It helps to count the real unit of work. The wikis live in nine database sections, s1 to s8 and x1, spread across three of our own servers. A change-log setup therefore needs one capture per section, not one per wiki, which is a far smaller number than the thousand-odd databases. Confirm that each candidate can cover a whole section in one job.

Which servers would we read from? Today's imports use two sets of credentials, and so read from two places. Most tables come from our own copy of the wikireplicas, an-redacteddb1001, where the data is already sanitised. The rest come from our analytics replicas, dbstore100[7-9], where the data is unredacted. That second group is larger than just the checkuser tables: it also covers actor, comment, content, watchlist, linktarget, the CentralAuth tables and the Wikifunctions tables. T426870: Move away from redacteddb for sqoop jobs proposes reading everything from the analytics replicas, because the sanitised views on an-redacteddb1001 drift out of step and are awkward for us to refresh, so a new design might give weight to following that direction.

Where does sanitisation happen afterwards? The wikireplicas views currently hide or remove some data on our behalf. If we read only from the unredacted analytics replicas, that filtering becomes the pipeline's responsibility for anything published onward. This follows from T426870: Move away from redacteddb for sqoop jobs whichever tool we choose, but the evaluation should state plainly where it happens instead.

Do we need the x3 section? Most of what we need is already on our own servers, since we hold s1 to s8 and x1. We do not currently operate a replica of the x3 section. Meanwhile some tables are moving between clusters: T431477: Data Lake - sqoop of wikidata_entity_usage needs to adapt to move to x1 MariaDB cluster covers the Wikidata entity usage table moving to x1, which we have, and T431476: Data Lake - sqoop of commons link tables needs to adapt to move to dedicated MariaDB cluster covers the Commons link tables moving to a dedicated cluster. So establish whether anything we import will end up being on x3. If it will, replicating that section to our own dbstore servers may be required.

Can it get storage credentials the way the rest of the platform will? The new storage issues short-lived S3 keys in exchange for a workload's identity token. A tool that can only use a stored access key is not disqualified, but it needs recording as an exception.

Does it write Iceberg tables, and suit our catalog? The catalog is being chosen in T435604: [Tech evaluation] Table catalog options for the S3 data lake, so check each candidate against the likely outcomes rather than assuming one.

Is every part of it free software? Check this component by component rather than project by project. An earlier attempt to use Kafka Connect for a similar job failed because the framework was free but the connector we needed was not.

Batch or streaming? Spark and Flink can each do both, but each was designed with one in mind. Judge every candidate in the mode we would actually run it.

Technical notes

• Judge connectors, not projects, because quality varies within a project. The parts that matter here are the MariaDB reader and the Iceberg writer.
• The external storage servers are not involved. We import metadata tables only. The content table is imported, but it holds addresses rather than the text itself, and no job imports the text table, so revision text never arrives this way.
• We might like to prototype the shortlist in codfw against the Ceph cluster there, so that the S3 write path and the credentials are exercised for real.
• Nothing on the candidate list runs here today. We do run Spark and Flink, so a tool that uses either is less new to us than one bringing its own engine.
• Review the requirements discusssed in the DPE deep dive, "What do we need from a Sqoop replacement?", linked from T435489: Data Lake ingestion after Hadoop: decide the future of Sqoop and Gobblin.

Acceptance criteria

• Each candidate is assessed against the questions above, with the results in a comparison document linked from this task.
• Change-log support for MariaDB is tested against a real analytics replica and the result recorded.
• A recommendation is agreed with Data Engineering, together with the follow-on tasks it implies.