@Test public void testIntOnlyNumericFunctions() throws Exception { final String exprs = " cast(%s(review_count, cast(stars as integer)) as double), cast(%s(review_count, review_count) as double) "; final List<String> intOnlyFuncs = Lists.newArrayList("mod"); for (String s : intOnlyFuncs) { String elasticQuery = String.format("select " + exprs + " from %s", s, s, ELASTIC_TABLE); testBuilder() .sqlQuery(elasticQuery) .unOrdered() .sqlBaselineQuery(String.format("select " + exprs + " from %s", s, s, PARQUET_TABLE)) .go(); } }
/** * Tests function evaluation of Dremio's native expressions in comparison to * expressions that are pushed into elastic. * @throws Exception */ @Test public void testUnaryNumericFunctions() throws Exception { final List<String> unaryFunctions = Lists.newArrayList( "ABS", "ACOS", "ASIN", "ATAN", "COS", "CEILING", /*"COT",*/ "DEGREES", "FLOOR", "RADIANS", "SIN", "TAN", "EXP", "SIGN", "SQRT" ); final String exprs = " cast(%s(stars) as double), cast(%s(review_count) as double)"; for (String s : unaryFunctions) { String elasticQuery = String.format("select " + exprs + " from %s", s, s, ELASTIC_TABLE); testBuilder() .sqlQuery(elasticQuery) .unOrdered() .sqlBaselineQuery(String.format("select " + exprs + " from %s", s, s, PARQUET_TABLE)) .go(); } }
@Test public void testBinaryPrefixNumericFunctions() throws Exception { final String exprs = " cast(%s(stars, review_count) as double), cast(%s(stars, stars) as double), cast(%s(review_count, review_count) as double) "; String s = "power"; testBuilder() .sqlQuery(String.format("select " + exprs + " from %s", s, s, s, ELASTIC_TABLE)) .unOrdered() .sqlBaselineQuery(String.format("select " + exprs + " from %s", s, s, s, PARQUET_TABLE)) .go(); }
@Test public void testPartitionRoundRobin() throws Exception { try { setSessionOption(ExecConstants.SLICE_TARGET, "1"); final String input = "sys.options"; final String tableName = "dfs_test.roundrobintable"; final String query = "CREATE TABLE " + tableName + " PARTITION BY (TYPE, KIND) as SELECT * FROM " + input; runSQL(query); testBuilder() .unOrdered() .sqlQuery("SELECT name, kind, type, status, num_val, string_val FROM " + tableName) .sqlBaselineQuery("SELECT name, kind, type, status, num_val, string_val FROM " + input) .go(); final String tableName2 = "dfs_test.roundrobintable2"; final String query2 = "CREATE TABLE " + tableName2 + " ROUNDROBIN PARTITION BY (TYPE, KIND) as SELECT * FROM " + input; runSQL(query2); testBuilder() .unOrdered() .sqlQuery("SELECT name, kind, type, status, num_val, string_val FROM " + tableName2) .sqlBaselineQuery("SELECT name, kind, type, status, num_val, string_val FROM " + input) .go(); } finally { setSessionOption(ExecConstants.SLICE_TARGET, String.valueOf(ExecConstants.SLICE_TARGET_DEFAULT)); } }
@Test public void testPartitionHash() throws Exception { try { setSessionOption(ExecConstants.SLICE_TARGET, "1"); final String input = "sys.options"; final String tableName = "dfs_test.hashpartitiontable"; final String query = "CREATE TABLE " + tableName + " PARTITION BY (TYPE, KIND) as SELECT * FROM " + input; runSQL(query); testBuilder() .unOrdered() .sqlQuery("SELECT name, kind, type, status, num_val, string_val FROM " + tableName) .sqlBaselineQuery("SELECT name, kind, type, status, num_val, string_val FROM " + input) .go(); final String tableName2 = "dfs_test.hashpartitiontable2"; final String query2 = "CREATE TABLE " + tableName2 + " HASH PARTITION BY (TYPE, KIND) as SELECT * FROM " + input; runSQL(query2); testBuilder() .unOrdered() .sqlQuery("SELECT name, kind, type, status, num_val, string_val FROM " + tableName2) .sqlBaselineQuery("SELECT name, kind, type, status, num_val, string_val FROM " + input) .go(); } finally { setSessionOption(ExecConstants.SLICE_TARGET, String.valueOf(ExecConstants.SLICE_TARGET_DEFAULT)); } }
public void runTestAndValidate(String selection, String validationSelection, String inputTable, String outputFile, boolean sort) throws Exception { try { deleteTableIfExists(outputFile); test("use dfs_test"); // test("ALTER SESSION SET \"planner.add_producer_consumer\" = false"); String query = select(selection, inputTable, sort); System.out.println(outputFile); String create = "CREATE TABLE " + outputFile + " AS " + query; String validateQuery = select(validationSelection, outputFile, sort); test(create); test(validateQuery); // TODO: remove testBuilder() .unOrdered() .sqlQuery(validateQuery) .sqlBaselineQuery(query) .go(); Configuration hadoopConf = new Configuration(); Path output = new Path(getDfsTestTmpSchemaLocation(), outputFile); FileSystem fs = output.getFileSystem(hadoopConf); for (FileStatus file : fs.listStatus(output)) { ParquetMetadata footer = ParquetFileReader.readFooter(hadoopConf, file, SKIP_ROW_GROUPS); String version = footer.getFileMetaData().getKeyValueMetaData().get(DREMIO_VERSION_PROPERTY); assertEquals(DremioVersionInfo.getVersion(), version); PageHeaderUtil.validatePageHeaders(file.getPath(), footer); } } finally { deleteTableIfExists(outputFile); } }
@Test // see DRILL-2408 public void testWriteEmptyFileAfterFlush() throws Exception { final String outputFile = "testparquetwriteremptyfiles_test_write_empty_file_after_flush"; deleteTableIfExists(outputFile); try { // this specific value will force a flush just after the final row is written // this may cause the creation of a new "empty" parquet file test("ALTER SESSION SET \"store.parquet.block-size\" = 19926"); final String query = "SELECT * FROM cp.\"employee.json\" LIMIT 100"; test("CREATE TABLE dfs_test.%s AS %s", outputFile, query); // this query will fail if an "empty" file was created testBuilder() .unOrdered() .sqlQuery("SELECT * FROM dfs_test.%s", outputFile) .sqlBaselineQuery(query) .go(); } finally { // restore the session option test("ALTER SESSION SET \"store.parquet.block-size\" = %d", ExecConstants.PARQUET_BLOCK_SIZE_VALIDATOR.getDefault().getNumVal()); deleteTableIfExists(outputFile); } }
@Test public void testMultipleWriters() throws Exception { final String outputFile = "testparquetwriteremptyfiles_testmultiplewriters"; runSQL("alter session set \"planner.slice_target\" = 1"); try { final String query = "SELECT position_id FROM cp.\"employee.json\" WHERE position_id IN (15, 16) GROUP BY position_id"; test("CREATE TABLE dfs_test.%s AS %s", outputFile, query); // this query will fail if an "empty" file was created testBuilder() .unOrdered() .sqlQuery("SELECT * FROM dfs_test.%s", outputFile) .sqlBaselineQuery(query) .go(); } finally { runSQL("alter session set \"planner.slice_target\" = " + ExecConstants.SLICE_TARGET_DEFAULT); deleteTableIfExists(outputFile); } }
@Test public void orcVectorizedTest() throws Exception { testBuilder() .sqlQuery("SELECT * from hive.orc_region") .unOrdered() .sqlBaselineQuery("SELECT * FROM hive.parquet_region") .go(); // project only few columns testBuilder() .sqlQuery("SELECT r_comment, r_regionkey from hive.orc_region") .unOrdered() .sqlBaselineQuery("SELECT r_comment, r_regionkey FROM hive.parquet_region") .go(); } }
@Test public void countStar() throws Exception { testPhysicalPlan("SELECT count(*) FROM hive.kv", "columns=[]"); testPhysicalPlan("SELECT count(*) FROM hive.kv_parquet", "columns=[]"); testBuilder() .sqlQuery("SELECT count(*) as cnt FROM hive.kv") .unOrdered() .sqlBaselineQuery("SELECT count(key) as cnt FROM hive.kv") .go(); testBuilder() .sqlQuery("SELECT count(*) as cnt FROM hive.kv_parquet") .unOrdered() .sqlBaselineQuery("SELECT count(key) as cnt FROM hive.kv_parquet") .go(); }
@Test public void testComplex243() throws Exception { final String jsonFile = "cp.\"parquet/complex243.json\""; final String parquetTable = "dfs_test.\"complex243_json\""; final String ctas = "CREATE TABLE " + parquetTable + " AS SELECT id, ooa FROM " + jsonFile; runSQL(ctas); final String query = "SELECT t.id, t.ooa[2].a.aa.aaa as aaa FROM %s t"; testBuilder() .unOrdered() .sqlQuery(query, parquetTable) .sqlBaselineQuery(query, jsonFile) .go(); } }
@Test public void orderByCompareCountExcelText() throws Exception { testBuilder() .sqlQuery(countQuery(E_ORDERBY)) .unOrdered() .sqlBaselineQuery(countQuery(T_ORDERBY)) .go(); }
@Test public void test4349() throws Exception { // start by creating a parquet file from the input csv file runSQL("CREATE TABLE dfs_test.\"4349\" AS SELECT columns[0] id, CAST(NULLIF(columns[1], '') AS DOUBLE) val FROM cp.\"parquet2/4349.csv.gz\""); // querying the parquet file should return the same results found in the csv file testBuilder() .unOrdered() .sqlQuery("SELECT * FROM dfs_test.\"4349\" WHERE id = 'b'") .sqlBaselineQuery("SELECT columns[0] id, CAST(NULLIF(columns[1], '') AS DOUBLE) val FROM cp.\"parquet2/4349.csv.gz\" WHERE columns[0] = 'b'") .go(); }
@Test public void join() throws Exception { final String joinArrow = "SELECT * FROM TABLE(dfs_test.lineitem(type => 'arrow')) l JOIN " + "TABLE(dfs_test.orders(type => 'arrow')) o ON l.l_orderkey = o.o_orderkey"; final String joinParquet = "SELECT * FROM cp.\"tpch/lineitem.parquet\" l JOIN " + "cp.\"tpch/orders.parquet\" o ON l.l_orderkey = o.o_orderkey"; testBuilder() .unOrdered() .sqlQuery(joinArrow) .sqlBaselineQuery(joinParquet) .go(); }
@Test public void joinCompareCountExcelText() throws Exception { testBuilder() .sqlQuery(countQuery(E_JOIN)) .unOrdered() .sqlBaselineQuery(countQuery(T_JOIN)) .go(); }
@Test // See DRILL-3476 public void testNestedFilter() throws Exception { String query = "select a from cp.\"jsoninput/nestedFilter.json\" t where t.a.b = 1"; String baselineQuery = "select * from cp.\"jsoninput/nestedFilter.json\" t where t.a.b = 1"; testBuilder() .sqlQuery(query) .unOrdered() .sqlBaselineQuery(baselineQuery) .go(); } }
@Test public void groupByCompareCountExcelText() throws Exception { testBuilder() .sqlQuery(countQuery(E_GROUPBY)) .unOrdered() .sqlBaselineQuery(countQuery(T_GROUPBY)) .go(); }
@Test public void simple() throws Exception { final String query = "SELECT * FROM TABLE(dfs_test.arrowRegion(type => 'arrow'))"; test(query); testBuilder() .unOrdered() .sqlQuery(query) .sqlBaselineQuery("SELECT * FROM cp.\"region.json\"") .go(); }
@Test public void testSkipAll() throws Exception { final String query = "SELECT count(*) FROM cp.\"json/map_list_map.json\""; testPhysicalPlan(query, "columns=[]"); testBuilder() .sqlQuery(query) .unOrdered() .sqlBaselineQuery("SELECT count(id) FROM cp.\"json/map_list_map.json\"") .go(); }
@Test public void simpleCompareCountExcelText() throws Exception { testBuilder() .sqlQuery(countQuery(E_SIMPLE)) .unOrdered() .sqlBaselineQuery(countQuery(T_SIMPLE)) .go(); }