vdiravka commented on a change in pull request #1466: DRILL-6381: Add support for index based planning and execution URL: https://github.com/apache/drill/pull/1466#discussion_r223657717
########## File path: contrib/format-maprdb/src/test/java/com/mapr/drill/maprdb/tests/index/IndexPlanTest.java ########## @@ -0,0 +1,1715 @@ +/* + * 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 com.mapr.drill.maprdb.tests.index; + +import com.mapr.db.Admin; +import com.mapr.drill.maprdb.tests.MaprDBTestsSuite; +import com.mapr.drill.maprdb.tests.json.BaseJsonTest; +import com.mapr.tests.annotations.ClusterTest; +import org.apache.drill.PlanTestBase; +import org.joda.time.DateTime; +import org.joda.time.format.DateTimeFormat; +import org.apache.drill.common.config.DrillConfig; +import org.junit.AfterClass; +import org.junit.BeforeClass; +import org.junit.FixMethodOrder; +import org.junit.Ignore; +import org.junit.Test; +import org.junit.experimental.categories.Category; +import org.junit.runners.MethodSorters; +import java.util.Properties; + + +@FixMethodOrder(MethodSorters.NAME_ASCENDING) +@Category(ClusterTest.class) +public class IndexPlanTest extends BaseJsonTest { + + final static String PRIMARY_TABLE_NAME = "/tmp/index_test_primary"; + + final static int PRIMARY_TABLE_SIZE = 10000; + private static final String sliceTargetSmall = "alter session set `planner.slice_target` = 1"; + private static final String sliceTargetDefault = "alter session reset `planner.slice_target`"; + private static final String noIndexPlan = "alter session set `planner.enable_index_planning` = false"; + private static final String defaultHavingIndexPlan = "alter session reset `planner.enable_index_planning`"; + private static final String disableHashAgg = "alter session set `planner.enable_hashagg` = false"; + private static final String enableHashAgg = "alter session set `planner.enable_hashagg` = true"; + private static final String defaultnonCoveringSelectivityThreshold = "alter session set `planner.index.noncovering_selectivity_threshold` = 0.025"; + private static final String incrnonCoveringSelectivityThreshold = "alter session set `planner.index.noncovering_selectivity_threshold` = 0.25"; + private static final String disableFTS = "alter session set `planner.disable_full_table_scan` = true"; + private static final String enableFTS = "alter session reset `planner.disable_full_table_scan`"; + private static final String preferIntersectPlans = "alter session set `planner.index.prefer_intersect_plans` = true"; + private static final String defaultIntersectPlans = "alter session reset `planner.index.prefer_intersect_plans`"; + private static final String lowRowKeyJoinBackIOFactor + = "alter session set `planner.index.rowkeyjoin_cost_factor` = 0.01"; + private static final String defaultRowKeyJoinBackIOFactor + = "alter session reset `planner.index.rowkeyjoin_cost_factor`"; + + /** + * A sample row of this 10K table: + ------------------+-----------------------------+--------+ + | 1012 | {"city":"pfrrs","state":"pc"} | {"email":"[email protected]","phone":"6500005471"} | + {"ssn":"100007423"} | {"fname":"KfFzK","lname":"UZwNk"} | {"age":53.0,"income":45.0} | 1012 | + * + * This test suite generate random content to fill all the rows, since the random function always start from + * the same seed for different runs, when the row count is not changed, the data in table will always be the same, + * thus the query result could be predicted and verified. + */ + + @BeforeClass + public static void setupTableIndexes() throws Exception { + + Properties overrideProps = new Properties(); + overrideProps.setProperty("format-maprdb.json.useNumRegionsForDistribution", "true"); + updateTestCluster(1, DrillConfig.create(overrideProps)); + + MaprDBTestsSuite.setupTests(); + MaprDBTestsSuite.createPluginAndGetConf(getDrillbitContext()); + + test(incrnonCoveringSelectivityThreshold); + + System.out.print("setupTableIndexes begins"); + Admin admin = MaprDBTestsSuite.getAdmin(); + if (admin != null) { + if (admin.tableExists(PRIMARY_TABLE_NAME)) { + admin.deleteTable(PRIMARY_TABLE_NAME); + } + } + + LargeTableGen gen = new LargeTableGen(MaprDBTestsSuite.getAdmin()); + /** + * indexDef is an array of string, LargeTableGen.generateTableWithIndex will take it as parameter to generate indexes + * for primary table. + * indexDef[3*i] defines i-th index's indexName, NOTE: IF the name begins with "hash", it is a hash index + * indexDef[3*i+1] indexed field, + * and indexDef[3*i+2] defines i-th index's non-indexed fields + */ + final String[] indexDef = //null; + {"i_ssn", "id.ssn", "contact.phone", + "i_state_city", "address.state,address.city", "name.fname,name.lname",//mainly for composite key test + "i_age", "personal.age", "", + "i_income", "personal.income", "", + "i_lic", "driverlicense", "reverseid", + "i_state_city_dl", "address.state,address.city", "driverlicense", + "i_cast_int_ssn", "$CAST(id.ssn@INT)", "contact.phone", + "i_cast_vchar_lic", "$CAST(driverlicense@STRING)","contact.email", + "i_state_age_phone", "address.state,personal.age,contact.phone", "name.fname", + "i_cast_age_income_phone", "$CAST(personal.age@INT),$CAST(personal.income@INT),contact.phone", "name.fname", + "i_age_with_fname", "personal.age", "name.fname", + "hash_i_reverseid", "reverseid", "", + "hash_i_cast_timestamp_firstlogin", "$CAST(activity.irs.firstlogin@TIMESTAMP)", "id.ssn" + }; + gen.generateTableWithIndex(PRIMARY_TABLE_NAME, PRIMARY_TABLE_SIZE, indexDef); + } + + @AfterClass + public static void cleanupTableIndexes() throws Exception { + Admin admin = MaprDBTestsSuite.getAdmin(); + if (admin != null) { + if (admin.tableExists(PRIMARY_TABLE_NAME)) { + // admin.deleteTable(PRIMARY_TABLE_NAME); + } + } + test(defaultnonCoveringSelectivityThreshold); + } + + @Test + public void CTASTestTable() throws Exception { + String ctasQuery = "CREATE TABLE hbase.tmp.`backup_index_test_primary` " + + "AS SELECT * FROM hbase.`index_test_primary` as t "; + test(ctasQuery); + test("DROP TABLE IF EXISTS hbase.tmp.`backup_index_test_primary`"); + } + + @Test + public void CoveringPlanWithNonIndexedField() throws Exception { + + String query = "SELECT t.`contact`.`phone` AS `phone` FROM hbase.`index_test_primary` as t " + + " where t.id.ssn = '100007423'"; + test(defaultHavingIndexPlan); + PlanTestBase.testPlanMatchingPatterns(query, + new String[] {".*JsonTableGroupScan.*tableName=.*index_test_primary.*indexName=i_ssn"}, + new String[]{"RowKeyJoin"} + ); + + System.out.println("Covering Plan Verified!"); + + testBuilder() + .sqlQuery(query) + .ordered() + .baselineColumns("phone").baselineValues("6500005471") + .go(); + return; + + } + + @Test + public void CoveringPlanWithOnlyIndexedField() throws Exception { + String query = "SELECT t.`id`.`ssn` AS `ssn` FROM hbase.`index_test_primary` as t " + + " where t.id.ssn = '100007423'"; + test(defaultHavingIndexPlan); + PlanTestBase.testPlanMatchingPatterns(query, + new String[] {".*JsonTableGroupScan.*tableName=.*index_test_primary.*indexName=i_ssn"}, + new String[]{"RowKeyJoin"} + ); + + System.out.println("Covering Plan Verified!"); + + testBuilder() + .optionSettingQueriesForTestQuery(defaultHavingIndexPlan) + .sqlQuery(query) + .ordered() + .baselineColumns("ssn").baselineValues("100007423") + .go(); + + return; + } + + @Test + public void NoIndexPlanForNonIndexField() throws Exception { + + String query = "SELECT t.`id`.`ssn` AS `ssn` FROM hbase.`index_test_primary` as t " + + " where t.contact.phone = '6500005471'"; + test(defaultHavingIndexPlan); + PlanTestBase.testPlanMatchingPatterns(query, + new String[] {".*JsonTableGroupScan.*tableName=.*index_test_primary"}, + new String[]{"RowKeyJoin", "indexName="} + ); + + System.out.println("No Index Plan Verified!"); + + testBuilder() + .sqlQuery(query) + .unOrdered() + .baselineColumns("ssn").baselineValues("100007423") + .baselineColumns("ssn").baselineValues("100007632") + .go(); + + return; + } + + @Test + public void NonCoveringPlan() throws Exception { + + String query = "SELECT t.`name`.`fname` AS `fname` FROM hbase.`index_test_primary` as t " + + " where t.id.ssn = '100007423'"; + test(defaultHavingIndexPlan); + PlanTestBase.testPlanMatchingPatterns(query, + new String[] {"RowKeyJoin", ".*RestrictedJsonTableGroupScan.*tableName=.*index_test_primary,", + ".*JsonTableGroupScan.*tableName=.*index_test_primary,.*indexName=i_ssn"}, + new String[]{} + ); + + System.out.println("Non-Covering Plan Verified!"); Review comment: possibly `logger.info`? ---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: [email protected] With regards, Apache Git Services
