GoogleSQL for Bigtable queryexamples

The examples on this page demonstrate SQL query patterns for common and advancedBigtable queries. You can run GoogleSQL queries in theBigtable Studio query editor.You can also run queries using theBigtable client library forJava.

Before you read this page, read theGoogleSQL forBigtable overview.

The examples on this page use IDs and values similar to those inData for examples.

Common Bigtable SQL query patterns

The following are examples of common queries for Bigtable data. Tosee examples of similar queries that call the Bigtable Data API,seeReadexamples andUsefilters. For examples of queries on structured rowkeys, seeStructured row keyqueries.

Retrieve the latest version of all columns for a given row key.

SELECT*FROMmyTableWHERE_key='r1'

Retrieve all versions of all columns for a given row key.

SELECT*FROMmyTable(with_history=>TRUE)WHERE_key='r1'

Retrieve the latest version of a particular column from a particular columnfamily for a given row key.

SELECTstats_summary['os_build']ASosFROManalyticsWHERE_key='phone#4c410523#20190501'

Retrieve the row keys and the latest version of multiple columns for a givenrow key range.

SELECT_key,stats_summary['os_build']ASos,stats_summary['user_agent']ASagentFROManalyticsWHERE_key>='phone#4c410523#20190501'AND_key <'phone#4c410523#201906201'

Retrieve all versions of all columns for multiple row key ranges, up to 10rows.

SELECT*FROManalytics(with_history=>TRUE)WHERE(_key>='phone#4c410523#20190501'AND_key <'phone#4c410523#201906201')OR(_key>='phone#5c10102#20190501'AND_key <'phone#5c10102#20190601')LIMIT10

Retrieve all versions of all columns for multiple row keys.

SELECT*FROManalytics(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 differentapproach.

SELECT*FROManalytics(with_history=>TRUE)WHERE_keyISIN('phone#4c410523#20190501','phone#4c410523#20190502')

Retrieve the latest version of all columns within a column family for a row keyprefix.

SELECTstats_summaryFROManalyticsWHERE_keyLIKE'phone#%'

Retrieve the row keys and three latest versions of all columns within a columnfamily for all rows in the table. This query requires a full table scan, so it'snot recommended for low-latency, high-throughput access patterns.

SELECT_key,cell_planFROManalytics(with_history=>TRUE,latest_n=>3)

Retrieve the latest version of all columns with row keys matching a specifiedregular expression. This query requires a full table scan, so it's notrecommended for low-latency, high-throughput access patterns, unless you alsoprovide a row key prefix or row key range predicate in theWHERE clause.

SELECT*FROMmyTable(with_history=>TRUE)WHEREREGEXP_CONTAINS(_key,'.*#20190501$')

Retrieve the latest version of all columns with the matching row key prefix andcounter value more than123. You don't need to cast for this comparison,becauseBigtableaggregates are numeric.

SELECT*FROMmyTableWHERE_keyLIKE'user12%'ANDcounterFamily['counter'] >123

Retrieve the latest version of all columns for a row key prefix if the referrermatches a specific value.

SELECT*FROManalyticsWHERE_keyLIKE'com.mysite%'ANDsession['referrer']='./home'

Categorize a given row based on the value of a given column. This query issimilar to using acomposing conditionalfilter in theBigtable Data API.

SELECT*,CASEcell_plan['data_plan']WHEN'10gb'THEN'passed-filter'ELSE'filtered-out'ENDASlabelFROManalytics

Retrieve the row key and the column qualifiers in a specific column family for aspecified row key range. In SQL, column families are represented by the map datatype, where each column qualifier and value is mapped as a key-value pair. ThisSQL query is similar to using astrip valuefilter in theBigtable Data API.

SELECT_key,MAP_KEYS(cell_plan)ASkeysFROManalyticsWHERE_key>='phone#4c410523#20190501'AND_key <'phone#4c410523#201906201'

TheUNPACK function lets you transform Bigtable data into atabular time series format, which is useful when performing time seriesanalysis. Consider an example where you have aclicks column in anengagement column family. The following query usesUNPACK to see how certaincampaigns are performing by aggregating the clicks over a minute from the lasthour.

SELECTFORMAT_TIMESTAMP('%M',_timestamp)ASminute,COUNT(clicks)AStotal_clicksFROMUNPACK((SELECTengagement['clicks']asclicksFROMmetrics(with_history=>true,after=>TIMESTAMP_SUB(CURRENT_TIMESTAMP(),INTERVAL1HOUR))WHERE_key=@campaign_id))GROUPBYminute;

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 ofthe JSON attributeabc in thesession column family. For more information,seeJSONfunctions.

SELECT_key,JSON_VALUE(session['payload'],'$.abc')ASabcFROManalytics

With the following query, you can retrieve the row key and calculate the averagesession length using the latest value of twoBigtable aggregatecells , which are numeric, for each row in the table.

SELECT_keyASuserid,session['total_minutes']/session['count']ASavg_session_lengthFROManalytics

With the following query, you can retrieve the latest version of all columns fora given row key prefix if thesession column family containsreferrer,origin, orserver as a column qualifier. Alternatively, this query can alsobe written as a series of individual comparisons such assession['referrer']IS NOT NULL OR session['origin'] IS NOT NULL. However, for queries involvinga large number of comparisons, the following approach is recommended.

SELECT*FROManalyticsWHERE_keyLIKE'com.abc%'ANDARRAY_INCLUDES_ANY(MAP_KEYS(session),['referrer','origin','server'])

With the following query, you can retrieve the latest version of all columns fora given row key prefix if thesession column family containsreferrer,origin, andserver as column qualifiers. Alternatively, this query can bewritten as a series of individual comparisons such assession['referrer'] ISNOT NULL AND session ['origin'] IS NOT NULL.

SELECT*FROManalyticsWHERE_keyLIKE'com.abc%'ANDARRAY_INCLUDES_ALL(MAP_KEYS(session),['referrer','origin','server'])

With the following query, you can retrieve the latest version of all columns fora given row key prefix if thesession column family containscom.google.search,com.google.maps, orcom.google.shopping as values.

SELECT*FROManalyticsWHERE_keyLIKE'com.abc%'ANDARRAY_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 ifkey-value pairs in thecell_plan column family include bothdata_plan:unlimited androaming:North America.

SELECT*FROManalyticsWHEREARRAY_INCLUDES_ALL(CAST(MAP_ENTRIES(cell_plan)ASARRAY<STRUCT<keySTRING,valueSTRING>>),[('data_plan','unlimited'),('roaming','North America')])

With the following query, you can retrieve therow key andtemperaturereadings for weather sensors for cases where the temperature exceeded 70 degreesduring the last seven measurements.

SELECT_keyASsensorid,ARRAY_FILTER(CAST(sensor['temperature']ASARRAY<STRUCT<timestampTIMESTAMP,valueSTRING>>),e->CAST(e.valueASFLOAT32) >70)AShigh_temperatureFROMweather(with_history=>TRUE,latest_n=>7)

In temporal filtering order,latest_n comes last, so a query likeafter => X,before => y, latest_n => 3 returns the latest three values that satisfy theafter and before conditions. If your use case requireslatest_n to takeprecedence, you can providelatest_n as the only temporal filter,and then apply the rest of the temporal filters using query operators in yourSELECT statement, as shown in the example. For more information, seeTemporalfilters.

SELECTARRAY_FILTER(CAST(address['street']ASARRAY<STRUCT<timestampTIMESTAMP,valueSTRING>>),e->e.timestamp >TIMESTAMP('2021-01-04T23:51:00.000Z'))ASstreet_addressFROMlocations(with_history=>TRUE,latest_n=>3)

Similar to the previous example, you can apply a different temporal filter toeach column family in your query. For example, the following query returns thethree most recent versions of thestreet columnand the two least recent versions of thestate column.

SELECTARRAY_FILTER(CAST(address['street']ASARRAY<STRUCT<timestampTIMESTAMP,valueSTRING>>),(e,i)->i<=2)ASstreet_address,ARRAY_FILTER(ARRAY_REVERSE(CAST(address['state']ASARRAY<STRUCT<timestampTIMESTAMP,valueSTRING>>)),(e,i)->i<=1)ASstateFROMlocations(with_history=>TRUE)

With the following query, you can retrieve all versions of all columns ifkey-value pairs in the address column family include bothcity:Savannah orcity:Nashville at any point in time.

SELECT*FROMlocations(with_history=>TRUE)WHEREARRAY_LENGTH(ARRAY_FILTER(CAST(MAP_ENTRIES(address)ASARRAY<STRUCT<keySTRING,valueARRAY<STRUCT<timestampTIMESTAMP,valueSTRING>>>>),e->e.key='city'ANDARRAY_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 writtenin the following shorter form.

SELECT*FROMlocations(with_history=>TRUE)WHEREARRAY_LENGTH(ARRAY_FILTER(MAP_ENTRIES(address),e->e.key='city'ANDARRAY_INCLUDES_ANY(ARRAY_TRANSFORM(e.value,k->k.value),['Savannah','Nashville'])))    >0

What's next

• Browse the GoogleSQL for Bigtable reference documentation.
• Use the Bigtable Spark connector.
• Manage row key schemas.