Java Code Examples for org.apache.flink.table.api.TableEnvironment#sqlQuery()

@Test
public void testGetStatsFromCatalog() throws Exception {
	EnvironmentSettings settings = EnvironmentSettings.newInstance().useBlinkPlanner().inBatchMode().build();
	TableEnvironment tEnv = TableEnvironment.create(settings);
	tEnv.registerTableSource("T1", new TestTableSource(true, tableSchema));
	tEnv.registerTableSource("T2", new TestTableSource(true, tableSchema));
	Catalog catalog = tEnv.getCatalog(tEnv.getCurrentCatalog()).orElse(null);
	assertNotNull(catalog);

	catalog.alterTableStatistics(ObjectPath.fromString("default_database.T1"),
			new CatalogTableStatistics(100, 10, 1000L, 2000L), true);
	catalog.alterTableStatistics(ObjectPath.fromString("default_database.T2"),
			new CatalogTableStatistics(100000000, 1000, 1000000000L, 2000000000L), true);
	catalog.alterTableColumnStatistics(ObjectPath.fromString("default_database.T1"), createColumnStats(), true);
	catalog.alterTableColumnStatistics(ObjectPath.fromString("default_database.T2"), createColumnStats(), true);

	Table table = tEnv.sqlQuery("select * from T1, T2 where T1.s3 = T2.s3");
	String result = tEnv.explain(table);
	// T1 is broadcast side
	String expected = TableTestUtil.readFromResource("/explain/testGetStatsFromCatalog.out");
	assertEquals(expected, TableTestUtil.replaceStageId(result));
}
 

@Test
public void testReadComplexDataType() throws Exception {
	final String dbName = "source_db";
	final String tblName = "complex_test";
	TableEnvironment tEnv = createTableEnv();
	tEnv.executeSql("create table source_db.complex_test(" +
					"a array<int>, m map<int,string>, s struct<f1:int,f2:bigint>)");
	Integer[] array = new Integer[]{1, 2, 3};
	Map<Integer, String> map = new LinkedHashMap<>();
	map.put(1, "a");
	map.put(2, "b");
	Object[] struct = new Object[]{3, 3L};
	HiveTestUtils.createTextTableInserter(hiveShell, dbName, tblName)
			.addRow(new Object[]{array, map, struct})
			.commit();
	Table src = tEnv.sqlQuery("select * from hive.source_db.complex_test");
	List<Row> rows = Lists.newArrayList(src.execute().collect());
	Assert.assertEquals(1, rows.size());
	assertArrayEquals(array, (Integer[]) rows.get(0).getField(0));
	assertEquals(map, rows.get(0).getField(1));
	assertEquals(Row.of(struct[0], struct[1]), rows.get(0).getField(2));
}
 

@Test
public void testDefaultPartitionName() throws Exception {
	TableEnvironment tableEnv = getTableEnvWithHiveCatalog();
	tableEnv.executeSql("create database db1");
	tableEnv.executeSql("create table db1.src (x int, y int)");
	tableEnv.executeSql("create table db1.part (x int) partitioned by (y int)");
	HiveTestUtils.createTextTableInserter(hiveShell, "db1", "src").addRow(new Object[]{1, 1}).addRow(new Object[]{2, null}).commit();

	// test generating partitions with default name
	TableEnvUtil.execInsertSqlAndWaitResult(tableEnv, "insert into db1.part select * from db1.src");
	HiveConf hiveConf = hiveShell.getHiveConf();
	String defaultPartName = hiveConf.getVar(HiveConf.ConfVars.DEFAULTPARTITIONNAME);
	Table hiveTable = hmsClient.getTable("db1", "part");
	Path defaultPartPath = new Path(hiveTable.getSd().getLocation(), "y=" + defaultPartName);
	FileSystem fs = defaultPartPath.getFileSystem(hiveConf);
	assertTrue(fs.exists(defaultPartPath));

	TableImpl flinkTable = (TableImpl) tableEnv.sqlQuery("select y, x from db1.part order by x");
	List<Row> rows = Lists.newArrayList(flinkTable.execute().collect());
	assertEquals(Arrays.toString(new String[]{"1,1", "null,2"}), rows.toString());

	tableEnv.executeSql("drop database db1 cascade");
}
 

/**
 * Test to read from partition table.
 * @throws Exception
 */
@Test
public void testReadPartitionTable() throws Exception {
	final String dbName = "source_db";
	final String tblName = "test_table_pt";
	TableEnvironment tEnv = createTableEnv();
	tEnv.executeSql("CREATE TABLE source_db.test_table_pt " +
					"(`year` STRING, `value` INT) partitioned by (pt int)");
	HiveTestUtils.createTextTableInserter(hiveShell, dbName, tblName)
			.addRow(new Object[]{"2014", 3})
			.addRow(new Object[]{"2014", 4})
			.commit("pt=0");
	HiveTestUtils.createTextTableInserter(hiveShell, dbName, tblName)
			.addRow(new Object[]{"2015", 2})
			.addRow(new Object[]{"2015", 5})
			.commit("pt=1");
	Table src = tEnv.sqlQuery("select * from hive.source_db.test_table_pt");
	List<Row> rows = Lists.newArrayList(src.execute().collect());

	assertEquals(4, rows.size());
	Object[] rowStrings = rows.stream().map(Row::toString).sorted().toArray();
	assertArrayEquals(new String[]{"2014,3,0", "2014,4,0", "2015,2,1", "2015,5,1"}, rowStrings);
}
 

/**
 * Test to read from partition table.
 * @throws Exception
 */
@Test
public void testReadPartitionTable() throws Exception {
	final String catalogName = "hive";
	final String dbName = "source_db";
	final String tblName = "test_table_pt";
	hiveShell.execute("CREATE TABLE source_db.test_table_pt " +
					"(year STRING, value INT) partitioned by (pt int);");
	hiveShell.insertInto(dbName, tblName)
			.withColumns("year", "value", "pt")
			.addRow("2014", 3, 0)
			.addRow("2014", 4, 0)
			.addRow("2015", 2, 1)
			.addRow("2015", 5, 1)
			.commit();
	TableEnvironment tEnv = HiveTestUtils.createTableEnv();
	tEnv.registerCatalog(catalogName, hiveCatalog);
	Table src = tEnv.sqlQuery("select * from hive.source_db.test_table_pt");
	List<Row> rows = JavaConverters.seqAsJavaListConverter(TableUtil.collect((TableImpl) src)).asJava();

	assertEquals(4, rows.size());
	Object[] rowStrings = rows.stream().map(Row::toString).sorted().toArray();
	assertArrayEquals(new String[]{"2014,3,0", "2014,4,0", "2015,2,1", "2015,5,1"}, rowStrings);
}
 

@Test
public void testParallelismSetting() {
	final String dbName = "source_db";
	final String tblName = "test_parallelism";
	TableEnvironment tEnv = createTableEnv();
	tEnv.executeSql("CREATE TABLE source_db.test_parallelism " +
			"(`year` STRING, `value` INT) partitioned by (pt int)");
	HiveTestUtils.createTextTableInserter(hiveShell, dbName, tblName)
			.addRow(new Object[]{"2014", 3})
			.addRow(new Object[]{"2014", 4})
			.commit("pt=0");
	HiveTestUtils.createTextTableInserter(hiveShell, dbName, tblName)
			.addRow(new Object[]{"2015", 2})
			.addRow(new Object[]{"2015", 5})
			.commit("pt=1");
	Table table = tEnv.sqlQuery("select * from hive.source_db.test_parallelism");
	PlannerBase planner = (PlannerBase) ((TableEnvironmentImpl) tEnv).getPlanner();
	RelNode relNode = planner.optimize(TableTestUtil.toRelNode(table));
	ExecNode execNode = planner.translateToExecNodePlan(toScala(Collections.singletonList(relNode))).get(0);
	@SuppressWarnings("unchecked")
	Transformation transformation = execNode.translateToPlan(planner);
	Assert.assertEquals(2, transformation.getParallelism());
}
 

@Test
public void testReadNonPartitionedTable() throws Exception {
	final String catalogName = "hive";
	final String dbName = "source_db";
	final String tblName = "test";
	hiveShell.execute("CREATE TABLE source_db.test ( a INT, b INT, c STRING, d BIGINT, e DOUBLE)");
	hiveShell.insertInto(dbName, tblName)
			.withAllColumns()
			.addRow(1, 1, "a", 1000L, 1.11)
			.addRow(2, 2, "b", 2000L, 2.22)
			.addRow(3, 3, "c", 3000L, 3.33)
			.addRow(4, 4, "d", 4000L, 4.44)
			.commit();

	TableEnvironment tEnv = HiveTestUtils.createTableEnv();
	tEnv.registerCatalog(catalogName, hiveCatalog);
	Table src = tEnv.sqlQuery("select * from hive.source_db.test");
	List<Row> rows = JavaConverters.seqAsJavaListConverter(TableUtil.collect((TableImpl) src)).asJava();

	Assert.assertEquals(4, rows.size());
	Assert.assertEquals("1,1,a,1000,1.11", rows.get(0).toString());
	Assert.assertEquals("2,2,b,2000,2.22", rows.get(1).toString());
	Assert.assertEquals("3,3,c,3000,3.33", rows.get(2).toString());
	Assert.assertEquals("4,4,d,4000,4.44", rows.get(3).toString());
}
 

@Test
public void testProjectionPushDown() throws Exception {
	TableEnvironment tableEnv = createTableEnv();
	tableEnv.executeSql("create table src(x int,y string) partitioned by (p1 bigint, p2 string)");
	try {
		HiveTestUtils.createTextTableInserter(hiveShell, "default", "src")
				.addRow(new Object[]{1, "a"})
				.addRow(new Object[]{2, "b"})
				.commit("p1=2013, p2='2013'");
		HiveTestUtils.createTextTableInserter(hiveShell, "default", "src")
				.addRow(new Object[]{3, "c"})
				.commit("p1=2014, p2='2014'");
		Table table = tableEnv.sqlQuery("select p1, count(y) from hive.`default`.src group by p1");
		String[] explain = table.explain().split("==.*==\n");
		assertEquals(4, explain.length);
		String logicalPlan = explain[2];
		String physicalPlan = explain[3];
		String expectedExplain =
				"HiveTableSource(x, y, p1, p2) TablePath: default.src, PartitionPruned: false, PartitionNums: null, ProjectedFields: [2, 1]";
		assertTrue(logicalPlan, logicalPlan.contains(expectedExplain));
		assertTrue(physicalPlan, physicalPlan.contains(expectedExplain));

		List<Row> rows = Lists.newArrayList(table.execute().collect());
		assertEquals(2, rows.size());
		Object[] rowStrings = rows.stream().map(Row::toString).sorted().toArray();
		assertArrayEquals(new String[]{"2013,2", "2014,1"}, rowStrings);
	} finally {
		tableEnv.executeSql("drop table src");
	}
}
 

@Test
public void testPartitionPrunning() throws Exception {
	final String catalogName = "hive";
	final String dbName = "source_db";
	final String tblName = "test_table_pt_1";
	hiveShell.execute("CREATE TABLE source_db.test_table_pt_1 " +
					"(year STRING, value INT) partitioned by (pt int);");
	hiveShell.insertInto(dbName, tblName)
			.withColumns("year", "value", "pt")
			.addRow("2014", 3, 0)
			.addRow("2014", 4, 0)
			.addRow("2015", 2, 1)
			.addRow("2015", 5, 1)
			.commit();
	TableEnvironment tEnv = HiveTestUtils.createTableEnv();
	tEnv.registerCatalog(catalogName, hiveCatalog);
	Table src = tEnv.sqlQuery("select * from hive.source_db.test_table_pt_1 where pt = 0");
	// first check execution plan to ensure partition prunning works
	String[] explain = tEnv.explain(src).split("==.*==\n");
	assertEquals(4, explain.length);
	String abstractSyntaxTree = explain[1];
	String optimizedLogicalPlan = explain[2];
	String physicalExecutionPlan = explain[3];
	assertTrue(abstractSyntaxTree.contains("HiveTableSource(year, value, pt) TablePath: source_db.test_table_pt_1, PartitionPruned: false, PartitionNums: 2]"));
	assertTrue(optimizedLogicalPlan.contains("HiveTableSource(year, value, pt) TablePath: source_db.test_table_pt_1, PartitionPruned: true, PartitionNums: 1]"));
	assertTrue(physicalExecutionPlan.contains("HiveTableSource(year, value, pt) TablePath: source_db.test_table_pt_1, PartitionPruned: true, PartitionNums: 1]"));
	// second check execute results
	List<Row> rows = JavaConverters.seqAsJavaListConverter(TableUtil.collect((TableImpl) src)).asJava();
	assertEquals(2, rows.size());
	Object[] rowStrings = rows.stream().map(Row::toString).sorted().toArray();
	assertArrayEquals(new String[]{"2014,3,0", "2014,4,0"}, rowStrings);
}
 

@Test
public void testLimitPushDown() throws Exception {
	TableEnvironment tableEnv = createTableEnv();
	tableEnv.executeSql("create table src (a string)");
	try {
		HiveTestUtils.createTextTableInserter(hiveShell, "default", "src")
					.addRow(new Object[]{"a"})
					.addRow(new Object[]{"b"})
					.addRow(new Object[]{"c"})
					.addRow(new Object[]{"d"})
					.commit();
		//Add this to obtain correct stats of table to avoid FLINK-14965 problem
		hiveShell.execute("analyze table src COMPUTE STATISTICS");
		Table table = tableEnv.sqlQuery("select * from hive.`default`.src limit 1");
		String[] explain = table.explain().split("==.*==\n");
		assertEquals(4, explain.length);
		String logicalPlan = explain[2];
		String physicalPlan = explain[3];
		String expectedExplain = "HiveTableSource(a) TablePath: default.src, PartitionPruned: false, " +
								"PartitionNums: null, LimitPushDown true, Limit 1";
		assertTrue(logicalPlan.contains(expectedExplain));
		assertTrue(physicalPlan.contains(expectedExplain));

		List<Row> rows = Lists.newArrayList(table.execute().collect());
		assertEquals(1, rows.size());
		Object[] rowStrings = rows.stream().map(Row::toString).sorted().toArray();
		assertArrayEquals(new String[]{"a"}, rowStrings);
	} finally {
		tableEnv.executeSql("drop table src");
	}
}
 

@Test
public void testParallelismOnLimitPushDown() {
	final String dbName = "source_db";
	final String tblName = "test_parallelism_limit_pushdown";
	TableEnvironment tEnv = createTableEnv();
	tEnv.getConfig().getConfiguration().setBoolean(
			HiveOptions.TABLE_EXEC_HIVE_INFER_SOURCE_PARALLELISM, false);
	tEnv.getConfig().getConfiguration().setInteger(
			ExecutionConfigOptions.TABLE_EXEC_RESOURCE_DEFAULT_PARALLELISM, 2);
	tEnv.executeSql("CREATE TABLE source_db.test_parallelism_limit_pushdown " +
				"(`year` STRING, `value` INT) partitioned by (pt int)");
	HiveTestUtils.createTextTableInserter(hiveShell, dbName, tblName)
				.addRow(new Object[]{"2014", 3})
				.addRow(new Object[]{"2014", 4})
				.commit("pt=0");
	HiveTestUtils.createTextTableInserter(hiveShell, dbName, tblName)
				.addRow(new Object[]{"2015", 2})
				.addRow(new Object[]{"2015", 5})
				.commit("pt=1");
	Table table = tEnv.sqlQuery("select * from hive.source_db.test_parallelism_limit_pushdown limit 1");
	PlannerBase planner = (PlannerBase) ((TableEnvironmentImpl) tEnv).getPlanner();
	RelNode relNode = planner.optimize(TableTestUtil.toRelNode(table));
	ExecNode execNode = planner.translateToExecNodePlan(toScala(Collections.singletonList(relNode))).get(0);
	@SuppressWarnings("unchecked")
	Transformation transformation = execNode.translateToPlan(planner);
	Assert.assertEquals(1, ((PartitionTransformation) ((OneInputTransformation) transformation).getInput())
		.getInput().getParallelism());
}
 

@Test
public void testPartitionFilterDateTimestamp() throws Exception {
	TableEnvironment tableEnv = HiveTestUtils.createTableEnvWithBlinkPlannerBatchMode(SqlDialect.HIVE);
	TestPartitionFilterCatalog catalog = new TestPartitionFilterCatalog(
			hiveCatalog.getName(), hiveCatalog.getDefaultDatabase(), hiveCatalog.getHiveConf(), hiveCatalog.getHiveVersion());
	tableEnv.registerCatalog(catalog.getName(), catalog);
	tableEnv.useCatalog(catalog.getName());
	tableEnv.executeSql("create database db1");
	try {
		tableEnv.executeSql("create table db1.part(x int) partitioned by (p1 date,p2 timestamp)");
		HiveTestUtils.createTextTableInserter(hiveShell, "db1", "part")
				.addRow(new Object[]{1}).commit("p1='2018-08-08',p2='2018-08-08 08:08:08'");
		HiveTestUtils.createTextTableInserter(hiveShell, "db1", "part")
				.addRow(new Object[]{2}).commit("p1='2018-08-09',p2='2018-08-08 08:08:09'");
		HiveTestUtils.createTextTableInserter(hiveShell, "db1", "part")
				.addRow(new Object[]{3}).commit("p1='2018-08-10',p2='2018-08-08 08:08:10'");

		Table query = tableEnv.sqlQuery(
				"select x from db1.part where p1>cast('2018-08-09' as date) and p2<>cast('2018-08-08 08:08:09' as timestamp)");
		String[] explain = query.explain().split("==.*==\n");
		assertTrue(catalog.fallback);
		String optimizedPlan = explain[2];
		assertTrue(optimizedPlan, optimizedPlan.contains("PartitionPruned: true, PartitionNums: 1"));
		List<Row> results = Lists.newArrayList(query.execute().collect());
		assertEquals("[3]", results.toString());
		System.out.println(results);
	} finally {
		tableEnv.executeSql("drop database db1 cascade");
	}
}
 

@Test
public void testTableSourceProjection() throws Exception {
	TableEnvironment tEnv = createBatchTableEnv();
	HBaseTableSource hbaseTable = new HBaseTableSource(getConf(), TEST_TABLE_1);
	hbaseTable.addColumn(FAMILY1, F1COL1, Integer.class);
	hbaseTable.addColumn(FAMILY2, F2COL1, String.class);
	hbaseTable.addColumn(FAMILY2, F2COL2, Long.class);
	hbaseTable.addColumn(FAMILY3, F3COL1, Double.class);
	hbaseTable.addColumn(FAMILY3, F3COL2, Boolean.class);
	hbaseTable.addColumn(FAMILY3, F3COL3, String.class);
	tEnv.registerTableSource("hTable", hbaseTable);

	Table table = tEnv.sqlQuery("SELECT " +
		"  h.family1.col1, " +
		"  h.family3.col1, " +
		"  h.family3.col2, " +
		"  h.family3.col3 " +
		"FROM hTable AS h");

	List<Row> results = collectBatchResult(table);
	String expected =
		"10,1.01,false,Welt-1\n" +
			"20,2.02,true,Welt-2\n" +
			"30,3.03,false,Welt-3\n" +
			"40,4.04,true,Welt-4\n" +
			"50,5.05,false,Welt-5\n" +
			"60,6.06,true,Welt-6\n" +
			"70,7.07,false,Welt-7\n" +
			"80,8.08,true,Welt-8\n";

	TestBaseUtils.compareResultAsText(results, expected);
}
 

public static void main(String[] args) throws Exception {
	ParameterTool params = ParameterTool.fromArgs(args);
	String sourceTablePath = params.getRequired("sourceTablePath");
	String queryPath = params.getRequired("queryPath");
	String sinkTablePath = params.getRequired("sinkTablePath");
	Boolean useTableStats = params.getBoolean("useTableStats");
	TableEnvironment tableEnvironment = prepareTableEnv(sourceTablePath, useTableStats);

	//execute TPC-DS queries
	for (String queryId : TPCDS_QUERIES) {
		System.out.println("[INFO]Run TPC-DS query " + queryId + " ...");
		String queryName = QUERY_PREFIX + queryId + QUERY_SUFFIX;
		String queryFilePath = queryPath + FILE_SEPARATOR + queryName;
		String queryString = loadFile2String(queryFilePath);
		Table resultTable = tableEnvironment.sqlQuery(queryString);

		//register sink table
		String sinkTableName = QUERY_PREFIX + queryId + "_sinkTable";
		((TableEnvironmentInternal) tableEnvironment).registerTableSinkInternal(sinkTableName,
				new CsvTableSink(
					sinkTablePath + FILE_SEPARATOR + queryId + RESULT_SUFFIX,
					COL_DELIMITER,
					1,
					FileSystem.WriteMode.OVERWRITE,
					resultTable.getSchema().getFieldNames(),
					resultTable.getSchema().getFieldDataTypes()
				));
		TableResult tableResult = resultTable.executeInsert(sinkTableName);
		// wait job finish
		tableResult.getJobClient().get()
				.getJobExecutionResult(Thread.currentThread().getContextClassLoader())
				.get();
		System.out.println("[INFO]Run TPC-DS query " + queryId + " success.");
	}
}
 

@Test
public void testTableSink() throws Exception {
	HBaseTableSchema schema = new HBaseTableSchema();
	schema.addColumn(FAMILY1, F1COL1, Integer.class);
	schema.addColumn(FAMILY2, F2COL1, String.class);
	schema.addColumn(FAMILY2, F2COL2, Long.class);
	schema.setRowKey("rk", Integer.class);
	schema.addColumn(FAMILY3, F3COL1, Double.class);
	schema.addColumn(FAMILY3, F3COL2, Boolean.class);
	schema.addColumn(FAMILY3, F3COL3, String.class);

	Map<String, String> tableProperties = new HashMap<>();
	tableProperties.put("connector.type", "hbase");
	tableProperties.put("connector.version", "1.4.3");
	tableProperties.put("connector.property-version", "1");
	tableProperties.put("connector.table-name", TEST_TABLE_2);
	tableProperties.put("connector.zookeeper.quorum", getZookeeperQuorum());
	tableProperties.put("connector.zookeeper.znode.parent", "/hbase");
	DescriptorProperties descriptorProperties = new DescriptorProperties(true);
	descriptorProperties.putTableSchema(SCHEMA, schema.convertsToTableSchema());
	descriptorProperties.putProperties(tableProperties);
	TableSink tableSink = TableFactoryService
		.find(HBaseTableFactory.class, descriptorProperties.asMap())
		.createTableSink(descriptorProperties.asMap());

	StreamExecutionEnvironment execEnv = StreamExecutionEnvironment.getExecutionEnvironment();
	StreamTableEnvironment tEnv = StreamTableEnvironment.create(execEnv, streamSettings);

	DataStream<Row> ds = execEnv.fromCollection(testData1).returns(testTypeInfo1);
	tEnv.registerDataStream("src", ds);
	tEnv.registerTableSink("hbase", tableSink);

	String query = "INSERT INTO hbase SELECT ROW(f1c1), ROW(f2c1, f2c2), rowkey, ROW(f3c1, f3c2, f3c3) FROM src";
	tEnv.sqlUpdate(query);

	// wait to finish
	tEnv.execute("HBase Job");

	// start a batch scan job to verify contents in HBase table
	// start a batch scan job to verify contents in HBase table
	TableEnvironment batchTableEnv = createBatchTableEnv();

	HBaseTableSource hbaseTable = new HBaseTableSource(getConf(), TEST_TABLE_2);
	hbaseTable.setRowKey("rowkey", Integer.class);
	hbaseTable.addColumn(FAMILY1, F1COL1, Integer.class);
	hbaseTable.addColumn(FAMILY2, F2COL1, String.class);
	hbaseTable.addColumn(FAMILY2, F2COL2, Long.class);
	hbaseTable.addColumn(FAMILY3, F3COL1, Double.class);
	hbaseTable.addColumn(FAMILY3, F3COL2, Boolean.class);
	hbaseTable.addColumn(FAMILY3, F3COL3, String.class);
	batchTableEnv.registerTableSource("hTable", hbaseTable);

	Table table = batchTableEnv.sqlQuery(
		"SELECT " +
			"  h.rowkey, " +
			"  h.family1.col1, " +
			"  h.family2.col1, " +
			"  h.family2.col2, " +
			"  h.family3.col1, " +
			"  h.family3.col2, " +
			"  h.family3.col3 " +
			"FROM hTable AS h"
	);

	List<Row> results = collectBatchResult(table);
	String expected =
		"1,10,Hello-1,100,1.01,false,Welt-1\n" +
			"2,20,Hello-2,200,2.02,true,Welt-2\n" +
			"3,30,Hello-3,300,3.03,false,Welt-3\n" +
			"4,40,,400,4.04,true,Welt-4\n" +
			"5,50,Hello-5,500,5.05,false,Welt-5\n" +
			"6,60,Hello-6,600,6.06,true,Welt-6\n" +
			"7,70,Hello-7,700,7.07,false,Welt-7\n" +
			"8,80,,800,8.08,true,Welt-8\n";

	TestBaseUtils.compareResultAsText(results, expected);
}
 

@Test
public void testTableSourceFullScan() throws Exception {
	TableEnvironment tEnv = createBatchTableEnv();
	if (isLegacyConnector) {
		HBaseTableSource hbaseTable = new HBaseTableSource(getConf(), TEST_TABLE_1);
		hbaseTable.addColumn(FAMILY1, F1COL1, Integer.class);
		hbaseTable.addColumn(FAMILY2, F2COL1, String.class);
		hbaseTable.addColumn(FAMILY2, F2COL2, Long.class);
		hbaseTable.addColumn(FAMILY3, F3COL1, Double.class);
		hbaseTable.addColumn(FAMILY3, F3COL2, Boolean.class);
		hbaseTable.addColumn(FAMILY3, F3COL3, String.class);
		hbaseTable.setRowKey(ROW_KEY, Integer.class);
		((TableEnvironmentInternal) tEnv).registerTableSourceInternal("hTable", hbaseTable);
	} else {
		tEnv.executeSql(
				"CREATE TABLE hTable (" +
				" family1 ROW<col1 INT>," +
				" family2 ROW<col1 STRING, col2 BIGINT>," +
				" family3 ROW<col1 DOUBLE, col2 BOOLEAN, col3 STRING>," +
				" rowkey INT," +
				" PRIMARY KEY (rowkey) NOT ENFORCED" +
				") WITH (" +
				" 'connector' = 'hbase-1.4'," +
				" 'table-name' = '" + TEST_TABLE_1 + "'," +
				" 'zookeeper.quorum' = '" + getZookeeperQuorum() + "'" +
				")");
	}

	Table table = tEnv.sqlQuery("SELECT " +
		"  h.family1.col1, " +
		"  h.family2.col1, " +
		"  h.family2.col2, " +
		"  h.family3.col1, " +
		"  h.family3.col2, " +
		"  h.family3.col3 " +
		"FROM hTable AS h");

	List<Row> results = collectBatchResult(table);
	String expected =
		"10,Hello-1,100,1.01,false,Welt-1\n" +
			"20,Hello-2,200,2.02,true,Welt-2\n" +
			"30,Hello-3,300,3.03,false,Welt-3\n" +
			"40,null,400,4.04,true,Welt-4\n" +
			"50,Hello-5,500,5.05,false,Welt-5\n" +
			"60,Hello-6,600,6.06,true,Welt-6\n" +
			"70,Hello-7,700,7.07,false,Welt-7\n" +
			"80,null,800,8.08,true,Welt-8\n";

	TestBaseUtils.compareResultAsText(results, expected);
}
 

@Test
public void testTableSourceProjection() throws Exception {
	TableEnvironment tEnv = createBatchTableEnv();

	if (isLegacyConnector) {
		HBaseTableSource hbaseTable = new HBaseTableSource(getConf(), TEST_TABLE_1);
		hbaseTable.addColumn(FAMILY1, F1COL1, Integer.class);
		hbaseTable.addColumn(FAMILY2, F2COL1, String.class);
		hbaseTable.addColumn(FAMILY2, F2COL2, Long.class);
		hbaseTable.addColumn(FAMILY3, F3COL1, Double.class);
		hbaseTable.addColumn(FAMILY3, F3COL2, Boolean.class);
		hbaseTable.addColumn(FAMILY3, F3COL3, String.class);
		hbaseTable.setRowKey(ROW_KEY, Integer.class);
		((TableEnvironmentInternal) tEnv).registerTableSourceInternal("hTable", hbaseTable);
	} else {
		tEnv.executeSql(
				"CREATE TABLE hTable (" +
				" family1 ROW<col1 INT>," +
				" family2 ROW<col1 STRING, col2 BIGINT>," +
				" family3 ROW<col1 DOUBLE, col2 BOOLEAN, col3 STRING>," +
				" rowkey INT," +
				" PRIMARY KEY (rowkey) NOT ENFORCED" +
				") WITH (" +
				" 'connector' = 'hbase-1.4'," +
				" 'table-name' = '" + TEST_TABLE_1 + "'," +
				" 'zookeeper.quorum' = '" + getZookeeperQuorum() + "'" +
				")");
	}

	Table table = tEnv.sqlQuery("SELECT " +
		"  h.family1.col1, " +
		"  h.family3.col1, " +
		"  h.family3.col2, " +
		"  h.family3.col3 " +
		"FROM hTable AS h");

	List<Row> results = collectBatchResult(table);
	String expected =
		"10,1.01,false,Welt-1\n" +
			"20,2.02,true,Welt-2\n" +
			"30,3.03,false,Welt-3\n" +
			"40,4.04,true,Welt-4\n" +
			"50,5.05,false,Welt-5\n" +
			"60,6.06,true,Welt-6\n" +
			"70,7.07,false,Welt-7\n" +
			"80,8.08,true,Welt-8\n";

	TestBaseUtils.compareResultAsText(results, expected);
}
 

@Test
public void testTableSink() throws Exception {
	StreamExecutionEnvironment execEnv = StreamExecutionEnvironment.getExecutionEnvironment();
	StreamTableEnvironment tEnv = StreamTableEnvironment.create(execEnv, streamSettings);

	// register HBase table testTable1 which contains test data
	String table1DDL = createHBaseTableDDL(TEST_TABLE_1, false);
	tEnv.executeSql(table1DDL);

	String table2DDL = createHBaseTableDDL(TEST_TABLE_2, false);
	tEnv.executeSql(table2DDL);

	String query = "INSERT INTO " + TEST_TABLE_2 + " SELECT" +
		" rowkey," +
		" family1," +
		" family2," +
		" family3" +
		" FROM " + TEST_TABLE_1;

	// wait to finish
	TableEnvUtil.execInsertSqlAndWaitResult(tEnv, query);

	// start a batch scan job to verify contents in HBase table
	TableEnvironment batchEnv = createBatchTableEnv();
	batchEnv.executeSql(table2DDL);

	Table table = batchEnv.sqlQuery(
		"SELECT " +
			"  h.rowkey, " +
			"  h.family1.col1, " +
			"  h.family2.col1, " +
			"  h.family2.col2, " +
			"  h.family3.col1, " +
			"  h.family3.col2, " +
			"  h.family3.col3 " +
			"FROM " + TEST_TABLE_2 + " AS h"
	);
	List<Row> results = collectBatchResult(table);
	String expected =
			"1,10,Hello-1,100,1.01,false,Welt-1\n" +
			"2,20,Hello-2,200,2.02,true,Welt-2\n" +
			"3,30,Hello-3,300,3.03,false,Welt-3\n" +
			"4,40,null,400,4.04,true,Welt-4\n" +
			"5,50,Hello-5,500,5.05,false,Welt-5\n" +
			"6,60,Hello-6,600,6.06,true,Welt-6\n" +
			"7,70,Hello-7,700,7.07,false,Welt-7\n" +
			"8,80,null,800,8.08,true,Welt-8\n";

	TestBaseUtils.compareResultAsText(results, expected);
}
 

@Test
public void testCsvTableViaSQL() throws Exception {
	EnvironmentSettings settings = EnvironmentSettings.newInstance().useBlinkPlanner().inBatchMode().build();
	TableEnvironment tableEnv = TableEnvironment.create(settings);

	tableEnv.registerCatalog("myhive", hiveCatalog);
	tableEnv.useCatalog("myhive");

	String path = this.getClass().getResource("/csv/test.csv").getPath();

	tableEnv.executeSql("create table test2 (name String, age Int) with (\n" +
		"   'connector.type' = 'filesystem',\n" +
		"   'connector.path' = 'file://" + path + "',\n" +
		"   'format.type' = 'csv'\n" +
		")");

	Table t = tableEnv.sqlQuery("SELECT * FROM myhive.`default`.test2");

	List<Row> result = Lists.newArrayList(t.execute().collect());

	// assert query result
	assertEquals(
		new HashSet<>(Arrays.asList(
			Row.of("1", 1),
			Row.of("2", 2),
			Row.of("3", 3))),
		new HashSet<>(result)
	);

	tableEnv.executeSql("ALTER TABLE test2 RENAME TO newtable");

	t = tableEnv.sqlQuery("SELECT * FROM myhive.`default`.newtable");

	result = Lists.newArrayList(t.execute().collect());

	// assert query result
	assertEquals(
		new HashSet<>(Arrays.asList(
			Row.of("1", 1),
			Row.of("2", 2),
			Row.of("3", 3))),
		new HashSet<>(result)
	);

	tableEnv.executeSql("DROP TABLE newtable");
}
 

@Test
public void testCsvTableViaAPI() throws Exception {
	EnvironmentSettings settings = EnvironmentSettings.newInstance().useBlinkPlanner().inBatchMode().build();
	TableEnvironment tableEnv = TableEnvironment.create(settings);
	tableEnv.getConfig().addConfiguration(new Configuration().set(CoreOptions.DEFAULT_PARALLELISM, 1));

	tableEnv.registerCatalog("myhive", hiveCatalog);
	tableEnv.useCatalog("myhive");

	TableSchema schema = TableSchema.builder()
		.field("name", DataTypes.STRING())
		.field("age", DataTypes.INT())
		.build();

	FormatDescriptor format = new OldCsv()
		.field("name", Types.STRING())
		.field("age", Types.INT());

	CatalogTable source =
		new CatalogTableBuilder(
			new FileSystem().path(this.getClass().getResource("/csv/test.csv").getPath()),
			schema)
		.withFormat(format)
		.inAppendMode()
		.withComment("Comment.")
		.build();

	Path p = Paths.get(tempFolder.newFolder().getAbsolutePath(), "test.csv");

	CatalogTable sink =
		new CatalogTableBuilder(
			new FileSystem().path(p.toAbsolutePath().toString()),
			schema)
			.withFormat(format)
			.inAppendMode()
			.withComment("Comment.")
			.build();

	hiveCatalog.createTable(
		new ObjectPath(HiveCatalog.DEFAULT_DB, sourceTableName),
		source,
		false
	);

	hiveCatalog.createTable(
		new ObjectPath(HiveCatalog.DEFAULT_DB, sinkTableName),
		sink,
		false
	);

	Table t = tableEnv.sqlQuery(
		String.format("select * from myhive.`default`.%s", sourceTableName));

	List<Row> result = Lists.newArrayList(t.execute().collect());
	result.sort(Comparator.comparing(String::valueOf));

	// assert query result
	assertEquals(
		Arrays.asList(
			Row.of("1", 1),
			Row.of("2", 2),
			Row.of("3", 3)),
		result
	);

	TableEnvUtil.execInsertSqlAndWaitResult(tableEnv,
		String.format("insert into myhive.`default`.%s select * from myhive.`default`.%s",
			sinkTableName,
			sourceTableName));

	// assert written result
	File resultFile = new File(p.toAbsolutePath().toString());
	BufferedReader reader = new BufferedReader(new FileReader(resultFile));
	String readLine;
	for (int i = 0; i < 3; i++) {
		readLine = reader.readLine();
		assertEquals(String.format("%d,%d", i + 1, i + 1), readLine);
	}

	// No more line
	assertNull(reader.readLine());

	tableEnv.executeSql(String.format("DROP TABLE %s", sourceTableName));
	tableEnv.executeSql(String.format("DROP TABLE %s", sinkTableName));
}