Trigger rebuild of specific partitions of partitioned pre-aggregations using the Orchestration API

[siamak-haschemi]

Is your feature request related to a problem? Please describe.

We want to trigger rebuild of specific partitions of partitioned pre-aggregations using the Orchestrator API.

Describe the solution you'd like

Extend the cubejs-api/v1/pre-aggregations/jobs endpoint, so that selector.preAggregations is an object which accepts a list of patitions:

Example:

"selector": {
      "contexts": [{ "securityContext": {} }],
      "timezones": ["UTC"],
      "preAggregations": [
          {
                "name":"orders.main",
                "partitions": [....]
          }
    }

Describe alternatives you've considered

There is no real alternative to build specific partitions.

Additional context

In our pipeline (DBT), we have the knowledge about which partition became stale, so that cube has to rebuild the pre-aggregation cache for this specific partition.

[dfagnan]

Is this related? https://cube.dev/docs/guides/recipes/query-acceleration/refreshing-select-partitions

I'm thinking that triggering the refresh schedule via API might bypass the refresh key so isn't helpful as a workaround?

[github-actions]

If you are interested in working on this issue, please provide go ahead and provide PR for that.
We'd be happy to review it and merge it.
If this is the first time you are contributing a Pull Request to Cube, please check our contribution guidelines.
You can also post any questions while contributing in the #contributors channel in the Cube Slack.

[AmandineScopely]

Is this related? https://cube.dev/docs/guides/recipes/query-acceleration/refreshing-select-partitions

I'm thinking that triggering the refresh schedule via API might bypass the refresh key so isn't helpful as a workaround?

The link you sent might work for some use cases but in our case it doesn't. Today's data is actualized every 15 minutes via an ETL, and we also actualize the whole table (all partitions) once a day in the morning. Right now, only one refresh schedule is allowed by Cube so for consistency of the data, we choose to refresh the whole pre-aggregation (all the partitions) in the refresh schedule, so all the data are refreshed every 15 minutes. But only the last data change regularly so this is a waste of resources!
I've seen that the cubejs-system API already have this implemented, because you can manually select and rebuild partitions of a pre-aggregation from the Cube Cloud UI. The idea would be to transform the "preAggregation" string array to an array of objects with "id" and "partitions". For example, for a pre-aggregation partitioned by day, the payload would be:

{
    "action": "post",
    "selector": {
      "contexts": [{ "securityContext": {} }],
      "timezones": ["UTC"],
      "preAggregations": [{
        "id": "orders.main",
        "partitions": ["prod_pre_aggregations.orders_main20240327"]
        }]
    }
}

as suggested by @siamak-haschemi above.

[lvauvillier]

A possible workaround is to list partition tables from cubestore and manually delete them. The Cube worker will automatically rebuild them.

This can be done using a script:

const { CubeStoreDriver } = require("@cubejs-backend/cubestore-driver");
const schema = "prod_pre_aggregations";

async function main() {
  const client = new CubeStoreDriver({ host: "localhost" });
  // list preaggregations
  const tables = await client.getTablesQuery(schema);
  
  // delete specific preaggregation
  await client.dropTable(`${schema}.events_master20241113_hu5p5qzo_z4yoetyq_1jjaj1d`);

}

main();

We use this internally to save rebuilding costs.

[igorlukanin]

Implemented in #9342.

[igorlukanin]

Please check it out in v1.2.25.

See selector.dateRange in the reference docs: https://cube.dev/docs/product/apis-integrations/rest-api/reference#base_pathv1pre-aggregationsjobs