AlloyDB-föderierte Abfragen

Als Datenanalyst können Sie Daten in AlloyDB for PostgreSQL mithilfe von föderierten Abfragen über BigQuery abfragen.

Mit der BigQuery-AlloyDB-Föderation kann BigQuery Daten in AlloyDB in Echtzeit abfragen, ohne die Daten kopieren oder verschieben zu müssen.

Hinweise

Daten abfragen

Verwenden Sie die Funktion EXTERNAL_QUERY, um eine föderierte Abfrage aus einer GoogleSQL-Abfrage an Spanner zu senden.

Angenommen Sie speichern eine Kundentabelle in BigQuery und eine Verkaufstabelle in AlloyDB und möchten die beiden Tabellen in einer einzigen Abfrage verknüpfen. Im folgenden Beispiel wird eine föderierte Abfrage an eine AlloyDB-Tabelle namens orders gesendet und die Ergebnisse mit einer BigQuery-Tabelle namens mydataset.customers verknüpft.

Die Beispielabfrage besteht aus 3 Teilen:

  1. Die externe Abfrage SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id wird in der AlloyDB-Datenbank ausgeführt, um das erste Bestelldatum für jeden Kunden über die Funktion EXTERNAL_QUERY abzurufen.

  2. Verknüpfen Sie die Ergebnistabelle der externen Abfrage mit der Kundentabelle in BigQuery anhand von customer_id.

  3. Wählen Sie in den endgültigen Ergebnissen die Kundendaten und das Datum der ersten Bestellung aus.

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;

AlloyDB-Tabellenschema ansehen

Mit der Funktion EXTERNAL_QUERY können Sie information_schema-Tabellen abfragen, um auf Datenbankmetadaten zuzugreifen. Sie können beispielsweise alle Tabellen in der Datenbank auflisten oder sich das Tabellenschema ansehen. Weitere Informationen finden Sie unter PostgreSQL-information_schema-Tabellen.

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

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

Föderierte BigQuery-Abfragen verfolgen

Wenn Sie eine föderierte Abfrage in AlloyDB ausführen, wird die Abfrage in BigQuery mit einem Kommentar wie dem folgenden versehen:

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

Wenn Sie Protokolle auf Abfragenutzung überwachen, können Sie mit der folgenden Anmerkung Abfragen aus BigQuery identifizieren.

  1. Rufen Sie die Seite Log-Explorer auf.

    Zu „Log-Explorer“

  2. Geben Sie auf dem Tab Abfrage die folgende Abfrage ein:

    resource.type="alloydb.googleapis.com/Instance"
textPayload=~"Federated query from BigQuery"

  3. Klicken Sie auf Abfrage ausführen.

    Wenn für BigQuery-föderierte Abfragen Datensätze verfügbar sind, wird unter Abfrageergebnisse eine Liste mit Datensätzen angezeigt, die der folgenden ähnelt.

    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
*/

    Weitere Informationen zu Cloud Logging finden Sie unter Cloud Logging.

Fehlerbehebung

In diesem Abschnitt werden mögliche Fehler beschrieben, die beim Senden einer föderierten Abfrage an AlloyDB auftreten können. Außerdem finden Sie mögliche Lösungen zur Fehlerbehebung.

Problem: Verbindung zum Datenbankserver konnte nicht hergestellt werden. Fehlermeldung: 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.

Lösung: Prüfen Sie, ob Sie gültige Anmeldedaten verwendet und alle Voraussetzungen erfüllt haben, um die Verbindung zu AlloyDB zu erstellen. Prüfen Sie, ob das automatisch erstellte Dienstkonto beim Erstellen einer Verbindung zu AlloyDB die Rolle „AlloyDB-Client“ (roles/alloydb.client) hat. Weitere Informationen finden Sie unter Zugriff auf das Dienstkonto gewähren.

Nächste Schritte