Add `rank` field to `all.requests`

[rviscomi]

I often find myself joining pages and requests tables just to sort by rank. This field is page-specific but it'd be helpful to have this directly available in the requests table.

[kevinfarrugia]

It would also be helpful to run queries on a subset of all requests (to reduce costs) and filter on rank = 1000. Currently I am joining with httparchive.all.pages and filtering on rank from that table, but costs are much higher.

[max-ostapenko]

@pmeenan I was about to JOIN rank field in SQL when copying from crawl_staging.requests, but maybe you have an idea why we should do it in wptagent instead?

[pmeenan]

@max-ostapenko once the field exists in the table, the client-side schemas are here and then the value itself would get added here

[max-ostapenko]

Seems straightforward there, and probably better to have it throughout all pipeline.
I'll add it to wptagent then.

[pmeenan]

It doesn't look like the table itself has the column yet. I don't think it will be added automatically and will probably fail if the column isn't added before the next crawl (and I'm not sure if the schema is sensitive to the field ordering)

[max-ostapenko]

Do we need an existing data in the "crawl_staging" dataset?
If not, I'll create a blank requests table with the correct schema.

[pmeenan]

No, assuming the data has already been processed and is in the all dataset you can drop the table and then just Create table like all.requests. It gets truncated at the start of each crawl

[max-ostapenko]

BTW did we want to cluster by the rank field instead of is_main_document? @rviscomi @tunetheweb

If yes, I'll run the following query to get staging ready for the next crawl:

DROP TABLE `httparchive.crawl_staging.requests`;

CREATE TABLE `httparchive.crawl_staging.requests`
(
  date DATE NOT NULL OPTIONS(description="YYYY-MM-DD format of the HTTP Archive monthly crawl"),
  client STRING NOT NULL OPTIONS(description="Test environment: desktop or mobile"),
  page STRING NOT NULL OPTIONS(description="The URL of the page being tested"),
  is_root_page BOOL OPTIONS(description="Whether the page is the root of the origin."),
  root_page STRING NOT NULL OPTIONS(description="The URL of the root page being tested"),
  rank INT64 OPTIONS(description="Site popularity rank, from CrUX"),
  url STRING NOT NULL OPTIONS(description="The URL of the request"),
  is_main_document BOOL NOT NULL OPTIONS(description="Whether this request corresponds with the main HTML document of the page, which is the first HTML request after redirects"),
  type STRING OPTIONS(description="Simplified description of the type of resource (script, html, css, text, other, etc)"),
  index INT64 OPTIONS(description="The sequential 0-based index of the request"),
  payload STRING OPTIONS(description="JSON-encoded WebPageTest result data for this request"),
  summary STRING OPTIONS(description="JSON-encoded summarization of request data"),
  request_headers ARRAY<STRUCT<name STRING OPTIONS(description="Request header name"), value STRING OPTIONS(description="Request header value")>> OPTIONS(description="Request headers"),
  response_headers ARRAY<STRUCT<name STRING OPTIONS(description="Response header name"), value STRING OPTIONS(description="Response header value")>> OPTIONS(description="Response headers"),
  response_body STRING OPTIONS(description="Text-based response body")
)
PARTITION BY date
CLUSTER BY client, is_root_page, type, rank
OPTIONS(
  require_partition_filter=true
);

P.S. I'm trying to get the all dataset to a 'stable' version.

[pmeenan]

You shouldn't create the staging tables directly. Make whatever modification is needed to all.requests and then:

DROP TABLE `httparchive.crawl_staging.requests`;
CREATE TABLE `httparchive.crawl_staging.requests` LIKE `httparchive.all.requests`;

That will clone the structure of the all requests table and make sure they match.

[max-ostapenko]

There is at least one more change pending in all.requests - #263.

And adjusting the staging schema is easier than reprocessing all historical data in PROD table.

So my idea was to get to the desired state in staging first, map it to production schema within the automated pipeline. And get production table reprocessing started as we have achieved the stable schema in staging.

IMHO it's gonna be faster and cheaper way to apply all pending schema changes to all.

See steps outline here.