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 di Dataform per la pubblicazione di una tabella o la scrittura di un'affermazione. Puoi definire comandi SQL personalizzati da eseguire in BigQuery da parte di Dataform.

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 di Dataform (roles/dataform.editor) nelle aree di lavoro. Per saperne di più sulla concessione dei ruoli, consulta Gestire l'accesso a progetti, cartelle e organizzazioni.

Potresti anche riuscire a ottenere le autorizzazioni richieste tramite i ruoli personalizzati o altri ruoli predefiniti.

Crea un file per la definizione di un'operazione personalizzata

Memorizza i file SQLX di definizione delle operazioni personalizzate nella directory definitions/. Per creare un nuovo file SQLX nella directory definitions/:

  1. Vai allo spazio 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 del file, inserisci il nome del file seguito da .sqlx dopo definitions/. Ad esempio: definitions/sample-operation.sqlx.

    I nomi dei 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 le operazioni SQL personalizzate in BigQuery senza modifiche.

Puoi definire più operazioni SQL personalizzate in un unico file SQLX. BigQuery esegue tutte le operazioni in un file nello stesso contesto e crea il codice 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 dell'operazione personalizzata.

  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 operations file:

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 di riferimento

Puoi creare manualmente una tabella in un'operazione SQL personalizzata a cui 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 per altri script:

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

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

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

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

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

    Sostituisci CUSTOM_SQL_QUERY con l'istruzione SQL SELECTdi definizione della tabella.

  4. (Facoltativo) Fai clic su Formato.

Fare riferimento a una tabella di output dell'operazione SQL personalizzata

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

Il seguente esempio di codice mostra un'operazione SQL personalizzata in un custom_SQL_operation_table.sqlx file che crea una tabella di riferimento chiamata 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 della tabella:

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

Creare una tabella vuota

Potresti creare una tabella vuota in modo che un altro servizio 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 di oggetti del flusso di lavoro SQL, ad esempio tabelle e viste, puoi aggiungere la proprietà hasOutput:true al blocco config dell'operazione della tabella vuota.

  • Per creare una tabella vuota, utilizza la funzione CREATE TABLE in un type: "operations" file 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 rimessa in discussione da altri oggetti del flusso di lavoro SQL:

config {
  type: "operations"
}

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

Passaggi successivi