The examples below query the M-Lab data in various ways to demonstrate effective use of the M-Lab BigQuery data set. Please note that the examples presented here assume prior knowledge of database query languages such as SQL and some knowledge of computer networking terms and concepts such as subnets and IP addresses.
Basic Counting: How Many Users?
Let’s start with something simple. How many distinct users (distinct IPs, for simplicity) have ever run an NDT test?
#standardSQL SELECT COUNT(DISTINCT web100_log_entry.connection_spec.remote_ip) AS num_clients FROM `measurement-lab.release.ndt_all` WHERE web100_log_entry.connection_spec.remote_ip IS NOT NULL;
Result (last updated 1/25/2018):
Computing Statistics Over Time: How Many Users Per Day?
By slightly modifying the previous query, it is possible to compute how the number of users changed over time.
#standardSQL SELECT partition_date AS day, COUNT(DISTINCT web100_log_entry.connection_spec.remote_ip) AS num_clients FROM `measurement-lab.release.ndt_all` WHERE web100_log_entry.connection_spec.remote_ip IS NOT NULL GROUP BY day ORDER BY day ASC;
Dealing with IP Addresses: How Many Users from Distinct Subnets?
BigQuery supports various functions to parse IP addresses in different formats. You can use such functions to aggregate the number of users per subnet and to compute how many subnets have ever initiated a test.
The query that follows aggregates the client IP addresses into /24s and counts the number of unique /24s that have ever initiated at least one NDT test. Please refer to BigQuery Query Reference for more detail on the functions used in the query below.
We have to distinguish between IPv4 addresses and IPv6 addresses. We do this using the connection_spec.client_af which will be equal to 10 for IPv6 addresses, and 0 or 2 for IPv4 ones.
#standardSQL CREATE TEMPORARY FUNCTION computeSubnet(ip STRING, ipType INT64) RETURNS STRING AS ( IF(NET.SAFE_IP_FROM_STRING(ip) IS NOT NULL, CASE -- IPv4 WHEN (ipType = 0) OR (ipType = 2) THEN NET.IP_TO_STRING(NET.IP_TRUNC(NET.SAFE_IP_FROM_STRING(ip), 24)) -- IPv6 WHEN (ipType = 10) AND (LENGTH(ip) > 48) THEN NET.IP_TO_STRING(NET.IP_TRUNC(NET.SAFE_IP_FROM_STRING(ip), 48)) ELSE NULL END, NULL) ); SELECT COUNT(DISTINCT computeSubnet(web100_log_entry.connection_spec.remote_ip, connection_spec.client_af)) AS num_subnets FROM `measurement-lab.release.ndt_all`;
Computing Distributions of Tests Across Users: How Many Users Have Run a Certain Number of Tests?
Some IP addresses may have many initiated tests, while others may have only a few tests. To assess the representation of each IP address, we can classify the IP address based on the number of tests it has initiated.
- The query that follows computes the number of NDT tests initiated by each client IP address, groups the IP addresses by the number of tests run, and returns the number of IP addresses in each group.
- The inner query (in parentheses beginning with the second SELECT statement) calculates the number of NDT tests that each client performed. The query uses the
GROUP BYclause to collapse all the rows with the same
remote_ipaddress. The BigQuery Query Reference describes the
- The outer query transforms the results of the inner query by grouping each client according to the number of tests it performed, and then calculating the number of clients in each bucket.
#standardSQL SELECT num_tests, COUNT(*) AS num_clients FROM ( SELECT COUNT(*) AS num_tests, web100_log_entry.connection_spec.remote_ip AS remote_ip FROM `measurement-lab.release.ndt_all` WHERE partition_date >= '2017-01-01' AND partition_date <= '2017-12-31' GROUP BY remote_ip ) GROUP BY num_tests ORDER BY num_tests ASC;