AlloyDB federated queries

As a data analyst, you can query data in AlloyDB for PostgreSQL from BigQueryusingfederated queries.

BigQuery AlloyDB federation lets BigQueryquery data residing in AlloyDB in real time without copying ormoving the data.

Before you begin

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

• To get the permissions that you need to query an AlloyDB 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 AlloyDB from aGoogleSQL query, use theEXTERNAL_QUERY function.

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

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 AlloyDB databaseto get the first order date for each customer throughtheEXTERNAL_QUERY function.

2. Join the external query results table with the customers table inBigQuery bycustomer_id.

3. Select customer information and first order date in the final result set.

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;

View an AlloyDB table schema

You can use theEXTERNAL_QUERY function to queryinformation_schema tablesto access database metadata. For example, you can list all of the tables in thedatabase or view the table schema. For more information, seePostgreSQL information_schema tables.

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

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

Track BigQuery federated queries

When you run a federated query against AlloyDB,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, the following annotation can help youidentify 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="alloydb.googleapis.com/Instance"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*/

   For more information about Cloud Logging, seeCloud Logging.

Troubleshooting

This section describes potential errors you might encounter when sending afederated query to AlloyDB and provides possible troubleshootingresolutions.

Issue: Failed to connect to the database server with this 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 you used valid credentials and followed all prerequisiteswhile creating theconnection to AlloyDB.Check if the service account that is automatically createdwhen a connection to AlloyDB iscreated has the AlloyDB Client (roles/alloydb.client) role.For more information, seeGrant access to the service account.

What's next

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