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.

  SELECT 
  
 SUM 
 ( 
 MarketingBudget 
 ) 
  
 AS 
  
 total_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 .