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:

  • Übertragen Sie Daten mithilfe der Anweisung LOAD DATA aus Dateien in Amazon S3 und Azure Blob Storage in BigQuery-Tabellen.

  • Filtern Sie Daten aus Dateien in Amazon S3- oder Blob-Speicher, bevor Sie die Ergebnisse in BigQuery-Tabellen übertragen. Verwenden Sie dazu die Anweisung CREATE TABLE AS SELECT. Verwenden Sie die Anweisung INSERT INTO SELECT, um Daten an die Zieltabelle anzuhängen.

    Die Datenbearbeitung wird auf die externen Tabellen angewendet, die auf Daten aus Amazon S3 oder Blob Storage verweisen.

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 im Abschnitt „Omni Cross Cloud Data Transfer“ unter BigQuery Omni – Preise.

Die mit Clouds übertragenen Byte werden Ihnen mit der Anweisung CREATE TABLE AS SELECT oder der Anweisung INSERT INTO SELECT und für 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. Weitere Informationen finden Sie unter BigQuery Omni-Preise.

Hinweise

Um Google Cloud Lesezugriff auf die Dateien in anderen Clouds zu gewähren, bitten Sie Ihren Administrator, eine Verbindung herzustellen und diese 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.
  • LOAD DATA wird nur unterstützt, wenn Sie Daten von einem Amazon Simple Storage Service (Amazon S3) oder Azure Blob Storage in eine gemeinsame BigQuery-Region übertragen. Weitere Informationen finden Sie unter Standorte.
    • Sie können Daten aus jeder US-Region in eine US-Multi-Region übertragen. Sie können auch von jeder EU-Region in eine EU-Multi-Region übertragen.

Beispiel

Beispiel 1

Im folgenden Beispiel wird eine Parquet-Datei mit dem Namen sample.parquet aus einem Amazon S3-Bucket mit einem Schema zur automatischen Erkennung 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 Storage 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

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

Beschränkungen

  • Wenn das Ergebnis der Abfrage SELECT 60 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 Daten über Regionen hinweg lesen kann.

  • CREATE TABLE AS SELECT und INSERT INTO SELECT werden nur unterstützt, wenn Sie Daten von einem Amazon S3- oder Blob Storage-Speicher in eine gemeinsame BigQuery-Region übertragen. Weitere Informationen finden Sie unter Standorte.

    • Sie können Daten aus jeder US-Region in eine US-Multi-Region übertragen. Sie können auch von jeder EU-Region in eine EU-Multi-Region übertragen.

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:

    bq show myawsdataset.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:

  bq show myotherdataset.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 die maximale Abfrageergebnis finden Sie unter Maximale Größe von Abfrageergebnissen in 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