private void testMultipleBatchJoin(final long right, final long left, final String joinType, final long expected) throws Exception { final String leftSide = BaseTestQuery.getTempDir("merge-join-left.json"); final String rightSide = BaseTestQuery.getTempDir("merge-join-right.json"); final BufferedWriter leftWriter = new BufferedWriter(new FileWriter(new File(leftSide))); final BufferedWriter rightWriter = new BufferedWriter(new FileWriter(new File(rightSide))); generateData(leftWriter, rightWriter, left, right); final String query1 = String.format("select count(*) c1 from dfs.\"%s\" L %s join dfs.\"%s\" R on L.k=R.k1", leftSide, joinType, rightSide); testBuilder() .sqlQuery(query1) .optionSettingQueriesForTestQuery("alter session set \"planner.enable_hashjoin\" = false") .unOrdered() .baselineColumns("c1") .baselineValues(expected) .go(); }
@Test // DRILL-3037 public void testSimpleQuery() throws Exception { final String query = String.format("SELECT sales_city, sales_country FROM dfsRegion ORDER BY region_id DESC LIMIT 2"); testBuilder() .optionSettingQueriesForTestQuery(String.format("USE %s", MINIDFS_STORAGE_PLUGIN_NAME)) .sqlQuery(query) .unOrdered() .baselineColumns("sales_city", "sales_country") .baselineValues("Santa Fe", "Mexico") .baselineValues("Santa Anita", "Mexico") .go(); }
private void describeHelper(final String options, final String describeCmd) throws Exception { final TestBuilder builder = testBuilder(); if (!Strings.isNullOrEmpty(options)) { builder.optionSettingQueriesForTestQuery(options); } builder.sqlQuery(describeCmd) .unOrdered() .baselineColumns(baselineCols) .baselineValues(expVal1) .baselineValues(expVal2) .go(); }
@Test public void testExchangeRemoveForJoinPlan() throws Exception { final String WORKING_PATH = TestTools.getWorkingPath(); final String TEST_RES_PATH = WORKING_PATH + "/src/test/resources"; String sql = String.format("select t2.n_nationkey from dfs.\"%s/tpchmulti/region\" t1 join dfs.\"%s/tpchmulti/nation\" t2 on t2.n_regionkey = t1.r_regionkey", TEST_RES_PATH, TEST_RES_PATH); testBuilder() .unOrdered() .optionSettingQueriesForTestQuery("alter session set \"planner.slice_target\" = 10; alter session set \"planner.join.row_count_estimate_factor\" = 0.1") // Enforce exchange will be inserted. .sqlQuery(sql) .optionSettingQueriesForBaseline("alter session set \"planner.slice_target\" = 100000; alter session set \"planner.join.row_count_estimate_factor\" = 1.0") // Use default option setting. .sqlBaselineQuery(sql) .build().run(); }
@Test public void queryFromNonDefaultSchema() throws Exception{ testBuilder() .sqlQuery("SELECT full_name FROM cp.\"employee.json\" LIMIT 1") .unOrdered() .optionSettingQueriesForTestQuery("USE dfs_test") .baselineColumns("full_name") .baselineValues("Sheri Nowmer") .go(); }
@Test public void currentSchemaUDFWithSingleLevelDefaultSchema() throws Exception { testBuilder() .optionSettingQueriesForTestQuery("USE dfs_test") .sqlQuery("select current_schema from cp.\"employee.json\" limit 1") .unOrdered() .baselineColumns("current_schema") .baselineValues("dfs_test") .go(); } }
@Test public void showTablesLike() throws Exception{ testBuilder() .sqlQuery("SHOW TABLES LIKE '%CH%'") .unOrdered() .optionSettingQueriesForTestQuery("USE INFORMATION_SCHEMA") .baselineColumns("TABLE_SCHEMA", "TABLE_NAME") .baselineValues("INFORMATION_SCHEMA", "SCHEMATA") .go(); }
@Test public void defaultSchemaClasspath() throws Exception{ testBuilder() .sqlQuery("SELECT full_name FROM \"employee.json\" LIMIT 1") .unOrdered() .optionSettingQueriesForTestQuery("USE cp") .baselineColumns("full_name") .baselineValues("Sheri Nowmer") .go(); }
@Test public void defaultSchemaDfs() throws Exception{ testBuilder() .sqlQuery("SELECT R_REGIONKEY FROM \"[WORKING_PATH]/../../sample-data/region.parquet\" LIMIT 1") .unOrdered() .optionSettingQueriesForTestQuery("USE dfs") .baselineColumns("R_REGIONKEY") .baselineValues(0L) .go(); }
@Test public void describeTableWithColumnName() throws Exception{ testBuilder() .sqlQuery("DESCRIBE \"TABLES\" TABLE_CATALOG") .unOrdered() .optionSettingQueriesForTestQuery("USE INFORMATION_SCHEMA") .baselineColumns("COLUMN_NAME", "DATA_TYPE", "IS_NULLABLE") .baselineValues("TABLE_CATALOG", "CHARACTER VARYING", "YES") .go(); }
@Test public void testSumWithTypeCase() throws Exception { String query = "select sum(cast(f1 as bigint)) sum_f1 from " + "(select case when is_bigint(field1) then assert_bigint(field1) " + "when is_list(field1) then field1[0] when is_struct(field1) then t.field1.inner1 end f1 " + "from cp.\"jsoninput/union/a.json\" t)"; testBuilder() .sqlQuery(query) .ordered() .optionSettingQueriesForTestQuery("alter session set \"exec.enable_union_type\" = true") .baselineColumns("sum_f1") .baselineValues(9L) .go(); }
private void attemptTestNumericTypes(String query, int record_count) throws Exception { TestBuilder builder = testBuilder() .sqlQuery(query) .optionSettingQueriesForTestQuery("alter session set \"exec.enable_union_type\" = true") .ordered() .baselineColumns("a"); for (int i = record_count; i >= 0;) { builder.baselineValues((long) i--); if (i >= 0) { builder.baselineValues((double) i--); } } builder.go(); }
@Test public void testSortWithRepeatedMapWithExchanges() throws Exception { testBuilder() .sqlQuery("select (t.a) as col from cp.\"jsoninput/repeatedmap_sort_bug.json\" t order by t.b") .optionSettingQueriesForTestQuery("alter session set \"planner.slice_target\" = 1") .ordered() .baselineColumns("col") .baselineValues(repeated_map) .go(); // reset the planner.slice_target test("alter session set \"planner.slice_target\" = " + ExecConstants.SLICE_TARGET_DEFAULT); }
@Test @Ignore("currently namespace doesn't store view/table type") public void testInfoSchemaWithHiveView() throws Exception { testBuilder() .optionSettingQueriesForTestQuery("USE hive.\"default\"") .sqlQuery("SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'hiveview'") .unOrdered() .baselineColumns("TABLE_CATALOG", "TABLE_SCHEMA", "TABLE_NAME", "VIEW_DEFINITION") .baselineValues("DRILL", "hive.default", "hiveview", "SELECT \"kv\".\"key\", \"kv\".\"value\" FROM \"default\".\"kv\"") .go(); }
@Test public void defaultSchemaHive() throws Exception{ testBuilder() .sqlQuery("SELECT * FROM kv LIMIT 2") .unOrdered() .optionSettingQueriesForTestQuery("USE hive") .baselineColumns("key", "value") .baselineValues(1, " key_1") .baselineValues(2, " key_2") .go(); }
@Test public void defaultTwoLevelSchemaHive() throws Exception{ testBuilder() .sqlQuery("SELECT * FROM kv_db1 LIMIT 2") .unOrdered() .optionSettingQueriesForTestQuery("USE hive.db1") .baselineColumns("key", "value") .baselineValues("1", " key_1") .baselineValues("2", " key_2") .go(); } }
private void attemptTestNumericTypes(String query) throws Exception { TestBuilder builder = testBuilder() .sqlQuery(query) .optionSettingQueriesForTestQuery("alter session set \"exec.enable_union_type\" = true") .ordered() .baselineColumns("kl", "vl"); for (long i = 0; i < 12; ++i) { if (i % 2 == 0) { builder.baselineValues(i, i); } else { builder.baselineValues((double) i, (double) i); } } builder.go(); }
@Test public void describeTable() throws Exception{ testBuilder() .sqlQuery("DESCRIBE CATALOGS") .unOrdered() .optionSettingQueriesForTestQuery("USE INFORMATION_SCHEMA") .baselineColumns("COLUMN_NAME", "DATA_TYPE", "IS_NULLABLE") .baselineValues("CATALOG_NAME", "CHARACTER VARYING", "YES") .baselineValues("CATALOG_DESCRIPTION", "CHARACTER VARYING", "YES") .baselineValues("CATALOG_CONNECT", "CHARACTER VARYING", "YES") .go(); }
@Test public void testSimilar() throws Exception { String query = "select n_nationkey " + "from cp.\"tpch/nation.parquet\" " + "where n_name similar to 'CHINA' " + "order by n_regionkey"; testBuilder() .sqlQuery(query) .unOrdered() .optionSettingQueriesForTestQuery("alter session set \"planner.slice_target\" = 1") .baselineColumns("n_nationkey") .baselineValues(18) .go(); test("alter session set \"planner.slice_target\" = " + ExecConstants.SLICE_TARGET_DEFAULT); }
@Ignore("DX-2290") @Test public void describeTableWithColQualifier() throws Exception{ testBuilder() .sqlQuery("DESCRIBE COLUMNS 'TABLE%'") .unOrdered() .optionSettingQueriesForTestQuery("USE INFORMATION_SCHEMA") .baselineColumns("COLUMN_NAME", "DATA_TYPE", "IS_NULLABLE") .baselineValues("TABLE_CATALOG", "CHARACTER VARYING", "YES") .baselineValues("TABLE_SCHEMA", "CHARACTER VARYING", "YES") .baselineValues("TABLE_NAME", "CHARACTER VARYING", "YES") .go(); }