Structured row key queries

If a table has a row key schema that defines structured row keys , you can use SQL to query the individual segments – or columns – of the row key.

Continuous materialized views, including asynchronous secondary indexes, generate structured row keys by default. You can also define a structured row key for any Bigtable table that you want to query with SQL by creating a row key schema for the table. For more information, see Manage row key schemas .

Keys

In a Bigtable table without a structured row key schema, each row is indexed by a single row key. When you query the table with SQL, the row key is a column named _key that SQL uses as the primary key. It's not a pseudocolumn, so values in the _key column are returned when you execute a SELECT * query on the table.

On the other hand, in a table that has a row key schema, the row key (primary key) is the combination of all the columns named as fields in the row key schema. When you add a row key schema to a table, the _key column becomes a pseudocolumn, which means it won't show up in a SELECT * query, but you can select it explicitly with a SELECT _key statement.

Sample queries

The examples in this section assume that a table named sales has the following schema:

 field {
    field_name: "user_id"
    type: { bytes_type { encoding { raw {} } } }
  }
  field {
    field_name: "purchase_date"
    type: { string_type { encoding { utf8_bytes {} } } }
  }
  field {
    field_name: "order_number"
    type: { string_type { encoding { utf8_bytes {} } } }
  }
  encoding {
    delimited_bytes { delimiter "#" }
  } 

The sales table contains the following data. The product column family has two columns.

Structured row key query results

Because the sales table has structured row keys, if you query the table with a SELECT * statement, the query returns each segment of the row key as a separate column. As in any SQL query to a Bigtable table, columns in a column family are expressed as maps.

   
 SELECT 
  
 * 
  
 from 
  
 sales 
 

Results look like the following:

You can also specify the row key columns in your query, as shown in the following example:

   
 SELECT 
  
 product 
 [ 
 product_type 
 ] 
  
 AS 
  
 product_type 
 , 
  
 product 
 [ 
 product_name 
 ] 
  
 AS 
  
 product_name 
  
 FROM 
  
 sales 
  
 WHERE 
  
 user_id 
  
 = 
  
 b 
 "user1" 
 

The results look like the following:

Filters

You can filter on the row key schema columns using SQL functions. The following example assumes that CURRENT_DATE() returns 2025-05-24 :

   
 SELECT 
  
 user_id 
 , 
  
 product 
 [ 
 "product_name" 
 ] 
  
 AS 
  
 product_name 
  
 FROM 
  
 sales 
  
 WHERE 
  
 PARSE_DATE 
 ( 
 "YYYY-MM-DD" 
 , 
  
 purchase_date 
 ) 
  
 = 
  
 CURRENT_DATE 
 () 
  
 AND 
  
 user_id 
  
 = 
  
 b 
 "user2" 
 

The results are as follows:

Aggregate queries

The following example shows how use an aggregate query on structured row key fields:

   
 SELECT 
  
 user_id 
 , 
  
 product 
 [ 
 product_type 
 ] 
  
 AS 
  
 product_type 
 , 
  
 count 
 ( 
 * 
 ) 
  
 AS 
  
 count 
  
 FROM 
  
 sales 
  
 GROUP 
  
 BY 
  
 1 
 , 
  
 2 
 

The query results are as follows:

Original row key

To retrieve the original row key in a table with structured row keys, specify the _key column in your query.

   
 SELECT 
  
 _key 
 , 
  
 user_id 
  
 FROM 
  
 sales 
 

The query returns the following:

What's next

• GoogleSQL for Bigtable overview
• Schema design best practices