Repository: calcite Updated Branches: refs/heads/master 7321c8708 -> 025eaf118
http://git-wip-us.apache.org/repos/asf/calcite/blob/025eaf11/druid/src/main/java/org/apache/calcite/adapter/druid/DruidType.java ---------------------------------------------------------------------- diff --git a/druid/src/main/java/org/apache/calcite/adapter/druid/DruidType.java b/druid/src/main/java/org/apache/calcite/adapter/druid/DruidType.java new file mode 100644 index 0000000..f50fdfd --- /dev/null +++ b/druid/src/main/java/org/apache/calcite/adapter/druid/DruidType.java @@ -0,0 +1,81 @@ +/* + * 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.adapter.druid; + +import org.apache.calcite.sql.type.SqlTypeName; + +/** Druid type. */ +public enum DruidType { + LONG(SqlTypeName.BIGINT), + // SQL DOUBLE and FLOAT types are both 64 bit, but we use DOUBLE because + // people find FLOAT confusing. + FLOAT(SqlTypeName.DOUBLE), + STRING(SqlTypeName.VARCHAR), + HYPER_UNIQUE(SqlTypeName.VARBINARY), + THETA_SKETCH(SqlTypeName.VARBINARY); + + /** The corresponding SQL type. */ + public final SqlTypeName sqlType; + + DruidType(SqlTypeName sqlType) { + this.sqlType = sqlType; + } + + /** + * Returns true if and only if this enum should be used inside of a {@link ComplexMetric} + * */ + public boolean isComplex() { + return this == THETA_SKETCH || this == HYPER_UNIQUE; + } + + /** + * Returns a DruidType matching the given String type from a Druid metric + * */ + public static DruidType getTypeFromMetric(String type) { + assert type != null; + if (type.equals("hyperUnique")) { + return HYPER_UNIQUE; + } else if (type.equals("thetaSketch")) { + return THETA_SKETCH; + } else if (type.startsWith("long") || type.equals("count")) { + return LONG; + } else if (type.startsWith("double")) { + return FLOAT; + } + throw new AssertionError("Unknown type: " + type); + } + + /** + * Returns a DruidType matching the String from a meta data query + * */ + public static DruidType getTypeFromMetaData(String type) { + assert type != null; + switch (type) { + case "LONG": + return LONG; + case "FLOAT": + return FLOAT; + case "STRING": + return STRING; + default: + // Likely a sketch, or a type String from the aggregations field. + return getTypeFromMetric(type); + } + } +} + +// End DruidType.java http://git-wip-us.apache.org/repos/asf/calcite/blob/025eaf11/druid/src/test/java/org/apache/calcite/adapter/druid/DruidQueryFilterTest.java ---------------------------------------------------------------------- diff --git a/druid/src/test/java/org/apache/calcite/adapter/druid/DruidQueryFilterTest.java b/druid/src/test/java/org/apache/calcite/adapter/druid/DruidQueryFilterTest.java index 49e4cc9..b2e8635 100644 --- a/druid/src/test/java/org/apache/calcite/adapter/druid/DruidQueryFilterTest.java +++ b/druid/src/test/java/org/apache/calcite/adapter/druid/DruidQueryFilterTest.java @@ -113,7 +113,8 @@ public class DruidQueryFilterTest { final RexBuilder rexBuilder = new RexBuilder(typeFactory); final DruidTable druidTable = new DruidTable(Mockito.mock(DruidSchema.class), "dataSource", null, - ImmutableSet.<String>of(), "timestamp", null); + ImmutableSet.<String>of(), "timestamp", null, null, + null); final RelDataType varcharType = typeFactory.createSqlType(SqlTypeName.VARCHAR); final RelDataType varcharRowType = typeFactory.builder() http://git-wip-us.apache.org/repos/asf/calcite/blob/025eaf11/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT.java ---------------------------------------------------------------------- diff --git a/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT.java b/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT.java index a3f3c6f..0eed641 100644 --- a/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT.java +++ b/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT.java @@ -119,6 +119,30 @@ public class DruidAdapterIT { }; } + /** + * Creates a query against FOODMART with approximate parameters + * */ + private CalciteAssert.AssertQuery foodmartApprox(String sql) { + return approxQuery(FOODMART, sql); + } + + /** + * Creates a query against WIKI with approximate parameters + * */ + private CalciteAssert.AssertQuery wikiApprox(String sql) { + return approxQuery(WIKI, sql); + } + + private CalciteAssert.AssertQuery approxQuery(URL url, String sql) { + return CalciteAssert.that() + .enable(enabled()) + .with(ImmutableMap.of("model", url.getPath())) + .with(CalciteConnectionProperty.APPROXIMATE_DISTINCT_COUNT.camelName(), true) + .with(CalciteConnectionProperty.APPROXIMATE_TOP_N.camelName(), true) + .with(CalciteConnectionProperty.APPROXIMATE_DECIMAL.camelName(), true) + .query(sql); + } + /** Creates a query against a data set given by a map. */ private CalciteAssert.AssertQuery sql(String sql, URL url) { return CalciteAssert.that() @@ -755,7 +779,7 @@ public class DruidAdapterIT { /** Tests a query that contains no GROUP BY and is therefore executed as a * Druid "select" query. */ @Test public void testFilterSortDesc() { - final String sql = "select * from \"foodmart\"\n" + final String sql = "select \"product_name\" from \"foodmart\"\n" + "where \"product_id\" BETWEEN '1500' AND '1502'\n" + "order by \"state_province\" desc, \"product_id\""; final String druidQuery = "{'queryType':'select','dataSource':'foodmart'," @@ -763,20 +787,8 @@ public class DruidAdapterIT { + "'filter':{'type':'and','fields':[" + "{'type':'bound','dimension':'product_id','lower':'1500','lowerStrict':false,'ordering':'lexicographic'}," + "{'type':'bound','dimension':'product_id','upper':'1502','upperStrict':false,'ordering':'lexicographic'}]}," - + "'dimensions':['product_id','brand_name','product_name','SKU','SRP','gross_weight','net_weight'," - + "'recyclable_package','low_fat','units_per_case','cases_per_pallet','shelf_width','shelf_height'," - + "'shelf_depth','product_class_id','product_subcategory','product_category','product_department'," - + "'product_family','customer_id','account_num','lname','fname','mi','address1','address2','address3'," - + "'address4','city','state_province','postal_code','country','customer_region_id','phone1','phone2'," - + "'birthdate','marital_status','yearly_income','gender','total_children','num_children_at_home'," - + "'education','date_accnt_opened','member_card','occupation','houseowner','num_cars_owned'," - + "'fullname','promotion_id','promotion_district_id','promotion_name','media_type','cost','start_date'," - + "'end_date','store_id','store_type','region_id','store_name','store_number','store_street_address'," - + "'store_city','store_state','store_postal_code','store_country','store_manager','store_phone'," - + "'store_fax','first_opened_date','last_remodel_date','store_sqft','grocery_sqft','frozen_sqft'," - + "'meat_sqft','coffee_bar','video_store','salad_bar','prepared_food','florist','time_id','the_day'," - + "'the_month','the_year','day_of_month','week_of_year','month_of_year','quarter','fiscal_period']," - + "'metrics':['unit_sales','store_sales','store_cost'],'granularity':'all'," + + "'dimensions':['product_name','state_province','product_id']," + + "'metrics':[],'granularity':'all'," + "'pagingSpec':{'threshold':16384,'fromNext':true},'context':{'druid.query.fetch':false}}"; sql(sql) .limit(4) @@ -801,29 +813,16 @@ public class DruidAdapterIT { /** As {@link #testFilterSortDesc()} but the bounds are numeric. */ @Test public void testFilterSortDescNumeric() { - final String sql = "select * from \"foodmart\"\n" + final String sql = "select \"product_name\" from \"foodmart\"\n" + "where \"product_id\" BETWEEN 1500 AND 1502\n" + "order by \"state_province\" desc, \"product_id\""; - final String druidQuery = "{'queryType':'select','dataSource':'foodmart'," - + "'descending':false,'intervals':['1900-01-09T00:00:00.000/2992-01-10T00:00:00.000']," - + "'filter':{'type':'and','fields':[" - + "{'type':'bound','dimension':'product_id','lower':'1500','lowerStrict':false,'ordering':'numeric'}," - + "{'type':'bound','dimension':'product_id','upper':'1502','upperStrict':false,'ordering':'numeric'}]}," - + "'dimensions':['product_id','brand_name','product_name','SKU','SRP','gross_weight','net_weight'," - + "'recyclable_package','low_fat','units_per_case','cases_per_pallet','shelf_width','shelf_height'," - + "'shelf_depth','product_class_id','product_subcategory','product_category','product_department'," - + "'product_family','customer_id','account_num','lname','fname','mi','address1','address2','address3'," - + "'address4','city','state_province','postal_code','country','customer_region_id','phone1','phone2'," - + "'birthdate','marital_status','yearly_income','gender','total_children','num_children_at_home'," - + "'education','date_accnt_opened','member_card','occupation','houseowner','num_cars_owned'," - + "'fullname','promotion_id','promotion_district_id','promotion_name','media_type','cost','start_date'," - + "'end_date','store_id','store_type','region_id','store_name','store_number','store_street_address'," - + "'store_city','store_state','store_postal_code','store_country','store_manager','store_phone'," - + "'store_fax','first_opened_date','last_remodel_date','store_sqft','grocery_sqft','frozen_sqft'," - + "'meat_sqft','coffee_bar','video_store','salad_bar','prepared_food','florist','time_id','the_day'," - + "'the_month','the_year','day_of_month','week_of_year','month_of_year','quarter','fiscal_period']," - + "'metrics':['unit_sales','store_sales','store_cost'],'granularity':'all'," - + "'pagingSpec':{'threshold':16384,'fromNext':true},'context':{'druid.query.fetch':false}}"; + final String druidQuery = "{'queryType':'select','dataSource':'foodmart','descending':false," + + "'intervals':['1900-01-09T00:00:00.000/2992-01-10T00:00:00.000'],'filter':{'type':" + + "'and','fields':[{'type':'bound','dimension':'product_id','lower':'1500'," + + "'lowerStrict':false,'ordering':'numeric'},{'type':'bound','dimension':'product_id'," + + "'upper':'1502','upperStrict':false,'ordering':'numeric'}]},'dimensions':" + + "['product_name','state_province','product_id'],'metrics':[],'granularity':'all','pagingSpec':" + + "{'threshold':16384,'fromNext':true},'context':{'druid.query.fetch':false}}"; sql(sql) .limit(4) .returns( @@ -847,30 +846,13 @@ public class DruidAdapterIT { /** Tests a query whose filter removes all rows. */ @Test public void testFilterOutEverything() { - final String sql = "select * from \"foodmart\"\n" + final String sql = "select \"product_name\" from \"foodmart\"\n" + "where \"product_id\" = -1"; final String druidQuery = "{'queryType':'select','dataSource':'foodmart'," + "'descending':false,'intervals':['1900-01-09T00:00:00.000/2992-01-10T00:00:00.000']," + "'filter':{'type':'selector','dimension':'product_id','value':'-1'}," - + "'dimensions':['product_id','brand_name','product_name','SKU','SRP'," - + "'gross_weight','net_weight','recyclable_package','low_fat','units_per_case'," - + "'cases_per_pallet','shelf_width','shelf_height','shelf_depth'," - + "'product_class_id','product_subcategory','product_category'," - + "'product_department','product_family','customer_id','account_num'," - + "'lname','fname','mi','address1','address2','address3','address4'," - + "'city','state_province','postal_code','country','customer_region_id'," - + "'phone1','phone2','birthdate','marital_status','yearly_income','gender'," - + "'total_children','num_children_at_home','education','date_accnt_opened'," - + "'member_card','occupation','houseowner','num_cars_owned','fullname'," - + "'promotion_id','promotion_district_id','promotion_name','media_type','cost'," - + "'start_date','end_date','store_id','store_type','region_id','store_name'," - + "'store_number','store_street_address','store_city','store_state'," - + "'store_postal_code','store_country','store_manager','store_phone'," - + "'store_fax','first_opened_date','last_remodel_date','store_sqft','grocery_sqft'," - + "'frozen_sqft','meat_sqft','coffee_bar','video_store','salad_bar','prepared_food'," - + "'florist','time_id','the_day','the_month','the_year','day_of_month'," - + "'week_of_year','month_of_year','quarter','fiscal_period']," - + "'metrics':['unit_sales','store_sales','store_cost'],'granularity':'all'," + + "'dimensions':['product_name']," + + "'metrics':[],'granularity':'all'," + "'pagingSpec':{'threshold':16384,'fromNext':true},'context':{'druid.query.fetch':false}}"; sql(sql) .limit(4) @@ -881,26 +863,13 @@ public class DruidAdapterIT { /** As {@link #testFilterSortDescNumeric()} but with a filter that cannot * be pushed down to Druid. */ @Test public void testNonPushableFilterSortDesc() { - final String sql = "select * from \"foodmart\"\n" + final String sql = "select \"product_name\" from \"foodmart\"\n" + "where cast(\"product_id\" as integer) - 1500 BETWEEN 0 AND 2\n" + "order by \"state_province\" desc, \"product_id\""; final String druidQuery = "{'queryType':'select','dataSource':'foodmart'," + "'descending':false,'intervals':['1900-01-09T00:00:00.000/2992-01-10T00:00:00.000']," - + "'dimensions':['product_id','brand_name','product_name','SKU','SRP','gross_weight'," - + "'net_weight','recyclable_package','low_fat','units_per_case','cases_per_pallet'," - + "'shelf_width','shelf_height','shelf_depth','product_class_id','product_subcategory'," - + "'product_category','product_department','product_family','customer_id','account_num'," - + "'lname','fname','mi','address1','address2','address3','address4','city','state_province'," - + "'postal_code','country','customer_region_id','phone1','phone2','birthdate','marital_status'," - + "'yearly_income','gender','total_children','num_children_at_home','education'," - + "'date_accnt_opened','member_card','occupation','houseowner','num_cars_owned','fullname'," - + "'promotion_id','promotion_district_id','promotion_name','media_type','cost','start_date'," - + "'end_date','store_id','store_type','region_id','store_name','store_number','store_street_address'," - + "'store_city','store_state','store_postal_code','store_country','store_manager','store_phone'," - + "'store_fax','first_opened_date','last_remodel_date','store_sqft','grocery_sqft','frozen_sqft'," - + "'meat_sqft','coffee_bar','video_store','salad_bar','prepared_food','florist','time_id','the_day'," - + "'the_month','the_year','day_of_month','week_of_year','month_of_year','quarter','fiscal_period']," - + "'metrics':['unit_sales','store_sales','store_cost'],'granularity':'all'," + + "'dimensions':['product_id','product_name','state_province']," + + "'metrics':[],'granularity':'all'," + "'pagingSpec':{'threshold':16384,'fromNext':true},'context':{'druid.query.fetch':false}}"; sql(sql) .limit(4) @@ -2279,7 +2248,7 @@ public class DruidAdapterIT { } /** - * Turn on now count(distinct ) will get pushed after CALC-1853 + * Turn on now count(distinct ) */ @Test public void testHyperUniquePostAggregator() { final String sqlQuery = "select \"store_state\", sum(\"store_cost\") / count(distinct " @@ -2290,11 +2259,7 @@ public class DruidAdapterIT { final String plan = "PLAN=EnumerableInterpreter\n" + " DruidQuery(table=[[foodmart, foodmart]], intervals=" + "[[1900-01-09T00:00:00.000/2992-01-10T00:00:00.000]], groups=[{63}], "; - CalciteAssert.that() - .enable(enabled()) - .with(ImmutableMap.of("model", FOODMART.getPath())) - .with(CalciteConnectionProperty.APPROXIMATE_DISTINCT_COUNT.camelName(), true) - .query(sqlQuery) + foodmartApprox(sqlQuery) .runs() .explainContains(plan) .queryContains(druidChecker(postAggString)); @@ -2958,11 +2923,11 @@ public class DruidAdapterIT { * acceptable * */ @Test public void testDistinctCountWhenApproxResultsAccepted() { - String sql = "select count(distinct \"customer_id\") from \"foodmart\""; + String sql = "select count(distinct \"store_state\") from \"foodmart\""; String expectedSubExplain = "DruidQuery(table=[[foodmart, foodmart]], intervals=[[1900-01-09T00" - + ":00:00.000/2992-01-10T00:00:00.000]], groups=[{}], aggs=[[COUNT(DISTINCT $20)]])"; + + ":00:00.000/2992-01-10T00:00:00.000]], groups=[{}], aggs=[[COUNT(DISTINCT $63)]])"; String expectedAggregate = "{'type':'cardinality','name':" - + "'EXPR$0','fieldNames':['customer_id']}"; + + "'EXPR$0','fieldNames':['store_state']}"; testCountWithApproxDistinct(true, sql, expectedSubExplain, expectedAggregate); } @@ -2972,11 +2937,11 @@ public class DruidAdapterIT { * are not acceptable */ @Test public void testDistinctCountWhenApproxResultsNotAccepted() { - String sql = "select count(distinct \"customer_id\") from \"foodmart\""; + String sql = "select count(distinct \"store_state\") from \"foodmart\""; String expectedSubExplain = " BindableAggregate(group=[{}], EXPR$0=[COUNT($0)])\n" + " DruidQuery(table=[[foodmart, foodmart]], " + "intervals=[[1900-01-09T00:00:00.000/2992-01-10T00:00:00.000]], " - + "groups=[{20}], aggs=[[]])"; + + "groups=[{63}], aggs=[[]])"; testCountWithApproxDistinct(false, sql, expectedSubExplain); } @@ -3026,11 +2991,11 @@ public class DruidAdapterIT { */ @Test public void testCountOnMetricRenamed() { String sql = "select \"B\", count(\"A\") from " - + "(select \"unit_sales\" as \"A\", \"customer_id\" as \"B\" from \"foodmart\") " + + "(select \"unit_sales\" as \"A\", \"store_state\" as \"B\" from \"foodmart\") " + "group by \"B\""; String expectedSubExplain = " BindableAggregate(group=[{0}], EXPR$1=[COUNT($1)])\n" + " DruidQuery(table=[[foodmart, foodmart]], intervals=[[1900-01-09T00:00:00.000" - + "/2992-01-10T00:00:00.000]], projects=[[$20, $89]])\n"; + + "/2992-01-10T00:00:00.000]], projects=[[$63, $89]])\n"; testCountWithApproxDistinct(true, sql, expectedSubExplain); testCountWithApproxDistinct(false, sql, expectedSubExplain); @@ -3038,11 +3003,11 @@ public class DruidAdapterIT { @Test public void testDistinctCountOnMetricRenamed() { String sql = "select \"B\", count(distinct \"A\") from " - + "(select \"unit_sales\" as \"A\", \"customer_id\" as \"B\" from \"foodmart\") " + + "(select \"unit_sales\" as \"A\", \"store_state\" as \"B\" from \"foodmart\") " + "group by \"B\""; String expectedSubExplain = " BindableAggregate(group=[{0}], EXPR$1=[COUNT($1)])\n" + " DruidQuery(table=[[foodmart, foodmart]], intervals=[[1900-01-09T00:00:" - + "00.000/2992-01-10T00:00:00.000]], projects=[[$20, $89]], groups=[{0, 1}], " + + "00.000/2992-01-10T00:00:00.000]], projects=[[$63, $89]], groups=[{0, 1}], " + "aggs=[[]])"; testCountWithApproxDistinct(true, sql, expectedSubExplain); @@ -3064,6 +3029,160 @@ public class DruidAdapterIT { .explainContains(expectedExplain) .queryContains(druidChecker(expectedDruidQuery)); } + + /** + * Tests the use of count(distinct ...) on a complex metric column in SELECT + * */ + @Test public void testCountDistinctOnComplexColumn() { + // Because approximate distinct count has not been enabled + sql("select count(distinct \"user_id\") from \"wiki\"", WIKI) + .failsAtValidation("Rolled up column 'user_id' is not allowed in COUNT"); + + foodmartApprox("select count(distinct \"customer_id\") from \"foodmart\"") + // customer_id gets transformed into it's actual underlying sketch column, + // customer_id_ts. The thetaSketch aggregation is used to compute the count distinct. + .queryContains( + druidChecker("{'queryType':'timeseries','dataSource':" + + "'foodmart','descending':false,'granularity':'all','aggregations':[{'type':" + + "'thetaSketch','name':'EXPR$0','fieldName':'customer_id_ts'}]," + + "'intervals':['1900-01-09T00:00:00.000/2992-01-10T00:00:00.000']," + + "'context':{'skipEmptyBuckets':true}}")) + .returnsUnordered("EXPR$0=5581"); + + foodmartApprox("select sum(\"store_sales\"), " + + "count(distinct \"customer_id\") filter (where \"store_state\" = 'CA') " + + "from \"foodmart\" where \"the_month\" = 'October'") + // Check that filtered aggregations work correctly + .queryContains( + druidChecker("{'type':'filtered','filter':" + + "{'type':'selector','dimension':'store_state','value':'CA'},'aggregator':" + + "{'type':'thetaSketch','name':'EXPR$1','fieldName':'customer_id_ts'}}]")) + .returnsUnordered("EXPR$0=42342.27003854513; EXPR$1=459"); + } + + /** + * Tests the use of other aggregations with complex columns + * */ + @Test public void testAggregationsWithComplexColumns() { + wikiApprox("select count(\"user_id\") from \"wiki\"") + .failsAtValidation("Rolled up column 'user_id' is not allowed in COUNT"); + + wikiApprox("select sum(\"user_id\") from \"wiki\"") + .failsAtValidation("Cannot apply 'SUM' to arguments of type " + + "'SUM(<VARBINARY>)'. Supported form(s): 'SUM(<NUMERIC>)'"); + + wikiApprox("select avg(\"user_id\") from \"wiki\"") + .failsAtValidation("Cannot apply 'AVG' to arguments of type " + + "'AVG(<VARBINARY>)'. Supported form(s): 'AVG(<NUMERIC>)'"); + + wikiApprox("select max(\"user_id\") from \"wiki\"") + .failsAtValidation("Rolled up column 'user_id' is not allowed in MAX"); + + wikiApprox("select min(\"user_id\") from \"wiki\"") + .failsAtValidation("Rolled up column 'user_id' is not allowed in MIN"); + } + + /** + * Test post aggregation support with +, -, /, * operators + * */ + @Test public void testPostAggregationWithComplexColumns() { + foodmartApprox("select " + + "(count(distinct \"customer_id\") * 2) + " + + "count(distinct \"customer_id\") - " + + "(3 * count(distinct \"customer_id\")) " + + "from \"foodmart\"") + .queryContains( + druidChecker("'aggregations':[{'type':'thetaSketch','name':'$f0'," + + "'fieldName':'customer_id_ts'}],'postAggregations':[{'type':" + + "'arithmetic','name':'postagg#0','fn':'-','fields':[{'type':" + + "'arithmetic','name':'','fn':'+','fields':[{'type':'arithmetic','" + + "name':'','fn':'*','fields':[{'type':'thetaSketchEstimate','name':" + + "'','field':{'type':'fieldAccess','name':'','fieldName':'$f0'}}," + + "{'type':'constant','name':'','value':2.0}]},{'type':" + + "'thetaSketchEstimate','name':'','field':{'type':'fieldAccess'," + + "'name':'','fieldName':'$f0'}}]},{'type':'arithmetic','name':''," + + "'fn':'*','fields':[{'type':'constant','name':'','value':3.0}," + + "{'type':'thetaSketchEstimate','name':'','field':{'type':" + + "'fieldAccess','name':'','fieldName':'$f0'}}]}]}]")) + .returnsUnordered("EXPR$0=0"); + + foodmartApprox("select " + + "\"the_month\" as \"month\", " + + "sum(\"store_sales\") / count(distinct \"customer_id\") as \"avg$\" " + + "from \"foodmart\" group by \"the_month\"") + .queryContains( + druidChecker("'aggregations':[{'type':'doubleSum','name':" + + "'$f1','fieldName':'store_sales'},{'type':'thetaSketch','name':'$f2'," + + "'fieldName':'customer_id_ts'}],'postAggregations':[{'type':'arithmetic'," + + "'name':'postagg#0','fn':'quotient','fields':[{'type':'fieldAccess','name':" + + "'','fieldName':'$f1'},{'type':'thetaSketchEstimate','name':'','field':" + + "{'type':'fieldAccess','name':'','fieldName':'$f2'}}]}]")) + .returnsUnordered( + "month=January; avg$=32.621555448603154", + "month=February; avg$=33.102020332456796", + "month=March; avg$=33.84970980632612", + "month=April; avg$=32.55751708428246", + "month=May; avg$=32.426177288475564", + "month=June; avg$=33.93093597960329", + "month=July; avg$=34.36859022315321", + "month=August; avg$=32.81181751598012", + "month=September; avg$=33.32773288973384", + "month=October; avg$=32.74730822215777", + "month=November; avg$=34.51727744987063", + "month=December; avg$=33.62788702774498"); + + wikiApprox("select (count(distinct \"user_id\") + 100) - " + + "(count(distinct \"user_id\") * 2) from \"wiki\"") + .queryContains( + druidChecker("'aggregations':[{'type':'hyperUnique','name':'$f0'," + + "'fieldName':'user_unique'}],'postAggregations':[{'type':" + + "'arithmetic','name':'postagg#0','fn':'-','fields':[{'type':" + + "'arithmetic','name':'','fn':'+','fields':[{'type':" + + "'hyperUniqueCardinality','name':'','fieldName':'$f0'}," + + "{'type':'constant','name':'','value':100.0}]},{'type':" + + "'arithmetic','name':'','fn':'*','fields':[{'type':" + + "'hyperUniqueCardinality','name':'','fieldName':'$f0'}," + + "{'type':'constant','name':'','value':2.0}]}]}]")) + .returnsUnordered("EXPR$0=-10590"); + } + + /** + * Test to make sure that if a complex metric is also a dimension, then + * {@link org.apache.calcite.adapter.druid.DruidTable} should allow it to be used like any other + * column. + * */ + @Test public void testComplexMetricAlsoDimension() { + foodmartApprox("select \"customer_id\" from \"foodmart\"") + .runs(); + + foodmartApprox("select count(distinct \"the_month\"), \"customer_id\" " + + "from \"foodmart\" group by \"customer_id\"") + .queryContains( + druidChecker("{'queryType':'groupBy','dataSource':'foodmart'," + + "'granularity':'all','dimensions':[{'type':'default','dimension':" + + "'customer_id'}],'limitSpec':{'type':'default'},'aggregations':[{" + + "'type':'cardinality','name':'EXPR$0','fieldNames':['the_month']}]," + + "'intervals':['1900-01-09T00:00:00.000/2992-01-10T00:00:00.000']}")); + } + + /** + * Test to make sure that SELECT * doesn't fail, and that the rolled up column is not requested + * in the JSON query. + * */ + @Test public void testSelectStarWithRollUp() { + final String sql = "select * from \"wiki\" limit 5"; + sql(sql, WIKI) + // make sure user_id column is not present + .queryContains( + druidChecker("{'queryType':'select','dataSource':'wikiticker'," + + "'descending':false,'intervals':['1900-01-09T00:00:00.000/2992-01-10T00:00:00.000']," + + "'dimensions':['channel','cityName','comment','countryIsoCode','countryName'," + + "'isAnonymous','isMinor','isNew','isRobot','isUnpatrolled','metroCode'," + + "'namespace','page','regionIsoCode','regionName'],'metrics':['count','added'," + + "'deleted','delta'],'granularity':'all','pagingSpec':{'threshold':5,'fromNext'" + + ":true},'context':{'druid.query.fetch':true}}")); + } + } // End DruidAdapterIT.java http://git-wip-us.apache.org/repos/asf/calcite/blob/025eaf11/druid/src/test/resources/druid-foodmart-model.json ---------------------------------------------------------------------- diff --git a/druid/src/test/resources/druid-foodmart-model.json b/druid/src/test/resources/druid-foodmart-model.json index f2a5713..7f776d9 100644 --- a/druid/src/test/resources/druid-foodmart-model.json +++ b/druid/src/test/resources/druid-foodmart-model.json @@ -132,7 +132,15 @@ { "name": "store_cost", "type": "double" + }, + { + "name" : "customer_id_ts", + "type" : "thetaSketch", + "fieldName" : "customer_id" } + ], + "complexMetrics" : [ + "customer_id" ] } } http://git-wip-us.apache.org/repos/asf/calcite/blob/025eaf11/druid/src/test/resources/druid-wiki-model.json ---------------------------------------------------------------------- diff --git a/druid/src/test/resources/druid-wiki-model.json b/druid/src/test/resources/druid-wiki-model.json index 6ea04b8..ce93cda 100644 --- a/druid/src/test/resources/druid-wiki-model.json +++ b/druid/src/test/resources/druid-wiki-model.json @@ -49,8 +49,7 @@ "namespace", "page", "regionIsoCode", - "regionName", - "user" + "regionName" ], "metrics": [ { @@ -75,8 +74,11 @@ { "name" : "user_unique", "type" : "hyperUnique", - "fieldName" : "user" + "fieldName" : "user_id" } + ], + "complexMetrics" : [ + "user_id" ] } } http://git-wip-us.apache.org/repos/asf/calcite/blob/025eaf11/plus/src/main/java/org/apache/calcite/adapter/os/DuTableFunction.java ---------------------------------------------------------------------- diff --git a/plus/src/main/java/org/apache/calcite/adapter/os/DuTableFunction.java b/plus/src/main/java/org/apache/calcite/adapter/os/DuTableFunction.java index bf4a36c..336f5fa 100644 --- a/plus/src/main/java/org/apache/calcite/adapter/os/DuTableFunction.java +++ b/plus/src/main/java/org/apache/calcite/adapter/os/DuTableFunction.java @@ -17,6 +17,7 @@ package org.apache.calcite.adapter.os; import org.apache.calcite.DataContext; +import org.apache.calcite.config.CalciteConnectionConfig; import org.apache.calcite.linq4j.Enumerable; import org.apache.calcite.linq4j.function.Function1; import org.apache.calcite.rel.type.RelDataType; @@ -25,6 +26,8 @@ import org.apache.calcite.schema.ScannableTable; import org.apache.calcite.schema.Schema; import org.apache.calcite.schema.Statistic; import org.apache.calcite.schema.Statistics; +import org.apache.calcite.sql.SqlCall; +import org.apache.calcite.sql.SqlNode; import org.apache.calcite.sql.type.SqlTypeName; import org.apache.calcite.util.ImmutableBitSet; @@ -63,6 +66,15 @@ public class DuTableFunction { public Schema.TableType getJdbcTableType() { return Schema.TableType.TABLE; } + + public boolean isRolledUp(String column) { + return false; + } + + public boolean rolledUpColumnValidInsideAgg(String column, SqlCall call, + SqlNode parent, CalciteConnectionConfig config) { + return true; + } }; } http://git-wip-us.apache.org/repos/asf/calcite/blob/025eaf11/plus/src/main/java/org/apache/calcite/adapter/os/FilesTableFunction.java ---------------------------------------------------------------------- diff --git a/plus/src/main/java/org/apache/calcite/adapter/os/FilesTableFunction.java b/plus/src/main/java/org/apache/calcite/adapter/os/FilesTableFunction.java index 2923a0f..d54cfb2 100644 --- a/plus/src/main/java/org/apache/calcite/adapter/os/FilesTableFunction.java +++ b/plus/src/main/java/org/apache/calcite/adapter/os/FilesTableFunction.java @@ -17,6 +17,7 @@ package org.apache.calcite.adapter.os; import org.apache.calcite.DataContext; +import org.apache.calcite.config.CalciteConnectionConfig; import org.apache.calcite.linq4j.AbstractEnumerable; import org.apache.calcite.linq4j.Enumerable; import org.apache.calcite.linq4j.Enumerator; @@ -26,6 +27,8 @@ import org.apache.calcite.schema.ScannableTable; import org.apache.calcite.schema.Schema; import org.apache.calcite.schema.Statistic; import org.apache.calcite.schema.Statistics; +import org.apache.calcite.sql.SqlCall; +import org.apache.calcite.sql.SqlNode; import org.apache.calcite.sql.type.SqlTypeName; import org.apache.calcite.util.ImmutableBitSet; import org.apache.calcite.util.Util; @@ -273,6 +276,15 @@ public class FilesTableFunction { public Schema.TableType getJdbcTableType() { return Schema.TableType.TABLE; } + + public boolean isRolledUp(String column) { + return false; + } + + public boolean rolledUpColumnValidInsideAgg(String column, SqlCall call, + SqlNode parent, CalciteConnectionConfig config) { + return true; + } }; } http://git-wip-us.apache.org/repos/asf/calcite/blob/025eaf11/plus/src/main/java/org/apache/calcite/adapter/os/GitCommitsTableFunction.java ---------------------------------------------------------------------- diff --git a/plus/src/main/java/org/apache/calcite/adapter/os/GitCommitsTableFunction.java b/plus/src/main/java/org/apache/calcite/adapter/os/GitCommitsTableFunction.java index fd14a44..24d9751 100644 --- a/plus/src/main/java/org/apache/calcite/adapter/os/GitCommitsTableFunction.java +++ b/plus/src/main/java/org/apache/calcite/adapter/os/GitCommitsTableFunction.java @@ -17,6 +17,7 @@ package org.apache.calcite.adapter.os; import org.apache.calcite.DataContext; +import org.apache.calcite.config.CalciteConnectionConfig; import org.apache.calcite.linq4j.AbstractEnumerable; import org.apache.calcite.linq4j.Enumerable; import org.apache.calcite.linq4j.Enumerator; @@ -26,6 +27,8 @@ import org.apache.calcite.schema.ScannableTable; import org.apache.calcite.schema.Schema; import org.apache.calcite.schema.Statistic; import org.apache.calcite.schema.Statistics; +import org.apache.calcite.sql.SqlCall; +import org.apache.calcite.sql.SqlNode; import org.apache.calcite.sql.type.SqlTypeName; import org.apache.calcite.util.ImmutableBitSet; @@ -157,6 +160,15 @@ public class GitCommitsTableFunction { public Schema.TableType getJdbcTableType() { return Schema.TableType.TABLE; } + + public boolean isRolledUp(String column) { + return false; + } + + public boolean rolledUpColumnValidInsideAgg(String column, SqlCall call, + SqlNode parent, CalciteConnectionConfig config) { + return true; + } }; } } http://git-wip-us.apache.org/repos/asf/calcite/blob/025eaf11/plus/src/main/java/org/apache/calcite/adapter/os/PsTableFunction.java ---------------------------------------------------------------------- diff --git a/plus/src/main/java/org/apache/calcite/adapter/os/PsTableFunction.java b/plus/src/main/java/org/apache/calcite/adapter/os/PsTableFunction.java index 217a4fa..62a4f04 100644 --- a/plus/src/main/java/org/apache/calcite/adapter/os/PsTableFunction.java +++ b/plus/src/main/java/org/apache/calcite/adapter/os/PsTableFunction.java @@ -18,6 +18,7 @@ package org.apache.calcite.adapter.os; import org.apache.calcite.DataContext; import org.apache.calcite.avatica.util.TimeUnit; +import org.apache.calcite.config.CalciteConnectionConfig; import org.apache.calcite.linq4j.Enumerable; import org.apache.calcite.linq4j.function.Function1; import org.apache.calcite.rel.type.RelDataType; @@ -26,6 +27,8 @@ import org.apache.calcite.schema.ScannableTable; import org.apache.calcite.schema.Schema; import org.apache.calcite.schema.Statistic; import org.apache.calcite.schema.Statistics; +import org.apache.calcite.sql.SqlCall; +import org.apache.calcite.sql.SqlNode; import org.apache.calcite.sql.type.SqlTypeName; import org.apache.calcite.util.ImmutableBitSet; import org.apache.calcite.util.Util; @@ -160,6 +163,15 @@ public class PsTableFunction { public Schema.TableType getJdbcTableType() { return Schema.TableType.TABLE; } + + public boolean isRolledUp(String column) { + return false; + } + + public boolean rolledUpColumnValidInsideAgg(String column, SqlCall call, + SqlNode parent, CalciteConnectionConfig config) { + return true; + } }; } } http://git-wip-us.apache.org/repos/asf/calcite/blob/025eaf11/plus/src/main/java/org/apache/calcite/adapter/os/StdinTableFunction.java ---------------------------------------------------------------------- diff --git a/plus/src/main/java/org/apache/calcite/adapter/os/StdinTableFunction.java b/plus/src/main/java/org/apache/calcite/adapter/os/StdinTableFunction.java index 17cfc12..d2b5fa1 100644 --- a/plus/src/main/java/org/apache/calcite/adapter/os/StdinTableFunction.java +++ b/plus/src/main/java/org/apache/calcite/adapter/os/StdinTableFunction.java @@ -17,6 +17,7 @@ package org.apache.calcite.adapter.os; import org.apache.calcite.DataContext; +import org.apache.calcite.config.CalciteConnectionConfig; import org.apache.calcite.linq4j.AbstractEnumerable; import org.apache.calcite.linq4j.Enumerable; import org.apache.calcite.linq4j.Enumerator; @@ -26,6 +27,8 @@ import org.apache.calcite.schema.ScannableTable; import org.apache.calcite.schema.Schema; import org.apache.calcite.schema.Statistic; import org.apache.calcite.schema.Statistics; +import org.apache.calcite.sql.SqlCall; +import org.apache.calcite.sql.SqlNode; import org.apache.calcite.sql.type.SqlTypeName; import org.apache.calcite.util.ImmutableBitSet; @@ -105,6 +108,15 @@ public class StdinTableFunction { public Schema.TableType getJdbcTableType() { return Schema.TableType.TABLE; } + + public boolean isRolledUp(String column) { + return false; + } + + public boolean rolledUpColumnValidInsideAgg(String column, SqlCall call, + SqlNode parent, CalciteConnectionConfig config) { + return true; + } }; } } http://git-wip-us.apache.org/repos/asf/calcite/blob/025eaf11/plus/src/main/java/org/apache/calcite/adapter/os/VmstatTableFunction.java ---------------------------------------------------------------------- diff --git a/plus/src/main/java/org/apache/calcite/adapter/os/VmstatTableFunction.java b/plus/src/main/java/org/apache/calcite/adapter/os/VmstatTableFunction.java index 924a868..65b48b6 100644 --- a/plus/src/main/java/org/apache/calcite/adapter/os/VmstatTableFunction.java +++ b/plus/src/main/java/org/apache/calcite/adapter/os/VmstatTableFunction.java @@ -17,6 +17,7 @@ package org.apache.calcite.adapter.os; import org.apache.calcite.DataContext; +import org.apache.calcite.config.CalciteConnectionConfig; import org.apache.calcite.linq4j.Enumerable; import org.apache.calcite.linq4j.function.Function1; import org.apache.calcite.rel.type.RelDataType; @@ -25,6 +26,8 @@ import org.apache.calcite.schema.ScannableTable; import org.apache.calcite.schema.Schema; import org.apache.calcite.schema.Statistic; import org.apache.calcite.schema.Statistics; +import org.apache.calcite.sql.SqlCall; +import org.apache.calcite.sql.SqlNode; import org.apache.calcite.sql.type.SqlTypeName; import org.apache.calcite.util.ImmutableBitSet; import org.apache.calcite.util.Util; @@ -152,6 +155,15 @@ public class VmstatTableFunction { public Schema.TableType getJdbcTableType() { return Schema.TableType.TABLE; } + + public boolean isRolledUp(String column) { + return false; + } + + public boolean rolledUpColumnValidInsideAgg(String column, SqlCall call, + SqlNode parent, CalciteConnectionConfig config) { + return true; + } }; } } http://git-wip-us.apache.org/repos/asf/calcite/blob/025eaf11/site/_docs/druid_adapter.md ---------------------------------------------------------------------- diff --git a/site/_docs/druid_adapter.md b/site/_docs/druid_adapter.md index fa088d5..cd74d74 100644 --- a/site/_docs/druid_adapter.md +++ b/site/_docs/druid_adapter.md @@ -78,7 +78,6 @@ A basic example of a model file is given below: "page", "regionIsoCode", "regionName", - "user" ], "metrics": [ { @@ -103,8 +102,11 @@ A basic example of a model file is given below: { "name" : "user_unique", "type" : "hyperUnique", - "fieldName" : "user" + "fieldName" : "user_id" } + ], + "complexMetrics" : [ + "user_id" ] } } @@ -165,6 +167,18 @@ part of the query to Druid, including the `COUNT(*)` function, but not the `ORDER BY ... LIMIT`. (We plan to lift this restriction; see [[CALCITE-1206](https://issues.apache.org/jira/browse/CALCITE-1206)].) +# Complex Metrics +Druid has special metrics that produce quick but approximate results. +Currently there are two types: + +* `hyperUnique` - HyperLogLog data sketch used to estimate the cardinality of a dimension +* `thetaSketch` - Theta sketch used to also estimate the cardinality of a dimension, + but can be used to perform set operations as well. + +In the model definition, there is an array of Strings called `complexMetrics` that declares +the alias for each complex metric defined. The alias is used in SQL, but it's real column name +is used when Calcite generates the JSON query for druid. + # Foodmart data set The test VM also includes a data set that denormalizes
