Grouping columns descriptions in Dataform

sysph · August 1, 2023, 10:17am

Hi there,

I have the code below in a docs.js file in Dataform includes folder. However, it seems not working after execution. eg. I used 3 docts notation before dims.session_date. Is it correct way to use in Dataform? Thanks in advance.

const dims = {
    post_id:`page post id`,
    page_title: `page title`,
    page_path: `page path`,
...
}
const metrics = {
    word_count: `total # of word count`,
    pageviews: `total # of pageviews`,
    article_pageviews: `total # of article pageviews`,
...
}
const fields_ga3_daily_article_performance = {
     ...dims.session_date,
     ...dims.post_id,
     ...dims.premium_status,
     ...dims.session_status,
     ...dims.channel_group,
     ...metrics.pageviews,
...
}
 
const fields_ga3_daily_channel_performance = {
     ...dims.session_date,
     ...dims.channel,
     ...dims.channel_group,
     ...dims.session_status,
...
}
 
module.exports = {
    dims,
    metrics,

     fields_ga3_daily_article_performance,
     fields_ga3_daily_channel_performance,
...
}

This is how i use in SQLX models

config {
name: "table1"
columns: docs.fields_ga3_article_performance
...
}
select ...

config {
name: "table2"
columns: docs.fields_ga3_channel_performance
...
}
select ...

I’ve done the same for table descriptions too and it doesn’t make any change in BQ table. Hope you can give me some ideas.

config{

description:table_definitions.daily_article_performance_table_desc,
}

const source_table_desc = "GA3 master source table";

const daily_article_performance_table_desc = "This table contains aggregated GA3 daily article performance data";

ms4446 · August 1, 2023, 2:39pm

The code in your docs.js file is mostly correct, but you need to make a few changes to your SQLX models in order to use it.

First, you need to require the docs.js file into your SQLX models. You can do this by adding the following line to the top of your SQLX models file:

const docs = require(“includes/docs.js”);

Once you have required the docs.js file, you can use the fields that you defined in the file in your SQLX models. For example, you can use the following code to create a table that uses the fields_ga3_daily_article_performance field:

config {
type: “table”,
name: “table1”,
columns: ${JSON.stringify(docs.fields_ga3_daily_article_performance)}
}

select …

Similarly, you can use the following code to set the description of a table to the value of the daily_article_performance_table_desc constant:

config {
type: “table”,
name: “table1”,
description: ${docs.daily_article_performance_table_desc}
}

select …

Once you have made these changes, your SQLX models should be able to use the fields and descriptions that you defined in the docs.js file.

Here is a complete example of a SQLX model that uses the docs.js file:

const docs = require(“includes/docs.js”);

config {
type: “table”,
name: “table1”,
columns: ${JSON.stringify(docs.fields_ga3_daily_article_performance)},
description: ${docs.daily_article_performance_table_desc}
}

select …

Please note that the columns field in the config block is expecting an array of column objects, not a string. If your fields_ga3_daily_article_performance is a string, you might need to adjust your docs.js file to return an array of column objects instead.

sysph · August 1, 2023, 3:56pm

Hi @ms4446 ,

Thanks for getting back to me so quick. I tried to add const docs = require(“includes/docs.js”); in my SQLX model (in GCP Dataform) but it shows error.

Do i need to put const docs = require(“includes/docs.js”); in a js {} block? thanks

Syntax error: Expected end of input but got identifier “const” at [1:1].

ms4446 · August 1, 2023, 4:36pm

Yes, in Dataform SQLX files, JavaScript code should be enclosed within js {} blocks. Here’s how you can do it:

js {
const docs = require(“includes/docs.js”);
}

config {
type: “table”,
name: “table1”,
columns: ${JSON.stringify(docs.fields_ga3_daily_article_performance)},
description: ${docs.daily_article_performance_table_desc}
}

select …

sysph · August 2, 2023, 10:29am

Hi @ms4446 ,

I’ve tried and it shows

Unexpected token ‘{’

ms4446 · August 2, 2023, 2:28pm

My apologies. Apparently, Dataform SQLX files don’t support embedding JavaScript code directly in the way I described.

In Dataform, you can define JavaScript functions in separate .js files and then use those functions in your SQLX files. Here’s how you can do it:

Define Functions in a Separate .js File: In your docs.js file, you can define functions that return the objects you want to use in your SQLX files.

// docs.js
module.exports = {
getFieldsGa3DailyArticlePerformance: function() {
return {
session_date: ‘session date’,
post_id: ‘post id’,
// … other fields
};
},
getDailyArticlePerformanceTableDesc: function() {
return “This table contains aggregated GA3 daily article performance data”;
}
// … other functions
};

Use the Functions in Your SQLX Files: In your SQLX files, you can use the js { } block to call the functions defined in your .js file.

– table1.sqlx
js {
const docs = require(“includes/docs.js”);
const fields = docs.getFieldsGa3DailyArticlePerformance();
const description = docs.getDailyArticlePerformanceTableDesc();
}

config {
type: “table”,
name: “table1”,
columns: ${JSON.stringify(fields)},
description: ${description}
}

select …

By defining functions in your .js file and calling those functions in your SQLX files, you can achieve the desired behavior without encountering syntax errors.

sysph · August 7, 2023, 2:50pm

Let me give it a try. Thanks ms4446

anonymous · August 21, 2023, 3:58pm

Hello @ms4446 ,

What i am trying to do is export a variable from one sqlx file to another.
I have received the value of this variable through a js function and i want to export it.
I was trying to export it using the js{ } code block.

What could be another approach to this problem?

ms4446 · August 21, 2023, 6:23pm

In Dataform, sharing variables or values between SQLX files directly isn’t as straightforward as in traditional programming languages. However, there are a few approaches you can take:

Centralize in a JavaScript File:

This approach involves creating a centralized .js file where you define and export your variables or functions. You can then use the require function in each SQLX file where you need those variables or functions.

For example, let’s say you have a variable called sharedVariable that you want to share between two SQLX files. You would first create a shared.js file with the following content:
```
module.exports = {
    sharedVariable: "your_value"
};
```
You can then import the shared.js file into your SQLX files using the require function:
```
js {
    const shared = require("includes/shared.js");
}

select ${shared.sharedVariable} from your_table;
```
Use SQLX Macros:

This approach involves defining a macro in one SQLX file that returns the value you want to share. You can then use that macro in other SQLX files.

For example, let’s say you have a macro called get_shared_value() that returns the value of the sharedVariable variable. You would first define the macro in a file called macros.sqlx:

SQL
```
define macro get_shared_value() {
    "your_value"
}
```
You can then use the get_shared_value() macro in other SQLX files:
```
select ${get_shared_value()} from your_table;
```
Use Intermediate Tables:

This approach involves creating an intermediate table to store the value you want to share. You can then reference the intermediate table in other SQLX files.

For example, let’s say you want to share the value of the sharedVariable variable between two SQLX files. You would first create an intermediate table called shared_value_table with a single column called value. You would then insert the value of the sharedVariable variable into the value column of the shared_value_table.

You can then reference the shared_value_table in other SQLX files:
```
select value from shared_value_table;
```
Use Environment Variables:

This approach involves using Dataform’s environment variables feature to share a value between SQLX files. You can set the environment variable in Dataform’s settings, and then access the environment variable in your SQLX files using the env function.

For example, let’s say you want to share the value of the ENV_VARIABLE environment variable between two SQLX files. You would first set the ENV_VARIABLE environment variable in Dataform’s settings. You would then access the ENV_VARIABLE environment variable in your SQLX files using the env function:
```
select ${env("ENV_VARIABLE")} from your_table;
```

anonymous · August 22, 2023, 12:17pm

@ms4446 Thanks a lot for guidance! Approaches suggested by you are working.

I am new to dataform and i have to implement a working Dataform workflow which i have to trigger from Airflow.

Now the issue is, I have tried to implement many things described in official google documentation but there are many issues with these implementations.

1.Inline JS is not possible.

2.Triggering Dataform dags with specific tags and with vars.

3.Cannot import DataformWorkflowInvocationStateSensor operator in my dag code.

There are many such issues.
Please guide me to a reliable tech forum/article that i can use for implementing these scenarios.
Also, would it be better if i use dataform core instead of dataform for the same.

ms4446 · August 22, 2023, 3:04pm

In Dataform, sharing variables or values between SQLX files directly isn’t as straightforward as in traditional programming languages. However, there are a few approaches you can take:

Centralize in a JavaScript File:

Create a centralized .js file where you define and export your variables or functions.
Use the require function in each SQLX file where you need those variables or functions.

For example, in **shared.js**:

module.exports = {
sharedVariable: “your_value”
};

Then, in your SQLX files:

js {
const shared = require(“includes/shared.js”);
}

select ${shared.sharedVariable} from your_table;

Use SQLX Macros:

Define a macro in one SQLX file that returns the value you want to share.
Use that macro in other SQLX files.

For example, in **macros.sqlx**:

define macro get_shared_value() {
“your_value”
}

Then, in another SQLX file:

select ${get_shared_value()} from your_table;

Use Intermediate Tables:

If the value you’re trying to share is the result of a query, consider creating an intermediate table to store that value.
Reference the intermediate table in other SQLX files.

For example, in **intermediate_table.sqlx**:

select your_value as shared_value from your_table;

Then, in another SQLX file:

select shared_value from ${ref(“intermediate_table”)};

Environment Variables:

If the value is sensitive or could change based on the environment (e.g., development vs. production), consider using Dataform’s environment variables feature.
Set the environment variable in Dataform’s settings.
Access the environment variable in your SQLX files using the **env** function.

For example:

select ${env(“YOUR_ENV_VARIABLE”)} from your_table;

Choose the approach that best fits your use case. If the value is static or can be determined by a JavaScript function, the first approach might be the most suitable. If the value is the result of a SQL query, the third approach might be more appropriate. If the value could change based on the environment, consider the fourth approach.

Topic		Replies	Views
Ho to reference a variable from js script within .sqlx file? Data Analytics bigquery , dataform	9	203	April 2, 2024
[Dataform] How to organize table functions, docs within includes folder? Data Analytics bigquery , dataform , business-intelligence , analytics-general	1	217	July 4, 2024
How to get column names from a definition using JS Data Analytics dataform	1	72	August 15, 2025

Grouping columns descriptions in Dataform

AI Suggested topics