上下文变量提供了比较强大的cube.js 自定义处理，目前cube.js 提供了filter，user context
sql utils，compile context,unsafe value
filter params
基于FILTER_PARAMS 允许基于filter 在sql 生成的时候
- 参考格式
FILTER_PARAMS.<CUBE_NAME>.<FILTER_NAME>.filter(expression)
- 说明
expression 可以是字符串以及函数
比如
cube(`OrderFacts`, {
sql: `SELECT * FROM orders WHERE ${FILTER_PARAMS.OrderFacts.date.filter('date')}`,
measures: {
count: {
type: `count`
}
},
dimensions: {
date: {
sql: `date`,
type: `time`
}
}
});
查询
{
measures: ['OrderFacts.count'],
timeDimensions: [{
dimension: 'OrderFacts.date',
granularity: 'day',
dateRange: ['2018-01-01', '2018-12-31']
}]
}
对于['2018-01-01', '2018-12-31']区间的查询sql如下
SELECT * FROM orders WHERE date >= '2018-01-01 00:00:00' and date <= '2018-12-31 23:59:59'
函数格式
cube(`Events`, {
sql: `
SELECT * FROM schema.\`events*\`
WHERE ${FILTER_PARAMS.Events.date.filter((from, to) =>
`_TABLE_SUFFIX >= FORMAT_TIMESTAMP('%Y%m%d', TIMESTAMP(${from})) AND _TABLE_SUFFIX <= FORMAT_TIMESTAMP('%Y%m%d', TIMESTAMP(${to}))`
)}
`,
dimensions: {
date: {
sql: `date`,
type: `time`
}
}
});
user context
- 参考格式
cube(`Orders`, {
sql: `SELECT * FROM orders WHERE ${USER_CONTEXT.email.filter('email')}`,
dimensions: {
date: {
sql: `date`,
type: `time`
}
}
});
对于必须的可以使用requiredFilter
cube(`Orders`, {
sql: `SELECT * FROM orders WHERE ${USER_CONTEXT.email.requiredFilter('email')}`,
dimensions: {
date: {
sql: `date`,
type: `time`
}
}
});
sql utils
主要进行时区的转换处理
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
可以实现类似RequestContext 的处理
const { authInfo: { deploymentId } } = COMPILE_CONTEXT;
const schemaName = `user_${deploymentId}`;
cube(`Users`, {
sql: `select * from ${schemaName}.users`,
// ...
});