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

@Test
public void testParquetNameMapping() throws Exception {
	TableEnvironment tableEnv = getTableEnvWithHiveCatalog();
	tableEnv.executeSql("create database db1");
	try {
		tableEnv.executeSql("create table db1.t1 (x int,y int) stored as parquet");
		TableEnvUtil.execInsertSqlAndWaitResult(tableEnv, "insert into table db1.t1 values (1,10),(2,20)");
		Table hiveTable = hiveCatalog.getHiveTable(new ObjectPath("db1", "t1"));
		String location = hiveTable.getSd().getLocation();
		tableEnv.executeSql(String.format("create table db1.t2 (y int,x int) stored as parquet location '%s'", location));
		tableEnv.getConfig().getConfiguration().setBoolean(HiveOptions.TABLE_EXEC_HIVE_FALLBACK_MAPRED_READER, true);
		assertEquals("[1, 2]", Lists.newArrayList(tableEnv.sqlQuery("select x from db1.t1").execute().collect()).toString());
		assertEquals("[1, 2]", Lists.newArrayList(tableEnv.sqlQuery("select x from db1.t2").execute().collect()).toString());
	} finally {
		tableEnv.executeSql("drop database db1 cascade");
	}
}
 

@Test
public void testDateTimestampPartitionColumns() throws Exception {
	TableEnvironment tableEnv = getTableEnvWithHiveCatalog();
	tableEnv.executeSql("create database db1");
	try {
		tableEnv.executeSql("create table db1.part(x int) partitioned by (dt date,ts timestamp)");
		HiveTestUtils.createTextTableInserter(hiveShell, "db1", "part")
				.addRow(new Object[]{1})
				.addRow(new Object[]{2})
				.commit("dt='2019-12-23',ts='2019-12-23 00:00:00'");
		HiveTestUtils.createTextTableInserter(hiveShell, "db1", "part")
				.addRow(new Object[]{3})
				.commit("dt='2019-12-25',ts='2019-12-25 16:23:43.012'");
		List<Row> results = Lists.newArrayList(tableEnv.sqlQuery("select * from db1.part order by x").execute().collect());
		assertEquals("[1,2019-12-23,2019-12-23T00:00, 2,2019-12-23,2019-12-23T00:00, 3,2019-12-25,2019-12-25T16:23:43.012]", results.toString());

		results = Lists.newArrayList(tableEnv.sqlQuery("select x from db1.part where dt=cast('2019-12-25' as date)").execute().collect());
		assertEquals("[3]", results.toString());

		TableEnvUtil.execInsertSqlAndWaitResult(tableEnv, "insert into db1.part select 4,cast('2019-12-31' as date),cast('2019-12-31 12:00:00.0' as timestamp)");
		results = Lists.newArrayList(tableEnv.sqlQuery("select max(dt) from db1.part").execute().collect());
		assertEquals("[2019-12-31]", results.toString());
	} finally {
		tableEnv.executeSql("drop database db1 cascade");
	}
}
 

@Test
public void testRegexSerDe() throws Exception {
	TableEnvironment tableEnv = getTableEnvWithHiveCatalog();
	tableEnv.executeSql("create database db1");
	try {
		tableEnv.executeSql("create table db1.src (x int,y string) " +
				"row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe' " +
				"with serdeproperties ('input.regex'='([\\\\d]+)\\u0001([\\\\S]+)')");
		HiveTestUtils.createTextTableInserter(hiveShell, "db1", "src")
				.addRow(new Object[]{1, "a"})
				.addRow(new Object[]{2, "ab"})
				.commit();
		assertEquals("[1,a, 2,ab]", Lists.newArrayList(tableEnv.sqlQuery("select * from db1.src order by x").execute().collect()).toString());
	} finally {
		tableEnv.executeSql("drop database db1 cascade");
	}
}
 

@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());
}
 

private void testCompressTextTable(boolean batch) throws Exception {
	TableEnvironment tableEnv = batch ?
			getTableEnvWithHiveCatalog() :
			getStreamTableEnvWithHiveCatalog();
	tableEnv.executeSql("create database db1");
	try {
		tableEnv.executeSql("create table db1.src (x string,y string)");
		hiveShell.execute("create table db1.dest like db1.src");
		HiveTestUtils.createTextTableInserter(hiveShell, "db1", "src")
				.addRow(new Object[]{"a", "b"})
				.addRow(new Object[]{"c", "d"})
				.commit();
		hiveCatalog.getHiveConf().setBoolVar(HiveConf.ConfVars.COMPRESSRESULT, true);
		TableEnvUtil.execInsertSqlAndWaitResult(tableEnv, "insert into db1.dest select * from db1.src");
		List<String> expected = Arrays.asList("a\tb", "c\td");
		verifyHiveQueryResult("select * from db1.dest", expected);
		verifyFlinkQueryResult(tableEnv.sqlQuery("select * from db1.dest"), expected);
	} finally {
		tableEnv.executeSql("drop database db1 cascade");
	}
}
 

@Test
public void testNonExistingPartitionFolder() throws Exception {
	TableEnvironment tableEnv = getTableEnvWithHiveCatalog();
	tableEnv.executeSql("create database db1");
	try {
		tableEnv.executeSql("create table db1.part (x int) partitioned by (p int)");
		HiveTestUtils.createTextTableInserter(hiveShell, "db1", "part").addRow(new Object[]{1}).commit("p=1");
		HiveTestUtils.createTextTableInserter(hiveShell, "db1", "part").addRow(new Object[]{2}).commit("p=2");
		tableEnv.executeSql("alter table db1.part add partition (p=3)");
		// remove one partition
		Path toRemove = new Path(hiveCatalog.getHiveTable(new ObjectPath("db1", "part")).getSd().getLocation(), "p=2");
		FileSystem fs = toRemove.getFileSystem(hiveShell.getHiveConf());
		fs.delete(toRemove, true);

		List<Row> results = Lists.newArrayList(tableEnv.sqlQuery("select * from db1.part").execute().collect());
		assertEquals("[1,1]", results.toString());
	} finally {
		tableEnv.executeSql("drop database db1 cascade");
	}
}
 

@Test
public void testDynamicPartition() throws Exception {
	TableEnvironment tableEnv = getTableEnvWithHiveCatalog();
	tableEnv.executeSql("create database db1");
	try {
		tableEnv.executeSql("create table db1.src (x int, y string, z double)");
		HiveTestUtils.createTextTableInserter(hiveShell, "db1", "src")
				.addRow(new Object[]{1, "a", 1.1})
				.addRow(new Object[]{2, "a", 2.2})
				.addRow(new Object[]{3, "b", 3.3})
				.commit();
		tableEnv.executeSql("create table db1.dest (x int) partitioned by (p1 string, p2 double)");
		TableEnvUtil.execInsertSqlAndWaitResult(tableEnv, "insert into db1.dest select * from db1.src");
		assertEquals(3, hiveCatalog.listPartitions(new ObjectPath("db1", "dest")).size());
		verifyHiveQueryResult("select * from db1.dest", Arrays.asList("1\ta\t1.1", "2\ta\t2.2", "3\tb\t3.3"));
	} finally {
		tableEnv.executeSql("drop database db1 cascade");
	}
}
 

@Test
public void testInsertPartitionWithStarSource() throws Exception {
	TableEnvironment tableEnv = getTableEnvWithHiveCatalog();
	tableEnv.executeSql("create table src (x int,y string)");
	HiveTestUtils.createTextTableInserter(
			hiveShell,
			"default",
			"src")
			.addRow(new Object[]{1, "a"})
			.commit();
	tableEnv.executeSql("create table dest (x int) partitioned by (p1 int,p2 string)");
	TableEnvUtil.execInsertSqlAndWaitResult(tableEnv,
			"insert into dest partition (p1=1) select * from src");
	List<Row> results = Lists.newArrayList(tableEnv.sqlQuery("select * from dest").execute().collect());
	assertEquals("[1,1,a]", results.toString());
	tableEnv.executeSql("drop table if exists src");
	tableEnv.executeSql("drop table if exists dest");
}
 

@Test
public void testResolvingSchemaOfCustomCatalogTableSql() throws Exception {
	TableTestUtil testUtil = getTestUtil();
	TableEnvironment tableEnvironment = testUtil.getTableEnv();
	GenericInMemoryCatalog genericInMemoryCatalog = new GenericInMemoryCatalog("in-memory");
	genericInMemoryCatalog.createTable(
		new ObjectPath("default", "testTable"),
		new CustomCatalogTable(isStreamingMode),
		false);
	tableEnvironment.registerCatalog("testCatalog", genericInMemoryCatalog);
	tableEnvironment.executeSql("CREATE VIEW testTable2 AS SELECT * FROM testCatalog.`default`.testTable");

	testUtil.verifyPlan(
		"SELECT COUNT(*) FROM testTable2 GROUP BY TUMBLE(rowtime, INTERVAL '10' MINUTE)");
}
 

@Test
public void testWriteNullValues() throws Exception {
	TableEnvironment tableEnv = HiveTestUtils.createTableEnvWithBlinkPlannerBatchMode(SqlDialect.HIVE);
	tableEnv.registerCatalog(hiveCatalog.getName(), hiveCatalog);
	tableEnv.useCatalog(hiveCatalog.getName());
	tableEnv.executeSql("create database db1");
	try {
		// 17 data types
		tableEnv.executeSql("create table db1.src" +
				"(t tinyint,s smallint,i int,b bigint,f float,d double,de decimal(10,5),ts timestamp,dt date," +
				"str string,ch char(5),vch varchar(8),bl boolean,bin binary,arr array<int>,mp map<int,string>,strt struct<f1:int,f2:string>)");
		HiveTestUtils.createTextTableInserter(hiveShell, "db1", "src")
				.addRow(new Object[]{null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null})
				.commit();
		hiveShell.execute("create table db1.dest like db1.src");

		TableEnvUtil.execInsertSqlAndWaitResult(tableEnv, "insert into db1.dest select * from db1.src");
		List<String> results = hiveShell.executeQuery("select * from db1.dest");
		assertEquals(1, results.size());
		String[] cols = results.get(0).split("\t");
		assertEquals(17, cols.length);
		assertEquals("NULL", cols[0]);
		assertEquals(1, new HashSet<>(Arrays.asList(cols)).size());
	} finally {
		tableEnv.executeSql("drop database db1 cascade");
	}
}
 

@Test
public void testStaticPartition() throws Exception {
	TableEnvironment tableEnv = getTableEnvWithHiveCatalog();
	tableEnv.executeSql("create database db1");
	try {
		tableEnv.executeSql("create table db1.src (x int)");
		HiveTestUtils.createTextTableInserter(hiveShell, "db1", "src").addRow(new Object[]{1}).addRow(new Object[]{2}).commit();
		tableEnv.executeSql("create table db1.dest (x int) partitioned by (p1 string, p2 double)");
		TableEnvUtil.execInsertSqlAndWaitResult(tableEnv, "insert into db1.dest partition (p1='1''1', p2=1.1) select x from db1.src");
		assertEquals(1, hiveCatalog.listPartitions(new ObjectPath("db1", "dest")).size());
		verifyHiveQueryResult("select * from db1.dest", Arrays.asList("1\t1'1\t1.1", "2\t1'1\t1.1"));
	} finally {
		tableEnv.executeSql("drop database db1 cascade");
	}
}
 

public static void main(String[] args) throws Exception {
    EnvironmentSettings settings = EnvironmentSettings.newInstance().build();
    TableEnvironment tEnv = TableEnvironment.create(settings);

    tEnv.executeSql("CREATE TABLE transactions (\n" +
            "    account_id  BIGINT,\n" +
            "    amount      BIGINT,\n" +
            "    transaction_time TIMESTAMP(3),\n" +
            "    WATERMARK FOR transaction_time AS transaction_time - INTERVAL '5' SECOND\n" +
            ") WITH (\n" +
            "    'connector' = 'kafka',\n" +
            "    'topic'     = 'transactions',\n" +
            "    'properties.bootstrap.servers' = 'kafka:9092',\n" +
            "    'format'    = 'csv'\n" +
            ")");

    tEnv.executeSql("CREATE TABLE spend_report (\n" +
            "    account_id BIGINT,\n" +
            "    log_ts     TIMESTAMP(3),\n" +
            "    amount     BIGINT\n," +
            "    PRIMARY KEY (account_id, log_ts) NOT ENFORCED" +
            ") WITH (\n" +
            "  'connector'  = 'jdbc',\n" +
            "  'url'        = 'jdbc:mysql://mysql:3306/sql-demo',\n" +
            "  'table-name' = 'spend_report',\n" +
            "  'driver'     = 'com.mysql.jdbc.Driver',\n" +
            "  'username'   = 'sql-demo',\n" +
            "  'password'   = 'demo-sql'\n" +
            ")");

    Table transactions = tEnv.from("transactions");
    report(transactions).executeInsert("spend_report");
}
 

@Test
public void testPartialDynamicPartition() throws Exception {
	TableEnvironment tableEnv = getTableEnvWithHiveCatalog();
	tableEnv.executeSql("create database db1");
	try {
		tableEnv.executeSql("create table db1.src (x int, y string)");
		HiveTestUtils.createTextTableInserter(hiveShell, "db1", "src").addRow(new Object[]{1, "a"}).addRow(new Object[]{2, "b"}).commit();
		tableEnv.executeSql("create table db1.dest (x int) partitioned by (p1 double, p2 string)");
		TableEnvUtil.execInsertSqlAndWaitResult(tableEnv, "insert into db1.dest partition (p1=1.1) select x,y from db1.src");
		assertEquals(2, hiveCatalog.listPartitions(new ObjectPath("db1", "dest")).size());
		verifyHiveQueryResult("select * from db1.dest", Arrays.asList("1\t1.1\ta", "2\t1.1\tb"));
	} finally {
		tableEnv.executeSql("drop database db1 cascade");
	}
}
 

private TableEnvironment prepareTable(boolean isStreaming) {
	EnvironmentSettings.Builder builder = EnvironmentSettings.newInstance().useBlinkPlanner();
	if (isStreaming) {
		builder = builder.inStreamingMode();
	} else {
		builder = builder.inBatchMode();
	}
	EnvironmentSettings settings = builder.build();
	TableEnvironment tableEnv = TableEnvironment.create(settings);
	tableEnv.getConfig().getConfiguration().setInteger(TABLE_EXEC_RESOURCE_DEFAULT_PARALLELISM, 1);

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

	String srcPath = this.getClass().getResource("/csv/test3.csv").getPath();

	tableEnv.executeSql("CREATE TABLE proctime_src (" +
			"price DECIMAL(10, 2)," +
			"currency STRING," +
			"ts6 TIMESTAMP(6)," +
			"ts AS CAST(ts6 AS TIMESTAMP(3))," +
			"WATERMARK FOR ts AS ts," +
			"l_proctime AS PROCTIME( )) " + // test " " in proctime()
			String.format("WITH (" +
					"'connector.type' = 'filesystem'," +
					"'connector.path' = 'file://%s'," +
					"'format.type' = 'csv')", srcPath));

	return tableEnv;
}
 

@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 testDropCatalog() {
	String name = "c1";
	TableEnvironment tableEnv = getTableEnvironment();

	String ddl = String.format("create catalog %s with('type'='%s')", name, CATALOG_TYPE_VALUE_GENERIC_IN_MEMORY);
	tableEnv.executeSql(ddl);
	assertTrue(tableEnv.getCatalog(name).isPresent());

	ddl = String.format("drop catalog %s", name);
	tableEnv.executeSql(ddl);
	assertFalse(tableEnv.getCatalog(name).isPresent());
}
 

@Test
public void testCreateCatalog() {
	String name = "c1";
	TableEnvironment tableEnv = getTableEnvironment();
	String ddl = String.format("create catalog %s with('type'='%s')", name, CATALOG_TYPE_VALUE_GENERIC_IN_MEMORY);

	tableEnv.executeSql(ddl);

	assertTrue(tableEnv.getCatalog(name).isPresent());
	assertTrue(tableEnv.getCatalog(name).get() instanceof GenericInMemoryCatalog);
}
 

@Test
public void testTableSourceSinkWithDDL() throws Exception {
	if (OLD_PLANNER.equals(planner) || isLegacyConnector) {
		// only test for blink planner and new connector, because types TIMESTAMP/DATE/TIME/DECIMAL works well in
		// new connector(using blink-planner), but exits some precision problem in old planner or legacy connector.
		return;
	}

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

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

	// register HBase table which is empty
	String table3DDL = createHBaseTableDDL(TEST_TABLE_3, true);
	tEnv.executeSql(table3DDL);

	String insertStatement = "INSERT INTO " + TEST_TABLE_3 +
		" SELECT rowkey," +
		" family1," +
		" family2," +
		" family3," +
		" family4" +
		" from " + TEST_TABLE_1;
	// wait to finish
	TableEnvUtil.execInsertSqlAndWaitResult(tEnv, insertStatement);

	// start a batch scan job to verify contents in HBase table
	TableEnvironment batchEnv = createBatchTableEnv();
	batchEnv.executeSql(table3DDL);
	String query = "SELECT " +
			"  h.rowkey, " +
			"  h.family1.col1, " +
			"  h.family2.col1, " +
			"  h.family2.col2, " +
			"  h.family3.col1, " +
			"  h.family3.col2, " +
			"  h.family3.col3, " +
			"  h.family4.col1, " +
			"  h.family4.col2, " +
			"  h.family4.col3, " +
			"  h.family4.col4 " +
			" FROM " + TEST_TABLE_3 + " AS h";
	Iterator<Row> collected = tEnv.executeSql(query).collect();
	List<String> result = Lists.newArrayList(collected).stream()
		.map(Row::toString)
		.sorted()
		.collect(Collectors.toList());

	List<String> expected = new ArrayList<>();
	expected.add("1,10,Hello-1,100,1.01,false,Welt-1,2019-08-18T19:00,2019-08-18,19:00,12345678.0001");
	expected.add("2,20,Hello-2,200,2.02,true,Welt-2,2019-08-18T19:01,2019-08-18,19:01,12345678.0002");
	expected.add("3,30,Hello-3,300,3.03,false,Welt-3,2019-08-18T19:02,2019-08-18,19:02,12345678.0003");
	expected.add("4,40,null,400,4.04,true,Welt-4,2019-08-18T19:03,2019-08-18,19:03,12345678.0004");
	expected.add("5,50,Hello-5,500,5.05,false,Welt-5,2019-08-19T19:10,2019-08-19,19:10,12345678.0005");
	expected.add("6,60,Hello-6,600,6.06,true,Welt-6,2019-08-19T19:20,2019-08-19,19:20,12345678.0006");
	expected.add("7,70,Hello-7,700,7.07,false,Welt-7,2019-08-19T19:30,2019-08-19,19:30,12345678.0007");
	expected.add("8,80,null,800,8.08,true,Welt-8,2019-08-19T19:40,2019-08-19,19:40,12345678.0008");
	assertEquals(expected, result);
}
 

private void readWriteFormat(String format) throws Exception {
	TableEnvironment tableEnv = getTableEnvWithHiveCatalog();

	tableEnv.executeSql("create database db1");

	// create source and dest tables
	String suffix;
	if (format.equals("csv")) {
		suffix = "row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'";
	} else {
		suffix = "stored as " + format;
	}
	String tableSchema;
	// use 2018-08-20 00:00:00.1 to avoid multi-version print difference.
	List<Object> row1 = new ArrayList<>(Arrays.asList(1, "a", "2018-08-20 00:00:00.1"));
	List<Object> row2 = new ArrayList<>(Arrays.asList(2, "b", "2019-08-26 00:00:00.1"));
	// some data types are not supported for parquet tables in early versions -- https://issues.apache.org/jira/browse/HIVE-6384
	if (HiveVersionTestUtil.HIVE_120_OR_LATER || !format.equals("parquet")) {
		tableSchema = "(i int,s string,ts timestamp,dt date)";
		row1.add("2018-08-20");
		row2.add("2019-08-26");
	} else {
		tableSchema = "(i int,s string,ts timestamp)";
	}

	tableEnv.executeSql(String.format(
			"create table db1.src %s partitioned by (p1 string, p2 timestamp) %s", tableSchema, suffix));
	tableEnv.executeSql(String.format(
			"create table db1.dest %s partitioned by (p1 string, p2 timestamp) %s", tableSchema, suffix));

	// prepare source data with Hive
	// TABLE keyword in INSERT INTO is mandatory prior to 1.1.0
	hiveShell.execute(String.format(
			"insert into table db1.src partition(p1='first',p2='2018-08-20 00:00:00.1') values (%s)",
			toRowValue(row1)));
	hiveShell.execute(String.format(
			"insert into table db1.src partition(p1='second',p2='2018-08-26 00:00:00.1') values (%s)",
			toRowValue(row2)));

	List<String> expected = Arrays.asList(
			String.join("\t", ArrayUtils.concat(
					row1.stream().map(Object::toString).toArray(String[]::new),
					new String[]{"first", "2018-08-20 00:00:00.1"})),
			String.join("\t", ArrayUtils.concat(
					row2.stream().map(Object::toString).toArray(String[]::new),
					new String[]{"second", "2018-08-26 00:00:00.1"})));

	verifyFlinkQueryResult(tableEnv.sqlQuery("select * from db1.src"), expected);

	// populate dest table with source table
	TableEnvUtil.execInsertSqlAndWaitResult(tableEnv, "insert into db1.dest select * from db1.src");

	// verify data on hive side
	verifyHiveQueryResult("select * from db1.dest", expected);

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

@Test
public void testPartitionFilter() 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 int,p2 string)");
		HiveTestUtils.createTextTableInserter(hiveShell, "db1", "part")
				.addRow(new Object[]{1}).commit("p1=1,p2='a'");
		HiveTestUtils.createTextTableInserter(hiveShell, "db1", "part")
				.addRow(new Object[]{2}).commit("p1=2,p2='b'");
		HiveTestUtils.createTextTableInserter(hiveShell, "db1", "part")
				.addRow(new Object[]{3}).commit("p1=3,p2='c'");
		// test string partition columns with special characters
		HiveTestUtils.createTextTableInserter(hiveShell, "db1", "part")
				.addRow(new Object[]{4}).commit("p1=4,p2='c:2'");
		Table query = tableEnv.sqlQuery("select x from db1.part where p1>1 or p2<>'a' order by x");
		String[] explain = query.explain().split("==.*==\n");
		assertFalse(catalog.fallback);
		String optimizedPlan = explain[2];
		assertTrue(optimizedPlan, optimizedPlan.contains("PartitionPruned: true, PartitionNums: 3"));
		List<Row> results = Lists.newArrayList(query.execute().collect());
		assertEquals("[2, 3, 4]", results.toString());

		query = tableEnv.sqlQuery("select x from db1.part where p1>2 and p2<='a' order by x");
		explain = query.explain().split("==.*==\n");
		assertFalse(catalog.fallback);
		optimizedPlan = explain[2];
		assertTrue(optimizedPlan, optimizedPlan.contains("PartitionPruned: true, PartitionNums: 0"));
		results = Lists.newArrayList(query.execute().collect());
		assertEquals("[]", results.toString());

		query = tableEnv.sqlQuery("select x from db1.part where p1 in (1,3,5) order by x");
		explain = query.explain().split("==.*==\n");
		assertFalse(catalog.fallback);
		optimizedPlan = explain[2];
		assertTrue(optimizedPlan, optimizedPlan.contains("PartitionPruned: true, PartitionNums: 2"));
		results = Lists.newArrayList(query.execute().collect());
		assertEquals("[1, 3]", results.toString());

		query = tableEnv.sqlQuery("select x from db1.part where (p1=1 and p2='a') or ((p1=2 and p2='b') or p2='d') order by x");
		explain = query.explain().split("==.*==\n");
		assertFalse(catalog.fallback);
		optimizedPlan = explain[2];
		assertTrue(optimizedPlan, optimizedPlan.contains("PartitionPruned: true, PartitionNums: 2"));
		results = Lists.newArrayList(query.execute().collect());
		assertEquals("[1, 2]", results.toString());

		query = tableEnv.sqlQuery("select x from db1.part where p2 = 'c:2' order by x");
		explain = query.explain().split("==.*==\n");
		assertFalse(catalog.fallback);
		optimizedPlan = explain[2];
		assertTrue(optimizedPlan, optimizedPlan.contains("PartitionPruned: true, PartitionNums: 1"));
		results = Lists.newArrayList(query.execute().collect());
		assertEquals("[4]", results.toString());
	} finally {
		tableEnv.executeSql("drop database db1 cascade");
	}
}