Query with query options

voidQueryWithQueryOptions(google::cloud::spanner::Clientclient){
namespacespanner=::google::cloud::spanner;
autosql=spanner::SqlStatement("SELECT SingerId, FirstName FROM Singers");
autoopts=
google::cloud::Options{}
.set<spanner::QueryOptimizerVersionOption>("1")
.set<spanner::QueryOptimizerStatisticsPackageOption>("latest");
autorows=client.ExecuteQuery(std::move(sql),std::move(opts));
usingRowType=std::tuple<std::int64_t,std::string>;
for(auto&row:spanner::StreamOf<RowType>(rows)){
if(!row)throwstd::move(row).status();
std::cout << "SingerId: " << std::get<0>(*row) << "\t";
std::cout << "FirstName: " << std::get<1>(*row) << "\n";
}
std::cout << "Read completed for [spanner_query_with_query_options]\n";
}

usingGoogle.Cloud.Spanner.Data ;
usingSystem.Collections.Generic;
usingSystem.Threading.Tasks;
publicclassRunCommandWithQueryOptionsAsyncSample
{
publicclassAlbum
{
publicintSingerId{get;set;}
publicintAlbumId{get;set;}
publicstringAlbumTitle{get;set;}
}
publicasyncTask<List<Album>>RunCommandWithQueryOptionsAsync(stringprojectId,stringinstanceId,stringdatabaseId)
{
varconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
usingvarconnection=newSpannerConnection (connectionString);
usingvarcmd=connection.CreateSelectCommand ("SELECT SingerId, AlbumId, AlbumTitle FROM Albums");
cmd.QueryOptions =QueryOptions .Empty
.WithOptimizerVersion ("1")
// The list of available statistics packages for the database can
// be found by querying the "INFORMATION_SCHEMA.SPANNER_STATISTICS"
// table.
.WithOptimizerStatisticsPackage ("latest");
varalbums=newList<Album>();
usingvarreader=awaitcmd.ExecuteReaderAsync ();
while(awaitreader.ReadAsync ())
{
albums.Add(newAlbum()
{
AlbumId=reader.GetFieldValue<int>("AlbumId"),
SingerId=reader.GetFieldValue<int>("SingerId"),
AlbumTitle=reader.GetFieldValue<string>("AlbumTitle")
});
}
returnalbums;
}
}

import(
"context"
"fmt"
"io"
"time"
"cloud.google.com/go/spanner"
sppb"cloud.google.com/go/spanner/apiv1/spannerpb"
"google.golang.org/api/iterator"
)
funcqueryWithQueryOptions(wio.Writer,dbstring)error{
ctx:=context.Background()
client,err:=spanner.NewClient(ctx,db)
iferr!=nil{
returnerr
}
deferclient.Close()
stmt:=spanner.Statement {SQL:`SELECT VenueId, VenueName, LastUpdateTime FROM Venues`}
queryOptions:=spanner.QueryOptions {
Options:&sppb.ExecuteSqlRequest_QueryOptions{
OptimizerVersion:"1",
// The list of available statistics packages can be found by
// querying the "INFORMATION_SCHEMA.SPANNER_STATISTICS" table.
OptimizerStatisticsPackage:"latest",
},
}
iter:=client.Single ().QueryWithOptions(ctx,stmt,queryOptions)
deferiter.Stop()
for{
row,err:=iter.Next()
iferr==iterator.Done{
returnnil
}
iferr!=nil{
returnerr
}
varvenueIDint64
varvenueNamestring
varlastUpdateTimetime.Time
iferr:=row.Columns (&venueID,&venueName,&lastUpdateTime);err!=nil{
returnerr
}
fmt.Fprintf(w,"%d %s %s\n",venueID,venueName,lastUpdateTime)
}
}

staticvoidqueryWithQueryOptions(DatabaseClientdbClient){
try(ResultSetresultSet=
dbClient
.singleUse()
.executeQuery(
Statement
.newBuilder("SELECT SingerId, AlbumId, AlbumTitle FROM Albums")
.withQueryOptions(QueryOptions
.newBuilder()
.setOptimizerVersion("1")
// The list of available statistics packages can be found by querying the
// "INFORMATION_SCHEMA.SPANNER_STATISTICS" table.
.setOptimizerStatisticsPackage("latest")
.build())
.build())){
while(resultSet.next()){
System.out.printf(
"%d %d %s\n",resultSet.getLong(0),resultSet.getLong(1),resultSet.getString(2));
}
}
}

// Imports the Google Cloud client library
const{Spanner}=require('@google-cloud/spanner');
/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// Creates a client
constspanner=newSpanner ({
projectId:projectId,
});
// Gets a reference to a Cloud Spanner instance and database
constinstance=spanner.instance(instanceId);
constdatabase=instance.database(databaseId);
constquery={
sql:`SELECT AlbumId, AlbumTitle, MarketingBudget
 FROM Albums
 ORDER BY AlbumTitle`,
queryOptions:{
optimizerVersion:'latest',
// The list of available statistics packages can be found by querying the
// "INFORMATION_SCHEMA.SPANNER_STATISTICS" table.
optimizerStatisticsPackage:'latest',
},
};
// Queries rows from the Albums table
try{
const[rows]=awaitdatabase.run(query);
rows.forEach(row=>{
constjson=row.toJSON();
constmarketingBudget=json.MarketingBudget
?json.MarketingBudget
:null;// This value is nullable
console.log(
`AlbumId: ${json.AlbumId}, AlbumTitle: ${json.AlbumTitle}, MarketingBudget: ${marketingBudget}`,
);
});
}catch(err){
console.error('ERROR:',err);
}finally{
// Close the database when finished.
database.close();
}

use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Database;
/**
 * Queries sample data using SQL with query options.
 * Example:
 * ```
 * query_data_with_query_options($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function query_data_with_query_options(string $instanceId, string $databaseId): void
{
 $spanner = new SpannerClient();
 $instance = $spanner->instance($instanceId);
 $database = $instance->database($databaseId);
 $results = $database->execute(
 'SELECT VenueId, VenueName, LastUpdateTime FROM Venues',
 [
 'queryOptions' => [
 'optimizerVersion' => '1',
 // Pin the statistics package to the latest version just for
 // this query.
 'optimizerStatisticsPackage' => 'latest'
 ]
 ]
 );
 foreach ($results as $row) {
 printf('VenueId: %s, VenueName: %s, LastUpdateTime: %s' . PHP_EOL,
 $row['VenueId'], $row['VenueName'], $row['LastUpdateTime']);
 }
}

# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
with database.snapshot() as snapshot:
 results = snapshot.execute_sql(
 "SELECT VenueId, VenueName, LastUpdateTime FROM Venues",
 query_options={
 "optimizer_version": "1",
 "optimizer_statistics_package": "latest",
 },
 )
 for row in results:
 print("VenueId: {}, VenueName: {}, LastUpdateTime: {}".format(*row))

# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require"google/cloud/spanner"
spanner=Google::Cloud::Spanner .newproject:project_id
client=spanner.client instance_id,database_id
sql_query="SELECT VenueId, VenueName, LastUpdateTime FROM Venues"
query_options={
optimizer_version:"1",
# The list of available statistics packagebs can be
# found by querying the "INFORMATION_SCHEMA.SPANNER_STATISTICS"
# table.
optimizer_statistics_package:"latest"
}
client.execute(sql_query,query_options:query_options).rows.each do|row|
puts"#{row[:VenueId]}#{row[:VenueName]}#{row[:LastUpdateTime]}"
end