Automatische Schemaerkennung verwenden

Automatische Schemaerkennung

Die automatische Schemaerkennung ist für das Laden von Daten in BigQuery und für das Abfragen einer externen Datenquelle verfügbar.

Wenn die automatische Erkennung aktiviert ist, startet BigQuery den Ableitungsvorgang. Zu diesem Zweck wird eine zufällige Datei in der Datenquelle ausgewählt und es werden bis zu 100 Datenzeilen als repräsentative Stichprobe durchsucht. BigQuery überprüft dann jedes Feld und versucht basierend auf den Werten in dieser Stichprobe, diesem Feld einen Datentyp zuzuweisen.

So rufen Sie das erkannte Schema für eine Tabelle auf:

  • Verwenden Sie in der Befehlszeile den Befehl bq show.
  • Verwenden Sie die Cloud Console oder die klassische Web-UI, um das Tabellenschema aufzurufen

Wenn BigQuery Schemas ermittelt, könnte es – in seltenen Fällen – einen Feldnamen ändern, um ihn mit der BigQuery-SQL-Syntax kompatibel zu machen.

Informationen zu Datentypkonvertierungen finden Sie in:

Daten mithilfe der automatischen Schemaerkennung laden

So aktivieren Sie die automatische Schemaerkennung beim Laden von Daten:

  • Cloud Console: Klicken Sie im Bereich Schema für Automatisch erkennen auf das Kästchen Schema und Eingabeparameter.
  • Klassische BigQuery-Web-UI: Klicken Sie im Bereich Schema auf das Kästchen Automatisch erkennen.
  • bq: Verwenden Sie den Befehl bq load mit dem Parameter --autodetect.

Wenn diese Option aktiviert ist, versucht BigQuery, das Schema für CSV- und JSON-Dateien automatisch abzuleiten.

Die automatische Schemaerkennung wird nicht für Avro-, Parquet-, ORC-Dateien, Firestore-Exportdateien oder Datastore-Exportdateien verwendet. Wenn Sie diese Dateien in BigQuery laden, wird das Tabellenschema automatisch aus den selbstbeschreibenden Quelldaten abgerufen.

So verwenden Sie die automatische Schemaerkennung beim Laden von JSON- oder CSV-Daten:

Console

  1. Rufen Sie in der Cloud Console die BigQuery-Web-UI auf.
    Zur Cloud Console

  2. Wählen Sie im Bereich Ressourcen des Navigationsbereichs ein Dataset aus.

  3. Klicken Sie auf Tabelle erstellen.

    Tabelle erstellen.

  4. Gehen Sie auf der Seite Tabelle erstellen im Abschnitt Quelle so vor:

    • Geben Sie unter Create table from (Tabelle erstellen aus) Ihren gewünschten Quelltyp an:
    • Suchen Sie im Quellfeld nach der Datei oder dem Cloud Storage-Bucket oder geben Sie den Cloud Storage-URI ein. In der BigQuery-Web-UI kann zwar nur ein URI eingefügt werden, Platzhalter werden aber unterstützt. Der Cloud Storage-Bucket muss sich am selben Standort wie das Dataset befinden, das die von Ihnen erstellte Tabelle enthält.

      Screenshot: Datei auswählen

    • Wählen Sie als Dateiformat CSV oder JSON aus.

  5. Gehen Sie auf der Seite Create table (Tabelle erstellen) im Abschnitt Destination (Ziel) so vor:

    • Wählen Sie für Dataset-Name das entsprechende Dataset aus.

      Dataset auswählen.

    • Geben Sie im Feld Tabllenname den Namen der Tabelle ein, die Sie in BigQuery erstellen.

    • Achten Sie darauf, dass Table type (Tabellentyp) auf Native table (Native Tabelle) eingestellt ist.

  6. Klicken Sie auf Tabelle erstellen.

Klassische UI

  1. Rufen Sie die BigQuery-Web-UI auf.
    Zur BigQuery-Web-UI

  2. Klicken Sie in der Navigation neben dem Namen des Datasets auf den Abwärtspfeil Abwärtspfeilsymbol.

  3. Klicken Sie auf Neue Tabelle erstellen.

    Hinweis: In der UI ist der Vorgang zum Laden von Daten mit dem Vorgang zum Erstellen einer Tabelle identisch.
  4. Gehen Sie auf der Seite Create table (Tabelle erstellen) so vor:

    • Klicken Sie für Source Data (Quelldaten) auf Create from source (Aus Quelle erstellen).
    • Wählen Sie unter Destination Table (Zieltabelle) das Dataset aus und geben Sie in das Feld Destination table name (Name der Zieltabelle) den Namen der Tabelle ein.
    • Klicken Sie für Schema auf Automatisch erkennen, um das Schema zu bestimmen.

      Screenshot: Option für automatische Schemaerkennung

    • Klicken Sie auf Tabelle erstellen.

bq

Führen Sie den Befehl bq load mit dem Parameter --autodetect aus.

Optional: Geben Sie das Flag --location an und legen Sie als Wert Ihren Standort fest.

Mit dem folgenden Befehl wird eine Datei mit automatischer Schemaerkennung geladen:

bq --location=LOCATION load \
--autodetect \
--source_format=FORMAT \
DATASET.TABLE \
PATH_TO_SOURCE

Ersetzen Sie dabei Folgendes:

  • LOCATION: Name Ihres Standorts. Das Flag --location ist optional. Wenn Sie beispielsweise BigQuery in der Region Tokio verwenden, geben Sie für das Flag den Wert asia-northeast1 an. Mit der Datei ".bigqueryrc" können Sie für den Standort einen Standardwert festlegen.
  • FORMAT: entweder NEWLINE_DELIMITED_JSON oder CSV.
  • DATASET: das Dataset mit der Tabelle, in die Sie Daten laden.
  • TABLE: der Name der Tabelle, in die Sie Daten laden.
  • path_to_source ist der Speicherort der CSV- oder JSON-Datei.

Beispiele:

Geben Sie den folgenden Befehl ein, um myfile.csv von Ihrem lokalen Computer in eine Tabelle namens mytable zu laden, die in einem Dataset namens mydataset gespeichert ist.

bq load --autodetect --source_format=CSV mydataset.mytable ./myfile.csv

Geben Sie den folgenden Befehl ein, um myfile.json von Ihrem lokalen Computer in eine Tabelle namens mytable zu laden, die in einem Dataset namens mydataset gespeichert ist.

bq load --autodetect --source_format=NEWLINE_DELIMITED_JSON \
mydataset.mytable ./myfile.json

API

  1. Erstellen Sie einen load-Job, der auf die Quelldaten verweist. Weitere Informationen zum Erstellen von Jobs finden Sie unter BigQuery-Jobs programmatisch ausführen. Geben Sie Ihren Standort im Abschnitt jobReference im Attribut location an.

  2. Geben Sie das Datenformat an. Legen Sie dazu das Attribut sourceFormat fest. Wenn Sie die automatische Schemaerkennung verwenden möchten, muss dieser Wert auf NEWLINE_DELIMITED_JSON oder CSV gesetzt werden.

  3. Verwenden Sie das Attribut autodetect, um die automatische Schemaerkennung auf true zu setzen.

Go

Bevor Sie dieses Beispiel ausprobieren, folgen Sie den Schritten zur Einrichtung von Go in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Informationen finden Sie in der Referenzdokumentation zur BigQuery Go API.

import (
	"context"
	"fmt"

	"cloud.google.com/go/bigquery"
)

// importJSONAutodetectSchema demonstrates loading data from newline-delimited JSON data in Cloud Storage
// and using schema autodetection to identify the available columns.
func importJSONAutodetectSchema(projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// tableID := "mytable"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	gcsRef := bigquery.NewGCSReference("gs://cloud-samples-data/bigquery/us-states/us-states.json")
	gcsRef.SourceFormat = bigquery.JSON
	gcsRef.AutoDetect = true
	loader := client.Dataset(datasetID).Table(tableID).LoaderFrom(gcsRef)
	loader.WriteDisposition = bigquery.WriteEmpty

	job, err := loader.Run(ctx)
	if err != nil {
		return err
	}
	status, err := job.Wait(ctx)
	if err != nil {
		return err
	}

	if status.Err() != nil {
		return fmt.Errorf("job completed with error: %v", status.Err())
	}
	return nil
}

Node.js

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von Node.js in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Informationen finden Sie in der Referenzdokumentation zur BigQuery Node.js API.

// Import the Google Cloud client libraries
const {BigQuery} = require('@google-cloud/bigquery');
const {Storage} = require('@google-cloud/storage');

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const datasetId = "my_dataset";
// const tableId = "my_table";

/**
 * This sample loads the JSON file at
 * https://storage.googleapis.com/cloud-samples-data/bigquery/us-states/us-states.json
 *
 * TODO(developer): Replace the following lines with the path to your file.
 */
const bucketName = 'cloud-samples-data';
const filename = 'bigquery/us-states/us-states.json';

async function loadJSONFromGCSAutodetect() {
  // Imports a GCS file into a table with autodetected schema.

  // Instantiate clients
  const bigquery = new BigQuery();
  const storage = new Storage();

  // Configure the load job. For full list of options, see:
  // https://cloud.google.com/bigquery/docs/reference/rest/v2/Job#JobConfigurationLoad
  const metadata = {
    sourceFormat: 'NEWLINE_DELIMITED_JSON',
    autodetect: true,
    location: 'US',
  };

  // Load data from a Google Cloud Storage file into the table
  const [job] = await bigquery
    .dataset(datasetId)
    .table(tableId)
    .load(storage.bucket(bucketName).file(filename), metadata);
  // load() waits for the job to finish
  console.log(`Job ${job.id} completed.`);

  // Check the job's status for errors
  const errors = job.status.errors;
  if (errors && errors.length > 0) {
    throw errors;
  }
}
loadJSONFromGCSAutodetect();

PHP

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von PHP in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Informationen finden Sie in der Referenzdokumentation zur BigQuery PHP API.

use Google\Cloud\BigQuery\BigQueryClient;
use Google\Cloud\Core\ExponentialBackoff;

/** Uncomment and populate these variables in your code */
// $projectId  = 'The Google project ID';
// $datasetId  = 'The BigQuery dataset ID';

// instantiate the bigquery table service
$bigQuery = new BigQueryClient([
    'projectId' => $projectId,
]);
$dataset = $bigQuery->dataset($datasetId);
$table = $dataset->table('us_states');

// create the import job
$gcsUri = 'gs://cloud-samples-data/bigquery/us-states/us-states.json';
$loadConfig = $table->loadFromStorage($gcsUri)->autodetect(true)->sourceFormat('NEWLINE_DELIMITED_JSON');
$job = $table->runJob($loadConfig);
// poll the job until it is complete
$backoff = new ExponentialBackoff(10);
$backoff->execute(function () use ($job) {
    print('Waiting for job to complete' . PHP_EOL);
    $job->reload();
    if (!$job->isComplete()) {
        throw new Exception('Job has not yet completed', 500);
    }
});
// check if the job has errors
if (isset($job->info()['status']['errorResult'])) {
    $error = $job->info()['status']['errorResult']['message'];
    printf('Error running job: %s' . PHP_EOL, $error);
} else {
    print('Data imported successfully' . PHP_EOL);
}

Python

Wenn Sie die automatische Schemaerkennung aktivieren möchten, legen Sie das Attribut LoadJobConfig.autodetect auf True fest.

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von Python in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Informationen finden Sie in der Referenzdokumentation zur BigQuery Python API.

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_id = 'my_dataset'

dataset_ref = client.dataset(dataset_id)
job_config = bigquery.LoadJobConfig()
job_config.autodetect = True
job_config.source_format = bigquery.SourceFormat.NEWLINE_DELIMITED_JSON
uri = "gs://cloud-samples-data/bigquery/us-states/us-states.json"
load_job = client.load_table_from_uri(
    uri, dataset_ref.table("us_states"), job_config=job_config
)  # API request
print("Starting job {}".format(load_job.job_id))

load_job.result()  # Waits for table load to complete.
print("Job finished.")

destination_table = client.get_table(dataset_ref.table("us_states"))
print("Loaded {} rows.".format(destination_table.num_rows))

Ruby

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von Ruby in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Informationen finden Sie in der Referenzdokumentation zur BigQuery Ruby API.

require "google/cloud/bigquery"

def load_table_gcs_json_autodetect dataset_id = "your_dataset_id"
  bigquery = Google::Cloud::Bigquery.new
  dataset  = bigquery.dataset dataset_id
  gcs_uri  = "gs://cloud-samples-data/bigquery/us-states/us-states.json"
  table_id = "us_states"

  load_job = dataset.load_job table_id,
                              gcs_uri,
                              format:     "json",
                              autodetect: true
  puts "Starting job #{load_job.job_id}"

  load_job.wait_until_done! # Waits for table load to complete.
  puts "Job finished."

  table = dataset.table table_id
  puts "Loaded #{table.rows_count} rows to table #{table.id}"
end

Automatische Schemaerkennung für externe Datenquellen

Wenn Sie eine Tabelle erstellen, die mit einer externen Datenquelle verknüpft ist, aktivieren Sie die automatische Schemaerkennung:

  • Klicken Sie in der Cloud Console für Automatische Erkennung auf das Kästchen Schema und Eingabeparameter.
  • Klicken Sie in der klassischen BigQuery-Web-UI auf das Kästchen Automatisch erkennen.

Wenn diese Option aktiviert ist, versucht BigQuery, das Schema für CSV- und JSON-Dateien automatisch abzuleiten.

Derzeit können Sie die automatische Schemaerkennung für externe Google Tabellen-Datenquellen nicht mithilfe der Cloud Console oder der klassischen Web-UI aktivieren. Die automatische Schemaerkennung wird außerdem nicht für externe Avro-, Firestore- oder Datastore-Exportdateien verwendet. Wenn Sie eine Tabelle erstellen, die mit einem dieser Dateitypen verknüpft ist, ruft BigQuery das Schema automatisch aus den selbstbeschreibenden Quelldaten ab.

Über die Befehlszeile können Sie die automatische Schemaerkennung aktivieren, wenn Sie eine Tabellendefinitionsdatei für CSV-, JSON- oder Google Tabellen-Daten erstellen. Wenn Sie mit der Befehlszeile eine Tabellendefinitionsdatei erstellen, übergeben Sie das Flag --autodetect an den Befehl mkdef, um die automatische Schemaerkennung zu aktivieren, oder das Flag --noautodetect, um die automatische Erkennung zu deaktivieren.

Wenn Sie das Flag --autodetect verwenden, ist die Einstellung autodetect in der Tabellendefinitionsdatei auf true gesetzt. Wenn Sie das Flag --noautodetect verwenden, ist die Einstellung autodetect auf false gesetzt. Wenn Sie beim Erstellen einer Tabellendefinition keine Schemadefinition für die externe Datenquelle angeben und das Flag --noautodetect oder --autodetect nicht verwenden, wird für autodetect standardmäßig true verwendet.

Wenn Sie eine Tabellendefinitionsdatei mithilfe der API erstellen, setzen Sie den Wert des Attributs autodetect auf true oder false. Wenn Sie autodetect auf true setzen, wird die automatische Erkennung aktiviert. Wenn Sie autodetect auf false setzen, wird sie deaktiviert.

Details der automatischen Erkennung

Neben der Erkennung von Schemadetails wird Folgendes automatisch erkannt:

Komprimierung

BigQuery erkennt die GZIP-kompatible Dateikomprimierung beim Öffnen einer Datei.

CSV-Trennzeichen

BigQuery erkennt die folgenden Trennzeichen:

  • Komma ( , )
  • Senkrechter Strich ( | )
  • Tabulator ( \t )

CSV-Header

BigQuery leitet Header ab, indem es die erste Zeile einer Datei mit anderen Zeilen im Dataset vergleicht. Wenn die erste Zeile nur Strings enthält und die anderen Zeilen andere Datentypen enthalten, geht BigQuery davon aus, dass es sich bei der ersten Zeile um eine Headerzeile handelt. In diesem Fall weist BigQuery Spaltennamen anhand der Feldnamen in der Headerzeile zu. Die Namen können auch geändert werden, um die Namenskonventionen für Spalten in BigQuery zu erfüllen. Leerzeichen werden beispielsweise durch Unterstriche ersetzt.

Andernfalls geht BigQuery davon aus, dass die erste Zeile eine Datenzeile ist, und weist allgemeine Spaltennamen wie string_field_1 zu. Beachten Sie, dass die Spaltennamen nach dem Erstellen einer Tabelle im Schema nicht mehr aktualisiert werden können. Sie können die Namen jedoch manuell ändern, nachdem die Tabelle erstellt wurde. Eine weitere Option ist die Bereitstellung eines expliziten Schemas, anstelle der Verwendung der automatischen Erkennung.

Möglicherweise haben Sie eine CSV-Datei mit einem Header, in dem alle Datenfelder Strings sind. BigQuery erkennt in diesem Fall nicht automatisch, dass die erste Zeile ein Header ist. Verwenden Sie die Option -skip_leading_rows, um den Header zu überspringen. Andernfalls wird der Header als Daten importiert. Sie sollten in diesem Fall auch ein explizites Schema angeben, damit Sie Spaltennamen zuweisen können.

CSV-Zeilenvorschübe in Anführungszeichen

BigQuery erkennt Zeilenvorschubzeichen in Anführungszeichen in einer CSV-Datei und interpretiert das Zeilenvorschubzeichen in Anführungszeichen nicht als Zeilengrenze.

Datum- und Uhrzeitwerte

BigQuery erkennt Datums- und Uhrzeitwerte basierend auf der Formatierung der Quelldaten.

Werte in DATE-Spalten müssen das folgende Format haben: YYYY-MM-DD.

Die Werte in den TIME-Spalten müssen im folgenden Format angegeben werden: HH:MM:SS[.SSSSSS] (die Komponente des Sekundenbruchteils ist optional).

Bei TIMESTAMP-Spalten erkennt BigQuery eine Vielzahl von Zeitstempelformaten, einschließlich, aber nicht beschränkt auf:

  • YYYY-MM-DD HH:MM
  • YYYY-MM-DD HH:MM:SS
  • YYYY-MM-DD HH:MM:SS.SSSSSS
  • YYYY/MM/DD HH:MM

Ein Zeitstempel kann auch einen UTC-Versatz oder den UTC-Zonenbezeichner ("Z") enthalten.

Im Folgenden finden Sie einige Beispiele für Werte, die BigQuery automatisch als Zeitstempelwerte erkennt:

  • 2018-08-19 12:11
  • 2018-08-19 12:11:35.22
  • 2018/08/19 12:11
  • 2018-08-19 07:11:35.220 -05:00

Wenn BigQuery das Format nicht erkennt, wird die Spalte als String-Datentyp geladen. In diesem Fall müssen Sie die Quelldaten möglicherweise vor dem Laden vorverarbeiten. Wenn Sie beispielsweise CSV-Daten aus einer Tabelle exportieren, legen Sie das Datumsformat auf eines der hier gezeigten Beispiele fest. Alternativ können Sie die Daten nach dem Laden in BigQuery umwandeln.