Dataform core lets you create SQL workflow objects with SQLX and JavaScript. While optional, using JavaScript along with SQLX to create similar elements repeatedly in your workflow. For example, with JavaScript you can create a view of each table in your workflow with certain user IDs removed. You can also develop SQL workflow objects exclusively with JavaScript.
To get started with developing Dataform SQL workflows using JavaScript, you must be familiar with JavaScript syntax and the following JavaScript concepts:
- Variables
- Arrays
- Conditional statements
- For loops
- Maps
- Functions
- Objects
- Exporting and importing modules
Add JavaScript code to a SQLX file
You can add JavaScript code to a SQLX file in two ways: inline or inside a JavaScript block.
You can use a JavaScript block to define functions or constants in a SQLX file. You can use inline JavaScript to dynamically modify a SQLX or SQL query.
The following code sample shows the self
Dataform core built-in
JavaScript function added inline to the post_operations
block in a SQLX file:
config {type: "table"}
SELECT * FROM ...
post_operations {
GRANT `roles/bigquery.dataViewer`
ON
TABLE ${self()}
TO "group:allusers@example.com", "user:otheruser@example.com"
}
The following code sample shows a constant defined in a JavaScript block and used inline inside a query in a SQLX file:
js {
const columnName = "foo";
}
SELECT 1 AS ${columnName} FROM "..."
Reuse code with JavaScript
You can reuse JavaScript code to streamline development in Dataform. To reuse JavaScript constants and functions across a single SQLX file, you can encapsulate them in a JavaScript block. To reuse JavaScript code across a single Dataform repository, you can create includes. To reuse JavaScript code across multiple Dataform repositories, you can create or import a package.
Reuse code across a single SQLX file with JavaScript encapsulation
To create repetitive parts of SQL code that you can reuse within a single SQLX file, you can encapsulate functions and constants in a JavaScript block. You can reuse code defined in a JavaScript block only inside the SQLX file where the block is defined. For more information, see Overview of Dataform core.
The following code sample shows a constant and function defined in a JavaScript block and used inline inside a query in a SQLX file:
js {
const foo = 1;
function bar(number){
return number+1;
}
}
select
${foo} as one,
${bar(foo)} as two
Reuse code across a single repository with includes
Includes are JavaScript constants or functions global to your repository.
You define includes in the includes
directory of your repository. You can then reuse them across your repository
in JavaScript and SQLX files.
The following code sample shows the definition of the launch_date
constant
in the includes/constants.js
file:
// filename is includes/constants.js
const launch_date = "11.11.2011";
module.exports = { launch_date };
The following code sample shows the launch_date
constant referenced in a table
definition query in a SQLX file:
config {type: "table"}
SELECT * FROM source_table WHERE date > ${constants.launch_date}
Reuse code across multiple repositories with packages
Packages are collections of JavaScript code that you can import and use across multiple Dataform repositories to streamline SQL workflow development.
You can create your own custom package in Dataform or use one of the open-source Dataform packages, available on the GitHub open-source Dataform page.
For an example of using an open-source package in Dataform, see Use Slowly changing dimensions in Dataform.
To be able to use the contents of a package in Dataform, you need to install the package in your Dataform repository, and then import it to the individual JavaScript or SQLX file in which you want to use the package. For more information, see Install a package in Dataform.
To be able to install a private NPM package in a Dataform repository, you need to authenticate the package.
Create SQL workflows in JavaScript
As an alternative to developing SQL workflows in SQLX, or SQLX combined with
JavaScript, you can create SQL workflow objects in .js
files by using only
JavaScript. You can create multiple SQL workflow objects within one JavaScript
file with Dataform global methods and arbitrary JavaScript ES5 code,
such as loops and constants. Each of the Dataform global JavaScript
methods contain properties you can use to configure the created objects.
You can create the following SQL workflow objects by using only JavaScript in Dataform:
- Data source declarations
- Tables
- Manual assertions
- Custom SQL operations
For more information on creating SQL workflow objects using only JavaScript, see Create Dataform SQL workflows with JavaScript.
What's next
- To learn how to reuse variables and functions with includes, see Reuse variables and functions with includes in Dataform.
- To learn how to create a package, see Create a package in Dataform.
- To learn how to install a package, see Install a package in Dataform.
- To learn how to authenticate a private package, see Authenticate a private package.
- To learn how to use an open-source package in Dataform, see Use Slowly changing dimensions in Dataform.
- To learn how to create SQL workflows with JavaScript, see Create Dataform SQL workflows with JavaScript.