Cloud SQL federated queries

As a data analyst, you can query data in Cloud SQL from BigQueryusingfederated queries.

BigQuery Cloud SQL federation enables BigQueryto query data residing in Cloud SQL in real time, without copying ormoving data. Query federation supports both MySQL (2nd generation) and PostgreSQLinstances in Cloud SQL.

Alternatively, to replicate data intoBigQuery, you can also use Cloud Data Fusion orDatastream. For more about usingCloud Data Fusion, seeReplicating data from MySQL toBigQuery.

Before you begin

• Ensure that your BigQuery administrator has created aCloud SQL connection andshared it with you.

• To get the permissions that you need to query a Cloud SQL instance, ask your administrator to grant you theBigQuery Connection User (roles/bigquery.connectionUser) IAM role on your project. For more information about granting roles, seeManage access to projects, folders, and organizations.

  You might also be able to get the required permissions throughcustom roles or otherpredefined roles.

Query data

To send a federated query to Cloud SQL from aGoogleSQL query, use theEXTERNAL_QUERY function.

Suppose that you store a customer table in BigQuery, whilestoring a sales table in Cloud SQL, and want to join the two tables ina single query. The following example makes a federated query to aCloud SQL table namedorders and joins the results with aBigQuery table namedmydataset.customers.

SELECTc.customer_id,c.name,rq.first_order_dateFROMmydataset.customersAScLEFTOUTERJOINEXTERNAL_QUERY('us.connection_id','''SELECT customer_id, MIN(order_date) AS first_order_date  FROM orders  GROUP BY customer_id''')ASrqONrq.customer_id=c.customer_idGROUPBYc.customer_id,c.name,rq.first_order_date;

The example query includes 3 parts:

1. Run the external querySELECT customer_id, MIN(order_date) ASfirst_order_date FROM orders GROUP BY customer_id in the operationalPostgreSQL database to get the first order date for each customer throughtheEXTERNAL_QUERY() function.
2. Join the external query result table with the customers table inBigQuery bycustomer_id.
3. Select customer information and first order date.

View a Cloud SQL table schema

You can use theEXTERNAL_QUERY() function to query information_schema tablesto access database metadata, such as list all tables in the database or showtable schema. The following example information_schema queries work in bothMySQL and PostgreSQL. You can learn more fromMySQL information_schema tables andPostgreSQL information_schema tables.

-- List all tables in a database.SELECT*FROMEXTERNAL_QUERY("connection_id","select * from information_schema.tables;");

-- List all columns in a table.SELECT*FROMEXTERNAL_QUERY("connection_id","select * from information_schema.columns where table_name='x';");

Connection details

The following table shows the Cloud SQL connection properties:

The following table shows the properties for the Cloud SQL instance credential:

Track BigQuery federated queries

When you run a federated query against Cloud SQL,BigQuery annotates the query with a comment similar to the following:

/* Federated query from BigQuery. Project ID:PROJECT_ID, BigQuery Job ID:JOB_ID. */

If you are monitoring logs for query usage on a MySQL or PostgreSQL database,the following annotation can help you identify queries coming from BigQuery.

1. Go to theLogs Explorer page.

   Go to the Logs Explorer

2. In theQuery tab, enter the following query:

   resource.type="cloudsql_database"textPayload=~"Federated query from BigQuery"

3. ClickRun query.

   If there are records available for BigQuery federated queries,a list of records similar to the following appears inQuery results:

   YYYY-MM-DD hh:mm:ss.millis UTC [3210064]: [4-1]db=DATABASE, user=USER_ACCOUNTSTATEMENT: SELECT 1 FROM (SELECT FROM company_name_table) t;/* Federated query from BigQuery.Project ID:PROJECT_ID, BigQuery Job ID:JOB_ID*/YYYY-MM-DD hh:mm:ss.millis UTC [3210532]: [2-1]db=DATABASE, user=USER_ACCOUNTSTATEMENT: SELECT "company_id", "company type_id" FROM(SELECT FROM company_name_table) t;/* Federated query from BigQuery.Project ID:PROJECT_ID, BigQuery Job ID:JOB_ID*/

Troubleshooting

This section helps you troubleshoot issues you might encounter when sendinga federated query to Cloud SQL.

Issue: Failed to connect to database server. If you are querying a MySQLdatabase, you might encounter the following error:

Invalid table-valued function EXTERNAL_QUERY Failed to connect to MySQL database. Error: MysqlErrorCode(2013): Lost connection to MySQL server during query.

Alternatively, if you are querying a PostgreSQL database, you might encounterthe following error:

Invalid table-valued function EXTERNAL_QUERY Connect to PostgreSQL server failed: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.

Resolution: Ensure that valid credentials were used and all prerequisiteswere followed to create theconnection for Cloud SQL.Check if theservice account that is automatically createdwhen a connection to Cloud SQL iscreated has the Cloud SQL Client (roles/cloudsql.client) role. The serviceaccount is of the following format:service-PROJECT_NUMBER@gcp-sa-bigqueryconnection.iam.gserviceaccount.com.For detailed instructions, seeGrant access to the service account.

What's next

• Learn aboutfederated queries.
• Learn aboutMySQL to BigQuery data type mapping.
• Learn aboutPostgreSQL to BigQuery data type mapping.
• Learn aboutunsupported data types.