SQL-Abfragen mit der Translation API übersetzen

In diesem Dokument wird beschrieben, wie Sie mit der Translation API in BigQuery Skripts, die in anderen SQL-Dialekten geschrieben sind, in GoogleSQL-Abfragen übersetzen. Die Translation API kann den Prozess der Migration von Arbeitslasten zu BigQuery vereinfachen.

Eine Liste der von diesem SQL-Übersetzer unterstützten SQL-Dialekte und eine Liste der unterstützten Verarbeitungsstandorte finden Sie unter Unterstützte SQL Dialekte und Standorte.

Hinweis

Führen Sie die folgenden Schritte aus, bevor Sie einen Übersetzungsjob senden.

Übersetzungen aktivieren

Aktivieren Sie die erforderliche BigQuery Migration API. Weitere Informationen finden Sie unter SQL Übersetzungen aktivieren.

Erforderliche Berechtigungen

Bitten Sie Ihren Administrator, Ihnen die folgenden IAM-Rollen für die parent Ressource zu gewähren, um die Berechtigungen zu erhalten, die Sie zum Erstellen von Übersetzungsjobs mit dem interaktiven Übersetzer, der Translation API oder dem Batch-SQL-Übersetzer benötigen:

  • Migrationsjobs ansehen und überwachen: MigrationWorkflow-Betrachter (roles/bigquerymigration.viewer)
  • Migrationsjobs senden: MigrationWorkflow-Bearbeiter (roles/bigquerymigration.editor)
  • Auf die Cloud Storage-Buckets für Eingabe- und Ausgabedateien zugreifen: Storage-Objektadministrator (roles/storage.objectAdmin) für den Quell- und Ziel-Cloud Storage-Bucket.

Weitere Informationen zum Zuweisen von Rollen finden Sie unter Zugriff auf Projekte, Ordner und Organisationen verwalten.

Diese vordefinierten Rollen enthalten die Berechtigungen, die zum Erstellen von Übersetzungsjobs mit dem interaktiven Übersetzer, der Translation API oder dem Batch-SQL-Übersetzer erforderlich sind. Maximieren Sie den Abschnitt Erforderliche Berechtigungen , um die notwendigen Berechtigungen anzuzeigen, die erforderlich sind:

Erforderliche Berechtigungen

Die folgenden Berechtigungen sind erforderlich, um Übersetzungsjobs mit dem interaktiven Übersetzer, der Translation API oder dem Batch-SQL-Übersetzer zu erstellen:

  • bigquerymigration.workflows.create
  • bigquerymigration.workflows.get
  • bigquerymigration.workflows.list
  • bigquerymigration.workflows.delete
  • bigquerymigration.subtasks.get
  • bigquerymigration.subtasks.list
  • storage.objects.get
  • storage.objects.list
  • storage.objects.create

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

Eingabedateien nach Cloud Storage hochladen

Wenn Sie die Google Cloud Console oder die BigQuery Migration API verwenden möchten, um einen Übersetzungsjob auszuführen, müssen Sie die Quelldateien mit den Abfragen und Skripts hochladen, die in Cloud Storage übersetzt werden sollen. Sie können auch beliebige Metadatendateien oder YAML-Konfigurationsdateien in denselben Cloud Storage-Bucket hochladen, der die Quelldateien enthält. Weitere Informationen zum Erstellen von Buckets und zum Hochladen von Dateien in Cloud Storage erhalten Sie unter Buckets erstellen und Objekte aus einem Dateisystem hochladen.

Nicht unterstützte SQL-Funktionen mit Hilfs-UDFs verarbeiten

Beim Übersetzen von SQL aus einem Quelldialekt in BigQuery haben einige Funktionen möglicherweise kein direktes Äquivalent. Um dieses Problem zu beheben, stellt der BigQuery Migration Service (und die breitere BigQuery-Community) benutzerdefinierte Hilfsfunktionen (UDFs) bereit, die das Verhalten dieser nicht unterstützten Funktionen des Quelldialekts nachbilden.

Diese UDFs befinden sich häufig im bqutil öffentlichen Dataset, sodass übersetzte Abfragen anfänglich mit dem Format bqutil.<dataset>.<function>() darauf verweisen können. Beispiel: bqutil.fn.cw_count().

Wichtige Überlegungen für Produktionsumgebungen

bqutil bietet zwar einen praktischen Zugriff auf diese Hilfs-UDFs für die erste Übersetzung und Tests, die direkte Verwendung von bqutil für Produktionsarbeitslasten wird jedoch aus mehreren Gründen nicht empfohlen:

  1. Versionsverwaltung: Das bqutil-Projekt hostet die neueste Version dieser UDFs. Das bedeutet, dass sich ihre Definitionen im Laufe der Zeit ändern können. Wenn Sie sich direkt auf bqutil verlassen, kann dies zu unerwartetem Verhalten oder zu Breaking Changes in Ihren Produktionsabfragen führen, wenn die Logik einer UDF aktualisiert wird.
  2. Isolierung von Abhängigkeiten: Wenn Sie UDFs in Ihrem eigenen Projekt bereitstellen, wird Ihre Produktionsumgebung von externen Änderungen isoliert.
  3. Anpassung: Möglicherweise müssen Sie diese UDFs ändern oder optimieren, um sie besser an Ihre spezifische Geschäftslogik oder Leistungsanforderungen anzupassen. Das ist nur möglich, wenn sie sich in Ihrem eigenen Projekt befinden.
  4. Sicherheit und Governance: Die Sicherheitsrichtlinien Ihrer Organisation können den direkten Zugriff auf öffentliche Datasets wie bqutil für die Verarbeitung von Produktionsdaten einschränken. Das Kopieren von UDFs in Ihre kontrollierte Umgebung entspricht solchen Richtlinien.

Hilfs-UDFs in Ihrem Projekt bereitstellen

Für eine zuverlässige und stabile Verwendung in der Produktion sollten Sie diese Hilfs-UDFs in Ihrem eigenen Projekt und Dataset bereitstellen. So haben Sie die vollständige Kontrolle über Version, Anpassung und Zugriff. Eine detaillierte Anleitung zum Bereitstellen dieser UDFs finden Sie im Leitfaden zur UDF-Bereitstellung auf GitHub. Dieser Leitfaden enthält die erforderlichen Skripts und Schritte zum Kopieren der UDFs in Ihre Umgebung.

Übersetzungsjob senden

Verwenden Sie zum Senden eines Übersetzungsjobs mit der Translation API die Methode projects.locations.workflows.create und geben Sie eine Instanz der Ressource MigrationWorkflow mit einer unterstützten Aufgabentyp.

Sobald der Job gesendet wurde, können Sie eine Abfrage stellen, um Ergebnisse zu erhalten.

Batchübersetzung erstellen

Mit dem folgenden curl-Befehl wird ein Batchübersetzungsjob erstellt, in dem die Ein- und Ausgabedateien in Cloud Storage gespeichert werden. Das Feld source_target_mapping enthält eine Liste, die die literal-Quelleinträge einem optionalen relativen Pfad für die Zielausgabe zuordnet.

curl -d "{
  \"tasks\": {
      string: {
        \"type\": \"TYPE\",
        \"translation_details\": {
            \"target_base_uri\": \"TARGET_BASE\",
            \"source_target_mapping\": {
              \"source_spec\": {
                  \"base_uri\": \"BASE\"
              }
            },
            \"target_types\": \"TARGET_TYPES\",
        }
      }
  }
  }" \
  -H "Content-Type:application/json" \
  -H "Authorization: Bearer TOKEN" -X POST https://bigquerymigration.googleapis.com/v2alpha/projects/PROJECT_ID/locations/LOCATION/workflows

Ersetzen Sie Folgendes:

  • TYPE: Der Aufgabentyp der Übersetzung, der den Quell- und Zieldialekt bestimmt.
  • TARGET_BASE: Der Basis-URI für alle Übersetzungsausgaben.
  • BASE: Der Basis-URI für alle Dateien, die als Quellen für die Übersetzung gelesen werden.

  • TARGET_TYPES (optional): Die generierten Ausgabetypen. Wenn nicht angegeben, wird SQL generiert.

    • sql (Standard): Die übersetzten SQL-Abfragedateien.
    • suggestion: Von KI generierte Vorschläge.

    Die Ausgabe wird in einem Unterordner im Ausgabeverzeichnis gespeichert. Der Unterordner wird anhand des Werts in TARGET_TYPES benannt.

  • TOKEN: das Token zur Authentifizierung. Verwenden Sie zum Generieren eines Tokens den Befehl gcloud auth print-access-token oder den OAuth 2.0 Playground (verwenden Sie den Bereich https://www.googleapis.com/auth/cloud-platform).

  • PROJECT_ID: das Projekt, in dem die Übersetzung verarbeitet werden soll.

  • LOCATION: der Ort, an dem der Job verarbeitet wird.

Der vorherige Befehl gibt eine Antwort zurück, die eine Workflow-ID im Format projects/PROJECT_ID/locations/LOCATION/workflows/WORKFLOW_ID enthält.

Beispiel für eine Batchübersetzung

Wenn Sie die Teradata-SQL-Skripts im Cloud Storage-Verzeichnis gs://my_data_bucket/teradata/input/ übersetzen und die Ergebnisse im Cloud Storage-Verzeichnis gs://my_data_bucket/teradata/output/ speichern möchten, können Sie die folgende Abfrage verwenden:

{
  "tasks": {
     "task_name": {
       "type": "Teradata2BigQuery_Translation",
       "translation_details": {
         "target_base_uri": "gs://my_data_bucket/teradata/output/",
           "source_target_mapping": {
             "source_spec": {
               "base_uri": "gs://my_data_bucket/teradata/input/"
             }
          },
       }
    }
  }
}

Dieser Aufruf gibt eine Nachricht mit der erstellten Workflow-ID im Feld "name" zurück:

{
  "name": "projects/123456789/locations/us/workflows/12345678-9abc-def1-2345-6789abcdef00",
  "tasks": {
    "task_name": { /*...*/ }
  },
  "state": "RUNNING"
}

Wenn Sie den aktualisierten Status des Workflows abrufen möchten, führen Sie eine GET Abfrage aus. Der Job sendet Ausgaben an Cloud Storage, während er ausgeführt wird. Der state des Jobs ändert sich in COMPLETED, nachdem alle angeforderten target_types generiert wurden. Wenn die Aufgabe erfolgreich war, finden Sie die übersetzte SQL-Abfrage unter gs://my_data_bucket/teradata/output.

Beispiel für eine Batchübersetzung mit KI-Vorschlägen

Im folgenden Beispiel werden die Teradata-SQL-Skripts im Cloud Storage-Verzeichnis gs://my_data_bucket/teradata/input/ übersetzt und die Ergebnisse mit einem zusätzlichen KI-Vorschlag im Cloud Storage-Verzeichnis gs://my_data_bucket/teradata/output/ gespeichert:

{
  "tasks": {
     "task_name": {
       "type": "Teradata2BigQuery_Translation",
       "translation_details": {
         "target_base_uri": "gs://my_data_bucket/teradata/output/",
           "source_target_mapping": {
             "source_spec": {
               "base_uri": "gs://my_data_bucket/teradata/input/"
             }
          },
          "target_types": "suggestion",
       }
    }
  }
}

Nachdem die Aufgabe erfolgreich ausgeführt wurde, finden Sie die KI-Vorschläge im Cloud Storage-Verzeichnis gs://my_data_bucket/teradata/output/suggestion.

Interaktiven Übersetzungsjob mit Stringliteraleingaben und -ausgaben erstellen

Mit dem folgenden curl-Befehl wird ein Übersetzungsjob mit Ein- und Ausgaben von Stringliteralen erstellt. Das Feld source_target_mapping enthält eine Liste, die die Quellverzeichnisse einem optionalen relativen Pfad für die Zielausgabe zuordnet.

curl -d "{
  \"tasks\": {
      string: {
        \"type\": \"TYPE\",
        \"translation_details\": {
        \"source_target_mapping\": {
            \"source_spec\": {
              \"literal\": {
              \"relative_path\": \"PATH\",
              \"literal_string\": \"STRING\"
              }
            }
        },
        \"target_return_literals\": \"TARGETS\",
        }
      }
  }
  }" \
  -H "Content-Type:application/json" \
  -H "Authorization: Bearer TOKEN" -X POST https://bigquerymigration.googleapis.com/v2alpha/projects/PROJECT_ID/locations/LOCATION/workflows

Ersetzen Sie Folgendes:

  • TYPE: Der Aufgabentyp der Übersetzung, der den Quell- und Zieldialekt bestimmt.
  • PATH: die Kennung des Literaleintrags, ähnlich einem Dateinamen oder Pfad.
  • STRING: String der Literaleingabedaten (z. B. SQL), die übersetzt werden sollen.
  • TARGETS: die erwarteten Ziele, die der Nutzer direkt in der Antwort im Format literal zurückgeben möchte. Diese sollten im Ziel-URI-Format vorliegen (z. B. GENERATED_DIR + target_spec.relative_path + source_spec.literal.relative_path). Alles, was nicht in dieser Liste enthalten ist, wird in der Antwort nicht zurückgegeben. Das generierte Verzeichnis GENERATED_DIR für allgemeine SQL-Übersetzungen ist sql/.
  • TOKEN: das Token zur Authentifizierung. Verwenden Sie zum Generieren eines Tokens den Befehl gcloud auth print-access-token oder den OAuth 2.0 Playground (verwenden Sie den Bereich https://www.googleapis.com/auth/cloud-platform).
  • PROJECT_ID: das Projekt, in dem die Übersetzung verarbeitet werden soll.
  • LOCATION: der Ort, an dem der Job verarbeitet wird.

Der vorherige Befehl gibt eine Antwort zurück, die eine Workflow-ID im Format projects/PROJECT_ID/locations/LOCATION/workflows/WORKFLOW_ID enthält.

Wenn der Job abgeschlossen ist, können Sie die Ergebnisse abrufen, indem Sie den Job abfragen und das Inline-Feld translation_literals in der Antwort prüfen, nachdem der Workflow abgeschlossen ist.

Beispiel für eine interaktive Übersetzung

Um den Hive-SQL-String select 1 interaktiv zu übersetzen, können Sie die folgende Abfrage verwenden:

"tasks": {
  string: {
    "type": "HiveQL2BigQuery_Translation",
    "translation_details": {
      "source_target_mapping": {
        "source_spec": {
          "literal": {
            "relative_path": "input_file",
            "literal_string": "select 1"
          }
        }
      },
      "target_return_literals": "sql/input_file",
    }
  }
}

Sie können für das Literal ein beliebiges relative_path verwenden. Das übersetzte Literal wird jedoch nur in den Ergebnissen angezeigt, wenn Sie sql/$relative_path in target_return_literals einschließen. Sie können auch mehrere Literale in einer einzigen Abfrage angeben. In diesem Fall müssen alle relativen Pfade in target_return_literals enthalten sein.

Dieser Aufruf gibt eine Nachricht mit der erstellten Workflow-ID im "name" Feld zurück:

{
  "name": "projects/123456789/locations/us/workflows/12345678-9abc-def1-2345-6789abcdef00",
  "tasks": {
    "task_name": { /*...*/ }
  },
  "state": "RUNNING"
}

Wenn Sie den aktualisierten Status des Workflows abrufen möchten, führen Sie eine GET-Abfrage aus. Der Job ist abgeschlossen, wenn sich "state" in COMPLETED ändert. Wenn die Aufgabe erfolgreich war, finden Sie die übersetzte SQL-Anweisung in der Antwortnachricht:

{
  "name": "projects/123456789/locations/us/workflows/12345678-9abc-def1-2345-6789abcdef00",
  "tasks": {
    "string": {
      "id": "0fedba98-7654-3210-1234-56789abcdef",
      "type": "HiveQL2BigQuery_Translation",
      /* ... */
      "taskResult": {
        "translationTaskResult": {
          "translatedLiterals": [
            {
              "relativePath": "sql/input_file",
              "literalString": "-- Translation time: 2023-10-05T21:50:49.885839Z\n-- Translation job ID: projects/123456789/locations/us/workflows/12345678-9abc-def1-2345-6789abcdef00\n-- Source: input_file\n-- Translated from: Hive\n-- Translated to: BigQuery\n\nSELECT\n    1\n;\n"
            }
          ],
          "reportLogMessages": [
            ...
          ]
        }
      },
      /* ... */
    }
  },
  "state": "COMPLETED",
  "createTime": "2023-10-05T21:50:49.543221Z",
  "lastUpdateTime": "2023-10-05T21:50:50.462758Z"
}

Übersetzungsausgabe ansehen

Rufen Sie nach dem Ausführen des Übersetzungsjobs die Ergebnisse ab. Geben Sie dazu die Workflow-ID des Übersetzungsjobs mit dem folgenden Befehl an:

curl \
-H "Content-Type:application/json" \
-H "Authorization:Bearer TOKEN" -X GET https://bigquerymigration.googleapis.com/v2alpha/projects/PROJECT_ID/locations/LOCATION/workflows/WORKFLOW_ID

Ersetzen Sie Folgendes:

  • TOKEN: das Token zur Authentifizierung. Verwenden Sie zum Generieren eines Tokens den Befehl gcloud auth print-access-token oder den OAuth 2.0 Playground (verwenden Sie den Bereich https://www.googleapis.com/auth/cloud-platform).
  • PROJECT_ID: das Projekt, in dem die Übersetzung verarbeitet werden soll.
  • LOCATION: der Ort, an dem der Job verarbeitet wird.
  • WORKFLOW_ID: die ID, die beim Erstellen eines Übersetzungsworkflows generiert wird.

Die Antwort enthält den Status des Migrationsworkflows und alle abgeschlossenen Dateien in target_return_literals.

Die Antwort enthält den Status des Migrationsworkflows und alle abgeschlossenen Dateien in target_return_literals. Sie können diesen Endpunkt abfragen, um den Status Ihres Workflows zu prüfen.