Preview —Spanner columnar engine

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

Note: This feature is available with the Spanner Enterprise edition and Enterprise Plus edition. For more information, see theSpanner editions overview.

This page describes how to run queries against columnar data.

Query columnar data

Spanner query engine analyzes queries and automatically selects thecolumnar format if it is beneficial. However, several classes of queriesmight still need the query hint to use the columnar format. The following areexamples of how to use the@{scan_method=columnar} query hint:

• @{scan_method=columnar} SELECT COUNT(*) FROM Singers;
• SELECT COUNT(*) FROM Singers @{scan_method=columnar};
• @{scan_method=columnar} SELECT m.MsgBlob FROM Messages WHERE m.id='1234';

In addition, you can disable the automatic selection of columnar explicitly byusing the query hint@{scan_method=no_columnar}.

Query Spanner columnar data using BigQuery federated queries

To read Spanner columnar data from BigQuery, you caneither create anexternal datasetor use theEXTERNAL_QUERYfunction.

When you query external datasets, columnar data is automatically used if it'savailable and suitable for your query.

If you use theEXTERNAL_QUERY function, Spanner automaticallyuses the columnar data if it's available and appropriate for the workload.You can also include the@{scan_method=columnar} hint in the nested Spanner query.

In the following example for using the query hint:

• The first argument toEXTERNAL_QUERY specifies the external connection anddataset,my-project.us.albums.
• The second argument is a SQL query that selectsMarketingBudget from theAlbumInfo table whereMarketingBudget is less than 500,000.
• The@{scan_method=columnar} hint optimizes the external query for columnarscanning.
• The outerSELECT statement calculates the sum of theMarketingBudgetvalues returned by the external query.
• TheAS total_marketing_spend clause assigns an alias to the calculatedsum.

SELECTSUM(MarketingBudget)AStotal_marketing_spendFROMEXTERNAL_QUERY('my-project.us.albums','@{scan_method=columnar} SELECT AlbumInfo.MarketingBudget FROM AlbumInfo WHERE AlbumInfo.MarketingBudget < 500000;');

What's next

• Learn aboutcolumnar engine.
• Learn how toenable columnar engine.
• Learn how tomonitor columnar engine.