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