SQL-Übersetzungen mit YAML-Konfigurationsdateien transformieren

In diesem Dokument erfahren Sie, wie Sie mit YAML-Konfigurationsdateien SQL-Code während der Migration zu BigQuery transformieren. Sie enthält Richtlinien zum Erstellen eigener YAML-Konfigurationsdateien und Beispiele für verschiedene Übersetzungstransformationen, die von dieser Funktion unterstützt werden.

Wenn Sie den interaktiven SQL-Übersetzer von BigQuery, die BigQuery Migration API oder eine Batch-SQL-Übersetzung verwenden, können Sie YAML-Konfigurationsdateien angeben, um die Übersetzung einer SQL-Abfrage zu ändern. Mithilfe von YAML-Konfigurationsdateien können Sie bei der Übersetzung von SQL-Abfragen aus Ihrer Quelldatenbank weitere Anpassungen vornehmen.

Sie können eine YAML-Konfigurationsdatei zur Verwendung in einer SQL-Übersetzung auf folgende Weisen angeben:

Der interaktive SQL-Übersetzer, die BigQuery Migration API, der Batch-SQL-Übersetzer und der Python-Batchübersetzungsclient unterstützen die Verwendung mehrerer YAML-Konfigurationsdateien in einem einzigen Übersetzungsjob. Weitere Informationen finden Sie unter Mehrere YAML-Konfigurationen anwenden.

Anforderungen an YAML-Konfigurationsdateien

Lesen Sie sich die folgenden Informationen durch, bevor Sie eine YAML-Konfigurationsdatei erstellen, um sicherzustellen, dass sie mit dem BigQuery Migration Service kompatibel ist:

  • Sie müssen die YAML-Konfigurationsdateien in das Stammverzeichnis des Cloud Storage-Bucket hochladen, der die Eingabedateien für die SQL-Übersetzung enthält. Informationen zum Erstellen von Buckets und zum Hochladen von Dateien in Cloud Storage erhalten Sie unter Buckets erstellen und Objekte aus einem Dateisystem hochladen.
  • Die Dateigröße für eine einzelne YAML-Konfigurationsdatei darf 1 MB nicht überschreiten.
  • Die Gesamtgröße aller YAML-Konfigurationsdateien, die in einem einzelnen SQL-Übersetzungsjob verwendet werden, darf 4 MB nicht überschreiten.
  • Wenn Sie die regex-Syntax für den Namensabgleich verwenden, nutzen Sie RE2/J.
  • Alle YAML-Dateinamen für die Konfiguration müssen die Erweiterung .config.yaml haben, z. B. change-case.config.yaml.
    • config.yaml allein ist kein gültiger Name für die Konfigurationsdatei.

Richtlinien zum Erstellen einer YAML-Konfigurationsdatei

Dieser Abschnitt enthält einige allgemeine Richtlinien zum Erstellen einer YAML-Konfigurationsdatei:

Jede Konfigurationsdatei muss einen Header enthalten, in dem der Konfigurationstyp angegeben ist. Mit dem Typ object_rewriter werden SQL-Übersetzungen in einer YAML-Konfigurationsdatei angegeben. Im folgenden Beispiel wird der Typ object_rewriter verwendet, um einen Namen in Großbuchstaben zu schreiben:

type: object_rewriter
global:
  case:
    all: UPPERCASE

Entitätsauswahl

Wenn Sie entitätsspezifische Transformationen ausführen möchten, geben Sie die Entität in der Konfigurationsdatei an. Alle match-Properties sind optional. Verwenden Sie nur die match-Properties, die für eine Transformation erforderlich sind. In der folgenden YAML-Konfigurationsdatei werden Attribute enthüllt, die zugeordnet werden sollen, um bestimmte Entitäten auszuwählen:

match:
  database: <literal_name>
  schema: <literal_name>
  relation: <literal_name>
  attribute: <literal_name>
  databaseRegex: <regex>
  schemaRegex: <regex>
  relationRegex: <regex>
  attributeRegex: <regex>

Beschreibung der einzelnen match-Attribute:

  • database oder db: die Komponente „project_id“.
  • schema: die Dataset-Komponente.
  • relation: die Tabellenkomponente.
  • attribute: die Spaltenkomponente. Nur für die Attributauswahl gültig
  • databaseRegex oder dbRegex: Ordnet database-Attribut einem regulären Ausdruck zu (Vorschau).
  • schemaRegex: ordnet schema-Attribute regulären Ausdrücken zu (Vorschau).
  • relationRegex: ordnet relation-Attribute regulären Ausdrücken zu (Vorschau).
  • attributeRegex: ordnet attribute-Attribute regulären Ausdrücken zu Nur gültig für die Attributauswahl (Vorschau).

In der folgenden YAML-Konfigurationsdatei werden beispielsweise die match-Attribute angegeben, um die Tabelle testdb.acme.employee für eine Transformation in eine temporäre Tabelle auszuwählen.

type: object_rewriter
relation:
-
  match:
    database: testdb
    schema: acme
    relation: employee
  temporary: true

Sie können die Attribute databaseRegex, schemaRegex, relationRegex und attributeRegex verwenden, um reguläre Ausdrücke anzugeben und eine Teilmenge von Entitäten auszuwählen. Im folgenden Beispiel werden alle Beziehungen vom tmp_schema-Schema in testdb zu temporär geändert, solange ihr Name mit tmp_ beginnt:

type: object_rewriter
relation:
-
  match:
    schema: tmp_schema
    relationRegex: "tmp_.*"
  temporary: true

Bei der Übereinstimmung von Literal- und regex-Properties wird die Groß- und Kleinschreibung nicht berücksichtigt. Sie können die Groß- und Kleinschreibung erzwingen, indem Sie regex mit dem deaktivierten Flag i verwenden, wie im folgenden Beispiel gezeigt:

match:
  relationRegex: "(?-i:<actual_regex>)"

Sie können auch vollständig qualifizierte Entitäten mit einer entsprechenden Kurzstringsyntax angeben. Bei einer Kurzzeichen-Syntax werden genau drei (für die Beziehungsauswahl) oder vier (für die Attributauswahl) Namenssegmente erwartet, die durch Punkte voneinander getrennt sind, wie im Beispiel testdb.acme.employee. Die Segmente werden dann intern so interpretiert, als wären sie database, schema, relation und attribute respektive übergeben worden. Das heißt, dass Namen exakt abgeglichen werden. Daher sind reguläre Ausdrücke in der abgekürzten Syntax nicht zulässig. Im folgenden Beispiel wird die Verwendung der Kurzstringsyntax zum Angeben eines vollständig qualifizierten Elements in einer YAML-Konfigurationsdatei veranschaulicht:

type: object_rewriter
relation:
-
  match : "testdb.acme.employee"
  temporary: true

Wenn eine Tabelle einen Punkt im Namen enthält, können Sie den Namen nicht in einer abgekürzten Syntax angeben. In diesem Fall müssen Sie einen Objektabgleich verwenden. Im folgenden Beispiel wird die Tabelle testdb.acme.stg.employee zu temporär geändert:

type: object_rewriter
relation:
-
  match:
    database: testdb
    schema: acme
    relation: stg.employee
  temporary: true

In der YAML-Konfiguration wird key als Alias für match akzeptiert.

Standarddatenbank

Einige SQL-Dialekte, insbesondere Teradata, unterstützen database-name im qualifizierten Namen nicht. In diesem Fall ist es am einfachsten, Entitäten abzugleichen, indem Sie die Property database in match weglassen.

Sie können jedoch die default_database-Eigenschaft des BigQuery-Migrationsdienstes festlegen und diese Standarddatenbank in der match verwenden.

Unterstützte Zielattributtypen

Mit der Konfigurations-YAML-Datei können Sie Attributtyptransformationen ausführen, bei denen der Datentyp einer Spalte vom Quelltyp in einen Zieltyp umgewandelt wird. Die YAML-Konfigurationsdatei unterstützt die folgenden Zieltypen:

  • BOOLEAN
  • TINYINT
  • SMALLINT
  • INTEGER
  • BIGINT
  • FLOAT
  • DOUBLE
  • NUMERIC (Unterstützt optionale Genauigkeit und Skalierung, z. B. NUMERIC(18, 2))
  • TIME
  • TIMETZ
  • DATE
  • DATETIME
  • TIMESTAMP
  • TIMESTAMPTZ
  • CHAR (Unterstützt optionale Genauigkeit, z. B. CHAR(42))
  • VARCHAR (Unterstützt optionale Genauigkeit, z. B. VARCHAR(42))

Beispiele für YAML-Konfigurationen

Dieser Abschnitt enthält Beispiele zum Erstellen verschiedener YAML-Konfigurationsdateien für Ihre SQL-Übersetzungen. In jedem Beispiel wird die YAML-Syntax zum Transformieren Ihrer SQL-Übersetzung auf bestimmte Weisen beschrieben, und zwar mit einer kurzen Beschreibung. Jedes Beispiel enthält auch den Inhalt einer teradata-input.sql- oder hive-input.sql-Datei und einer bq-output.sql-Datei, sodass Sie die Auswirkungen einer Konfigurations-YAML in einer BigQuery-SQL-Abfrageübersetzung vergleichen können.

In den folgenden Beispielen wird Teradata oder Hive als Eingabe-SQL-Dialekt und BigQuery SQL als Ausgabedialekt verwendet. In den folgenden Beispielen wird ebenfalls testdb als Standarddatenbank und testschema als Schemasuchepfad verwendet.

Objektnamen ändern

Mit der folgenden Konfigurations-YAML-Datei wird die Groß- oder Kleinschreibung von Objektnamen geändert:

type: object_rewriter
global:
  case:
    all: UPPERCASE
    database: LOWERCASE
    attribute: LOWERCASE

Eine SQL-Übersetzung mit dieser YAML-Konfigurationsdatei könnte so aussehen:

teradata-input.sql 
      create table x(a int);
      select * from x;
bq-output.sql 
      CREATE TABLE testdb.TESTSCHEMA.X
      (
        a INT64
      )
      ;
      SELECT
          X.a
        FROM
          testdb.TESTSCHEMA.X
      ;

Tabelle temporär machen

Mit der folgenden YAML-Konfigurationsdatei wird eine reguläre Tabelle in eine temporäre Tabelle umgewandelt:

type: object_rewriter
relation:
  -
    match: "testdb.testschema.x"
    temporary: true

Eine SQL-Übersetzung mit dieser YAML-Konfigurationsdatei könnte so aussehen:

teradata-input.sql 
    create table x(a int);
bq-output.sql 
    CREATE TEMPORARY TABLE x
    (
      a INT64
    )
    ;

Tabelle sitzungsspezifisch machen

Mit der folgenden YAML-Konfigurationsdatei wird eine reguläre Tabelle in eine sitzungsspezifische Tabelle mit einer Ablaufzeit von 60 Sekunden geändert.

type: object_rewriter
relation:
  -
    match: "testdb.testschema.x"
    ephemeral:
      expireAfterSeconds: 60

Eine SQL-Übersetzung mit dieser YAML-Konfigurationsdatei könnte so aussehen:

teradata-input.sql 
    create table x(a int);
bq-output.sql 
    CREATE TABLE testdb.testschema.x
    (
      a INT64
    )
    OPTIONS(
      expiration_timestamp=timestamp_add(current_timestamp(), interval 60 SECOND)
    );

Partitionsablauf festlegen

Mit der folgenden Konfigurations-YAML-Datei wird der Ablauf einer partitionierten Tabelle in 1 Tag geändert:

type: object_rewriter
relation:
  -
    match: "testdb.testschema.x"
    partitionLifetime:
      expireAfterSeconds: 86400

Eine SQL-Übersetzung mit dieser YAML-Konfigurationsdatei könnte so aussehen:

teradata-input.sql 
    create table x(a int, b int) partition by (a);
bq-output.sql 
    CREATE TABLE testdb.testschema.x
    (
      a INT64,
      b INT64
    )
    CLUSTER BY a
    OPTIONS(
      partition_expiration_days=1
    );

Externen Speicherort oder Format für eine Tabelle ändern

Mit der folgenden YAML-Konfigurationsdatei werden der externe Speicherort und die Formation für eine Tabelle geändert:

type: object_rewriter
relation:
  -
    match: "testdb.testschema.x"
    external:
      locations: "gs://path/to/department/files"
      format: ORC

Eine SQL-Übersetzung mit dieser YAML-Konfigurationsdatei könnte so aussehen:

teradata-input.sql 
    create table x(a int);
bq-output.sql 
    CREATE EXTERNAL TABLE testdb.testschema.x
    (
      a INT64
    )
    OPTIONS(
      format='ORC',
      uris=[
        'gs://path/to/department/files'
      ]
    );

Tabellenbeschreibung festlegen oder ändern

In der folgenden YAML-Konfiguration wird die Beschreibung einer Tabelle festgelegt:

type: object_rewriter
relation:
  -
    match: "testdb.testschema.x"
    description:
      text: "Example description."

Eine SQL-Übersetzung mit dieser YAML-Konfigurationsdatei könnte so aussehen:

teradata-input.sql 
    create table x(a int);
bq-output.sql 
    CREATE TABLE testdb.testschema.x
    (
      a INT64
    )
    OPTIONS(
      description='Example description.'
    );

Tabellenpartitionierung festlegen oder ändern

Mit der folgenden Konfigurations-YAML-Datei wird das Partitionierungsschema einer Tabelle geändert:

type: object_rewriter
relation:
  -
    match: "testdb.testschema.x"
    partition:
      simple:
        add: [a]
  -
    match: "testdb.testschema.y"
    partition:
      simple:
        remove: [a]

Eine SQL-Übersetzung mit dieser YAML-Konfigurationsdatei könnte so aussehen:

teradata-input.sql 
    create table x(a date, b int);
    create table y(a date, b int) partition by (a);
bq-output.sql 
    CREATE TABLE testdb.testschema.x
    (
      a DATE,
      b INT64
    )
    PARTITION BY a;
    CREATE TABLE testdb.testschema.y
    (
      a DATE,
      b INT64
    )
    ;

Tabellenclustering festlegen oder ändern

Mit der folgenden YAML-Konfigurationsdatei wird das Clusterschema einer Tabelle geändert:

type: object_rewriter
relation:
  -
    match: "testdb.testschema.x"
    clustering:
      add: [a]
  -
    match: "testdb.testschema.y"
    clustering:
      remove: [b]

Eine SQL-Übersetzung mit dieser YAML-Konfigurationsdatei könnte so aussehen:

hive-input.sql 
    create table x(a int, b int);
    create table y(a int, b int) clustered by (b) into 16 buckets;
bq-output.sql 
    CREATE TABLE testdb.testschema.x
    (
      a INT64,
      b INT64
    )
    CLUSTER BY a;
    CREATE TABLE testdb.testschema.y
    (
      a INT64,
      b INT64
    )
    ;

Typ eines Spaltenattributs ändern

Mit der folgenden YAML-Konfigurationsdatei wird der Datentyp für ein Attribut einer Spalte geändert:

type: object_rewriter
attribute:
  -
    match:
      database: testdb
      schema: testschema
      attributeRegex: "a+"
    type:
      target: NUMERIC(10,2)

Sie können den Quelldatentyp in einen der unterstützten Zielattributtypen umwandeln.

Eine SQL-Übersetzung mit dieser YAML-Konfigurationsdatei könnte so aussehen:

teradata-input.sql 
    create table x(a int, b int, aa int);
bq-output.sql 
    CREATE TABLE testdb.testschema.x
    (
      a NUMERIC(31, 2),
      b INT64,
      aa NUMERIC(31, 2)
    )
    ;

Verbindung zu einem externen Datensee hinzufügen

Mit der folgenden YAML-Konfigurationsdatei wird die Quelltabelle als externe Tabelle gekennzeichnet, die auf in einem externen Data Lake gespeicherte Daten verweist. Der Data Lake wird durch eine Data-Lake-Verbindung angegeben.

type: object_rewriter
relation:
-
  key: "testdb.acme.employee"
  external:
    connection_id: "connection_test"

Eine SQL-Übersetzung mit dieser YAML-Konfigurationsdatei könnte so aussehen:

hive-input.sql 
    CREATE TABLE x
    (
      a VARCHAR(150),
      b INT
    );
bq-output.sql 
    CREATE EXTERNAL TABLE x
    (
      a STRING,
      b INT64
    )
    WITH CONNECTION `connection_test`
    OPTIONS(
    );

Zeichencodierung einer Eingabedatei ändern

Standardmäßig versucht der BigQuery Migration Service, die Zeichencodierung von Eingabedateien automatisch zu erkennen. Wenn der BigQuery Migration Service die Codierung einer Datei falsch erkennt, können Sie die Zeichencodierung mit einer YAML-Konfigurationsdatei explizit angeben.

In der folgenden YAML-Konfiguration wird die explizite Zeichencodierung der Eingabedatei als ISO-8859-1 angegeben.

type: experimental_input_formats
formats:
- source:
    pathGlob: "*.sql"
  contents:
    raw:
      charset: iso-8859-1

Globale Typkonvertierung

Die folgende YAML-Konfigurationsdatei ändert einen Datentyp in allen Skripts in einen anderen. Außerdem wird ein Quelldatentyp angegeben, der im transpilierten Skript vermieden werden soll. Dies unterscheidet sich von der Konfiguration Spaltenattributstyp ändern, bei der nur der Datentyp für ein einzelnes Attribut geändert wird.

BigQuery unterstützt die folgenden Datentypkonvertierungen:

  • DATETIME bis TIMESTAMP
  • TIMESTAMP nach DATETIME (akzeptiert optionale Zeitzone)
  • TIMESTAMP WITH TIME ZONE nach DATETIME (akzeptiert optionale Zeitzone)
  • CHAR bis VARCHAR

Im folgenden Beispiel wandelt die YAML-Konfiguration den Datentyp TIMESTAMP in DATETIME um.

type: experimental_object_rewriter
global:
  typeConvert:
    timestamp: DATETIME

In Dialekten wie Teradata geben datums-/uhrzeitbezogene Funktionen wie current_date, current_time oder current_timestamp Zeitstempel basierend auf der konfigurierten Zeitzone zurück, entweder der Ortszeit oder der Sitzungszeit. BigQuery gibt hingegen immer Zeitstempel in UTC zurück. Für ein konsistentes Verhalten zwischen den beiden Dialekten muss die Zeitzone entsprechend konfiguriert werden.

Im folgenden Beispiel werden in der Konfigurations-YAML-Datei ein TIMESTAMP- und ein TIMESTAMP WITH TIME ZONE-Datentyp in DATETIME konvertiert. Die Zielzeitzone ist auf Europe/Paris festgelegt.

type: experimental_object_rewriter
global:
  typeConvert:
    timestamp:
      target: DATETIME
      timezone: Europe/Paris
    timestamptz:
      target: DATETIME
      timezone: Europe/Paris

Eine SQL-Übersetzung mit dieser YAML-Konfigurationsdatei könnte so aussehen:

teradata-input.sql 
      create table x(a timestamp);
      select a from x where a > current_timestamp(0);
bq-output.sql 
      CREATE TABLE x
      (
        a TIMESTAMP
      )
      ;
      SELECT
          x.a
        FROM
          test.x
        WHERE x.a > datetime_trunc(current_datetime('Europe/Paris'), SECOND)
      ;

Kontoabrechnung ändern auswählen

Mit der folgenden YAML-Konfigurationsdatei werden die Klauseln „Sternprojektion“, GROUP BY und ORDER BY in SELECT-Anweisungen geändert.

starProjection unterstützt die folgenden Konfigurationen:

  • ALLOW
  • PRESERVE (Standard)
  • EXPAND

groupBy und orderBy unterstützen die folgenden Konfigurationen:

  • EXPRESSION
  • ALIAS
  • INDEX

Im folgenden Beispiel wird in der YAML-Konfiguration die Sternprojektion auf EXPAND konfiguriert.

type: experimental_statement_rewriter
select:
  starProjection: EXPAND

Eine SQL-Übersetzung mit dieser YAML-Konfigurationsdatei könnte so aussehen:

teradata-input.sql 
      create table x(a int, b TIMESTAMP);
      select * from x;
bq-output.sql 
      CREATE TABLE x
      (
        a INT64,
        b DATETIME
      )
      ;
      SELECT
          x.a
          x.b
        FROM
          x
      ;

UDF-Spezifikation

In der folgenden YAML-Konfiguration wird die Signatur der benutzerdefinierten Funktionen (UDFs) angegeben, die in den Quellscripts verwendet werden. Ähnlich wie Metadaten-ZIP-Dateien können UDF-Definitionen dazu beitragen, Eingabescripts genauer zu übersetzen.

type: metadata
udfs:
  - "date parse_short_date(dt int)"

Eine SQL-Übersetzung mit dieser YAML-Konfigurationsdatei könnte so aussehen:

teradata-input.sql 
      create table x(dt int);
      select parse_short_date(dt) + 1 from x;
bq-output.sql 
      CREATE TABLE x
      (
        dt INT64
      )
      ;
      SELECT
          date_add(parse_short_date(x.dt), interval 1 DAY)
        FROM
          x
      ;

Strenge der Dezimalpräzision festlegen

Standardmäßig erhöht der BigQuery Migration Service die numerische Genauigkeit auf die höchste Genauigkeit, die für eine bestimmte Skalierung verfügbar ist. Das folgende Konfigurations-YAML überschreibt dieses Verhalten, indem die Genauigkeitsstriktheit so konfiguriert wird, dass die Dezimalgenauigkeit der Quellanweisung beibehalten wird.

type: experimental_statement_rewriter
common:
  decimalPrecision: STRICT

Eine SQL-Übersetzung mit dieser YAML-Konfigurationsdatei könnte so aussehen:

teradata-input.sql 
      create table x(a decimal(3,0));
bq-output.sql 
      CREATE TABLE x
      (
        a NUMERIC(3)
      )
      ;

Zuordnung von Ausgabenamen

Sie können SQL-Objektnamen mithilfe einer YAML-Konfiguration zuordnen. Je nach Objekt können Sie verschiedene Teile des Namens ändern.

Statische Namenszuordnung

Verwenden Sie die statische Namenszuordnung, um den Namen einer Entität zuzuordnen. Wenn Sie nur bestimmte Teile des Namens ändern, aber andere Teile des Namens beibehalten möchten, fügen Sie nur die Teile ein, die geändert werden müssen.

Mit der folgenden YAML-Konfigurationsdatei wird der Name der Tabelle von my_db.my_schema.my_table in my_new_db.my_schema.my_new_table geändert.

type: experimental_object_rewriter
relation:
-
  match: "my_db.my_schema.my_table"
  outputName:
    database: "my_new_db"
    relation: "my_new_table"

Eine SQL-Übersetzung mit dieser YAML-Konfigurationsdatei könnte so aussehen:

teradata-input.sql 
      create table my_db.my_schema.my_table(a int);
bq-output.sql 
      CREATE TABLE my_new_db.my_schema.my_new_table
      (
        a INT64
      )

Mithilfe der statischen Namenszuordnung können Sie die Region aktualisieren, die von Namen in den öffentlichen benutzerdefinierten Funktionen verwendet wird.

Im folgenden Beispiel werden die Namen in der bqutil.fn-UDF von der Standard-Multi-Region us in die Region europe_west2 geändert:

type: experimental_object_rewriter
function:
-
  match:
    database: bqutil
    schema: fn
  outputName:
    database: bqutil
    schema: fn_europe_west2

Dynamische Namenszuordnung

Mit der dynamischen Namenszuordnung können Sie mehrere Objekte gleichzeitig ändern und anhand der zugeordneten Objekte neue Namen erstellen.

In der folgenden YAML-Konfigurationsdatei wird der Name aller Tabellen geändert. Dazu wird den Tabellen, die zum Schema staging gehören, das Präfix stg_ hinzugefügt und sie werden dann in das Schema production verschoben.

type: experimental_object_rewriter
relation:
-
  match:
    schema: staging
  outputName:
    schema: production
    relation: "stg_${relation}"

Eine SQL-Übersetzung mit dieser YAML-Konfigurationsdatei könnte so aussehen:

teradata-input.sql 
      create table staging.my_table(a int);
bq-output.sql 
      CREATE TABLE production.stg_my_table
      (
        a INT64
      )
      ;

Standard-Datenbank und Schemasuchepfad angeben

In der folgenden YAML-Konfiguration werden eine Standarddatenbank und ein Schemasuchepfad angegeben.

type: environment
session:
  defaultDatabase: myproject
  schemaSearchPath: [myschema1, myschema2]

Eine SQL-Übersetzung mit dieser YAML-Konfigurationsdatei könnte so aussehen:

teradata-input.sql 
      SELECT * FROM database.table
      SELECT * FROM table1
bq-output.sql 
      SELECT * FROM myproject.database.table.
      SELECT * FROM myproject.myschema1.table1

Umschreibung des globalen Ausgabenamens

In der folgenden YAML-Konfigurationsdatei werden die Ausgabenamen aller Objekte (Datenbank, Schema, Beziehung und Attribute) im Script gemäß den konfigurierten Regeln geändert.

type: experimental_object_rewriter
global:
  outputName:
    regex:
      - match: '\s'
        replaceWith: '_'
      - match: '>='
        replaceWith: 'gte'
      - match: '^[^a-zA-Z_].*'
        replaceWith: '_$0'

Eine SQL-Übersetzung mit dieser YAML-Konfigurationsdatei könnte so aussehen:

teradata-input.sql 
      create table "test special chars >= 12"("42eid" int, "custom column" varchar(10));
bq-output.sql 
      CREATE TABLE test_special_chars_employees_gte_12
      (
        _42eid INT64,
        custom_column STRING
      )
      ;

Leistung von übersetztem SQL optimieren und verbessern

Optionale Transformationen können auf übersetztes SQL angewendet werden, um Änderungen einzuführen, die die Abfrage im Hinblick auf Leistung oder Kosten verbessern können. Diese Optimierungen sind strikt von der Groß- und Kleinschreibung abhängig und sollten daher anhand der unveränderten SQL-Ausgabe bewertet werden, um ihre tatsächliche Auswirkung auf die Leistung zu ermitteln.

Mit der folgenden YAML-Konfiguration werden optionale Transformationen aktiviert. Die Konfiguration akzeptiert eine Liste von Optimierungen und bei Optimierungen, die Parameter akzeptieren, einen Abschnitt mit optionalen Parameterwerten.

type: experimental_optimizer
transformations:
  - name: PRECOMPUTE_INDEPENDENT_SUBSELECTS
  - name: REWRITE_CTE_TO_TEMP_TABLE
    parameters:
      threshold: 1
Optimierung Optionaler Parameter Beschreibung
PRECOMPUTE_INDEPENDENT_SUBSELECTS scope: [PREDICATE, PROJECTION] Schreibt die Abfrage um, indem eine DECLARE-Anweisung hinzugefügt wird, um einen Ausdruck in einer PREDICATE-Klausel oder PROJECTION durch eine vorausberechnete Variable zu ersetzen. Dies wird als statisches Prädikat erkannt, wodurch die Menge der gelesenen Daten reduziert werden kann. Wenn der Umfang weggelassen wird, ist der Standardwert PREDICATE (d. h. WHERE- und JOIN-ON-Klausel).

Wenn Sie eine skalare Unterabfrage in eine DECLARE-Anweisung extrahieren, wird das ursprüngliche Prädikat statisch und kann daher für eine verbesserte Ausführungsplanung verwendet werden. Durch diese Optimierung werden neue SQL-Anweisungen eingeführt.
REWRITE_CTE_TO_TEMP_TABLE threshold: N Schreibt allgemeine Tabellenausdrücke (CTE) in temporäre Tabellen um, wenn mehr als N-Verweise auf denselben allgemeinen Tabellenausdruck vorhanden sind. Dies reduziert die Abfragekomplexität und erzwingt die einzige Ausführung des allgemeinen Tabellenausdrucks. Wenn N weggelassen wird, ist der Standardwert 4.

Wir empfehlen diese Optimierung, wenn nicht triviale CTEs mehrmals referenziert werden. Die Einführung von temporären Tabellen hat einen Overhead, der höher sein kann als die Ausführung mehrerer CTEs mit geringer Komplexität oder Kardinalität. Durch diese Optimierung werden neue SQL-Anweisungen eingeführt.
REWRITE_ZERO_SCALE_NUMERIC_AS_INTEGER bigint: N NUMERIC/BIGNUMERIC-Attribute mit Nullskala werden in den Typ INT64 umgeschrieben, wenn die Genauigkeit innerhalb von N liegt. Wenn N weggelassen wird, ist der Standardwert 18.

Wir empfehlen diese Optimierung bei der Übersetzung von Quelldialekten, die keine Ganzzahltypen haben. Wenn Sie Spaltentypen ändern, müssen Sie alle nachgelagerten Verwendungen auf Typkompatibilität und semantische Änderungen prüfen. Beispiel: Bruchteilungen werden zu Ganzzahldivisionen, Code, der numerische Werte erwartet
DROP_TEMP_TABLE Fügt DROP TABLE-Anweisungen für alle temporären Tabellen hinzu, die in einem Script erstellt und bis zum Ende nicht gelöscht wurden. Dadurch wird der Abrechnungszeitraum für die temporäre Tabelle von 24 Stunden auf die Ausführungszeit des Skripts reduziert. Durch diese Optimierung werden neue SQL-Anweisungen eingeführt.

Wir empfehlen diese Optimierung, wenn nach dem Ende der Skriptausführung nicht auf temporäre Tabellen zugegriffen wird, um weitere Verarbeitungsschritte auszuführen. Durch diese Optimierung werden neue SQL-Anweisungen eingeführt.
REGEXP_CONTAINS_TO_LIKE Schreibt einige Kategorien von REGEXP_CONTAINS-Übereinstimmungsmustern in LIKE-Ausdrücke um.

Wir empfehlen diese Optimierung, wenn kein anderer Prozess wie der Makroersatz darauf angewiesen ist, dass die regulären Ausdrucksmusterliterale unverändert in der Ausgabe-SQL-Anweisung beibehalten werden.
ADD_DISTINCT_TO_SUBQUERY_IN_SET_COMPARISON Fügt Unterabfragen eine DISTINCT-Klausel hinzu, die als Wert für den [NOT] IN-Operator verwendet werden.

Wir empfehlen diese Optimierung, wenn die Kardinalität (bestimmte Anzahl von Werten) des Unterabfrageergebnisses deutlich niedriger als die Anzahl der Werte ist. Wenn diese Voraussetzung nicht erfüllt ist, kann sich diese Transformation negativ auf die Leistung auswirken.

Gemini-basierte YAML-Konfigurationsdatei erstellen

Damit eine KI-Ausgabe generiert werden kann, muss das Quellverzeichnis mit der SQL-Übersetzungseingabe eine YAML-Konfigurationsdatei enthalten.

Voraussetzungen

Die YAML-Konfigurationsdatei für KI-Ausgaben muss das Suffix .ai_config.yaml haben. Beispiel: rules_1.ai_config.yaml.

Unterstützte Felder

suggestion_type: SUGGESTION_TYPE
rewrite_target: TARGET
instruction: NL_PROMPT
translation_rules:
- instruction: NL_RULE_1
  examples:
  - input: RULE_1_INPUT_1
    output: RULE_1_OUTPUT_1
  - input: RULE_1_INPUT_2
    output: RULE_1_OUTPUT_2
- instruction: NL_RULE_2
  examples:
  - input: RULE_2_INPUT_1
    output: RULE_2_OUTPUT_1
…
…

Sie können die folgenden Variablen ersetzen, um die Ausgabe der KI-Übersetzung zu konfigurieren:

  • SUGGESTION_TYPE (optional): Gibt den Typ der KI-Vorschläge an, die generiert werden sollen. Die folgenden Vorschlagstypen werden unterstützt:

    • QUERY_CUSTOMIZATION (Standardeinstellung): Es werden KI-Vorschläge für SQL-Code basierend auf den in der YAML-Konfigurationsdatei angegebenen Übersetzungsregeln generiert.
    • TRANSLATION_EXPLANATION: Er generiert Text mit einer Zusammenfassung der übersetzten GoogleSQL-Abfrage sowie den Unterschieden und Inkonsistenzen zwischen der Quell-SQL-Abfrage und der übersetzten GoogleSQL-Abfrage.
    • CUSTOM_SUGGESTION: Er generiert SQL- oder Textausgaben mithilfe von Übersetzungsartefakten. Nutzer können sich in Prompts mit Platzhaltern auf Übersetzungsartefakte beziehen. Der Übersetzungsdienst hängt die entsprechenden Artefakte an den endgültigen LLM-Prompt an, der an Gemini gesendet wird. Die folgenden Übersetzungsartefakte können in den Prompt aufgenommen werden:
      • {{SOURCE_DIALECT}}: Damit wird auf den SQL-Dialekt der Quelle verwiesen.
      • {{SOURCE_SQL}}: Damit wird auf die SQL-Datenquelle für die Übersetzung verwiesen.
      • {{TARGET_SQL}}: Damit wird auf die standardmäßig übersetzte SQL-Anweisung verwiesen.

  • TARGET (optional): Gibt an, ob die Übersetzungsregel auf die Eingabe-SQL-Anweisung SOURCE_SQL oder die Ausgabe-SQL-Anweisung TARGET_SQL angewendet werden soll (Standard).

  • NL_PROMPT (optional): Beschreiben Sie in natürlicher Sprache eine Änderung am Ziel-SQL. Die Gemini-optimierte SQL-Übersetzung prüft die Anfrage und nimmt die angegebene Änderung vor.

  • NL_RULE_1 (optional): Beschreiben Sie in natürlicher Sprache eine Übersetzungsregel.

  • RULE_1_INPUT_1 (optional): Ein SQL-Muster, das ersetzt werden soll.

  • RULE_1_OUTPUT_1 (optional): Das erwartete SQL-Muster, nachdem input ersetzt wurde.

Sie können bei Bedarf weitere translation_rules und examples hinzufügen.

Beispiele

In den folgenden Beispielen werden Gemini-basierte YAML-Konfigurationsdateien erstellt, die Sie für Ihre SQL-Übersetzungen verwenden können.

Obere Funktion in der Standardabfrage für die Übersetzungsausgabe entfernen

translation_rules:
- instruction: "Remove upper() function"
  examples:
  - input: "upper(X)"
    output: "X"

Mehrere Übersetzungsregeln erstellen, um die Übersetzungsausgabe anzupassen

translation_rules:
- instruction: "Remove upper() function"
  examples:
  - input: "upper(X)"
    output: "X"
- instruction: "Insert a comment at the head that explains each statement in detail.

SQL-Kommentare aus der Übersetzungseingabeabfrage entfernen

rewrite_target: SOURCE_SQL
translation_rules:
- instruction: "Remove all the sql comments in the input sql query."

Übersetzungserklärungen mit dem Standard-LLM-Prompt generieren

In diesem Beispiel werden die Standard-LLM-Prompts des Übersetzungsdienstes verwendet, um Texterläuterungen zu generieren:

suggestion_type: "TRANSLATION_EXPLANATION"

Erstellt Übersetzungserklärungen anhand Ihrer eigenen Prompts in natürlicher Sprache

suggestion_type: "TRANSLATION_EXPLANATION"
instruction: "Explain the syntax differences between the source Teradata query and the translated GoogleSQL query."

Fehler bei der Übersetzung von MySQL in GoogleSQL behoben: unsupported constraint on PRIMARY

suggestion_type: "CUSTOM_SUGGESTION"
instruction: "Add PRIMARY KEY (...) NOT ENFORCED to the target sql as a column constraint based on the source sql. Output sql without sql code block.\n\nsource sql: {{SOURCE_SQL}}\ntarget sql: {{TARGET_SQL}}"

Mehrere YAML-Konfigurationen anwenden

Wenn Sie eine YAML-Konfigurationsdatei in einer Batch- oder interaktiven SQL-Übersetzung angeben, können Sie mehrere YAML-Konfigurationsdateien in einem einzelnen Übersetzungsjob auswählen, um verschiedene Transformationen widerzuspiegeln. Wenn mehrere Konfigurationen in Konflikt stehen, wird eine Transformation möglicherweise von einer anderen überschrieben. Wir empfehlen, in jeder Datei unterschiedliche Konfigurationseinstellungen zu verwenden, um Konflikte bei Transformationen im selben Übersetzungsauftrag zu vermeiden.

Im folgenden Beispiel werden zwei separate YAML-Konfigurationsdateien aufgelistet, die für einen einzelnen SQL-Übersetzungsjob bereitgestellt wurden. Eine zum Ändern des Attributs einer Spalte und eine, um die Tabelle als temporär zu bestimmen:

change-type-example.config.yaml:

type: object_rewriter
attribute:
  -
    match: "testdb.testschema.x.a"
    type:
      target: NUMERIC(10,2)

make-temp-example.config.yaml:

type: object_rewriter
relation:
  -
    match: "testdb.testschema.x"
    temporary: true

Eine SQL-Übersetzung mit diesen beiden YAML-Konfigurationsdateien könnte so aussehen:

teradata-input.sql 
    create table x(a int);
bq-output.sql 
    CREATE TEMPORARY TABLE x
    (
      a NUMERIC(31, 2)
    )
    ;