The new schema and cost concerns for users #149
Comments
|
Let's use this issue to explore ways to minimize costs related to the
|
|
Important to note that there are two pages
Remove these as don't seem to be used anymore, and probably better covered by custom metrics:
There's argument to remove more, but think those are easy, non controversial wins and will half the size of this column (at least). I also wonder if the features and technologies should be JSON rather than array? Selecting two of these (e.g. website that use Wordpress AND jQuery) is more painful with arrays (requires a join AFAIK?). But on the other hand unnesting with JSON requires a JavaScript function AFAIK so pluses and minuses. However does seem a little inconsistent to use JSON in some places and arrays in others, unless we have a good reason? requests Remove these as unlikely to be used and can get from payload if really needed
Remove these as covered by request_headers or response_headers:
Similar to comment above, I wonder if we should use JSON rather than Arrays for Response Headers and Request Headers? |
Might not be the best solution, but the WordPress/jQuery example is possible without joins: WITH pages AS (
SELECT
page,
ARRAY_AGG(t.technology) AS technologies
FROM
`httparchive.all.pages` TABLESAMPLE SYSTEM (0.01 PERCENT),
UNNEST(technologies) AS t
WHERE
date = '2022-10-01' AND
client = 'mobile'
GROUP BY
page
)
SELECT
page
FROM
pages
WHERE
'WordPress' IN UNNEST(technologies) AND
'jQuery' IN UNNEST(technologies)Also possible to process a JSON-encoded array of technologies without a UDF: WITH json AS (
SELECT
page,
TO_JSON(technologies) AS technologies
FROM
`httparchive.all.pages` TABLESAMPLE SYSTEM (0.01 PERCENT)
WHERE
date = '2022-10-01' AND
client = 'mobile'
),
pages AS (
SELECT
page,
ARRAY_AGG(JSON_VALUE(t, '$.technology')) AS technologies
FROM
json,
UNNEST(JSON_QUERY_ARRAY(technologies, '$')) AS t
GROUP BY
page
)
SELECT
page
FROM
pages
WHERE
'WordPress' IN UNNEST(technologies) AND
'jQuery' IN UNNEST(technologies)IMO using arrays where possible is more semantic and avoids unnecessary decoding steps. There might be efficiency benefits, but that's not my main motivation. I do think it improves the QX in the general case—querying a single technology at a time or querying all technologies. |
|
Btw, another (admittedly small) grievance with arrays, is it makes the preview more difficult to use, as can't scroll down (e.g. I want a Lighthouse payload, but first one doesn't have what I want, and scrolling down is painful due to arrays of "useless" data). Noticed this type of thing more and more that I use this and kinda annoying. Any easy workaround I'm missing? I tend to use |
|
Great insights @tunetheweb, if that's actually not used then we'll be able to go down to:
Here is an estimation query: CREATE TEMP FUNCTION `prune_object`(
json_str STRING,
keys_to_remove ARRAY<STRING>
) RETURNS STRING
LANGUAGE js AS """
try {
var jsonObject = JSON.parse(json_str);
keys_to_remove.forEach(function(key) {
delete jsonObject[key];
});
return JSON.stringify(jsonObject);
} catch (e) {
return json_str;
}
""";
SELECT
SUM(BIT_COUNT(CAST(summary AS BYTES))) * 2 / 1024 / 1024 / 1024 AS summary_Gb,
SUM(BIT_COUNT(CAST(summary_pruned AS BYTES))) * 2 / 1024 / 1024 / 1024 AS summary_pruned_Gb,
SUM(BIT_COUNT(CAST(summary_pruned AS BYTES))) / SUM(BIT_COUNT(CAST(summary AS BYTES))) AS share
FROM (
SELECT
summary,
prune_object(
summary,
["metadata", "pageid", "createDate", "startedDateTime", "archive", "label", "crawlid", "url", "urlhash", "urlShort", "wptid", "wptrun", "rank", "PageSpeed", "_adult_site", "avg_dom_depth", "doctype", "document_height", "document_width", "localstorage_size", "sessionstorage_size", "meta_viewport", "num_iframes", "num_scripts", "num_scripts_sync", "num_scripts_async", "usertiming"]) as summary_pruned
FROM `all.pages` TABLESAMPLE SYSTEM (5 PERCENT)
WHERE date = '2024-08-01'
) |
|
I've been looking into an idea of using JSON columns for I like an idea of getting from: We would still write strings to staging (there are limitations for writing JSON directly), but then transform it while appending to It's a bit of a stretch, but worth checking now than after the backfill. A few things I have noticed:
SELECT
page,
metadata.rank,
payload,
custom_metrics.ads.ads.present, -- nesting with dot notation
categories
FROM `httparchive.scratchspace.pages_10k_JSON`,
UNNEST(JSON_QUERY_ARRAY(technologies)) AS tech, -- JSON is not supported for unnesting
UNNEST(JSON_QUERY_ARRAY(tech, "$.categories")) AS categories
WHERE client = "mobile"
AND is_root_page
AND rank = 1000I share the feelings about the UI issues related to REPEATED columns (issuetracker), but much more I like to use a simpler syntax, e.g.: SELECT
page
FROM `httparchive.all.pages`
WHERE
date = '2024-09-01' AND
client = 'mobile' AND
is_root_page AND
rank = 10000 AND
'WordPress' IN UNNEST(technologies.technology) AND
'6.6.2' IN UNNEST(technologies.info)So let's hope for UI improvements.
CREATE TEMP FUNCTION parse(cm STRING)
RETURNS JSON
LANGUAGE js AS """
try {
cm = JSON.parse(cm);
} catch (e) {
cm = null;
}
return cm;
""";
SELECT
custom_metrics AS original,
SAFE.PARSE_JSON(custom_metrics) AS native_parse_default,
SAFE.PARSE_JSON(custom_metrics, wide_number_mode => 'round') AS native_parse_rounded,
parse(custom_metrics) AS udf_parse
FROM `sample_data.pages_10k`
WHERE client = "mobile"
AND is_root_page
AND rank = 1000
AND SAFE.PARSE_JSON(custom_metrics) IS NULL
|
So we have a new schema in the
alldataset which basically has two tables:pagesrequestsPreviously we have had separate schemas for each data type (pages, requests, lighthouse, response bodies, technologies) and also summary tables (summary_pages, summary_requests).
There is a LOT to like about the new schema, including:
_TABLE_SUFFIXAnd there are some good cost benefits:
pagestable is partitioned ondateand clustered onclient,is_root_page, andrank. This means, for these columns, you only pay for the rows you query. This saves time and real dollars. In the old schema you paid the full amount for every table you queried even if you were only getting a few rows.requeststable is partitioned ondateand clustered onclient,is_root_page,is_main_document, andtype, with same benefits as above.WHEREclause. This prevents querying the whole table, which could be VERY expensive. The query will not run without a date. You can, however, use a date range (e.g.WHERE date > '1900-01-01) for trend queries - though you shouldn't do that for really expensive queries.payloadcolumn for common things. This was basically available before with thesummarytables, but has been greatly enhanced. For pages we now pull our rank, all the previous summary data and custom metrics into their own columns. This can also be enhanced in future to pull out more things (e.g. page-level CrUX data, lighthouse scores, or particular audits).So we want to migrate people to the new schema as, in general, it's easier to use, and costs less (in dollars AND time).
However, I have one concern with the new schema and everything being in the one table, as opposed to split out before. This makes the (VERY!) expensive
payload,lighthouseandreponse_bodiesdata much easier to query. I don't think below are good defaults:Coming from a more traditional RDMS background, it's quite common in my experience to run a
SELECT *on a table to view it, and I worry BigQuery newbies could do this and end up with HUGE bills. BigQuery does have theSchemaandPreviewoptions on tables and these are much better than usingSELECT *but, as I say, not everyone is a BigQuery user.We can (and have) insisted on a partition field (
date) but BigQuery does not allow us to insist on columns being explicitly given so we cannot prevent people running above sort ofSELECT *queries.We have a number of options to address this concern of mine:
SELECTeven in the old schema and in many ways it's better now, even if in some ways maybe it's worse.all.pagesandall.requeststables and switch back to having separatepayload,lighthouseandresponse_bodiestables. This then means either 1) Extra joins or 2) Duplicating data so loses some of the benefits of the new Schema. It would also be a change to our pipeline.alltables within theallschema (e.g.all.summary_pages,all.summary_requests), without those expensive columns and encourage their use, over theall.pagesandall.requeststables, especially for beginners. But is this more confusing and will it lead to people writing the same query in two different ways?httparchive.queryschema with broken down tables:httparchive.query.summary_pages,httparchive.query.pages,httparchive.query.lighthouse,httparchive.query.summary_requests,httparchive.query.response_bodies) and encourage their use, with people free to use theallschema if they need to, to avoid joins. We could even not bother with theresponse_bodiestable in this basic Schema and say that's only for experts. But is this more confusing and will it lead to people writing the same query in two different ways?The Views can be created once and will automatically update so I don't think maintenance is an issue.
For example, in the
latestschema we currently have three new view that automatically look at the latest month's data and also look at a subset of the data:I'd be interested to hear views on this (@rviscomi I know you have some as we've discussed), and whether we need to do anything for this?
The text was updated successfully, but these errors were encountered: