This document shows you how to use JavaScript to create SQL workflow elements in Dataform. You might want to use JavaScript over Dataform core to create similar elements repeatedly in your workflow.
About developing workflows in Dataform with JavaScript
Dataform core lets you create SQL workflow actions with SQLX and JavaScript. With JavaScript, you can create similar actions repeatedly in your workflow. For example, you can create a view of each table in your workflow with certain user IDs removed.
The following JavaScript code sample lets you create a view of each table where
the value of the user_id
field does not correspond to one of the values in the
blocked_user_ids
list:
const tableNames = ["user_events", "user_settings", "user_logs"];
tableNames.forEach(tableName => {
publish(tableName + "_blocked_removed").query(
ctx => `
SELECT * FROM ${ctx.ref(tableName)}
WHERE user_id NOT IN (
SELECT user_id
FROM ${ctx.ref("blocked_user_ids")}
)`
);
});
This code sample creates three views named
user_events_blocked_removed
, user_settings_blocked_removed
, and
user_logs_blocked_removed
that don't contain any of the blocked user IDs.
You can create multiple actions within one JavaScript file with Dataform global methods and arbitrary JavaScript ES5 code, such as loops and constants.
You can define the following actions with JavaScript in Dataform:
Before you begin
In the Google Cloud console, go to the Dataform page.
Select or create a repository.
Select or create a development workspace.
Required roles
To get the permissions that you need to develop a SQL workflow with JavaScript,
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 in Dataform
Store JavaScript files for definitions and data source declarations in the
definitions/
directory. To create a new JavaScript file in the definitions/
directory, follow these steps:
In the Files pane, next to
definitions/
, click the More menu.Click Create file.
In the Create new file pane, do the following:
In the Add a file path field, after
definitions/
, enter the name of the file followed by.js
. For example,definitions/definitions.js
.Filenames can only include numbers, letters, hyphens, and underscores.
Click Create file.
Set Dataform SQL workflow object properties with JavaScript
You can use the following Dataform global methods to create SQL workflow objects with Dataform:
declare
. Used to declare a data source.publish
. Used to define a table.assert
. Used to create an assertion.operate
. Used to define a custom SQL operation.
Each of the global methods contain properties you can use to configure the created object. For more information on global methods and their properties, see the Dataform core reference.
In the publish()
method that creates a table, you can set table properties
by passing them as the second method argument.
To pass table properties as the second argument of publish()
,
follow these steps:
In your development workspace, in the Files pane, expand
definitions/
.Select a JavaScript file.
In the file, add table properties to the
publish()
method in the following format:method("first_method_argument", { property1: "property1_value", property2: "property2_value", property3: "property3_value", });
Optional: Click Format.
The following code sample shows how to set properties to the publish()
method
by passing the properties as a second argument of the method:
publish("table1", {
type: "table",
dependencies: ["other_table"],
description: {
"Value is 1"
}
}).query(ctx => "SELECT 1 AS test");
Reference includes in a JavaScript file
You can reference any includes function, macro, or constant inside a JavaScript file. For more information on includes in Dataform, see Reuse variables and functions with JavaScript includes.
The syntax for referencing includes inside a JavaScript file depends on the location of the include file. Dataform stores such files in the includes directory.
Reference top-level includes
- To reference a top-level includes file, reference the filename when declaring your variables.
The following code sample references the serviceName
and serviceId
variables from the includes/service.js
file:
const {serviceName, serviceId} = service;
Reference nested includes
To reference a nested includes file, enter the filename in the JavaScript require function.
The following code sample references the serviceName
and serviceId
variables from the includes/allServices/service.js
file:
const {serviceName, serviceId} = require("includes/allServices/service.js");
Use Dataform query functions in JavaScript methods
Dataform provides a number of built-in functions that you can use
inside queries, such as ref
and self
. For more information on
Dataform built-in functions, see the
Dataform API reference.
To use a built-in query function in a JavaScript method, follow these steps:
In your development workspace, in the Files pane, expand
definitions/
.Select a JavaScript file.
In the file, enter a global Dataform JavaScript method.
In the method, enter a contextable
ctx
argument.Optional: If you are using JavaScript template strings, wrap the contextable argument in backticks ``.
In the contextable argument, enter the query function with its parameter as a context object.
Optional: Click Format.
The following code sample shows the ref
query function wrapped inside a
contextable argument of the publish method:
publish("example").query(ctx => `SELECT * FROM ${ctx.ref("other_table")}`);
Declare a SQL workflow data source with JavaScript
You can declare multiple data sources in one JavaScript declaration file with the Dataform declare JavaScript method. For more information on the declare method, see the Dataform core reference. For more information on data sources in Dataform, see Declare a data source.
To declare a data source in a JavaScript file, follow these steps:
In your development workspace, in the Files pane, expand definitions/.
Select a JavaScript file.
In the file, enter the following code snippet:
declare({ database: "DATABASE_PROJECT_ID", schema: "BIGQUERY_SCHEMA", name: "RELATION_NAME", });
Replace the following:
DATABASE_PROJECT_ID
: the project ID of the project which contains the data sourceBIGQUERY_SCHEMA
: the BigQuery dataset in which the external relation existsRELATION_NAME
: the name of the relation that you can later use to reference the data source in Dataform
To declare another data source in the same file, add an extra
declare
block to the file.Optional: Click Format.
Define a table with JavaScript
You can create a table with the Dataform JavaScript publish
method. For more information on the publish method, see the
Dataform core reference.
You can define the following table types: table, incremental table, and view. For more information on defining tables in Dataform, see Create a table.
To define a table in a JavaScript file, follow these steps:
In your development workspace, in the Files pane, expand
definitions/
.Select a JavaScript file.
In the file, enter the following code snippet:
publish("TABLE_NAME").query(ctx => "SELECT_QUERY");
Replace
TABLE_NAME
with the name of the table.Replace
SELECT_QUERY
with a SQL SELECT statement defining the table.
To set the table type, add table dependencies, and add table description, set object properties of the
publish
method.To define another table in the same file, repeat step 3 and step 4.
Optional: Click Format.
Define manual Dataform assertions with JavaScript
You can create manual SQL assertions in a JavaScript file with the Dataform assert JavaScript method. For more information on the assert method, see the Dataform core reference.
A manual assertion SQL query must return zero rows. If the query returns rows when executed, the assertion fails. You can create multiple assertions in one JavaScript file.
For more information on assertions in Dataform, see Test tables with assertions.
To create a manual assertion in a JavaScript file, follow these steps:
In your development workspace, in the Files pane, expand
definitions/
.Select a JavaScript file.
In the file, enter the following code snippet:
assert("ASSERTION_NAME").query(ctx => "CUSTOM_ASSERTION_QUERY");
Replace
ASSERTION_NAME
with the name for your custom assertion.Replace
CUSTOM_ASSERTION_QUERY
with your SQL assertion query.
To create another assertion in the same file, repeat step 3.
Optional: Click Format.
The following code sample shows a JavaScript assertion that asserts that no
value in source_table
is NULL
:
assert("assertion1").query(ctx => "SELECT * FROM source_table WHERE value IS NULL");
Define custom SQL operations with JavaScript
You can define custom SQL operations in a JavaScript file with the Dataform operate JavaScript method. For more information on custom SQL operations in Dataform, see Add custom SQL operations.
To define a custom SQL operation with JavaScript, follow these steps:
In your development workspace, in the Files pane, expand
definitions/
.Select a JavaScript file.
In the file, enter the following code snippet:
operate("OPERATION_NAME").queries(ctx => "CUSTOM_SQL_QUERY");
Replace
OPERATION_NAME
with the name for the custom operation.Replace
CUSTOM_SQL_QUERY
with your custom SQL query.
To define another custom SQL operation in the same file, repeat step 3.
Optional: Click Format.
The following code sample shows a custom SQL operation in a JavaScript file that
inserts a single new row into some_table
and sets test_column
of the new row
to 2
:
operate("operation1").queries("INSERT INTO some_table (test_column) VALUES (2)");
What's next
- To learn more about Dataform JavaScript methods and built-in query functions, see the Dataform core reference.
- To learn more about developing in Dataform with JavaScript, see Introduction to JavaScript in Dataform.