This document shows you how to create JavaScript includes to reuse code in Dataform.
In the includes/
folder of your repository, you can define JavaScript
includes. Includes are global constants or functions that you can reuse across
your entire repository.
Before you begin
In the Google Cloud console, go to the Dataform page.
Select or create a Dataform repository.
Select or create a Dataform development workspace.
Required roles
To get the permissions that you need to reuse code with JavaScript includes,
ask your administrator to grant you the
Dataform Editor (roles/dataform.editor
) IAM role on workspaces.
For more information about granting roles, see Manage access to projects, folders, and organizations.
You might also be able to get the required permissions through custom roles or other predefined roles.
Create a JavaScript file for includes in Dataform
To create a new JavaScript file in the includes/
directory, follow these
steps:
In the Files pane, next to
includes/
, click the More menu.Click Create file.
In the Create new file pane, do the following:
In the Add a file path field, after
includes/
, enter the name of the file followed by.js
. For example,includes/constants.js
.Filenames can only include numbers, letters, hyphens, and underscores.
Click Create file.
Create a JavaScript constant
To create a constant that you can reuse across your project, follow these steps:
Go to your development workspace.
In the Files pane, expand
includes/
.Create or select a JavaScript file with the
.js
extension.In the file, enter the following code snippet:
const CONSTANT_NAME = CONSTANT_VALUE; module.exports = { CONSTANT_NAME };
Replace the following:
- CONSTANT_NAME: the name of your constant
- CONSTANT_VALUE: the value of your constant
Optional: Click Format.
The following code sample defines the PROJECT_ID
constant in the
includes/constants.js
file:
// filename is includes/constants.js
const PROJECT_ID = "my_project_name";
module.exports = { PROJECT_ID };
The following code sample references the PROJECT_ID
constant in a table
definition query in a SQLX file:
config { type: "table" }
SELECT * FROM ${constants.PROJECT_ID}.my_schema_name.my_table_name
The following code sample shows the above Dataform core table definition query compiled into SQL:
SELECT * FROM my_project_name.my_schema_name.my_table_name
Create a custom JavaScript function
To create a custom JavaScript function that you can reuse across your project, follow these steps:
Go to your development workspace.
In the Files pane, expand
includes/
.Create or select a JavaScript file with the
.js
extension.In the file, write your custom JavaScript function.
In the file, enter the following code snippet:
module.exports = { FUNCTION_NAME }
Replace
FUNCTION_NAME
with the name of your function.Optional: Click Format.
The following code sample shows a custom JavaScript function, called
renderScript
, in the includes/functions.js
file, that generates a SQL
script:
function renderScript(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 = { renderScript };
The following code sample shows the use of the custom renderScript
JavaScript
function in a Dataform core table definition query:
config {
type: "table",
tags: ["advanced", "hourly"],
disabled: true
}
${functions.renderScript(ref("source_table"),
["country", "device_type"],
["revenue", "pageviews", "sessions"]
)}
The following code sample shows the above Dataform core table definition query compiled into 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
Reference an include in a SQLX file
You can reference any include function or constant inside a SQLX file. The
syntax for referencing includes depends on the location of the include file.
A top-level includes file is located directly in the includes/
directory.
A nested includes file is located in a subdirectory of includes/
.
Reference a top-level include in a SQLX file
- To reference a top-level include function or constant in a
Dataform core query, enter the include definition file name without
the
.js
extension followed by the name of the exported object.
The following code sample references the firstDate
constant, defined in the
includes/constants.js
file, in a table definition SQLX file:
config {type: "table"}
select * from source_table where date > ${constants.firstDate}
Reference a nested include in a SQLX file
To reference includes located in subdirectories of definitions
, import
includes by using the JavaScript require
function and a js {}
block.
To reference a nested include with the require
JavaScript function,
follow these steps:
Go to your development workspace.
In the Files pane, expand
definitions/
.Select a SQLX file.
In the
config
block, enter the following code snippet:js { var { VARIABLE_NAME } = require("SUBDIRECTORY_INCLUDE"); }
Replace the following:
- VARIABLE_NAME: the name of the constant or function that you want to import
- SUBDIRECTORY_INCLUDE: the path
of the nested
includes
file
Optional: Click Format.
The following code sample references the firstDate
constant, defined in the
nested includes/allConstants/constants.js
file, in a table definition SQLX
file:
config {type: "table"}
js {
var { firstDate } = require("includes/allConstants/constants");
}
select * from source_table where date > ${firstDate}
Use a JavaScript include function with the Dataform core ref
function
To use a JavaScript include function with the Dataform core ref
function, you need to pass ref
as an argument of the JavaScript include
function inside a SQLX file.
The following code sample shows the includes/script_builder.js
file with
the renderScript
JavaScript function that aggregates metrics by using SUM
and groups then by dimension:
function renderScript(table, dimensions, metrics) {
return `
SELECT
${dimensions.map((field) => `${field} AS ${field}`).join(",\\n")},
${metrics.map((field) => `SUM(${field}) AS ${field}`).join(",\\n")}
FROM ${table}
GROUP BY ${dimensions.map((field, i) => `${i + 1}`).join(", ")}
`;
}
module.exports = { renderScript };
The following code sample shows the renderScript
JavaScript function used in
the definitions/stats_per_country_and_device.sqlx
file with the
Dataform core ref
function passed as an argument:
${script_builder.renderScript(
ref("source_table"),
["country", "device_type"],
["revenue", "pageviews", "sessions"])}
The following code sample shows the
definitions/stats_per_country_and_device.sqlx
query compiled to SQL:
SELECT country AS country,
device_type AS device_type,
SUM(revenue) AS revenue,
SUM(pageviews) AS pageviews,
SUM(sessions) AS sessions
FROM my_schema.source_table
GROUP BY 1, 2
For more information about the Dataform core ref
function, see
Overview of Dataform core.
What's next
To learn how to reference includes in JavaScript files, see Create SQL workflows with JavaScript.
To learn about reusing code with and block and inline JavaScript inside Dataform core files, see Overview of Dataform core.