Making the scraper results queryable from SQL is a huge win for analytics, for example the outputs can be plugged into a Superset dashboard.
Currently, the outputs are only in .ndjson and .csv files under https://analytics.wikimedia.org/published/datasets/one-off/html-dump-scraper-refs/, but these can only be analyzed after import or by using eg. `jq`.
- [] Plan Hive schema, map data types.
- Page summary schema should match internal analysis, use discrete fields and native data types, not a JSON blob.
- Maybe truncate long lists eg. of templates used on the page? (product question, not blocking)
- Note: `map<string, int>` is troublesome because an empty map always defaults to `map<string, string>`
- Upstream protocols must preserve structure
- [] Decide on database and namespace.
- Proposal: wmde.page_summary, wmde.wiki_summary
- EventGate: wmde_page_analysis
- [x] (Deprecated) Hive adapter:
- [] https://gitlab.com/wmde/technical-wishes/apache_hive/-/merge_requests/1
- [] https://gitlab.com/wmde/technical-wishes/apache_hive/-/merge_requests/2
- [] https://github.com/kafka4beam/sasl_auth/pull/41
- [] https://github.com/kafka4beam/sasl_auth/pull/42
- [] Scraper persists outputs in Hive.
- [] Refactor `pipeline.ex` so that page summarization is a stream from input (enterprise or local file) to output (hive exporter, local file...)
- [] Output module to export to EventGate. Are batch inserts possible?
- [] Document fields and provide example HiveQL queries.
- [] Write Airflow or dbt jobs. See https://gitlab.wikimedia.org/repos/data-engineering/dbt-jobs
- [] Include sensors and alerts. "wiki snapshot is completely analyzed", "monthly snapshot never arrived"...
- [] Refine EventGate data into independent page_summary and wiki_summary tables.
- [] Keep 13 months of snapshot SCD.
- [] Take over per-wiki aggregation (remove from scraper).
== Schema
The per-page table will hold all analysis outputs, mapped into Hive native data types.
Draft per-page schema:
```
create table if not exists page_summary (
-- Page metadata (partition columns are in separate clause)
page_id int,
page_namespace string,
page_title string,
revision_id int,
revision_timestamp string,
-- General analysis fields
html_length int,
wikitext_length int,
-- Cite refs analysis fields
automatic_ref_names_count int,
automatic_ref_name_usages_count int,
identical_ref_count int,
list_defined_ref_count,
nested_ref_count,
potential_ref_transclusions map<string, int>,
potential_transclusions_with_top_level_refs map<string, int>,
ref_by_top_ref_transclusion_count int,
ref_by_transclusion_count int,
ref_count int,
ref_error_counts_by_type map<string, int>,
reflist_count int,
ref_reuse_counts array<int>,
refs_with_solely_transclusion_count int,
refs_with_transclusions_count int,
ref_with_name_count int,
similar_ref_count int,
subref_count int,
transclusion_count int,
transclusions_inside_refs map<string, int>,
unique_name_count int
)
partitioned by (
snapshot string,
wiki_db string
)
```
== Hive adapter (deprecated)
There seems to be no existing driver for Hive in Elixir, so we've written a basic one: `apache_hive`. It needs to support HiverServ2 Thrift over Kerberos5/GSSAPI SASL.
== Out of scope
There are no known external consumers of the published analytics files, so no obligation to keep producing the files. Old published files could even be deleted and json code run optionally depending on configuration.