Column definition sintax using js file

[giopappy]

Hi All,

to make more flexible the creation of standard views, I'm trying to define them using javascript, but I can't assign a description to columns. This is an example code:

const col = "COL1: \"test col\"";
publish("testview",{
    type: "view",
    columns: {col}
}).query(ctx => "SELECT 1 AS COL1")

The view has been created but no description is available for COL1

This work:

publish("testview",{
    type: "view",
    columns: {COL1: "test col"}
}).query(ctx => "SELECT 1 AS COL1")

but I've to put this sentence in a for loop to create lots of views based on different queries.

Can anyone help me?

Thanks 

[ms4446]

It seems like you are encountering a problem with dynamic property names in JavaScript object literals when trying to create column descriptions in BigQuery views.

In JavaScript, if you want to use a variable as a property name in an object, you need to use computed property names. This is done by wrapping the variable in square brackets.

Based on your example, you want to dynamically set the column name and its description in your publish function. Here's how you can modify your code to achieve that:

const colName = "COL1"; 
const colDescription = "test col";

publish("testview", { 
    type: "view", 
    columns: { 
        [colName]: colDescription // Using computed property names 
    } 
}).query(ctx => "SELECT 1 AS " + colName); 

In this modified code:

• colName is the variable holding the name of the column.
• colDescription is the variable holding the description of the column.

By using [colName]: colDescription inside the object, you are dynamically setting the property name based on the value of colName.

For your requirement of creating multiple views inside a loop, you can use a similar approach. Here’s a basic example of how you might iterate over an array of column names and descriptions to create multiple views:

const columns = [
    { name: "COL1", description: "Description for COL1" },
    { name: "COL2", description: "Description for COL2" },
    // Add more columns as needed
];

columns.forEach(col => {
    publish(col.name + "_view", {
        type: "view",
        columns: {
            [col.name]: col.description
        }
    }).query(ctx => `SELECT 1 AS ${col.name}`);
});

1. Iterates over an array of objects where each object contains a name and description for a column.
2. For each column, it creates a view named after the column with the appropriate description set dynamically.

Make sure your use case aligns with the limits and capabilities of the system you are working within, such as the number of views that can be programmatically created and managed in your BigQuery environment.

[giopappy]

Thank you, this is very useful. Your example code shows how to create a view per each column, my requirement is to automatically create a view with n columns. Each view with a different query. So I've to put both columns (but the number of columns could vary) :

const columns = [
    { name: "COL1", description: "Description for COL1" },
    { name: "COL2", description: "Description for COL2" },
    // Add more columns as needed
];

 in the publish "columns" statement. I think that a way could be to dynamically create a string and then execute it with "eval" command. Do you think that there is a simpler way to do it?

Thanks a lot

[ms4446 · Accepted solution]

You're correct in avoiding the use of eval for dynamically constructing code due to its potential security and performance implications. Instead, a cleaner and safer approach is to create views with variable numbers of columns and distinct queries using JavaScript object manipulation. Here’s how you can achieve this:

Example:

const viewDefinitions = [
  {
    name: "view1",
    columns: [
      { name: "COL1", description: "Description for COL1" },
      { name: "COL2", description: "Description for COL2" }
    ],
    query: () => `SELECT 1 AS COL1, 2 AS COL2` // Custom query for view1
  },
  {
    name: "view2",
    columns: [
      { name: "COL3", description: "Description for COL3" },
      { name: "COL4", description: "Description for COL4" },
      { name: "COL5", description: "Description for COL5" }
    ],
    query: () => `SELECT 3 AS COL3, 4 AS COL4, 5 AS COL5` // Custom query for view2
  }
  // Additional view definitions can be added here as needed
];

viewDefinitions.forEach(viewDef => {
  const columnDefinitions = viewDef.columns.reduce((acc, col) => {
    acc[col.name] = col.description; // Dynamically build column definitions
    return acc;
  }, {});

  publish(viewDef.name, { type: "view", columns: columnDefinitions })
    .query(viewDef.query); // Apply the custom query for this view
});

Explanation:

• viewDefinitions Array: This contains objects, each defining a view's configuration, including the name, columns, and the query.
• Dynamic Columns Object: Uses .reduce() on the columns array to transform it into the required object structure for the columns property in the publish function.
• publish Call: Invokes the publish function with the dynamically constructed columns object, the view's name, and specifying it as a "view".
• query() Call: Chains this method to publish to assign the custom SQL query for the view.

Key Improvements:

• Structured and Clear: Each view is neatly defined within its own object, making the code easy to manage and update.
• Security Enhanced: Avoids the security risks associated with eval by utilizing safe JavaScript practices.
• Flexible and Customizable: Allows for easy addition and modification of views by adjusting the viewDefinitions array.
• Adaptable Queries: Each view can have a tailored query, enhancing the versatility for specific requirements.