Query columnar data

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 the scan_method hint at the statement level or at the table level.

For example, you can use the following queries to read columnar data from the Singers and Messages 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 can either create an external dataset or use the EXTERNAL_QUERY function.

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

If you use the EXTERNAL_QUERY function, include the @{scan_method=columnar} hint in the nested Spanner query.

In the following example:

• The first argument to EXTERNAL_QUERY specifies the external connection and dataset, my-project.us.albums.
• The second argument is a SQL query that selects MarketingBudget from the AlbumInfo table where MarketingBudget is less than 500,000.
• The @{scan_method=columnar} hint optimizes the external query for columnar scanning.
• The outer SELECT statement calculates the sum of the MarketingBudget values returned by the external query.
• The AS total_marketing_spend clause assigns an alias to the calculated sum.

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

What's next

• Learn about columnar engine.
• Learn how to enable columnar engine.
• Learn how to monitor columnar engine.