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 theAlbumInfo
table whereMarketingBudget
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 theMarketingBudget
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.