Skrip contoh inti Dataform

Dokumen ini menunjukkan contoh skrip inti Dataform dan JavaScript yang dapat Anda gunakan untuk membuat alur kerja di Dataform.

Membuat tabel

Membuat tampilan dengan inti Dataform

Contoh kode berikut menunjukkan definisi tampilan yang disebut 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 Dataform core

Contoh kode berikut menunjukkan definisi tabel yang disebut 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 inkremental:

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 Dataform core

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 Dataform core

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 dalam 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 Dataform core

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()})`) }

Membangun tabel 30 hari bergulir yang diperbarui secara inkremental dengan Dataform core

Contoh kode berikut menunjukkan konfigurasi tabel inkremental dalam file definitions/incremental_example.sqlx. Dalam konfigurasi ini, Dataform membuat incremental_example sementara yang diperbarui secara inkremental, 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 dari 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 dari file definisi tabel definitions/table_with_postops.sqlx:

config {type: "table"}

SELECT * FROM ...

post_operations {
  GRANT `roles/bigquery.dataViewer`
  ON
  TABLE ${self()}
  TO "group:allusers@example.com", "user:otheruser@example.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 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 membuat satu tabel per negara yang ditentukan dalam countries di 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")
  .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"`)

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 include

Menggunakan variabel global dengan JavaScript

Contoh kode berikut menunjukkan definisi konstanta project_id dan first_date di 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 dirujuk 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`;
}

module.exports = {
   country_group
};

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

Membuat skrip SQL dengan fungsi JavaScript kustom

Contoh kode berikut menunjukkan fungsi kustom render_script yang ditentukan dalam 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

Konfigurasi tindakan

Memuat file SQL dengan konfigurasi tindakan

Konfigurasi tindakan memfasilitasi pemuatan file SQL murni. Anda dapat menentukan konfigurasi tindakan dalam file actions.yaml di folder definitions.

Untuk mengetahui informasi selengkapnya tentang jenis tindakan yang tersedia dan opsi konfigurasi tindakan yang valid, lihat Referensi Konfigurasi Dataform.

Contoh kode berikut menunjukkan definisi tampilan yang disebut new_view dalam file definitions/actions.yaml:

actions:
  - view:
    filename: new_view.sql

File SQL definitions/new_view.sql, yang dirujuk oleh contoh kode sebelumnya, berisi SQL murni:

SELECT * FROM source_data