Dokumen ini menunjukkan contoh skrip JavaScript dan Dataform core yang dapat Anda gunakan untuk membuat alur kerja SQL dalam Dataform.
Membuat tabel
Membuat tampilan dengan inti Dataform
Contoh kode berikut menunjukkan definisi tampilan bernama new_view
dalam file definitions/new_view.sqlx
:
config { type: "view" }
SELECT * FROM source_data
Membuat tampilan terwujud dengan inti Dataform
Contoh kode berikut menunjukkan definisi tampilan terwujud yang disebut
new_materialized_view
dalam file definitions/new_materialized_view.sqlx
:
config {
type: "view",
materialized: true
}
SELECT * FROM source_data
Membuat tabel dengan inti Dataform
Contoh kode berikut menunjukkan definisi tabel bernama new_table
dalam file definitions/new_table.sqlx
:
config { type: "table" }
SELECT * FROM source_data
Membuat tabel inkremental dengan Dataform core
Contoh kode berikut menunjukkan tabel inkremental yang
memproses baris tabel productiondb.logs
secara bertahap:
config { type: "incremental" }
SELECT timestamp, message FROM ${ref("productiondb", "logs")}
${when(incremental(), `WHERE timestamp > (SELECT MAX(timestamp) FROM ${self()})`) }
Menggunakan fungsi ref
untuk mereferensikan tabel dengan inti Dataform
Contoh kode berikut menunjukkan fungsi ref
yang digunakan untuk mereferensikan tabel source_data
dalam file definisi tabel definitions/new_table_with_ref.sqlx
:
config { type: "table" }
SELECT * FROM ${ref("source_data")}
Menambahkan dokumentasi ke tabel, tampilan, atau deklarasi dengan inti Dataform
Contoh kode berikut menunjukkan deskripsi tabel dan kolom dalam file definisi tabel 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")}
Mengonfigurasi tabel inkremental
Menambahkan baris tabel baru untuk tanggal baru di data sumber dengan Dataform core
Contoh kode berikut menunjukkan konfigurasi tabel inkremental
dalam file definitions/incremental_table.sqlx
. Dalam konfigurasi ini, Dataform menambahkan baris baru ke incremental_table
untuk setiap tanggal baru:
config { type: "incremental" }
SELECT date(timestamp) AS date, action
FROM weblogs.user_actions
${ when(incremental(), `WHERE timestamp > (select max(date) FROM ${self()})`)
Mengambil snapshot tabel secara berkala dengan inti Dataform
Contoh kode berikut menunjukkan konfigurasi tabel inkremental dalam
file definitions/snapshots_table.sqlx
. Dalam konfigurasi ini, Dataform membuat snapshots_table
dengan snapshot productiondb.customers
pada tanggal yang ditentukan:
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()})`) }
Membuat tabel bergulir 30 hari yang diperbarui secara bertahap dengan inti Dataform
Contoh kode berikut menunjukkan konfigurasi tabel inkremental dalam
file definitions/incremental_example.sqlx
. Dalam konfigurasi ini, Dataform membuat incremental_example
sementara yang diperbarui secara bertahap, dan menghapus tabel setelah 30 hari sejak pembuatannya:
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()})`) }
Membuat operasi SQL kustom
Menjalankan beberapa operasi SQL dalam file SQLX dengan Dataform core
Contoh kode berikut menunjukkan ;
yang digunakan untuk memisahkan beberapa operasi SQL
yang ditentukan dalam definitions/operations.sqlx
:
config { type: "operations" }
DELETE FROM datatable where country = 'GB';
DELETE FROM datatable where country = 'FR';
Menjalankan SQL kustom sebelum membuat tabel dengan Dataform core
Contoh kode berikut menunjukkan operasi SQL kustom yang ditentukan dalam
blok pre_operations
file definisi tabel
definitions/table_with_preops.sqlx
:
config {type: "table"}
SELECT * FROM ...
pre_operations {
INSERT INTO table ...
}
Menjalankan SQL kustom setelah membuat tabel dengan Dataform core
Contoh kode berikut menunjukkan operasi SQL kustom yang ditentukan dalam
blok post_operations
file definisi tabel
definitions/table_with_postops.sqlx
:
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";
}
Memvalidasi tabel
Menambahkan pernyataan ke tabel, tampilan, atau deklarasi dengan Dataform core
Contoh kode berikut menunjukkan pernyataan uniqueKey
, nonNull
, dan rowConditions
yang ditambahkan ke file definisi tabel 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 ...
Menambahkan pernyataan kustom dengan Dataform core
Contoh kode berikut menunjukkan pernyataan kustom dalam file definisi tabel yang memvalidasi apakah kolom a
, atau b
, atau c
dari source_data
adalah null
:
config { type: "assertion" }
SELECT
*
FROM
${ref("source_data")}
WHERE
a is null
or b is null
or c is null
Mengembangkan dengan JavaScript
Menggunakan variabel dan fungsi inline dengan JavaScript
Contoh kode berikut menunjukkan variabel foo
yang ditentukan dalam blok js
,
lalu digunakan secara inline dalam file SQLX:
js {
const foo = 1;
function bar(number){
return number+1;
}
}
SELECT
${foo} AS one,
${bar(foo)} AS two
Membuat satu tabel per negara dengan JavaScript
Contoh kode berikut menunjukkan penggunaan fungsi forEach
untuk menghasilkan
satu tabel per setiap negara yang ditentukan dalam countries
dalam
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
`
);
});
Mendeklarasikan beberapa sumber dalam satu file dengan JavaScript
Contoh kode berikut menunjukkan deklarasi beberapa sumber data dalam
file definitions/external_dependencies.js
:
declare({
schema: "stripe",
name: "charges"
});
declare({
schema: "shopify",
name: "orders"
});
declare({
schema: "salesforce",
name: "accounts"
});
Mendeklarasikan beberapa sumber dalam satu file menggunakan forEach
Contoh kode berikut menunjukkan deklarasi beberapa sumber data dengan
fungsi forEach
dalam file definitions/external_dependencies.js
:
["charges", "subscriptions", "line_items", "invoices"]
.forEach(source => declare({
schema: "stripe",
name: source
})
);
Menghapus informasi sensitif di semua tabel yang berisi PII dengan JavaScript
Contoh kode berikut menunjukkan fungsi dalam file definitions/delete_pii.js
yang menghapus informasi yang dipilih di semua tabel yang berisi
informasi identitas pribadi (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"]))
);
Menambahkan preOps
dan postOps
dengan JavaScript
Contoh kode berikut menunjukkan fungsi publish
yang digunakan untuk membuat kueri
dengan preOps
dan postOps
dalam
tabel definitions/pre_and_post_ops_example.js
:
publish("example")
.query(ctx => `SELECT * FROM ${ctx.ref("other_table")}`)
.preOps(ctx => `DELETE ${ctx.self()}`)
.postOps(ctx => `GRANT SELECT on ${ctx.self()} to role`)
Membuat tabel inkremental dengan JavaScript
Contoh kode berikut menunjukkan fungsi publish
yang digunakan untuk membuat tabel inkremental dalam 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()}`)}
`)
Mengisi ulang tabel harian dengan JavaScript
Contoh kode berikut menunjukkan pengisian ulang tabel yang diperbarui setiap hari dalam 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}'`)
);
Menggunakan kembali kode dengan penyertaan
Menggunakan variabel global dengan JavaScript
Contoh kode berikut menunjukkan definisi konstanta project_id
dan first_date
dalam includes/constants.js
:
const project_id = "project_id";
const first_date = "'1970-01-01'";
module.exports = {
project_id,
first_date
};
Contoh kode berikut menunjukkan konstanta first_date
yang direferensikan dalam
file definitions/new_table.sqlx
:
config {type: "table"}
SELECT * FROM source_table WHERE date > ${constants.first_date}
Membuat pemetaan negara dengan JavaScript
Contoh kode berikut menunjukkan fungsi kustom country_group
yang ditentukan dalam
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`;
Contoh kode berikut menunjukkan definisi tabel yang menggunakan fungsi country_group
dalam file definisi tabel 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
Contoh kode berikut menunjukkan kueri yang ditentukan dalam
definitions/new_table.sqlx
yang dikompilasi ke 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
Menghasilkan skrip SQL dengan fungsi JavaScript kustom
Contoh kode berikut menunjukkan fungsi kustom render_script
yang ditentukan di 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 };
Contoh kode berikut menunjukkan definisi tabel yang menggunakan fungsi render_script
dalam file definisi tabel 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"]
)}
Contoh kode berikut menunjukkan kueri yang ditentukan dalam
definitions/new_table.sqlx
yang dikompilasi ke 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