Skip to main content

CubeJS Schema

  • dimensions
    • 单个数据点的 属性
    • Time Dimension
    • type:
      • time, string, number, boolean, geo
    • format:
      • imageUrl, link, currency, percent
      • id - 避免针对 number 加分隔符
  • measures
    • set of data points
    • type:
      • number - 用于计算 measure
      • count - 不需要写 sql,一般会配合下钻
      • countDistinct - COUNT DISTINCT
      • countDistinctApprox
        • 能使用 rollup pre-aggregations
        • 可能使用 HyperLogLog
      • sum, avg, min, max, runningTotal
    • format: percent, currency
    • drillMembers:
      • 下钻纬度 - 会默认包含这些下钻属性
      • 可用于构建更丰富的客户端
  • joins
    • relationship: belongsTo,hasMany,hasOne
    • sql
    • 均为 LEFT JOIN
      • 通过 IS NOT NULL 达到 INNER JOIN
  • segments
    • 预定义在 schema 中的过滤条件
    • 建议用于复杂查询,简单场景使用 filter
  • preAggregations
    • 预聚合
  • 上下文变量
    • FILTER_PARAMS - FILTER_PARAMS.<CUBE_NAME>.<FILTER_NAME>.filter(expression)
    • SECURITY_CONTEXT
      • 推荐尽量使用 queryRewrite
    • SQL_UTILS
      • convertTz('created_at')
    • COMPILE_CONTEXT - 编译上下文
  • partition
    • rollup 基于分片
    • 分片通常是时间纬度
    • 时间维度+时间精度产生不同粒度的分片
cube(`Users`, {
sql: `select * from public.users`,
title: `用户`,
description: '所有的系统用户',
// extends: BaseEntity // 继承
// sqlAlias: `usr`,

joins: {
// key 必需为 Cube 名字
// 意味着相同关系只能 join 一个,除非创建多个相同 cube
Teams: {
relationship: `belongsTo`,
sql: `${Users}.team_id = ${Teams}.id`,
},
},
segments: {
activitiedUsers: {
sql: `${CUBE}.state = 'activitied'`,
},
},
preAggregations: {
usersByStatus: {
// rollup - 默认 - count, sum, min, max, countDistinctApprox
// originalSql - 特殊情况使用 - 例如 漏斗,很复杂的 SQL
// rollupJoin - 支持 join
type: 'rollup',
dimensions: [CUBE.status],
measures: [CUBE.count],
timeDimension: CUBE.createdAt,
// hour, day, week, month, quarter, year
granularity: `day`,
segments: [CUBE.activitiedUsers],
// 分区粒度
partitionGranularity: `month`,
// 刷新 key
refreshKey: {
// 无 sql 默认 1 hour
// 有 sql 默认 10 seconds
every: `1 day`,
// 整理 rollup - 默认 true
incremental: true,
// 增量窗口
updateWindow: `7 day`,
},
useOriginalSqlPreAggregations: true,
// 定时刷新
scheduledRefresh: false,
// 范围定义
buildRangeStart: {
sql: `SELECT NOW() - interval '300 day'`,
},
buildRangeEnd: {
sql: `SELECT NOW()`,
},
//
indexes: {
categoryIndex: {
columns: [CUBE.category],
},
timestampIndex: {
columns: ['timestamp'],
},
},
},
// rollup join
usersRollup: {
type: 'rollupJoin',
dimensions: [Teams.teamsRollup, CUBE.usersByStatus],
},
// sql
main: {
type: `originalSql`,
},
},
measures: {
count: {
sql: `id`,
type: `count`,
title: '总数',
description: '用户数量',
shown: true, // 是否可见
// 自定义过滤查询
filters: [{ sql: `${CUBE}.status = 'activited'` }],
// 滚动窗口
rollingWindow: {
// (-?\d+) (minute|hour|day|week|month|year)
// unbounded
trailing: `1 month`,
leading: '',
offset: '', // start|end
},
// 下钻纬度
drillMembers: [id, status, Products.name, Users.email],
// 传递到前端的自定义信息
meta: {
any: 'value',
},
},

payingCount: {
sql: `id`,
type: `count`,
filters: [{ sql: `${CUBE}.paying = 'true'` }],
},

payingPercentage: {
// 计算值
sql: `100.0 * ${payingCount} / ${count}`,
type: `number`,
format: `percent`,
},

// 引用 subquery
averageTeamsScore: {
sql: `${teamsScore}`,
type: `avg`,
},
},
// 纬度
dimensions: {
id: {
sql: `id`,
type: `string`,
// 会用于去重,join
// shown 会默认为 false
primaryKey: true,
},
age: {
sql: 'age',
type: 'number',
title: '年龄',
description: '用户活了几多年',
shown: true,
case: {
when: [
{ sql: `${CUBE}.age < 2`, label: `baby` },
{ sql: `${CUBE}.age < 18`, label: `young` },
{ sql: `${CUBE}.age < 60`, label: { sql: `${CUBE}.job` } }, // label 也可以引用
],
else: { label: `老人` },
},
meta: {
any: 'value',
},
},

// 时间维度
createdAt: {
sql: `created_at`,
type: `time`,
},

completedAt: {
sql: `completed_at`,
type: `time`,
},
// 子查询
// - 必需先定义 join
// - 引用 measure
teamsScore: {
sql: `${Teams.score}`,
type: `number`,
subQuery: true, // 声明子查询
propagateFiltersToSubQuery: true, // 传递查询到子查询
},
},

// SQL 生成后是否允许 cube 重写查询 - 可进行一定优化
rewriteQueries: false,
// 缓存刷新 key
refreshKey: {
// 1. evert interval
every: '2 minute', // 默认 BigQuery, Athena, Snowflake, Presto
every: '10 second', // 其他 DB 默认
// 2. 自定义
sql: `SELECT MAX(update_at) FROM users`,
// 3. corn
every: '30 5 * * 5',
timezone: 'Asia/Shanghai',
},
// 数据源
dataSource: `default`,
});

// 抽象 Cube - 用于 extends
const Users = cube({
sql: `select * from users`,

dimensions: {
// primary key,
name: {
sql: `${CUBE}.name`,
type: `string`,
},
},
});

cube(`BaseEvents`, {
sql: `select * from events`,
});

// 继承 sql, joins, measures, dimensions
cube(`PageViews`, {
sql: `select * from page_views`,
extends: BaseEvents,

dimensions: {
pagePath: {
sql: `page_path`,
type: `string`,
},
},
});

// Data Blending/数据混合 - 融合多组数据
cube(`AllSales`, {
sql: `
SELECT
amount,
user_id AS customer_id,
created_at,
'OnlineOrders' AS row_type
FROM ${OnlineOrders.sql()}
UNION ALL
SELECT
amount,
customer_id,
created_at,
'Orders' AS row_type
FROM ${RetailOrders.sql()}
`,
});

预聚合

cube(`Users`, {
preAggregations: {
main: {
measures: [CUBE.count],
refreshKey: {
every: `1 hour`,
incremental: true,
updateWindow: `7 day`,
},
},
},
});
external db
CUBEJS_EXT_DB_HOST=YOUR_DB_HOST_HERE
CUBEJS_EXT_DB_PORT=YOUR_DB_PORT_HERE
CUBEJS_EXT_DB_NAME=YOUR_DB_NAME_HERE
CUBEJS_EXT_DB_USER=YOUR_DB_USER_HERE
CUBEJS_EXT_DB_PASS=YOUR_DB_PASS_HERE
CUBEJS_EXT_DB_TYPE=SUPPORTED_DB_TYPE_HERE