Cube.js: Pre-aggregation is not working with cockroach db

[sheik-syed]

Pre-aggregation is not working with the below configuration in cockroach DB.
I created a test app with Postgres parameter
eg: cubejs create test-app -d postgres
but actually im using cockroach db.
Remaining functionality working except preaggregation.
Please help me to resolve this issue.

Schema
cube(Orders, {
sql: SELECT * FROM public.orders,

joins: {

},
measures: {
count: {
type: count,
drillMembers: [id, createdAt]
},
amount: {
sql: amount,
type: sum
}
},
dimensions: {
id: {
sql: id,
type: number,
primaryKey: true
},
createdAt: {
sql: created_at,
type: time
}
},
preAggregations: {
categoryAndDate: {
type: rollup,
measureReferences: [Orders.count],
timeDimensionReference: createdAt,
granularity: day
}
}
});
]

Console Error

Performing query completed: 0c88d95f-2c29-4e03-8116-7f0ee07e5395-span-1 (812ms)
Error querying db: 6ac1cedc-1221-4c41-a37a-431f9aa3d441-span-1

"SELECT \"orders__created_at_day\" \"orders__created_at_day\", sum(\"orders__count\") \"orders__count\" FROM stb_pre_aggregations.orders_category_and_date GROUP BY 1 ORDER BY 1 ASC LIMIT 10000"

{
"params": []
}
error: at or near "if": syntax error: unimplemented: this syntax
at Parser.parseErrorMessage (/Users/sheik/SSS/cube-js/testme/test5/node_modules/pg-protocol/dist/parser.js:278:15)
at Parser.handlePacket (/Users/sheik/SSS/cube-js/testme/test5/node_modules/pg-protocol/dist/parser.js:126:29)
at Parser.parse (/Users/sheik/SSS/cube-js/testme/test5/node_modules/pg-protocol/dist/parser.js:39:38)
at TLSSocket.stream.on (/Users/sheik/SSS/cube-js/testme/test5/node_modules/pg-protocol/dist/index.js:8:42)
at emitOne (events.js:116:13)
at TLSSocket.emit (events.js:211:7)
at addChunk (_stream_readable.js:263:12)
at readableAddChunk (_stream_readable.js:250:11)
at TLSSocket.Readable.push (_stream_readable.js:208:10)
at TLSWrap.onread (net.js:597:20)
Orchestrator error: 6ac1cedc-1221-4c41-a37a-431f9aa3d441-span-1 (4008ms)

[ovr]

Hello!

Currently, Cube does not support cockroach and community don't provide the driver for it.

IMHO:

It's a bad idea to use PostgreSQL driver to work with CockroachDB, because each driver defines BaseQuery and BaseFilter to generate SQL, specially for the database.

cC @cube-js/core Any plans to support CockroachDB?

[sheik-syed]

Thanks for your response. Please suggest any workaround for cockroach db support or when cube-js support cockroach db

[ovr]

any workaround for cockroach db support

You can use https://cube.dev/docs/pre-aggregations#external-pre-aggregations.

1. aggregate from CockroachDB
2. write to PostgreSQL (for example, or any another writable driver/db)

when cube-js support cockroach db

Currently, there are no plans to add support for it (from core team). But feel free to prepare a PR with the driver. If you are going to create the driver, i highly recommend taking a look at

1. https://github.com/cube-js/cube.js/blob/master/CONTRIBUTING.md#implementing-sql-dialect
2. https://github.com/cube-js/cube.js/pull/1099 (as an example)

[sheik-syed]

Thank you