Dieses Dokument enthält Beispiele für Dataform Core- und JavaScript-Skripts, 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 mit dem Namen 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 mit dem Namen 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, die Zeilen der Tabelle productiondb.logs
schrittweise verarbeitet:
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 Funktion ref
, mit der auf die Tabelle source_data
in der Tabellendefinitionsdatei definitions/new_table_with_ref.sqlx
verwiesen wird:
config { type: "table" }
SELECT * FROM ${ref("source_data")}
Dokumentation zu einer Tabelle, Ansicht oder Deklaration mit Dataform Core hinzufügen
Das folgende Codebeispiel enthält Tabellen- und Spaltenbeschreibungen 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
Neue Tabellenzeilen für neue Datumsangaben in Quelldaten mit Dataform Core hinzufügen
Das folgende Codebeispiel zeigt die Konfiguration einer inkrementellen Tabelle in der Datei definitions/incremental_table.sqlx
. Bei dieser Konfiguration hängt Dataform 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()})`)
Snapshot einer Tabelle regelmäßig mit Dataform Core erstellen
Das folgende Codebeispiel zeigt die 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 schrittweise aktualisiert wird
Das folgende Codebeispiel zeigt die Konfiguration einer inkrementellen Tabelle in der Datei definitions/incremental_example.sqlx
. In dieser Konfiguration erstellt Dataform eine temporäre incremental_example
, die schrittweise 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 in definitions/operations.sqlx
definierter SQL-Vorgänge verwendet wird:
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 im Block pre_operations
der Tabellendefinitionsdatei definitions/table_with_preops.sqlx
definiert ist:
config {type: "table"}
SELECT * FROM ...
pre_operations {
INSERT INTO table ...
}
Benutzerdefinierte SQL-Abfrage nach dem Erstellen einer Tabelle mit Dataform Core ausführen
Das folgende Codebeispiel zeigt einen benutzerdefinierten SQL-Vorgang, der im Block post_operations
der Tabellendefinitionsdatei definitions/table_with_postops.sqlx
definiert ist:
config {type: "table"}
SELECT * FROM ...
post_operations {
GRANT SELECT on ${self()} to group "allusers@examplecompany.com";
GRANT SELECT on ${self()} to group "allotherusers@examplecompany.com";
}
Tabellen validieren
Assertions zu einer Tabelle, Ansicht oder Deklaration mit Dataform Core hinzufügen
Das folgende Codebeispiel zeigt die Assertions uniqueKey
, nonNull
und rowConditions
, die der Tabellendefinitionsdatei 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 Assertion mit Dataform Core hinzufügen
Das folgende Codebeispiel zeigt eine benutzerdefinierte Assertion in einer Tabellendefinitionsdatei, die 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 Variable foo
, 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
Eine Tabelle pro Land mit JavaScript generieren
Das folgende Codebeispiel zeigt die Verwendung der Funktion forEach
, um eine Tabelle pro Land zu generieren, das in countries
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 innerhalb einer Datei mit JavaScript deklarieren
Im folgenden Codebeispiel wird die Deklaration mehrerer Datenquellen in der Datei definitions/external_dependencies.js
veranschaulicht:
declare({
schema: "stripe",
name: "charges"
});
declare({
schema: "shopify",
name: "orders"
});
declare({
schema: "salesforce",
name: "accounts"
});
Mehrere Quellen innerhalb einer Datei mit forEach
deklarieren
Im folgenden Codebeispiel wird die Deklaration mehrerer Datenquellen mit der Funktion forEach
in der Datei definitions/external_dependencies.js
veranschaulicht:
["charges", "subscriptions", "line_items", "invoices"]
.forEach(source => declare({
schema: "stripe",
name: source
})
);
Vertrauliche Informationen aus allen Tabellen mit personenidentifizierbaren Informationen mit JavaScript löschen
Das folgende Codebeispiel zeigt eine Funktion in der Datei definitions/delete_pii.js
, 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
Das folgende Codebeispiel zeigt die Funktion publish
, die zum Erstellen einer Abfrage mit preOps
und postOps
in der Tabelle definitions/pre_and_post_ops_example.js
verwendet wird:
publish("example")
.query(ctx => `SELECT * FROM ${ctx.ref("other_table")}`)
.preOps(ctx => `DELETE ${ctx.self()}`)
.postOps(ctx => `GRANT SELECT on ${ctx.self()} to role`)
Inkrementelle Tabellen mit JavaScript erstellen
Das folgende Codebeispiel zeigt die Funktion publish
, die zum Erstellen einer inkrementellen Tabelle in der Datei definitions/incremental_example.js
verwendet wird:
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 Tagestabelle mit JavaScript
Das folgende Codebeispiel zeigt, wie Sie einen Backfill für eine Tabelle durchführen, 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“-Elementen wiederverwenden
Globale Variablen mit JavaScript verwenden
Das folgende Codebeispiel zeigt die Definition der Konstanten project_id
und first_date
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 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 Funktion country_group
, 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`;
Das folgende Codebeispiel zeigt eine Tabellendefinition, die die Funktion country_group
in der Tabellendefinitionsdatei definitions/new_table.sqlx
verwendet:
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, 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-Skript mit einer benutzerdefinierten 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, die die Funktion render_script
in der Tabellendefinitionsdatei definitions/new_table.sqlx
verwendet:
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, 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