이 문서에서는 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`;
}
module.exports = {
country_group
};
다음 코드 샘플은 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"]
)}
다음 코드 샘플은 SQL로 컴파일된 definitions/new_table.sqlx
에 정의된 쿼리를 보여줍니다.
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 파일 로드를 용이하게 합니다. definitions
폴더의 actions.yaml
파일에서 작업 구성을 정의할 수 있습니다.
사용 가능한 작업 유형과 유효한 작업 구성 옵션에 대한 자세한 내용은 Dataform 구성 참조를 확인하세요.
다음 코드 샘플은 definitions/actions.yaml
파일에 있는 new_view
라는 뷰 정의를 보여줍니다.
actions:
- view:
filename: new_view.sql
앞의 코드 샘플에서 참조하는 definitions/new_view.sql
SQL 파일에는 순수 SQL이 포함되어 있습니다.
SELECT * FROM source_data