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.

For information about access to this release, see the access request page.

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

The@{scan_method=columnar} query hint enables a query to read columnar data.You can set thescan_method hint at thestatement levelor at thetable level.

For example, you can use the following queries to read columnar data from theSingers andMessages table:

• @{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';

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, include the@{scan_method=columnar}hint in the nested Spanner query.

In the following example:

• 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.