Introduction

Spanner is a NewSQL database designed, built, and deployed at Google for OLTP systems. It is a fully managed, mission-critical, relational database service that offers transactional consistency at a global scale, schemas, SQL, and automatic, synchronous replication for high availability.

Denodo can connect to Google Cloud Spanner in order to use it as a data source and to import information. In this document, we will learn how to configure Denodo to access Google Cloud Spanner using a JDBC driver.

The document is structured in two sections:

• Google Cloud Spanner configuration: Describing the required configuration steps to create a service account in Google Cloud before being able to connect through JDBC.
• Denodo configuration: Describing the required steps to create a Denodo data source and base view on top of Google Cloud Spanner.

Sample Google Cloud Spanner Database

We have created a sample table called ‘Category’ inside a database of a particular instance, which contains simple data as shown.

Note: ‘Primary key’ is mandatory while creating a particular table in Google Cloud Spanner.

Table in Google Cloud Spanner

Data of ‘category’ table in Google Cloud Spanner

Creating a Service account and Key in Google Cloud Spanner

• Go to the Google Cloud Platform Dashboard and click on “IAM & Admin”:

‘IAM & Admin’ option.

• Once in “IAM & Admin”, click “Service Accounts”.

Creating the service account.

• Fill in the form providing the name of the service account to create your account.

Creating the service account.

• Then, specify a ‘Role’ as per your requirement. For example, a member with ‘Cloud Spanner Database Admin’ title has full control of spanner databases.

Assigning a Role.

• The next step is to generate a key to access the data.

Creating the key.

• For this particular case, we will generate a JSON key that will download a file with the key and the proper credentials.

Generation of the key file.

• The generated JSON key file has the necessary details needed for OAuth 2.0 authentication, such as private key, client_id, auth_uri, token_uri, etc. Now we can go to the Denodo Platform to finish the configuration.

Data Boost

Cloud Spanner Data Boost is a fully managed, serverless service that provides independent compute resources for supported Cloud Spanner workloads. Data Boost lets you execute analytics queries and data exports with near-zero impact to existing workloads on the provisioned Spanner instance.

The service consists of Spanner clusters that Google manages at the region level. For eligible queries that request Data Boost, Spanner routes the workload to these servers transparently. Eligible queries are those for which the first operator in the query execution plan is a distributed union. These queries don't have to change to take advantage of Data Boost.

Connect to Google Cloud Spanner with Data Boost

To connect to Google Cloud Spanner, you need to create a JDBC data source within the Denodo Platform. Then you can create the base views that will represent the Spanner tables.

Create a JDBC Data Source

Open the Denodo Design Studio or the Virtual DataPort Administration Tool, log in and go to File > New > Data source. Select Google Spanner data source. The configuration of Google Spanner source opens.

Creation of a data source.

Enter the connection details for your spanner database and click OK:

• Provide a name, for example ds_spanner
• Database URL:                 

jdbc:cloudspanner:/projects/<PROJECT_ID>/instances/<INSTANCE_ID>/databases/<DB_NAME>;credentials=<PATH_TO_CREDENTIALS_FILE>;lenient=true

The values for PROJECT_ID can be obtained from the credentials JSON file created above.

• Under the “Advanced” option, check the Use data boost and auto-partition mode. 

If everything is correct, save the data source. You can verify this by clicking on Test Connection.

Create a Base View

To create a base view of the tables you want to query, follow these steps:

• Switch to the tab CREATE BASE VIEW
• Click + to expand the schema name you are interested in.
• Select the tables (in this case, the base view could be ‘category’ table) you want to import.
• Click on Create Selected

Creation of a base view.

Note: If the view is created using the ‘Create from Query’ option and has "@" and "{ " special characters in them, these characters need to be escaped with the "\" (backslash) separately.

The base view is now created:

The schema of the example base view.

Finally, the results of our execution will look like this:

The results of the base view we created in our example.

Connecting to Google Cloud Spanner using the JDBC driver

Note: Starting from the Denodo 8.0u20240306 update, Denodo provides a prebuilt JDBC adapter for Google Spanner.

Create a JDBC Data Source

Open the Denodo Design Studio or the Virtual DataPort Administration Tool, log in and go to File > New > Data source. Select Google Spanner data source. The configuration of Google Spanner source opens.

Enter the connection details for your spanner database and click OK:

• Provide a name, for example ds_spanner
• Database URL:                 

jdbc:cloudspanner://ProjectId=<PROJECT_ID>;Instance=<instance_ID>;Database=<db_name>;lenient=true

• Select Authentication as Service-based OAuth Authentication and upload the credentials JSON file.

The values for PROJECT_ID can be obtained from the credentials JSON file created above.

• Under the “Advanced” option, select spanner as Driver class path and provide com.google.cloud.spanner.jdbc.JdbcDriver as Driver Class.

If everything is correct, save the data source. You can verify this by clicking on Test Connection.

Create a Base View

To create a base view of the tables you want to query, you can follow the same steps as when using Data Boost.

Note: For versions, before the Denodo 8.0u20240306 update, you need to download and install the JDBC Driver in the Denodo Platform.

References

Cloud Spanner

Virtual DataPort Administration Guide: Uploading a JDBC Driver

Virtual DataPort Administration Guide: JDBC Sources

Data Boost overview