Aggiungi operazioni SQL personalizzate

Questo documento mostra come definire operazioni SQL personalizzate in un file SQLX dedicato.

Dataform può eseguire operazioni SQL personalizzate che non rientrano nel modello Dataform per la pubblicazione di una tabella o la scrittura di un'asserzione. Puoi definire comandi SQL personalizzati che Dataform deve eseguire in BigQuery.

Prima di iniziare

  1. Nella console Google Cloud, vai alla pagina Dataform.

    Vai alla pagina Dataform

  2. Seleziona o crea un repository.

  3. Seleziona o crea un'area di lavoro di sviluppo.

Ruoli obbligatori

Per ottenere le autorizzazioni necessarie per definire un'operazione SQL personalizzata, chiedi all'amministratore di concederti il ruolo IAM Editor Dataform (roles/dataform.editor) per le aree di lavoro. Per saperne di più sulla concessione dei ruoli, consulta Gestire l'accesso.

Potresti anche essere in grado di ottenere le autorizzazioni richieste tramite i ruoli personalizzati o altri ruoli predefiniti.

Crea un file per la definizione delle operazioni personalizzate

Archivia i file SQLX di definizione dell'operazione personalizzata nella directory definitions/. Per creare un nuovo file SQLX nella directory definitions/, procedi nel seguente modo:

  1. Vai all'area di lavoro di sviluppo.
  2. Nel riquadro File, accanto a definitions/, fai clic sul menu Altro .
  3. Fai clic su Crea file.

  4. Nel campo Aggiungi un percorso file, inserisci il nome del file seguito da .sqlx dopo definitions/. Ad esempio: definitions/sample-operation.sqlx.

    I nomi file possono includere solo numeri, lettere, trattini e trattini bassi.

  5. Fai clic su Crea file.

Definisci un'operazione SQL personalizzata

Puoi definire un'operazione SQL personalizzata in un file SQLX di type: operations. Puoi scrivere qualsiasi istruzione SQL di BigQuery in un file operations. Dataform esegue operazioni SQL personalizzate in BigQuery senza modifiche.

Puoi definire più operazioni SQL personalizzate in un file SQLX. BigQuery esegue tutte le operazioni in un file nello stesso contesto e crea l'SQL eseguito unendo tutte le operazioni con un punto e virgola ;.

Per definire più operazioni SQL personalizzate quando utilizzi il framework Dataform open source con un data warehouse diverso da BigQuery, separa le operazioni con ---.

Per definire un'operazione SQL personalizzata in un file SQLX dedicato:

  1. Nell'area di lavoro di sviluppo, seleziona il file SQLX per la definizione delle operazioni personalizzate.

  2. Nel file, inserisci il seguente snippet di codice:

    config { type: "operations" }

  3. All'esterno del blocco config, scrivi l'operazione SQL.

  4. (Facoltativo) Fai clic su Formato.

Il seguente esempio di codice mostra più operazioni SQL personalizzate definite in un file operations:

config { type: "operations" }

DELETE FROM dataset.table WHERE country = 'GB';

DELETE FROM dataset.table WHERE country = 'FR';

Il seguente esempio di codice mostra un'operazione SQL personalizzata che crea manualmente una vista:

config { type: "operations" }
CREATE OR REPLACE VIEW dataset.table AS (SELECT 1 AS TEST)

Crea una tabella di output con riferimento

Puoi creare manualmente una tabella in un'operazione SQL personalizzata a cui puoi fare riferimento in altri script. Per creare una tabella disponibile per altri script, devi dichiarare che l'operazione ha un output.

Per fare in modo che il nome della tabella di output corrisponda al nome del file operations, puoi utilizzare la funzione self nell'operazione CREATE.

Per creare una tabella in un'operazione personalizzata e renderla disponibile ad altri script:

  1. Nell'area di lavoro di sviluppo, seleziona il file SQLX per la definizione personalizzata dell'operazione.

  2. Nel file SQLX, inserisci il seguente snippet di codice:

    config {
 type: "operations",
 hasOutput: true
 }

  3. (Facoltativo) Per creare una corrispondenza tra il nome della tabella di output e il nome del file, scrivi un'operazione SQL CREATE con la funzione self nel seguente formato:

    CREATE OR REPLACE TABLE ${self()} AS (CUSTOM_SQL_QUERY)

    Sostituisci CUSTOM_SQL_QUERY con l'istruzione SELECTSQL di definizione della tabella.

  4. (Facoltativo) Fai clic su Formato.

Fai riferimento a una tabella di output di un'operazione SQL personalizzata

  • Per fare riferimento a una tabella di output di un'operazione SQL personalizzata in una definizione SQLX di una tabella diversa, inserisci il nome file della tabella di output nella funzione ref.

Il seguente esempio di codice mostra un'operazione SQL personalizzata in un file custom_SQL_operation_table.sqlx che crea una tabella di riferimento denominata custom_SQL_operation_table:

// filename is custom_SQL_operation_table.sqlx
config {
type: "operations",
hasOutput: true
}
CREATE OR REPLACE VIEW ${self()} AS (SELECT 1 AS TEST)

Il seguente esempio di codice mostra il riferimento a custom\_SQL\_operation\_table table in un file SQLX di definizione di tabella:

config { type: "table" }
SELECT * FROM ${ref("custom_SQL_operation_table")}

Crea una tabella vuota

Puoi creare una tabella vuota in modo che un servizio diverso possa completarla con i dati. Puoi creare una tabella vuota in un'operazione SQL personalizzata con la funzione CREATE TABLE. Per poter fare riferimento alla tabella vuota in altre definizioni degli oggetti del flusso di lavoro SQL, ad esempio tabelle e viste, puoi aggiungere la proprietà hasOutput:true al blocco config dell'operazione di tabella vuota.

  • Per creare una tabella vuota, utilizza la funzione CREATE TABLE in un file type: "operations" nel seguente formato:
config {
  type: "operations",
  hasOutput: true  // optional, lets you reference the empty table
}

CREATE TABLE ${self()} (

)

Il seguente esempio di codice mostra un'operazione SQL personalizzata che crea una tabella vuota con un numero intero e una colonna di stringhe. La tabella vuota creata non può essere utilizzata come riferimento da altri oggetti del flusso di lavoro SQL:

config {
  type: "operations"
}

CREATE TABLE ${self()} (
  x INT64,
  y STRING
)

Passaggi successivi