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:
- Click on
New Database Connection
- Select the
Google Spanner
driver (not theGoogle Spanner (Community)
driver) - 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 autoConfigEmulator
connection property. Setting this property to will instruct the JDBC driver to do the following:
- 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. - The
instance
anddatabase
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:
- Connect to
localhost:9010
- Disable SSL
- Create
test-instance
andtest-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:
- Start the Google Cloud Spanner emulator using the default configuration using either the gcloud CLI or docker image.
- 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. - Click on New Database Connection in DBeaver and select the Google (Cloud) Spanner driver.
- Enter a value for Project, Instance and Database (e.g.
test-project, test-instance, test-database
). Leave the Private Key Path field empty. - Click on the Driver Properties tab and change the value for the connection property
autoConfigEmulator
totrue
. - 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.
Note that when you use autoConfigEmulator
, you do not need to set any of the following properties:
- 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)
- You do not need to set the environment variable SPANNER_EMULATOR_HOST.
- You do not need to set the connection property usePlainText.
- You do not need configure any credentials.