Script di esempio principali Dataform

Questo documento mostra esempi di script Dataform principali e JavaScript che che puoi usare per creare un flusso di lavoro SQL in Dataform.

Creazione di tabelle

Creazione di una vista con il core Dataform

Il seguente esempio di codice mostra la definizione di una vista denominata new_view nel file definitions/new_view.sqlx:

config { type: "view" }

SELECT * FROM source_data

Creazione di una vista materializzata con il core Dataform

Il seguente esempio di codice mostra la definizione di una vista materializzata denominata new_materialized_view nel file definitions/new_materialized_view.sqlx:

config {
  type: "view",
  materialized: true
}

SELECT * FROM source_data

Creazione di una tabella con il core Dataform

Il seguente esempio di codice mostra la definizione di una tabella denominata new_table nel file definitions/new_table.sqlx:

config { type: "table" }

SELECT * FROM source_data

Creazione di una tabella incrementale con il core Dataform

Il seguente esempio di codice mostra una tabella incrementale che elabora in modo incrementale le righe della tabella productiondb.logs:

config { type: "incremental" }

SELECT timestamp, message FROM ${ref("productiondb", "logs")}

${when(incremental(), `WHERE timestamp > (SELECT MAX(timestamp) FROM ${self()})`) }

Utilizzo della funzione ref per fare riferimento alle tabelle con il core Dataform

Il seguente esempio di codice mostra la funzione ref utilizzata per fare riferimento alla Tabella source_data in definitions/new_table_with_ref.sqlx file di definizione della tabella:

config { type: "table" }

SELECT * FROM ${ref("source_data")}

Aggiunta della documentazione a una tabella, una visualizzazione o una dichiarazione con il core Dataform

Il seguente esempio di codice mostra le descrizioni di tabelle e colonne nel file di definizione della tabella definitions/documented_table.sqlx:

config { type: "table",
         description: "This table is an example",
         columns:{
             user_name: "Name of the user",
             user_id: "ID of the user"
      }
  }

SELECT user_name, user_id FROM ${ref("source_data")}

Configurare le tabelle incrementali

Aggiunta di nuove righe di tabella per nuove date nei dati di origine con il core Dataform

Il seguente esempio di codice mostra una configurazione di una tabella incrementale nel file definitions/incremental_table.sqlx. In questa configurazione, Dataform aggiunge una nuova riga a incremental_table per ogni nuova data:

config { type: "incremental" }

SELECT date(timestamp) AS date, action
FROM weblogs.user_actions

${ when(incremental(), `WHERE timestamp > (select max(date) FROM ${self()})`)

Acquisizione periodica di uno snapshot di una tabella con il core Dataform

Il seguente esempio di codice mostra una configurazione di una tabella incrementale definitions/snapshots_table.sqlx. In questa configurazione, Dataform crea snapshots_table con uno snapshot di productiondb.customers alla data specificata:

config { type: "incremental" }

SELECT current_date() AS snapshot_date, customer_id, name, account_settings FROM productiondb.customers

${ when(incremental(), `WHERE snapshot_date > (SELECT max(snapshot_date) FROM ${self()})`) }

Creazione di una tabella continuativa di 30 giorni che si aggiorna in modo incrementale con il core Dataform

Il seguente esempio di codice mostra una configurazione di una tabella incrementale definitions/incremental_example.sqlx. In questa configurazione, Dataform crea un incremental_example temporaneo che si aggiorna in modo incrementale ed elimina la tabella dopo 30 giorni dalla sua creazione:

config {type: "incremental"}

post_operations {
  delete FROM ${self()} WHERE date < (date_add(Day, -30, CURRENT_DATE))
}

SELECT
 date(timestamp) AS date,
 order_id,
FROM source_table
  ${ when(incremental(), `WHERE timestamp > (SELECT max(date) FROM ${self()})`) }

Creazione di operazioni SQL personalizzate

Esecuzione di diverse operazioni SQL in un file SQLX con core Dataform

Il seguente esempio di codice mostra ; utilizzato per separare più operazioni SQL definita in definitions/operations.sqlx:

config { type: "operations" }

DELETE FROM datatable where country = 'GB';
DELETE FROM datatable where country = 'FR';

Esecuzione di un SQL personalizzato prima di creare una tabella con il core Dataform

Il seguente esempio di codice mostra un'operazione SQL personalizzata definita nel Blocco pre_operations di definitions/table_with_preops.sqlx file di definizione della tabella:

config {type: "table"}

SELECT * FROM ...

pre_operations {
  INSERT INTO table ...
}

Esecuzione di un codice SQL personalizzato dopo aver creato una tabella con il core Dataform

Il seguente esempio di codice mostra un'operazione SQL personalizzata definita nel Blocco post_operations di definitions/table_with_postops.sqlx file di definizione della tabella:

config {type: "table"}

SELECT * FROM ...

post_operations {
  GRANT `roles/bigquery.dataViewer`
  ON
  TABLE ${self()}
  TO "group:allusers@example.com", "user:otheruser@example.com"
}

Convalida delle tabelle

Aggiunta di asserzioni a una tabella, una visualizzazione o una dichiarazione con il core Dataform

Il seguente esempio di codice mostra uniqueKey, nonNull, e rowConditions asserzioni aggiunte a definitions/tested_table.sqlx file di definizione della tabella:

config {
  type: "table",
  assertions: {
    uniqueKey: ["user_id"],
    nonNull: ["user_id", "customer_id"],
    rowConditions: [
      'signup_date is null or signup_date > "2022-01-01"',
      'email like "%@%.%"'
    ]
  }
}
SELECT ...

Aggiunta di un'asserzione personalizzata con il core Dataform

Il seguente esempio di codice mostra un'asserzione personalizzata in un file di definizione di tabella che si convalida se le colonne a, b o c di source_data sono null:

config { type: "assertion" }

SELECT
  *
FROM
  ${ref("source_data")}
WHERE
  a is null
  or b is null
  or c is null

Sviluppare con JavaScript

Utilizzo di variabili e funzioni in linea con JavaScript

Il seguente esempio di codice mostra la variabile foo definita in un blocco js e poi utilizzato in linea in un file SQLX:

js {
 const foo = 1;
 function bar(number){
     return number+1;
 }
}

SELECT
 ${foo} AS one,
 ${bar(foo)} AS two

Generazione di una tabella per paese con JavaScript

Il seguente esempio di codice mostra l'utilizzo della funzione forEach per generare una tabella per ogni paese definito in countries nel File definitions/one_table_per_country.js:

const countries = ["GB", "US", "FR", "TH", "NG"];

countries.forEach(country => {
  publish("reporting_" + country)
    .dependencies(["source_table"])
    .query(
      ctx => `
      SELECT '${country}' AS country
      `
    );
});

Dichiarazione di più origini all'interno di un file con JavaScript

Il seguente esempio di codice mostra la dichiarazione di più origini dati nel File definitions/external_dependencies.js:

declare({
  schema: "stripe",
  name: "charges"
});

declare({
  schema: "shopify",
  name: "orders"
});

declare({
  schema: "salesforce",
  name: "accounts"
});

Dichiarazione di più origini all'interno di un file utilizzando forEach

Il seguente esempio di codice mostra la dichiarazione di più origini dati con il Funzione forEach nel file definitions/external_dependencies.js:

["charges", "subscriptions", "line_items", "invoices"]
  .forEach(source => declare({
      schema: "stripe",
      name: source
    })
  );

Eliminazione delle informazioni sensibili in tutte le tabelle contenenti PII con JavaScript

Il seguente esempio di codice mostra una funzione in definitions/delete_pii.js che elimina le informazioni selezionate in tutte le tabelle contenenti informazioni che consentono l'identificazione personale (PII):

const pii_tables = ["users", "customers", "leads"];
pii_tables.forEach(table =>
  operate(`gdpr_cleanup: ${table}`,
    ctx => `
      DELETE FROM raw_data.${table}
      WHERE user_id in (SELECT * FROM users_who_requested_deletion)`)
      .tags(["gdpr_deletion"]))
);

Aggiunta di preOps e postOps con JavaScript in corso...

Il seguente esempio di codice mostra la funzione publish utilizzata per creare una query con preOps e postOps in Tabella definitions/pre_and_post_ops_example.js:

publish("example")
  .preOps(ctx => `GRANT \`roles/bigquery.dataViewer\` ON TABLE ${ctx.ref("other_table")} TO "group:automation@example.com"`)
  .query(ctx => `SELECT * FROM ${ctx.ref("other_table")}`)
  .postOps(ctx => `REVOKE \`roles/bigquery.dataViewer\` ON TABLE ${ctx.ref("other_table")} TO "group:automation@example.com"`)

Creazione di tabelle incrementali con JavaScript

Il seguente esempio di codice mostra la funzione publish utilizzata per creare un tabella incrementale nel file definitions/incremental_example.js:

publish("incremental_example", {
  type: "incremental"
}).query(ctx => `
  SELECT * FROM ${ctx.ref("other_table")}
  ${ctx.when(ctx.incremental(),`WHERE timestamp > (SELECT MAX(date) FROM ${ctx.self()}`)}
`)

Backfill di una tabella giornaliera con JavaScript

Il seguente esempio di codice mostra il backfill di una tabella che si aggiorna quotidianamente nel File definitions/backfill_daily_data.js:

var getDateArray = function(start, end) {
  var startDate = new Date(start); //YYYY-MM-DD
  var endDate = new Date(end); //YYYY-MM-DD

  var arr = new Array();
  var dt = new Date(startDate);
  while (dt <= endDate) {
    arr.push(new Date(dt).toISOString().split("T")[0]);
    dt.setDate(dt.getDate() + 1);
  }
  return arr;
};

var dateArr = getDateArray("2020-03-01", "2020-04-01");

// step 1: create table
operate(`create table`, 'create table if not exists backfill_table (`fields`) `);
// step 2: insert into the table

dateArr.forEach((day, i) =>
  operate(`backfill ${day}`
   `insert into backfill_table select fields where day = '${day}'`)
);

Riutilizzare il codice con inclusioni

Utilizzo delle variabili globali con JavaScript

Il seguente esempio di codice mostra la definizione di project_id e first_date costanti in includes/constants.js:

const project_id = "project_id";
const first_date = "'1970-01-01'";
module.exports = {
  project_id,
  first_date
};

Il seguente esempio di codice mostra la costante first_date a cui viene fatto riferimento nel definitions/new_table.sqlx file:

config {type: "table"}

SELECT * FROM source_table WHERE date > ${constants.first_date}

Creazione di una mappatura di un paese con JavaScript

Il seguente esempio di codice mostra la funzione personalizzata country_group definita in il file includes/mapping.js:

function country_group(country){
  return `
  case
    when ${country} in ('US', 'CA') then 'NA'
    when ${country} in ('GB', 'FR', 'DE', 'IT', 'PL', 'SE') then 'EU'
    when ${country} in ('AU') then ${country}
    else 'Other'
  end`;
}

module.exports = {
   country_group
};

Il seguente esempio di codice mostra una definizione di tabella che utilizza il parametro Funzione country_group in definitions/new_table.sqlx file di definizione della tabella:

config { type: "table"}

SELECT
  country AS country,
  ${mapping.country_group("country")} AS country_group,
  device_type AS device_type,
  sum(revenue) AS revenue,
  sum(pageviews) AS pageviews,
  sum(sessions) AS sessions

FROM ${ref("source_table")}

GROUP BY 1, 2, 3

Il seguente esempio di codice mostra la query definita in definitions/new_table.sqlx compilato in SQL:

SELECT
  country AS country,
  case
    when country in ('US', 'CA') then 'NA'
    when country in ('GB', 'FR', 'DE', 'IT', 'PL', 'SE') then 'EU'
    when country in ('AU') then country
    else 'Other'
  end AS country_group,
  device_type AS device_type,
  sum(revenue) AS revenue,
  sum(pageviews) AS pageviews,
  sum(sessions) AS sessions

FROM "dataform"."source_table"

GROUP BY 1, 2, 3

Generazione di uno script SQL con una funzione JavaScript personalizzata

Il seguente esempio di codice mostra la funzione personalizzata render_script definita in includes/script_builder.js:

function render_script(table, dimensions, metrics) {
  return `
      SELECT
      ${dimensions.map(field => `${field} AS ${field}`).join(",")},
      ${metrics.map(field => `sum(${field}) AS ${field}`).join(",\n")}
      FROM ${table}
      GROUP BY ${dimensions.map((field, i) => `${i + 1}`).join(", ")}
    `;
}

module.exports = { render_script };

Il seguente esempio di codice mostra una definizione di tabella che utilizza il parametro Funzione render_script in definitions/new_table.sqlx file di definizione della tabella:

config {
    type: "table",
    tags: ["advanced", "hourly"],
    disabled: true
}

${script_builder.render_script(ref("source_table"),
                               ["country", "device_type"],
                               ["revenue", "pageviews", "sessions"]
                               )}

Il seguente esempio di codice mostra la query definita in definitions/new_table.sqlx compilato in SQL:

SELECT
  country AS country,
  device_type AS device_type,
  sum(revenue) AS revenue,
  sum(pageviews) AS pageviews,
  sum(sessions) AS sessions

FROM "dataform"."source_table"

GROUP BY 1, 2

Configurazioni di azioni

Caricamento dei file SQL con configurazioni delle azioni in corso...

Le configurazioni delle azioni facilitano il caricamento di file SQL puri. Puoi definire configurazioni di azioni in actions.yaml file nella cartella definitions.

Per ulteriori informazioni sui tipi di azioni disponibili e sulle configurazioni di azioni valide consulta la sezione Configurazioni Dataform Riferimento.

Il seguente esempio di codice mostra la definizione di una vista denominata new_view nel definitions/actions.yaml file:

actions:
  - view:
    filename: new_view.sql

Il file SQL definitions/new_view.sql, a cui fa riferimento il codice precedente contiene SQL puro:

SELECT * FROM source_data