BigQuery Schema

Background

• Each M-Lab tool consists of a client and a server.
• Whenever an M-Lab user starts a test, the client and server interact to measure different aspects of that user’s connection.
• A single user request triggers one or more tests (e.g., client-to-server test, server-to-client test).
• For each test, a server collects a log, and the test can be uniquely identified by its log filename.
• Timestamp fields are stored in our schema in UTC
• Time only fields are stored in milliseconds (ms)

BigQuery Storage and Processing Datasets

For each release, M-Lab publishes tables and views in two groups of datasets. One group of datasets is used primarily for storage and processing, and another group which we recommended that most researchers use to query for M-Lab data.   Four storage and processing datasets are published for each release, beginning with v3.1:  

• base_tables
  • Contains the raw NDT and Switch tables.
  • This dataset will eventually hold all M-Lab data, from 2009 to present, for NDT, Sidestream, Switch, and Paris Traceroute.  
• batch
  • Destination for batch processed data prior to dedupping.  
• intermediate_vX_Y_Z
  • Provides working, intermediate BigQuery Views for each release version
  • These views adapt data from tables in the base_tables dataset to make release views available.
  • Intermediate dataset views are publicly visible for transparency, but we discourage most people from using them directly.  
• legacy
  • Contains exact copies of the data stored in our previous NDT fast tables, for use in the intermediate and release views.
  • This dataset will eventually include all legacy Sidestream and Paris Traceroute tables.

Recommended Datasets and Views for Most Researcher Queries

The datasets in the previous section outline where raw M-Lab test data is parsed and stored. While they are also queryable in the above locations, M-Lab recommends that most researchers instead begin querying the views published in our release dataset. This will make future table schema transitions less impactful since queries can be pinned to the release views instead of to a specific table name.   Three researcher query datasets are published for each new release:

• rc
  • Beta test version of the next release of views.
  • These views represent the most recent release-candidate.  
• rc_vX_Y_Z
  • Recent stable releases.  
• release
  • An alias to current supported release.
  • This is the set of views that most people should use.
  • These views are updated to include any refinements to the recommended filtering, and may include changes to the schema over time.

Beginning with the upcoming v3.1 release, in the rc, rc_vX_Y_Z, and release datasets we will publish the following views:  

• ndt_all
• ndt_all_legacysql
• ndt_downloads
• ndt_downloads_legacysql
• ndt_uploads
• ndt_uploads_legacysql

The views above ending in _legacysql require you to use legacySQL queries, and those labeled without it (standardSQL) require you the use of standardSQL queries. Additionally, the views which include ‘uploads’ or ‘downloads’ provide a subset of NDT data that are valid, completed tests which meet the criteria described on our page, Calculating Common Metrics. We highly recommend using standardSQL as this will be required to query all M-Lab tables in the future.   Researchers interested in querying unfiltered NDT data should use the table in the base_tables dataset. Sidestream and Paris Traceroute tables will also be added to the base_tables dataset in the coming weeks:  

• base_tables
  • ndt - measurement-lab.base_tables.ndt
  • sidestream - measurement-lab.base_tables.sidestream
  • switch - measurement-lab.base_tables.switch
  • traceroute - measurement-lab.base_tables.traceroute

Summary of BigQuery Tables (v3.1)

The current version of M-Lab BigQuery tables is v3.1, and are listed below. Tables are bolded and table views are listed underneath where applicable.

Base Tables for Historical and Current Data

• measurement-lab.legacy.ndt (data ~ 2015-01-01 - 2017-05-10)
• measurement-lab.legacy.ndt_pre2015 (data ~ 2009-02-18 - 2014-12-31)
• measurement-lab.base_tables.ndt
• measurement-lab.base_tables.sidestream
• measurement-lab.base_tables.switch
• measurement-lab.base_tables.traceroute

Recommended Datasets and Views for Research Queries

• measurement-lab.rc
• measurement-lab.rc_v3_1
• measurement-lab.release
  • measurement-lab.release.ndt_all
  • measurement-lab.release.ndt_all_legacysql
  • measurement-lab.release.ndt_downloads
  • measurement-lab.release.ndt_downloads_legacysql
  • measurement-lab.release.ndt_uploads
  • measurement-lab.release.ndt_uploads_legacysql

If you have queries that reference historical M-Lab tables, please review Migrating Queries to learn how to convert them to use M-Lab’s release views.

Please also review the table/view schema version history and changelog at the end of this page for more information.

Equivalent BigQuery and Web100 Field Types

tcp-kis.txt defines each Web100 variable with a specific SNMP type. This table shows how to map each SNMP type to a BigQuery type.

Blacklist Flags Field

The field blacklist_flags was used to mark test affected by the “switch discard issue” identified in 2015-2016. M-Lab NDT data from 2010-01-01 to 2015-10-02 was marked using this field. M-Lab may use the field for other use cases in the future for tests potentially impacted by site configuration issues, or otherwise communicate potentially relevant information about the state of the platform at the time of the test.

In the NDT v3.1 schema, we have added a sub-record, anomalies, which contains an equivalent field, anomalies.blacklist_flags.   Currently, the following values are present in NDT data from 2010-01-01 to 2015-10-02 in these fields:  

All views in the release dataset limit to tests where blacklist_flags is 0 or NULL.  

Table Schemas (v3.1)

The schemas for NDT, Sidestream, and Paris Traceroute are provided below. Note that the schemas for release views of NDT are largely the same as listed below with the exception of the date partition field.

NDT - measurement-lab.base_tables.ndt

Sidestream - measurement-lab.base_tables.sidestream

Paris Traceroute - measurement-lab.base_tables.traceroute

Switch - measurement-lab.base_tables.switch

The switch table schema (also known as “DISCO”, named after the “DIScard COllection” service that records the switch data).

v2 - March 2016

• Began the publication of per project “fast tables” for NDT, NPAD, Paris Traceroute, and Sidestream.
  • plx.google:m_lab.ndt.all
  • plx.google:m_lab.npad.all
  • plx.google:m_lab.paris-traceroute.all
• Continued the publication of v1 monthly tables, and published a migration guide.
• Deprecated fields in v2 “fast tables”:
  • type
  • project
  • web100_log_entry.is_last_entry
  • web100_log_entry.group_name

v2.1 - November 2016

• The field blacklist_flags was added to v2 per project “fast tables”, and historical data from 201001-01 to 2015-10-02 was re-parsed to add this annotation, due to a switch discard issue related to traffic microbursts.

v3 - May 2017

• Began publication to new date partitioned table and updated schema to support the new, open source, ETL pipeline.
• Data publication to v2 tables stopped at this time.

v3.0.1 - October / November 2017

• The schema for v3.0.1 tables was updated, removing an alpha feature called deltas, which attempted to log the differences between test snaplogs instead of the final test values. This feature will be revisited in future schema updates.
• Newly released data annotation engine added geolocation and some metadata to tests from 2016 to present.
• Published a series of beta BigQuery views for NDT data, to allow data queries across both v2 and v3.0.x tables.
• Published traceroute and sidestream table to replace v2 versions, migrated data, re-annotated data.

v3.0.2 - December 2017

• Standardized the naming scheme for BigQuery table and view names to be consistent with new semantic versioning.
• All tables and views must be queried using StandardSQL, except for views with “legacysql” in the name.
• Views for tests other than NDT may be published in the future using the same format:
  • <test>_all_<version> (standardSQL)
  • <test>_all_legacysql_<version>
• Complete documentation for tables, views, the contents of views, and what data they limit (where applicable) will be published on this page.
• Views will be published concurrently with new table schemas, such that all table versions will have corresponding views.
• Previous versions of our tables will be referenced by versions 1.0, 2.0, etc. in our documentation but actual table names will not be changed.
• Re-ran historical annotations for traceroute, npad, and sidestream data due to a bug where some geolocation annotations was not present in all past test data.

v3.1 - February 2018

• First official release of v3 tables, with all historical data re-parsed, and annotated with geolocation metadata.

v3.1.1 - July 2018

• Publish official Switch tables from the DISCO dataset.

Published tables and views are:

• measurement-lab.legacy.ndt (data ~ 2015-01-01 - 2017-05-10)
• measurement-lab.legacy.ndt_pre2015 (data ~ 2009-02-18 - 2014-12-31)
• measurement-lab.base_tables.ndt

• measurement-lab.base_tables.switch

• measurement-lab.rc
• measurement-lab.release_v3_1
• measurement-lab.release
  • measurement-lab.release.ndt_all
  • measurement-lab.release.ndt_all_legacysql
  • measurement-lab.release.ndt_downloads
  • measurement-lab.release.ndt_downloads_legacysql
  • measurement-lab.release.ndt_uploads
  • measurement-lab.release.ndt_uploads_legacysql

Planned Additions/Enhancements for Future Releases

• Begin publishing new Sidestream and Paris Traceroute data to tables in the base_tables dataset:
  • measurement-lab.base_tables.sidestream
  • measurement-lab.base_tables.traceroute
• Publish release BigQuery Views for Sidestream and Paris Traceroute
• Publish Sidestream and Paris Traceroute collected prior to the switch to base_tables in the legacy dataset
  • measurement-lab.legacy.sidestream
  • measurement-lab.legacy.traceroute
• Move historical NPAD data into the legacy dataset
  • measurement-lab.legacy.npad