目录
背景
使用OLAP进行建模,会创建模型表A,用户能够感知的表也是A,因此在使用时只针对A做查询操作。但因为底层引擎使用的是Kylin,它是否能够识别A并精准的命中CUBE?此为本次的起因
模型配置
选择事实表KYLIN_ACCOUNT 和 维度表KYLIN_COUNTRY
Cube构建
查询SQL
案例一
SELECT
*
FROM
(
SELECT
KYLIN_COUNTRY.COUNTRY,
max(
KYLIN_ACCOUNT.ACCOUNT_SELLER_LEVEL
)
FROM
KYLIN_ACCOUNT KYLIN_ACCOUNT
LEFT JOIN KYLIN_COUNTRY KYLIN_COUNTRY ON KYLIN_ACCOUNT.ACCOUNT_COUNTRY = KYLIN_COUNTRY.COUNTRY
GROUP BY
KYLIN_COUNTRY.COUNTRY
案例二
SELECT
count(ACCOUNT_SELLER_LEVEL)
FROM
(
SELECT
KYLIN_COUNTRY.COUNTRY,
max(
KYLIN_ACCOUNT.ACCOUNT_SELLER_LEVEL
) AS ACCOUNT_SELLER_LEVEL
FROM
KYLIN_ACCOUNT KYLIN_ACCOUNT
LEFT JOIN KYLIN_COUNTRY KYLIN_COUNTRY ON KYLIN_ACCOUNT.ACCOUNT_COUNTRY = KYLIN_COUNTRY.COUNTRY
GROUP BY
KYLIN_COUNTRY.COUNTRY
)
案例三:转义的SQL
SELECT
COUNTRY,
max(ACCOUNT_SELLER_LEVEL)
FROM
(
SELECT
KYLIN_ACCOUNT.ACCOUNT_BUYER_LEVEL,
KYLIN_ACCOUNT.ACCOUNT_COUNTRY,
KYLIN_COUNTRY.COUNTRY,
KYLIN_COUNTRY. NAME,
KYLIN_ACCOUNT.ACCOUNT_SELLER_LEVEL
FROM
KYLIN_ACCOUNT KYLIN_ACCOUNT
LEFT JOIN KYLIN_COUNTRY KYLIN_COUNTRY ON KYLIN_ACCOUNT.ACCOUNT_COUNTRY = KYLIN_COUNTRY.COUNTRY
)
GROUP BY
COUNTRY,
ACCOUNT_SELLER_LEVEL
案例四:直接按照模型名查询
结论
SQL的最内层必须拼装成Kylin的SQL(事实表 left join 维度表 形式)且命中了CUBE才能正确被Kylin查询,其他形式的SQL语句均会会报错(或者需要Kylin进行下推实现)