URL and Connection Properties

Cloud Spanner Connection Properties

The Google Cloud Spanner JDBC Driver supports the following connection properties and URL parts:

• Base URL: jdbc:cloudspanner://localhost (Required)
• Project: The project id on Google Cloud (Required; E.g. helpful-hall-123456)
• Instance: The instance id on Google Cloud Spanner (Required; E.g. test-instance)
• Database: The database name on Google Cloud Spanner (Required; E.g. test-db)
• UseCustomHost: (true/false) Use this property to specify whether the driver should connect to a custom host, such as for example an emulator instead of to Google Cloud Spanner itself. If this property is set to true, the driver will use the host name specified in the URL. If the property is not set, or is set to false, the driver will always connect to Google Cloud Spanner, regardless of the host name specified in the URL.
• PvtKeyPath: Path to a local key file for a service account with permission to access Cloud Spanner (Required when OAuthAccessToken is not set; E.g. /home/user/cloudspanner-key.json)
• OAuthAccessToken: An OAuth access token for a session with access to Google Cloud Spanner (It is recommended to use PvtKeyPath instead of an OAuth access token)
• SimulateProductName: Use this property in order to get the Cloud Spanner driver to report a different product name than Google Cloud Spanner when the method DatabaseMetaData#getDatabaseProductName() is called. Some applications and frameworks use this method to determine how certain queries should be generated. If such a framework does not support Google Cloud Spanner, you can use this property into letting the framework think it is communicating with some other database. One known example where this is necessary, is when using Google Cloud Spanner in combination with Spring Batch and JPA. This property, as well as the 'simulate version' properties do not actually change the behaviour of the driver or connections, other than the product name and version returned by the driver.
• SimulateProductMajorVersion: Use this property to simulate a specific version of another DBMS.
• SimulateProductMinorVersion: Use this property to simulate a specific version of another DBMS.
• AllowExtendedMode: Set this property to true to allow larger bulk inserts/updates/deletes than normally allowed by Cloud Spanner. When this property is set, the driver will try to break large updates into several batches in order to circumvent the known transaction quotas of Google Cloud Spanner. Large insert/update/delete statements will be executed in several batches, each batch being its own transaction separate from the running transaction on your current JDBC connection. This means that the statement will NOT be executed as one single transaction, but as a number of transactions, each of which could fail. This property can be turned on and off dynamically using a SET_CONNECTION_PROPERTY statement.
• AsyncDdlOperations: Set this property to true to allow DDL-statements (CREATE TABLE, ALTER TABLE, ...) to be executed asynchronously. If this property is not set (or set to false), DDL-statements will be executed synchronously and you will have to wait for Cloud Spanner to finish the update before you can use the connection again. DDL-statements can take quite a long time, so set this property to true if you plan on executing multiple DDL-statements and you do not have a good reason for leaving it off. You can use the commands SHOW_DDL_OPERATIONS and CLEAN_DDL_OPERATIONS for monitoring the status of asynchronous DDL-statements. This property can be turned on and off using a SET_CONNECTION_PROPERTY statement.
• AutoBatchDdlOperations: Set this property to true to automatically batch DDL-statements together for parallel execution. This property is especially handy when you work with a Cloud Spanner database through a standard database tool such as DBeaver. Instead of executing each DDL-statement individually, this option lets you automatically group DDL-statements together and send them as one batch to Cloud Spanner. This is a lot faster than executing them one by one sequentially, and also faster than executing them one-by-one asynchronously. After issuing a number of DDL-statements in AutoBatchDdlOperations mode, you must issue a EXECUTE_DDL_STATEMENTS command in order to send the statements to Cloud Spanner.
• BatchReadOnlyMode: Set this property to instruct a connection to use the BatchClient API of Google Cloud Spanner instead of the default DatabaseClient API. The BatchClient allows large queries to be divided into several partitions that can be processed in parallel. When a connection is in BatchReadOnlyMode and a query is passed to one of the methods Statement#execute(String) or PreparedStatement#execute(), the driver will create a partitioned query and return multiple result sets that can be processed in parallel. The result sets can be fetched by calling the standard JDBC methods Statement#getResultSet() and Statement#getMoreResults(int). Make sure you call Statement#getMoreResults(Statement.KEEP_CURRENT_RESULT) when iterating over the result sets to pass these to worker threads in order to keep all result sets opened. The BatchReadOnlyMode can be turned on/off by executing a 'SET_CONNECTION_PROPERTY BatchReadOnlyMode=true/false' statement, or by calling ICloudSpannerConnection#setBatchReadOnly(boolean).

If AllowExtendedMode is not set (or set to false), the driver will never try to break up large updates into several batches. If you issue a statement that exceeds the transaction quotas of Cloud Spanner, Cloud Spanner will report an error and the transaction will be rolled back. You could still try to split your statement into several smaller updates and run these manually in different transactions.

Example URL

jdbc:cloudspanner://localhost;Project=adroit-valve-123456;Instance=test-instance;Database=test-db;PvtKeyPath=C:\Users\MyUserName\Documents\CloudSpannerKeys\cloudspanner-key.json;AllowExtendedMode=true;AsyncDdlOperations=true