Questo documento mostra esempi di script JavaScript e di Dataform di base che puoi utilizzare per creare un flusso di lavoro SQL in Dataform.
Creare tabelle
Creazione di una vista con Dataform Core
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 Dataform Core
Il seguente esempio di codice mostra la definizione di una vista materializzata chiamata
new_materialized_view
nel file definitions/new_materialized_view.sqlx
:
config {
type: "view",
materialized: true
}
SELECT * FROM source_data
Creazione di una tabella con Dataform Core
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 Dataform Core
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 Dataform Core
Il seguente esempio di codice mostra la funzione ref
utilizzata per fare riferimento alla tabella source_data
nel file di definizione della tabella definitions/new_table_with_ref.sqlx
:
config { type: "table" }
SELECT * FROM ${ref("source_data")}
Aggiunta di documentazione a una tabella, una vista o una dichiarazione con Dataform Core
Il seguente esempio di codice mostra le descrizioni delle tabelle e delle 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")}
Configurazione delle tabelle incrementali
Aggiunta di nuove righe di tabelle per nuove date nei dati di origine con Dataform Core
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()})`)
Acquisisci periodicamente uno snapshot di una tabella con Dataform Core
Il seguente esempio di codice mostra una configurazione di una tabella incrementale nel
definitions/snapshots_table.sqlx
file. 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 mobile di 30 giorni che si aggiorna in modo incrementale con il nucleo di Dataform
Il seguente esempio di codice mostra una configurazione di una tabella incrementale nel
definitions/incremental_example.sqlx
file. 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()})`) }
Creare operazioni SQL personalizzate
Eseguire diverse operazioni SQL in un file SQLX con il nucleo di Dataform
Il seguente esempio di codice mostra ;
utilizzato per separare più operazioni SQL
definite in definitions/operations.sqlx
:
config { type: "operations" }
DELETE FROM datatable where country = 'GB';
DELETE FROM datatable where country = 'FR';
Eseguire SQL personalizzato prima di creare una tabella con il nucleo di Dataform
Il seguente esempio di codice mostra un'operazione SQL personalizzata definita nel blocco pre_operations
del file di definizione della tabella definitions/table_with_preops.sqlx
:
config {type: "table"}
SELECT * FROM ...
pre_operations {
INSERT INTO table ...
}
Eseguire SQL personalizzato dopo aver creato una tabella con il nucleo di Dataform
Il seguente esempio di codice mostra un'operazione SQL personalizzata definita nel blocco post_operations
del file di definizione della tabella definitions/table_with_postops.sqlx
:
config {type: "table"}
SELECT * FROM ...
post_operations {
GRANT `roles/bigquery.dataViewer`
ON
TABLE ${self()}
TO "group:allusers@example.com", "user:otheruser@example.com"
}
Convalidare le tabelle
Aggiunta di asserzioni a una tabella, una vista o una dichiarazione con Dataform Core
Il seguente esempio di codice mostra le asserzioni uniqueKey
, nonNull
e rowConditions
aggiunte al file di definizione della tabella definitions/tested_table.sqlx
:
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'affermazione personalizzata con il nucleo di Dataform
Il seguente esempio di codice mostra un'affermazione personalizzata in un file di definizione della tabella
che 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
Sviluppo con JavaScript
Utilizzare variabili e funzioni in linea con JavaScript
Il seguente esempio di codice mostra la variabile foo
definita in un blocco js
e poi utilizzata in linea in un file SQLX:
js {
const foo = 1;
function bar(number){
return number+1;
}
}
SELECT
${foo} AS one,
${bar(foo)} AS two
Generare 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
definitions/external_dependencies.js
file:
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 la funzione forEach
nel file definitions/external_dependencies.js
:
["charges", "subscriptions", "line_items", "invoices"]
.forEach(source => declare({
schema: "stripe",
name: source
})
);
Eliminazione di informazioni sensibili in tutte le tabelle contenenti PII con JavaScript
Il seguente esempio di codice mostra una funzione nel file definitions/delete_pii.js
che elimina le informazioni selezionate in tutte le tabelle che contengono
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
Il seguente esempio di codice mostra la funzione publish
utilizzata per creare una query con preOps
e postOps
nella 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 una 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()}`)}
`)
Eseguire il backfill di una tabella giornaliera con JavaScript
Il seguente esempio di codice mostra il backfill di una tabella che si aggiorna quotidianamente nel
definitions/backfill_daily_data.js
file:
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}'`)
);
Riutilizzo del codice con i file include
Utilizzo di variabili globali con JavaScript
Il seguente esempio di codice mostra la definizione delle costanti project_id
e first_date
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 si fa riferimento nel
file definitions/new_table.sqlx
:
config {type: "table"}
SELECT * FROM source_table WHERE date > ${constants.first_date}
Creazione di una mappatura dei paesi con JavaScript
Il seguente esempio di codice mostra la funzione personalizzata country_group
definita nel
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 la funzione country_group
nel file di definizione della tabella definitions/new_table.sqlx
:
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
compilata 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 la funzione render_script
nel file di definizione della tabella definitions/new_table.sqlx
:
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
compilata 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 di file SQL con configurazioni di azioni
Le configurazioni di azioni facilitano il caricamento di file SQL puri. Puoi definire le configurazioni delle azioni
nei file actions.yaml
nella cartella definitions
.
Per ulteriori informazioni sui tipi di azioni disponibili e sulle opzioni di configurazione delle azioni valide, consulta la documentazione di riferimento per le configurazioni di Dataform.
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 precedente
esempio di codice, contiene SQL puro:
SELECT * FROM source_data