[
https://issues.apache.org/jira/browse/KYLIN-5730?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
liyang closed KYLIN-5730.
-------------------------
> Query dry-run for better modeling
> ---------------------------------
>
> Key: KYLIN-5730
> URL: https://issues.apache.org/jira/browse/KYLIN-5730
> Project: Kylin
> Issue Type: Improvement
> Affects Versions: 5.0-beta
> Reporter: Xiaoxiang Yu
> Assignee: Xiaoxiang Yu
> Priority: Major
> Fix For: 5.0.0
>
> Attachments: KYLIN-5730-01.png, KYLIN-5730-02.png, KYLIN-5730-03.png
>
>
> h2. Background
> When user enable this feature, the query insight page will display helpful
> message
> for user to understand why model is not match and what to do in next step.
>
> Following messages will display some query analytics, including at least:
> # RelNode Tree
> # OLAPContext and matched Model for each context
> # Spark Physical Plan
> Configuration entry is 'kylin.query.dryrun-enabled', at project level.
> h2. How to use
> # enable this in project configuration
> # send a query in Insight page
> # read Dry-run message and fix something and retry
>
> h4. Dry-run message sample:
> # {{Last Exception :}}
> {{{} No realization found for {id = 0, model = not matched, fact table =
> TPCH_CN.LINEITEM{}}}}
> {{ Incapable message : MODEL_UNMATCHED_JOIN, MODEL_UNMATCHED_JOIN,
> MODEL_UNMATCHED_JOIN, MODEL_UNMATCHED_JOIN, MODEL_UNMATCHED_JOIN,
> MODEL_UNMATCHED_JOIN, MODEL_UNMATCHED_JOIN, MODEL_UNMATCHED_JOIN, }}
> # {{OLAPContext(s) and matched model(s) :}}
> {{ Ctx=0 is not matched by any model/snapshot, recommend :}}
> {{ {}}
> {{ "Fact Table" : "TPCH_CN.LINEITEM",}}
> {{ "Dimension Tables" : [ "TPCH_CN.REGION", "TPCH_CN.ORDERS",
> "TPCH_CN.SUPPLIER", "TPCH_CN.PART", "TPCH_CN.CUSTOMER", "TPCH_CN.NATION"],}}
> {{ "Query Columns" : [ "TPCH_CN.NATION.N_NAME",
> "TPCH_CN.ORDERS.O_ORDERDATE", "TPCH_CN.REGION.R_NAME", "TPCH_CN.PART.P_TYPE",
> "TPCH_CN.LINEITEM.DISC_PRICE"],}}
> {{ "Dimension(Group by)" : [ "TPCH_CN.ORDERS.O_ORDERDATE"],}}
> {{ "Dimension(Filter cond)" : [ "TPCH_CN.ORDERS.O_ORDERDATE",
> "TPCH_CN.REGION.R_NAME", "TPCH_CN.PART.P_TYPE"],}}
> {{ "Measure" : [ "SUM(UNKNOWN_ALIAS.$F1)",
> "SUM(T_1_4CD6601C.DISC_PRICE)"],}}
> {{ "Join" : "}}
> {{Fact: [T_1_4CD6601C:LINEITEM]}}
> {{ Dim: [T_1_4CD6601C:LINEITEM] INNER JOIN [T_2_716C1DB8:PART] ON
> [L_PARTKEY] = [P_PARTKEY]}}
> {{ Dim: [T_1_4CD6601C:LINEITEM] INNER JOIN [T_3_6D21031C:SUPPLIER] ON
> [L_SUPPKEY] = [S_SUPPKEY]}}
> {{ Dim: [T_3_6D21031C:SUPPLIER] INNER JOIN [T_7_4F00CD31:NATION] ON
> [S_NATIONKEY] = [N_NATIONKEY]}}
> {{ Dim: [T_1_4CD6601C:LINEITEM] INNER JOIN [T_4_5CEAD9DF:ORDERS] ON
> [L_ORDERKEY] = [O_ORDERKEY]}}
> {{ Dim: [T_4_5CEAD9DF:ORDERS] INNER JOIN [T_5_54B979E7:CUSTOMER] ON
> [O_CUSTKEY] = [C_CUSTKEY]}}
> {{ Dim: [T_5_54B979E7:CUSTOMER] INNER JOIN [T_6_42081A08:NATION] ON
> [C_NATIONKEY] = [N_NATIONKEY]}}
> {{ Dim: [T_6_42081A08:NATION] INNER JOIN [T_8_B14C00A:REGION] ON
> [N_REGIONKEY] = [R_REGIONKEY]",}}
> {{ "Index Id" : 0,}}
> {{ "Query Column / Index Column" : "5 / 0",}}
> {{ "Index Columns" : []}}
> {{{} }{{}}}}
> # {{RelNode(with ctx id) :}}
> {{ OLAPToEnumerableConverter}}
> {{ LimitRel(ctx=[0@null], fetch=[500])}}
> {{ SortRel(sort0=[$0], dir0=[ASC-nulls-first], ctx=[0@null])}}
> {{ ProjectRel(O_YEAR=[$0], MKT_SHARE=[/($1, $2)], ctx=[0@null])}}
> {{ AggregateRel(group-set=[[0]], groups=[null], agg#0=[SUM($1)],
> agg#1=[SUM($2)], ctx=[0@null])}}
> {{ ProjectRel(O_YEAR=[EXTRACT(FLAG(YEAR), $54)], $f1=[CASE(=($72,
> 'BRAZIL'), $16, 0)], VOLUME=[$16], ctx=[0@null])}}
> {{ FilterRel(condition=[AND(=($76, 'AMERICA'), >=($54,
> 1995-01-01), <=($54, 1996-12-31), =($38, 'ECONOMY ANODIZED STEEL'))],
> ctx=[0@null])}}
> {{ JoinRel(condition=[=($69, $75)], joinType=[inner],
> ctx=[0@null])}}
> {{ JoinRel(condition=[=($46, $71)], joinType=[inner],
> ctx=[0@null])}}
> {{ JoinRel(condition=[=($62, $67)], joinType=[inner],
> ctx=[0@null])}}
> {{ JoinRel(condition=[=($51, $59)], joinType=[inner],
> ctx=[0@null])}}
> {{ JoinRel(condition=[=($0, $50)], joinType=[inner],
> ctx=[0@null])}}
> {{ JoinRel(condition=[=($2, $43)], joinType=[inner],
> ctx=[0@null])}}
> {{ JoinRel(condition=[=($1, $34)], joinType=[inner],
> ctx=[0@null])}}
> {{ TableScan(table=[[TPCH_CN, LINEITEM]],
> ctx=[0@null], fields=[[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15,
> 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33]])}}
> {{ TableScan(table=[[TPCH_CN, PART]],
> ctx=[0@null], fields=[[0, 1, 2, 3, 4, 5, 6, 7, 8]])}}
> {{ TableScan(table=[[TPCH_CN, SUPPLIER]],
> ctx=[0@null], fields=[[0, 1, 2, 3, 4, 5, 6]])}}
> {{ TableScan(table=[[TPCH_CN, ORDERS]], ctx=[0@null],
> fields=[[0, 1, 2, 3, 4, 5, 6, 7, 8]])}}
> {{ TableScan(table=[[TPCH_CN, CUSTOMER]], ctx=[0@null],
> fields=[[0, 1, 2, 3, 4, 5, 6, 7]])}}
> {{ TableScan(table=[[TPCH_CN, NATION]], ctx=[0@null],
> fields=[[0, 1, 2, 3]])}}
> {{ TableScan(table=[[TPCH_CN, NATION]], ctx=[0@null],
> fields=[[0, 1, 2, 3]])}}
> {{ TableScan(table=[[TPCH_CN, REGION]], ctx=[0@null],
> fields=[[0, 1, 2]])}}
> # {{SQL Text :}}
> {{select /*+ MODEL_PRIORITY(Q8) */ o_year,}}
> {{ sum(case}}
> {{ when nation = 'BRAZIL'}}
> {{ then volume}}
> {{ else 0}}
> {{ end) / sum(volume)}}
> {{ as mkt_share}}
> {{from}}
> {{ (}}
> {{ select year(o_orderdate) as o_year,}}
> {{ LINEITEM.DISC_PRICE as volume,}}
> {{ n2.n_name as nation}}
> {{ from}}
> {{ tpch_cn.lineitem}}
> {{ join tpch_cn.part on p_partkey = l_partkey}}
> {{ join tpch_cn.supplier on s_suppkey = l_suppkey}}
> {{ join tpch_cn.orders on l_orderkey = o_orderkey}}
> {{ join tpch_cn.customer on o_custkey = c_custkey}}
> {{ join tpch_cn.nation n1 on c_nationkey = n1.n_nationkey}}
> {{ join tpch_cn.nation n2 on s_nationkey = n2.n_nationkey}}
> {{ join tpch_cn.region on n1.n_regionkey = r_regionkey}}
> {{ where}}
> {{ r_name = 'AMERICA'}}
> {{ and o_orderdate between '1995-01-01' and '1996-12-31'}}
> {{ and p_type = 'ECONOMY ANODIZED STEEL'}}
> {{ ) as all_nations}}
> {{group by}}
> {{ o_year}}
> {{order by}}
> {{ o_year}}
> {{LIMIT 500}}
> # {{Physical plan :}}
> {{ not exists}}
> {{<-------------------- Dry Run Info -------------------->}}
>
> h2. Screenshots
> h4. Step1 : enable this in project configuration
> !KYLIN-5730-01.png!
> h4. Step2 : send a query in Insight page
> !KYLIN-5730-02.png!
> h4. Step3 : Dry-run message in Insight page
> !KYLIN-5730-03.png!
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
