Reuse variables and functions with includes in Dataform

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.

Go to Dataform
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.

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

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.