Dataform Core 샘플 스크립트

{

이 문서에서는 Dataform에서 SQL 워크플로를 만드는 데 사용할 수 있는 Dataform 코어 및 JavaScript 스크립트의 예시를 보여줍니다.

테이블 만들기

Dataform 코어로 뷰 만들기

다음 코드 샘플은 definitions/new_view.sqlx 파일에 있는 new_view라는 뷰 정의를 보여줍니다.

config { type: "view" }

SELECT * FROM source_data

Dataform 코어로 구체화된 뷰 만들기

다음 코드 샘플은 definitions/new_materialized_view.sqlx 파일에 있는 new_materialized_view이라는 구체화된 뷰의 정의를 보여줍니다.

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

SELECT * FROM source_data

Dataform 코어로 테이블 만들기

다음 코드 샘플은 definitions/new_table.sqlx 파일에 있는 new_table라는 테이블의 정의를 보여줍니다.

config { type: "table" }

SELECT * FROM source_data

Dataform 코어로 증분 테이블 만들기

다음 코드 샘플은 productiondb.logs 테이블의 행을 점진적으로 처리하는 증분 테이블을 보여줍니다.

config { type: "incremental" }

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

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

`ref` 함수를 사용하여 Dataform 코어로 테이블 참조

다음 코드 샘플은 definitions/new_table_with_ref.sqlx 테이블 정의 파일에서 source_data 테이블을 참조하는 데 사용되는 ref 함수를 보여줍니다.

config { type: "table" }

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

Dataform 코어로 테이블, 뷰 또는 선언에 문서 추가

다음 코드 샘플은 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 코어로 소스 데이터에서 새 날짜를 위한 새 테이블 행 추가

다음 코드 샘플에서는 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 코어로 주기적으로 테이블 스냅샷 만들기

다음 코드 샘플에서는 definitions/snapshots_table.sqlx 파일의 증분 테이블 구성을 보여줍니다. 이 구성에서 Dataform은 지정된 날짜에 productiondb.customers 스냅샷을 사용하여 snapshots_table을 만듭니다.

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

Dataform 코어로 점진적으로 업데이트되는 30일 순환 테이블 빌드

다음 코드 샘플에서는 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 작업 만들기

Dataform 코어로 SQLX 파일에서 여러 SQL 작업 실행

다음 코드 샘플은 definitions/operations.sqlx에 정의된 여러 SQL 작업을 구분하는 데 사용되는 ;을 보여줍니다.

config { type: "operations" }

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

Dataform 코어로 테이블을 만들기 전에 커스텀 SQL 실행

다음 코드 샘플은 definitions/table_with_preops.sqlx 테이블 정의 파일의 pre_operations 블록에 정의된 커스텀 SQL 작업을 보여줍니다.

config {type: "table"}

SELECT * FROM ...

pre_operations {
  INSERT INTO table ...
}

Dataform 코어로 테이블을 만든 후 커스텀 SQL 실행

다음 코드 샘플은 definitions/table_with_postops.sqlx 테이블 정의 파일의 post_operations 블록에 정의된 커스텀 SQL 작업을 보여줍니다.

config {type: "table"}

SELECT * FROM ...

post_operations {
  GRANT `roles/bigquery.dataViewer`
  ON
  TABLE ${self()}
  TO "group:allusers@example.com", "user:otheruser@example.com"
}

테이블 검증

Dataform 코어로 테이블, 뷰 또는 선언에 어설션 추가

다음 코드 샘플은 definitions/tested_table.sqlx 테이블 정의 파일에 추가된 uniqueKey, nonNull, rowConditions 어설션을 보여줍니다.

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 코어로 커스텀 어설션 추가

다음 코드 샘플은 source_data의 a, b 또는 c 열이 null인지 검증하는 테이블 정의 파일의 커스텀 어설션을 보여줍니다.

config { type: "assertion" }

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

JavaScript로 개발

JavaScript에서 인라인 변수 및 함수 사용

다음 코드 샘플은 js 블록에서 정의된 다음 SQLX 파일에서 인라인으로 사용되는 foo 환경 변수를 보여줍니다.

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

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

JavaScript를 사용하여 국가당 하나의 테이블 만들기

다음 코드 샘플은 forEach 함수를 사용하여 definitions/one_table_per_country.js 파일에서 countries에 정의된 각 국가당 하나의 테이블을 생성하는 방법을 보여줍니다.

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`를 사용하여 한 파일 내에서 여러 소스 선언

다음 코드 샘플은 definitions/external_dependencies.js 파일에 forEach 함수가 있는 여러 데이터 소스의 선언을 보여줍니다.

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

JavaScript로 PII가 포함된 모든 테이블에서 민감한 정보 삭제

다음 코드 샘플은 개인 식별 정보(PII)가 포함된 모든 테이블에서 선택한 정보를 삭제하는 definitions/delete_pii.js 파일의 함수를 보여줍니다.

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

JavaScript로 `preOps` 및 `postOps` 추가

다음 코드 샘플은 definitions/pre_and_post_ops_example.js 테이블에 preOps 및 postOps를 사용하여 쿼리를 만드는 데 사용되는 publish 함수를 보여줍니다.

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로 증분 테이블 만들기

다음 코드 샘플에서는 definitions/incremental_example.js 파일에 증분 테이블을 만드는 데 사용되는 publish 함수를 보여줍니다.

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}'`)
);

includes로 코드 재사용

JavaScript에서 전역 변수 사용

다음 코드 샘플은 includes/constants.js의 project_id 및 first_date 상수 정의를 보여줍니다.

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

다음 코드 샘플은 definitions/new_table.sqlx 파일에서 참조되는 first_date 상수를 보여줍니다.

config {type: "table"}

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

JavaScript로 국가 매핑 만들기

다음 코드 샘플은 includes/mapping.js 파일에 정의된 country_group 커스텀 함수를 보여줍니다.

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`;

다음 코드 샘플은 definitions/new_table.sqlx 테이블 정의 파일에 country_group 함수를 사용하는 테이블 정의를 보여줍니다.

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

다음 코드 샘플은 SQL로 컴파일된 definitions/new_table.sqlx에 정의된 쿼리를 보여줍니다.

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

커스텀 JavaScript 함수로 SQL 스크립트 생성

다음 코드 샘플은 includes/script_builder.js에 정의된 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 };

다음 코드 샘플은 definitions/new_table.sqlx 테이블 정의 파일에 render_script 함수를 사용하는 테이블 정의를 보여줍니다.

config {
    type: "table",
    tags: ["advanced", "hourly"],
    disabled: true
}

${script_builder.render_script(ref("source_table"),
                               ["country", "device_type"],
                               ["revenue", "pageviews", "sessions"]
                               )}