Looker supports the Google Cloud Spanner relational database. This page lists the known issues with connecting to Spanner and explains how to make a connection.
Features and Known Issues
This section explains several features that are and are not supported when Looker connects to Google Cloud Spanner.
PDTs Are Not Supported
The Spanner JDBC driver is read-only, so it is not possible for Looker to create persistent derived tables. Derived tables are supported, however.
Symmetric Aggregates Are Not Supported
Spanner SQL doesn’t contain the necessary functions to support the calculations necessary for symmetric aggregates. SQL joins are supported, however.
Time Zones Are Not Supported
Time zone support is not implemented in this initial version of Spanner support. Keep in mind that the database’s default time zone is America/Pacific.
DISTINCT Aggregates Are Only Available for One Column per Query
Spanner SQL supports
DISTINCT aggregates on only one column 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
DISTINCT aggregates in a subselect or a derived table.
Only One Connection to Google Spanner Is Supported per Instance
Due to the way Spanner authenticates service accounts, only one connection to Google Spanner is supported per instance.
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.
An upcoming JDBC driver will contain a fix for this issue. See your Account Manager if this issue affects you.
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, contact firstname.lastname@example.org with 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/looker-spanner-69cb06906dc0.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/looker-spanner-69cb06906dc0.json chmod 600 ~/looker/looker-spanner-69cb06906dc0.json
Preparing the Looker Environment
The Google Cloud Spanner JDBC driver uses the environment variable
GOOGLE_APPLICATION_CREDENTIALS to locate the credential file, so it must be set in the environment that runs Looker.
Create or edit the
lookerstart.cfgfile and add a line similar to this:export GOOGLE_APPLICATION_CREDENTIALS=/home/looker/looker-spanner-69cb06906dc0.json
Start Looker with
./looker start, or restart Looker with
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 across all users to the Spanner database. 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: Leave this blank.
Click Test These Settings, and you should see
Can connectin a green box.
Click Add Connection.
PvtKeyPathis documented as a JDBC connection parameter, but it doesn’t appear to work for authenticating a valid service account credential file. This is true even though it is able to detect the location of the file, whether the file is valid JSON, and whether the embedded key is valid. This is the error when pointing to a credential file:Cannot connect: connection refused: Java::JavaSql::SQLException: [Simba]SpannerJDBCDriverFailed to connect to Spanner: A project ID is required for this service but could not be determined from the builder or the environment. Please set a project ID using the builder.
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.
If you receive the following error:Cannot connect: connection refused: Java::JavaSql::SQLException: [Simba]SpannerJDBCDriver Failed to connect to Spanner: A project ID is required for this service but could not be determined from the builder or the environment. Please set a project ID using the builder.
This 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 methodis returned:
This can be safely ignored.
Looker’s ability to provide some features depends on whether the database dialect can support them.
In the current Looker release, 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.