org.apache.flink.table.api.TableEnvironment Java Examples

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

	Table table = tableEnvironment.from("testCatalog.`default`.testTable")
		.window(Tumble.over(lit(10).minute()).on($("rowtime")).as("w"))
		.groupBy($("w"))
		.select(lit(1).count());
	testUtil.verifyPlan(table);
}
 

@Override
public List<String> completeStatement(String sessionId, String statement, int position) {
	final ExecutionContext<?> context = getExecutionContext(sessionId);
	final TableEnvironment tableEnv = context.getTableEnvironment();

	try {
		return context.wrapClassLoader(() ->
				Arrays.asList(tableEnv.getCompletionHints(statement, position)));
	} catch (Throwable t) {
		// catch everything such that the query does not crash the executor
		if (LOG.isDebugEnabled()) {
			LOG.debug("Could not complete statement at " + position + ":" + statement, t);
		}
		return Collections.emptyList();
	}
}
 

@Test
public void testTimestampUDF() throws Exception {

	TableEnvironment tableEnv = HiveTestUtils.createTableEnvWithBlinkPlannerBatchMode(SqlDialect.HIVE);
	tableEnv.registerCatalog(hiveCatalog.getName(), hiveCatalog);
	tableEnv.useCatalog(hiveCatalog.getName());
	tableEnv.executeSql(String.format("create function myyear as '%s'", UDFYear.class.getName()));
	tableEnv.executeSql("create table src(ts timestamp)");
	try {
		HiveTestUtils.createTextTableInserter(hiveShell, "default", "src")
				.addRow(new Object[]{Timestamp.valueOf("2013-07-15 10:00:00")})
				.addRow(new Object[]{Timestamp.valueOf("2019-05-23 17:32:55")})
				.commit();

		List<Row> results = Lists.newArrayList(
				tableEnv.sqlQuery("select myyear(ts) as y from src").execute().collect());
		Assert.assertEquals(2, results.size());
		Assert.assertEquals("[2013, 2019]", results.toString());
	} finally {
		tableEnv.executeSql("drop table src");
	}
}
 

public TableEnvironment createJavaFlinkBatchTableEnvironment() {
  try {
    Class<?> clazz = null;
    if (flinkVersion.isFlink110()) {
      clazz = Class
              .forName("org.apache.flink.table.api.java.internal.BatchTableEnvironmentImpl");
    } else {
      clazz = Class
              .forName("org.apache.flink.table.api.bridge.java.internal.BatchTableEnvironmentImpl");
    }

    Constructor con = clazz.getConstructor(
            ExecutionEnvironment.class,
            TableConfig.class,
            CatalogManager.class,
            ModuleManager.class);
    return (TableEnvironment) con.newInstance(
            benv.getJavaEnv(),
            tblConfig,
            catalogManager,
            moduleManager);
  } catch (Throwable t) {
    throw new TableException("Create BatchTableEnvironment failed.", t);
  }
}
 

@Test
public void testDefaultPartitionName() throws Exception {
	hiveShell.execute("create database db1");
	hiveShell.execute("create table db1.src (x int, y int)");
	hiveShell.execute("create table db1.part (x int) partitioned by (y int)");
	hiveShell.insertInto("db1", "src").addRow(1, 1).addRow(2, null).commit();

	TableEnvironment tableEnv = getTableEnvWithHiveCatalog();

	// test generating partitions with default name
	tableEnv.sqlUpdate("insert into db1.part select * from db1.src");
	tableEnv.execute("mytest");
	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));

	// TODO: test reading from flink when https://issues.apache.org/jira/browse/FLINK-13279 is fixed
	assertEquals(Arrays.asList("1\t1", "2\tNULL"), hiveShell.executeQuery("select * from db1.part"));

	hiveShell.execute("drop database db1 cascade");
}
 

public static void main(String[] args) throws Exception {
	ParameterTool params = ParameterTool.fromArgs(args);
	String outputPath = params.getRequired("outputPath");
	String sqlStatement = params.getRequired("sqlStatement");

	TableEnvironment tEnv = TableEnvironment.create(EnvironmentSettings.newInstance()
		.useBlinkPlanner()
		.inBatchMode()
		.build());

	tEnv.registerTableSource("table1", new GeneratorTableSource(10, 100, 60, 0));
	tEnv.registerTableSource("table2", new GeneratorTableSource(5, 0.2f, 60, 5));
	tEnv.registerTableSink("sinkTable",
		new CsvTableSink(outputPath)
			.configure(new String[]{"f0", "f1"}, new TypeInformation[]{Types.INT, Types.SQL_TIMESTAMP}));

	tEnv.sqlUpdate(sqlStatement);
	tEnv.execute("TestSqlJob");
}
 

@Test
public void testReadComplexDataType() throws Exception {
	final String catalogName = "hive";
	final String dbName = "source_db";
	final String tblName = "complex_test";
	hiveShell.execute("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};
	hiveShell.insertInto(dbName, tblName)
			.withAllColumns()
			.addRow(array, map, struct)
			.commit();
	TableEnvironment tEnv = HiveTestUtils.createTableEnv();
	tEnv.registerCatalog(catalogName, hiveCatalog);
	Table src = tEnv.sqlQuery("select * from hive.source_db.complex_test");
	List<Row> rows = JavaConverters.seqAsJavaListConverter(TableUtil.collect((TableImpl) src)).asJava();
	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 testWhitespacePartValue() throws Exception {
	TableEnvironment tableEnv = getTableEnvWithHiveCatalog();
	tableEnv.executeSql("create database db1");
	try {
		tableEnv.executeSql("create table db1.dest (x int) partitioned by (p string)");
		StatementSet stmtSet = tableEnv.createStatementSet();
		stmtSet.addInsertSql("insert into db1.dest select 1,'  '");
		stmtSet.addInsertSql("insert into db1.dest select 2,'a \t'");
		TableResult tableResult = stmtSet.execute();
		// wait job finished
		tableResult.getJobClient().get().getJobExecutionResult(Thread.currentThread().getContextClassLoader()).get();
		assertEquals("[p=  , p=a %09]", hiveShell.executeQuery("show partitions db1.dest").toString());
	} finally {
		tableEnv.executeSql("drop database db1 cascade");
	}
}
 

public static void main(String[] args) throws Exception {
	ParameterTool params = ParameterTool.fromArgs(args);
	String outputPath = params.getRequired("outputPath");
	String sqlStatement = params.getRequired("sqlStatement");

	TableEnvironment tEnv = TableEnvironment.create(EnvironmentSettings.newInstance()
		.useBlinkPlanner()
		.inBatchMode()
		.build());

	((TableEnvironmentInternal) tEnv).registerTableSourceInternal("table1", new GeneratorTableSource(10, 100, 60, 0));
	((TableEnvironmentInternal) tEnv).registerTableSourceInternal("table2", new GeneratorTableSource(5, 0.2f, 60, 5));
	((TableEnvironmentInternal) tEnv).registerTableSinkInternal("sinkTable",
		new CsvTableSink(outputPath)
			.configure(new String[]{"f0", "f1"}, new TypeInformation[]{Types.INT, Types.SQL_TIMESTAMP}));

	TableResult result = tEnv.executeSql(sqlStatement);
	// wait job finish
	result.getJobClient().get().getJobExecutionResult(Thread.currentThread().getContextClassLoader()).get();
}
 

@Test
public void testDecimal() throws Exception {
	TableEnvironment tableEnv = getTableEnvWithHiveCatalog();
	tableEnv.executeSql("create database db1");
	try {
		tableEnv.executeSql("create table db1.src1 (x decimal(10,2))");
		tableEnv.executeSql("create table db1.src2 (x decimal(10,2))");
		tableEnv.executeSql("create table db1.dest (x decimal(10,2))");
		// populate src1 from Hive
		// TABLE keyword in INSERT INTO is mandatory prior to 1.1.0
		hiveShell.execute("insert into table db1.src1 values (1.0),(2.12),(5.123),(5.456),(123456789.12)");

		// populate src2 with same data from Flink
		TableEnvUtil.execInsertSqlAndWaitResult(tableEnv, "insert into db1.src2 values (cast(1.0 as decimal(10,2))), (cast(2.12 as decimal(10,2))), " +
				"(cast(5.123 as decimal(10,2))), (cast(5.456 as decimal(10,2))), (cast(123456789.12 as decimal(10,2)))");
		// verify src1 and src2 contain same data
		verifyHiveQueryResult("select * from db1.src2", hiveShell.executeQuery("select * from db1.src1"));

		// populate dest with src1 from Flink -- to test reading decimal type from Hive
		TableEnvUtil.execInsertSqlAndWaitResult(tableEnv, "insert into db1.dest select * from db1.src1");
		verifyHiveQueryResult("select * from db1.dest", hiveShell.executeQuery("select * from db1.src1"));
	} finally {
		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);
}
 

@Override
public String explainStatement(SessionContext session, String statement) throws SqlExecutionException {
	final ExecutionContext<?> context = getOrCreateExecutionContext(session);
	final TableEnvironment tableEnv = context
		.createEnvironmentInstance()
		.getTableEnvironment();

	// translate
	try {
		final Table table = createTable(context, tableEnv, statement);
		return context.wrapClassLoader(() -> tableEnv.explain(table));
	} catch (Throwable t) {
		// catch everything such that the query does not crash the executor
		throw new SqlExecutionException("Invalid SQL statement.", t);
	}
}
 

@Test
public void testNotNullConstraints() throws Exception {
	Assume.assumeTrue(HiveVersionTestUtil.HIVE_310_OR_LATER);
	TableEnvironment tableEnv = getTableEnvWithHiveCatalog();
	tableEnv.executeSql("create database db1");
	try {
		tableEnv.executeSql("create table db1.tbl (x int,y bigint not null enable rely,z string not null enable norely)");
		CatalogBaseTable catalogTable = hiveCatalog.getTable(new ObjectPath("db1", "tbl"));
		TableSchema tableSchema = catalogTable.getSchema();
		assertTrue("By default columns should be nullable",
				tableSchema.getFieldDataTypes()[0].getLogicalType().isNullable());
		assertFalse("NOT NULL columns should be reflected in table schema",
				tableSchema.getFieldDataTypes()[1].getLogicalType().isNullable());
		assertTrue("NOT NULL NORELY columns should be considered nullable",
				tableSchema.getFieldDataTypes()[2].getLogicalType().isNullable());
	} finally {
		tableEnv.executeSql("drop database db1 cascade");
	}
}
 

@Override
public void useCatalog(SessionContext session, String catalogName) throws SqlExecutionException {
	final ExecutionContext<?> context = getOrCreateExecutionContext(session);
	final TableEnvironment tableEnv = context
		.createEnvironmentInstance()
		.getTableEnvironment();

	context.wrapClassLoader(() -> {
		// Rely on TableEnvironment/CatalogManager to validate input
		try {
			tableEnv.useCatalog(catalogName);
		} catch (CatalogException e) {
			throw new SqlExecutionException("Failed to switch to catalog " + catalogName, e);
		}
		session.setCurrentCatalog(catalogName);
		session.setCurrentDatabase(tableEnv.getCurrentDatabase());
		return null;
	});
}
 

@Test
public void testBatchAppend() {
	TableEnvironment tEnv = HiveTestUtils.createTableEnvWithBlinkPlannerBatchMode(SqlDialect.HIVE);
	tEnv.registerCatalog(hiveCatalog.getName(), hiveCatalog);
	tEnv.useCatalog(hiveCatalog.getName());
	tEnv.executeSql("create database db1");
	tEnv.useDatabase("db1");
	try {
		tEnv.executeSql("create table append_table (i int, j int)");
		TableEnvUtil.execInsertSqlAndWaitResult(tEnv, "insert into append_table select 1, 1");
		TableEnvUtil.execInsertSqlAndWaitResult(tEnv, "insert into append_table select 2, 2");
		ArrayList<Row> rows = Lists.newArrayList(tEnv.executeSql("select * from append_table").collect());
		rows.sort(Comparator.comparingInt(o -> (int) o.getField(0)));
		Assert.assertEquals(Arrays.asList(Row.of(1, 1), Row.of(2, 2)), rows);
	} finally {
		tEnv.executeSql("drop database db1 cascade");
	}
}
 

public TableEnvironment createScalaFlinkBatchTableEnvironment() {
  try {
    Class clazz = null;
    if (flinkVersion.isFlink110()) {
      clazz = Class
              .forName("org.apache.flink.table.api.scala.internal.BatchTableEnvironmentImpl");
    } else {
      clazz = Class
              .forName("org.apache.flink.table.api.bridge.scala.internal.BatchTableEnvironmentImpl");
    }
    Constructor constructor = clazz
            .getConstructor(
                    org.apache.flink.api.scala.ExecutionEnvironment.class,
                    TableConfig.class,
                    CatalogManager.class,
                    ModuleManager.class);

    return (TableEnvironment)
            constructor.newInstance(benv, tblConfig, catalogManager, moduleManager);
  } catch (Exception e) {
    throw new TableException("Fail to createScalaFlinkBatchTableEnvironment", e);
  }
}
 

@Test
public void testConfiguration() throws Exception {
	final ExecutionContext<?> context = createConfigurationExecutionContext();
	final TableEnvironment tableEnv = context.getTableEnvironment();

	Configuration conf = tableEnv.getConfig().getConfiguration();
	assertEquals(100, conf.getInteger(ExecutionConfigOptions.TABLE_EXEC_SORT_DEFAULT_LIMIT));
	assertTrue(conf.getBoolean(ExecutionConfigOptions.TABLE_EXEC_SPILL_COMPRESSION_ENABLED));
	assertEquals("128kb", conf.getString(ExecutionConfigOptions.TABLE_EXEC_SPILL_COMPRESSION_BLOCK_SIZE));

	assertTrue(conf.getBoolean(OptimizerConfigOptions.TABLE_OPTIMIZER_JOIN_REORDER_ENABLED));

	// these options are not modified and should be equal to their default value
	assertEquals(
		ExecutionConfigOptions.TABLE_EXEC_SORT_ASYNC_MERGE_ENABLED.defaultValue(),
		conf.getBoolean(ExecutionConfigOptions.TABLE_EXEC_SORT_ASYNC_MERGE_ENABLED));
	assertEquals(
		ExecutionConfigOptions.TABLE_EXEC_SHUFFLE_MODE.defaultValue(),
		conf.getString(ExecutionConfigOptions.TABLE_EXEC_SHUFFLE_MODE));
	assertEquals(
		OptimizerConfigOptions.TABLE_OPTIMIZER_BROADCAST_JOIN_THRESHOLD.defaultValue().longValue(),
		conf.getLong(OptimizerConfigOptions.TABLE_OPTIMIZER_BROADCAST_JOIN_THRESHOLD));
}
 

private void testCaseInsensitive(String format) throws Exception {
	TableEnvironment tEnv = createTableEnvWithHiveCatalog(hiveCatalog);
	String folderURI = TEMPORARY_FOLDER.newFolder().toURI().toString();

	// Flink to write sensitive fields to parquet file
	tEnv.executeSql(String.format(
			"create table parquet_t (I int, J int) with (" +
					"'connector'='filesystem','format'='%s','path'='%s')",
			format,
			folderURI));
	waitForJobFinish(tEnv.executeSql("insert into parquet_t select 1, 2"));
	tEnv.executeSql("drop table parquet_t");

	// Hive to read parquet file
	tEnv.getConfig().setSqlDialect(SqlDialect.HIVE);
	tEnv.executeSql(String.format(
			"create external table parquet_t (i int, j int) stored as %s location '%s'",
			format,
			folderURI));
	Assert.assertEquals(
			Row.of(1, 2),
			tEnv.executeSql("select * from parquet_t").collect().next());
}
 

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

/**
 * Creates a table using the given query in the given table environment.
 */
private Table createTable(TableEnvironment tableEnv, String selectQuery) {
	// parse and validate query
	try {
		return tableEnv.sqlQuery(selectQuery);
	} catch (Throwable t) {
		// catch everything such that the query does not crash the executor
		throw new SqlExecutionException("Invalid SQL statement.", t);
	}
}
 

@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 testTables() throws Exception {
	final ExecutionContext<?> context = createDefaultExecutionContext();
	final TableEnvironment tableEnv = context.getTableEnvironment();

	assertArrayEquals(
		new String[]{"TableNumber1", "TableNumber2", "TableSourceSink", "TestView1", "TestView2"},
		tableEnv.listTables());
}
 

private static void testSqlError(
		TableEnvironment env,
		Table inputTable,
		SqlErrorTestItem testItem) {
	try {
		env.sqlQuery("SELECT " + testItem.expression + " FROM " + inputTable).execute();
		fail("Error expected: " + testItem.errorMessage);
	} catch (Throwable t) {
		assertTrue(t instanceof ValidationException);
		assertThat(t.getMessage(), containsString(testItem.errorMessage));
	}
}
 

@Test
public void testCreateDropTemporaryCatalogFunctionsWithDifferentIdentifier() {
	TableEnvironment tableEnv = getTableEnvironment();
	String createNoCatalogDB = "create temporary function f4" +
		" as '" + TEST_FUNCTION + "'";

	String dropNoCatalogDB = "drop temporary function f4";

	tableEnv.sqlUpdate(createNoCatalogDB);
	tableEnv.sqlUpdate(dropNoCatalogDB);

	String createNonExistsCatalog = "create temporary function catalog1.default_database.f4" +
		" as '" + TEST_FUNCTION + "'";

	String dropNonExistsCatalog = "drop temporary function catalog1.default_database.f4";

	tableEnv.sqlUpdate(createNonExistsCatalog);
	tableEnv.sqlUpdate(dropNonExistsCatalog);

	String createNonExistsDB = "create temporary function default_catalog.db1.f4" +
		" as '" + TEST_FUNCTION + "'";

	String dropNonExistsDB = "drop temporary function default_catalog.db1.f4";

	tableEnv.sqlUpdate(createNonExistsDB);
	tableEnv.sqlUpdate(dropNonExistsDB);
}
 

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 testPartitionPrunning() throws Exception {
	final String dbName = "source_db";
	final String tblName = "test_table_pt_1";
	TableEnvironment tEnv = createTableEnv();
	tEnv.executeSql("CREATE TABLE source_db.test_table_pt_1 " +
					"(`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_1 where pt = 0");
	// first check execution plan to ensure partition prunning works
	String[] explain = src.explain().split("==.*==\n");
	assertEquals(4, explain.length);
	String optimizedLogicalPlan = explain[2];
	String physicalExecutionPlan = explain[3];
	assertTrue(optimizedLogicalPlan, optimizedLogicalPlan.contains(
			"HiveTableSource(year, value, pt) TablePath: source_db.test_table_pt_1, PartitionPruned: true, PartitionNums: 1"));
	assertTrue(physicalExecutionPlan, physicalExecutionPlan.contains(
			"HiveTableSource(year, value, pt) TablePath: source_db.test_table_pt_1, PartitionPruned: true, PartitionNums: 1"));
	// second check execute results
	List<Row> rows = Lists.newArrayList(src.execute().collect());
	assertEquals(2, rows.size());
	Object[] rowStrings = rows.stream().map(Row::toString).sorted().toArray();
	assertArrayEquals(new String[]{"2014,3,0", "2014,4,0"}, rowStrings);
}
 

@Override
public TableSchema getTableSchema(SessionContext session, String name) throws SqlExecutionException {
	final TableEnvironment tableEnv = getOrCreateExecutionContext(session)
		.createEnvironmentInstance()
		.getTableEnvironment();
	try {
		return tableEnv.scan(name).getSchema();
	} catch (Throwable t) {
		// catch everything such that the query does not crash the executor
		throw new SqlExecutionException("No table with this name could be found.", t);
	}
}
 

@Test
public void testViews() throws Exception {
	TableEnvironment tableEnv = getTableEnvWithHiveCatalog();
	tableEnv.executeSql("create database db1");
	try {
		tableEnv.executeSql("create table db1.src (key int,val string)");
		HiveTestUtils.createTextTableInserter(hiveShell, "db1", "src")
				.addRow(new Object[]{1, "a"})
				.addRow(new Object[]{1, "aa"})
				.addRow(new Object[]{1, "aaa"})
				.addRow(new Object[]{2, "b"})
				.addRow(new Object[]{3, "c"})
				.addRow(new Object[]{3, "ccc"})
				.commit();
		tableEnv.executeSql("create table db1.keys (key int,name string)");
		HiveTestUtils.createTextTableInserter(hiveShell, "db1", "keys")
				.addRow(new Object[]{1, "key1"})
				.addRow(new Object[]{2, "key2"})
				.addRow(new Object[]{3, "key3"})
				.addRow(new Object[]{4, "key4"})
				.commit();
		hiveShell.execute("create view db1.v1 as select key as k,val as v from db1.src limit 2");
		hiveShell.execute("create view db1.v2 as select key,count(*) from db1.src group by key having count(*)>1 order by key");
		hiveShell.execute("create view db1.v3 as select k.key,k.name,count(*) from db1.src s join db1.keys k on s.key=k.key group by k.key,k.name order by k.key");
		List<Row> results = Lists.newArrayList(tableEnv.sqlQuery("select count(v) from db1.v1").execute().collect());
		assertEquals("[2]", results.toString());
		results = Lists.newArrayList(tableEnv.sqlQuery("select * from db1.v2").execute().collect());
		assertEquals("[1,3, 3,2]", results.toString());
		results = Lists.newArrayList(tableEnv.sqlQuery("select * from db1.v3").execute().collect());
		assertEquals("[1,key1,3, 2,key2,1, 3,key3,2]", results.toString());
	} finally {
		tableEnv.executeSql("drop database db1 cascade");
	}
}
 

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