Cloud SpannerHandler

This is the implementation of the Cloud Spanner handler for MindsDB.

Cloud Spanner

Cloud Spanner is a fully managed, mission-critical, relational database service that offers transactional consistency at global scale, automatic, synchronous replication for high availability. Cloud spanner supports two SQL dialects: GoogleSQL (ANSI 2011 with extensions) and PostgreSQL.

Implementation

This handler was implemented using the google-cloud-spanner python client library.

The arguments to establish a connection are:

• instance_id: the instance identifier.
• database_id: the datbase identifier.
• project: the identifier of the project that owns the resources.
• credentials: a stringified GCP service account key JSON.

Usage

In order to make use of this handler and connect to a Cloud Spanner database in MindsDB, the following syntax can be used:

CREATE DATABASE cloud_spanner_datasource
WITH
engine='cloud_spanner',
parameters={
    "instance_id":"my-instance",
    "database_id":"example-id",
    "project":"my-project",
    "dialect": "postgres" -- optional, default is 'googlesql'
    "credentials":"{...}"
};

Now, you can use this established connection to query your database as follows:

SELECT * FROM cloud_spanner_datasource.my_table;

NOTE : Cloud Spanner supports PostgreSQL syntax and also Google's GoogleSQL dialect. But, not all PostgresSQL dialect features are supported. Find the list of such features below.

• Change streams
• GoogleSQL JSON type (PostgreSQL-dialect databases support the PostgreSQL JSONB type.)
• SELECT DISTINCT (DISTINCT is supported in aggregate functions.)
• FULL JOIN with USING
• Query optimizer versioning
• Optimizer statistics package versioning
• ORDER BY, LIMIT, and OFFSET in UNION,EXCEPT, or DISTINCT statements
• The following columns in SPANNER_SYS statistics tables:
  • Transaction statistics: TOTAL_LATENCY_DISTRIBUTION and OPERATIONS_BY_TABLE
  • Query statistics: LATENCY_DISTRIBUTION
  • Lock Statistics: SAMPLE_LOCK_REQUESTS