SPEAKER: Data is often scattered in many places.
You may store customer tables in BigQuery,
while your sales tables live in Cloud SQL.
Nonetheless, it is important to be
able to join these tables together
when performing analysis.
Cloud SQL Federation from BigQuery
enables you to analyze data residing
in Cloud SQL in real time, without copying or moving data.
It supports both MySQL and Postgres instances,
and Cloud SQL.
After an initial one-time setup, you
can write queries referencing connected Cloud SQL
tables using a special SQL function called External Query.
Let's walk through the steps to complete the initial setup,
and then run an example query.
Step 1.
Enable the BigQuery connection service.
Start in the Cloud console, and select
the project that includes the Cloud SQL instance
that you want to query.
If you would like to start with the same Cloud SQL
instance we are using in this Quick Start,
you may pause the video and first follow the codelab linked
in the description below.
In this project, you will need to have both the BigQuery admin
role and the Cloud SQL editor or Cloud SQL admin role.
Use the left-hand menu to click on APIs and Services.
Click Enable APIs and Services.
And then search for BigQuery connection API.
Click to enable.
Step 2.
Configure public IP connectivity for your Cloud SQL instance.
Use the search bar to navigate to Cloud SQL.
Click the instance name to open its details page.
Select the connections tab.
Make sure the public IP checkbox is marked.
While public IP connectivity is not as secure as private IP
only, your instance will remain inaccessible
from the public internet, because you have not
provided an authorized address.
However, this will make it possible to connect
to your instance from BigQuery.
Click Save, if you made changes.
Step 3.
Set up the Cloud SQL database connection in BigQuery.
Use the search bar to navigate to BigQuery.
Click Add Data.
And select External Data Source.
Now you'll provide the details needed to establish
the connection resource.
Choose your database type-- in this case, MySQL.
Give the connection ID and name.
You will use this identifier when you are referencing
the resource from a query.
The location you choose must be compatible with your Cloud SQL
region and your intended use within BigQuery.
Since our Cloud SQL database is located in US Central one,
we will need to use the US multi region location.
More information on location compatibility
is linked in the description below.
Enter the full name of the Cloud SQL instance
in the format of project ID, colon, location ID, colon,
instance ID.
You can find the instance ID on the details page of the Cloud
SQL instance you want to query.
For database name, enter the name of the database.
Then enter the username and password for the database.
Note-- if these same user credentials are
valid for other databases in the particular Cloud SQL instance,
the connection will also allow queries
over those additional databases through the same connection
resource.
Click Create Connection.
You'll see the connection populate in the left-hand nav.
Step 4.
Grant permissions to connection users.
Select the connection in the left-hand nav and click Share
Connection.
Enter the user's address and select BigQuery Connection
User, which gives the user the ability
to query with the connection.
Alternatively, you can give a user
the role BigQuery connection admin,
which allows them to create, manage, and share connections,
in addition to using them in queries.
Now that we've completed the initial setup,
let's see how to write queries over tables
in the connected database.
Our first query joins the employees and salaries tables,
both in the Cloud SQL database, to analyze the average salary
by year of hire.
Querying over the external connection
requires the external query function.
The syntax of this function requires the connection ID,
which you can find by selecting the connection
and looking in the details.
Next is a string of the query in the external databases SQL
dialect.
Note that when querying a Cloud SQL connection,
BigQuery can not predict the amount of data to be processed.
You will only know after you run your query, when you hit Run.
The query is executed in the external database in Cloud SQL.
Next, let's see an example of joining a BigQuery
table with a table in your Cloud SQL database.
We have taken a snapshot of the salaries table
and loaded it into BigQuery's, so
that we can show joining data across BigQuery and Cloud SQL
resources.
The example query includes three parts.
External query is used to select the employee number
and hire date fields from the Cloud SQL employees table.
Then, the results are joined by employee number
with the salaries table which is located in BigQuery.
Finally, the query calculates the average salary
for employees in each hire year.
Click Run.
And after the query completes, you'll see the same results.
The cost of your queries will follow standard BigQuery
pricing, either on demand, based on the amount of data
processed, or as part of a flat rate model
if that is applicable to your organization.
You've just seen how to set up a Cloud SQL connection
and query the connection using the external query function.
For more information on Federated queries
with Cloud SQL, please navigate to the documentation
linked below.
Happy analyzing.
[MUSIC PLAYING]