Contoh skrip inti dataform

{

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"]
                               )}