Cubes

Parameters

title

Use title to change the display name of the cube. By default, Cube.js will humanize the cube's name, so for instance, UsersOrder would become Users Orders. If default humanizing doesn't work in your case, please use the title parameter. It is highly recommended to give human readable names to your cubes. It will help everyone on a team better understand the data structure and will help maintain a consistent set of definitions across an organization.

cube(`Orders`, {
  sql: `SELECT * FROM orders`,

  title: `Product Orders`,
});

description

Use a description in your cubes to allow your team to better understand what this cube is about. It is a very simple and yet useful tool that gives a hint to everyone and makes sure data is interpreted correctly by users.

cube(`Orders`, {
  sql: `SELECT * FROM orders`,

  title: `Product Orders`,
  description: `All orders related information`,
});

extends

You can extend cubes in order to reuse all declared members of a cube. In the example below, ExtendedOrderFacts will reuse the sql and count measures from OrderFacts:

cube(`OrderFacts`, {
  sql: `SELECT * FROM orders`

  measures: {
    count: {
      type: `count`,
      sql: `id`
    }
  }
});

cube(`ExtendedOrderFacts`, {
  extends: OrderFacts,

  measures: {
    doubleCount: {
      type: `number`,
      sql: `${count} * 2`
    }
  }
});

You can also omit the cube name while defining it. This way Cube.js doesn't register this cube globally but instead it returns reference to it which you can use while combining cubes. It makes sense to use it for dynamic schema generation and reusing with extends. Previous example without defining OrderFacts cube globally:

const OrderFacts = cube({
  sql: `SELECT * FROM orders`

  measures: {
    count: {
      type: `count`,
      sql: `id`
    }
  }
});

cube(`ExtendedOrderFacts`, {
  extends: OrderFacts,

  measures: {
    doubleCount: {
      type: `number`,
      sql: `${count} * 2`
    }
  }
});

refreshKey

Cube.js caching layer uses refreshKey queries to get the current version of content for a specific cube. If a query result changes, Cube.js will invalidate all queries that rely on that cube.

The default values for refreshKey are

• every: '2 minute' for BigQuery, Athena, Snowflake, and Presto.
• every: '10 second' for all other databases.

Refresh key of a query is a concatenation of all cubes refresh keys involved in query. For rollup queries pre-aggregation table name is used as a refresh key.

You can set up a custom refresh check SQL by changing refreshKey property. Often, a MAX(updated_at_timestamp) for OLTP data is a viable option, or examining a metadata table for whatever system is managing the data to see when it last ran. timestamp in that case.

cube(`OrderFacts`, {
  sql: `SELECT * FROM orders`,

  // With this refreshKey Cube.js will only refresh the data if
  // the value of previous MAX(updated_at_timestamp) changed
  // By default Cube.js will check this refreshKey every 10 seconds
  refreshKey: {
    sql: `SELECT MAX(updated_at_timestamp) FROM orders`
  }
});

You can use interval based refreshKey. For example:

cube(`OrderFacts`, {
  sql: `SELECT * FROM orders`,

  refreshKey: {
    every: `1 hour`
  }
});

every - can be set as an interval with granularities second, minute, hour, day, and week or accept CRON string with some limitations. If you set every as CRON string, you can use the timezone property.

For example:

cube(`OrderFacts`, {
  sql: `SELECT * FROM orders`,
  refreshKey: {
    every: '30 5 * * 5',
    timezone: 'America/Los_Angeles'
  }
});

every can accept only equal time intervals - so "Day of month" and "month" intervals in CRON expressions are not supported.

Such refreshKey is just a syntactic sugar over refreshKey SQL. It's guaranteed that refreshKey change it's value at least once during every interval. It will be converted to appropriate SQL select which value will change over time based on interval value. Values of interval based refreshKey are tried to be checked ten times within defined interval but not more than once per 1 second and not less than once per 5 minute. For example if interval is 10 minute it's refreshKeyRenewalThreshold will be 60 seconds and generated refreshKey SQL (Postgres) would be:

SELECT FLOOR(EXTRACT(EPOCH FROM NOW()) / 600)

For 5 second interval refreshKeyRenewalThreshold will be just 1 second and SQL will be:

SELECT FLOOR(EXTRACT(EPOCH FROM NOW()) / 5)

Supported cron formats

• Standard cron syntax

*    *    *    *    *
┬    ┬    ┬    ┬    ┬
│    │    │    │    |
│    │    │    │    └ day of week (0 - 7) (0 or 7 is Sun)
│    │    │    └───── month (1 - 12)
│    │    └────────── day of month (1 - 31, L)
│    └─────────────── hour (0 - 23)
└──────────────────── minute (0 - 59)

• Advanced cron format with support for seconds

*    *    *    *    *    *
┬    ┬    ┬    ┬    ┬    ┬
│    │    │    │    │    |
│    │    │    │    │    └ day of week (0 - 7) (0 or 7 is Sun)
│    │    │    │    └───── month (1 - 12)
│    │    │    └────────── day of month (1 - 31, L)
│    │    └─────────────── hour (0 - 23)
│    └──────────────────── minute (0 - 59)
└───────────────────────── second (0 - 59, optional)

dataSource

Each cube in schema can have it's own dataSource name to support scenarios where data should be fetched from multiple databases. Value of dataSource parameter will be passed to dbType and driverFactory functions as part of the context paramameter. By default, each cube has a default value for it's dataSource. To override it you can use:

cube(`OrderFacts`, {
  sql: `SELECT * FROM orders`,

  dataSource: `prod_db`
});

sqlAlias

Use sqlAlias when auto-generated cube alias prefix is too long and truncated by DB such as Postgres:

cube(`OrderFacts`, {
  sql: `SELECT * FROM orders`,

  sqlAlias: `ofacts`,

  // ...
});

It'll generate aliases for members such as ofacts__count. sqlAlias affects all member names including pre-aggregation table names.

Context Variables

Security Context

SECURITY_CONTEXT is a user security object that is passed by the Cube.js Client.

Please see Security Context section on how to set SECURITY_CONTEXT value.

Security context is suitable for the row level security implementation. For example, if you have an orders table that contains an email field you can restrict all queries to render results that belong only to the current user as follows:

cube(`Orders`, {
  sql: `SELECT * FROM orders WHERE ${SECURITY_CONTEXT.email.filter('email')}`,

  dimensions: {
    date: {
      sql: `date`,
      type: `time`
    }
  }
});

To ensure filter value presents for all requests requiredFilter can be used:

cube(`Orders`, {
  sql: `SELECT * FROM orders WHERE ${SECURITY_CONTEXT.email.requiredFilter('email')}`,

  dimensions: {
    date: {
      sql: `date`,
      type: `time`
    }
  }
});

Unsafe Value

You can access values of context variables directly in javascript in order to use it during your SQL generation. For example:

cube(`Orders`, {
  sql: `SELECT * FROM ${SECURITY_CONTEXT.type.unsafeValue() === 'employee' ? 'employee' : 'public'}.orders`,

  dimensions: {
    date: {
      sql: `date`,
      type: `time`
    }
  }
});

SQL Utils

convertTz

In case you need to convert your timestamp to user request timezone in cube or member SQL you can use SQL_UTILS.convertTz() method. Note that Cube.js will automatically convert timezones for timeDimensions fields in queries.

In case the same database field needs to be queried in dimensions and timeDimensions, create dedicated dimensions in the cube definition for the respective use:

cube(`visitors`, {
  // ...

  dimensions: {
    createdAtConverted: { // do not use in timeDimensions query property
      type: 'time',
      sql: SQL_UTILS.convertTz(`created_at`)
    },
    createdAt: { // use in timeDimensions query property
      type: 'time',
      sql: `created_at`
    },
  }
})

Compile context

There's global COMPILE_CONTEXT that captured as RequestContext at the time of schema compilation. It contains securityContext and any other variables provided by extendContext.

const { securityContext: { deploymentId } } = COMPILE_CONTEXT;

const schemaName = `user_${deploymentId}`;

cube(`Users`, {
  sql: `select * from ${schemaName}.users`,

  // ...
});