You can translate the document:

NOTE: The Google Cloud Spanner adapter will be added in the update for Denodo 8.0 scheduled for December 2023. Please contact Denodo support if you want to access a preview of this feature. It is possible to integrate with Spanner with older updates (see section Connecting to Google Cloud Spanner using the JDBC driver) but advanced capabilities like "Data Boost" will not be available.

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

  1. Open the Denodo Design Studio or the Virtual DataPort Administration Tool, log in and go to File > New > Data source > JDBC. The configuration of a new JDBC source opens.

Creation of a data source.

  1. Enter the connection details for your spanner database and click OK:
  • Provide a name, for example ds_spanner
  • Select Google Spanner as Database adapter
  • Database URL:                 

jdbc:cloudspanner:/projects/<PROJECT_ID>/instances/<INSTANCE_NAME>/databases/<DB_NAME>;credentials=<PATH_TO_CREDENTIALS_FILE>

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

Download the JDBC driver

First of all, download the JDBC driver to connect to Google Cloud Spanner. It can be downloaded from https://cloud.google.com/spanner/docs/jdbc-drivers. We are going to use the JDBC interface for connecting, as the recommendation is to use JDBC whenever possible due to better performance.

Install the JDBC Driver in the Denodo Platform

Now that we have downloaded the driver, we need to install it. Since Denodo 8.0, you can upload the JDBC driver from your client to the Denodo installation where you are connected to.

This can be done via the Virtual DataPort Administration Tool by navigating to File > Extension Management > Libraries > Import.

In that window, you need to choose  jdbc_other and provide a custom name and select the corresponding JDBC driver file that you have downloaded.

 

Note: For previous versions, the recommended way is to copy the driver jars to the folder <DENODO_HOME>/lib-external/jdbc-drivers/database name - version

Later, you need to provide the 'database name - version' in the driver classpath.

Connect to Google Cloud Spanner

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

  1. Open the Denodo Design Studio or the Virtual DataPort Administration Tool, log in and go to File > New > Data source > JDBC. The configuration of a new JDBC source opens.

Creation of a data source.

  1. Enter the connection details for your spanner database and click OK:
  • Provide a name, for example ds_spanner
  • Select Google Spanner as Database adapter
  • Database URL:                 

jdbc:cloudspanner://ProjectId=<PROJECT_ID>;Instance=<instance_name>;Database=<db_name>;PvtKeyPath=<PATH_TO_CREDENTIALS_FILE>

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

  • Under the “Advanced” option, select the Driver class path that you have uploaded previously CloudSpanner42Driver and Driver class as com.simba.cloudspanner.core.jdbc42.CloudSpanner42Driver

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.

References

Cloud Spanner

Virtual DataPort Administration Guide: Uploading a JDBC Driver

Virtual DataPort Administration Guide: JDBC Sources

Data Boost overview

Questions

Ask a question

You must sign in to ask a question. If you do not have an account, you can register here