Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 7f9c006

Browse filesBrowse files
kurtisvgandrewsg
kurtisvg
authored and
andrewsg
committed
Spanner Commit Timestamp Sample (GoogleCloudPlatform#1425)
1 parent 4c47af9 commit 7f9c006
Copy full SHA for 7f9c006

File tree

Expand file treeCollapse file tree

3 files changed

+215
-2
lines changed
Filter options
Expand file treeCollapse file tree

3 files changed

+215
-2
lines changed

‎spanner/cloud-client/requirements.txt

Copy file name to clipboard
+1-1Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,2 +1,2 @@
1-
google-cloud-spanner==1.2.0
1+
google-cloud-spanner==1.3.0
22
futures==3.2.0; python_version < "3"

‎spanner/cloud-client/snippets.py

Copy file name to clipboardExpand all lines: spanner/cloud-client/snippets.py
+160-1Lines changed: 160 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -460,7 +460,145 @@ def read_only_transaction(instance_id, database_id):
460460
# [END spanner_read_only_transaction]
461461

462462

463-
if __name__ == '__main__':
463+
# [START spanner_create_table_with_timestamp_column]
464+
def create_table_with_timestamp(instance_id, database_id):
465+
"""Creates a table with a COMMIT_TIMESTAMP column."""
466+
467+
spanner_client = spanner.Client()
468+
instance = spanner_client.instance(instance_id)
469+
database = instance.database(database_id)
470+
471+
operation = database.update_ddl([
472+
"""CREATE TABLE Performances (
473+
SingerId INT64 NOT NULL,
474+
VenueId INT64 NOT NULL,
475+
EventDate Date,
476+
Revenue INT64,
477+
LastUpdateTime TIMESTAMP NOT NULL
478+
OPTIONS(allow_commit_timestamp=true)
479+
) PRIMARY KEY (SingerId, VenueId, EventDate),
480+
INTERLEAVE IN PARENT Singers ON DELETE CASCADE"""
481+
])
482+
483+
print('Waiting for operation to complete...')
484+
operation.result()
485+
486+
print('Created Performances table on database {} on instance {}'.format(
487+
database_id, instance_id))
488+
# [END spanner_create_table_with_timestamp_column]
489+
490+
491+
# [START spanner_insert_data_with_timestamp_column]
492+
def insert_data_with_timestamp(instance_id, database_id):
493+
"""Inserts data with a COMMIT_TIMESTAMP field into a table. """
494+
495+
spanner_client = spanner.Client()
496+
instance = spanner_client.instance(instance_id)
497+
498+
database = instance.database(database_id)
499+
500+
with database.batch() as batch:
501+
batch.insert(
502+
table='Performances',
503+
columns=(
504+
'SingerId', 'VenueId', 'EventDate',
505+
'Revenue', 'LastUpdateTime',),
506+
values=[
507+
(1, 4, "2017-10-05", 11000, spanner.COMMIT_TIMESTAMP),
508+
(1, 19, "2017-11-02", 15000, spanner.COMMIT_TIMESTAMP),
509+
(2, 42, "2017-12-23", 7000, spanner.COMMIT_TIMESTAMP)])
510+
511+
print('Inserted data.')
512+
# [END spanner_insert_data_with_timestamp_column]
513+
514+
515+
# [START spanner_add_timestamp_column]
516+
def add_timestamp_column(instance_id, database_id):
517+
"""
518+
Adds a new TIMESTAMP column to the Albums table in the example database.
519+
"""
520+
spanner_client = spanner.Client()
521+
instance = spanner_client.instance(instance_id)
522+
523+
database = instance.database(database_id)
524+
525+
operation = database.update_ddl([
526+
'ALTER TABLE Albums ADD COLUMN LastUpdateTime TIMESTAMP '
527+
'OPTIONS(allow_commit_timestamp=true)'])
528+
529+
print('Waiting for operation to complete...')
530+
operation.result()
531+
532+
print('Altered table "Albums" on database {} on instance {}.'.format(
533+
database_id, instance_id))
534+
# [END spanner_add_timestamp_column]
535+
536+
537+
# [START spanner_update_data_with_timestamp_column]
538+
def update_data_with_timestamp(instance_id, database_id):
539+
"""Updates Performances tables in the database with the COMMIT_TIMESTAMP
540+
column.
541+
542+
This updates the `MarketingBudget` column which must be created before
543+
running this sample. You can add the column by running the `add_column`
544+
sample or by running this DDL statement against your database:
545+
546+
ALTER TABLE Albums ADD COLUMN MarketingBudget INT64
547+
548+
In addition this update expects the LastUpdateTime column added by
549+
applying this DDL statement against your database:
550+
551+
ALTER TABLE Albums ADD COLUMN LastUpdateTime TIMESTAMP
552+
OPTIONS(allow_commit_timestamp=true)
553+
"""
554+
spanner_client = spanner.Client()
555+
instance = spanner_client.instance(instance_id)
556+
557+
database = instance.database(database_id)
558+
559+
with database.batch() as batch:
560+
batch.update(
561+
table='Albums',
562+
columns=(
563+
'SingerId', 'AlbumId', 'MarketingBudget', 'LastUpdateTime'),
564+
values=[
565+
(1, 4, 11000, spanner.COMMIT_TIMESTAMP),
566+
(1, 19, 15000, spanner.COMMIT_TIMESTAMP),
567+
(2, 42, 7000, spanner.COMMIT_TIMESTAMP)])
568+
569+
print('Updated data.')
570+
# [END spanner_update_data_with_timestamp_column]
571+
572+
573+
# [START spanner_query_data_with_timestamp_column]
574+
def query_data_with_timestamp(instance_id, database_id):
575+
"""Queries sample data from the database using SQL.
576+
577+
This updates the `LastUpdateTime` column which must be created before
578+
running this sample. You can add the column by running the
579+
`add_timestamp_column` sample or by running this DDL statement
580+
against your database:
581+
582+
ALTER TABLE Performances ADD COLUMN LastUpdateTime TIMESTAMP
583+
OPTIONS (allow_commit_timestamp=true)
584+
585+
"""
586+
spanner_client = spanner.Client()
587+
instance = spanner_client.instance(instance_id)
588+
589+
database = instance.database(database_id)
590+
591+
with database.snapshot() as snapshot:
592+
results = snapshot.execute_sql(
593+
'SELECT SingerId, AlbumId, AlbumTitle FROM Albums '
594+
'ORDER BY LastUpdateTime DESC')
595+
596+
for row in results:
597+
print(u'SingerId: {}, AlbumId: {}, AlbumTitle: {}'.format(*row))
598+
# [END spanner_query_data_with_timestamp_column]
599+
600+
601+
if __name__ == '__main__': # noqa: C901
464602
parser = argparse.ArgumentParser(
465603
description=__doc__,
466604
formatter_class=argparse.RawDescriptionHelpFormatter)
@@ -495,6 +633,17 @@ def read_only_transaction(instance_id, database_id):
495633
subparsers.add_parser('add_storing_index', help=add_storing_index.__doc__)
496634
subparsers.add_parser(
497635
'read_data_with_storing_index', help=insert_data.__doc__)
636+
subparsers.add_parser(
637+
'create_table_with_timestamp',
638+
help=create_table_with_timestamp.__doc__)
639+
subparsers.add_parser(
640+
'insert_data_with_timestamp', help=insert_data_with_timestamp.__doc__)
641+
subparsers.add_parser(
642+
'add_timestamp_column', help=add_timestamp_column.__doc__)
643+
subparsers.add_parser(
644+
'update_data_with_timestamp', help=update_data_with_timestamp.__doc__)
645+
subparsers.add_parser(
646+
'query_data_with_timestamp', help=query_data_with_timestamp.__doc__)
498647

499648
args = parser.parse_args()
500649

@@ -530,3 +679,13 @@ def read_only_transaction(instance_id, database_id):
530679
add_storing_index(args.instance_id, args.database_id)
531680
elif args.command == 'read_data_with_storing_index':
532681
read_data_with_storing_index(args.instance_id, args.database_id)
682+
elif args.command == 'create_table_with_timestamp':
683+
create_table_with_timestamp(args.instance_id, args.database_id)
684+
elif args.command == 'insert_data_with_timestamp':
685+
insert_data_with_timestamp(args.instance_id, args.database_id)
686+
elif args.command == 'add_timestamp_column':
687+
add_timestamp_column(args.instance_id, args.database_id)
688+
elif args.command == 'update_data_with_timestamp':
689+
update_data_with_timestamp(args.instance_id, args.database_id)
690+
elif args.command == 'query_data_with_timestamp':
691+
query_data_with_timestamp(args.instance_id, args.database_id)

‎spanner/cloud-client/snippets_test.py

Copy file name to clipboardExpand all lines: spanner/cloud-client/snippets_test.py
+54Lines changed: 54 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -180,3 +180,57 @@ def _():
180180
out, _ = capsys.readouterr()
181181

182182
assert 'Forever Hold Your Peace' in out
183+
184+
185+
def test_create_table_with_timestamp(temporary_database, capsys):
186+
snippets.create_table_with_timestamp(
187+
SPANNER_INSTANCE,
188+
temporary_database.database_id)
189+
190+
out, _ = capsys.readouterr()
191+
192+
assert 'Performances' in out
193+
194+
195+
def test_insert_data_with_timestamp(temporary_database, capsys):
196+
snippets.insert_data_with_timestamp(
197+
SPANNER_INSTANCE,
198+
temporary_database.database_id)
199+
200+
out, _ = capsys.readouterr()
201+
202+
assert 'Inserted data.' in out
203+
204+
205+
def test_add_timestamp_column(temporary_database, capsys):
206+
snippets.add_timestamp_column(
207+
SPANNER_INSTANCE,
208+
temporary_database.database_id)
209+
210+
out, _ = capsys.readouterr()
211+
212+
assert 'Albums' in out
213+
214+
215+
@pytest.mark.slow
216+
def test_update_data_with_timestamp(temporary_database, capsys):
217+
snippets.update_data_with_timestamp(
218+
SPANNER_INSTANCE,
219+
temporary_database.database_id)
220+
221+
out, _ = capsys.readouterr()
222+
223+
assert 'Updated data.' in out
224+
225+
226+
@pytest.mark.slow
227+
def test_query_data_with_timestamp(temporary_database, capsys):
228+
@eventually_consistent.call
229+
def _():
230+
snippets.query_data_with_timestamp(
231+
SPANNER_INSTANCE,
232+
temporary_database.database_id)
233+
234+
out, _ = capsys.readouterr()
235+
236+
assert 'Go, Go, Go' in out

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.