Continuous materialized view 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.

To create a continuous materialized view of a Bigtable table, you run aSQL query that defines the continuous materialized view.

This document describes concepts and patterns to help you prepare yourcontinuous materialized view SQL query. Before you read this document, youshould be familiar withContinuous materializedviews andGoogleSQL forBigtable.

Continuous materialized views use restricted SQL syntax. Thefollowing pattern shows how to build a continuous materialized view SQL query:

SELECTexpressionASalias[,...]FROMfrom_item[WHEREbool_expression]GROUPBYexpression[,...];from_item:{table_name[as_alias]|field_path}as_alias:[AS]alias

If you want to build a continuous materialized view SQL query as an asynchronoussecondary index, use theORDER BY clause:

SELECTexpressionASalias[,...]FROMfrom_item[WHEREbool_expression]ORDERBYexpression[,...];from_item:{table_name[as_alias]|field_path}as_alias:[AS]alias

Query limitations

The following rules apply to a SQL query used to create a continuousmaterialized view:

• Must be aSELECT statement.
• Must have aGROUP BY clause or, for asynchronous secondary index queries, anORDER BY clause, but not both.
• Must use only supported aggregation functions.
• Can have multiple aggregations per group.

Supported aggregations

You can use the following aggregation functions in a SQL query that defines acontinuous materialized view:

• COUNT
• SUM
• MIN
• MAX
• HLL_COUNT.INIT
• HLL_COUNT.MERGE
• HLL_COUNT.MERGE_PARTIAL
• ANY_VALUE
• BIT_AND
• BIT_OR
• BIT_XOR
• AVG

If youSELECT COUNT(*) you must define a row key, like in the followingexample:

SELECT'*'AS_key,COUNT(*)AScountFROMfooGROUPBY_key;

Unsupported SQL features

You can't use the following SQL features:

• Any feature not supported byGoogleSQL for Bigtable
• ARRAY
• ARRAY_AGG
• ARRAY_CONCAT_AGG
• COUNT_IF
• CURRENT_TIME and other non-deterministic functions
• DATE,DATETIME as output columns (UseTIMESTAMP or store a string.)
• DESC sort in the output
• DISTINCT option, as inSUM(*DISTINCT* value))
• LIMIT/OFFSET
• SELECT *
• OVER clause to create a windowing aggregation
• STRUCT

You also can't nestGROUP BY orORDER BY clauses or create map columns. For additionallimitations, seeLimitations.

Avoiding excluded rows

Input rows are excluded from a continuous materialized view in the followingcircumstances:

• More than 1 MiB of data is selected from the row. For example, if yourquery isSELECT apple AS apples , SUM(banana) AS sum_bananas FROM my_tableGROUP BY apples, then any row that contains more than 1MiB of data in theapple andbanana columns is excluded from the continuous materializedview.
• More than 1 MiB of data is output from the row. This might occur whenyou use queries such asSELECT REPEAT(apple, 1000) or use large constants.
• More than 10 times more data is output than was selected.
• The query doesn't match your data. This would include attempting todivide a zero, integer overflow, or expecting a row key format that isn'tused in every row key.

Excluded rows increment the user errors metric when they are first processed.For more information about metrics that can help you monitor your continuousmaterialized views, seeMetrics.

Query details

This section describes a continuous materialized view query and how the resultsmight look when the view is queried. Data in the source table is theinput, and the result data in the continuous materialized view is theoutput. Output data is either aggregated or unaggregated (in the defined key).

SELECT statement

The select statement configures the columns and aggregations used in thecontinuous materialized view. The statement must use either aGROUP BY clauseto aggregate across rows or anORDER BY clause to create an asynchronous secondaryindex.

SELECT * is not supported, butSELECT COUNT(*) is.

As in a typicalSELECT statement, you can have multiple aggregations per agrouped set of data. The ungrouped columns must be an aggregation result.

This is an example of a standardGROUP BY aggregation query in SQL:

SELECTmyfamily["node"]ASnode,myfamily["type"]AStype,COUNT(clicks)ASclicks_per_keyFROMmytableGROUPBYnode,type

Row keys and unaggregated data

You can specify a_key as the row key for a continuous materializedview. If you don't, columns in theGROUP BY clause form the key in theview.

Row keys defined by a_key column

You can optionally specify a_key column when you define yourcontinuous materialized view. (This is different from the_keycolumn that you get whenyou execute a SQL query on a Bigtable table.) If you specify a_key, the following rules apply:

• You must group by_key, and you can't group by anything else except(optionally) by_timestamp. For more information, seeTimestamps.
• The_key column must be of typeBYTES.

Specifying a_key is useful if you plan to read the view withReadRowsrather than with SQL, because it gives you control over the row key format. Onthe other hand, a SQL query to a view with a defined_key might need to decodethe_key explicitly instead of just returning structured key columns.

Row keys defined byGROUP BY orORDER BY clause

If you don't specify a_key, the unaggregated columns in yourSELECT listbecome the row key in the view. You can assign the key columns any namessupported by SQL conventions. Use this approach if you plan to use SQL to querythe view rather than aReadRows request.

Unaggregated output columns in theSELECT list must be included in theGROUPBY clause. The order in which columns are written in theGROUP BY clause isthe order in which the data is stored in the continuous materialized view rowkey. For example,GROUP BY a, b, c is implicitlyORDER BY a ASC, b ASC, cASC.

If you use anORDER BY clause instead of aGROUP BY clause to create anasynchronous secondary index, the columns in yourSELECT list that are part of theORDER BY clause become the row key in the view. The order in which columnsare written in theORDER BY clause is the order in which the data is storedin the continuous materialized view row key. For example,ORDER BY a, b, cstores the data with row keys ordered bya ASC, thenb ASC, thenc ASC.

SELECTfam["baz"]ASbaz,SUM(fam["foo"])ASsum_foo,SUM(fam["bar"])ASsum_barFROMTABLEGROUPBYbaz;

SELECT_key,TIMESTAMP_TRUNC(_timestamp,DAY)AS_timestamp,SUM(sum_family["sum_column"])ASsum_column,SUM(sum_family["foo"])ASsecond_sum_columnFROMUNPACK(SELECT*FROMmy_table(with_history=>TRUE))GROUPBY1,2

If you read from the view usingReadRows, you need to decode the aggregateddata in your read request. For more information onReadRows requests, seeReads.

Aggregated values in a continuous materialized view are stored using encodingdescribed in the following table, based on the output type of the column fromthe view definition.

What's next

• Create and manage continuous materializedviews
• GoogleSQL for Bigtable referencedocumentation