Comments (8)
Sounds like we have a way forward here. Instead of creating views, we will populate _stable
tables from the Parquet datasets. Steps to proceed:
-
Create a reasonably performant JS UDF for crc32 and sample_id (likely we should store the crcTable as a blob on GCS so we don't have to generate it each time).
-
Develop a query on top of
telemetry_core_parquet
that gets data into the right shape fortelemetry_stable.core_v10
, including usingTIMESTAMP_MICROS(DIV(metadata.timestamp, 1000)) AS submission_timestamp
. -
Run incremental queries to backfill all available days into
core
-
Generalize to other tables
from bigquery-etl.
I'm attempting to write this view for core pings right now and realizing some issues:
- We don't have submission_timestamp in the Parquet data, only submission_date; we have a
timestamp
field that I believe is client-provided and thus not guaranteed to fall on submission_date; would we manufacture some value like<submission_date> 12:00:00
? - We don't have sample_id in the parquet data; would we want to just use NULL for the Parquet data? We don't currently have a way of calculating sample_id in BigQuery due to lack of crc32 support, but we could probably make a JS UDF
- We are documenting in DTMO that
_stable
tables are guaranteed to have no duplicate document_ids within a partition, but we don't have that guarantee for the Parquet data
It's possible to address (2) and (3) if we abandon the idea of making a view and instead copy data into the telemetry_stable.core_v*
tables. This would give us a materialized sample_id and allow us to perform deduplication.
Fully solving (1) might require falling back to Heka data (does that exist for core pings? do the Heka messages even contain an ingestion timestamp?). I don't really like the idea of manufacturing a timestamp, as this might be very confusing to users. Not sure what to do there.
from bigquery-etl.
i'm not 100% sure, but I think metadata.timestamp
is populated by nginx at receipt time, not provided by the client, making it the correct source for submission_timestamp
from bigquery-etl.
i'm not 100% sure, but I think
metadata.timestamp
is populated by nginx at receipt time, not provided by the client, making it the correct source forsubmission_timestamp
@whd Can you confirm how metadata.timestamp
is populated for d2p datasets? Is it a reasonable analogue for submission_timestamp
in the GCP pipeline?
from bigquery-etl.
i'm not 100% sure, but I think
metadata.timestamp
is populated by nginx at receipt time, not provided by the client, making it the correct source forsubmission_timestamp
This is correct. See https://github.com/mozilla-services/nginx_moz_ingest/blob/master/src/ngx_http_moz_ingest_module.c#L613-L625.
@whd Can you confirm how
metadata.timestamp
is populated for d2p datasets? Is it a reasonable analogue forsubmission_timestamp
in the GCP pipeline?
It is. See https://github.com/mozilla-services/lua_sandbox_extensions/blob/master/parquet/sandboxes/heka/output/s3_parquet.lua#L59-L63 and e.g. https://github.com/mozilla-services/mozilla-pipeline-schemas/blob/master/schemas/telemetry/core/core.9.parquetmr.txt#L4.
from bigquery-etl.
It would appear that metadata.timestamp
always falls on the submission_date
, which bodes well.
WITH
times AS (
SELECT
submission_date,
TIMESTAMP_MICROS(DIV(MIN(metadata.timestamp), 1000)) mintime,
TIMESTAMP_MICROS(DIV(MAX(metadata.timestamp), 1000)) maxtime
FROM
`moz-fx-data-derived-datasets.telemetry_derived.telemetry_core_parquet_v3`
WHERE
submission_date >= "2019-01-01"
GROUP BY
1
ORDER BY
1),
inbounds AS (
SELECT
*,
DATE(mintime) = submission_date AS goodmin,
DATE(maxtime) = submission_date AS goodmax
FROM
times )
SELECT
*
FROM
inbounds
WHERE
NOT (goodmin
AND goodmax)
Produces no rows showing a timestamp outside the submission_date
.
from bigquery-etl.
Verifying that a CRC32 calculation in JS UDF is feasible:
-- Based on https://stackoverflow.com/a/18639999/1260237
create temp function crc32(data STRING) returns int64 language js AS """
var makeCRCTable = function(){
var c;
var crcTable = [];
for(var n =0; n < 256; n++){
c = n;
for(var k =0; k < 8; k++){
c = ((c&1) ? (0xEDB88320 ^ (c >>> 1)) : (c >>> 1));
}
crcTable[n] = c;
}
return crcTable;
}
var crc32 = function(str) {
var crcTable = makeCRCTable();
var crc = 0 ^ (-1);
for (var i = 0; i < str.length; i++ ) {
crc = (crc >>> 8) ^ crcTable[(crc ^ str.charCodeAt(i)) & 0xFF];
}
return (crc ^ (-1)) >>> 0;
};
return crc32(data);
""";
--select MOD(crc32("51baf8b4-75d1-3648-b96d-809569b89a12"), 100)
select MOD(crc32("6612376a-ac10-4cf8-a341-22340fb1b48e"), 100)
Both of these give correct sample_id.
from bigquery-etl.
Done
from bigquery-etl.
Related Issues (20)
- Add custom ping into Shredder HOT 4
- Change broken_site_report view to only use historical tables
- Add a separate view for live user_reports
- Improve dryrun skip logic for stage deploys
- Encode Derived Funnel Dimensions into UDF HOT 3
- Adding the ability to override scheduling parameters on backfill
- Schema updates for tables with not associated query.sql HOT 3
- Support publishing only authorized views
- Use schema.yaml for static tables instead of schema.json HOT 1
- [Data Checks Bug]: Apple CPUs are represented as empty string HOT 1
- Python 3.12 bootstrap - ModuleNotFoundError: No module named 'distutils'
- Create new derived query for Firefox-CI worker costs
- Support secrets in DAG generation
- event_monitoring_live generation is skipping apps that only have events defined in dependencies HOT 2
- materialized views don't get updated after deploy HOT 2
- Recreating materialized views may cause occasional dry run failures on main HOT 1
- Union default_table_workgroup_access with table-level workgroup_access in metadata generation logic
- `event` column from `events_stream` inaccessible in Looker HOT 2
- CI may be running stale tests in some cases HOT 1
- Monitor plus Attribution: Update ETL to store the channel for relevant Bigquery Tables and Looker explore HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from bigquery-etl.