Ho to reference a variable from js script within .sqlx file?

Google Cloud Data Analytics
,
1

I am trying to insert a js script inside a .sqlx file and then want to reference a variable from it inside config, but cannot.

I tried like this:

js {
  const dpds = ["merge_bsi_address_stage"]
}

config {
  type: "operations",
  tags: "adsid_tlog",
  dependencies: dpds
}

But I get an error: dpds is not defined
Or like this:

js {
  const dpds = ["merge_bsi_address_stage"]
}

config {
  type: "operations",
  tags: "adsid_tlog",
  dependencies: this.dpds
}

Then I do not get an error, but also dependencies are empty.

2

The error “dpds is not defined” occurs in Dataform’s .sqlx files because variables declared within a JavaScript (js) block are not directly accessible in the SQLX configuration (config) block. This limitation stems from the fact that js and config blocks are distinct and do not share their scope.

Correctly Setting Configuration Properties Using JavaScript

To dynamically set configuration properties in Dataform using JavaScript, you should define the entire configuration within the js block. This approach is necessary because you cannot reference variables from a js block within a config block. The this keyword does not provide a workaround, as it does not refer to the .sqlx file or grant access to the js block’s scope.

Corrected Code Example


js {
  const dpds = ["merge_bsi_address_stage"];
  publish("your_table_name") {
    type: "operations",
    tags: ["adsid_tlog"],
    dependencies: dpds
  };
}

Explanation of the Corrected Code

  • Table Name: Replace "your_table_name" with the actual name of the table you are configuring.
  • Publish Function: The publish function is used within the js block to set the entire configuration for the table.
  • Dependencies: The dpds array, defined within the js block, is used to set the dependencies correctly.

By following this method, you ensure that your JavaScript variables and logic are appropriately applied to the SQLX configuration, avoiding scope-related errors.

3

Hi @ms4446 ,

Thanks for the answer. I have two questions :

  • The code that you have provided seems to be wrong : I don’t know you could have the publish method and then open the bracket
  • Normally, in a js file, you use the publish method and then the .query. How can you refer to the query in your sqlx file ?

Thanks for your answer !

4

When working with Dataform and its SQLX files, it’s essential to grasp how JavaScript can be integrated to dynamically configure data transformations and pipelines. Here’s a correct approach, addressing previous misconceptions:

Previous Misconceptions:

  • publish function: This function is primarily used in Dataform’s JavaScript API (.js files) for defining datasets. It’s not directly used within the structure of a .sqlx file.
  • Direct JavaScript in SQL: While JavaScript enhances SQLX, inject JS variables directly into SQL queries with ${variable_name} is not supported. Use Dataform’s built-in functions instead.

Correct Approach:

js { 
  const dependencies = ["merge_bsi_address_stage"]; 
  const myTableName = "your_table_name"; 
} 

SELECT * FROM ${ref(myTableName)} 

config { 
  type: "view", 
  tags: ["adsid_tlog"], 
  dependencies: dependencies 
}

Explanation:

  • JavaScript Block: Define variables to be used in dynamic configurations or as references within your SQL.
  • SQL Queries: Utilize Dataform’s built-in functions like ref() to create dynamic references that adapt based on JavaScript variables.
  • Configuration Block: Specify metadata (type, tags) and set dependencies dynamically using your JavaScript variables.
5

qchuchu_1-1711648552231.png
qchuchu_1-1711648552231.png2098×303 32.4 KB

I’ve just tried your approach, but I’m getting a “depedencies” is not defined error. Do you know why ?

Best,

6

This is the kind of approach I’ve been trying before but it seems that within the config block it’s not possible to reference constant that have been declared inside the same file.

7

I started to deep dive in the code of dataform but I don’t really see in the code where the js constants are being resolved. It seems that if the constant is defined in the includes folder it’s OK, but in my case the constant really make sense only in the scope of my file..

8

I see where the confusion is coming from, and I apologize for any misunderstanding caused by my previous responses. Let’s address the issue with the correct approach given the limitations you’ve encountered.

In Dataform (Google Cloud), the js {} block and the config {} block indeed operate in separate scopes within .sqlx files. This means variables defined in a js {} block cannot be directly referenced in the config {} block as if they were in the same scope. This separation leads to the “dependencies is not defined” error you’re encountering.

Correct Approach for Using JavaScript Variables in Config

Given the scope limitation, the correct approach to dynamically setting configurations based on JavaScript variables involves a slightly different strategy. Since direct reference isn’t possible as you’ve discovered, you would typically use the js {} block to return the entire configuration object, including any dynamic values you wish to include. However, this pattern applies more to .js files within Dataform projects rather than .sqlx files.

Workaround for SQLX Files

For .sqlx files, since the direct inclusion of JavaScript variables into the config {} block isn’t supported, you would need to statically define your dependencies within the config {} block or reconsider the structure of your project to utilize .js files for dynamic configuration if your use case heavily relies on dynamic dependencies.

If your project’s structure allows for it, moving the dynamic logic to JavaScript files (*.js) where you can programmatically define and manipulate datasets might be a more flexible approach. Here’s a conceptual example of how you might structure a .js file:

const dependencies = ["merge_bsi_address_stage"]; 
const myTableName = "your_table_name"; 

publish(myTableName, { 
    type: "view", 
    query: `SELECT * FROM ${myTableName}`, 
    tags: ["adsid_tlog"], 
    dependencies: dependencies 
});

This .js approach allows for the dynamic inclusion of variables in both the query and the configuration, leveraging the full programmability of JavaScript.

For SQLX Files

Given the current limitations, ensure your config {} block in .sqlx files contains statically defined values or consider restructuring your Dataform project to utilize .js files for parts of your pipeline that require dynamic configuration.

9

Given this limitation, here are a few strategies to manage file-specific constants:

Encapsulating Logic with JavaScript Functions

  • Encapsulation promotes modular code, making logic that utilizes constants more organized and debuggable. It’s an effective way to manage scope and enhance code readability.
function computeDiscountedPrice() {
  const discountRate = 0.1; // Local scope constant
  // Logic to apply discountRate
  return { discountedPrice: 100 - (100 * discountRate) };
}
const priceDetails = computeDiscountedPrice();

Inline JavaScript for Dynamic SQL Queries

  • This method leverages the flexibility of JavaScript to dynamically construct SQL queries, allowing for the integration of logic and constants directly within SQL code.
const dynamicTableName = "monthly_sales";
return `SELECT * FROM ${dynamicTableName} WHERE region = 'North America';`;

Utilizing Predefined Variables

  • Predefined variables offer a robust solution for managing environment-specific constants, enhancing the adaptability and scalability of your data pipelines across different settings.
  • Tip: Configure these variables via the Dataform web interface or the dataform.json file, ensuring your scripts remain flexible and environment-agnostic.

Reevaluating Constant Scope

  • The scope of constants should be periodically reviewed. This practice can uncover opportunities to optimize code maintainability and efficiency as project requirements evolve.

Adhering to Documentation and Naming Conventions

  • Best Practice: Effective documentation and clear naming conventions are crucial for code maintainability, particularly in team environments. They clarify the purpose and scope of constants, facilitating easier code navigation and updates.

Considerations and Trade-offs

  • Function Encapsulation vs. Inline Javascript: Choosing between these depends on your specific needs—functions for modularity and reusability, or inline JavaScript for direct SQL query manipulation.

  • Predefined Variables: Ideal for static or environment-specific values, but may not be suitable for all dynamic scenarios within data transformations.

Implementing these strategies for managing file-specific constants in Dataform allows for more organized, maintainable, and efficient data transformation projects. By carefully choosing between JavaScript functions, inline SQL manipulation, and predefined variables—balanced with regular reviews of constant scope and adherence to documentation best practices—you can navigate Dataform’s scoping limitations effectively, ensuring your projects are both robust and adaptable.

10

Ok. Thanks for your answer.

Someone else opened an issue here on github : https://github.com/dataform-co/dataform/issues/1704.

I might have some time to improve this. I’ve just checked the code, but it requires little help from the dev team to just help me understand how I could contribute !

++