@Test public void testFlattenWithParquet_0() throws Exception { String query = "select flatten(t.z) as zflat, flatten(t.l) as lflat from dfs_test.parquetTable t"; testPlanSubstrPatterns(query, new String[] {"columns=[`z`, `l`]"}, null); testBuilder().sqlQuery(query).unOrdered().baselineColumns("zflat", "lflat") .baselineValues(mapOf("orange", "yellow", "pink", "red", "white", "polar"), 4L) .baselineValues(mapOf("orange", "yellow", "pink", "red", "white", "polar"), 2L) .baselineValues(mapOf("pink", "purple"), 4L) .baselineValues(mapOf("pink", "purple"), 2L) .baselineValues(mapOf("pink", "lilac"), 4L) .baselineValues(mapOf("pink", "lilac"), 2L) .baselineValues(mapOf("orange", "stucco"), 4L) .baselineValues(mapOf("orange", "stucco"), 2L) .go(); }
@Test public void testFlattenWithProjIntoScan_2() throws Exception { String query = "select flatten(t.z) as zflat, flatten(t.l) as lflat, t.\"integer\" as myinteger from cp.\"/jsoninput/input2.json\" t"; testPlanSubstrPatterns(query, new String[] {"columns=[`z`, `l`, `integer`]"}, null); testBuilder().sqlQuery(query).unOrdered().baselineColumns("zflat", "lflat", "myinteger") .baselineValues(mapOf("orange", "yellow", "pink", "red"), 4L, 2010L) .baselineValues(mapOf("orange", "yellow", "pink", "red"), 2L, 2010L) .baselineValues(mapOf("pink", "purple"), 4L, 2010L) .baselineValues(mapOf("pink", "purple"), 2L, 2010L) .baselineValues(mapOf("pink", "lilac"), 4L, 2001L) .baselineValues(mapOf("pink", "lilac"), 2L, 2001L) .baselineValues(mapOf("orange", "stucco"), 4L, 6005L) .baselineValues(mapOf("orange", "stucco"), 2L, 6005L) .go(); }
@Test public void testFlattenWithProjIntoScan_4() throws Exception { String query = "select flatten(t.z) as zflat, flatten(t.l) as lflat from cp.\"/jsoninput/input2.json\" t"; testPlanSubstrPatterns(query, new String[] {"columns=[`z`, `l`]"}, null); testBuilder().sqlQuery(query).unOrdered().baselineColumns("zflat", "lflat") .baselineValues(mapOf("orange", "yellow", "pink", "red"), 4L) .baselineValues(mapOf("orange", "yellow", "pink", "red"), 2L) .baselineValues(mapOf("pink", "purple"), 4L) .baselineValues(mapOf("pink", "purple"), 2L) .baselineValues(mapOf("pink", "lilac"), 4L) .baselineValues(mapOf("pink", "lilac"), 2L) .baselineValues(mapOf("orange", "stucco"), 4L) .baselineValues(mapOf("orange", "stucco"), 2L) .go(); }
@Test public void testFlattenWithProjIntoScan_6() throws Exception { String query = "select flatten(t.z) as zflat from cp.\"/jsoninput/input2.json\" t"; testPlanSubstrPatterns(query, new String[] {"columns=[`z`]"}, null); testBuilder().sqlQuery(query).unOrdered().baselineColumns("zflat") .baselineValues(mapOf("orange", "yellow", "pink", "red")) .baselineValues(mapOf("pink", "purple")) .baselineValues(mapOf("pink", "lilac")) .baselineValues(mapOf("orange", "stucco")) .go(); }
@Test public void testNewNestedColumn() throws Exception { for (int i = 0; i < 100; i++) { elastic.dataFromFile(schema, table, NEW_NESTED_COLUMN_1); } elastic.dataFromFile(schema, table, NEW_NESTED_COLUMN_2); String query = String.format("select a from elasticsearch.%s.%s order by id", schema, table); TestBuilder testBuilder = testBuilder() .sqlQuery(query) .ordered() .baselineColumns("a"); for (int i = 0; i < 100; i++) { testBuilder.baselineValues(mapOf("b", mapOf("c1", 1L))); } testBuilder.baselineValues(mapOf("b", mapOf("c2", 2L))); testBuilder.go(); } }
@Test public void caseStruct2() throws Exception { final String query = "SELECT MAPPIFY(CASE WHEN t.a.b = 1 THEN t.a ELSE null END) AS a" + " FROM cp.\"jsoninput/input4.json\" t"; testBuilder() .sqlQuery(query) .ordered() .baselineColumns("a") .baselineValues(listOf( mapOf("key", "b", "value", 1L), mapOf("key", "c", "value", mapOf("d", 2L)))) .build() .run(); }
@Test public void testNewNestedColumn() throws Exception { test("ALTER SYSTEM SET \"" + ExecConstants.ENABLE_REATTEMPTS.getOptionName() + "\" = true"); try { final String query = String.format("select a from dfs_root.\"%s/schemachange/nested/\" order by id", TEST_RES_PATH); testBuilder() .sqlQuery(query) .ordered() .baselineColumns("a") .baselineValues(mapOf("b", mapOf("c1", 1L))) .baselineValues(mapOf("b", mapOf("c2", 2L))) .go(); } finally { test("ALTER SYSTEM RESET \"" + ExecConstants.ENABLE_REATTEMPTS.getOptionName() + "\""); } }
@Test public void testNestedArray() throws Exception { ElasticsearchCluster.ColumnData[] data = new ElasticsearchCluster.ColumnData[]{ new ElasticsearchCluster.ColumnData("location", OBJECT, new Object[][]{ {ImmutableMap.of("city", ImmutableList.of("San Francisco"))}, {ImmutableMap.of("city", ImmutableList.of("Oakland"))} }) }; elastic.load(schema, table, data); String sql = String.format("select location from elasticsearch.%s.%s l where contains(l.location.city:Oakland)", schema, table); testBuilder().sqlQuery(sql).unOrdered() .baselineColumns("location") .baselineValues(mapOf("city", listOf("Oakland"))) .go(); }
@Test public void caseStruct1() throws Exception { final String query = "SELECT CASE WHEN t.a.b = 1 THEN t.a ELSE null END AS a FROM cp.\"jsoninput/input4.json\" t"; testBuilder() .sqlQuery(query) .ordered() .baselineColumns("a") .baselineValues(mapOf("b", 1L, "c", mapOf("d", 2L))) .build() .run(); }
@Test public void complexProjectPushdown() throws Exception { File tmpFile = tmp.newFile("complexPpd.json"); try (BufferedWriter bw = new BufferedWriter(new FileWriter(tmpFile))) { bw.write("{\"a\": {\"Tuesday\": {\"close\": 19,\"open\": 10},\"Friday\": {\"close\": 19,\"open\": 10}}}"); } test("CREATE TABLE dfs_test.complexPpd STORE AS (type => 'arrow') AS SELECT * FROM dfs.\"" + tmpFile.getAbsolutePath() + "\""); String query = "SELECT t.a.\"Tuesday\" as col FROM TABLE(dfs_test.complexPpd(type => 'arrow')) as t"; testPlanMatchingPatterns(query, new String[]{"columns=\\[`a`.`Tuesday`\\]"}, null); testBuilder() .unOrdered() .sqlQuery(query) .baselineColumns("col") .baselineValues(mapOf("close", 19L, "open", 10L)) .go(); }
@Test public void testMapEmptyBetween() throws Exception { final String query = "select * from cp.\"vector/complex/map-empty-between.json\""; testBuilder() .sqlQuery(query) .ordered() .baselineColumns("a", "c") .baselineValues(mapOf("b", 1L), null) .baselineValues(null, 1L) .baselineValues(mapOf("b", 2L), null) .go(); }
@Test public void testNullAndEmptyMaps() throws Exception { testBuilder() .ordered() .sqlQuery("SELECT map FROM cp.\"/json/null_map.json\" LIMIT 10") .baselineColumns("map") .baselineValues(mapOf("a", 1L, "b", 2L, "c", 3L)) .baselineValues(((JsonStringHashMap<String, Object>) null)) .baselineValues(mapOf()) .baselineValues(((JsonStringHashMap<String, Object>) null)) .baselineValues(mapOf("a", 1L, "b", 2L, "c", 3L)) .build() .run(); }
@Test public void testMapEmptyFirst() throws Exception { final String query = "select * from cp.\"vector/complex/map-empty-first.json\""; testBuilder() .sqlQuery(query) .ordered() .baselineColumns("a", "c") .baselineValues(null, 1L) .baselineValues(mapOf("b", 1L), null) .baselineValues(mapOf("b", 2L), null) .go(); }
@Test public void testMapEmptyLast() throws Exception { final String query = "select * from cp.\"vector/complex/map-empty-last.json\""; testBuilder() .sqlQuery(query) .ordered() .baselineColumns("a", "c") .baselineValues(mapOf("b", 1L), null) .baselineValues(mapOf("b", 2L), null) .baselineValues(null, 1L) .go(); }
@Test public void testBaselineValsVerificationWithComplexAndNulls() throws Exception { testBuilder() .sqlQuery("select * from cp.\"/jsoninput/input2.json\" limit 1") .ordered() .baselineColumns("integer", "float", "x", "z", "l", "rl") .baselineValues(2010l, 17.4, mapOf("y", "kevin", "z", "paul"), listOf(mapOf("orange", "yellow", "pink", "red"), mapOf("pink", "purple")), listOf(4l, 2l), listOf(listOf(2l, 1l), listOf(4l, 6l))) .build().run(); }
@Test public void testRepeatedMapEmptyLast() throws Exception { final String query = "select * from cp.\"vector/complex/repeated-map-empty-last.json\""; testBuilder() .sqlQuery(query) .ordered() .baselineColumns("a") .baselineValues(listOf(mapOf("b", 1L))) .baselineValues(listOf(mapOf("b", 2L))) .baselineValues(listOf()) .go(); }
@Test public void testRepeatedMapEmptyBetween() throws Exception { final String query = "select * from cp.\"vector/complex/repeated-map-empty-between.json\""; testBuilder() .sqlQuery(query) .ordered() .baselineColumns("a") .baselineValues(listOf(mapOf("b", 1L))) .baselineValues(listOf()) .baselineValues(listOf(mapOf("b", 2L))) .go(); }
@Test public void testRepeatedMapEmptyFirst() throws Exception { final String query = "select * from cp.\"vector/complex/repeated-map-empty-first.json\""; testBuilder() .sqlQuery(query) .ordered() .baselineColumns("a") .baselineValues(listOf()) .baselineValues(listOf(mapOf("b", 1L))) .baselineValues(listOf(mapOf("b", 2L))) .go(); }
@Test public void testNullAndEmptyMaps() throws Exception { final AvroTestUtil.AvroTestRecordWriter testSetup = AvroTestUtil.generateNullAndEmptyMaps(); final String file = testSetup.getFilePath(); final String sql = "select a_int, b_map from dfs_root.\"" + file + "\""; testBuilder() .sqlQuery(sql) .ordered() .baselineColumns("a_int", "b_map") .baselineValues(0, mapOf("a", "1", "b", "2")) .baselineValues(1, null) .baselineValues(2, mapOf()) .baselineValues(3, null) .go(); }
@Test // DRILL-4459 public void testGetJsonObject() throws Exception { setEnableReAttempts(true); try { testBuilder() .sqlQuery("select convert_from(json, 'json') as json from hive.simple_json " + "where GET_JSON_OBJECT(simple_json.json, '$.employee_id') like 'Emp2'") .ordered() .baselineColumns("json") .baselineValues(TestBuilder.mapOf("employee_id", "Emp2", "full_name", "Kamesh", "first_name", "Bh", "last_name", "Venkata", "position", "Store")) .go(); } finally { setEnableReAttempts(false); } }