In diesem Dokument finden Sie Beispiele für Dataform-Core- und JavaScript-Scripts, mit denen Sie einen SQL-Workflow in Dataform erstellen können.
Tabellen erstellen
Ansicht mit Dataform Core erstellen
Im folgenden Codebeispiel wird die Definition einer Ansicht namens new_view in der Datei definitions/new_view.sqlx gezeigt:
config { type: "view" }
SELECT * FROM source_data
Materialisierte Ansicht mit Dataform Core erstellen
Im folgenden Codebeispiel wird die Definition einer materialisierten Ansicht namens new_materialized_view in der Datei definitions/new_materialized_view.sqlx gezeigt:
config {
type: "view",
materialized: true
}
SELECT * FROM source_data
Tabelle mit Dataform Core erstellen
Im folgenden Codebeispiel wird die Definition einer Tabelle namens new_table in der Datei definitions/new_table.sqlx gezeigt:
config { type: "table" }
SELECT * FROM source_data
Inkrementelle Tabelle mit Dataform Core erstellen
Das folgende Codebeispiel zeigt eine Inkrementtabelle, in der Zeilen der Tabelle productiondb.logs inkrementell verarbeitet werden:
config { type: "incremental" }
SELECT timestamp, message FROM ${ref("productiondb", "logs")}
${when(incremental(), `WHERE timestamp > (SELECT MAX(timestamp) FROM ${self()})`) }
Mit der Funktion ref auf Tabellen mit Dataform-Core verweisen
Im folgenden Codebeispiel wird die Funktion ref verwendet, um auf die Tabelle source_data in der Datei definitions/new_table_with_ref.sqlx mit Tabellendefinitionen zu verweisen:
config { type: "table" }
SELECT * FROM ${ref("source_data")}
Tabellen, Ansichten oder Deklarationen mit Dataform Core dokumentieren
Im folgenden Codebeispiel sind die Beschreibungen der Tabellen und Spalten in der definitions/documented_table.sqlx-Tabellendefinitiondatei zu sehen:
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")}
Inkrementelle Tabellen konfigurieren
Mit Dataform Core neue Tabellenzeilen für neue Datumsangaben in den Quelldaten hinzufügen
Das folgende Codebeispiel zeigt eine Konfiguration einer inkrementellen Tabelle in der definitions/incremental_table.sqlx-Datei. In dieser Konfiguration fügt Dataform der Tabelle incremental_table für jedes neue Datum eine neue Zeile hinzu:
config { type: "incremental" }
SELECT date(timestamp) AS date, action
FROM weblogs.user_actions
${ when(incremental(), `WHERE timestamp > (select max(date) FROM ${self()})`)
Regelmäßig einen Snapshot einer Tabelle mit Dataform Core erstellen
Das folgende Codebeispiel zeigt eine Konfiguration einer inkrementellen Tabelle in der Datei definitions/snapshots_table.sqlx. In dieser Konfiguration erstellt Dataform snapshots_table mit einem Snapshot von productiondb.customers am angegebenen Datum:
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()})`) }
Eine rollierende 30-Tage-Tabelle erstellen, die mit Dataform Core inkrementell aktualisiert wird
Das folgende Codebeispiel zeigt eine Konfiguration einer inkrementellen Tabelle in der Datei definitions/incremental_example.sqlx. In dieser Konfiguration erstellt Dataform eine temporäre incremental_example, die inkrementell aktualisiert wird, und löscht die Tabelle nach 30 Tagen nach der Erstellung:
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()})`) }
Benutzerdefinierte SQL-Vorgänge erstellen
Mehrere SQL-Vorgänge in einer SQLX-Datei mit Dataform Core ausführen
Im folgenden Codebeispiel wird ; verwendet, um mehrere in definitions/operations.sqlx definierte SQL-Vorgänge zu trennen:
config { type: "operations" }
DELETE FROM datatable where country = 'GB';
DELETE FROM datatable where country = 'FR';
Benutzerdefinierte SQL-Abfragen ausführen, bevor eine Tabelle mit Dataform Core erstellt wird
Das folgende Codebeispiel zeigt einen benutzerdefinierten SQL-Vorgang, der im Block pre_operations der Datei definitions/table_with_preops.sqlx mit der Tabellendefinition definiert ist:
config {type: "table"}
SELECT * FROM ...
pre_operations {
INSERT INTO table ...
}
Benutzerdefinierte SQL-Abfragen nach dem Erstellen einer Tabelle mit Dataform Core ausführen
Das folgende Codebeispiel zeigt einen benutzerdefinierten SQL-Vorgang, der im Block post_operations der Datei definitions/table_with_postops.sqlx mit der Tabellendefinition definiert ist:
config {type: "table"}
SELECT * FROM ...
post_operations {
GRANT `roles/bigquery.dataViewer`
ON
TABLE ${self()}
TO "group:allusers@example.com", "user:otheruser@example.com"
}
Tabellen validieren
Tabellen, Ansichten oder Deklarationen mit Dataform Core Assertions hinzufügen
Im folgenden Codebeispiel sind uniqueKey-, nonNull- und rowConditions-Behauptungen zu sehen, die der Tabellendefinitiondatei definitions/tested_table.sqlx hinzugefügt wurden:
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 ...
Benutzerdefinierte Behauptung mit Dataform Core hinzufügen
Das folgende Codebeispiel zeigt eine benutzerdefinierte Bestätigung in einer Tabellendefinitiondatei, die prüft, ob die Spalten a, b oder c von source_data null sind:
config { type: "assertion" }
SELECT
*
FROM
${ref("source_data")}
WHERE
a is null
or b is null
or c is null
Entwicklung mit JavaScript
Inline-Variablen und ‑Funktionen mit JavaScript verwenden
Das folgende Codebeispiel zeigt die foo-Variable, die in einem js-Block definiert und dann inline in einer SQLX-Datei verwendet wird:
js {
const foo = 1;
function bar(number){
return number+1;
}
}
SELECT
${foo} AS one,
${bar(foo)} AS two
Mit JavaScript eine Tabelle pro Land generieren
Im folgenden Codebeispiel wird die Funktion forEach verwendet, um eine Tabelle für jedes Land zu generieren, das in countries in der Datei definitions/one_table_per_country.js definiert ist:
const countries = ["GB", "US", "FR", "TH", "NG"];
countries.forEach(country => {
publish("reporting_" + country)
.dependencies(["source_table"])
.query(
ctx => `
SELECT '${country}' AS country
`
);
});
Mehrere Quellen in einer Datei mit JavaScript deklarieren
Das folgende Codebeispiel zeigt die Deklaration mehrerer Datenquellen in der definitions/external_dependencies.js-Datei:
declare({
schema: "stripe",
name: "charges"
});
declare({
schema: "shopify",
name: "orders"
});
declare({
schema: "salesforce",
name: "accounts"
});
Mehrere Quellen in einer Datei mit forEach deklarieren
Im folgenden Codebeispiel wird die Deklaration mehrerer Datenquellen mit der Funktion forEach in der Datei definitions/external_dependencies.js gezeigt:
["charges", "subscriptions", "line_items", "invoices"]
.forEach(source => declare({
schema: "stripe",
name: source
})
);
Löschen sensibler Daten in allen Tabellen mit personenidentifizierbaren Informationen mit JavaScript
Im folgenden Codebeispiel wird eine Funktion in der Datei definitions/delete_pii.js gezeigt, mit der ausgewählte Informationen in allen Tabellen gelöscht werden, die personenidentifizierbare Informationen enthalten:
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"]))
);
preOps und postOps mit JavaScript hinzufügen
Im folgenden Codebeispiel wird die Funktion publish verwendet, um eine Abfrage mit preOps und postOps in der Tabelle definitions/pre_and_post_ops_example.js zu erstellen:
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"`)
Inkrementelle Tabellen mit JavaScript erstellen
Im folgenden Codebeispiel wird die Funktion publish verwendet, um eine inkrementelle Tabelle in der definitions/incremental_example.js-Datei zu erstellen:
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()}`)}
`)
Tägliche Tabelle mit JavaScript rückwirkend füllen
Im folgenden Codebeispiel wird das Backfilling einer Tabelle gezeigt, die täglich in der Datei definitions/backfill_daily_data.js aktualisiert wird:
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}'`)
);
Code mit Includes wiederverwenden
Globale Variablen mit JavaScript verwenden
Das folgende Codebeispiel zeigt die Definition von project_id- und first_date-Konstanten in der includes/constants.js:
const project_id = "project_id";
const first_date = "'1970-01-01'";
module.exports = {
project_id,
first_date
};
Das folgende Codebeispiel zeigt die Konstante first_date, auf die in der Datei definitions/new_table.sqlx verwiesen wird:
config {type: "table"}
SELECT * FROM source_table WHERE date > ${constants.first_date}
Länderzuordnung mit JavaScript erstellen
Das folgende Codebeispiel zeigt die benutzerdefinierte country_group-Funktion, die in der Datei includes/mapping.js definiert ist:
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
};
Das folgende Codebeispiel zeigt eine Tabellendefinition, in der die Funktion country_group in der Datei definitions/new_table.sqlx verwendet wird:
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
Das folgende Codebeispiel zeigt die in definitions/new_table.sqlx definierte Abfrage, die in SQL kompiliert wurde:
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
SQL-Script mit benutzerdefinierter JavaScript-Funktion generieren
Das folgende Codebeispiel zeigt die in includes/script_builder.js definierte benutzerdefinierte Funktion render_script:
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 };
Das folgende Codebeispiel zeigt eine Tabellendefinition, in der die Funktion render_script in der Datei definitions/new_table.sqlx verwendet wird:
config {
type: "table",
tags: ["advanced", "hourly"],
disabled: true
}
${script_builder.render_script(ref("source_table"),
["country", "device_type"],
["revenue", "pageviews", "sessions"]
)}
Das folgende Codebeispiel zeigt die in definitions/new_table.sqlx definierte Abfrage, die in SQL kompiliert wurde:
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
Aktionenkonfigurationen
SQL-Dateien mit Aktionskonfigurationen laden
Aktionskonfigurationen erleichtern das Laden reiner SQL-Dateien. Sie können Aktionskonfigurationen in actions.yaml-Dateien im Ordner definitions definieren.
Weitere Informationen zu verfügbaren Aktionstypen und gültigen Optionen für Aktionskonfigurationen finden Sie in der Referenz zu Dataform-Konfigurationen.
Im folgenden Codebeispiel wird die Definition einer Ansicht namens new_view in der Datei definitions/actions.yaml gezeigt:
actions:
- view:
filename: new_view.sql
Die SQL-Datei definitions/new_view.sql, auf die im vorherigen Codebeispiel verwiesen wird, enthält reinen SQL-Code:
SELECT * FROM source_data