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

Commit dadc6dc

Browse files
skuruppuskuruppu
authored
feat: support setting optimizerStatisticsPackage (#1225)
`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 b18be4b commit dadc6dc

3 files changed

Lines changed: 67 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
@@ -388,6 +388,10 @@ class Database extends common.GrpcServiceObject {
388388
if (process.env.SPANNER_OPTIMIZER_VERSION) {
389389
options.optimizerVersion = process.env.SPANNER_OPTIMIZER_VERSION;
390390
}
391+
if (process.env.SPANNER_OPTIMIZER_STATISTICS_PACKAGE) {
392+
options.optimizerStatisticsPackage =
393+
process.env.SPANNER_OPTIMIZER_STATISTICS_PACKAGE;
394+
}
391395
return options;
392396
}
393397

‎test/spanner.ts‎

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

945945
describe('queryOptions', () => {
946946
/** Common verify method for QueryOptions tests. */
947-
function verifyQueryOptions(optimizerVersion: string) {
947+
function verifyQueryOptions(
948+
optimizerVersion: string,
949+
optimizerStatisticsPackage: string
950+
) {
948951
const request = spannerMock.getRequests().find(val => {
949952
return (val as v1.ExecuteSqlRequest).sql;
950953
}) as v1.ExecuteSqlRequest;
@@ -957,20 +960,28 @@ describe('Spanner with mock server', () => {
957960
request.queryOptions!.optimizerVersion,
958961
optimizerVersion
959962
);
963+
assert.strictEqual(
964+
request.queryOptions!.optimizerStatisticsPackage,
965+
optimizerStatisticsPackage
966+
);
960967
}
961968

962969
describe('on request', () => {
970+
const OPTIMIZER_VERSION = '100';
971+
const OPTIMIZER_STATISTICS_PACKAGE = 'auto_20191128_14_47_22UTC';
972+
963973
it('database.run', async () => {
964974
const query = {
965975
sql: selectSql,
966976
queryOptions: QueryOptions.create({
967-
optimizerVersion: '100',
977+
optimizerVersion: OPTIMIZER_VERSION,
978+
optimizerStatisticsPackage: OPTIMIZER_STATISTICS_PACKAGE,
968979
}),
969980
} as ExecuteSqlRequest;
970981
const database = newTestDatabase();
971982
try {
972983
await database.run(query);
973-
verifyQueryOptions('100');
984+
verifyQueryOptions(OPTIMIZER_VERSION, OPTIMIZER_STATISTICS_PACKAGE);
974985
} finally {
975986
await database.close();
976987
}
@@ -980,14 +991,15 @@ describe('Spanner with mock server', () => {
980991
const query = {
981992
sql: selectSql,
982993
queryOptions: QueryOptions.create({
983-
optimizerVersion: '100',
994+
optimizerVersion: OPTIMIZER_VERSION,
995+
optimizerStatisticsPackage: OPTIMIZER_STATISTICS_PACKAGE,
984996
}),
985997
} as ExecuteSqlRequest;
986998
const database = newTestDatabase();
987999
try {
9881000
const [snapshot] = await database.getSnapshot();
9891001
await snapshot.run(query);
990-
verifyQueryOptions('100');
1002+
verifyQueryOptions(OPTIMIZER_VERSION, OPTIMIZER_STATISTICS_PACKAGE);
9911003
await snapshot.end();
9921004
} finally {
9931005
await database.close();
@@ -998,14 +1010,15 @@ describe('Spanner with mock server', () => {
9981010
const query = {
9991011
sql: selectSql,
10001012
queryOptions: QueryOptions.create({
1001-
optimizerVersion: '100',
1013+
optimizerVersion: OPTIMIZER_VERSION,
1014+
optimizerStatisticsPackage: OPTIMIZER_STATISTICS_PACKAGE,
10021015
}),
10031016
} as ExecuteSqlRequest;
10041017
const database = newTestDatabase();
10051018
database.runTransaction(async (err, transaction) => {
10061019
assert.ifError(err);
10071020
await transaction!.run(query);
1008-
verifyQueryOptions('100');
1021+
verifyQueryOptions(OPTIMIZER_VERSION, OPTIMIZER_STATISTICS_PACKAGE);
10091022
await transaction!.commit();
10101023
await database.close();
10111024
done();
@@ -1016,14 +1029,15 @@ describe('Spanner with mock server', () => {
10161029
const query = {
10171030
sql: selectSql,
10181031
queryOptions: QueryOptions.create({
1019-
optimizerVersion: '100',
1032+
optimizerVersion: OPTIMIZER_VERSION,
1033+
optimizerStatisticsPackage: OPTIMIZER_STATISTICS_PACKAGE,
10201034
}),
10211035
} as ExecuteSqlRequest;
10221036
const database = newTestDatabase();
10231037
try {
10241038
await database.runTransactionAsync(async transaction => {
10251039
await transaction.run(query);
1026-
verifyQueryOptions('100');
1040+
verifyQueryOptions(OPTIMIZER_VERSION, OPTIMIZER_STATISTICS_PACKAGE);
10271041
await transaction.commit();
10281042
});
10291043
} finally {
@@ -1034,6 +1048,8 @@ describe('Spanner with mock server', () => {
10341048

10351049
describe('with environment variable', () => {
10361050
const OPTIMIZER_VERSION = '20';
1051+
const OPTIMIZER_STATISTICS_PACKAGE = 'auto_20191128_14_47_22UTC';
1052+
10371053
let spannerWithEnvVar: Spanner;
10381054
let instanceWithEnvVar: Instance;
10391055

@@ -1050,6 +1066,8 @@ describe('Spanner with mock server', () => {
10501066

10511067
before(() => {
10521068
process.env.SPANNER_OPTIMIZER_VERSION = OPTIMIZER_VERSION;
1069+
process.env.SPANNER_OPTIMIZER_STATISTICS_PACKAGE =
1070+
OPTIMIZER_STATISTICS_PACKAGE;
10531071
spannerWithEnvVar = new Spanner({
10541072
projectId: 'fake-project-id',
10551073
servicePath: 'localhost',
@@ -1062,13 +1080,14 @@ describe('Spanner with mock server', () => {
10621080

10631081
after(() => {
10641082
delete process.env.SPANNER_OPTIMIZER_VERSION;
1083+
delete process.env.SPANNER_OPTIMIZER_STATISTICS_PACKAGE;
10651084
});
10661085

10671086
it('database.run', async () => {
10681087
const database = newTestDatabase();
10691088
try {
10701089
await database.run(selectSql);
1071-
verifyQueryOptions(OPTIMIZER_VERSION);
1090+
verifyQueryOptions(OPTIMIZER_VERSION, OPTIMIZER_STATISTICS_PACKAGE);
10721091
} finally {
10731092
await database.close();
10741093
}
@@ -1079,10 +1098,11 @@ describe('Spanner with mock server', () => {
10791098
// as they are overridden by the environment variable.
10801099
const database = newTestDatabase(undefined, {
10811100
optimizerVersion: 'version-in-db-opts',
1101+
optimizerStatisticsPackage: 'stats-package-in-db-opts',
10821102
});
10831103
try {
10841104
await database.run(selectSql);
1085-
verifyQueryOptions(OPTIMIZER_VERSION);
1105+
verifyQueryOptions(OPTIMIZER_VERSION, OPTIMIZER_STATISTICS_PACKAGE);
10861106
} finally {
10871107
await database.close();
10881108
}
@@ -1095,9 +1115,10 @@ describe('Spanner with mock server', () => {
10951115
sql: selectSql,
10961116
queryOptions: {
10971117
optimizerVersion: 'version-on-query',
1118+
optimizerStatisticsPackage: 'stats-package-on-query',
10981119
},
10991120
});
1100-
verifyQueryOptions('version-on-query');
1121+
verifyQueryOptions('version-on-query', 'stats-package-on-query');
11011122
} finally {
11021123
await database.close();
11031124
}
@@ -1108,7 +1129,7 @@ describe('Spanner with mock server', () => {
11081129
try {
11091130
const [snapshot] = await database.getSnapshot();
11101131
await snapshot.run(selectSql);
1111-
verifyQueryOptions(OPTIMIZER_VERSION);
1132+
verifyQueryOptions(OPTIMIZER_VERSION, OPTIMIZER_STATISTICS_PACKAGE);
11121133
await snapshot.end();
11131134
} finally {
11141135
await database.close();
@@ -1123,9 +1144,10 @@ describe('Spanner with mock server', () => {
11231144
sql: selectSql,
11241145
queryOptions: {
11251146
optimizerVersion: 'version-on-query',
1147+
optimizerStatisticsPackage: 'stats-package-on-query',
11261148
},
11271149
});
1128-
verifyQueryOptions('version-on-query');
1150+
verifyQueryOptions('version-on-query', 'stats-package-on-query');
11291151
await snapshot.end();
11301152
} finally {
11311153
await database.close();
@@ -1135,11 +1157,12 @@ describe('Spanner with mock server', () => {
11351157
it('snapshot.run with database-with-query-options', async () => {
11361158
const database = newTestDatabase(undefined, {
11371159
optimizerVersion: 'version-in-db-opts',
1160+
optimizerStatisticsPackage: 'stats-package-in-db-opts',
11381161
});
11391162
try {
11401163
const [snapshot] = await database.getSnapshot();
11411164
await snapshot.run(selectSql);
1142-
verifyQueryOptions(OPTIMIZER_VERSION);
1165+
verifyQueryOptions(OPTIMIZER_VERSION, OPTIMIZER_STATISTICS_PACKAGE);
11431166
await snapshot.end();
11441167
} finally {
11451168
await database.close();
@@ -1151,7 +1174,7 @@ describe('Spanner with mock server', () => {
11511174
database.runTransaction(async (err, transaction) => {
11521175
assert.ifError(err);
11531176
await transaction!.run(selectSql);
1154-
verifyQueryOptions(OPTIMIZER_VERSION);
1177+
verifyQueryOptions(OPTIMIZER_VERSION, OPTIMIZER_STATISTICS_PACKAGE);
11551178
await transaction!.commit();
11561179
await database.close();
11571180
done();
@@ -1166,9 +1189,10 @@ describe('Spanner with mock server', () => {
11661189
sql: selectSql,
11671190
queryOptions: {
11681191
optimizerVersion: 'version-on-query',
1192+
optimizerStatisticsPackage: 'stats-package-on-query',
11691193
},
11701194
});
1171-
verifyQueryOptions('version-on-query');
1195+
verifyQueryOptions('version-on-query', 'stats-package-on-query');
11721196
await transaction!.commit();
11731197
await database.close();
11741198
done();
@@ -1178,11 +1202,12 @@ describe('Spanner with mock server', () => {
11781202
it('transaction.run with database-with-query-options', done => {
11791203
const database = newTestDatabase(undefined, {
11801204
optimizerVersion: 'version-in-db-opts',
1205+
optimizerStatisticsPackage: 'stats-package-in-db-opts',
11811206
});
11821207
database.runTransaction(async (err, transaction) => {
11831208
assert.ifError(err);
11841209
await transaction!.run(selectSql);
1185-
verifyQueryOptions(OPTIMIZER_VERSION);
1210+
verifyQueryOptions(OPTIMIZER_VERSION, OPTIMIZER_STATISTICS_PACKAGE);
11861211
await transaction!.commit();
11871212
await database.close();
11881213
done();
@@ -1194,7 +1219,7 @@ describe('Spanner with mock server', () => {
11941219
try {
11951220
await database.runTransactionAsync(async transaction => {
11961221
await transaction.run(selectSql);
1197-
verifyQueryOptions(OPTIMIZER_VERSION);
1222+
verifyQueryOptions(OPTIMIZER_VERSION, OPTIMIZER_STATISTICS_PACKAGE);
11981223
await transaction.commit();
11991224
});
12001225
} finally {
@@ -1210,9 +1235,10 @@ describe('Spanner with mock server', () => {
12101235
sql: selectSql,
12111236
queryOptions: {
12121237
optimizerVersion: 'version-on-query',
1238+
optimizerStatisticsPackage: 'stats-package-on-query',
12131239
},
12141240
});
1215-
verifyQueryOptions('version-on-query');
1241+
verifyQueryOptions('version-on-query', 'stats-package-on-query');
12161242
await transaction.commit();
12171243
});
12181244
} finally {
@@ -1223,11 +1249,12 @@ describe('Spanner with mock server', () => {
12231249
it('async transaction.run with database-with-query-options', async () => {
12241250
const database = newTestDatabase(undefined, {
12251251
optimizerVersion: 'version-in-db-opts',
1252+
optimizerStatisticsPackage: 'stats-package-in-db-opts',
12261253
});
12271254
try {
12281255
await database.runTransactionAsync(async transaction => {
12291256
await transaction.run(selectSql);
1230-
verifyQueryOptions(OPTIMIZER_VERSION);
1257+
verifyQueryOptions(OPTIMIZER_VERSION, OPTIMIZER_STATISTICS_PACKAGE);
12311258
await transaction.commit();
12321259
});
12331260
} finally {
@@ -1238,19 +1265,21 @@ describe('Spanner with mock server', () => {
12381265

12391266
describe('on database options', () => {
12401267
const OPTIMIZER_VERSION = '40';
1268+
const OPTIMIZER_STATISTICS_PACKAGE = 'auto_20191128_14_47_22UTC';
12411269

12421270
// Request a database with default query options.
12431271
function newTestDatabase(options?: SessionPoolOptions): Database {
12441272
return instance.database(`database-${dbCounter++}`, options, {
12451273
optimizerVersion: OPTIMIZER_VERSION,
1274+
optimizerStatisticsPackage: OPTIMIZER_STATISTICS_PACKAGE,
12461275
} as IQueryOptions);
12471276
}
12481277

12491278
it('database.run', async () => {
12501279
const database = newTestDatabase();
12511280
try {
12521281
await database.run(selectSql);
1253-
verifyQueryOptions(OPTIMIZER_VERSION);
1282+
verifyQueryOptions(OPTIMIZER_VERSION, OPTIMIZER_STATISTICS_PACKAGE);
12541283
} finally {
12551284
await database.close();
12561285
}
@@ -1261,7 +1290,7 @@ describe('Spanner with mock server', () => {
12611290
try {
12621291
const [snapshot] = await database.getSnapshot();
12631292
await snapshot.run(selectSql);
1264-
verifyQueryOptions(OPTIMIZER_VERSION);
1293+
verifyQueryOptions(OPTIMIZER_VERSION, OPTIMIZER_STATISTICS_PACKAGE);
12651294
await snapshot.end();
12661295
} finally {
12671296
await database.close();
@@ -1273,7 +1302,7 @@ describe('Spanner with mock server', () => {
12731302
database.runTransaction(async (err, transaction) => {
12741303
assert.ifError(err);
12751304
await transaction!.run(selectSql);
1276-
verifyQueryOptions(OPTIMIZER_VERSION);
1305+
verifyQueryOptions(OPTIMIZER_VERSION, OPTIMIZER_STATISTICS_PACKAGE);
12771306
await transaction!.commit();
12781307
await database.close();
12791308
done();
@@ -1285,7 +1314,7 @@ describe('Spanner with mock server', () => {
12851314
try {
12861315
await database.runTransactionAsync(async transaction => {
12871316
await transaction.run(selectSql);
1288-
verifyQueryOptions(OPTIMIZER_VERSION);
1317+
verifyQueryOptions(OPTIMIZER_VERSION, OPTIMIZER_STATISTICS_PACKAGE);
12891318
await transaction.commit();
12901319
});
12911320
} finally {

0 commit comments

Comments
 (0)