סקריפטים לדוגמה של Dataform Core

במסמך הזה מוצגות דוגמאות לסקריפטים של Dataform core ו-JavaScript שאפשר להשתמש בהם כדי ליצור תהליך עבודה ב-Dataform.

יצירת טבלאות

יצירת תצוגה באמצעות Dataform core

בדוגמת הקוד הבאה מוצגת הגדרה של תצוגה שנקראת new_view בקובץ definitions/new_view.sqlx:

config { type: "view" }

SELECT * FROM source_data

יצירת תצוגה מהותית באמצעות Dataform core

בדוגמת הקוד הבאה מוצגת ההגדרה של תצוגה חומרית בשם new_materialized_view בקובץ definitions/new_materialized_view.sqlx:

config {
  type: "view",
  materialized: true
}

SELECT * FROM source_data

יצירת טבלה באמצעות Dataform Core

בדוגמת קוד הבאה מוצגת הגדרה של טבלה בשם new_table בקובץ definitions/new_table.sqlx:

config { type: "table" }

SELECT * FROM source_data

יצירת טבלה מצטברת באמצעות Dataform Core

בדוגמת הקוד הבאה מוצגת טבלה מצטברת שמעבדת באופן מצטבר שורות של הטבלה productiondb.logs:

config { type: "incremental" }

SELECT timestamp, message FROM ${ref("productiondb", "logs")}

${when(incremental(), `WHERE timestamp > (SELECT MAX(timestamp) FROM ${self()})`) }

שימוש בפונקציה ref כדי להפנות לטבלאות באמצעות Dataform Core

בדוגמת הקוד הבאה מוצגת הפונקציה ref שמשמשת להפניה לטבלה source_data בקובץ הגדרת הטבלה definitions/new_table_with_ref.sqlx:

config { type: "table" }

SELECT * FROM ${ref("source_data")}

הוספת תיעוד לטבלה, לתצוגה או להצהרה באמצעות Dataform Core

בדוגמת הקוד הבאה מוצגים תיאורים של טבלה ועמודות בקובץ ההגדרה של הטבלה 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")}

הגדרה של טבלאות מצטברות

הוספת שורות חדשות בטבלה לתאריכים חדשים בנתוני המקור באמצעות Dataform core

בדוגמת הקוד הבאה מוצגת הגדרה של טבלה מצטברת בקובץ definitions/incremental_table.sqlx. בהגדרה הזו, אפליקציית Dataform מוסיפה שורה חדשה לטבלה incremental_table לכל תאריך חדש:

config { type: "incremental" }

SELECT date(timestamp) AS date, action
FROM weblogs.user_actions

${ when(incremental(), `WHERE timestamp > (select max(date) FROM ${self()})`)

יצירת תמונת מצב של טבלה באופן תקופתי באמצעות Dataform core

בדוגמת הקוד הבאה מוצגת הגדרה של טבלה מצטברת בקובץ definitions/snapshots_table.sqlx. בהגדרה הזו, Dataform יוצרת את snapshots_table עם תמונת מצב של productiondb.customers בתאריך שצוין:

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

יצירת טבלה של 30 ימים שמתעדכנת בהדרגה באמצעות Dataform core

בדוגמת הקוד הבאה מוצגת הגדרה של טבלה מצטברת בקובץ definitions/incremental_example.sqlx. בהגדרה הזו,‏ Dataform יוצרת טבלה זמנית incremental_example שמתעדכנת באופן מצטבר, ומוחקת את הטבלה אחרי 30 יום מהיצירה שלה:

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

יצירת פעולות SQL בהתאמה אישית

הרצת כמה פעולות SQL בקובץ SQLX באמצעות Dataform Core

בדוגמת הקוד הבאה אפשר לראות את השימוש ב-; כדי להפריד בין כמה פעולות SQL שמוגדרות ב-definitions/operations.sqlx:

config { type: "operations" }

DELETE FROM datatable where country = 'GB';
DELETE FROM datatable where country = 'FR';

הרצת SQL בהתאמה אישית לפני יצירת טבלה באמצעות Dataform core

בדוגמת קוד הבאה מוצגת פעולת SQL מותאמת אישית שמוגדרת בבלוק pre_operations של קובץ הגדרת הטבלה definitions/table_with_preops.sqlx:

config {type: "table"}

SELECT * FROM ...

pre_operations {
  INSERT INTO table ...
}

הרצת SQL בהתאמה אישית אחרי יצירת טבלה באמצעות Dataform core

בדוגמת קוד הבאה מוצגת פעולת SQL מותאמת אישית שמוגדרת בבלוק post_operations של קובץ הגדרת הטבלה 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"
}

אימות טבלאות

הוספת הצהרות לטבלה, לתצוגה או להצהרה באמצעות Dataform Core

בדוגמת הקוד הבאה מוצגות טענות uniqueKey, nonNull ו-rowConditions שנוספו לקובץ הגדרת הטבלה 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 ...

הוספת טענה מותאמת אישית באמצעות Dataform Core

דוגמת הקוד הבאה מציגה טענת נכוֹנוּת בהתאמה אישית בקובץ הגדרת טבלה שבודקת אם העמודות a, b או c מתוך source_data הן null:

config { type: "assertion" }

SELECT
  *
FROM
  ${ref("source_data")}
WHERE
  a is null
  or b is null
  or c is null

פיתוח באמצעות JavaScript

שימוש במשתנים ובפונקציות מוטבעים עם JavaScript

בדוגמת הקוד הבאה אפשר לראות את המשתנה foo שמוגדר בבלוק js ואז נעשה בו שימוש בשורה בקובץ SQLX:

js {
 const foo = 1;
 function bar(number){
     return number+1;
 }
}

SELECT
 ${foo} AS one,
 ${bar(foo)} AS two

יצירת טבלה אחת לכל מדינה באמצעות JavaScript

בדוגמת הקוד הבאה מוצג שימוש בפונקציה forEach כדי ליצור טבלה אחת לכל מדינה שמוגדרת ב-countries בקובץ 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
      `
    );
});

הצהרה על כמה מקורות בקובץ אחד באמצעות JavaScript

בדוגמת הקוד הבאה מוצגת הצהרה על כמה מקורות נתונים בקובץ definitions/external_dependencies.js:

declare({
  schema: "stripe",
  name: "charges"
});

declare({
  schema: "shopify",
  name: "orders"
});

declare({
  schema: "salesforce",
  name: "accounts"
});

הצהרה על כמה מקורות בקובץ אחד באמצעות forEach

בדוגמת הקוד הבאה מוצגת הצהרה על כמה מקורות נתונים באמצעות הפונקציה forEach בקובץ definitions/external_dependencies.js:

["charges", "subscriptions", "line_items", "invoices"]
  .forEach(source => declare({
      schema: "stripe",
      name: source
    })
  );

מחיקת מידע רגיש בכל הטבלאות שמכילות PII באמצעות JavaScript

בדוגמת הקוד הבאה מוצגת פונקציה בקובץ definitions/delete_pii.js שמוחקת מידע נבחר בכל הטבלאות שמכילות פרטים אישיים מזהים (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"]))
);

הוספה של preOps ושל postOps באמצעות JavaScript

בדוגמת הקוד הבאה מוצגת הפונקציה publish שמשמשת ליצירת שאילתה עם preOps ו-postOps בטבלה 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"`)

יצירת טבלאות מצטברות באמצעות JavaScript

בדוגמת הקוד הבאה מוצגת הפונקציה publish שמשמשת ליצירת טבלה מצטברת בקובץ 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()}`)}
`)

מילוי חוזר של טבלה יומית באמצעות JavaScript

בדוגמה הבאה של קוד מוצג מילוי חוסרים בטבלה שמתעדכנת מדי יום בקובץ 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}'`)
);

שימוש חוזר בקוד באמצעות include

שימוש במשתנים גלובליים עם JavaScript

בדוגמת הקוד הבאה מוצגת ההגדרה של הקבועים project_id ו-first_date ב-includes/constants.js:

const project_id = "project_id";
const first_date = "'1970-01-01'";
module.exports = {
  project_id,
  first_date
};

בדוגמת הקוד הבאה מוצג הקבוע first_date שמופיע בקובץ definitions/new_table.sqlx:

config {type: "table"}

SELECT * FROM source_table WHERE date > ${constants.first_date}

יצירת מיפוי של מדינה באמצעות JavaScript

בדוגמת הקוד הבאה מוצגת הפונקציה המותאמת אישית country_group שמוגדרת בקובץ 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
};

בדוגמת קוד הבאה מוצגת הגדרת טבלה שמשתמשת בפונקציה country_group בקובץ ההגדרה של הטבלה 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

בדוגמת הקוד הבאה אפשר לראות את השאילתה שמוגדרת ב-definitions/new_table.sqlx אחרי הידור ל-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 באמצעות פונקציית JavaScript בהתאמה אישית

בדוגמת הקוד הבאה מוצגת הפונקציה המותאמת אישית render_script שמוגדרת ב-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 };

בדוגמת קוד הבאה מוצגת הגדרת טבלה שמשתמשת בפונקציה render_script בקובץ ההגדרה של הטבלה 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"]
                               )}

בדוגמת הקוד הבאה אפשר לראות את השאילתה שמוגדרת ב-definitions/new_table.sqlx אחרי הידור ל-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

הגדרות של פעולות

טעינת קובצי SQL עם הגדרות פעולה

הגדרות של פעולות מאפשרות לטעון קובצי SQL טהורים. אפשר להגדיר הגדרות של פעולות בקובצי actions.yaml בתיקייה definitions.

מידע נוסף על סוגי הפעולות הזמינים ועל אפשרויות ההגדרות התקינות של הפעולות זמין במאמר הפניה להגדרות של Dataform.

בדוגמת הקוד הבאה מוצגת הגדרה של תצוגה שנקראת new_view בקובץ definitions/actions.yaml:

actions:
  - view:
    filename: new_view.sql

קובץ ה-SQL‏ definitions/new_view.sql, שאליו מתייחס קטע הקוד הקודם, מכיל SQL טהור:

SELECT * FROM source_data