Tabellenschemas manuell ändern

In diesem Dokument wird beschrieben, wie Sie die Schemadefinition für vorhandene BigQuery-Tabellen manuell ändern. Viele Schemaänderungen werden in BigQuery nicht nativ unterstützt und erfordern manuelle Problemumgehungen. Folgende Schemaänderungen werden nicht unterstützt:

• Name einer Spalte ändern
• Datentyp einer Spalte ändern
• Modus einer Spalte ändern (ausgenommen das Lockern von REQUIRED auf NULLABLE)
• Spalte löschen

Weitere Informationen zu unterstützten Schemaänderungen in BigQuery finden Sie unter Tabellenschemas ändern.

Achtung: Die auf dieser Seite beschriebenen Optionen umfassen das Überschreiben der Tabelle oder das Laden der Daten in eine neue Tabelle. Bei nach Aufnahmezeit partitionierten Tabellen löschen diese Vorgänge die ursprünglichen Partitionierungsinformationen. Nach dem Vorgang befinden sich alle Tabellendaten in einer einzigen Partition.

Name einer Spalte ändern

Das Umbenennen einer Spalte wird von der Console, dem bq-Befehlszeilentool oder der API nicht unterstützt. Wenn Sie ein Tabellenschema mit einer umbenannten Spalte aktualisieren, wird der folgende Fehler zurückgegeben: BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table.

Es gibt zwei Möglichkeiten, eine Spalte manuell umzubenennen:

• Über eine SQL-Abfrage: Wählen Sie diese Option, wenn eine unkomplizierte und nutzerfreundliche Lösung für Sie wichtiger ist als die Kosten.
• Durch Neuerstellung der Tabelle: Wählen Sie diese Option, wenn die Kosten für Sie wichtiger sind als eine unkomplizierte und nutzerfreundliche Lösung.

Option 1: Abfragen verwenden

Wählen Sie alle Spalten in der Tabelle aus und geben Sie der Spalte, die Sie umbenennen möchten, einen Alias, um den Namen einer Spalte mithilfe einer SQL-Abfrage zu ändern. Sie können die vorhandene Tabelle mit dem Abfrageergebnis überschreiben oder eine neue Zieltabelle daraus erstellen. Wenn Sie die Spalte mit einem neuen Namen versehen, muss dieser den BigQuery-Regeln für Spaltennamen entsprechen.

Vorteile

• Wenn Sie eine Abfrage verwenden, um die Daten in eine neue Zieltabelle zu schreiben, werden die ursprünglichen Daten beibehalten.
• Wenn Sie die ursprüngliche Tabelle über den Abfragejob überschreiben, fallen anstatt für zwei Tabellen nur für eine Speicherkosten an, die ursprünglichen Daten gehen jedoch verloren.

Nachteile

• Beim Umbenennen einer Spalte über eine Abfrage muss die gesamte Tabelle gescannt werden. Die Abfragegebühren können beträchtlich sein, wenn die Tabelle sehr groß ist.
• Wenn Sie die Abfrageergebnisse in eine neue Zieltabelle schreiben, fallen für die alte und die neue Tabelle Speicherkosten an (wenn Sie die alte Tabelle nicht löschen).
• Umbenannte Spalten, die erforderlich waren, können Nullwerte enthalten.

Beispiel für einen Spaltenalias

Im folgenden Beispiel sehen Sie eine Standard-SQL-Abfrage, mit der alle Daten in mytable ausgewählt werden, mit Ausnahme von zwei Spalten, die umbenannt werden müssen. Es wird ein Alias verwendet, um neue Namen für die zwei Spalten zu generieren. column_one wird in newcolumn_one umbenannt und column_two wird in newcolumn_two umbenannt. Die vorhandene Tabelle wird mit dem Abfrageergebnis überschrieben.

Console

1. Wählen Sie in der Console den Abfrageeditor.

2. Geben Sie im Abfrageeditor die folgende Abfrage ein, um alle Daten in mydataset.mytable auszuwählen, mit Ausnahme der beiden Spalten, die umbenannt werden müssen. mydataset.mytable befindet sich in Ihrem Standardprojekt. In der Abfrage wird ein Alias verwendet, um den Namen column_one in newcolumn_one und den Namen column_two in newcolumn_two zu ändern.

   SELECT
    * EXCEPT(column_one, column_two),
    column_one AS newcolumn_one, column_two AS newcolumn_two
   FROM
    mydataset.mytable
   

3. Klicken Sie auf More (Mehr) und wählen Sie Query settings (Abfrageeinstellungen) aus.

4. Gehen Sie im Abschnitt Destination (Ziel) so vor:

   1. Wählen Sie Zieltabelle für Abfrageergebnisse festlegen.

   2. Wählen Sie für Dataset die Option myproject.mydataset aus.

   3. Geben Sie unter Tabellen-ID mytable ein.

   4. Wählen Sie für Schreibeinstellung für Zieltabelle die Option Tabelle überschreiben. Bei dieser Einstellung wird mytable mit den Abfrageergebnissen überschrieben.

5. Klicken Sie auf Speichern, um die Einstellungen zu aktualisieren, und klicken Sie dann im Abfrageeditor auf Ausführen. Wenn der Abfragejob abgeschlossen ist, haben die Spalten in mytable neue Namen.

bq

Geben Sie den folgenden bq query-Befehl ein, um alle Daten in mydataset.mytable auszuwählen, mit Ausnahme der zwei Spalten, die umbenannt werden müssen. mydataset.mytable befindet sich in Ihrem Standardprojekt. In der Abfrage wird ein Alias verwendet, um den Namen column_one in newcolumn_one und den Namen column_two in newcolumn_two zu ändern.

Schreiben Sie die Abfrageergebnisse mit dem Flag --destination_table in mydataset.mytable und geben Sie das Flag --replace an, um mytable zu überschreiben. Mit dem Flag use_legacy_sql=false legen Sie die Standard-SQL-Abfragesyntax fest.

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

bq query \
--destination_table mydataset.mytable \
--replace \
--use_legacy_sql=false \
'SELECT
  * EXCEPT(column_one,
    column_two),
  column_one AS newcolumn_one,
  column_two AS newcolumn_two
FROM
  mydataset.mytable'

API

Rufen Sie die Methode jobs.insert auf und konfigurieren Sie einen query-Job, um den Namen von column_one in newcolumn_one und den Namen von column_two in newcolumn_two zu ändern. Optional: Geben Sie im Abschnitt jobReference im Attribut location Ihren Standort an.

Die im Abfragejob verwendete SQL-Abfrage würde so lauten: SELECT * EXCEPT(column_one, column_two), column_one AS newcolumn_one, column_two AS newcolumn_two FROM mydataset.mytable. Von dieser Abfrage werden alle Daten in mytable mit Ausnahme der zwei Spalten ausgewählt, die umbenannt werden müssen. Es wird ein Alias verwendet, um neue Namen für die zwei Spalten zu generieren.

Fügen Sie mydataset.mytable im Attribut configuration.query.destinationTable hinzu und geben Sie WRITE_TRUNCATE im Attribut configuration.query.writeDisposition an, um mytable mit den Abfrageergebnissen zu überschreiben. Geben Sie im Attribut configuration.query.destinationTable den Tabellennamen ein, um eine neue Zieltabelle anzugeben.

Option 2: Daten in eine neue Tabelle exportieren und laden

Sie haben noch eine weitere Möglichkeit, um Spalten umzubenennen. Exportieren Sie dazu die Tabellendaten nach Cloud Storage und laden Sie die Daten in eine neue Tabelle mit einer Schemadefinition, die den richtigen Spaltennamen enthält. Außerdem können Sie die vorhandene Tabelle durch den Ladejob überschreiben.

Vorteile

• Für die Export- und Ladejobs entstehen Ihnen keine Gebühren. Derzeit sind BigQuery-Lade- und Exportjobs kostenlos.
• Wenn Sie die ursprüngliche Tabelle über den Ladejob überschreiben, fallen anstatt für zwei Tabellen nur für eine Speicherkosten an, die ursprünglichen Daten gehen jedoch verloren.

Nachteile

• Wenn Sie die Daten in eine neue Tabelle laden, fallen für die ursprüngliche und die neue Tabelle Speicherkosten an (wenn Sie die alte Tabelle nicht löschen).
• Es fallen Gebühren für das Speichern der exportierten Daten in Cloud Storage an.

Datentyp einer Spalte ändern

Das Ändern des Datentyps einer Spalte wird von der Console, dem bq-Befehlszeilentool und der API nicht unterstützt. Beim Versuch, eine Tabelle durch Anwenden eines Schemas zu aktualisieren, in dem ein neuer Datentyp für eine Spalte angegeben wird, wird der folgende Fehler zurückgegeben: BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table.

Es gibt zwei Möglichkeiten, den Datentyp einer Spalte manuell zu ändern:

• Über eine SQL-Abfrage: Wählen Sie diese Option, wenn eine unkomplizierte und nutzerfreundliche Lösung für Sie wichtiger ist als die Kosten.
• Durch Neuerstellung der Tabelle: Wählen Sie diese Option, wenn die Kosten für Sie wichtiger sind als eine unkomplizierte und nutzerfreundliche Lösung.

Option 1: Abfragen verwenden

Verwenden Sie eine SQL-Abfrage, wenn Sie alle Tabellendaten auswählen und die entsprechende Spalte in einen anderen Datentyp umwandeln möchten. Mit dem Abfrageergebnis können Sie die Tabelle überschreiben oder eine neue Zieltabelle daraus erstellen.

Vorteile

• Wenn Sie eine Abfrage verwenden, um die Daten in eine neue Zieltabelle zu schreiben, werden die ursprünglichen Daten beibehalten.
• Wenn Sie die ursprüngliche Tabelle über den Abfragejob überschreiben, fallen anstatt für zwei Tabellen nur für eine Speicherkosten an, die ursprünglichen Daten gehen jedoch verloren.

Nachteile

• Beim Ändern des Datentyps einer Spalte über eine Abfrage muss die gesamte Tabelle gescannt werden. Die Abfragegebühren können beträchtlich sein, wenn die Tabelle sehr groß ist.
• Wenn Sie die Abfrageergebnisse in eine neue Zieltabelle schreiben, fallen für die alte und die neue Tabelle Speicherkosten an (wenn Sie die alte Tabelle nicht löschen).

Beispiel für CAST

Im folgenden Beispiel sehen Sie eine Standard-SQL-Abfrage, mit der alle Daten aus column_two und column_three in mydataset.mytable ausgewählt werden und column_one von DATE in STRING umgewandelt wird. Die vorhandene Tabelle wird mit dem Abfrageergebnis überschrieben. In der überschriebenen Tabelle wird column_one als Datentyp STRING gespeichert.

Eine Abfrage mit CAST kann fehlschlagen, wenn BigQuery die Umwandlung nicht ausführen kann. Weitere Informationen zu Umwandlungsregeln in Standard-SQL finden Sie in der Referenzdokumentation zu Funktionen und Operatoren unter Umwandeln.

Console

1. Klicken Sie in der Console auf Neue Abfrage erstellen.

2. Geben Sie im Abfrageeditor die folgende Abfrage ein, um alle Daten aus column_two und column_three in mydataset.mytable auszuwählen und column_one von DATE in STRING umzuwandeln. In der Abfrage wird ein Alias verwendet, um column_one mit demselben Namen umzuwandeln. mydataset.mytable befindet sich in Ihrem Standardprojekt.

   SELECT
    column_two, column_three, CAST(column_one AS STRING) AS column_one
   FROM
    mydataset.mytable
   

3. Klicken Sie auf Mehr und wählen Sie Abfrageeinstellungen aus.

4. Aktivieren Sie im Abschnitt Ziel die Option Zieltabelle für Abfrageergebnisse festlegen.

5. Für die folgenden Felder gilt:

   1. Behalten Sie für Projektname das Standardprojekt als Wert bei. Dies ist das Projekt, das mydataset.mytable enthält.

   2. Wählen Sie mydataset als Dataset-Name aus.

   3. Geben Sie im Feld Tabellenname mytable ein.

   4. Klicken Sie auf OK.

6. Wählen Sie im Abschnitt Schreibeinstellung für Zieltabelle die Option Tabelle überschreiben als Schreibeinstellung aus. Bei dieser Einstellung wird mytable mit den Abfrageergebnissen überschrieben.

7. Optional: Klicken Sie unter Verarbeitungsstandort auf Automatische Auswahl und wählen Sie den Standort Ihrer Daten aus.

8. Klicken Sie auf Speichern, um die Einstellungen zu aktualisieren, und klicken Sie dann im Abfrageeditor auf Ausführen. Wenn der Abfragejob abgeschlossen ist, hat column_one den Datentyp STRING.

bq

Geben Sie den folgenden bq query-Befehl ein, um alle Daten aus column_two und column_three in mydataset.mytable auszuwählen und column_one von DATE in STRING umzuwandeln. In der Abfrage wird ein Alias verwendet, um column_one mit demselben Namen umzuwandeln. mydataset.mytable befindet sich in Ihrem Standardprojekt.

Die Abfrageergebnisse werden mit dem Flag --destination_table in mydataset.mytable geschrieben. Das Flag --replace wird verwendet, um mytable zu überschreiben. Mit dem Flag use_legacy_sql=false legen Sie die Standard-SQL-Abfragesyntax fest.

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

bq query \
--destination_table mydataset.mytable \
--replace \
--use_legacy_sql=false \
'SELECT
  column_two,
  column_three,
  CAST(column_one AS STRING) AS column_one
FROM
  mydataset.mytable'

API

Rufen Sie die Methode jobs.insert auf und konfigurieren Sie einen query-Job, um alle Daten aus column_two und column_three in mydataset.mytable auszuwählen und column_one von DATE in STRING umzuwandeln. Optional: Geben Sie im Abschnitt jobReference im Attribut location Ihren Standort an.

Die im Abfragejob verwendete SQL-Abfrage würde so lauten: SELECT column_two, column_three, CAST(column_one AS STRING) AS column_one FROM mydataset.mytable. In der Abfrage wird ein Alias verwendet, um column_one mit demselben Namen umzuwandeln.

Fügen Sie mydataset.mytable im Attribut configuration.query.destinationTable hinzu und geben Sie WRITE_TRUNCATE im Attribut configuration.query.writeDisposition an, um mytable mit den Abfrageergebnissen zu überschreiben.

Option 2: Daten in eine neue Tabelle exportieren und laden

Sie haben noch eine weitere Möglichkeit, um den Datentyp einer Spalte zu ändern. Exportieren Sie dazu die Tabellendaten in Cloud Storage und laden Sie die Daten dann in eine neue Tabelle, deren Schemadefinition den richtigen Datentyp für die Spalte angibt. Außerdem können Sie die vorhandene Tabelle durch den Ladejob überschreiben.

Vorteile

• Für die Export- und Ladejobs entstehen Ihnen keine Gebühren. Derzeit sind BigQuery-Lade- und Exportjobs kostenlos.
• Wenn Sie die ursprüngliche Tabelle über den Ladejob überschreiben, fallen anstatt für zwei Tabellen nur für eine Speicherkosten an, die ursprünglichen Daten gehen jedoch verloren.

Nachteile

• Wenn Sie die Daten in eine neue Tabelle laden, fallen für die ursprüngliche und die neue Tabelle Speicherkosten an (wenn Sie die alte Tabelle nicht löschen).
• Es fallen Gebühren für das Speichern der exportierten Daten in Cloud Storage an.

Spaltenmodus ändern

Aktuell kann der Spaltenmodus nur von REQUIRED zu NULLABLE geändert werden. Die Änderung des Spaltenmodus von REQUIRED zu NULLABLE wird auch als Spaltenlockerung bezeichnet. Weitere Informationen zum Lockern der Spalten REQUIRED zu NULLABLE finden Sie unter Modus einer Spalte lockern.

Wenn Sie versuchen, eine nicht unterstützte Änderung auf einen Spaltenmodus anzuwenden, wird ein Fehler wie der folgende zurückgegeben. In diesem Beispiel wurde versucht, einen Spaltenmodus von NULLABLE in REPEATED zu ändern: BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table. Field field has changed mode from NULLABLE to REPEATED.

Daten in eine neue Tabelle exportieren und laden

Sie können den Modus einer Spalte manuell ändern. Exportieren Sie dazu die Tabellendaten nach Cloud Storage und laden Sie die Daten dann in eine neue Tabelle mit einer Schemadefinition, die den richtigen Modus für die Spalte angibt. Außerdem können Sie die vorhandene Tabelle durch den Ladejob überschreiben.

Vorteile

• Für die Export- und Ladejobs entstehen Ihnen keine Gebühren. Derzeit sind BigQuery-Lade- und Exportjobs kostenlos.
• Wenn Sie die ursprüngliche Tabelle über den Ladejob überschreiben, fallen anstatt für zwei Tabellen nur für eine Speicherkosten an, die ursprünglichen Daten gehen jedoch verloren.

Nachteile

• Wenn Sie die Daten in eine neue Tabelle laden, fallen für die ursprüngliche und die neue Tabelle Speicherkosten an (wenn Sie die alte Tabelle nicht löschen).
• Es fallen Gebühren für das Speichern der exportierten Daten in Cloud Storage an.

Spalten aus Tabellenschema löschen

Das Löschen einer Spalte aus einem Schema einer vorhandenen Tabelle wird von der Console, dem bq-Befehlszeilentool und der API nicht unterstützt. Wenn Sie versuchen, eine Tabelle zu aktualisieren, indem Sie ein Schema anwenden, mit dem eine Spalte entfernt wird, wird der folgende Fehler zurückgegeben: BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table.

Es gibt zwei Möglichkeiten, eine Spalte manuell zu löschen:

• Über eine SQL-Abfrage: Wählen Sie diese Option, wenn eine unkomplizierte und nutzerfreundliche Lösung für Sie wichtiger ist als die Kosten.
• Durch Neuerstellung der Tabelle: Wählen Sie diese Option, wenn die Kosten für Sie wichtiger sind als eine unkomplizierte und nutzerfreundliche Lösung.

Option 1: Abfragen verwenden

Es gibt zwei SQL-Anweisungen, mit denen Sie eine Spalte löschen können:

• SELECT * EXCEPT
• ALTER TABLE DROP COLUMN

Der folgende Abschnitt zeigt, wie Sie eine SELECT * EXCEPT-Abfrage verwenden, die die zu entfernende(n) Spalte(n) ausschließt, und das Abfrageergebnis zum Überschreiben der Tabelle oder zum Erstellen einer neuen Zieltabelle verwendet.

Informationen zur Verwendung der Anweisung ALTER TABLE DROP COLUMN finden Sie in den Beispielen auf der Seite Datendefinitionssprache.

Vorteile

• Wenn Sie eine Abfrage verwenden, um die Daten in eine neue Zieltabelle zu schreiben, werden die ursprünglichen Daten beibehalten.
• Wenn Sie die ursprüngliche Tabelle über den Abfragejob überschreiben, fallen anstatt für zwei Tabellen nur für eine Speicherkosten an, die ursprünglichen Daten gehen jedoch verloren.

Nachteile

• Beim Löschen einer Spalte über eine Abfrage müssen die Daten in allen Spalten gescannt werden, abgesehen von der Spalte, die Sie entfernen. Die Abfragegebühren können beträchtlich sein, wenn die Tabelle sehr groß ist.
• Wenn Sie die Abfrageergebnisse in eine neue Zieltabelle schreiben, fallen für die alte und die neue Tabelle Speicherkosten an (wenn Sie die alte Tabelle nicht löschen).

Beispiel für SELECT * EXCEPT

Im folgenden Beispiel sehen Sie eine Standard-SQL-Abfrage, mit der alle Daten in mydataset.mytable mit Ausnahme von column_two ausgewählt werden. Die vorhandene Tabelle wird mit dem Abfrageergebnis überschrieben.

Console

1. Klicken Sie in der Console auf Neue Abfrage erstellen.

2. Geben Sie im Abfrageeditor die folgende Abfrage ein, um alle Daten in mydataset.mytable mit Ausnahme von column_two auszuwählen. mydataset.mytable befindet sich in Ihrem Standardprojekt.

   SELECT
    * EXCEPT(column_two)
   FROM
    mydataset.mytable
   

3. Klicken Sie auf Mehr und wählen Sie Abfrageeinstellungen aus.

4. Aktivieren Sie im Abschnitt Ziel die Option Zieltabelle für Abfrageergebnisse festlegen.

5. Für die folgenden Felder gilt:

   1. Behalten Sie für Projektname das Standardprojekt als Wert bei. Dies ist das Projekt, das mydataset.mytable enthält.

   2. Wählen Sie mydataset als Dataset-Name aus.

   3. Geben Sie im Feld Tabellenname mytable ein.

   4. Klicken Sie auf OK.

6. Wählen Sie im Abschnitt Schreibeinstellung für Zieltabelle die Option Tabelle überschreiben als Schreibeinstellung aus. Bei dieser Einstellung wird mytable mit den Abfrageergebnissen überschrieben.

7. Optional: Klicken Sie unter Verarbeitungsstandort auf Automatische Auswahl und wählen Sie den Standort Ihrer Daten aus.

8. Klicken Sie auf Speichern, um die Einstellungen zu aktualisieren, und klicken Sie dann im Abfrageeditor auf Ausführen. Wenn der Abfragejob abgeschlossen ist, sind in der Tabelle jetzt alle Spalten außer column_two enthalten.

bq

Geben Sie den folgenden bq query-Befehl ein, um alle Daten aus mydataset.mytable mit Ausnahme von column_two auszuwählen. mydataset.mytable befindet sich in Ihrem Standardprojekt. Die Abfrageergebnisse werden mit dem Flag --destination_table in mydataset.mytable geschrieben. Das Flag --replace wird verwendet, um mytable zu überschreiben. Mit dem Flag use_legacy_sql=false legen Sie die Standard-SQL-Abfragesyntax fest.

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

bq query \
--destination_table mydataset.mytable \
--replace \
--use_legacy_sql=false \
'SELECT
  * EXCEPT(column_two)
FROM
  mydataset.mytable'

API

Rufen Sie die Methode jobs.insert auf und konfigurieren Sie einen query-Job, um alle Daten aus mydataset.mytable mit Ausnahme von column_two auszuwählen. Optional: Geben Sie im Abschnitt jobReference im Attribut location Ihren Standort an.

Die im Abfragejob verwendete SQL-Abfrage würde so lauten: SELECT * EXCEPT(column_two) FROM mydataset.mytable.

Fügen Sie mydataset.mytable im Attribut configuration.query.destinationTable hinzu und geben Sie WRITE_TRUNCATE im Attribut configuration.query.writeDisposition an, um mytable mit den Abfrageergebnissen zu überschreiben.

Option 2: Daten in eine neue Tabelle exportieren und laden

Sie haben noch eine weitere Möglichkeit, um Spalten zu entfernen. Exportieren Sie dazu die Tabellendaten in Cloud Storage, löschen Sie die Daten für die zu entfernenden Spalten und laden Sie die Daten dann in eine neue Tabelle mit einer Schemadefinition, die nicht die entfernten Spalten enthält. Außerdem können Sie die vorhandene Tabelle durch den Ladejob überschreiben.

Vorteile

• Für die Export- und Ladejobs entstehen Ihnen keine Gebühren. Derzeit sind BigQuery-Lade- und Exportjobs kostenlos.
• Wenn Sie die ursprüngliche Tabelle über den Ladejob überschreiben, fallen anstatt für zwei Tabellen nur für eine Speicherkosten an, die ursprünglichen Daten gehen jedoch verloren.

Nachteile

• Wenn Sie die Daten in eine neue Tabelle laden, fallen für die ursprüngliche und die neue Tabelle Speicherkosten an (wenn Sie die alte Tabelle nicht löschen).
• Es fallen Gebühren für das Speichern der exportierten Daten in Cloud Storage an.

Tabellensicherheit

Informationen zum Steuern des Zugriffs auf Tabellen in BigQuery finden Sie unter Einführung in die Tabellenzugriffssteuerung.