Database Reference
In-Depth Information
In the relational database you would use an additional table,
WirelessNetwork
, with a foreign key. The foreign key relationships of
Application
and
WirelessNetwork
to
PhoneLog
are identical.
CREATE TABLE WirelessNetwork (
record_id CHAR(64),
position TINYINT UNSIGNED,
ssid CHAR(32),
bssid CHAR(32),
connected BOOLEAN,
PRIMARY KEY (record_id, position),
FOREIGN KEY (record_id) REFERENCES
PhoneLog(record_id)
);
Consider a query that determines the relationship between recent
applications running and the currently connected wireless network for a
given device.
SELECT
wireless.ssid AS ssid,
running.name AS app,
COUNT(1) AS cnt
FROM PhoneLog
NATURAL JOIN Application running
NATURAL JOIN WirelessNetwork wireless
WHERE id = 'U7nHcz-7bKTu'
AND wireless.connected
AND running.importance_level > 300
GROUP BY ssid, app
ORDER BY ssid, cnt DESC
Simply dropping the
NATURAL JOIN
s does not yield a valid BigQuery
query. This is because of the restriction on the use of independently
repeating fields. The reason this restriction exists is to avoid inadvertent
data explosions. The double natural join generates the cartesian product
of records with a given log ID from the
Application
table with a set of
records with the same log ID in the
WirelessNetwork
table. The query
explicitly requests the cartesian product by including multiple joins. If
