Skip to content
This repository was archived by the owner on Mar 4, 2026. It is now read-only.

Commit 9305151

Browse files
skuruppuskuruppu
committed
feat: support setting optimizerStatisticsPackage
`optimizerStatisticsPackage` can be set in `QueryOptions` when running Cloud Spanner queries. Can be configured through the following mechanisms: 1. Through the `SPANNER_OPTIMIZER_STATISTICS_PACKAGE` environment variable. 1. At `Database` level using `spanner.instance('instance-name').database('database-name', sessionPoolOptions, queryOptions)`. 1. At query level using `ExecuteSqlRequest.queryOptions`. If the options are configured through multiple mechanisms then: 1. Options set at an environment variable level will override options configured at the `Database` level. 1. Options set at a query-level will override options set at either the `Database` or environment variable level. If no options are set, the optimizer statistics package will default to: 1. The optimizer statistics package the database is pinned to in the backend. 1. If the database is not pinned to a specific statistics package, then the Cloud Spanner backend will use the "latest" version.
1 parent 31f9fd9 commit 9305151

3 files changed

Lines changed: 66 additions & 26 deletions

File tree

‎samples/queryoptions.js‎

Lines changed: 9 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -33,7 +33,14 @@ async function databaseWithQueryOptions(instanceId, databaseId, projectId) {
3333

3434
// Gets a reference to a Cloud Spanner instance and database
3535
const instance = spanner.instance(instanceId);
36-
const database = instance.database(databaseId, {}, {optimizerVersion: '1'});
36+
const database = instance.database(
37+
databaseId,
38+
{},
39+
{
40+
optimizerVersion: '1',
41+
optimizerStatisticsPackage: 'auto_20191128_14_47_22UTC',
42+
}
43+
);
3744

3845
const query = {
3946
sql: `SELECT AlbumId, AlbumTitle, MarketingBudget
@@ -90,6 +97,7 @@ async function queryWithQueryOptions(instanceId, databaseId, projectId) {
9097
ORDER BY AlbumTitle`,
9198
queryOptions: {
9299
optimizerVersion: 'latest',
100+
optimizerStatisticsPackage: 'latest',
93101
},
94102
};
95103

‎src/database.ts‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -380,6 +380,10 @@ class Database extends common.GrpcServiceObject {
380380
if (process.env.SPANNER_OPTIMIZER_VERSION) {
381381
options.optimizerVersion = process.env.SPANNER_OPTIMIZER_VERSION;
382382
}
383+
if (process.env.SPANNER_OPTIMIZER_STATISTICS_PACKAGE) {
384+
options.optimizerStatisticsPackage =
385+
process.env.SPANNER_OPTIMIZER_STATISTICS_PACKAGE;
386+
}
383387
return options;
384388
}
385389

‎test/spanner.ts‎

Lines changed: 53 additions & 25 deletions
Original file line numberDiff line numberDiff line change
@@ -853,7 +853,10 @@ describe('Spanner with mock server', () => {
853853

854854
describe('queryOptions', () => {
855855
/** Common verify method for QueryOptions tests. */
856-
function verifyQueryOptions(optimizerVersion: string) {
856+
function verifyQueryOptions(
857+
optimizerVersion: string,
858+
optimizerStatisticsPackage: string
859+
) {
857860
const request = spannerMock.getRequests().find(val => {
858861
return (val as v1.ExecuteSqlRequest).sql;
859862
}) as v1.ExecuteSqlRequest;
@@ -866,20 +869,28 @@ describe('Spanner with mock server', () => {
866869
request.queryOptions!.optimizerVersion,
867870
optimizerVersion
868871
);
872+
assert.strictEqual(
873+
request.queryOptions!.optimizerStatisticsPackage,
874+
optimizerStatisticsPackage
875+
);
869876
}
870877

871878
describe('on request', () => {
879+
const OPTIMIZER_VERSION = '100';
880+
const OPTIMIZER_STATISTICS_PACKAGE = 'auto_20191128_14_47_22UTC';
881+
872882
it('database.run', async () => {
873883
const query = {
874884
sql: selectSql,
875885
queryOptions: QueryOptions.create({
876-
optimizerVersion: '100',
886+
optimizerVersion: OPTIMIZER_VERSION,
887+
optimizerStatisticsPackage: OPTIMIZER_STATISTICS_PACKAGE,
877888
}),
878889
} as ExecuteSqlRequest;
879890
const database = newTestDatabase();
880891
try {
881892
await database.run(query);
882-
verifyQueryOptions('100');
893+
verifyQueryOptions(OPTIMIZER_VERSION, OPTIMIZER_STATISTICS_PACKAGE);
883894
} finally {
884895
await database.close();
885896
}
@@ -889,14 +900,15 @@ describe('Spanner with mock server', () => {
889900
const query = {
890901
sql: selectSql,
891902
queryOptions: QueryOptions.create({
892-
optimizerVersion: '100',
903+
optimizerVersion: OPTIMIZER_VERSION,
904+
optimizerStatisticsPackage: OPTIMIZER_STATISTICS_PACKAGE,
893905
}),
894906
} as ExecuteSqlRequest;
895907
const database = newTestDatabase();
896908
try {
897909
const [snapshot] = await database.getSnapshot();
898910
await snapshot.run(query);
899-
verifyQueryOptions('100');
911+
verifyQueryOptions(OPTIMIZER_VERSION, OPTIMIZER_STATISTICS_PACKAGE);
900912
await snapshot.end();
901913
} finally {
902914
await database.close();
@@ -907,14 +919,15 @@ describe('Spanner with mock server', () => {
907919
const query = {
908920
sql: selectSql,
909921
queryOptions: QueryOptions.create({
910-
optimizerVersion: '100',
922+
optimizerVersion: OPTIMIZER_VERSION,
923+
optimizerStatisticsPackage: OPTIMIZER_STATISTICS_PACKAGE,
911924
}),
912925
} as ExecuteSqlRequest;
913926
const database = newTestDatabase();
914927
database.runTransaction(async (err, transaction) => {
915928
assert.ifError(err);
916929
await transaction!.run(query);
917-
verifyQueryOptions('100');
930+
verifyQueryOptions(OPTIMIZER_VERSION, OPTIMIZER_STATISTICS_PACKAGE);
918931
await transaction!.commit();
919932
await database.close();
920933
done();
@@ -925,14 +938,15 @@ describe('Spanner with mock server', () => {
925938
const query = {
926939
sql: selectSql,
927940
queryOptions: QueryOptions.create({
928-
optimizerVersion: '100',
941+
optimizerVersion: OPTIMIZER_VERSION,
942+
optimizerStatisticsPackage: OPTIMIZER_STATISTICS_PACKAGE,
929943
}),
930944
} as ExecuteSqlRequest;
931945
const database = newTestDatabase();
932946
try {
933947
await database.runTransactionAsync(async transaction => {
934948
await transaction.run(query);
935-
verifyQueryOptions('100');
949+
verifyQueryOptions(OPTIMIZER_VERSION, OPTIMIZER_STATISTICS_PACKAGE);
936950
await transaction.commit();
937951
});
938952
} finally {
@@ -943,6 +957,8 @@ describe('Spanner with mock server', () => {
943957

944958
describe('with environment variable', () => {
945959
const OPTIMIZER_VERSION = '20';
960+
const OPTIMIZER_STATISTICS_PACKAGE = 'auto_20191128_14_47_22UTC';
961+
946962
let spannerWithEnvVar: Spanner;
947963
let instanceWithEnvVar: Instance;
948964

@@ -959,6 +975,7 @@ describe('Spanner with mock server', () => {
959975

960976
before(() => {
961977
process.env.SPANNER_OPTIMIZER_VERSION = OPTIMIZER_VERSION;
978+
process.env.SPANNER_OPTIMIZER_STATISTICS_PACKAGE = OPTIMIZER_STATISTICS_PACKAGE;
962979
spannerWithEnvVar = new Spanner({
963980
projectId: 'fake-project-id',
964981
servicePath: 'localhost',
@@ -971,13 +988,14 @@ describe('Spanner with mock server', () => {
971988

972989
after(() => {
973990
delete process.env.SPANNER_OPTIMIZER_VERSION;
991+
delete process.env.SPANNER_OPTIMIZER_STATISTICS_PACKAGE;
974992
});
975993

976994
it('database.run', async () => {
977995
const database = newTestDatabase();
978996
try {
979997
await database.run(selectSql);
980-
verifyQueryOptions(OPTIMIZER_VERSION);
998+
verifyQueryOptions(OPTIMIZER_VERSION, OPTIMIZER_STATISTICS_PACKAGE);
981999
} finally {
9821000
await database.close();
9831001
}
@@ -988,10 +1006,11 @@ describe('Spanner with mock server', () => {
9881006
// as they are overridden by the environment variable.
9891007
const database = newTestDatabase(undefined, {
9901008
optimizerVersion: 'version-in-db-opts',
1009+
optimizerStatisticsPackage: 'stats-package-in-db-opts',
9911010
});
9921011
try {
9931012
await database.run(selectSql);
994-
verifyQueryOptions(OPTIMIZER_VERSION);
1013+
verifyQueryOptions(OPTIMIZER_VERSION, OPTIMIZER_STATISTICS_PACKAGE);
9951014
} finally {
9961015
await database.close();
9971016
}
@@ -1004,9 +1023,10 @@ describe('Spanner with mock server', () => {
10041023
sql: selectSql,
10051024
queryOptions: {
10061025
optimizerVersion: 'version-on-query',
1026+
optimizerStatisticsPackage: 'stats-package-on-query',
10071027
},
10081028
});
1009-
verifyQueryOptions('version-on-query');
1029+
verifyQueryOptions('version-on-query', 'stats-package-on-query');
10101030
} finally {
10111031
await database.close();
10121032
}
@@ -1017,7 +1037,7 @@ describe('Spanner with mock server', () => {
10171037
try {
10181038
const [snapshot] = await database.getSnapshot();
10191039
await snapshot.run(selectSql);
1020-
verifyQueryOptions(OPTIMIZER_VERSION);
1040+
verifyQueryOptions(OPTIMIZER_VERSION, OPTIMIZER_STATISTICS_PACKAGE);
10211041
await snapshot.end();
10221042
} finally {
10231043
await database.close();
@@ -1032,9 +1052,10 @@ describe('Spanner with mock server', () => {
10321052
sql: selectSql,
10331053
queryOptions: {
10341054
optimizerVersion: 'version-on-query',
1055+
optimizerStatisticsPackage: 'stats-package-on-query',
10351056
},
10361057
});
1037-
verifyQueryOptions('version-on-query');
1058+
verifyQueryOptions('version-on-query', 'stats-package-on-query');
10381059
await snapshot.end();
10391060
} finally {
10401061
await database.close();
@@ -1044,11 +1065,12 @@ describe('Spanner with mock server', () => {
10441065
it('snapshot.run with database-with-query-options', async () => {
10451066
const database = newTestDatabase(undefined, {
10461067
optimizerVersion: 'version-in-db-opts',
1068+
optimizerStatisticsPackage: 'stats-package-in-db-opts',
10471069
});
10481070
try {
10491071
const [snapshot] = await database.getSnapshot();
10501072
await snapshot.run(selectSql);
1051-
verifyQueryOptions(OPTIMIZER_VERSION);
1073+
verifyQueryOptions(OPTIMIZER_VERSION, OPTIMIZER_STATISTICS_PACKAGE);
10521074
await snapshot.end();
10531075
} finally {
10541076
await database.close();
@@ -1060,7 +1082,7 @@ describe('Spanner with mock server', () => {
10601082
database.runTransaction(async (err, transaction) => {
10611083
assert.ifError(err);
10621084
await transaction!.run(selectSql);
1063-
verifyQueryOptions(OPTIMIZER_VERSION);
1085+
verifyQueryOptions(OPTIMIZER_VERSION, OPTIMIZER_STATISTICS_PACKAGE);
10641086
await transaction!.commit();
10651087
await database.close();
10661088
done();
@@ -1075,9 +1097,10 @@ describe('Spanner with mock server', () => {
10751097
sql: selectSql,
10761098
queryOptions: {
10771099
optimizerVersion: 'version-on-query',
1100+
optimizerStatisticsPackage: 'stats-package-on-query',
10781101
},
10791102
});
1080-
verifyQueryOptions('version-on-query');
1103+
verifyQueryOptions('version-on-query', 'stats-package-on-query');
10811104
await transaction!.commit();
10821105
await database.close();
10831106
done();
@@ -1087,11 +1110,12 @@ describe('Spanner with mock server', () => {
10871110
it('transaction.run with database-with-query-options', done => {
10881111
const database = newTestDatabase(undefined, {
10891112
optimizerVersion: 'version-in-db-opts',
1113+
optimizerStatisticsPackage: 'stats-package-in-db-opts',
10901114
});
10911115
database.runTransaction(async (err, transaction) => {
10921116
assert.ifError(err);
10931117
await transaction!.run(selectSql);
1094-
verifyQueryOptions(OPTIMIZER_VERSION);
1118+
verifyQueryOptions(OPTIMIZER_VERSION, OPTIMIZER_STATISTICS_PACKAGE);
10951119
await transaction!.commit();
10961120
await database.close();
10971121
done();
@@ -1103,7 +1127,7 @@ describe('Spanner with mock server', () => {
11031127
try {
11041128
await database.runTransactionAsync(async transaction => {
11051129
await transaction.run(selectSql);
1106-
verifyQueryOptions(OPTIMIZER_VERSION);
1130+
verifyQueryOptions(OPTIMIZER_VERSION, OPTIMIZER_STATISTICS_PACKAGE);
11071131
await transaction.commit();
11081132
});
11091133
} finally {
@@ -1119,9 +1143,10 @@ describe('Spanner with mock server', () => {
11191143
sql: selectSql,
11201144
queryOptions: {
11211145
optimizerVersion: 'version-on-query',
1146+
optimizerStatisticsPackage: 'stats-package-on-query',
11221147
},
11231148
});
1124-
verifyQueryOptions('version-on-query');
1149+
verifyQueryOptions('version-on-query', 'stats-package-on-query');
11251150
await transaction.commit();
11261151
});
11271152
} finally {
@@ -1132,11 +1157,12 @@ describe('Spanner with mock server', () => {
11321157
it('async transaction.run with database-with-query-options', async () => {
11331158
const database = newTestDatabase(undefined, {
11341159
optimizerVersion: 'version-in-db-opts',
1160+
optimizerStatisticsPackage: 'stats-package-in-db-opts',
11351161
});
11361162
try {
11371163
await database.runTransactionAsync(async transaction => {
11381164
await transaction.run(selectSql);
1139-
verifyQueryOptions(OPTIMIZER_VERSION);
1165+
verifyQueryOptions(OPTIMIZER_VERSION, OPTIMIZER_STATISTICS_PACKAGE);
11401166
await transaction.commit();
11411167
});
11421168
} finally {
@@ -1147,19 +1173,21 @@ describe('Spanner with mock server', () => {
11471173

11481174
describe('on database options', () => {
11491175
const OPTIMIZER_VERSION = '40';
1176+
const OPTIMIZER_STATISTICS_PACKAGE = 'auto_20191128_14_47_22UTC';
11501177

11511178
// Request a database with default query options.
11521179
function newTestDatabase(options?: SessionPoolOptions): Database {
11531180
return instance.database(`database-${dbCounter++}`, options, {
11541181
optimizerVersion: OPTIMIZER_VERSION,
1182+
optimizerStatisticsPackage: OPTIMIZER_STATISTICS_PACKAGE,
11551183
} as IQueryOptions);
11561184
}
11571185

11581186
it('database.run', async () => {
11591187
const database = newTestDatabase();
11601188
try {
11611189
await database.run(selectSql);
1162-
verifyQueryOptions(OPTIMIZER_VERSION);
1190+
verifyQueryOptions(OPTIMIZER_VERSION, OPTIMIZER_STATISTICS_PACKAGE);
11631191
} finally {
11641192
await database.close();
11651193
}
@@ -1170,7 +1198,7 @@ describe('Spanner with mock server', () => {
11701198
try {
11711199
const [snapshot] = await database.getSnapshot();
11721200
await snapshot.run(selectSql);
1173-
verifyQueryOptions(OPTIMIZER_VERSION);
1201+
verifyQueryOptions(OPTIMIZER_VERSION, OPTIMIZER_STATISTICS_PACKAGE);
11741202
await snapshot.end();
11751203
} finally {
11761204
await database.close();
@@ -1182,7 +1210,7 @@ describe('Spanner with mock server', () => {
11821210
database.runTransaction(async (err, transaction) => {
11831211
assert.ifError(err);
11841212
await transaction!.run(selectSql);
1185-
verifyQueryOptions(OPTIMIZER_VERSION);
1213+
verifyQueryOptions(OPTIMIZER_VERSION, OPTIMIZER_STATISTICS_PACKAGE);
11861214
await transaction!.commit();
11871215
await database.close();
11881216
done();
@@ -1194,7 +1222,7 @@ describe('Spanner with mock server', () => {
11941222
try {
11951223
await database.runTransactionAsync(async transaction => {
11961224
await transaction.run(selectSql);
1197-
verifyQueryOptions(OPTIMIZER_VERSION);
1225+
verifyQueryOptions(OPTIMIZER_VERSION, OPTIMIZER_STATISTICS_PACKAGE);
11981226
await transaction.commit();
11991227
});
12001228
} finally {

0 commit comments

Comments
 (0)