GoogleSQL for Bigtable query examples
The examples on this page demonstrate SQL query patterns for common and advanced Bigtable queries. You can run GoogleSQL queries in the Bigtable Studio query editor. You can also run queries using the Bigtable client library for Java.
Before you read this page, read the GoogleSQL for Bigtable overview.
The examples on this page use IDs and values similar to those in Data for examples.
Common Bigtable SQL query patterns
The following are examples of common queries for Bigtable data. To see examples of similar queries that call the Bigtable Data API, see Read examples and Use filters.
Retrieve the latest version of all columns for a given row key.
SELECT * FROM myTable WHERE _key = 'r1'
Retrieve all versions of all columns for a given row key.
SELECT * FROM myTable(with_history => TRUE) WHERE _key = 'r1'
Retrieve the latest version of a particular column from a particular column family for a given row key.
SELECT stats_summary['os_build'] AS os
FROM analytics
WHERE _key = 'phone#4c410523#20190501'
Retrieve the row keys and the latest version of multiple columns for a given row key range.
SELECT
_key,
stats_summary['os_build'] AS os,
stats_summary['user_agent'] AS agent
FROM analytics
WHERE _key >= 'phone#4c410523#20190501' AND _key < 'phone#4c410523#201906201'
Retrieve all versions of all columns for multiple row key ranges, up to 10 rows.
SELECT *
FROM analytics(with_history => TRUE)
WHERE
(_key >= 'phone#4c410523#20190501' AND _key < 'phone#4c410523#201906201')
OR (_key >= 'phone#5c10102#20190501' AND _key < 'phone#5c10102#20190601')
LIMIT 10
Retrieve all versions of all columns for multiple row keys.
SELECT *
FROM analytics(with_history => TRUE)
WHERE _key = 'phone#4c410523#20190501' OR _key = 'phone#4c410523#20190502'
Retrieve all versions of all columns for multiple row keys using a different approach.
SELECT *
FROM analytics(with_history => TRUE)
WHERE _key IS IN ('phone#4c410523#20190501', 'phone#4c410523#20190502')
Retrieve the latest version of all columns within a column family for a row key prefix.
SELECT stats_summary
FROM analytics
WHERE _key LIKE 'phone#%'
Retrieve the row keys and three latest versions of all columns within a column family for all rows in the table. This query requires a full table scan, so it's not recommended for low-latency, high-throughput access patterns.
SELECT _key, cell_plan FROM analytics(with_history => TRUE, latest_n => 3)
Retrieve the latest version of all columns with row keys matching a specified
regular expression. This query requires a full table scan, so it's not
recommended for low-latency, high-throughput access patterns, unless you also
provide a row key prefix or row key range predicate in the WHERE
clause.
SELECT *
FROM myTable(with_history => TRUE)
WHERE REGEXP_CONTAINS(_key, '.*#20190501$')
Retrieve the latest version of all columns with the matching row key prefix and
counter value more than 123
. You don't need to cast for this comparison,
because Bigtable
aggregates
are numeric.
SELECT *
FROM myTable
WHERE _key LIKE 'user12%' AND counterFamily['counter'] > 123
Retrieve the latest version of all columns for a row key prefix if the referrer matches a specific value.
SELECT *
FROM analytics
WHERE _key LIKE 'com.mysite%' AND session['referrer'] = './home'
Categorize a given row based on the value of a given column. This query is similar to using a composing conditional filter in the Bigtable Data API.
SELECT
*,
CASE cell_plan['data_plan']
WHEN '10gb' THEN 'passed-filter'
ELSE 'filtered-out'
END
AS label
FROM analytics
Retrieve the row key and the column qualifiers in a specific column family for a specified row key range. In SQL, column families are represented by the map data type, where each column qualifier and value is mapped as a key-value pair. This SQL query is similar to using a strip value filter in the Bigtable Data API.
SELECT _key, MAP_KEYS(cell_plan) AS keys
FROM analytics
WHERE _key >= 'phone#4c410523#20190501' AND _key < 'phone#4c410523#201906201'
Advanced Bigtable SQL query patterns
The following samples demonstrate more advanced patterns.
With the following query, you can retrieve the row key and most recent value of
the JSON attribute abc
in the session
column family. For more information,
see JSON
functions.
SELECT _key, JSON_VALUE(session['payload'], '$.abc') AS abc FROM analytics
With the following query, you can retrieve the row key and calculate the average session length using the latest value of two Bigtable aggregate cells , which are numeric, for each row in the table.
SELECT
_key AS userid,
session['total_minutes'] / session['count'] AS avg_session_length
FROM analytics
With the following query, you can retrieve the latest version of all columns for
a given row key prefix if the session
column family contains referrer
,
origin
, or server
as a column qualifier. Alternatively, this query can also
be written as a series of individual comparisons such as session['referrer']
IS NOT NULL OR session['origin'] IS NOT NULL
. However, for queries involving
a large number of comparisons, the following approach is recommended.
SELECT *
FROM analytics
WHERE
_key LIKE 'com.abc%'
AND ARRAY_INCLUDES_ANY(MAP_KEYS(session), ['referrer', 'origin', 'server'])
With the following query, you can retrieve the latest version of all columns for
a given row key prefix if the session
column family containsreferrer
,
origin
, and server
as column qualifiers. Alternatively, this query can be
written as a series of individual comparisons such as session['referrer'] IS
NOT NULL AND session ['origin'] IS NOT NULL
.
SELECT *
FROM analytics
WHERE
_key LIKE 'com.abc%'
AND ARRAY_INCLUDES_ALL(MAP_KEYS(session), ['referrer', 'origin', 'server'])
With the following query, you can retrieve the latest version of all columns for
a given row key prefix if the session
column family contains
com.google.search
, com.google.maps
, or com.google.shopping
as values.
SELECT *
FROM analytics
WHERE
_key LIKE 'com.abc%'
AND ARRAY_INCLUDES_ANY(
MAP_VALUES(session),
['com.google.search', 'com.google.maps', 'com.google.shopping'])
With the following query, you can retrieve the latest version of all columns if
key-value pairs in the cell_plan
column family include both
data_plan:unlimited
and roaming:North America
.
SELECT *
FROM analytics
WHERE
ARRAY_INCLUDES_ALL(
CAST(
MAP_ENTRIES(cell_plan)
AS ARRAY<STRUCT<key STRING, value STRING>>),
[('data_plan', 'unlimited'), ('roaming', 'North America')])
With the following query, you can retrieve the row key
and temperature
readings for weather sensors for cases where the temperature exceeded 70 degrees
during the last seven measurements.
SELECT
_key AS sensorid,
ARRAY_FILTER(
CAST(
sensor['temperature']
AS ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>),
e -> CAST(e.value AS FLOAT32) > 70) AS high_temperature
FROM weather(with_history => TRUE, latest_n => 7)
In temporal filtering order, latest_n
comes last, so a query like after => X,
before => y, latest_n => 3
returns the latest three values that satisfy the
after and before conditions. If your use case requires latest_n
to take
precedence, you can provide latest_n
as the only temporal filter,
and then apply the rest of the temporal filters using query operators in your
SELECT
statement, as shown in the example. For more information, see Temporal
filters.
SELECT
ARRAY_FILTER(
CAST(
address['street']
AS ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>),
e -> e.timestamp > TIMESTAMP('2021-01-04 23:51:00+00'))
AS street_address
FROM locations(with_history => TRUE, latest_n => 3)
Similar to the previous example, you can apply a different temporal filter to
each column family in your query. For example, the following query returns the
three most recent versions of the street
column
and the two least recent versions of the state
column.
SELECT
ARRAY_FILTER(
CAST(
address['street']
AS ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>),
(e, i) -> i <= 2)
AS street_address,
ARRAY_FILTER(
ARRAY_REVERSE(
CAST(
address['state']
AS ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>)),
(e, i) -> i <= 1)
AS state
FROM locations(with_history => TRUE)
With the following query, you can retrieve all versions of all columns if
key-value pairs in the address column family include both city:Savannah
or
city:Nashville
at any point in time.
SELECT *
FROM locations(with_history => TRUE)
WHERE
ARRAY_LENGTH(
ARRAY_FILTER(
CAST(
MAP_ENTRIES(address)
AS ARRAY<
STRUCT<
key STRING,
value ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>>>),
e ->
e.key = 'city'
AND ARRAY_INCLUDES_ANY(
ARRAY_TRANSFORM(e.value, k -> k.value), ['Savannah', 'Nashville'])))
> 0
In this particular example, casting is not required, so this can also be written in the following shorter form.
SELECT *
FROM locations(with_history => TRUE)
WHERE
ARRAY_LENGTH(
ARRAY_FILTER(
MAP_ENTRIES(address),
e ->
e.key = 'city'
AND ARRAY_INCLUDES_ANY(
ARRAY_TRANSFORM(e.value, k -> k.value), ['Savannah', 'Nashville'])))
> 0