Update pages and requests schemas on staging to match crawl dataset

[max-ostapenko]

The older data schema is being reprocessed using these queries:

• pages.js
• requests.js

After we promote these new schemas to be the new default we need to update agent processing.

We should be able to just do SELECT * when copying data from crawl_staging to crawl in crawl_complete pipeline.

[max-ostapenko]

The transformation of crawl_staging.requests into crawl.requests lasted ~13h for Nov 2024 crawl (not including failed attempts).

@pmeenan let's update the wptagent to match crawl_staging with crawl schema.
As agreed we will not update legacy table anymore, so ready for a cleanup.

[max-ostapenko]

HTTPArchive/dataform#33 to sync the pipeline with the adjustments.

[max-ostapenko]

I hope it's not gonna be needed, BUT in case there are some issues with ingesting data to JSON columns we can fallback to parsing STRING data within the pipeline as we do currently.

[max-ostapenko]

@pmeenan from data in crawl_staging it looks like pages.summary values are not yet trimmed, e.g.:

• '$._adult_site',
• '$.archive',
• '$.avg_dom_depth',
• '$.crawlid',
• '$.createDate',
• '$.doctype',
• '$.document_height',
• '$.document_width',
• '$.label',
• '$.localstorage_size',
• '$.meta_viewport',
• '$.metadata',
• '$.num_iframes',
• '$.num_scripts_async',
• '$.num_scripts_sync',
• '$.num_scripts',
• '$.pageid',
• '$.PageSpeed',
• '$.rank',
• '$.sessionstorage_size',
• '$.startedDateTime',
• '$.url',
• '$.urlhash',
• '$.urlShort',
• '$.usertiming',
• '$.wptid',
• '$.wptrun'

and pages.metadata:

• '$.page_id',
• '$.parent_page_id',
• '$.root_page_id'

True, or are these rows outdated?

[max-ostapenko]

Some of these fields were removed in #15.
How should we proceed?

[pmeenan]

Were they present in all the records? The first few were before I removed the fields but the last 1-2 should have most of them removed.

…

On Thu, Dec 5, 2024 at 7:41 PM Max Ostapenko ***@***.***> wrote: Some of these fields were removed in #15 <#15>. How should we proceed? — Reply to this email directly, view it on GitHub <#18 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AADMOBM2XNCNCONIR4U25AL2EDXDFAVCNFSM6AAAAABP4RC5H6VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDKMRRHAZTGNBRGY> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[max-ostapenko]

Yeah, if sorted by payload.startedDateTime last two are OK.
👍🏽