Configuring a Google Cloud SQL Data Source

Overview

Explore Analytics provides direct connectivity to Google Cloud SQL for live reporting, allowing you to deliver superb data analysis, visualization, and reporting. The data resides in your Google Cloud SQL instance and there's no need to transfer the data to Explore Analytics.

Google Cloud SQL uses MySQL and therefore you get all the benefits of using Explore Analytics with MySQL:

• Discover all the tables, fields, and their data-types, as well as relationships between tables that are defined in the database using foreign-key constraints. With this information, Explore Analytics can guide the user in the analysis and combine data from multiple tables without requiring the user to grasp the concept of a table join.
• Let MySQL perform the data filtering and aggregating at the source. This means that Explore Analytics never requests more than a few thousand rows from the database, resulting in quick interactive performance suitable for live data exploration with no need to warehouse data on the Explore Analytics server.

Configuring the Data Source

Before You Create the Data Source

• You need to grant Explore Analytics access to your Cloud SQL Instance. This is done using the Google APIs Dashboard. Select the project and the Cloud SQL Instance and then use the "Instance Settings" button (top right). In the Instance Settings dialog, add "exploreanalyticsagent" to the list of authorized applications. "exploreanalyticsagent" is the application id of the Explore Analytics Agent for Cloud SQL.

• A database (MySQL) user in your Cloud SQL Instance for Explore Analytics to connect. This user must have a password. For security reasons, Explore Analytics will not connect using a database user with no password. In particular, if your "root" user has no password, you'll need to use another user. We recommend creating a user for Explore Analytics with only read access to the database.

Creating the Cloud SQL Data Source

Creating a Cloud SQL Data source is done from the Explore Analytics user interface. You'll need to be registered and logged into Explore Analytics. Then from the "Data" menu, select "Add a Google Cloud SQL data source". This will bring up the following dialog.

Data Source Name

This is a name to identify this Cloud SQL database in Explore Analytics.

Description

An optional description of the data source.

Cloud SQL Instance Name

This is your Google Cloud SQL instance name as shown in the Google APIs dashboard.

Database User

This is the database user that Explore Analytics will use to log into your database. In this example, we created a user called "exploreservice" for use by Explore Analytics.

Database Password

This is the password of the database user. Enter it twice for confirmation.

Database Name

A Cloud SQL instance has one or more databases. Please specify the name of the database that you wish to use for Explore Analytics. If you'd like to use more than one database, then create a separate data source for each.

When you click OK, Explore Analytics will connect to your Cloud SQL instance and fetch meta-data information about your tables. You'll see the following progress dialog. When it's done, you're ready to use Explore Analytics with your data!

Refreshing the Data Source

If you add new tables or fields to your database, you can let Explore Analytics know about it by refreshing the data source. From the "Data" menu, select "Browse data sources and tables", then right-click (or touch on a touch device) and select "Refresh data source configuration".

Architecture and Security Notes

To access Cloud SQL instances, Explore Analytics has a Google App Engine application called Explore Analytics Agent for Cloud SQL. We'll refer to it as the agent. The agent provides a gateway between Explore Analytics and the Google Apps cloud. To grant Explore Analytics access to your Cloud SQL instance, you authorize the agent (identified by "exploreanalyticsagent").

Once authorized, the agent connects to your Cloud SQL instance using a database user and password. It is therefore important to use a strong password for all the database users on your Cloud SQL instance. We recommend creating a separate database user for Explore Analytics. That way you can easily revoke access from Explore Analytics by deleting this user and revoking the authorization for the agent.

⇦ Configuring a ServiceNow Data Source

⇧ Table of Contents

⇨ Configuring an Amazon RDS Data Source