Föderierte Abfragefunktionen

Google SQL for BigQuery unterstützt die folgenden Funktionen für föderierte Abfragen.

Funktionsliste

Name Fazit
EXTERNAL_QUERY Führt eine Abfrage für eine externen Datenbank aus und gibt die Ergebnisse als temporäre Tabelle zurück.

EXTERNAL_QUERY

EXTERNAL_QUERY('connection_id', '''external_database_query'''[, 'options'])

Beschreibung

Führt eine Abfrage für eine externe Datenbank aus und gibt die Ergebnisse als temporäre Tabelle zurück. Der Datentyp der externen Datenbank wird in der temporären Ergebnistabelle in einen GoogleSQL-Datentyp mit diesen Datentypzuordnungen umgewandelt.

  • external_database_query: Die Abfrage, die für die externe Datenbank ausgeführt werden soll.

  • connection_id: Die ID der Verbindungsressource. Die Verbindungsressource enthält Einstellungen für die Verbindung zwischen der externen Datenbank und BigQuery. Wenn Sie kein Standardprojekt konfiguriert haben, stellen Sie die Projekt-ID der Verbindungs-ID im folgenden Format voran:

    projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID

    Dabei gilt:

    • PROJECT_ID: die Projekt-ID
    • LOCATION: der Standort der Verbindung
    • CONNECTION_ID: die Verbindungs-ID

    Beispiel: projects/example-project/locations/us/connections/sql-bq. Weitere Informationen finden Sie unter Verbindungsressource erstellen.

+ options: Ein optionaler String einer JSON-Formatzuordnung mit Schlüssel/Wert-Paaren von Optionsnamen und Wert. Bei beiden wird die Groß- und Kleinschreibung berücksichtigt.

For example::
``` '{"default_type_for_decimal_columns":"numeric"}' ```

Supported options:

|Option Name | Description
|-------- | -------
|"default_type_for_decimal_columns" | Can be "float64", "numeric", "bignumeric" or "string". With this option, the MySQL Decimal type or PostgreSQL Numeric type will be mapped to the provided BigQuery type. When this option is not provided, the MySQL Decimal type or PostgreSQL Numeric type will be mapped to BigQuery NUMERIC type.
|"query_execution_priority" | Can be "low", "medium" or "high". Only supported in Spanner. Specifies priority for execution of the query. Execution priority is "medium" by default.

Zusätzliche Hinweise:

  • Die Funktion EXTERNAL_QUERY wird in der Regel in einer FROM-Klausel verwendet.
  • Mit der Funktion EXTERNAL_QUERY() können Sie auf Metadaten zur externen Datenbank zugreifen.
  • EXTERNAL_QUERY() berücksichtigt die Reihenfolge des Ergebnisses der externen Abfrage nicht, auch wenn die externe Abfrage ORDER BY enthält.

Rückgabedatentyp

BigQuery-Tabelle

Beispiele

Angenommen, Sie benötigen für jeden Ihrer Kunden das Datum der ersten Bestellung, damit dieses in einen Bericht aufgenommen werden kann. Diese Daten befinden sich derzeit nicht in BigQuery, sind jedoch in Ihrer operativen PostgreSQL-Datenbank in verfügbar. Mit dem folgenden Beispiel für eine föderierte Abfrage wird dies erreicht. Es besteht aus drei Teilen:

  1. Die externe Abfrage SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id wird in der operativen PostgreSQL-Datenbank ausgeführt, um das erste Bestelldatum für jeden Kunden über die Funktion EXTERNAL_QUERY() abzurufen.
  2. Die Ergebnistabelle der externen Abfrage wird mit der Kundentabelle in BigQuery nach customer_id verknüpft.
  3. Die Kundendaten und das Datum der ersten Bestellung werden ausgewählt.
SELECT
  c.customer_id, c.name, SUM(t.amount) AS total_revenue, rq.first_order_date
FROM customers AS c
INNER JOIN transaction_fact AS t ON c.customer_id = t.customer_id
LEFT OUTER JOIN
  EXTERNAL_QUERY(
    '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;

Mit der Funktion EXTERNAL_QUERY() können Sie information_schema-Tabellen abfragen, um auf Datenbankmetadaten zuzugreifen, z. B. um eine Liste aller Tabellen in der Datenbank oder das Tabellenschema abzurufen. Die folgenden information_schema-Beispielabfragen funktionieren sowohl in MySQL als auch in PostgreSQL.

-- 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';'''
  );

EXTERNAL_QUERY() berücksichtigt nicht die Reihenfolge im Ergebnis externer Abfragen, selbst wenn die externe Abfrage ORDER BY enthält. Mit der folgenden Beispielabfrage werden Zeilen in Cloud SQL zwar nach der Kunden-ID sortiert, BigQuery gibt die Ergebniszeilen jedoch nicht in dieser Reihenfolge aus.

-- ORDER BY will not order rows.
SELECT *
FROM
  EXTERNAL_QUERY(
    'connection_id',
    '''SELECT * FROM customers AS c ORDER BY c.customer_id'''
  );

Datentypzuordnungen

Wenn Sie eine föderierte Abfrage ausführen, werden die Daten aus der externen Datenbank in GoogleSQL-Typen umgewandelt. Nachfolgend sind die Datentypzuordnungen von MySQL zu BigQuery und von PostgreSQL zu BigQuery aufgeführt.

Wissenswertes über Zuordnungen:

  • Die meisten MySQL-Datentypen können mit einigen Ausnahmen wie etwa decimal, timestamp und time demselben BigQuery-Datentyp zugeordnet werden.
  • PostgreSQL unterstützt viele nicht standardmäßige Datentypen, die in BigQuery nicht unterstützt werden, zum Beispiel money, path, uuid, boxer und andere.
  • Die numerischen Datentypen in MySQL und PostgreSQL werden standardmäßig dem BigQuery-Wert NUMERIC zugeordnet. Der NUMERIC-Wertebereich ist in BigQuery kleiner als in MySQL und PostgreSQL. Er kann auch mit BIGNUMERIC, FLOAT64 oder STRING mit "default_type_for_decimal_columns" in EXTERNAL_QUERY-Optionen verknüpft werden.

Fehlerbehandlung

Wenn Ihre externe Abfrage einen Datentyp enthält, der in BigQuery nicht unterstützt wird, schlägt die Abfrage sofort fehl. Sie können den nicht unterstützten Datentyp in einen anderen unterstützten MySQL/PostgreSQL-Datentyp umwandeln. Weitere Informationen zum Umwandeln finden Sie unter Nicht unterstützte Datentypen.

Typzuordnung von MySQL zu BigQuery

MySQL-Typ MySQL-Beschreibung BigQuery-Typ Unterschied zwischen Typen
Ganzzahl
INT 4 Byte, 2^32 - 1 INT64
TINYINT 1 Byte, 2^8 - 1 INT64
SMALLINT 2 Byte, 2^16 - 1 INT64
MEDIUMINT 3 Byte, 2^24 - 1 INT64
BIGINT 8 Byte, 2^64 - 1 INT64
UNSIGNED BIGINT 8 Byte, 2^64 - 1 NUMERIC
Genaue numerische Werte
DECIMAL (M,D) Eine Dezimalzahl wird durch (M,D) dargestellt, wobei M die Gesamtzahl der Stellen und D die Anzahl der Dezimalstellen ist. M <= 65 NUMERIC, BIGNUMERIC, FLOAT64 oder STRING

 DECIMAL (M,D) wird standardmäßig NUMERIC zugeordnet oder kann BIGNUMERIC, FLOAT64 oder STRING mit default_type_for_decimal_columns zugeordnet werden.
Ungefähre numerische Werte
FLOAT (M,D) 4 Byte, M <= 23 FLOAT64
DOUBLE (M,D) 8 Byte, M <= 53 FLOAT64
Datum und Uhrzeit
TIMESTAMP „1970-01-01 00:00:01“ UTC bis „2038-01-19 03:14:07“ UTC TIMESTAMP MySQL TIMESTAMP wird als UTC-Zeitzone abgerufen, unabhängig davon, wo der Nutzer BigQuery aufruft.
DATETIME "1000-01-01 00:00:00" bis "9999-12-31 23:59:59" DATETIME
DATE „1000-01-01“ bis „9999-12-31“ DATE
TIME Uhrzeit im Format „HH:MM:SS“
„-838:59:59“ bis „838:59:59“		 TIME
 Der TIME-Bereich in BigQuery ist kleiner, von 00:00:00 bis 23:59:59
YEAR INT64
Zeichen und Strings
ENUM Stringobjekt mit einem Wert aus einer Liste zulässiger Werte STRING
CHAR (M) Ein String mit fester Länge zwischen 1 und 255 Zeichen STRING
VARCHAR (M) Ein String mit variabler Länge zwischen 1 und 255 Zeichen STRING
TEXT Ein Feld mit einer maximalen Länge von 65.535 Zeichen STRING
TINYTEXT TEXT-Spalte mit einer maximalen Länge von 255 Zeichen STRING
MEDIUMTEXT TEXT-Spalte mit einer maximalen Länge von 16.777.215 Zeichen STRING
LONGTEXT TEXT-Spalte mit einer maximalen Länge von 4.294.967.295 Zeichen STRING
Binär
BLOB Ein Binary Large Object mit einer maximalen Länge von 65.535 Zeichen BYTES
MEDIUM_BLOB Ein BLOB mit einer maximalen Länge von 16.777.215 Zeichen BYTES
LONG_BLOB Ein BLOB mit einer maximalen Länge von 4.294.967.295 Zeichen BYTES
TINY_BLOB Ein BLOB mit einer maximalen Länge von 255 Zeichen BYTES
BINARY Ein String mit fester Länge zwischen 1 und 255 Zeichen BYTES
VARBINARY Ein String mit variabler Länge zwischen 1 und 255 Zeichen BYTES
Andere
SET Geben Sie beim Deklarieren der SET-Spalte einige Werte vor. Fügen Sie dann einen beliebigen Satz vordefinierter Werte in diese Spalte ein. STRING
GEOMETRY GEOGRAPHY NOCH NICHT UNTERSTÜTZT
BIT INT64 NOCH NICHT UNTERSTÜTZT

Typzuordnung von PostgreSQL zu BigQuery

Name Beschreibung BigQuery-Typ Unterschied zwischen Typen
Ganzzahl
smallint 2 Byte, -32.768 bis +32.767 INT64
smallserial Siehe smallint INT64
integer 4 Byte, -2.147.483.648 bis +2.147.483.647 INT64
serial Siehe integer INT64
bigint 8 Byte, -9.223.372.036.854.775.808 bis 9.223.372.036.854.775.807 INT64
bigserial Siehe bigint INT64
Genaue numerische Werte
numeric [ (p, s) ] Bis zu 1.000 Stellen NUMERIC, BIGNUMERIC, FLOAT64 oder STRING Numerisch [ (p, s) ] wird standardmäßig NUMERIC zugeordnet oder kann BIGNUMERIC, FLOAT64 oder STRING mit default_type_for_decimal_columns zugeordnet werden.
Decimal [ (p, s) ] Siehe numeric NUMERIC Siehe numeric
money 8 Byte, zwei Dezimalstellen, -92.233.720.368.547.758,08 bis +92.233.720.368.547.758,07 NICHT UNTERSTÜTZT
Ungefähre numerische Werte
real 4 Byte, Gleitkommazahl mit einfacher Genauigkeit FLOAT64
double precision 8 Byte, Gleitkommazahl mit doppelter Genauigkeit FLOAT64
Datum und Uhrzeit
date Kalenderdatum (Jahr, Monat, Tag) DATE
time [ (p) ] [ ohne Zeitzone ] Uhrzeit (ohne Zeitzone) TIME
time [ (p) ] mit Zeitzone Uhrzeit, mit Zeitzone NICHT UNTERSTÜTZT
timestamp [ (p) ] [ ohne Zeitzone ] Datum und Uhrzeit (ohne Zeitzone) DATETIME
timestamp [ (p) ] mit Zeitzone Datum und Uhrzeit, mit Zeitzone TIMESTAMP PostgreSQL TIMESTAMP wird als UTC-Zeitzone abgerufen, unabhängig davon, von wo aus der Nutzer BigQuery aufruft.
interval Eine Zeitdauer NICHT UNTERSTÜTZT
Zeichen und Strings
character [ (n) ] Zeichenstring mit fester Länge STRING
character varying [ (n) ] Zeichenstring mit variabler Länge STRING
text Zeichenstring mit variabler Länge STRING
Binär
bytea Binärdaten ("Bytearray") BYTES
bit [ (n) ] Bitfolge mit fester Länge BYTES
bit varying [ (n) ] Bitfolge mit variabler Länge BYTES
Andere
boolean logischer boolescher Wert (true/false) BOOL
inet IPv4- oder IPv6-Hostadresse NICHT UNTERSTÜTZT
path geometrischer Pfad auf einer Ebene NICHT UNTERSTÜTZT
pg_lsn PostgreSQL-Logsequenznummer NICHT UNTERSTÜTZT
point geometrischer Punkt auf einer Ebene NICHT UNTERSTÜTZT
polygon geschlossener geometrischer Pfad auf einer Ebene NICHT UNTERSTÜTZT
tsquery Textsuchabfrage NICHT UNTERSTÜTZT
tsvector Textsuchdokument NICHT UNTERSTÜTZT
txid_snapshot Snapshot der Transaktions-ID auf Nutzerebene NICHT UNTERSTÜTZT
uuid universell eindeutige Kennzeichnung NICHT UNTERSTÜTZT
xml XML-Daten STRING
box rechteckiges Feld auf einer Ebene NICHT UNTERSTÜTZT
cidr IPv4- oder IPv6-Netzwerkadresse NICHT UNTERSTÜTZT
circle Kreis auf einer Ebene NICHT UNTERSTÜTZT
interval [ Felder ] [ (p) ] Zeitspanne NICHT UNTERSTÜTZT
json JSON-Textdaten STRING
jsonb JSON-Binärdaten, zerlegt NICHT UNTERSTÜTZT
line unendliche Linie auf einer Ebene NICHT UNTERSTÜTZT
lseg Liniensegment auf einer Ebene NICHT UNTERSTÜTZT
macaddr MAC-Adresse (Media Access Control) NICHT UNTERSTÜTZT
macaddr8 MAC-Adresse (Media Access Control) (EUI-64-Format) NICHT UNTERSTÜTZT

Nicht unterstützte MySQL- und PostgreSQL-Datentypen

Wenn Ihre externe Abfrage einen Datentyp enthält, der in BigQuery nicht unterstützt wird, schlägt die Abfrage sofort fehl. Sie können den nicht unterstützten Datentyp in einen anderen unterstützten MySQL/PostgreSQL-Datentyp umwandeln.

  • Nicht unterstützter MySQL-Datentyp
    • Fehlermeldung: Invalid table-valued function external_query Found unsupported MySQL type in BigQuery. at [1:15]
    • Nicht unterstützter Typ: GEOMETRY, BIT
    • Lösung: Wandeln Sie den nicht unterstützten Datentyp in STRING um.
    • Beispiel: SELECT ST_AsText(ST_GeomFromText('POINT(1 1)'));. Dieser Befehl wandelt den nicht unterstützten Datentyp GEOMETRY in STRING um.
  • Nicht unterstützter PostgreSQL-Datentyp
    • Fehlermeldung: Invalid table-valued function external_query Postgres type (OID = 790) is not supported now at [1:15]
    • Nicht unterstützter Typ: money, time with time zone, inet, path, pg_lsn, point, polygon, tsquery, tsvector, txid_snapshot, uuid, box, cidr, circle, interval, jsonb, line, lseg, macaddr, macaddr8
    • Lösung: Wandeln Sie den nicht unterstützten Datentyp in STRING um.
    • Beispiel: SELECT CAST('12.34'::float8::numeric::money AS varchar(30));. Dieser Befehl wandelt den nicht unterstützten Datentyp money in string um.

Typzuordnung von Spanner zu BigQuery

Wenn Sie eine föderierte Spanner-Abfrage ausführen, werden die Daten aus Spanner in GoogleSQL-Typen umgewandelt.

Spanner-GoogleSQL-Typ Spanner-PostgreSQL-Typ BigQuery-Typ
ARRAY - ARRAY
BOOL bool BOOL
BYTES bytea BYTES
DATE date DATE
FLOAT64 float8 FLOAT64
INT64 bigint INT64
JSON JSONB JSON
NUMERIC numeric* NUMERIC
STRING varchar STRING
STRUCT - Nicht unterstützt für föderierte Spanner-Abfragen
TIMESTAMP timestamptz TIMESTAMP mit abgeschnittenen Nanosekunden

* PostgreSQL-numerische Werte mit einer Genauigkeit, die größer als die von BigQuery unterstützte Genauigkeit ist, werden gerundet. Werte, die größer als der Höchstwert sind, generieren einen Invalid NUMERIC value-Fehler.

Wenn Ihre externe Abfrage einen Datentyp enthält, der für föderierte Abfragen nicht unterstützt wird, schlägt die Abfrage sofort fehl. Sie können den nicht unterstützten Datentyp in einen unterstützten Datentyp umwandeln.