Cloud Spanner in DBeaver

Google Cloud Spanner is a fully managed, scalable, relational database service for regional and global application data. It is the first scalable, enterprise-grade, globally-distributed, and strongly consistent database service built for the cloud specifically to combine the benefits of relational database structure with non-relational horizontal scale.

One of the advantages of working with a relational database is the large set of standard tooling available. Cloud Spanner includes a JDBC driver that has been added as an extension to DBeaver. This means that you can query and update data in a Cloud Spanner database using DBeaver without the need for any manual installation of drivers. The JDBC driver also includes a number of very useful additional SQL statements that you can use to efficiently work with Cloud Spanner.

This article provides a list of tips and best practices when working with Cloud Spanner through a generic SQL interface such as DBeaver. Most of the tips in this article are also usable in other applications that use the JDBC driver to connect to Cloud Spanner.

Creating a Connection in DBeaver

DBeaver 7.1.3 and higher come with the Cloud Spanner JDBC driver included. Follow these steps to create a connection:

1. Click on New Database Connection
2. Select the Google Spanner driver (not the Google Spanner (Community) driver)
3. Enter the project ID, instance ID, database ID, and select a valid credentials key file.

Batching DDL Statements

Cloud Spanner supports standard DDL statements for creating and altering tables and indexes. It is highly recommended to batch multiple DDL statements together when you need to execute multiple statements in a row. This is often the case when you are creating or altering a test database. The total execution speed of a batch of DDL statements is a lot lower than when each statement is executed individually. Use the START BATCH DDL and RUN BATCH statements to execute a batch of DDL.

Batching DML Statements

Cloud Spanner allows multiple DML statements to be batched together in a single remote procedure call. Using this feature lowers the total execution time of a set of statements. This feature is very similar to the DDL batch feature shown above.

Executing Partitioned DML Statements

Cloud Spanner allows a maximum of 20,000 mutations in a single read/write transaction. Sometimes it is necessary to execute larger transactions. For this, Cloud Spanner supports partitioned DML statements. Partitioned DML statements are also supported by the JDBC driver. The JDBC driver must be in autocommit mode to execute Partitioned DML.

Read-only Transactions

Cloud Spanner supports read-only transactions that offer consistent reads without taking any locks. The default staleness mode when executing a read-only transaction is strong. That means that all data that was committed when the read-only transaction was started will be visible to the transaction. You can choose a different staleness mode if you want to read stale data.

Connecting to the Emulator

Cloud Spanner also offers a local emulator that can be used for testing and development purposes. Connecting to the emulator using JDBC (or DBeaver) has been made as easy as possible in the Spanner JDBC driver version 2.0.0 and later through the addition of the autoConfigEmulatorconnection property. Setting this property to will instruct the JDBC driver to do the following:

1. Use the default emulator host and port (localhost:9010), unless a specific host and port has been specified in the connection string. The connection will also automatically disable SSL, as the emulator does not support that.
2. The instance and database that are referenced in the connection string will automatically be created on the emulator if they do not already exist.

The above means that the connection string jdbc:cloudspanner:/projects/test-project/instances/test-instance/databases/test-database?autoConfigEmulator=true will automatically:

1. Connect to localhost:9010
2. Disable SSL
3. Create test-instance and test-database if any of these do not exist

Steps to Connect to Emulator in DBeaver

Follow these steps to connect to the Cloud Spanner emulator in DBeaver:

1. Start the Google Cloud Spanner emulator using the default configuration using either the gcloud CLI or docker image.
2. Make sure that you have version 2.0.0 or later of the Google Cloud Spanner JDBC driver installed in DBeaver. You do this by clicking on the menu item Database | Driver Manager and then double clicking on the Google Spanner (or Google Cloud Spanner, the name depends on DBeaver version) driver name. Click on the Download/Update button and update the driver to the latest version.
3. Click on New Database Connection in DBeaver and select the Google (Cloud) Spanner driver.
4. Enter a value for Project, Instance and Database (e.g. test-project, test-instance, test-database). Leave the Private Key Path field empty.
5. Click on the Driver Properties tab and change the value for the connection property autoConfigEmulator to true.
6. Click Finish. DBeaver will now connect to the emulator that you started in step 1, and if the instance and database did not already exist, these will automatically have been created on the emulator.

Set autoConfigEmulator to true

Note that when you use autoConfigEmulator, you do not need to set any of the following properties:

1. You do not need to set the host name or port number in the connection string (as long as the emulator is using the default host and port number localhost:9010)
2. You do not need to set the environment variable SPANNER_EMULATOR_HOST.
3. You do not need to set the connection property usePlainText.
4. You do not need configure any credentials.