8000 Add Spanner commit timestamp sample. by jsimonweb · Pull Request #1072 · GoogleCloudPlatform/java-docs-samples · GitHub
[go: up one dir, main page]
Skip to content

Add Spanner commit timestamp sample. #1072

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
merged 2 commits into from
Mar 28, 2018
Merged

Add Spanner commit timestamp sample. #1072

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
2 commits
Select commit Hold shift + click to select a range
9441c3f
Add Spanner commit timestamp sample.
jsimonweb Mar 28, 2018
5aa0b1f
Update pom file to use latest version.
jsimonweb Mar 28, 2018
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
< 10000 /include-fragment>
Diff view
Diff view
2 changes: 1 addition & 1 deletion spanner/cloud-client/pom.xml
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -128,7 +128,7 @@ limitations under the License.
<dependency>
<groupId>com.google.cloud</groupId>
<artifactId>google-cloud-bom</artifactId>
<version>0.37.0-alpha</version>
<version>0.42.0-alpha</version>
<type>pom</type>
<scope>import</scope>
</dependency>
Expand Down
219 changes: 219 additions & 0 deletions spanner/cloud-client/src/main/java/com/example/spanner/SpannerSample.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -34,7 +34,9 @@
import com.google.cloud.spanner.Struct;
import com.google.cloud.spanner.TimestampBound;
import com.google.cloud.spanner.TransactionContext;
import com.google.cloud.spanner.Value;
import com.google.spanner.admin.database.v1.CreateDatabaseMetadata;
import com.google.spanner.admin.database.v1.UpdateDatabaseDdlMetadata;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
Expand All @@ -48,6 +50,7 @@
* <li> Writing, reading and executing SQL queries.
* <li> Writing data using a read-write transaction.
* <li> Using an index to read and execute SQL queries over data.
* <li> Using commit timestamp for tracking when a record was last updated.
* </ul>
*/
public class SpannerSample {
Expand Down Expand Up @@ -84,6 +87,24 @@ static class Album {
}
}

/**
* Class to contain performance sample data.
*/
static class Performance {

final long singerId;
final long venueId;
final String eventDate;
final long revenue;

Performance(long singerId, long venueId, String eventDate, long revenue) {
this.singerId = singerId;
this.venueId = venueId;
this.eventDate = eventDate;
this.revenue = revenue;
}
}

// [START spanner_insert_data]
static final List<Singer> SINGERS =
Arrays.asList(
Expand All @@ -102,6 +123,14 @@ static class Album {
new Album(2, 3, "Terrified"));
// [END spanner_insert_data]

// [START spanner_insert_data_with_timestamp_column]
static final List<Performance> PERFORMANCES =
Arrays.asList(
new Performance(1, 4, "2017-10-05", 11000),
new Performance(1, 19, "2017-11-02", 15000),
new Performance(2, 42, "2017-12-23", 7000));
// [END spanner_insert_data_with_timestamp_column]

// [START spanner_create_database]
static void createDatabase(DatabaseAdminClient dbAdminClient, DatabaseId id) {
Operation<Database, CreateDatabaseMetadata> op = dbAdminClient
Expand All @@ -126,6 +155,48 @@ static void createDatabase(DatabaseAdminClient dbAdminClient, DatabaseId id) {
}
// [END spanner_create_database]

// [START spanner_create_table_with_timestamp_column]
static void createTableWithTimestamp(DatabaseAdminClient dbAdminClient, DatabaseId id) {
Operation<Void, UpdateDatabaseDdlMetadata> op = dbAdminClient
.updateDatabaseDdl(
id.getInstanceId().getInstance(),
id.getDatabase(),
Arrays.asList(
"CREATE TABLE Performances (\n"
+ " SingerId INT64 NOT NULL,\n"
+ " VenueId INT64 NOT NULL,\n"
+ " EventDate Date,\n"
+ " Revenue INT64, \n"
+ " LastUpdateTime TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true)\n"
+ ") PRIMARY KEY (SingerId, VenueId, EventDate),\n"
+ " INTERLEAVE IN PARENT Singers ON DELETE CASCADE"), null);
op.waitFor().getResult();
System.out.println("Created Performances table in database: [" + id + "]");
}
// [END spanner_create_table_with_timestamp_column]

// [START spanner_insert_data_with_timestamp_column]
static void writeExampleDataWithTimestamp(DatabaseClient dbClient) {
List<Mutation> mutations = new ArrayList<>();
for (Performance performance : PERFORMANCES) {
mutations.add(
Mutation.newInsertBuilder("Performances")
Copy link
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Not required, but it would probably be more readable as:
.set("SingerId").to(performance.singerId)
.set("VenueId").to(performance.venueId)
...

.set("SingerId")
.to(performance.singerId)
.set("VenueId")
.to(performance.venueId)
.set("EventDate")
.to(performance.eventDate)
.set("Revenue")
.to(performance.revenue)
.set("LastUpdateTime")
.to(Value.COMMIT_TIMESTAMP)
.build());
}
dbClient.write(mutations);
}
// [END spanner_insert_data_with_timestamp_column]

// [START spanner_insert_data]
static void writeExampleData(DatabaseClient dbClient) {
List<Mutation> mutations = new ArrayList<>();
Expand Down Expand Up @@ -422,6 +493,98 @@ static void readStaleData(DatabaseClient dbClient) {
}
// [END spanner_read_stale_data]

// [START spanner_add_timestamp_column]
static void addCommitTimestamp(DatabaseAdminClient adminClient, DatabaseId dbId) {
adminClient.updateDatabaseDdl(dbId.getInstanceId().getInstance(),
dbId.getDatabase(),
Arrays.asList(
"ALTER TABLE Albums ADD COLUMN LastUpdateTime TIMESTAMP "
+ "OPTIONS (allow_commit_timestamp=true)"),
null).waitFor();
System.out.println("Added LastUpdateTime as a commit timestamp column in Albums table.");
}
// [END spanner_add_timestamp_column]

// Before executing this method, a new column MarketingBudget has to be added to the Albums
// table by applying the DDL statement "ALTER TABLE Albums ADD COLUMN MarketingBudget INT64".
// In addition this update expects the LastUpdateTime column added by applying the DDL statement
// "ALTER TABLE Albums ADD COLUMN LastUpdateTime TIMESTAMP OPTIONS (allow_commit_timestamp=true)"
// [START spanner_update_data_with_timestamp_column]
static void updateWithTimestamp(DatabaseClient dbClient) {
// Mutation can be used to update/insert/delete a single row in a table. Here we use
// newUpdateBuilder to create update mutations.
List<Mutation> mutations =
Arrays.asList(
Mutation.newUpdateBuilder("Albums")
.set("SingerId")
Copy link
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

.set(...).to(...) would be easier to read.

Copy link
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Though our style checkers might be unhappy if you do it.

.to(1)
.set("AlbumId")
.to(1)
.set("MarketingBudget")
.to(1000000)
.set("LastUpdateTime")
.to(Value.COMMIT_TIMESTAMP)
.build(),
Mutation.newUpdateBuilder("Albums")
.set("SingerId")
.to(2)
.set("AlbumId")
.to(2)
.set("MarketingBudget")
.to(750000)
.set("LastUpdateTime")
.to(Value.COMMIT_TIMESTAMP)
.build());
// This writes all the mutations to Cloud Spanner atomically.
dbClient.write(mutations);
}
// [END spanner_update_data_with_timestamp_column]

// [START spanner_query_data_with_timestamp_column]
static void queryMarketingBudgetWithTimestamp(DatabaseClient dbClient) {
// Rows without an explicit value for MarketingBudget will have a MarketingBudget equal to
// null.
ResultSet resultSet =
dbClient
.singleUse()
.executeQuery(Statement.of(
"SELECT SingerId, AlbumId, MarketingBudget, LastUpdateTime FROM Albums"
+ " ORDER BY LastUpdateTime DESC"));
while (resultSet.next()) {
System.out.printf(
"%d %d %s %s\n",
resultSet.getLong("SingerId"),
resultSet.getLong("AlbumId"),
// We check that the value is non null. ResultSet getters can only be used to retrieve
// non null values.
resultSet.isNull("MarketingBudget") ? "NULL" : resultSet.getLong("MarketingBudget"),
resultSet.isNull("LastUpdateTime") ? "NULL" : resultSet.getTimestamp("LastUpdateTime"));
}
}
// [END spanner_query_data_with_timestamp_column]

static void queryPerformancesTable(DatabaseClient dbClient) {
// Rows without an explicit value for Revenue will have a Revenue equal to
// null.
ResultSet resultSet =
dbClient
.singleUse()
.executeQuery(Statement.of(
"SELECT SingerId, VenueId, EventDate, Revenue, LastUpdateTime FROM Performances"
+ " ORDER BY LastUpdateTime DESC"));
while (resultSet.next()) {
System.out.printf(
"%d %d %s %s %s\n",
resultSet.getLong("SingerId"),
resultSet.getLong("VenueId"),
resultSet.getDate("EventDate"),
// We check that the value is non null. ResultSet getters can only be used to retrieve
// non null values.
resultSet.isNull("Revenue") ? "NULL" : resultSet.getLong("Revenue"),
resultSet.getTimestamp("LastUpdateTime"));
}
}

static void run(DatabaseClient dbClient, DatabaseAdminClient dbAdminClient, String command,
DatabaseId database) {
switch (command) {
Expand Down Expand Up @@ -470,6 +633,24 @@ static void run(DatabaseClient dbClient, DatabaseAdminClient dbAdminClient, Stri
case "readstaledata":
readStaleData(dbClient);
break;
case "addcommittimestamp":
addCommitTimestamp(dbAdminClient, database);
break;
case "updatewithtimestamp":
updateWithTimestamp(dbClient);
break;
case "querywithtimestamp":
queryMarketingBudgetWithTimestamp(dbClient);
break;
case "createtablewithtimestamp":
createTableWithTimestamp(dbAdminClient, database);
break;
case "writewithtimestamp":
writeExampleDataWithTimestamp(dbClient);
break;
case "queryperformancestable":
queryPerformancesTable(dbClient);
break;
default:
printUsageAndExit();
}
Expand All @@ -484,6 +665,44 @@ static void printUsageAndExit() {
" SpannerExample createdatabase my-instance example-db");
System.err.println(
" SpannerExample write my-instance example-db");
System.err.println(
" SpannerExample query my-instance example-db");
System.err.println(
" SpannerExample read my-instance example-db");
System.err.println(
" SpannerExample addmarketingbudget my-instance example-db");
System.err.println(
" SpannerExample update my-instance example-db");
System.err.println(
" SpannerExample writetransaction my-instance example-db");
System.err.println(
" SpannerExample querymarketingbudget my-instance example-db");
System.err.println(
" SpannerExample addindex my-instance example-db");
System.err.println(
" SpannerExample readindex my-instance example-db");
System.err.println(
" SpannerExample queryindex my-instance example-db");
System.err.println(
" SpannerExample addstoringindex my-instance example-db");
System.err.println(
" SpannerExample readstoringindex my-instance example-db");
System.err.println(
" SpannerExample readonlytransaction my-instance example-db");
System.err.println(
" SpannerExample readstaledata my-instance example-db");
System.err.println(
" SpannerExample addcommittimestamp my-instance example-db");
System.err.println(
" SpannerExample updatewithtimestamp my-instance example-db");
System.err.println(
" SpannerExample querywithtimestamp my-instance example-db");
System.err.println(
" SpannerExample createtablewithtimestamp my-instance example-db");
System.err.println(
" SpannerExample writewithtimestamp my-instance example-db");
System.err.println(
" SpannerExample queryperformancestable my-instance example-db");
System.exit(1);
}

Expand Down
19 changes: 18 additions & 1 deletion spanner/cloud-client/src/test/java/com/example/spanner/SpannerSampleIT.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -85,7 +85,7 @@ public void testSample() throws Exception {
out = runSample("query");
assertThat(out).contains("1 1 Total Junk");
runSample("addmarketingbudget");

// wait for 15 seconds to elapse and then run an update, and query for stale data
lastUpdateDataTimeInMillis = System.currentTimeMillis();
while (System.currentTimeMillis() < lastUpdateDataTimeInMillis + 16000) {
Expand Down Expand Up @@ -116,6 +116,23 @@ public void testSample() throws Exception {

out = runSample("readonlytransaction");
assertThat(out.replaceAll("[\r\n]+", " ")).containsMatch("(Total Junk.*){2}");

out = runSample("addcommittimestamp");
assertThat(out).contains("Added LastUpdateTime as a commit timestamp column");

runSample("updatewithtimestamp");
out = runSample("querywithtimestamp");
assertThat(out).contains("1 1 1000000");
assertThat(out).contains("2 2 750000");

out = runSample("createtablewithtimestamp");
assertThat(out).contains("Created Performances table in database");

runSample("writewithtimestamp");
out = runSample("queryperformancestable");
assertThat(out).contains("1 4 2017-10-05 11000");
assertThat(out).contains("1 19 2017-11-02 15000");
assertThat(out).contains("2 42 2017-12-23 7000");
}

private String formatForTest(String name) {
Expand Down
0