com.google.cloud.spanner.DatabaseAdminClient Java Examples

public static void main(String[] args) {
  SpannerOptions options = SpannerOptions.newBuilder().build();
  Spanner spanner = options.getService();

  try {

    DatabaseId db = DatabaseId.of(options.getProjectId(), args[0], args[1]);
    // [END init_client]
    // This will return the default project id based on the environment.
    String clientProject = spanner.getOptions().getProjectId();
    if (!db.getInstanceId().getProject().equals(clientProject)) {
      System.err.println("Invalid project specified. Project in the database id should match"
          + "the project name set in the environment variable GCLOUD_PROJECT. Expected: "
          + clientProject);
    }
    // [START init_client]
    DatabaseClient dbClient = spanner.getDatabaseClient(db);
    DatabaseAdminClient dbAdminClient = spanner.getDatabaseAdminClient();
    createDatabase(dbAdminClient, db);
  } finally {
    spanner.close();
  }
  
}
 

public static void main(String[] args) throws Exception {
  if (!(args.length == 3 || args.length == 4)) {
    printUsageAndExit();
  }
  SpannerOptions options = SpannerOptions.newBuilder().build();
  Spanner spanner = options.getService();
  try {
    String command = args[0];
    DatabaseId db = DatabaseId.of(options.getProjectId(), args[1], args[2]);
    DatabaseClient dbClient = spanner.getDatabaseClient(db);
    DatabaseAdminClient dbAdminClient = spanner.getDatabaseAdminClient();
    switch (command) {
      case "create":
        create(dbAdminClient, db);
        break;
      default:
        printUsageAndExit();
    }
  } finally {
    spanner.close();
  }
  System.out.println("Closed client");
}
 

static void updateBackup(DatabaseAdminClient dbAdminClient, BackupId backupId) {
  // Get current backup metadata.
  Backup backup = dbAdminClient.newBackupBuilder(backupId).build().reload();
  // Add 30 days to the expire time.
  // Expire time must be within 366 days of the create time of the backup.
  Timestamp expireTime =
      Timestamp.ofTimeMicroseconds(
          TimeUnit.SECONDS.toMicros(backup.getExpireTime().getSeconds())
              + TimeUnit.NANOSECONDS.toMicros(backup.getExpireTime().getNanos())
              + TimeUnit.DAYS.toMicros(30L));
  System.out.println(String.format(
      "Updating expire time of backup [%s] to %s...",
      backupId.toString(),
      LocalDateTime.ofEpochSecond(
          expireTime.getSeconds(),
          expireTime.getNanos(),
          OffsetDateTime.now().getOffset()).toString()));

  // Update expire time.
  backup = backup.toBuilder().setExpireTime(expireTime).build();
  backup.updateExpireTime();
  System.out.println("Updated backup [" + backupId + "]");
}
 

static void listDatabaseOperations(
    InstanceAdminClient instanceAdminClient,
    DatabaseAdminClient dbAdminClient,
    InstanceId instanceId) {
  Instance instance = instanceAdminClient.getInstance(instanceId.getInstance());
  // Get optimize restored database operations.
  String filter = "(metadata.@type:type.googleapis.com/"
                  + "google.spanner.admin.database.v1.OptimizeRestoredDatabaseMetadata)";
  for (Operation op : instance.listDatabaseOperations(Options.filter(filter)).iterateAll()) {
    try {
      OptimizeRestoredDatabaseMetadata metadata =
          op.getMetadata().unpack(OptimizeRestoredDatabaseMetadata.class);
      System.out.println(String.format(
          "Database %s restored from backup is %d%% optimized",
          metadata.getName(),
          metadata.getProgress().getProgressPercent()));
    } catch (InvalidProtocolBufferException e) {
      // The returned operation does not contain OptimizeRestoredDatabaseMetadata.
      System.err.println(e.getMessage());
    }
  }
}
 

static DatabaseAdminClient getDatabaseAdminClient() {
  if (databaseAdminClient == null) {
    try {
      connect();
    } catch (IOException e) {
      if (sc != null) {
        sc.log("getDatabaseAdminClient ", e);
      }
    }
  }
  if (databaseAdminClient == null) {
    if (sc != null) {
      sc.log("Spanner : Unable to connect");
    }
  }
  return databaseAdminClient;
}
 

static DatabaseAdminClient getDatabaseAdminClient() {
  if (databaseAdminClient == null) {
    try {
      connect();
    } catch (IOException e) {
      if (sc != null) {
        sc.log("getDatabaseAdminClient ", e);
      }
    }
  }
  if (databaseAdminClient == null) {
    if (sc != null) {
      sc.log("Spanner : Unable to connect");
    }
  }
  return databaseAdminClient;
}
 

@After
public void tearDown() {
  DatabaseAdminClient adminClient = spanner.getDatabaseAdminClient();
  try {
    adminClient.dropDatabase(instanceId, databaseId);
  } catch (SpannerException e) {
    // Failed to cleanup.
  }

  spanner.close();
}
 

static void createDatabase(DatabaseAdminClient dbAdminClient, DatabaseId id) {
  Operation<Database, CreateDatabaseMetadata> op = dbAdminClient.createDatabase(
      id.getInstanceId().getInstance(), id.getDatabase(),
      Arrays.asList("CREATE TABLE DPH (\n" + "  subject STRING(MAX) NOT NULL,\n"
          + "  col_0  ARRAY<STRING(MAX)>, \n" + "  col_1  ARRAY<STRING(MAX)>, \n"
          + "  col_2  STRING(MAX), \n" + "  col_3  STRING(MAX), \n" + "  col_4  STRING(MAX), \n"
          + "  col_5  STRING(MAX), \n" + "  col_6  STRING(MAX), \n"
          + "  col_7  ARRAY<STRING(MAX)>, \n" + "  col_8  STRING(MAX), \n"
          + "  col_9  STRING(MAX), \n" + "  col_10  STRING(MAX), \n" + "  col_11  STRING(MAX), \n"
          + "  col_12  STRING(MAX), \n" + "  col_13  STRING(MAX), \n"
          + "  col_14  ARRAY<STRING(MAX)>, \n" + "  col_15  STRING(MAX), \n"
          + "  col_16  STRING(MAX)) \n" + " PRIMARY KEY (subject)"));
  Database db = op.waitFor().getResult();
  System.out.println("Created database [" + db.getId() + "]");
}
 

DatabaseAdminClient getDatabaseAdminClient() {
  // [START get_dbadmin_client]
  SpannerOptions options = SpannerOptions.newBuilder().build();
  Spanner spanner = options.getService();
  DatabaseAdminClient dbAdminClient = spanner.getDatabaseAdminClient();
  // [END get_dbadmin_client]

  return dbAdminClient;
}
 

public static void main(String[] args) throws Exception {
  if (!(args.length == 3 || args.length == 4)) {
    printUsageAndExit();
  }
  SpannerOptions options = SpannerOptions.newBuilder().build();
  Spanner spanner = options.getService();
  try {
    String command = args[0];
    DatabaseId db = DatabaseId.of(options.getProjectId(), args[1], args[2]);
    DatabaseClient dbClient = spanner.getDatabaseClient(db);
    DatabaseAdminClient dbAdminClient = spanner.getDatabaseAdminClient();
    switch (command) {
      case "create":
        create(dbAdminClient, db);
        break;
      case "insert":
        String insertType;
        try {
          insertType = args[3];
        } catch (ArrayIndexOutOfBoundsException exception) {
          insertType = "";
        }
        insert(dbClient, insertType);
        break;
      default:
        printUsageAndExit();
    }
  } finally {
    spanner.close();
  }
  System.out.println("Closed client");
}
 

static void delete(DatabaseAdminClient dbAdminClient, DatabaseId db) {
  try  {
    dbAdminClient.dropDatabase(db.getInstanceId().getInstance(), db.getDatabase());
  } catch (Exception e) {
    System.err.println("Error encountered while deleting database. Error message: " + e);
  }
  System.out.printf("Database deleted.\n");
}
 

public static void main(String[] args) throws Exception {
  if (args.length != 3) {
    printUsageAndExit();
  }
  // [START init_client]
  SpannerOptions options = SpannerOptions.newBuilder().build();
  Spanner spanner = options.getService();
  try {
    String command = args[0];
    DatabaseId db = DatabaseId.of(options.getProjectId(), args[1], args[2]);
    // [END init_client]
    // This will return the default project id based on the environment.
    String clientProject = spanner.getOptions().getProjectId();
    if (!db.getInstanceId().getProject().equals(clientProject)) {
      System.err.println(
          "Invalid project specified. Project in the database id should match the"
              + "project name set in the environment variable GOOGLE_CLOUD_PROJECT. Expected: "
              + clientProject);
      printUsageAndExit();
    }
    // Generate a backup id for the sample database.
    String backupName =
        String.format(
            "%s_%02d",
            db.getDatabase(), LocalDate.now().get(ChronoField.ALIGNED_WEEK_OF_YEAR));
    BackupId backup = BackupId.of(db.getInstanceId(), backupName);

    // [START init_client]
    DatabaseClient dbClient = spanner.getDatabaseClient(db);
    DatabaseAdminClient dbAdminClient = spanner.getDatabaseAdminClient();
    InstanceAdminClient instanceAdminClient = spanner.getInstanceAdminClient();
    // Use client here...
    // [END init_client]
    run(dbClient, dbAdminClient, instanceAdminClient, command, db, backup);
  } finally {
    spanner.close();
  }
  // [END init_client]
  System.out.println("Closed client");
}
 

static void deleteBackup(DatabaseAdminClient dbAdminClient, BackupId backupId) {
  Backup backup = dbAdminClient.newBackupBuilder(backupId).build();
  // Delete the backup.
  System.out.println("Deleting backup [" + backupId + "]...");
  backup.delete();
  // Verify that the backup is deleted.
  if (backup.exists()) {
    System.out.println("Delete backup [" + backupId + "] failed");
    throw new RuntimeException("Delete backup [" + backupId + "] failed");
  } else {
    System.out.println("Deleted backup [" + backupId + "]");
  }
}
 

@After
public void tearDown() {
  DatabaseAdminClient adminClient = spanner.getDatabaseAdminClient();
  try {
    adminClient.dropDatabase(instanceId, databaseId);
  } catch (SpannerException e) {
    // Failed to cleanup.
  }

  spanner.close();
}
 

@After
public void tearDown() {
  DatabaseAdminClient adminClient = spanner.getDatabaseAdminClient();
  try {
    adminClient.dropDatabase(instanceId, databaseId);
  } catch (SpannerException e) {
    // Failed to cleanup.
  }

  spanner.close();
}
 

private ExposedSpannerAccessor(
    Spanner spanner,
    DatabaseClient databaseClient,
    DatabaseAdminClient databaseAdminClient,
    BatchClient batchClient) {
  this.spanner = spanner;
  this.databaseClient = databaseClient;
  this.databaseAdminClient = databaseAdminClient;
  this.batchClient = batchClient;
}
 

private SpannerAccessor(
    Spanner spanner,
    DatabaseClient databaseClient,
    DatabaseAdminClient databaseAdminClient,
    BatchClient batchClient,
    SpannerConfig spannerConfig) {
  this.spanner = spanner;
  this.databaseClient = databaseClient;
  this.databaseAdminClient = databaseAdminClient;
  this.batchClient = batchClient;
  this.spannerConfig = spannerConfig;
}
 

@Bean
@ConditionalOnMissingBean
public SpannerDatabaseAdminTemplate spannerDatabaseAdminTemplate(
		Supplier<DatabaseClient> databaseClientProvider,
		DatabaseAdminClient adminClient, Supplier<DatabaseId> databaseIdProvider) {
	return new SpannerDatabaseAdminTemplate(adminClient, databaseClientProvider, databaseIdProvider);
}
 

@Test
public void testShowDDLOperations() throws SQLException, NoSuchFieldException, SecurityException,
    IllegalArgumentException, IllegalAccessException {
  final String ddl =
      "CREATE TABLE FOO (ID INT64 NOT NULL, NAME STRING(100) NOT NULL) PRIMARY KEY (ID)";

  Field adminClientField = CloudSpannerConnection.class.getDeclaredField("adminClient");
  adminClientField.setAccessible(true);
  DatabaseAdminClient adminClient = mock(DatabaseAdminClient.class);
  @SuppressWarnings("unchecked")
  Operation<Void, UpdateDatabaseDdlMetadata> operation = mock(Operation.class);
  when(operation.reload()).then(new Returns(operation));
  when(operation.getName()).then(new Returns("test"));
  when(adminClient.updateDatabaseDdl(any(), any(), any(), any())).then(new Returns(operation));
  adminClientField.set(connection, adminClient);
  Statement statement = connection.createStatement();
  assertFalse(statement.execute("SET_CONNECTION_PROPERTY AsyncDdlOperations=true"));
  assertEquals(1, statement.getUpdateCount());
  try (ResultSet rs = statement.executeQuery("SHOW_DDL_OPERATIONS")) {
    assertFalse(rs.next());
  }
  statement.execute(ddl);
  try (ResultSet rs = statement.executeQuery("SHOW_DDL_OPERATIONS")) {
    assertTrue(rs.next());
    assertEquals("test", rs.getString("NAME"));
    assertNotNull(rs.getTimestamp("TIME_STARTED"));
    assertEquals(ddl, rs.getString("STATEMENT"));
    assertFalse(rs.getBoolean("DONE"));
    assertNull(rs.getString("EXCEPTION"));
    assertFalse(rs.next());
  }
}
 

@Before
public void setup() {
	this.databaseAdminClient = mock(DatabaseAdminClient.class);
	this.databaseClient = mock(DatabaseClient.class);
	this.mockDatabasePage = mock(Page.class);
	this.databaseId = DatabaseId.of("fakeproject", "fakeinstance", "fakedb");
	this.spannerDatabaseAdminTemplate = new SpannerDatabaseAdminTemplate(
			this.databaseAdminClient, () -> this.databaseClient, () -> this.databaseId);
	this.ddlList = new ArrayList<>();
	this.ddlList.add("describe Something");

	when(this.databaseAdminClient.listDatabases("fakeinstance")).thenReturn(this.mockDatabasePage);
}
 

@Bean
public DatabaseAdminClient databaseAdminClient(Spanner spanner) {
	return spanner.getDatabaseAdminClient();
}
 

public static ExposedSpannerAccessor create(SpannerConfig spannerConfig) {
  SpannerOptions.Builder builder = SpannerOptions.newBuilder();

  ValueProvider<Duration> commitDeadline = spannerConfig.getCommitDeadline();
  if (commitDeadline != null && commitDeadline.get().getMillis() > 0) {

    // In Spanner API version 1.21 or above, we can set the deadline / total Timeout on an API
    // call using the following code:
    //
    // UnaryCallSettings.Builder commitSettings =
    // builder.getSpannerStubSettingsBuilder().commitSettings();
    // RetrySettings.Builder commitRetrySettings = commitSettings.getRetrySettings().toBuilder()
    // commitSettings.setRetrySettings(
    //     commitRetrySettings.setTotalTimeout(
    //         Duration.ofMillis(getCommitDeadlineMillis().get()))
    //     .build());
    //
    // However, at time of this commit, the Spanner API is at only at v1.6.0, where the only
    // method to set a deadline is with GRPC Interceptors, so we have to use that...
    SpannerInterceptorProvider interceptorProvider =
        SpannerInterceptorProvider.createDefault()
            .with(new CommitDeadlineSettingInterceptor(commitDeadline.get()));
    builder.setInterceptorProvider(interceptorProvider);
  }

  ValueProvider<String> projectId = spannerConfig.getProjectId();
  if (projectId != null) {
    builder.setProjectId(projectId.get());
  }
  ServiceFactory<Spanner, SpannerOptions> serviceFactory = spannerConfig.getServiceFactory();
  if (serviceFactory != null) {
    builder.setServiceFactory(serviceFactory);
  }
  ValueProvider<String> host = spannerConfig.getHost();
  if (host != null) {
    builder.setHost(host.get());
  }
  String userAgentString = USER_AGENT_PREFIX + "/" + ReleaseInfo.getReleaseInfo().getVersion();
  builder.setHeaderProvider(FixedHeaderProvider.create("user-agent", userAgentString));

  SessionPoolOptions.Builder sessionPoolOptions = SessionPoolOptions.newBuilder();
  sessionPoolOptions.setMinSessions(1);
  sessionPoolOptions.setMaxSessions(3);
  builder.setSessionPoolOption(sessionPoolOptions.build());

  SpannerOptions options = builder.build();

  Spanner spanner = options.getService();
  String instanceId = spannerConfig.getInstanceId().get();
  String databaseId = spannerConfig.getDatabaseId().get();
  DatabaseClient databaseClient =
      spanner.getDatabaseClient(DatabaseId.of(options.getProjectId(), instanceId, databaseId));
  BatchClient batchClient =
      spanner.getBatchClient(DatabaseId.of(options.getProjectId(), instanceId, databaseId));
  DatabaseAdminClient databaseAdminClient = spanner.getDatabaseAdminClient();

  return new ExposedSpannerAccessor(spanner, databaseClient, databaseAdminClient, batchClient);
}
 

public DatabaseAdminClient getDatabaseAdminClient() {
  return databaseAdminClient;
}
 

@Override
public PCollection<Ddl> expand(PCollection<Ddl> input) {
  return input.apply(
      "Apply DDL statements",
      ParDo.of(
          new DoFn<Ddl, Ddl>() {

            private transient ExposedSpannerAccessor spannerAccessor;

            @Setup
            public void setup() {
              spannerAccessor = ExposedSpannerAccessor.create(spannerConfig);
            }

            @Teardown
            public void teardown() {
              spannerAccessor.close();
            }

            @ProcessElement
            public void processElement(ProcessContext c) {
              Ddl ddl = c.element();
              DatabaseAdminClient databaseAdminClient = spannerAccessor.getDatabaseAdminClient();
              List<String> statements = c.sideInput(pendingDDLStatements);
              if (!statements.isEmpty()) {
                // This just kicks off the applying the DDL statements.
                // It does not wait for it to complete.
                OperationFuture<Void, UpdateDatabaseDdlMetadata> op =
                    databaseAdminClient.updateDatabaseDdl(
                        spannerConfig.getInstanceId().get(),
                        spannerConfig.getDatabaseId().get(),
                        statements,
                        null);
                if (waitForApply.get()) {
                  try {
                    op.get();
                  } catch (InterruptedException | ExecutionException e) {
                    throw new RuntimeException(e);
                  }
                }
              }
              c.output(ddl);
            }
          })
      .withSideInputs(pendingDDLStatements));
}
 

public DatabaseAdminClientSnippets(DatabaseAdminClient dbAdminClient) {
  this.dbAdminClient = dbAdminClient;
}
 

public static void main(String[] args) throws Exception {
  if (!(args.length == 3 || args.length == 4)) {
    printUsageAndExit();
  }
  SpannerOptions options = SpannerOptions.newBuilder().build();
  Spanner spanner = options.getService();
  try {
    String command = args[0];
    DatabaseId db = DatabaseId.of(options.getProjectId(), args[1], args[2]);
    DatabaseClient dbClient = spanner.getDatabaseClient(db);
    DatabaseAdminClient dbAdminClient = spanner.getDatabaseAdminClient();
    switch (command) {
      case "create":
        create(dbAdminClient, db);
        break;
      case "insert":
        String insertType;
        try {
          insertType = args[3];
        } catch (ArrayIndexOutOfBoundsException exception) {
          insertType = "";
        }
        insert(dbClient, insertType);
        break;
      case "query":
        if (args.length == 4) {
          int timespan = 0;
          try {
            timespan = Integer.parseInt(args[3]);
          } catch (NumberFormatException e) {
            System.err.println("query command's 'timespan' parameter must be a valid integer.");
            System.exit(1);
          }
          query(dbClient, timespan);
        } else {
          query(dbClient);
        }
        break;
      default:
        printUsageAndExit();
    }
  } finally {
    spanner.close();
  }
  System.out.println("Closed client");
}
 

protected DatabaseAdminClient getDatabaseAdminClient() {
  return spanner.getDatabaseAdminClient();
}
 

public static void main(String[] args) throws Exception {
  if (!(args.length == 3 || args.length == 4)) {
    printUsageAndExit();
  }
  SpannerOptions options = SpannerOptions.newBuilder().build();
  Spanner spanner = options.getService();
  try {
    String command = args[0];
    DatabaseId db = DatabaseId.of(options.getProjectId(), args[1], args[2]);
    DatabaseClient dbClient = spanner.getDatabaseClient(db);
    DatabaseAdminClient dbAdminClient = spanner.getDatabaseAdminClient();
    switch (command) {
      case "create":
        create(dbAdminClient, db);
        break;
      case "insert":
        String insertType;
        try {
          insertType = args[3];
        } catch (ArrayIndexOutOfBoundsException exception) {
          insertType = "";
        }
        insert(dbClient, insertType);
        break;
      case "query":
        if (args.length == 4) {
          int timespan = 0;
          try {
            timespan = Integer.parseInt(args[3]);
          } catch (NumberFormatException e) {
            System.err.println("query command's 'timespan' parameter must be a valid integer.");
            System.exit(1);
          }
          query(dbClient, timespan);
        } else {
          query(dbClient);
        }
        break;
      case "delete":
        delete(dbAdminClient, db);
        break;
      default:
        printUsageAndExit();
    }
  } finally {
    spanner.close();
  }
  System.out.println("Closed client");
}
 

DatabaseAdminClient getDatabaseAdminClient() {
  return databaseAdminClient;
}
 

@Bean
public SpannerDatabaseAdminTemplate spannerDatabaseAdminTemplate(
		DatabaseAdminClient databaseAdminClient, DatabaseClient databaseClient, DatabaseId databaseId) {
	return new SpannerDatabaseAdminTemplate(databaseAdminClient, () -> databaseClient, () -> databaseId);
}