Asynchronous secondary index queries

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

This document provides examples of common query patterns for buildingasynchronous secondary indexes in Bigtable. The examples use IDs andvalues that are similar to those inData for examples and you cancreate a test table totest the queries.

Before you read this page, familiarize yourself withCreate an asynchronous secondary indexandGoogleSQL for Bigtable.

Example queries

The following examples show how to create asynchronous secondary indexes usingdifferent GoogleSQL queries.

Create an inverted index based on a column qualifier and values

The following query creates an inverted index by extracting all key-value pairsfrom a column family that has a flexible schema. It usesMAP_ENTRIES to get anarray of all data that thestats_summary column family stores, and thenUNNEST to transform each key-value pair into a separate row. The resultingindex allows for fast lookups based onstat_description andstat_value. Ifyou add new types of statistics to the source table, then Bigtableincludes them in the asynchronous index automatically.

SELECTstats.keyasstat_description,stats.valueasstat_value,_keyasoriginal_key,cell_planascell_planFROMtest_tableCROSSJOINUNNEST(MAP_ENTRIES(stats_summary))statsORDERBYstat_description,stat_value,original_key

As a result, each key-value pair from the originalstats_summary column familyhas its own row in the continuous materialized view:

To get fast lookups on data that Bigtable captured as values, usethe new row keys to query the data, as shown in the following example:

SELECTcell_plan,original_keyFROM`mv_inverted_index`WHEREstat_description="connected_cell"ANDstat_value="2"

Create a new row key based on existing data

The following example creates a new index key,total_plan_capacity, bycategorizing data plans that thecell_plan column family stores. The queryusesMAP_KEYS to get all keys incell_plan andARRAY_INCLUDES_ANY tofilter for specific data plan keys. ACASE statement then defines the logic toassign atotal_plan_capacity category that's based on the presence and valuesof these data plans. This allows for efficient lookups based on the combineddata plan capacity.

SELECTCASEWHENcell_plan['data_plan_01gb']="true"AND(cell_plan['data_plan_05gb']="false"ORcell_plan['data_plan_05gb']ISNULL)AND(cell_plan['data_plan_10gb']="false"ORcell_plan['data_plan_10gb']ISNULL)THEN'x-small'WHENcell_plan['data_plan_01gb']="true"AND(cell_plan['data_plan_05gb']="true")AND(cell_plan['data_plan_10gb']="false"ORcell_plan['data_plan_10gb']ISNULL)THEN'small'WHENcell_plan['data_plan_01gb']="true"AND(cell_plan['data_plan_05gb']="false"ORcell_plan['data_plan_05gb']ISNULL)AND(cell_plan['data_plan_10gb']="true")THEN'medium'WHEN(cell_plan['data_plan_01gb']="false"ORcell_plan['data_plan_01gb']ISNULL)AND(cell_plan['data_plan_05gb']="true")AND(cell_plan['data_plan_10gb']="true")THEN'large'WHENcell_plan['data_plan_01gb']="true"AND(cell_plan['data_plan_05gb']="true")AND(cell_plan['data_plan_10gb']="true")THEN'x-large'ENDastotal_plan_capacity,_keyasoriginal_key,stats_summaryFROMtest_tableWHEREARRAY_INCLUDES_ANY(MAP_KEYS(cell_plan),["data_plan_01gb","data_plan_05gb","data_plan_10gb"])ORDERBYtotal_plan_capacity,original_key

Create a time-series-based index

Bigtable stores each cell value and its associated timestamp. Byusing theWITH_HISTORY=>TRUE flag in theFROM clause, you can retrieve allversions of data and their timestamps. TheUNPACK table function then expandsthe query results so that each timestamped value appears on its own row with aseparate_timestamp column. This lets you create an asynchronous secondaryindex where the new row key is based on these timestamp values, enabling quicklookups that are based on time ranges.

SELECT_timestampasstats_timestamp,_keyasoriginal_key,stats_summaryFROMUNPACK((select_key,stats_summaryFROMtest_table(WITH_HISTORY=>TRUE)))ORDERBYstats_timestamp,original_key

Create an index entry for items and values in a JSON cell

Bigtable is often used as a key-value store for large-scaleworkloads, including data in formats such as JSON. The following example showshow to create an asynchronous secondary index for fast lookups of data that JSONstores. The query usesJSON_EXTRACT to extract thestore_id from a JSONstring within thetransaction_information column family in a table namedsales. This index allows for quick retrieval of sales transactions by specificstore IDs.

SELECTJSON_EXTRACT(transaction_information["json"],"$.store_id")asstore_id,_keyasoriginal_key,transaction_informationfromsalesORDERBYstore_id,original_key;

What's next

• Continuous materialized view queries
• Create and manage continuous materialized views