Reduce the number of refine job calls to the hive metastore
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	JAllemandou
	Nov 18 2025, 10:11 AM

Description

thanks to this slack thread, we noticed that the hive metastore started to receive a very big number of queries per second when we deployed the airflow refine job:
https://grafana.wikimedia.org/goto/kk6c6tivg?orgId=1

We need to fix this.

Details

Related Changes in Gerrit:

	Subject	Repo	Branch	Lines +/-
	Update Refine-CLI job to not overwhelm metastore	analytics/refinery/source	master	+43 -32

Customize query in gerrit

Related Changes in GitLab:

	Title	Reference	Author	Source Branch	Dest Branch
	Update main refinery jar version	repos/data-engineering/airflow-dags!1822	joal	update_main_refine_jar	main
	Bump analytics_test refine jar	repos/data-engineering/airflow-dags!1819	aqu	T410378_bump_refine_to_hive_job_jar	main

Customize query in GitLab

Event Timeline

JAllemandou created this task.Nov 18 2025, 10:11 AM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptNov 18 2025, 10:11 AM

JAllemandou claimed this task.Nov 18 2025, 10:11 AM

JAllemandou moved this task from Next Up to In progress on the Data-Engineering (Q2 FY25/26 October 1st - December 31th) board.

bking subscribed.Nov 18 2025, 4:46 PM

Change #1206823 had a related patch set uploaded (by Joal; author: Joal):

[analytics/refinery/source@master] Update Refine-CLI job to not overwhelm metastore

https://gerrit.wikimedia.org/r/1206823

gerritbot added a project: Patch-For-Review.Nov 18 2025, 5:21 PM

I'm pretty sure I have nailed down the problem.
At write time, the refine job inserts partitions in overwrite mode. To do so, it is needed to set spark.sql.sources.partitionOverwriteMode to dynamic, otherwise Spark overwrites the entire table, not the partitions touched only.
Changing this approach to a delete+insert where we drop the partition, write the files in a folder with overwrite, then recreate the partition, has solved the problem.
My guess is that for Spark to decide about partitions drop and recreate, it first requests information about all the existing partitions in the table, to store that information in its own format (catalog) instead of having in the metastore only.
Anyhow, I have patch fixing the issue (see above), it currently runs on the test-cluster successfully, we'll deploy it tomorrow.