New Traceroute Table and Schema Now Available

DataParis TracerouteCommunity

By Ya Chang

•

August 1, 2019

To make our traceroute data in BigQuery more useful, researchers have sought an easy way to reconstruct the path of hops for the same test. This task was particularly hard because the schema, which was designed many years ago, put the hops of the same test in different rows.

To address this need from many of our partners and researchers, M-Lab is delighted to announce that the traceroute BigQuery table in the aggregate dataset is now available to the public. The new traceroute schema has one test per row, and all hops for a single test are inside the same row.

Here is the new schema, which also adds ASN annotation for traceroute source, destination, and hops:

schema_1

schema_2

schema_3

The new BigQuery table preserves all information that was in the previous table, measurement-lab.base_tables.traceroute, with better geo-location annotation coverage using time-based Maxmind databases. The geo-location annotation rate for traceroute source and destination are 100%. For traceroute tests since 2017, the geo-location annotation rate for hops almost doubled from ~20% in the BigQuery table, to >40% in current BigQuery table. The ASN annotation rate is about 97%.

Sample Queries#

Here are some sample queries to access the traceroute table with the new schema:

Count how many traceroute tests per day given a time range#

SELECT
  ts,
  COUNT(*) AS num
FROM (
  SELECT
    DATE(TestTime) as ts
  FROM `measurement-lab.aggregate.traceroute`
  WHERE
  DATE(TestTime) BETWEEN DATE("2016-01-01") AND DATE("2018-06-30")
)
GROUP BY ts
ORDER BY ts DESC

Count rate of source and destination IP being annotated with Geolocation information#

SELECT
  ts,
  COUNTIF(s_long IS NOT NULL AND s_lant IS NOT NULL) / COUNT(*) AS s_geo_success_rate,
  COUNTIF(d_long IS NOT NULL AND d_lant IS NOT NULL) / COUNT(*) AS d_geo_success_rate
FROM (
  SELECT
    DATE(TestTime) as ts,
    Source.Geo.longitude AS s_long,
    Source.Geo.latitude AS s_lant,
    Destination.Geo.longitude AS d_long,
    Destination.Geo.latitude AS d_lant
  FROM `measurement-lab.aggregate.traceroute`
  WHERE
    DATE(TestTime) BETWEEN DATE("2016-11-01") AND DATE("2016-11-30")
)
GROUP BY ts
ORDER BY ts DESC

Count how many hops were in “New York” per day.#

SELECT
  ts,
  COUNTIF(hop_ip IS NOT NULL) AS total_hops,
  COUNTIF(hop_city = 'New York' ) AS num_ny_hops
FROM (
  SELECT
    DATE(TestTime) as ts,
    Parseinfo.TaskFileName,
    hops.Source.IP AS hop_ip,
    hops.Source.City AS hop_city,
    hops.Source.CountryCode AS hop_country
  FROM
    `measurement-lab.aggregate.traceroute` AS traceroute,
    UNNEST(traceroute.Hop) as hops
  WHERE
    DATE(TestTime) BETWEEN DATE("2017-08-01") AND DATE("2017-08-31")
)
GROUP BY ts
ORDER BY ts DESC

Count how many hops were in a specific ASN#

SELECT
  ts,
  COUNTIF(hop_ip IS NOT NULL) AS total_hops,
  COUNTIF(hop_asn = 15169 ) AS num_asn_hops
FROM (
  SELECT
    DATE(TestTime) as ts,
    hops.Source.IP AS hop_ip,
    hops.Source.ASN AS hop_asn
  FROM
    `measurement-lab.aggregate.traceroute` AS traceroute,
    UNNEST(traceroute.Hop) as hops
  WHERE
  DATE(TestTime)  BETWEEN DATE("2017-08-01") AND DATE("2017-08-31")
)
GROUP BY ts
ORDER BY ts DESC

Count the annotation rate of ASN for hops#

SELECT
  ts,
  COUNTIF(hop_ip IS NOT NULL) AS total_hops,
  COUNTIF(hop_asn <> 0 )/COUNTIF(hop_ip IS NOT NULL) AS rate_asn_hops
FROM (
  SELECT
    DATE(TestTime) as ts,
    hops.Source.IP AS hop_ip,
    hops.Source.ASN AS hop_asn
  FROM
    `measurement-lab.aggregate.traceroute` AS traceroute,
    UNNEST(traceroute.Hop) as hops
  WHERE
    DATE(TestTime)  BETWEEN DATE("2017-08-01") AND DATE("2017-08-31")
)
GROUP BY ts
ORDER BY ts DESC

New Traceroute Table and Schema Now Available

Sample Queries#

Count how many traceroute tests per day given a time range#

Count rate of source and destination IP being annotated with Geolocation information#

Count how many hops were in “New York” per day.#

Count how many hops were in a specific ASN#

Count the annotation rate of ASN for hops#

Related Posts

New Traceroute Binary on M-Lab platform

AFRICOMM 2018 and AFRINIC-29

A Hands-On Tutorial with Reverse Traceroute

About the Authors