Looker has limited support for Google Cloud Spanner. This page lists the known issues of the current Spanner integration and explains how to connect.
The Google Cloud Spanner JDBC driver is not included in the Looker JAR files. Customer-hosted instances must install the Google Cloud Spanner JDBC driver before connecting Looker.
There are several feature limitations when using Google Cloud Spanner with Looker:
Symmetric aggregates are not supported. At the time of implementation, Spanner SQL did not contain the necessary functions to support the calculations necessary for symmetric aggregates. However, joins are supported.
Time zones are not supported. Time zone support is not implemented with Spanner. Keep in mind that the database’s default time zone is America/Pacific.
DISTINCTaggregates are only available for one column per query. At the time of implementation, Spanner SQL supported
DISTINCTaggregates on only one column per
SELECTstatement at a time. So it is possible that selecting multiple aggregates like
COUNTs on joined tables can return an error. A potential workaround is to calculate
DISTINCTaggregates in a subselect or a derived table.
Getting the Connection Credentials
Log in to the Google Console.
Select the appropriate project:
Select IAM & Admin and then Service accounts:
Select Create service account and fill in the dialog box as follows:
- Service account name: Enter
looker-spanner-serviceor something similar.
- Role: Select Cloud Spanner then Cloud Spanner Database Reader.
- Furnish a new private key: Check the box and select JSON under Key type.
- Service account name: Enter
Click Create and keep track of the following:
- The email address associated with the service account
- The name and location of the JSON credential file that was downloaded
Creating the Looker Connection
This section requires command line access to the machine running Looker. If your instance is hosted by Looker, open a support request in Looker’s Help Center by clicking Contact Us and provide your credential file so that we can perform these steps for you.
Preparing the Credential File
Copy the credential file to the machine that will be running Looker:cp ~/Downloads/spanner-credentials.json ~/looker/
Optional, but recommended: Modify the permissions on the credential file so that only the account running Looker can read it. Use the following commands:chown looker:looker ~/looker/spanner-credentials.json.json chmod 600 ~/looker/spanner-credentials.json.json
Preparing the Looker Environment
The Google Cloud Spanner JDBC driver must have access to the
spanner-credentials.json file to authenticate to a Google Cloud Spanner instance. Depending on the version of the Cloud Spanner JDBC Driver you use, you may need to configure Looker to access this file differently.
Versions of the Cloud Spanner JDBC Driver prior to
1.0.6may have issues with the
PvtKeyPathwhere, despite this parameter being documented, the driver cannot locate the Spanner credential file. In order to connect with earlier drivers, you must set the
GOOGLE_APPLICATION_CREDENTIALSenvironment variable as the path to the
spanner-credentials.jsonon the Looker host. This sets the environment variable for the entire host, so it has potential to cause issues with other Google Cloud processes described below in the Troubleshooting section. For these reasons we recommend using the latest version of the Cloud Spanner JDBC Driver.
Cloud Spanner JDBC Drivers Prior to Version
In the Looker application base directory, usually called
looker, create or edit the
lookerstart.cfg file and add a line similar to this:
/home/looker/spanner-credentials.json is the absolute path to the Spanner credentials file.
Installing the Cloud Spanner JDBC Driver
Follow the steps on the Custom JDBC Drivers documentation page using the values below:
- name: spanner dir_name: spanner module_path: com.simba.cloudspanner.core.jdbc42.CloudSpanner42Driver
This assumes you are using the JDBC 4.2 driver. If you are using 4.1, the module path will be
Configuring the Connection Settings Page
In the Admin section of Looker, navigate to the Connections page and click New Connection. Looker displays this page:
In the Dialect drop-down, select Google Cloud Spanner.
Fill out the page as follows:
- Name: The name of the connection. This is how it is referred to in the LookML model.
- Project Name: The Project ID for the Google Project containing the Spanner database.
- Instance Name: The name of the instance containing the Spanner database.
- Database: The name of the Spanner database.
- Schema: Leave this blank.
- Max Connections: The maximum number of total connections to the Spanner database across all users. The default is 30.
- Connection Pool Timeout: The number of seconds a query will wait before timing out due to a full connection pool.
- Additional Params: For JDBC Driver versions
1.0.6and up, specify PvtKeyPath=path/to/spanner-credentials.json. For prior JDBC Driver versions, leave blank.
Click Test These Settings, and you should see
Can connectin a green box.
Click Add Connection.
The following is a general error meaning that the credential file can’t be found:
There is a known issue when running dialect tests from the Connections page, where
Subclass must implement kill_query_stmt method is returned. This result can safely be ignored:
If you are using a Cloud Spanner JDBC Driver prior to
Only one connection to Google Spanner may be supported per instance, due to requiring the environment variable
GOOGLE_APPLICATION_CREDENTIALSthat affects the entire host. Depending on the role that is selected for the service account, it is possible to connect to separate instances within the same project or separate databases within the same instance.
If you are running Looker on a development machine that has the
gcloudcommand line environment set up, then it is possible that somehow the connection parameters have been misconfigured and Looker is using the
gclouddevelopment credentials to connect to Spanner. This could result in more permission being granted to Looker users than intended. Use
gcloud auth listto show the default credentialed account. Run Looker on a machine without a Google development environment to be absolutely sure. Also see the Google Cloud documentation to learn more.
For Looker to support some features, your database dialect must also support them.
In Looker 21.0, Google Cloud Spanner supports the following Looker features:
After you have connected your database to Looker, you’re ready to configure sign-in options for your users.