ARRAY 매개변수를 사용하여 데이터를 쿼리합니다.
코드 샘플
C#
Cloud Spanner용 클라이언트 라이브러리를 설치하고 사용하는 방법은 Cloud Spanner 클라이언트 라이브러리를 참조하세요.
string connectionString =
$"Data Source=projects/{projectId}/instances/"
+ $"{instanceId}/databases/{databaseId}";
// Create a list array of dates to use for querying.
var exampleArray = new List<DateTime>();
exampleArray.InsertRange(0, new DateTime[] {
DateTime.Parse("2020-10-01"),
DateTime.Parse("2020-11-01")
});
// Create connection to Cloud Spanner.
using (var connection = new SpannerConnection(connectionString))
{
var cmd = connection.CreateSelectCommand(
"SELECT VenueId, VenueName, AvailableDate FROM Venues v, "
+ "UNNEST(v.AvailableDates) as AvailableDate "
+ "WHERE AvailableDate in UNNEST(@ExampleArray)");
cmd.Parameters.Add("ExampleArray",
SpannerDbType.ArrayOf(SpannerDbType.Date), exampleArray);
using (var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
Console.WriteLine(
reader.GetFieldValue<string>("VenueId")
+ " " + reader.GetFieldValue<string>("VenueName")
+ " " +
reader.GetFieldValue<string>("AvailableDate"));
}
}
}C++
Cloud Spanner용 클라이언트 라이브러리를 설치하고 사용하는 방법은 Cloud Spanner 클라이언트 라이브러리를 참조하세요.
void QueryWithArrayParameter(google::cloud::spanner::Client client) {
namespace spanner = ::google::cloud::spanner;
std::vector<absl::CivilDay> example_array = {absl::CivilDay(2020, 10, 1),
absl::CivilDay(2020, 11, 1)};
spanner::SqlStatement select(
"SELECT VenueId, VenueName, AvailableDate FROM Venues v,"
" UNNEST(v.AvailableDates) as AvailableDate "
" WHERE AvailableDate in UNNEST(@available_dates)",
{{"available_dates", spanner::Value(example_array)}});
using RowType = std::tuple<std::int64_t, absl::optional<std::string>,
absl::optional<absl::CivilDay>>;
auto rows = client.ExecuteQuery(std::move(select));
for (auto const& row : spanner::StreamOf<RowType>(rows)) {
if (!row) throw std::runtime_error(row.status().message());
std::cout << "VenueId: " << std::get<0>(*row) << "\t";
std::cout << "VenueName: " << std::get<1>(*row).value() << "\t";
std::cout << "AvailableDate: " << std::get<2>(*row).value() << "\n";
}
std::cout << "Query completed for [spanner_query_with_array_parameter]\n";
}Go
Cloud Spanner용 클라이언트 라이브러리를 설치하고 사용하는 방법은 Cloud Spanner 클라이언트 라이브러리를 참조하세요.
import (
"context"
"fmt"
"io"
"time"
"cloud.google.com/go/civil"
"cloud.google.com/go/spanner"
"google.golang.org/api/iterator"
)
func queryWithArray(w io.Writer, db string) error {
ctx := context.Background()
client, err := spanner.NewClient(ctx, db)
if err != nil {
return err
}
defer client.Close()
var date1 = civil.Date{Year: 2020, Month: time.October, Day: 1}
var date2 = civil.Date{Year: 2020, Month: time.November, Day: 1}
var exampleArray = []civil.Date{date1, date2}
stmt := spanner.Statement{
SQL: `SELECT VenueId, VenueName, AvailableDate FROM Venues v,
UNNEST(v.AvailableDates) as AvailableDate
WHERE AvailableDate IN UNNEST(@availableDates)`,
Params: map[string]interface{}{
"availableDates": exampleArray,
},
}
iter := client.Single().Query(ctx, stmt)
defer iter.Stop()
for {
row, err := iter.Next()
if err == iterator.Done {
return nil
}
if err != nil {
return err
}
var venueID int64
var venueName string
var availableDate civil.Date
if err := row.Columns(&venueID, &venueName, &availableDate); err != nil {
return err
}
fmt.Fprintf(w, "%d %s %s\n", venueID, venueName, availableDate)
}
}
자바
Cloud Spanner용 클라이언트 라이브러리를 설치하고 사용하는 방법은 Cloud Spanner 클라이언트 라이브러리를 참조하세요.
static void queryWithArray(DatabaseClient dbClient) {
Value exampleArray =
Value.dateArray(Arrays.asList(Date.parseDate("2020-10-01"), Date.parseDate("2020-11-01")));
Statement statement =
Statement.newBuilder(
"SELECT VenueId, VenueName, AvailableDate FROM Venues v, "
+ "UNNEST(v.AvailableDates) as AvailableDate "
+ "WHERE AvailableDate in UNNEST(@availableDates)")
.bind("availableDates")
.to(exampleArray)
.build();
try (ResultSet resultSet = dbClient.singleUse().executeQuery(statement)) {
while (resultSet.next()) {
System.out.printf(
"%d %s %s\n",
resultSet.getLong("VenueId"),
resultSet.getString("VenueName"),
resultSet.getDate("AvailableDate"));
}
}
}Node.js
Cloud Spanner용 클라이언트 라이브러리를 설치하고 사용하는 방법은 Cloud Spanner 클라이언트 라이브러리를 참조하세요.
// 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.
const spanner = new Spanner({
projectId: projectId,
});
// Gets a reference to a Cloud Spanner instance and database.
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);
const fieldType = {
type: 'date',
};
const parentFieldType = {
type: 'array',
child: fieldType,
};
const exampleArray = ['2020-10-01', '2020-11-01'];
const query = {
sql: `SELECT VenueId, VenueName, AvailableDate FROM Venues v,
UNNEST(v.AvailableDates) as AvailableDate
WHERE AvailableDate in UNNEST(@availableDates)`,
params: {
availableDates: exampleArray,
},
types: {
availableDates: parentFieldType,
},
};
// Queries rows from the Venues table.
try {
const [rows] = await database.run(query);
rows.forEach(row => {
const availableDate = row[2]['value'];
const json = row.toJSON();
console.log(
`VenueId: ${json.VenueId}, VenueName: ${
json.VenueName
}, AvailableDate: ${JSON.stringify(availableDate).substring(1, 11)}`
);
});
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
database.close();
}PHP
Cloud Spanner용 클라이언트 라이브러리를 설치하고 사용하는 방법은 Cloud Spanner 클라이언트 라이브러리를 참조하세요.
use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Database;
use Google\Cloud\Spanner\Date;
/**
* Queries sample data from the database using SQL with an ARRAY parameter.
* Example:
* ```
* query_data_with_array_parameter($instanceId, $databaseId);
* ```
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function query_data_with_array_parameter($instanceId, $databaseId)
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$exampleArray = [
new Date(new \DateTime('2020-10-01')),
new Date(new \DateTime('2020-11-01'))
];
$results = $database->execute(
'SELECT VenueId, VenueName, AvailableDate FROM Venues v, ' .
'UNNEST(v.AvailableDates) as AvailableDate ' .
'WHERE AvailableDate in UNNEST(@availableDates)',
[
'parameters' => [
'availableDates' => $exampleArray
]
]
);
foreach ($results as $row) {
printf('VenueId: %s, VenueName: %s, AvailableDate: %s' . PHP_EOL,
$row['VenueId'], $row['VenueName'], $row['AvailableDate']);
}
}Python
Cloud Spanner용 클라이언트 라이브러리를 설치하고 사용하는 방법은 Cloud Spanner 클라이언트 라이브러리를 참조하세요.
# 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)
exampleArray = ["2020-10-01", "2020-11-01"]
param = {"available_dates": exampleArray}
param_type = {"available_dates": param_types.Array(param_types.DATE)}
with database.snapshot() as snapshot:
results = snapshot.execute_sql(
"SELECT VenueId, VenueName, AvailableDate FROM Venues v,"
"UNNEST(v.AvailableDates) as AvailableDate "
"WHERE AvailableDate in UNNEST(@available_dates)",
params=param,
param_types=param_type,
)
for row in results:
print(u"VenueId: {}, VenueName: {}, AvailableDate: {}".format(*row))Ruby
Cloud Spanner용 클라이언트 라이브러리를 설치하고 사용하는 방법은 Cloud Spanner 클라이언트 라이브러리를 참조하세요.
# 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.new project: project_id
client = spanner.client instance_id, database_id
sql_query = "SELECT VenueId, VenueName, AvailableDate FROM Venues v,
UNNEST(v.AvailableDates) as AvailableDate
WHERE AvailableDate in UNNEST(@available_dates)"
params = { available_dates: ["2020-10-01", "2020-11-01"] }
param_types = { available_dates: [:DATE] }
client.execute(sql_query, params: params, types: param_types).rows.each do |row|
puts "#{row[:VenueId]} #{row[:VenueName]} #{row[:AvailableDate]}"
end다음 단계
다른 Google Cloud 제품의 코드 샘플을 검색하고 필터링하려면 Google Cloud 샘플 브라우저를 참조하세요.