Use JavaScript in Dataform

Dataform core lets you create workflow actions 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 workflow actions exclusively with JavaScript.

Before you begin

1. In the Google Cloud console, go to the Dataform page.

   Go to Dataform

2. Select or create a repository.

3. Select or create a development workspace.

Additionally, 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

Required roles

To get the permissions that you need to develop a workflow with JavaScript and 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.

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 across a single SQLX file with JavaScript encapsulation

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.

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 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}

Create a JavaScript file for includes

To create a new JavaScript file in the includes/ directory, follow these steps:

1. In the Files pane, next to includes/, click more_vert More.

2. Click Create file.

3. In the Create new file pane, do the following:

   1. 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.

   2. Click Create file.

Create a JavaScript constant

To create a constant that you can reuse across your project, follow these steps:

1. Go to your development workspace.

2. In the Files pane, expand includes/.

3. Create or select a JavaScript file with the .js extension.

4. 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

5. 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 previous 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:

1. Go to your development workspace.

2. In the Files pane, expand includes/.

3. Create or select a JavaScript file with the .js extension.

4. In the file, write your custom JavaScript function.

5. In the file, enter the following code snippet:

    module.exports = { FUNCTION_NAME }
   

   Replace FUNCTION_NAME with the name of your function.