Insert data into a table with example data types by using mutations.
Code sample
C++
To learn how to install and use the client library for Spanner, see Spanner client libraries .
To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment .
void
InsertDatatypesData
(
google
::
cloud
::
spanner
::
Client
client
)
{
namespace
spanner
=
::
google
::
cloud
::
spanner
;
std
::
vector<absl
::
CivilDay
>
available_dates1
=
{
absl
::
CivilDay
(
2020
,
12
,
1
),
absl
::
CivilDay
(
2020
,
12
,
2
),
absl
::
CivilDay
(
2020
,
12
,
3
)};
std
::
vector<absl
::
CivilDay
>
available_dates2
=
{
absl
::
CivilDay
(
2020
,
11
,
1
),
absl
::
CivilDay
(
2020
,
11
,
5
),
absl
::
CivilDay
(
2020
,
11
,
15
)};
std
::
vector<absl
::
CivilDay
>
available_dates3
=
{
absl
::
CivilDay
(
2020
,
10
,
1
),
absl
::
CivilDay
(
2020
,
10
,
7
)};
auto
insert_venues
=
spanner
::
InsertMutationBuilder
(
"Venues"
,
{
"VenueId"
,
"VenueName"
,
"VenueInfo"
,
"Capacity"
,
"AvailableDates"
,
"LastContactDate"
,
"OutdoorVenue"
,
"PopularityScore"
,
"LastUpdateTime"
})
.
EmplaceRow
(
4
,
"Venue 4"
,
spanner
::
Bytes
(
"Hello World 1"
),
1800
,
available_dates1
,
absl
::
CivilDay
(
2018
,
9
,
2
),
false
,
0.85543
,
spanner
::
CommitTimestamp
())
.
EmplaceRow
(
19
,
"Venue 19"
,
spanner
::
Bytes
(
"Hello World 2"
),
6300
,
available_dates2
,
absl
::
CivilDay
(
2019
,
1
,
15
),
true
,
0.98716
,
spanner
::
CommitTimestamp
())
.
EmplaceRow
(
42
,
"Venue 42"
,
spanner
::
Bytes
(
"Hello World 3"
),
3000
,
available_dates3
,
absl
::
CivilDay
(
2018
,
10
,
1
),
false
,
0.72598
,
spanner
::
CommitTimestamp
())
.
Build
();
auto
commit_result
=
client
.
Commit
(
spanner
::
Mutations
{
insert_venues
});
if
(
!
commit_result
)
throw
std
::
move
(
commit_result
).
status
();
std
::
cout
<<
"Insert was successful [spanner_insert_datatypes_data]
\n
"
;
}
C#
To learn how to install and use the client library for Spanner, see Spanner client libraries .
To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment .
using
Google.Cloud.Spanner.Data
;
using
System
;
using
System.Collections.Generic
;
using
System.Linq
;
using
System.Text
;
using
System.Threading.Tasks
;
public
class
InsertDataTypesDataAsyncSample
{
public
class
Venue
{
public
int
VenueId
{
get
;
set
;
}
public
string
VenueName
{
get
;
set
;
}
public
byte
[]
VenueInfo
{
get
;
set
;
}
public
int
Capacity
{
get
;
set
;
}
public
List<DateTime>
AvailableDates
{
get
;
set
;
}
public
DateTime
LastContactDate
{
get
;
set
;
}
public
bool
OutdoorVenue
{
get
;
set
;
}
public
float
PopularityScore
{
get
;
set
;
}
}
public
async
Task
InsertDataTypesDataAsync
(
string
projectId
,
string
instanceId
,
string
databaseId
)
{
string
connectionString
=
$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}"
;
byte
[]
exampleBytes1
=
Encoding
.
UTF8
.
GetBytes
(
"Hello World 1"
);
byte
[]
exampleBytes2
=
Encoding
.
UTF8
.
GetBytes
(
"Hello World 2"
);
byte
[]
exampleBytes3
=
Encoding
.
UTF8
.
GetBytes
(
"Hello World 3"
);
var
availableDates1
=
new
List<DateTime>
{
DateTime
.
Parse
(
"2020-12-01"
),
DateTime
.
Parse
(
"2020-12-02"
),
DateTime
.
Parse
(
"2020-12-03"
)
};
var
availableDates2
=
new
List<DateTime>
{
DateTime
.
Parse
(
"2020-11-01"
),
DateTime
.
Parse
(
"2020-11-05"
),
DateTime
.
Parse
(
"2020-11-15"
)
};
var
availableDates3
=
new
List<DateTime>
{
DateTime
.
Parse
(
"2020-10-01"
),
DateTime
.
Parse
(
"2020-10-07"
)
};
List<Venue>
venues
=
new
List<Venue>
{
new
Venue
{
VenueId
=
4
,
VenueName
=
"Venue 4"
,
VenueInfo
=
exampleBytes1
,
Capacity
=
1800
,
AvailableDates
=
availableDates1
,
LastContactDate
=
DateTime
.
Parse
(
"2018-09-02"
),
OutdoorVenue
=
false
,
PopularityScore
=
0.85543f
},
new
Venue
{
VenueId
=
19
,
VenueName
=
"Venue 19"
,
VenueInfo
=
exampleBytes2
,
Capacity
=
6300
,
AvailableDates
=
availableDates2
,
LastContactDate
=
DateTime
.
Parse
(
"2019-01-15"
),
OutdoorVenue
=
true
,
PopularityScore
=
0.98716f
},
new
Venue
{
VenueId
=
42
,
VenueName
=
"Venue 42"
,
VenueInfo
=
exampleBytes3
,
Capacity
=
3000
,
AvailableDates
=
availableDates3
,
LastContactDate
=
DateTime
.
Parse
(
"2018-10-01"
),
OutdoorVenue
=
false
,
PopularityScore
=
0.72598f
}};
using
var
connection
=
new
SpannerConnection
(
connectionString
);
await
connection
.
OpenAsync
();
await
Task
.
WhenAll
(
venues
.
Select
(
venue
=
>
{
var
cmd
=
connection
.
CreateInsertCommand
(
"Venues"
,
new
SpannerParameterCollection
{
{
"VenueId"
,
SpannerDbType
.
Int64
,
venue
.
VenueId
},
{
"VenueName"
,
SpannerDbType
.
String
,
venue
.
VenueName
},
{
"VenueInfo"
,
SpannerDbType
.
Bytes
,
venue
.
VenueInfo
},
{
"Capacity"
,
SpannerDbType
.
Int64
,
venue
.
Capacity
},
{
"AvailableDates"
,
SpannerDbType
.
ArrayOf
(
SpannerDbType
.
Date
),
venue
.
AvailableDates
},
{
"LastContactDate"
,
SpannerDbType
.
Date
,
venue
.
LastContactDate
},
{
"OutdoorVenue"
,
SpannerDbType
.
Bool
,
venue
.
OutdoorVenue
},
{
"PopularityScore"
,
SpannerDbType
.
Float64
,
venue
.
PopularityScore
},
{
"LastUpdateTime"
,
SpannerDbType
.
Timestamp
,
SpannerParameter
.
CommitTimestamp
},
});
return
cmd
.
ExecuteNonQueryAsync
();
}));
}
}
Go
To learn how to install and use the client library for Spanner, see Spanner client libraries .
To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment .
import
(
"context"
"io"
"cloud.google.com/go/spanner"
)
func
writeDatatypesData
(
w
io
.
Writer
,
db
string
)
error
{
ctx
:=
context
.
Background
()
client
,
err
:=
spanner
.
NewClient
(
ctx
,
db
)
if
err
!=
nil
{
return
err
}
defer
client
.
Close
()
venueColumns
:=
[]
string
{
"VenueId"
,
"VenueName"
,
"VenueInfo"
,
"Capacity"
,
"AvailableDates"
,
"LastContactDate"
,
"OutdoorVenue"
,
"PopularityScore"
,
"LastUpdateTime"
}
m
:=
[]
*
spanner
.
Mutation
{
spanner
.
InsertOrUpdate
(
"Venues"
,
venueColumns
,
[]
interface
{}{
4
,
"Venue 4"
,
[]
byte
(
"Hello World 1"
),
1800
,
[]
string
{
"2020-12-01"
,
"2020-12-02"
,
"2020-12-03"
},
"2018-09-02"
,
false
,
0.85543
,
spanner
.
CommitTimestamp
}),
spanner
.
InsertOrUpdate
(
"Venues"
,
venueColumns
,
[]
interface
{}{
19
,
"Venue 19"
,
[]
byte
(
"Hello World 2"
),
6300
,
[]
string
{
"2020-11-01"
,
"2020-11-05"
,
"2020-11-15"
},
"2019-01-15"
,
true
,
0.98716
,
spanner
.
CommitTimestamp
}),
spanner
.
InsertOrUpdate
(
"Venues"
,
venueColumns
,
[]
interface
{}{
42
,
"Venue 42"
,
[]
byte
(
"Hello World 3"
),
3000
,
[]
string
{
"2020-10-01"
,
"2020-10-07"
},
"2018-10-01"
,
false
,
0.72598
,
spanner
.
CommitTimestamp
}),
}
_
,
err
=
client
.
Apply
(
ctx
,
m
)
return
err
}
Java
To learn how to install and use the client library for Spanner, see Spanner client libraries .
To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment .
static
Value
availableDates1
=
Value
.
dateArray
(
Arrays
.
asList
(
Date
.
parseDate
(
"2020-12-01"
),
Date
.
parseDate
(
"2020-12-02"
),
Date
.
parseDate
(
"2020-12-03"
)));
static
Value
availableDates2
=
Value
.
dateArray
(
Arrays
.
asList
(
Date
.
parseDate
(
"2020-11-01"
),
Date
.
parseDate
(
"2020-11-05"
),
Date
.
parseDate
(
"2020-11-15"
)));
static
Value
availableDates3
=
Value
.
dateArray
(
Arrays
.
asList
(
Date
.
parseDate
(
"2020-10-01"
),
Date
.
parseDate
(
"2020-10-07"
)));
static
String
exampleBytes1
=
BaseEncoding
.
base64
().
encode
(
"Hello World 1"
.
getBytes
());
static
String
exampleBytes2
=
BaseEncoding
.
base64
().
encode
(
"Hello World 2"
.
getBytes
());
static
String
exampleBytes3
=
BaseEncoding
.
base64
().
encode
(
"Hello World 3"
.
getBytes
());
static
final
List<Venue>
VENUES
=
Arrays
.
asList
(
new
Venue
(
4
,
"Venue 4"
,
exampleBytes1
,
1800
,
availableDates1
,
"2018-09-02"
,
false
,
0.85543f
,
new
BigDecimal
(
"215100.10"
),
Value
.
json
(
"[{\"name\":\"room 1\",\"open\":true},{\"name\":\"room 2\",\"open\":false}]"
)),
new
Venue
(
19
,
"Venue 19"
,
exampleBytes2
,
6300
,
availableDates2
,
"2019-01-15"
,
true
,
0.98716f
,
new
BigDecimal
(
"1200100.00"
),
Value
.
json
(
"{\"rating\":9,\"open\":true}"
)),
new
Venue
(
42
,
"Venue 42"
,
exampleBytes3
,
3000
,
availableDates3
,
"2018-10-01"
,
false
,
0.72598f
,
new
BigDecimal
(
"390650.99"
),
Value
.
json
(
"{\"name\":null,"
+
"\"open\":{\"Monday\":true,\"Tuesday\":false},"
+
"\"tags\":[\"large\",\"airy\"]}"
)));
static
void
writeDatatypesData
(
DatabaseClient
dbClient
)
{
List<Mutation>
mutations
=
new
ArrayList
<> ();
for
(
Venue
venue
:
VENUES
)
{
mutations
.
add
(
Mutation
.
newInsertBuilder
(
"Venues"
)
.
set
(
"VenueId"
)
.
to
(
venue
.
venueId
)
.
set
(
"VenueName"
)
.
to
(
venue
.
venueName
)
.
set
(
"VenueInfo"
)
.
to
(
venue
.
venueInfo
)
.
set
(
"Capacity"
)
.
to
(
venue
.
capacity
)
.
set
(
"AvailableDates"
)
.
to
(
venue
.
availableDates
)
.
set
(
"LastContactDate"
)
.
to
(
venue
.
lastContactDate
)
.
set
(
"OutdoorVenue"
)
.
to
(
venue
.
outdoorVenue
)
.
set
(
"PopularityScore"
)
.
to
(
venue
.
popularityScore
)
.
set
(
"Revenue"
)
.
to
(
venue
.
revenue
)
.
set
(
"VenueDetails"
)
.
to
(
venue
.
venueDetails
)
.
set
(
"LastUpdateTime"
)
.
to
(
Value
.
COMMIT_TIMESTAMP
)
.
build
());
}
dbClient
.
write
(
mutations
);
}
Node.js
To learn how to install and use the client library for Spanner, see Spanner client libraries .
To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment .
// 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
);
// Instantiate Spanner table objects.
const
venuesTable
=
database
.
table
(
'Venues'
);
const
exampleBytes1
=
new
Buffer
.
from
(
'Hello World 1'
);
const
exampleBytes2
=
new
Buffer
.
from
(
'Hello World 2'
);
const
exampleBytes3
=
new
Buffer
.
from
(
'Hello World 3'
);
const
availableDates1
=
[
'2020-12-01'
,
'2020-12-02'
,
'2020-12-03'
];
const
availableDates2
=
[
'2020-11-01'
,
'2020-11-05'
,
'2020-11-15'
];
const
availableDates3
=
[
'2020-10-01'
,
'2020-10-07'
];
// Note: Cloud Spanner interprets Node.js numbers as FLOAT64s, so they
// must be converted to strings before being inserted as INT64s.
const
data
=
[
{
VenueId
:
'4'
,
VenueName
:
'Venue 4'
,
VenueInfo
:
exampleBytes1
,
Capacity
:
'1800'
,
AvailableDates
:
availableDates1
,
LastContactDate
:
'2018-09-02'
,
OutdoorVenue
:
false
,
PopularityScore
:
Spanner
.
float
(
0.85543
),
LastUpdateTime
:
'spanner.commit_timestamp()'
,
},
{
VenueId
:
'19'
,
VenueName
:
'Venue 19'
,
VenueInfo
:
exampleBytes2
,
Capacity
:
'6300'
,
AvailableDates
:
availableDates2
,
LastContactDate
:
'2019-01-15'
,
OutdoorVenue
:
true
,
PopularityScore
:
Spanner
.
float
(
0.98716
),
LastUpdateTime
:
'spanner.commit_timestamp()'
,
},
{
VenueId
:
'42'
,
VenueName
:
'Venue 42'
,
VenueInfo
:
exampleBytes3
,
Capacity
:
'3000'
,
AvailableDates
:
availableDates3
,
LastContactDate
:
'2018-10-01'
,
OutdoorVenue
:
false
,
PopularityScore
:
Spanner
.
float
(
0.72598
),
LastUpdateTime
:
'spanner.commit_timestamp()'
,
},
];
// Inserts rows into the Venues table.
try
{
await
venuesTable
.
insert
(
data
);
console
.
log
(
'Inserted data.'
);
}
catch
(
err
)
{
console
.
error
(
'ERROR:'
,
err
);
}
finally
{
// Close the database when finished.
database
.
close
();
}
PHP
To learn how to install and use the client library for Spanner, see Spanner client libraries .
To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment .
use Google\Cloud\Spanner\SpannerClient;
/**
* Inserts sample data into a table with supported datatypes.
*
* The database and table must already exist and can be created using
* `create_table_with_datatypes`.
* Example:
* ```
* insert_data_with_datatypes($instanceId, $databaseId);
* ```
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function insert_data_with_datatypes(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$operation = $database->transaction(['singleUse' => true])
->insertBatch('Venues', [
[
'VenueId' => 4,
'VenueName' => 'Venue 4',
'VenueInfo' => base64_encode('Hello World 1'),
'Capacity' => 1800,
'AvailableDates' => ['2020-12-01', '2020-12-02', '2020-12-03'],
'LastContactDate' => '2018-09-02',
'OutdoorVenue' => false,
'PopularityScore' => 0.85543,
'LastUpdateTime' => $spanner->commitTimestamp()
], [
'VenueId' => 19,
'VenueName' => 'Venue 19',
'VenueInfo' => base64_encode('Hello World 2'),
'Capacity' => 6300,
'AvailableDates' => ['2020-11-01', '2020-11-05', '2020-11-15'],
'LastContactDate' => '2019-01-15',
'OutdoorVenue' => true,
'PopularityScore' => 0.98716,
'LastUpdateTime' => $spanner->commitTimestamp()
], [
'VenueId' => 42,
'VenueName' => 'Venue 42',
'VenueInfo' => base64_encode('Hello World 3'),
'Capacity' => 3000,
'AvailableDates' => ['2020-10-01', '2020-10-07'],
'LastContactDate' => '2018-10-01',
'OutdoorVenue' => false,
'PopularityScore' => 0.72598,
'LastUpdateTime' => $spanner->commitTimestamp()
],
])
->commit();
print('Inserted data.' . PHP_EOL);
}
Python
To learn how to install and use the client library for Spanner, see Spanner client libraries .
To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment .
# 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
)
exampleBytes1
=
base64
.
b64encode
(
"Hello World 1"
.
encode
())
exampleBytes2
=
base64
.
b64encode
(
"Hello World 2"
.
encode
())
exampleBytes3
=
base64
.
b64encode
(
"Hello World 3"
.
encode
())
available_dates1
=
[
"2020-12-01"
,
"2020-12-02"
,
"2020-12-03"
]
available_dates2
=
[
"2020-11-01"
,
"2020-11-05"
,
"2020-11-15"
]
available_dates3
=
[
"2020-10-01"
,
"2020-10-07"
]
with
database
.
batch
()
as
batch
:
batch
.
insert
(
table
=
"Venues"
,
columns
=
(
"VenueId"
,
"VenueName"
,
"VenueInfo"
,
"Capacity"
,
"AvailableDates"
,
"LastContactDate"
,
"OutdoorVenue"
,
"PopularityScore"
,
"LastUpdateTime"
,
),
values
=
[
(
4
,
"Venue 4"
,
exampleBytes1
,
1800
,
available_dates1
,
"2018-09-02"
,
False
,
0.85543
,
spanner
.
COMMIT_TIMESTAMP
,
),
(
19
,
"Venue 19"
,
exampleBytes2
,
6300
,
available_dates2
,
"2019-01-15"
,
True
,
0.98716
,
spanner
.
COMMIT_TIMESTAMP
,
),
(
42
,
"Venue 42"
,
exampleBytes3
,
3000
,
available_dates3
,
"2018-10-01"
,
False
,
0.72598
,
spanner
.
COMMIT_TIMESTAMP
,
),
],
)
print
(
"Inserted data."
)
Ruby
To learn how to install and use the client library for Spanner, see Spanner client libraries .
To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment .
# 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
# Get commit_timestamp
commit_timestamp
=
client
.
commit_timestamp
client
.
commit
do
|
c
|
c
.
insert
"Venues"
,
[
{
VenueId
:
4
,
VenueName
:
"Venue 4"
,
VenueInfo
:
StringIO
.
new
(
"Hello World 1"
),
Capacity
:
1_800
,
AvailableDates
:
[
"2020-12-01"
,
"2020-12-02"
,
"2020-12-03"
]
,
LastContactDate
:
"2018-09-02"
,
OutdoorVenue
:
false
,
PopularityScore
:
0
.
85543
,
LastUpdateTime
:
commit_timestamp
},
{
VenueId
:
19
,
VenueName
:
"Venue 19"
,
VenueInfo
:
StringIO
.
new
(
"Hello World 2"
),
Capacity
:
6_300
,
AvailableDates
:
[
"2020-11-01"
,
"2020-11-05"
,
"2020-11-15"
]
,
LastContactDate
:
"2019-01-15"
,
OutdoorVenue
:
true
,
PopularityScore
:
0
.
98716
,
LastUpdateTime
:
commit_timestamp
},
{
VenueId
:
42
,
VenueName
:
"Venue 42"
,
VenueInfo
:
StringIO
.
new
(
"Hello World 3"
),
Capacity
:
3_000
,
AvailableDates
:
[
"2020-10-01"
,
"2020-10-07"
]
,
LastContactDate
:
"2018-10-01"
,
OutdoorVenue
:
false
,
PopularityScore
:
0
.
72598
,
LastUpdateTime
:
commit_timestamp
}
]
end
puts
"Inserted data"
What's next
To search and filter code samples for other Google Cloud products, see the Google Cloud sample browser .
