Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from http://cloud.google.com/bigquery/docs/cloud-sql-federated-queries below:

Cloud SQL federated queries | BigQuery

Stay organized with collections Save and categorize content based on your preferences.

As a data analyst, you can query data in Cloud SQL from BigQuery using federated queries.

BigQuery Cloud SQL federation enables BigQuery to query data residing in Cloud SQL in real time, without copying or moving data. Query federation supports both MySQL (2nd generation) and PostgreSQL instances in Cloud SQL.

Alternatively, to replicate data into BigQuery, you can also use Cloud Data Fusion or Datastream. For more about using Cloud Data Fusion, see Replicating data from MySQL to BigQuery.

• Ensure that your BigQuery administrator has created a Cloud SQL connection and shared it with you.

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

  You might also be able to get the required permissions through custom roles or other predefined roles.

To send a federated query to Cloud SQL from a GoogleSQL query, use the EXTERNAL_QUERY function.

Suppose that you store a customer table in BigQuery, while storing a sales table in Cloud SQL, and want to join the two tables in a single query. The following example makes a federated query to a Cloud SQL table named orders and joins the results with a BigQuery table named mydataset.customers.

SELECT c.customer_id, c.name, rq.first_order_date
FROM mydataset.customers AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
  'us.connection_id',
  '''SELECT customer_id, MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY customer_id''') AS rq ON rq.customer_id = c.customer_id
GROUP BY c.customer_id, c.name, rq.first_order_date;

The example query includes 3 parts:

1. Run the external query SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id in the operational PostgreSQL database to get the first order date for each customer through the EXTERNAL_QUERY() function.
2. Join the external query result table with the customers table in BigQuery by customer_id.
3. Select customer information and first order date.

You can use the EXTERNAL_QUERY() function to query information_schema tables to access database metadata, such as list all tables in the database or show table schema. The following example information_schema queries work in both MySQL and PostgreSQL. You can learn more from MySQL information_schema tables and PostgreSQL information_schema tables.

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

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

The following table shows the Cloud SQL connection properties:

Project-id:location-id:instance-id

You can find the instance ID in the Cloud SQL instance detail page.

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

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 the Logs Explorer page.

   Go to the Logs Explorer

2. In the Query tab, enter the following query:

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

3. Click Run query.

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

   YYYY-MM-DD hh:mm:ss.millis UTC [3210064]: [4-1]
   db=DATABASE, user=USER_ACCOUNT
   STATEMENT: 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_ACCOUNT
   STATEMENT: 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
   */
   

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

Issue: Failed to connect to database server. If you are querying a MySQL database, 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 encounter the 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 prerequisites were followed to create the connection for Cloud SQL. Check if the service account that is automatically created when a connection to Cloud SQL is created has the Cloud SQL Client (roles/cloudsql.client) role. The service account is of the following format: service-PROJECT_NUMBER@gcp-sa-bigqueryconnection.iam.gserviceaccount.com. For detailed instructions, see Grant access to the service account.

• Learn about federated queries.
• Learn about MySQL to BigQuery data type mapping.
• Learn about PostgreSQL to BigQuery data type mapping.
• Learn about unsupported data types.

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-08-07 UTC.

[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-08-07 UTC."],[[["Cloud SQL federated queries allow data analysts to query data in Cloud SQL directly from BigQuery in real time, without the need to copy or move the data, supporting both MySQL and PostgreSQL instances."],["To perform a federated query, you must use the `EXTERNAL_QUERY` function within a GoogleSQL query, and a Cloud SQL connection must have been created and shared with you by your administrator, while ensuring you have the required BigQuery Connection User IAM role."],["You can join data from a Cloud SQL table with a BigQuery table in a single query using the `EXTERNAL_QUERY` function, as demonstrated by an example joining a Cloud SQL `orders` table with a BigQuery `customers` table."],["The `EXTERNAL_QUERY()` function can also be used to access Cloud SQL database metadata, such as listing all tables or showing a specific table's schema by querying the `information_schema` tables."],["When a federated query is run, BigQuery annotates the query with a comment including the Project ID and BigQuery Job ID, which can be used to identify these queries in the database logs using the Logs Explorer."]]],[]]

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4