This page describes how to enable Spanner columnar engine on a database, table, or index, and accelerate file format generation.
Enable Spanner columnar engine
Spanner columnar engine is enabled using a SET OPTIONS
clause
with the columnar_policy
option. You can apply this option when you create or
alter DATABASE
, TABLE
, or INDEX
schema objects. SEARCH INDEX
and
VECTOR INDEX
schema objects are never in columnar format.
Lower levels in a database and table hierarchy inherit the columnar_policy
from its parent. You can change this setting at lower levels.
The columnar_policy
option has the following flags:
'enabled'
or'disabled'
turns the columnar engine on or off for the specific schema object.NULL
(default) uses the columnar engine policy from the parent object, if one exists.NULL
clears previous settings on a table object.
You can also omit OPTIONS
to inherit the columnar_policy
from the parent
object.
The following example shows how to:
- Create a database with the columnar policy enabled.
- Define a
Singers
table that inherits the columnar policy from the database (omit thecolumnar_policy = NULL
for the table option). - Define a
Concerts
table with the columnar policy explicitly disabled.
CREATE DATABASE Music;
ALTER DATABASE Music SET OPTIONS (columnar_policy = 'enabled');
CREATE TABLE Singers(
SingerId INT64 NOT NULL,
FirstName STRING(1024),
LastName STRING(1024),
BirthDate DATE,
Status STRING(1024),
LastUpdated TIMESTAMP,)
PRIMARY KEY(SingerId),
OPTIONS ();
CREATE TABLE Concerts(
VenueId INT64 NOT NULL,
SingerId INT64 NOT NULL,
ConcertDate DATE NOT NULL,
BeginTime TIMESTAMP,
EndTime TIMESTAMP,)
PRIMARY KEY(VenueId, SingerId, ConcertDate),
OPTIONS (columnar_policy = 'disabled');
The following example shows how to enable the columnar engine on an existing
database named Artists
and disable it on a table within that database called
Museums
:
ALTER DATABASE Artists SET OPTIONS (columnar_policy = 'enabled');
ALTER TABLE Museums SET OPTIONS (columnar_policy = 'disabled');
Columnar file format generation
Spanner generates the columnar file format at compaction time. Compaction is a background process that typically is spread out over multiple days, but it might happen sooner if the size of the database grows substantially. For more information, see Optimal columnar coverage.
What's next
- Learn about columnar engine.
- Learn how to query columnar data.
- Learn how to monitor columnar engine.