Daten mit cloudübergreifenden Vorgängen laden

Als BigQuery-Administrator oder -Analyst können Sie Daten aus einem Amazon Simple Storage Service-Bucket (Amazon S3) oder Azure Blob Storage in BigQuery-Tabellen laden. Sie können die übertragenen Daten entweder mit den Daten in Google Cloud-Regionen zusammenführen oder BigQuery-Features wie BigQuery ML nutzen.

So können Sie Daten in BigQuery übertragen:

Kontingente und Limits

Informationen zu Kontingenten und Limits finden Sie unter Abfragejobs: Kontingente und Limits.

Preise

Die mit Clouds übertragenen Byte werden Ihnen mithilfe der Anweisung LOAD in Rechnung gestellt. Preisinformationen finden Sie unter Preise für die Datenextraktion.

Mit der Anweisung CREATE TABLE AS SELECT (in Vorschau) oder der Anweisung INSERT INTO SELECT (in Vorschau) werden Ihnen keine Byte in Rechnung gestellt. Ihnen wird jedoch die Rechenkapazität in Rechnung gestellt.

Sowohl LOAD- als auch CREATE TABLE AS SELECT-Anweisungen erfordern Slots in den BigQuery Omni-Regionen, um Amazon S3- und Blob Storage-Dateien zu laden.

Hinweis

Wenn Sie Google Cloud Lesezugriff auf die Dateien in anderen Clouds gewähren möchten, bitten Sie Ihren Administrator, eine Verbindung zu erstellen und für Sie freizugeben. Informationen zum Erstellen von Verbindungen finden Sie unter Verbindung zu Amazon S3 herstellen oder Verbindung zu Blob-Speicher herstellen.

Erforderliche Rolle

Bitten Sie Ihren Administrator, Ihnen die IAM-Rolle BigQuery Data Editor (roles/bigquery.dataEditor) für das Dataset zuzuweisen, um die Berechtigungen zu erhalten, die Sie zum Laden von Daten mit cloudübergreifenden Übertragungen benötigen. Weitere Informationen zum Zuweisen von Rollen finden Sie unter Zugriff verwalten.

Diese vordefinierte Rolle enthält die Berechtigungen, die zum Laden von Daten mit cloudübergreifenden Übertragungen erforderlich sind. Erweitern Sie den Abschnitt Erforderliche Berechtigungen, um die erforderlichen Berechtigungen anzuzeigen:

Erforderliche Berechtigungen

Die folgenden Berechtigungen sind erforderlich, um Daten mit cloudübergreifenden Übertragungen zu laden:

  • bigquery.tables.create
  • bigquery.tables.get
  • bigquery.tables.updateData
  • bigquery.tables.update
  • bigquery.jobs.create
  • bigquery.connections.use

Sie können diese Berechtigungen auch mit benutzerdefinierten Rollen oder anderen vordefinierten Rollen erhalten.

Weitere Informationen zu IAM-Rollen in BigQuery finden Sie unter Vordefinierte Rollen und Berechtigungen.

Daten laden

Mit der Anweisung LOAD DATA [INTO|OVERWRITE] können Sie Daten in BigQuery laden.

Beschränkungen

  • Das Laden von Daten in eine Zieltabelle mit Aufnahmezeitpartition wird nicht unterstützt.
  • LOAD DATA-Jobs werden nicht auf Reservierungen ausgeführt. Jobs verwenden On-Demand-Slots, die von Google Cloud verwaltet werden.
  • Die Verbindung und das Ziel-Dataset müssen zum selben Projekt gehören. Das projektübergreifende Laden von Daten wird nicht unterstützt.

Beispiel

Beispiel 1

Im folgenden Beispiel wird eine Parquet-Datei mit dem Namen sample.parquet mit einem automatischen Erkennungsschema aus einem Amazon S3-Bucket in die Tabelle test_parquet geladen:

LOAD DATA INTO mydataset.testparquet
  FROM FILES (
    uris = ['s3://test-bucket/sample.parquet'],
    format = 'PARQUET'
  )
  WITH CONNECTION `aws-us-east-1.test-connection`

Beispiel 2

Im folgenden Beispiel wird eine CSV-Datei mit dem Präfix sampled* aus Ihrem Blob-Speicher in die Tabelle test_csv mit vordefinierter Spaltenpartitionierung nach Zeit geladen:

LOAD DATA INTO mydataset.test_csv (Number INT64, Name STRING, Time DATE)
  PARTITION BY Time
  FROM FILES (
    format = 'CSV', uris = ['azure://test.blob.core.windows.net/container/sampled*'],
    skip_leading_rows=1
  )
  WITH CONNECTION `azure-eastus2.test-connection`

Beispiel 3

Im folgenden Beispiel wird die vorhandene Tabelle test_parquet mit Daten aus einer Datei namens sample.parquet mit einem Schema zur automatischen Erkennung überschrieben:

LOAD DATA OVERWRITE mydataset.testparquet
  FROM FILES (
    uris = ['s3://test-bucket/sample.parquet'],
    format = 'PARQUET'
  )
  WITH CONNECTION `aws-us-east-1.test-connection`

Daten filtern

Sie können Daten vor der Übertragung in BigQuery mit der Anweisung CREATE TABLE AS SELECT und der Anweisung INSERT INTO SELECT filtern.

Beschränkungen

  • Die Anweisungen CREATE TABLE und CREATE TABLE IF NOT EXISTS werden nicht unterstützt, aber die Anweisung CREATE OR REPLACE TABLE wird unterstützt.

  • Wenn das Ergebnis der Abfrage SELECT 20 GiB in logischen Byte überschreitet, schlägt die Abfrage fehl. Die Tabelle wird nicht erstellt und es werden keine Daten übertragen. Informationen zum Reduzieren der gescannten Datengröße finden Sie unter In Abfragen verarbeitete Daten reduzieren.

  • Temporäre Tabellen werden nicht unterstützt.

  • Die Übertragung des Geodaten-Formats Well-known Binary (WKB) wird nicht unterstützt.

  • Die INSERT INTO SELECT-Anweisung unterstützt nicht die Übertragung von Daten in eine geclusterte Tabelle.

  • Wenn die Zieltabelle in der INSERT INTO SELECT-Anweisung mit der Quelltabelle in der SELECT-Abfrage übereinstimmt, ändert die INSERT INTO SELECT-Anweisung keine Zeilen in der Zieltabelle. Die Zieltabelle wird nicht geändert, da BigQuery keine regionenübergreifenden Daten lesen kann.

Beispiel

Beispiel 1

Angenommen, Sie haben eine BigLake-Tabelle mit dem Namen myawsdataset.orders, die auf Daten aus Amazon S3 verweist. Sie möchten Daten aus dieser Tabelle in eine BigQuery-Tabelle myotherdataset.shipments am multiregionalen Standort „US“ übertragen.

Rufen Sie zuerst Informationen zur Tabelle myawsdataset.orders auf:

SELECT
  table_name, ddl
FROM
  `myproject`.myawsdataset.INFORMATION_SCHEMA.TABLES
WHERE
  table_name = 'orders';

Die Ausgabe sieht in etwa so aus:

  Last modified             Schema              Type     Total URIs   Expiration
----------------- -------------------------- ---------- ------------ -----------
  31 Oct 17:40:28   |- l_orderkey: integer     EXTERNAL   1
                    |- l_partkey: integer
                    |- l_suppkey: integer
                    |- l_linenumber: integer
                    |- l_returnflag: string
                    |- l_linestatus: string
                    |- l_commitdate: date

Rufen Sie als Nächstes Informationen zur Tabelle myotherdataset.shipments auf:

SELECT
  table_name, ddl
FROM
  `myproject`.myotherdataset.INFORMATION_SCHEMA.TABLES
WHERE
  table_name = 'shipments';

Die entsprechende Ausgabe sieht etwa so aus: Einige Spalten werden weggelassen, um die Ausgabe zu vereinfachen.

  Last modified             Schema             Total Rows   Total Bytes   Expiration   Time Partitioning   Clustered Fields   Total Logical
 ----------------- --------------------------- ------------ ------------- ------------ ------------------- ------------------ ---------------
  31 Oct 17:34:31   |- l_orderkey: integer      3086653      210767042                                                         210767042
                    |- l_partkey: integer
                    |- l_suppkey: integer
                    |- l_commitdate: date
                    |- l_shipdate: date
                    |- l_receiptdate: date
                    |- l_shipinstruct: string
                    |- l_shipmode: string

Mit der Anweisung CREATE TABLE AS SELECT können Sie jetzt Daten selektiv in die Tabelle myotherdataset.orders am multiregionalen Standort "US" laden:

CREATE OR REPLACE TABLE
  myotherdataset.orders
  PARTITION BY DATE_TRUNC(l_commitdate, YEAR) AS
SELECT
  *
FROM
  myawsdataset.orders
WHERE
  EXTRACT(YEAR FROM l_commitdate) = 1992;

Sie können dann einen Join-Vorgang mit der neu erstellten Tabelle ausführen:

SELECT
  orders.l_orderkey,
  orders.l_orderkey,
  orders.l_suppkey,
  orders.l_commitdate,
  orders.l_returnflag,
  shipments.l_shipmode,
  shipments.l_shipinstruct
FROM
  myotherdataset.shipments
JOIN
  `myotherdataset.orders` as orders
ON
  orders.l_orderkey = shipments.l_orderkey
AND orders.l_partkey = shipments.l_partkey
AND orders.l_suppkey = shipments.l_suppkey
WHERE orders.l_returnflag = 'R'; -- 'R' means refunded.

Wenn neue Daten verfügbar sind, hängen Sie die Daten des Jahres 1993 mit der Anweisung INSERT INTO SELECT an die Zieltabelle an:

INSERT INTO
   myotherdataset.orders
 SELECT
   *
 FROM
   myawsdataset.orders
 WHERE
   EXTRACT(YEAR FROM l_commitdate) = 1993;

Beispiel 2

Im folgenden Beispiel werden Daten in eine nach Aufnahmezeit partitionierte Tabelle eingefügt:

CREATE TABLE
 mydataset.orders(id String, numeric_id INT)
PARTITION BY _PARTITIONDATE;

Nachdem Sie eine partitionierte Tabelle erstellt haben, können Sie Daten in die nach Aufnahmezeit partitionierte Tabelle einfügen:

INSERT INTO
 mydataset.orders(
   _PARTITIONTIME,
   id,
   numeric_id)
SELECT
 TIMESTAMP("2023-01-01"),
 id,
 numeric_id,
FROM
 mydataset.ordersof23
WHERE
 numeric_id > 4000000;

Best Practices

  • Vermeiden Sie die Übertragung mehrerer Dateien, die kleiner als 5 MB sind. Erstellen Sie stattdessen eine externe Tabelle für Ihre Datei und exportieren Sie das Abfrageergebnis nach Amazon S3 oder Blob Storage, um eine größere Datei zu erstellen. Diese Methode verbessert die Übertragungszeit Ihrer Daten.
  • Informationen zum Limit für das maximale Abfrageergebnis finden Sie unter Maximales Ergebnisergebnis für BigQuery Omni.
  • Wenn sich die Quelldaten in einer mit GZIP komprimierten Datei befinden, legen Sie beim Erstellen externer Tabellen die Option external_table_options.compression auf GZIP fest.

Nächste Schritte