Dieses Dokument enthält Dataform Core- und JavaScript-Beispielskripts, mit denen Sie einen SQL-Workflow in Dataform erstellen können.
Tabellen erstellen
Ansicht mit Dataform Core erstellen
Das folgende Codebeispiel zeigt die Definition einer Ansicht namens new_view
in der Datei definitions/new_view.sqlx
:
config { type: "view" }
SELECT * FROM source_data
Materialisierte Ansicht mit Dataform Core erstellen
Das folgende Codebeispiel zeigt die Definition einer materialisierten Ansicht namens
new_materialized_view
in der Datei definitions/new_materialized_view.sqlx
:
config {
type: "view",
materialized: true
}
SELECT * FROM source_data
Tabelle mit Dataform Core erstellen
Das folgende Codebeispiel zeigt die Definition einer Tabelle mit dem Namen new_table
in der Datei definitions/new_table.sqlx
:
config { type: "table" }
SELECT * FROM source_data
Inkrementelle Tabelle mit Dataform Core erstellen
Das folgende Codebeispiel zeigt eine inkrementelle Tabelle,
verarbeitet Zeilen der Tabelle productiondb.logs
schrittweise:
config { type: "incremental" }
SELECT timestamp, message FROM ${ref("productiondb", "logs")}
${when(incremental(), `WHERE timestamp > (SELECT MAX(timestamp) FROM ${self()})`) }
ref
-Funktion zum Referenzieren von Tabellen mit Dataform Core verwenden
Das folgende Codebeispiel zeigt die ref
-Funktion, mit der auf das
Tabelle „source_data
“ im definitions/new_table_with_ref.sqlx
Tabellendefinitionsdatei:
config { type: "table" }
SELECT * FROM ${ref("source_data")}
Dokumentation zu einer Tabelle, Ansicht oder Deklaration mit Dataform Core hinzufügen
Im folgenden Codebeispiel sehen Sie Beschreibungen von Tabellen und Spalten
in der Tabellendefinitionsdatei 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")}
Inkrementelle Tabellen konfigurieren
Mit Dataform Core neue Tabellenzeilen für neue Datumsangaben in Quelldaten hinzufügen
Das folgende Codebeispiel zeigt eine Konfiguration einer inkrementellen Tabelle
in der Datei definitions/incremental_table.sqlx
. Bei dieser Konfiguration
Dataform hängt für jedes neue Datum eine neue Zeile an incremental_table
an:
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
definitions/snapshots_table.sqlx
-Datei. Bei dieser Konfiguration
Dataform erstellt 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 inkrementell mit Dataform Core aktualisiert wird
Das folgende Codebeispiel zeigt eine Konfiguration einer inkrementellen Tabelle in der
definitions/incremental_example.sqlx
-Datei. Bei dieser Konfiguration
Dataform erstellt eine temporäre incremental_example
, die aktualisiert wird
und löscht die Tabelle 30 Tage nach ihrer 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
Das folgende Codebeispiel zeigt, wie ;
zum Trennen mehrerer SQL-Vorgänge verwendet wird
definiert in definitions/operations.sqlx
:
config { type: "operations" }
DELETE FROM datatable where country = 'GB';
DELETE FROM datatable where country = 'FR';
Benutzerdefinierte SQL-Abfrage vor dem Erstellen einer Tabelle mit Dataform Core ausführen
Das folgende Codebeispiel zeigt einen benutzerdefinierten SQL-Vorgang, der in der
Block pre_operations
von definitions/table_with_preops.sqlx
Tabellendefinitionsdatei:
config {type: "table"}
SELECT * FROM ...
pre_operations {
INSERT INTO table ...
}
Benutzerdefinierte SQL-Abfrage ausführen, nachdem eine Tabelle mit Dataform Core erstellt wurde
Das folgende Codebeispiel zeigt einen benutzerdefinierten SQL-Vorgang, der in der
Block post_operations
von definitions/table_with_postops.sqlx
Tabellendefinitionsdatei:
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
Assertions zu einer Tabelle, Ansicht oder Deklaration mit Dataform Core hinzufügen
Das folgende Codebeispiel zeigt uniqueKey
, nonNull
,
und rowConditions
Assertions für definitions/tested_table.sqlx
hinzugefügt
Tabellendefinitionsdatei:
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 Assertion mit Dataform Core hinzufügen
Das folgende Codebeispiel zeigt eine benutzerdefinierte Assertion in einer Tabellendefinitionsdatei
Prüft, ob die Spalten a
, b
oder c
aus source_data
null
sind:
config { type: "assertion" }
SELECT
*
FROM
${ref("source_data")}
WHERE
a is null
or b is null
or c is null
Mit JavaScript entwickeln
Inline-Variablen und -Funktionen mit JavaScript verwenden
Das folgende Codebeispiel zeigt die in einem js
-Block definierte Variable foo
.
und dann inline in einer SQLX-Datei verwendet:
js {
const foo = 1;
function bar(number){
return number+1;
}
}
SELECT
${foo} AS one,
${bar(foo)} AS two
Eine Tabelle pro Land mit JavaScript generieren
Das folgende Codebeispiel zeigt die Verwendung der Funktion forEach
zum Generieren
eine Tabelle pro Land, definiert in countries
im
definitions/one_table_per_country.js
-Datei:
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
Im folgenden Codebeispiel wird 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 dem Parameter
forEach
-Funktion in der Datei definitions/external_dependencies.js
:
["charges", "subscriptions", "line_items", "invoices"]
.forEach(source => declare({
schema: "stripe",
name: source
})
);
Vertrauliche Informationen in allen Tabellen mit personenidentifizierbaren Informationen mit JavaScript löschen
Das folgende Codebeispiel zeigt eine Funktion in definitions/delete_pii.js
.
Datei, die ausgewählte Informationen in allen Tabellen löscht, die Folgendes enthalten:
personenidentifizierbare Informationen:
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
Das folgende Codebeispiel zeigt die publish
-Funktion zum Erstellen einer Abfrage
mit preOps
und postOps
in der
Tabelle 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"`)
Inkrementelle Tabellen mit JavaScript erstellen
Das folgende Codebeispiel zeigt die publish
-Funktion zum Erstellen eines
inkrementelle Tabelle in der Datei 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 einer täglichen Tabelle mit JavaScript
Im folgenden Codebeispiel wird für eine Tabelle, die täglich in der
definitions/backfill_daily_data.js
-Datei:
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 Einbindungen wiederverwenden
Globale Variablen mit JavaScript verwenden
Das folgende Codebeispiel zeigt die Definition von project_id
und first_date
Konstanten in 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 first_date
-Konstante, auf die im
definitions/new_table.sqlx
-Datei:
config {type: "table"}
SELECT * FROM source_table WHERE date > ${constants.first_date}
Länderzuordnung mit JavaScript erstellen
Im folgenden Codebeispiel sehen Sie die benutzerdefinierte Funktion country_group
, die in
die Datei 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
};
Das folgende Codebeispiel zeigt eine Tabellendefinition, bei der die Methode
Funktion country_group
in definitions/new_table.sqlx
Tabellendefinitionsdatei:
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
kompiliert 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
SQL-Script mit einer benutzerdefinierten JavaScript-Funktion generieren
Im folgenden Codebeispiel wird die benutzerdefinierte Funktion render_script
veranschaulicht:
definiert 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 };
Das folgende Codebeispiel zeigt eine Tabellendefinition, bei der die Methode
Funktion render_script
in definitions/new_table.sqlx
Tabellendefinitionsdatei:
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
kompiliert 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
Aktionskonfigurationen
SQL-Dateien mit Aktionskonfigurationen laden
Aktionskonfigurationen erleichtern das Laden reiner SQL-Dateien. Sie können Aktionskonfigurationen definieren
in actions.yaml
Dateien im Ordner definitions
.
Weitere Informationen zu verfügbaren Aktionstypen und gültigen Aktionskonfigurationen finden Sie auf der Seite Dataform Configs Referenz.
Im folgenden Codebeispiel wird die Definition einer Ansicht mit dem Namen new_view
im
definitions/actions.yaml
-Datei:
actions:
- view:
filename: new_view.sql
Die SQL-Datei definitions/new_view.sql
, auf die im vorherigen Code verwiesen wird
-Beispiel enthält reines SQL:
SELECT * FROM source_data