Repository: calcite Updated Branches: refs/heads/master 6284d3c88 -> 9c26a9e74
http://git-wip-us.apache.org/repos/asf/calcite/blob/b47413a1/core/src/test/java/org/apache/calcite/test/LatticeTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/LatticeTest.java b/core/src/test/java/org/apache/calcite/test/LatticeTest.java index aef9b6a..a5e70dc 100644 --- a/core/src/test/java/org/apache/calcite/test/LatticeTest.java +++ b/core/src/test/java/org/apache/calcite/test/LatticeTest.java @@ -118,6 +118,35 @@ public class LatticeTest { + " } ]\n" + "}\n"; + private static final String AUTO_LATTICE = "{\n" + + " name: 'star',\n" + + " sql: [\n" + + " 'select 1 from \"foodmart\".\"sales_fact_1997\" as \"s\"',\n" + + " 'join \"foodmart\".\"product\" as \"p\" using (\"product_id\")',\n" + + " 'join \"foodmart\".\"time_by_day\" as \"t\" using (\"time_id\")',\n" + + " 'join \"foodmart\".\"product_class\" as \"pc\" on \"p\".\"product_class_id\" = \"pc\".\"product_class_id\"'\n" + + " ],\n" + + " auto: false,\n" + + " algorithm: true,\n" + + " algorithmMaxMillis: 10000,\n" + + " rowCountEstimate: 86837,\n" + + " defaultMeasures: [ {\n" + + " agg: 'count'\n" + + " } ],\n" + + " tiles: [ {\n" + + " dimensions: [ 'the_year', ['t', 'quarter'] ],\n" + + " measures: [ {\n" + + " agg: 'sum',\n" + + " args: 'unit_sales'\n" + + " }, {\n" + + " agg: 'sum',\n" + + " args: 'store_sales'\n" + + " }, {\n" + + " agg: 'count'\n" + + " } ]\n" + + " } ]\n" + + "}\n"; + private static CalciteAssert.AssertThat modelWithLattice(String name, String sql, String... extras) { final StringBuilder buf = new StringBuilder("{ name: '") @@ -187,6 +216,28 @@ public class LatticeTest { }); } + /** Tests some of the properties of the {@link Lattice} data structure. */ + @Test public void testLattice() throws Exception { + modelWithLattice("star", + "select 1 from \"foodmart\".\"sales_fact_1997\" as s\n" + + "join \"foodmart\".\"product\" as p using (\"product_id\")\n" + + "join \"foodmart\".\"time_by_day\" as t on t.\"time_id\" = s.\"time_id\"") + .doWithConnection(c -> { + final SchemaPlus schema = c.getRootSchema(); + final SchemaPlus adhoc = schema.getSubSchema("adhoc"); + assertThat(adhoc.getTableNames().contains("EMPLOYEES"), is(true)); + final Map.Entry<String, CalciteSchema.LatticeEntry> entry = + adhoc.unwrap(CalciteSchema.class).getLatticeMap().firstEntry(); + final Lattice lattice = entry.getValue().getLattice(); + assertThat(lattice.firstColumn("S"), is(10)); + assertThat(lattice.firstColumn("P"), is(18)); + assertThat(lattice.firstColumn("T"), is(0)); + assertThat(lattice.firstColumn("PC"), is(-1)); + assertThat(lattice.defaultMeasures.size(), is(1)); + assertThat(lattice.rootNode.descendants.size(), is(3)); + }); + } + /** Tests that it's OK for a lattice to have the same name as a table in the * schema. */ @Test public void testLatticeWithSameNameAsTable() { @@ -259,6 +310,15 @@ public class LatticeTest { .connectThrows("only inner join allowed, but got LEFT"); } + /** Each lattice table must have a parent. */ + @Test public void testLatticeInvalidSql3() { + modelWithLattice("star", + "select 1 from \"foodmart\".\"sales_fact_1997\" as s\n" + + "join \"foodmart\".\"product\" as p using (\"product_id\")\n" + + "join \"foodmart\".\"time_by_day\" as t on s.\"product_id\" = p.\"product_id\"") + .connectThrows("child node must have precisely one parent"); + } + /** When a lattice is registered, there is a table with the same name. * It can be used for explain, but not for queries. */ @Test public void testLatticeStarTable() { @@ -353,7 +413,7 @@ public class LatticeTest { + "from \"foodmart\".\"sales_fact_1997\" as s\n" + "join \"foodmart\".\"time_by_day\" as t using (\"time_id\")\n") .enableMaterializations(true) - .explainContains("EnumerableTableScan(table=[[adhoc, m{27, 31}") + .explainContains("EnumerableTableScan(table=[[adhoc, m{32, 36}") .returnsCount(4); } @@ -368,7 +428,7 @@ public class LatticeTest { .enableMaterializations(true) .explainContains("" + "EnumerableCalc(expr#0..4=[{inputs}], proj#0..2=[{exprs}])\n" - + " EnumerableTableScan(table=[[adhoc, m{27, 31}") + + " EnumerableTableScan(table=[[adhoc, m{32, 36}") .returnsUnordered("the_year=1997; quarter=Q1; C=21588", "the_year=1997; quarter=Q2; C=20368", "the_year=1997; quarter=Q3; C=21453", @@ -392,7 +452,7 @@ public class LatticeTest { .explainContains("" + "EnumerableCalc(expr#0..3=[{inputs}], expr#4=[10], expr#5=[*($t3, $t4)], proj#0..2=[{exprs}], US=[$t5])\n" + " EnumerableAggregate(group=[{0}], C=[$SUM0($2)], Q=[MIN($1)], agg#2=[$SUM0($4)])\n" - + " EnumerableTableScan(table=[[adhoc, m{27, 31}") + + " EnumerableTableScan(table=[[adhoc, m{32, 36}") .enable(CalciteAssert.DB != CalciteAssert.DatabaseInstance.ORACLE) .returnsUnordered("the_year=1997; C=86837; Q=Q1; US=2667730.0000") .sameResultWithMaterializationsDisabled(); @@ -407,7 +467,7 @@ public class LatticeTest { * materialize</a>. */ @Test public void testTileAlgorithm() { final String explain = "EnumerableAggregate(group=[{2, 3}])\n" - + " EnumerableTableScan(table=[[adhoc, m{16, 17, 27, 31, 32, 37}]])"; + + " EnumerableTableScan(table=[[adhoc, m{16, 17, 32, 36, 37}]])"; checkTileAlgorithm( FoodMartLatticeStatisticProvider.class.getCanonicalName() + "#FACTORY", explain); @@ -418,8 +478,8 @@ public class LatticeTest { @Test public void testTileAlgorithm2() { // Different explain than above, but note that it still selects columns // (27, 31). - final String explain = "EnumerableAggregate(group=[{0, 1}])\n" - + " EnumerableTableScan(table=[[adhoc, m{27, 31, 32, 36, 37}]"; + final String explain = "EnumerableAggregate(group=[{4, 5}])\n" + + " EnumerableTableScan(table=[[adhoc, m{16, 17, 27, 31, 32, 36, 37}]"; checkTileAlgorithm(Lattices.class.getCanonicalName() + "#CACHED_SQL", explain); } @@ -429,8 +489,8 @@ public class LatticeTest { @Test public void testTileAlgorithm3() { Assume.assumeTrue("Yahoo sketches requires JDK 8 or higher", TestUtil.getJavaMajorVersion() >= 8); - final String explain = "EnumerableAggregate(group=[{0, 1}])\n" - + " EnumerableTableScan(table=[[adhoc, m{27, 31, 32, 36, 37}]"; + final String explain = "EnumerableAggregate(group=[{4, 5}])\n" + + " EnumerableTableScan(table=[[adhoc, m{16, 17, 27, 31, 32, 36, 37}]"; checkTileAlgorithm(Lattices.class.getCanonicalName() + "#PROFILER", explain); } @@ -580,7 +640,7 @@ public class LatticeTest { .enableMaterializations(true) .explainContains("EnumerableCalc(expr#0..1=[{inputs}], C=[$t1])\n" + " EnumerableAggregate(group=[{0}], C=[COUNT($1)])\n" - + " EnumerableTableScan(table=[[adhoc, m{27, 31}]])") + + " EnumerableTableScan(table=[[adhoc, m{32, 36}]])") .returnsUnordered("C=4"); } @@ -594,7 +654,7 @@ public class LatticeTest { .explainContains("EnumerableCalc(expr#0..1=[{inputs}], C=[$t1])\n" + " EnumerableAggregate(group=[{0}], C=[COUNT($0)])\n" + " EnumerableAggregate(group=[{0}])\n" - + " EnumerableTableScan(table=[[adhoc, m{27, 31}]])") + + " EnumerableTableScan(table=[[adhoc, m{32, 36}]])") .returnsUnordered("C=1"); } @@ -622,8 +682,8 @@ public class LatticeTest { } private void check(int n) throws IOException { - final FoodmartTest.FoodmartQuery query = - FoodmartTest.FoodMartQuerySet.instance().queries.get(n); + final FoodMartQuerySet set = FoodMartQuerySet.instance(); + final FoodMartQuerySet.FoodmartQuery query = set.queries.get(n); if (query == null) { return; } @@ -757,13 +817,54 @@ public class LatticeTest { .returns("EXPR$0=1\n"); } + @Test public void testSuggester() { + final Class<JdbcTest.EmpDeptTableFactory> clazz = + JdbcTest.EmpDeptTableFactory.class; + final String model = "" + + "{\n" + + " version: '1.0',\n" + + " schemas: [\n" + + JdbcTest.FOODMART_SCHEMA + + ",\n" + + " {\n" + + " name: 'adhoc',\n" + + " tables: [\n" + + " {\n" + + " name: 'EMPLOYEES',\n" + + " type: 'custom',\n" + + " factory: '" + clazz.getName() + "',\n" + + " operand: {'foo': true, 'bar': 345}\n" + + " }\n" + + " ],\n" + + " \"autoLattice\": true" + + " }\n" + + " ]\n" + + "}"; + final String sql = "select count(*)\n" + + "from \"sales_fact_1997\"\n" + + "join \"time_by_day\" using (\"time_id\")\n"; + final String explain = "PLAN=JdbcToEnumerableConverter\n" + + " JdbcAggregate(group=[{}], EXPR$0=[COUNT()])\n" + + " JdbcJoin(condition=[=($1, $0)], joinType=[inner])\n" + + " JdbcProject(time_id=[$0])\n" + + " JdbcTableScan(table=[[foodmart, time_by_day]])\n" + + " JdbcProject(time_id=[$1])\n" + + " JdbcTableScan(table=[[foodmart, sales_fact_1997]])\n"; + CalciteAssert.model(model) + .withDefaultSchema("foodmart") + .query(sql) + .returns("EXPR$0=86837\n") + .explainContains(explain); + } + private static CalciteAssert.AssertThat foodmartModel(String... extras) { - return modelWithLattice("star", - "select 1 from \"foodmart\".\"sales_fact_1997\" as \"s\"\n" - + "join \"foodmart\".\"product\" as \"p\" using (\"product_id\")\n" - + "join \"foodmart\".\"time_by_day\" as \"t\" using (\"time_id\")\n" - + "join \"foodmart\".\"product_class\" as \"pc\" on \"p\".\"product_class_id\" = \"pc\".\"product_class_id\"", - extras); + final String sql = "select 1\n" + + "from \"foodmart\".\"sales_fact_1997\" as \"s\"\n" + + "join \"foodmart\".\"product\" as \"p\" using (\"product_id\")\n" + + "join \"foodmart\".\"time_by_day\" as \"t\" using (\"time_id\")\n" + + "join \"foodmart\".\"product_class\" as \"pc\"\n" + + " on \"p\".\"product_class_id\" = \"pc\".\"product_class_id\""; + return modelWithLattice("star", sql, extras); } private CalciteAssert.AssertThat foodmartModelWithOneTile() { @@ -790,7 +891,7 @@ public class LatticeTest { final Connection connection = DriverManager.getConnection( "jdbc:calcite:model=core/src/test/resources/mysql-foodmart-lattice-model.json"); final ResultSet resultSet = connection.createStatement() - .executeQuery("select * from \"adhoc\".\"m{27, 31}\""); + .executeQuery("select * from \"adhoc\".\"m{32, 36}\""); System.out.println(CalciteAssert.toString(resultSet)); connection.close(); } http://git-wip-us.apache.org/repos/asf/calcite/blob/b47413a1/core/src/test/java/org/apache/calcite/test/UdfTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/UdfTest.java b/core/src/test/java/org/apache/calcite/test/UdfTest.java index 10c7458..4eac690 100644 --- a/core/src/test/java/org/apache/calcite/test/UdfTest.java +++ b/core/src/test/java/org/apache/calcite/test/UdfTest.java @@ -594,7 +594,9 @@ public class UdfTest { .throws_("No match found for function signature MY_SUM3(<NUMERIC>, " + "<NUMERIC>, <APPROXIMATE_NUMERIC>)"); with.query("select \"adhoc\".my_sum3(\"empid\",\"deptno\",\"name\") as p " - + "from \"adhoc\".EMPLOYEES\n"); + + "from \"adhoc\".EMPLOYEES\n") + .throws_("No match found for function signature MY_SUM3(<NUMERIC>, " + + "<NUMERIC>, <CHARACTER>)"); with.query("select \"adhoc\".my_sum2(\"commission\",250) as p " + "from \"adhoc\".EMPLOYEES\n") .returns("P=1500\n"); http://git-wip-us.apache.org/repos/asf/calcite/blob/b47413a1/core/src/test/java/org/apache/calcite/util/graph/DirectedGraphTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/util/graph/DirectedGraphTest.java b/core/src/test/java/org/apache/calcite/util/graph/DirectedGraphTest.java index 483a704..87c0600 100644 --- a/core/src/test/java/org/apache/calcite/util/graph/DirectedGraphTest.java +++ b/core/src/test/java/org/apache/calcite/util/graph/DirectedGraphTest.java @@ -18,6 +18,7 @@ package org.apache.calcite.util.graph; import com.google.common.collect.ImmutableList; import com.google.common.collect.ImmutableSet; +import com.google.common.collect.Iterables; import com.google.common.collect.Lists; import org.hamcrest.CoreMatchers; @@ -30,6 +31,9 @@ import java.util.List; import java.util.Set; import static org.hamcrest.CoreMatchers.equalTo; +import static org.hamcrest.CoreMatchers.is; +import static org.hamcrest.CoreMatchers.notNullValue; +import static org.hamcrest.CoreMatchers.nullValue; import static org.junit.Assert.assertEquals; import static org.junit.Assert.assertFalse; import static org.junit.Assert.assertNotNull; @@ -310,6 +314,72 @@ public class DirectedGraphTest { return list; } + @Test public void testAttributed() { + AttributedDirectedGraph<String, DefaultEdge> g = + AttributedDirectedGraph.create(new DefaultAttributedEdgeFactory()); + g.addVertex("A"); + g.addVertex("B"); + g.addVertex("C"); + g.addVertex("D"); + g.addVertex("E"); + g.addVertex("F"); + g.addEdge("A", "B", 1); + g.addEdge("B", "C", 1); + g.addEdge("D", "C", 1); + g.addEdge("C", "D", 1); + g.addEdge("E", "F", 1); + g.addEdge("C", "C", 1); + assertEquals("[A, B, C, D]", shortestPath(g, "A", "D").toString()); + g.addEdge("B", "D", 1); + assertEquals("[A, B, D]", shortestPath(g, "A", "D").toString()); + assertNull("There is no path from A to E", shortestPath(g, "A", "E")); + assertEquals("[]", shortestPath(g, "D", "D").toString()); + assertNull("Node X is not in the graph", shortestPath(g, "X", "A")); + assertEquals("[[A, B, C, D], [A, B, D]]", paths(g, "A", "D").toString()); + assertThat(g.addVertex("B"), is(false)); + + assertThat(Iterables.size(g.getEdges("A", "B")), is(1)); + assertThat(g.addEdge("A", "B", 1), nullValue()); + assertThat(Iterables.size(g.getEdges("A", "B")), is(1)); + assertThat(g.addEdge("A", "B", 2), notNullValue()); + assertThat(Iterables.size(g.getEdges("A", "B")), is(2)); + } + + /** Edge that stores its attributes in a list. */ + private static class DefaultAttributedEdge extends DefaultEdge { + private final List list; + + DefaultAttributedEdge(String source, String target, List list) { + super(source, target); + this.list = ImmutableList.copyOf(list); + } + + @Override public int hashCode() { + return super.hashCode() * 31 + list.hashCode(); + } + + @Override public boolean equals(Object obj) { + return this == obj + || obj instanceof DefaultAttributedEdge + && ((DefaultAttributedEdge) obj).source.equals(source) + && ((DefaultAttributedEdge) obj).target.equals(target) + && ((DefaultAttributedEdge) obj).list.equals(list); + } + } + + /** Factory for {@link DefaultAttributedEdge}. */ + private static class DefaultAttributedEdgeFactory + implements AttributedDirectedGraph.AttributedEdgeFactory<String, + DefaultEdge> { + public DefaultEdge createEdge(String v0, String v1, Object... attributes) { + return new DefaultAttributedEdge(v0, v1, + ImmutableList.copyOf(attributes)); + } + + public DefaultEdge createEdge(String v0, String v1) { + throw new UnsupportedOperationException(); + } + } } // End DirectedGraphTest.java http://git-wip-us.apache.org/repos/asf/calcite/blob/b47413a1/core/src/test/resources/hsqldb-foodmart-auto-lattice-model.json ---------------------------------------------------------------------- diff --git a/core/src/test/resources/hsqldb-foodmart-auto-lattice-model.json b/core/src/test/resources/hsqldb-foodmart-auto-lattice-model.json new file mode 100644 index 0000000..61ea2a2 --- /dev/null +++ b/core/src/test/resources/hsqldb-foodmart-auto-lattice-model.json @@ -0,0 +1,31 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to you under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +{ + "version": "1.0", + "defaultSchema": "foodmart", + "schemas": [ { + "type": "jdbc", + "name": "foodmart", + "jdbcUser": "FOODMART", + "jdbcPassword": "FOODMART", + "jdbcUrl": "jdbc:hsqldb:res:foodmart", + "jdbcSchema": "foodmart" + }, { + "name": "adhoc", + "autoLattice": true + } ] +} http://git-wip-us.apache.org/repos/asf/calcite/blob/b47413a1/plus/src/test/java/org/apache/calcite/materialize/TpcdsLatticeSuggesterTest.java ---------------------------------------------------------------------- diff --git a/plus/src/test/java/org/apache/calcite/materialize/TpcdsLatticeSuggesterTest.java b/plus/src/test/java/org/apache/calcite/materialize/TpcdsLatticeSuggesterTest.java new file mode 100644 index 0000000..0bb7f36 --- /dev/null +++ b/plus/src/test/java/org/apache/calcite/materialize/TpcdsLatticeSuggesterTest.java @@ -0,0 +1,206 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to you under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package org.apache.calcite.materialize; + +import org.apache.calcite.adapter.tpcds.TpcdsSchema; +import org.apache.calcite.config.CalciteConnectionConfigImpl; +import org.apache.calcite.config.CalciteConnectionProperty; +import org.apache.calcite.plan.Contexts; +import org.apache.calcite.prepare.CalcitePrepareImpl; +import org.apache.calcite.prepare.PlannerImpl; +import org.apache.calcite.rel.RelRoot; +import org.apache.calcite.schema.SchemaPlus; +import org.apache.calcite.sql.SqlNode; +import org.apache.calcite.sql.parser.SqlParseException; +import org.apache.calcite.sql.parser.SqlParser; +import org.apache.calcite.sql.validate.SqlConformanceEnum; +import org.apache.calcite.test.CalciteAssert; +import org.apache.calcite.tools.FrameworkConfig; +import org.apache.calcite.tools.Frameworks; +import org.apache.calcite.tools.Planner; +import org.apache.calcite.tools.RelConversionException; +import org.apache.calcite.tools.ValidationException; + +import net.hydromatic.tpcds.query.Query; + +import org.junit.Test; + +import java.util.List; +import java.util.Properties; +import java.util.Random; + +import static org.hamcrest.CoreMatchers.is; +import static org.junit.Assert.assertThat; + +/** + * Unit tests for {@link LatticeSuggester}. + */ +public class TpcdsLatticeSuggesterTest { + + private String number(String s) { + final StringBuilder b = new StringBuilder(); + int i = 0; + for (String line : s.split("\n")) { + b.append(++i).append(' ').append(line).append("\n"); + } + return b.toString(); + } + + private void checkFoodMartAll(boolean evolve) throws Exception { + final Tester t = new Tester().tpcds().withEvolve(evolve); + for (Query query : Query.values()) { + final String sql = query.sql(new Random(0)) + .replaceAll("as returns", "as \"returns\"") + .replaceAll("sum\\(returns\\)", "sum(\"returns\")") + .replaceAll(", returns", ", \"returns\"") + .replaceAll("14 days", "interval '14' day") + .replaceAll("substr\\(([^,]*),([^,]*),([^)]*)\\)", + "substring($1 from $2 for $3)"); + if (CalcitePrepareImpl.DEBUG) { + System.out.println("Query #" + query.id + "\n" + + number(sql)); + } + switch (query.id) { + case 6: + case 9: + continue; // NPE + } + if (query.id > 11) { + break; + } + t.addQuery(sql); + } + + // The graph of all tables and hops + final String expected = "graph(vertices: [" + + "[tpcds, CATALOG_SALES], " + + "[tpcds, CUSTOMER], " + + "[tpcds, CUSTOMER_ADDRESS], " + + "[tpcds, CUSTOMER_DEMOGRAPHICS], " + + "[tpcds, DATE_DIM], " + + "[tpcds, ITEM], " + + "[tpcds, PROMOTION], " + + "[tpcds, STORE], " + + "[tpcds, STORE_RETURNS], " + + "[tpcds, STORE_SALES], " + + "[tpcds, WEB_SALES]], " + + "edges: " + + "[Step([tpcds, CATALOG_SALES], [tpcds, CUSTOMER], CS_SHIP_CUSTOMER_SK:C_CUSTOMER_SK)," + + " Step([tpcds, CATALOG_SALES], [tpcds, DATE_DIM], CS_SOLD_DATE_SK:D_DATE_SK)," + + " Step([tpcds, STORE_RETURNS], [tpcds, CUSTOMER], SR_CUSTOMER_SK:C_CUSTOMER_SK)," + + " Step([tpcds, STORE_RETURNS], [tpcds, DATE_DIM], SR_RETURNED_DATE_SK:D_DATE_SK)," + + " Step([tpcds, STORE_RETURNS], [tpcds, STORE], SR_STORE_SK:S_STORE_SK)," + + " Step([tpcds, STORE_RETURNS], [tpcds, STORE_RETURNS], SR_STORE_SK:SR_STORE_SK)," + + " Step([tpcds, STORE_SALES], [tpcds, CUSTOMER], SS_CUSTOMER_SK:C_CUSTOMER_SK)," + + " Step([tpcds, STORE_SALES], [tpcds, CUSTOMER_DEMOGRAPHICS], SS_CDEMO_SK:CD_DEMO_SK)," + + " Step([tpcds, STORE_SALES], [tpcds, DATE_DIM], SS_SOLD_DATE_SK:D_DATE_SK)," + + " Step([tpcds, STORE_SALES], [tpcds, ITEM], SS_ITEM_SK:I_ITEM_SK)," + + " Step([tpcds, STORE_SALES], [tpcds, PROMOTION], SS_PROMO_SK:P_PROMO_SK)," + + " Step([tpcds, WEB_SALES], [tpcds, CUSTOMER], WS_BILL_CUSTOMER_SK:C_CUSTOMER_SK)," + + " Step([tpcds, WEB_SALES], [tpcds, DATE_DIM], WS_SOLD_DATE_SK:D_DATE_SK)])"; + assertThat(t.suggester.space.g.toString(), is(expected)); + if (evolve) { + assertThat(t.suggester.space.nodeMap.size(), is(5)); + assertThat(t.suggester.latticeMap.size(), is(3)); + assertThat(t.suggester.space.pathMap.size(), is(10)); + } else { + assertThat(t.suggester.space.nodeMap.size(), is(5)); + assertThat(t.suggester.latticeMap.size(), is(4)); + assertThat(t.suggester.space.pathMap.size(), is(10)); + } + } + + @Test public void testTpcdsAll() throws Exception { + checkFoodMartAll(false); + } + + @Test public void testTpcdsAllEvolve() throws Exception { + checkFoodMartAll(true); + } + + /** Test helper. */ + private static class Tester { + final LatticeSuggester suggester; + private final FrameworkConfig config; + + Tester() { + this(config(CalciteAssert.SchemaSpec.BLANK).build()); + } + + private Tester(FrameworkConfig config) { + this.config = config; + suggester = new LatticeSuggester(config); + } + + Tester tpcds() { + final SchemaPlus rootSchema = Frameworks.createRootSchema(true); + final double scaleFactor = 0.01d; + final SchemaPlus schema = + rootSchema.add("tpcds", new TpcdsSchema(scaleFactor)); + final FrameworkConfig config = Frameworks.newConfigBuilder() + .parserConfig(SqlParser.Config.DEFAULT) + .context( + Contexts.of( + new CalciteConnectionConfigImpl(new Properties()) + .set(CalciteConnectionProperty.CONFORMANCE, + SqlConformanceEnum.LENIENT.name()))) + .defaultSchema(schema) + .build(); + return withConfig(config); + } + + Tester withConfig(FrameworkConfig config) { + return new Tester(config); + } + + List<Lattice> addQuery(String q) throws SqlParseException, + ValidationException, RelConversionException { + final Planner planner = new PlannerImpl(config); + final SqlNode node = planner.parse(q); + final SqlNode node2 = planner.validate(node); + final RelRoot root = planner.rel(node2); + return suggester.addQuery(root.project()); + } + + /** Parses a query returns its graph. */ + LatticeRootNode node(String q) throws SqlParseException, + ValidationException, RelConversionException { + final List<Lattice> list = addQuery(q); + assertThat(list.size(), is(1)); + return list.get(0).rootNode; + } + + static Frameworks.ConfigBuilder config(CalciteAssert.SchemaSpec spec) { + final SchemaPlus rootSchema = Frameworks.createRootSchema(true); + final SchemaPlus schema = CalciteAssert.addSchema(rootSchema, spec); + return Frameworks.newConfigBuilder() + .parserConfig(SqlParser.Config.DEFAULT) + .defaultSchema(schema); + } + + Tester withEvolve(boolean evolve) { + if (evolve == config.isEvolveLattice()) { + return this; + } + final Frameworks.ConfigBuilder configBuilder = + Frameworks.newConfigBuilder(config); + return new Tester(configBuilder.evolveLattice(true).build()); + } + } +} + +// End TpcdsLatticeSuggesterTest.java http://git-wip-us.apache.org/repos/asf/calcite/blob/b47413a1/site/_data/docs.yml ---------------------------------------------------------------------- diff --git a/site/_data/docs.yml b/site/_data/docs.yml index ea38df9..1a19404 100644 --- a/site/_data/docs.yml +++ b/site/_data/docs.yml @@ -26,6 +26,7 @@ - adapter - spatial - stream + - materialized_views - lattice - title: Avatica http://git-wip-us.apache.org/repos/asf/calcite/blob/b47413a1/site/_docs/lattice.md ---------------------------------------------------------------------- diff --git a/site/_docs/lattice.md b/site/_docs/lattice.md index 5e8aef4..3d1732e 100644 --- a/site/_docs/lattice.md +++ b/site/_docs/lattice.md @@ -26,6 +26,11 @@ A lattice is a framework for creating and populating materialized views, and for recognizing that a materialized view can be used to solve a particular query. +* TOC +{:toc} + +## Concept + A lattice represents a star (or snowflake) schema, not a general schema. In particular, all relationships must be many-to-one, heading from a fact table at the center of the star. @@ -115,17 +120,230 @@ Examples: * ["order_date", "time_id"] A "tile" is a materialized table in a lattice, with a particular -dimensionality. (What Kylin calls a "cuboid".) The "tiles" attribute +dimensionality. The "tiles" attribute of the <a href="{{ site.baseurl }}/docs/model.html#lattice">lattice JSON element</a> defines an initial set of tiles to materialize. -If you run the algorithm, you can omit the tiles attribute. Calcite -will choose an initial set. If you include the tiles attribute, the -algorithm will start with that list and then start finding other tiles -that are complementary (i.e. "fill in the gaps" left by the initial -tiles). +## Demonstration + +Create a model that includes a lattice: + +{% highlight json %} +{ + "version": "1.0", + "defaultSchema": "foodmart", + "schemas": [ { + "type": "jdbc", + "name": "foodmart", + "jdbcUser": "FOODMART", + "jdbcPassword": "FOODMART", + "jdbcUrl": "jdbc:hsqldb:res:foodmart", + "jdbcSchema": "foodmart" + }, + { + "name": "adhoc", + "lattices": [ { + "name": "star", + "sql": [ + "select 1 from \"foodmart\".\"sales_fact_1997\" as \"s\"", + "join \"foodmart\".\"product\" as \"p\" using (\"product_id\")", + "join \"foodmart\".\"time_by_day\" as \"t\" using (\"time_id\")", + "join \"foodmart\".\"product_class\" as \"pc\" on \"p\".\"product_class_id\" = \"pc\".\"product_class_id\"" + ], + "auto": true, + "algorithm": true, + "rowCountEstimate": 86837, + "defaultMeasures": [ { + "agg": "count" + } ] + } ] + } ] +} +{% endhighlight %} + +This is a cut-down version of +[hsqldb-foodmart-lattice-model.json]({{ site.sourceRoot }}/core/src/test/resources/hsqldb-foodmart-lattice-model.json) +that does not include the "tiles" attribute, because we are going to generate +tiles automatically. Let's log into sqlline and connect to this schema: + +{% highlight sql %} +$ sqlline version 1.3.0 +sqlline> !connect jdbc:calcite:model=core/src/test/resources/hsqldb-foodmart-lattice-model.json "sa" "" +{% endhighlight %} + +You'll notice that it takes a few seconds to connect. +Calcite is running the optimization algorithm, and creating and +populating materialized views. Let's run a query and check out its plan: + +{% highlight sql %} +sqlline> select "the_year","the_month", count(*) as c +. . . .> from "sales_fact_1997" +. . . .> join "time_by_day" using ("time_id") +. . . .> group by "the_year","the_month"; ++----------+-----------+------+ +| the_year | the_month | C | ++----------+-----------+------+ +| 1997 | September | 6663 | +| 1997 | April | 6590 | +| 1997 | January | 7034 | +| 1997 | June | 6912 | +| 1997 | August | 7038 | +| 1997 | February | 6844 | +| 1997 | March | 7710 | +| 1997 | October | 6479 | +| 1997 | May | 6866 | +| 1997 | December | 8717 | +| 1997 | July | 7752 | +| 1997 | November | 8232 | ++----------+-----------+------+ +12 rows selected (0.147 seconds) + +sqlline> explain plan for +. . . .> select "the_year","the_month", count(*) as c +. . . .> from "sales_fact_1997" +. . . .> join "time_by_day" using ("time_id") +. . . .> group by "the_year","the_month"; ++--------------------------------------------------------------------------------+ +| PLAN | ++--------------------------------------------------------------------------------+ +| EnumerableCalc(expr#0..2=[{inputs}], the_year=[$t1], the_month=[$t0], C=[$t2]) | +| EnumerableAggregate(group=[{3, 4}], C=[$SUM0($7)]) | +| EnumerableTableScan(table=[[adhoc, m{16, 17, 27, 31, 32, 36, 37}]]) | ++--------------------------------------------------------------------------------+ + +{% endhighlight %} + +The query gives the right answer, but plan is somewhat surprising. +It doesn't read the `sales_fact_1997` or `time_by_day` tables, but instead +reads from a table called `m{16, 17, 27, 31, 32, 36, 37}`. This is one of the +tiles created at the start of the connection. + +It's a real table, and you can even query it directly. It has only 120 rows, +so is a more efficient way to answer the query: + +{% highlight sql %} +sqlline> !describe "adhoc"."m{16, 17, 27, 31, 32, 36, 37}" ++-------------+-------------------------------+--------------------+-----------+-----------------+ +| TABLE_SCHEM | TABLE_NAME | COLUMN_NAME | DATA_TYPE | TYPE_NAME | ++-------------+-------------------------------+--------------------+-----------+-----------------+ +| adhoc | m{16, 17, 27, 31, 32, 36, 37} | recyclable_package | 16 | BOOLEAN | +| adhoc | m{16, 17, 27, 31, 32, 36, 37} | low_fat | 16 | BOOLEAN | +| adhoc | m{16, 17, 27, 31, 32, 36, 37} | product_family | 12 | VARCHAR(30) | +| adhoc | m{16, 17, 27, 31, 32, 36, 37} | the_month | 12 | VARCHAR(30) | +| adhoc | m{16, 17, 27, 31, 32, 36, 37} | the_year | 5 | SMALLINT | +| adhoc | m{16, 17, 27, 31, 32, 36, 37} | quarter | 12 | VARCHAR(30) | +| adhoc | m{16, 17, 27, 31, 32, 36, 37} | fiscal_period | 12 | VARCHAR(30) | +| adhoc | m{16, 17, 27, 31, 32, 36, 37} | m0 | -5 | BIGINT NOT NULL | ++-------------+-------------------------------+--------------------+-----------+-----------------+ + +sqlline> select count(*) as c +. . . .> from "adhoc"."m{16, 17, 27, 31, 32, 36, 37}"; ++-----+ +| C | ++-----+ +| 120 | ++-----+ +1 row selected (0.12 seconds) +{% endhighlight %} + +Let's list the tables, and you will see several more tiles. There are also +tables of the `foodmart` schema, and the system tables `TABLES` and `COLUMNS`, +and the lattice itself, which appears as a table called `star`. + +{% highlight sql %} +sqlline> !tables ++-------------+-------------------------------+--------------+ +| TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | ++-------------+-------------------------------+--------------+ +| adhoc | m{16, 17, 18, 32, 37} | TABLE | +| adhoc | m{16, 17, 19, 27, 32, 36, 37} | TABLE | +| adhoc | m{4, 7, 16, 27, 32, 37} | TABLE | +| adhoc | m{4, 7, 17, 27, 32, 37} | TABLE | +| adhoc | m{7, 16, 17, 19, 32, 37} | TABLE | +| adhoc | m{7, 16, 17, 27, 30, 32, 37} | TABLE | +| adhoc | star | STAR | +| foodmart | customer | TABLE | +| foodmart | product | TABLE | +| foodmart | product_class | TABLE | +| foodmart | promotion | TABLE | +| foodmart | region | TABLE | +| foodmart | sales_fact_1997 | TABLE | +| foodmart | store | TABLE | +| foodmart | time_by_day | TABLE | +| metadata | COLUMNS | SYSTEM_TABLE | +| metadata | TABLES | SYSTEM_TABLE | ++-------------+-------------------------------+--------------+ + +{% endhighlight %} + +## Statistics + +The algorithm that chooses which tiles of a lattice to materialize depends on +a lot of statistics. It needs to know `select count(distinct a, b, c) from star` +for each combination of columns (`a, b, c`) it is considering materializing. As +a result the algorithm takes a long time on schemas with many rows and columns. + +We are working on a +[data profiler](https://issues.apache.org/jira/browse/CALCITE-1616) +to address this. + +## Lattice suggester + +If you have defined a lattice, Calcite will self-tune within that lattice. +But what if you have not defined a lattice? + +Enter the Lattice Suggester, which builds lattices based on incoming queries. +Create a model with a schema that has `"autoLattice": true`: + +{% highlight json %} +{ + "version": "1.0", + "defaultSchema": "foodmart", + "schemas": [ { + "type": "jdbc", + "name": "foodmart", + "jdbcUser": "FOODMART", + "jdbcPassword": "FOODMART", + "jdbcUrl": "jdbc:hsqldb:res:foodmart", + "jdbcSchema": "foodmart" + }, { + "name": "adhoc", + "autoLattice": true + } ] +} +{% endhighlight %} + +This is a cut-down version of +[hsqldb-foodmart-lattice-model.json]({{ site.sourceRoot }}/core/src/test/resources/hsqldb-foodmart-lattice-model.json) + +As you run queries, Calcite will start to build lattices based on those +queries. Each lattice is based on a particular fact table. As it sees more +queries on that fact table, it will evolve the lattice, joining more dimension +tables to the star, and adding measures. + +Each lattice will then optimize itself based on both the data and the queries. +The goal is to create summary tables (tiles) that are reasonably small but are +based on more frequently used attributes and measures. + +This feature is still experimental, but has the potential to make databases +more "self-tuning" than before. + +## Further directions + +Here are some ideas that have not yet been implemented: +* The algorithm that builds tiles takes into account a log of past queries. +* Materialized view manager sees incoming queries and builds tiles for them. +* Materialized view manager drops tiles that are not actively used. +* Lattice suggester adds lattices based on incoming queries, + transfers tiles from existing lattices to new lattices, + and drops lattices that are no longer being used. +* Tiles that cover a horizontal slice of a table; and a rewrite algorithm that + can answer a query by stitching together several tiles and going to the raw + data to fill in the holes. +* API to invalidate tiles, or horizontal slices of tiles, when the underlying + data is changed. -### References +## References <ul> <li>[<a name="ref-hru96">HRU96</a>] V. Harinarayan, A. Rajaraman and J. Ullman. http://git-wip-us.apache.org/repos/asf/calcite/blob/b47413a1/site/_docs/materialized_views.md ---------------------------------------------------------------------- diff --git a/site/_docs/materialized_views.md b/site/_docs/materialized_views.md index a407b12..d7d66ef 100644 --- a/site/_docs/materialized_views.md +++ b/site/_docs/materialized_views.md @@ -112,8 +112,8 @@ FROM depts JOIN ( SELECT empid, deptno FROM emps - WHERE empid = 1) subq -ON (depts.deptno = subq.deptno) + WHERE empid = 1) AS subq +ON depts.deptno = subq.deptno ``` * Materialized view definition: @@ -227,8 +227,8 @@ GROUP BY deptno ``` SELECT deptname, state, SUM(salary) AS s FROM emps -JOIN depts ON (emps.deptno = depts.deptno) -JOIN locations ON (emps.locationid = locations.locationid) +JOIN depts ON emps.deptno = depts.deptno +JOIN locations ON emps.locationid = locations.locationid GROUP BY deptname, state ``` @@ -237,7 +237,7 @@ GROUP BY deptname, state ``` SELECT empid, deptno, state, SUM(salary) AS s FROM emps -JOIN locations ON (emps.locationid = locations.locationid) +JOIN locations ON emps.locationid = locations.locationid GROUP BY empid, deptno, state ``` @@ -246,7 +246,7 @@ GROUP BY empid, deptno, state ``` SELECT deptname, state, SUM(s) FROM mv -JOIN depts ON (mv.deptno = depts.deptno) +JOIN depts ON mv.deptno = depts.deptno GROUP BY deptname, state ``` @@ -258,7 +258,7 @@ GROUP BY deptname, state ``` SELECT empid, deptname FROM emps -JOIN depts ON (emps.deptno = depts.deptno) +JOIN depts ON emps.deptno = depts.deptno WHERE salary > 10000 ``` @@ -267,7 +267,7 @@ WHERE salary > 10000 ``` SELECT empid, deptname FROM emps -JOIN depts ON (emps.deptno = depts.deptno) +JOIN depts ON emps.deptno = depts.deptno WHERE salary > 12000 ``` @@ -279,7 +279,7 @@ FROM mv UNION ALL SELECT empid, deptname FROM emps -JOIN depts ON (emps.deptno = depts.deptno) +JOIN depts ON emps.deptno = depts.deptno WHERE salary > 10000 AND salary <= 12000 ``` @@ -291,7 +291,7 @@ WHERE salary > 10000 AND salary <= 12000 ``` SELECT empid, deptname, SUM(salary) AS s FROM emps -JOIN depts ON (emps.deptno = depts.deptno) +JOIN depts ON emps.deptno = depts.deptno WHERE salary > 10000 GROUP BY empid, deptname ``` @@ -301,7 +301,7 @@ GROUP BY empid, deptname ``` SELECT empid, deptname, SUM(salary) AS s FROM emps -JOIN depts ON (emps.deptno = depts.deptno) +JOIN depts ON emps.deptno = depts.deptno WHERE salary > 12000 GROUP BY empid, deptname ``` @@ -311,14 +311,14 @@ GROUP BY empid, deptname ``` SELECT empid, deptname, SUM(s) FROM ( -SELECT empid, deptname, s -FROM mv -UNION ALL -SELECT empid, deptname, SUM(salary) AS s -FROM emps -JOIN depts ON (emps.deptno = depts.deptno) -WHERE salary > 10000 AND salary <= 12000 -GROUP BY empid, deptname) subq + SELECT empid, deptname, s + FROM mv + UNION ALL + SELECT empid, deptname, SUM(salary) AS s + FROM emps + JOIN depts ON emps.deptno = depts.deptno + WHERE salary > 10000 AND salary <= 12000 + GROUP BY empid, deptname) AS subq GROUP BY empid, deptname ``` @@ -327,7 +327,7 @@ GROUP BY empid, deptname This rule still presents some limitations. In particular, the rewriting rule attempts to match all views against each query. We plan to implement more refined filtering techniques such as those described in [<a href="#ref-gl01">GL01</a>]. -### References +## References <ul> <li>[<a name="ref-gl01">GL01</a>] Jonathan Goldstein and Per-Ã¥ke Larson.
