https://docs.sqlfluff.com/en/stable/dialects.html#hive

5 datasets are being refined as a POC on the prod cluster. 2 on the test cluster.

The Airflow PR has been merged and should be released in Airflow 2.9 in April.

Done. The last version was done with Blubber:

• https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/602
• T351792

Some research:

• Each XML dumps snapshot may represent ~5.5TB (including ~1.8TB for wikidata and 1.4TB for enwiki)
• The Airflow sensor may take ~19days to turn green. It waits until the last dump has been processed (_IMPORTED flag). Most dumps are generated in a matter of days (~4 on average, maybe). Enwiki may take 7 days. And they all wait for the wikidata dump (~19 days).
• When the sensor turns green, a heavy Spark job is launched to convert all the compressed XML to parquet. ~5.5TB (compressed) is taking ~4.5 days to process.
• The perceived gaps are due to the non-parallelism of the dag + very long jobs. 1 heavy job is preventing the other ones from running. due to the retries (Thx for the pointer @JAllemandou ). Other symptoms, same problem here I think: https://phabricator.wikimedia.org/T342911

Linked with: https://phabricator.wikimedia.org/T351792

wmf.wikidata_item_page_link/snapshot=2024-01-15 looked fine from a row count perspective but 2024-01-22 snapshot was unexpectedly available and had zero rows:

select count(*) from wmf.wikidata_item_page_link where snapshot='2024-01-22';
...
+------+
| _c0  |
+------+
| 0    |
+------+

I've added the missing partitions in the source of wikidata_item_page_link and its missing snapshot is now generated.

https://docs.google.com/document/d/1upAje5lMawu4X6seRxI8Lx7YN-oHEzcEcm2fO6E5OH0/edit

I would like to add rewrite_manifests to the list of maintenance actions:

• rewrite_data_files
• expire snapshots
• rewrite_manifests https://iceberg.apache.org/docs/latest/spark-procedures/#rewrite_manifests

@BTullis , @brouberol , @Stevemunene I would like your feedback on this subject:

Following the Grafana dashboard review, I've performed some changes to it:

• I distributed the graphs into 4 sections: Failures, Durations, Counts, Scheduling
• I added missing parameterization of variables (e.g the list of operators changes when the instance is selected)
• I updated TTLs into statsd-exporter in order to reflect into Prometheus the rate of the metrics sent by Airflow (e.g. when Airflow emits a metric to say 1 task is in failure, it generates 1 call. and we would like to get this single point into Prometheus)
• I isolated task failures into its own graph

1/ Splitting the CI

In this ticket, I needed to find a way to detect when an Iceberg table has some data in it. This would replace the Hive partition sensor when migrating a table to Iceberg.
We chose to launch a Spark application running an SQL count. It's now implemented here:

• https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/564
• https://gerrit.wikimedia.org/r/c/analytics/refinery/+/983673

A drawback of this solution is that it generates a FAILED Spark application each time the sensor does not find any data in the interval. When monitoring our Spark applications, we want to avoid artificially growing the FAILED counts.

https://github.com/wikimedia/operations-puppet/blob/f7c3eb56a9417571792b7636367f3c13e850bc83/modules/profile/manifests/airflow.pp#L199

I think it's because the airflow-analytics jobs check should be run like the other commands: with a custom PYTHONPATH=/path/to/root/of/airflow-dags/ as an env variable.

I have the first version of the code in review.

In this puppet patch, we are adding configuration to send more Airflow metrics to Prometheus, and to customize them.

The workaround to our Gilab-CI pb is here: https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/537

The puppet configuration is now merged, and statsd_exporter is running on an-test-client1002. Analytics Prometheus is scrapping from it, as it should.

We have multiple needs considering scheduling Airflow dags & tasks.

Running locally with Docker, I manage to fetch a sample:

airflow_schedulerjob_start{instance="airflow-statsd-exporter:9102", job="statsd-exporter"} 3

Where:

• The airflow_ prefix was added in airflow.
• airflow-statsd-exporter:9102 is an independent container

After all the discussion on this subject, I also think that publishing Spark metrics to Kafka (then exported to hdfs) seems like the most obvious first step.

Wikitech page updated: https://wikitech.wikimedia.org/wiki/Analytics/Data_Lake/Traffic/Webrequest

Hello @fgiunchedi ! This ticket, when done, will add some Airflow metrics to Prometheus:

• a very reasonable amount (less than a hundred thousand)
• prefixed with the name of the airflow instance
• passing through a statsd_exporter

Just to let you know 😄

Yes, Druid is an option. There is a Grafana collector, and Druid is designed for time-series.

@Ottomata , yes, collecting to HDFS is completely possible yes. e.g. with a Spark metrics KafkaSink (I've seen one) + Gobblin.

As a closing word, two serious options remain:

1. Prometheus with a low resolution (1 point per minute for any metrics). This is OK to monitor evolution between runs but usually not for debugging a Spark job.
2. Send metrics-data with high time resolution to a new ad-hoc backend accessible from Grafana (InfluxDB or PG are good candidates)

Knowing the estimated metrics cardinality looks OK for the current Prometheus setup is good.

@Ahoelzl , as a complement to T297231 , we could get from those Airflow metrics:

• monitoring/alerting of the global number of failures (usually hidden by the retries)
• monitoring/alerting of the duration of specific tasks (like the Spark ones)

+1 for the simplicity induced in the conda-analytics package.

Thanks @BTullis ! I've tested it. It's now resolved!

Here is the list of events concerned:

• Edit
• Diacritics*
• Some prefixed with MobileWeb:
  • MobileWebBrowse
  • MobileWebClickTracking
  • MobileWebCta
  • MobileWebDiffClickTracking
  • MobileWebEditing
  • MobileWebInfobox
  • MobileWebLanguageSwitcher
  • MobileWebMainMenuClickTracking
  • MobileWebSectionUsage
  • MobileWebShareButton
  • MobileWebUIClickTracking
  • MobileWebUploads
  • MobileWebWatching*
  • MobileWebWikiGrok*
• TaskRecommendation*
• TestSearchSatisfaction
• VET135171
• WikipediaZeroUsage

Let's summarize our current problem.

@Jelto done. wikitech username & email address checked. Thanks!

Here is the first version of it. Please review in the gdoc: https://docs.google.com/document/d/1clSe6bnIxJUdd2LaFtQ-_MXGIRFBOzKNVZ2130qfGqI/edit