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

@Test
public void testSelect() throws Exception {
	StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
	StreamTableEnvironment tableEnv = StreamTableEnvironment.create(env);
	StreamITCase.clear();

	DataStream<Tuple3<Integer, Long, String>> ds = JavaStreamTestData.getSmall3TupleDataSet(env);
	Table in = tableEnv.fromDataStream(ds, "a,b,c");
	tableEnv.registerTable("MyTable", in);

	String sqlQuery = "SELECT * FROM MyTable";
	Table result = tableEnv.sqlQuery(sqlQuery);

	DataStream<Row> resultSet = tableEnv.toAppendStream(result, Row.class);
	resultSet.addSink(new StreamITCase.StringSink<Row>());
	env.execute();

	List<String> expected = new ArrayList<>();
	expected.add("1,1,Hi");
	expected.add("2,2,Hello");
	expected.add("3,2,Hello world");

	StreamITCase.compareWithList(expected);
}
 

/**
 * build chi-square test result, it is for table and vector.
 */
public static Table buildResult(DataSet<Row> in,
                                String[] selectedColNames,
                                Long sessionId) {

    String[] outColNames = new String[]{"col", "chisquare_test"};
    TypeInformation[] outColTypes;
    if (selectedColNames == null) {
        outColTypes = new TypeInformation[]{Types.LONG, Types.STRING};
    } else {
        outColTypes = new TypeInformation[]{Types.STRING, Types.STRING};
    }
    return DataSetConversionUtil.toTable(sessionId,
        in.map(new BuildResult(selectedColNames)),
        outColNames,
        outColTypes);
}
 

/**
 * Save the word-topic model in the sideOutputs.
 */
private void saveWordTopicModelAndPerplexity(DataSet<Row> model, int numTopic,
                                             Boolean ifOnline) {
    DataSet<Row> wordTopicDataSet;
    if (ifOnline) {
        wordTopicDataSet = model.mapPartition(new BuildWordTopicModelOnline()).setParallelism(1);
    } else {
        wordTopicDataSet = model.mapPartition(new BuildWordTopicModelGibbs()).setParallelism(1);
    }
    String[] colNames = new String[numTopic + 1];
    TypeInformation[] colTypes = new TypeInformation[colNames.length];
    colNames[0] = "word";
    colTypes[0] = Types.STRING;
    for (int i = 0; i < numTopic; i++) {
        colNames[1 + i] = "topic_" + i;
        colTypes[1 + i] = Types.DOUBLE;
    }

    DataSet<Row> logPerplexity = model.mapPartition(new CalculatePerplexityAndLikelihood()).setParallelism(1);
    this.setSideOutputTables(new Table[] {
        DataSetConversionUtil.toTable(getMLEnvironmentId(), wordTopicDataSet, colNames, colTypes),
        DataSetConversionUtil.toTable(getMLEnvironmentId(),
            logPerplexity, new String[]{"logPerplexity", "logLikelihood"},
            new TypeInformation[]{Types.DOUBLE, Types.DOUBLE})
    });
}
 

@Test
public void test() throws Exception {
	StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
	StreamTableEnvironment tableEnv = createTableEnv(env);

	DataStream<Tuple2<Long, String>> ds = env.fromElements(
			Tuple2.of(1L, "a"),
			Tuple2.of(2L, "b"),
			Tuple2.of(3L, "c")
	);

	Table table = tableEnv.fromDataStream(ds, "id, name");

	Table result = tableEnv.sqlQuery("SELECT * from " + table);

	tableEnv.toAppendStream(result, ds.getType()).print();
	env.execute("test");
}
 

@Test
public void testAsFromAndToTuple() throws Exception {
	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment tableEnv = BatchTableEnvironment.create(env, config());

	Table table = tableEnv
		.fromDataSet(CollectionDataSets.get3TupleDataSet(env), "a, b, c")
		.select("a, b, c");

	TypeInformation<?> ti = new TupleTypeInfo<Tuple3<Integer, Long, String>>(
		BasicTypeInfo.INT_TYPE_INFO,
		BasicTypeInfo.LONG_TYPE_INFO,
		BasicTypeInfo.STRING_TYPE_INFO);

	DataSet<?> ds = tableEnv.toDataSet(table, ti);
	List<?> results = ds.collect();
	String expected = "(1,1,Hi)\n" + "(2,2,Hello)\n" + "(3,2,Hello world)\n" +
		"(4,3,Hello world, how are you?)\n" + "(5,3,I am fine.)\n" + "(6,3,Luke Skywalker)\n" +
		"(7,4,Comment#1)\n" + "(8,4,Comment#2)\n" + "(9,4,Comment#3)\n" + "(10,4,Comment#4)\n" +
		"(11,5,Comment#5)\n" + "(12,5,Comment#6)\n" + "(13,5,Comment#7)\n" +
		"(14,5,Comment#8)\n" + "(15,5,Comment#9)\n" + "(16,6,Comment#10)\n" +
		"(17,6,Comment#11)\n" + "(18,6,Comment#12)\n" + "(19,6,Comment#13)\n" +
		"(20,6,Comment#14)\n" + "(21,6,Comment#15)\n";
	compareResultAsText(results, expected);
}
 

@Test
public void testAsFromTupleByPosition() throws Exception {
	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment tableEnv = BatchTableEnvironment.create(env, config());

	Table table = tableEnv
		.fromDataSet(CollectionDataSets.get3TupleDataSet(env), $("a"), $("b"), $("c"))
		.select($("a"), $("b"), $("c"));

	DataSet<Row> ds = tableEnv.toDataSet(table, Row.class);
	List<Row> results = ds.collect();
	String expected = "1,1,Hi\n" + "2,2,Hello\n" + "3,2,Hello world\n" +
		"4,3,Hello world, how are you?\n" + "5,3,I am fine.\n" + "6,3,Luke Skywalker\n" +
		"7,4,Comment#1\n" + "8,4,Comment#2\n" + "9,4,Comment#3\n" + "10,4,Comment#4\n" +
		"11,5,Comment#5\n" + "12,5,Comment#6\n" + "13,5,Comment#7\n" +
		"14,5,Comment#8\n" + "15,5,Comment#9\n" + "16,6,Comment#10\n" +
		"17,6,Comment#11\n" + "18,6,Comment#12\n" + "19,6,Comment#13\n" +
		"20,6,Comment#14\n" + "21,6,Comment#15\n";
	compareResultAsText(results, expected);
}
 

@Test
public void testAsFromPojoProjected() throws Exception {
	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment tableEnv = BatchTableEnvironment.create(env, config());

	List<SmallPojo> data = new ArrayList<>();
	data.add(new SmallPojo("Peter", 28, 4000.00, "Sales", new Integer[] {42}));
	data.add(new SmallPojo("Anna", 56, 10000.00, "Engineering", new Integer[] {}));
	data.add(new SmallPojo("Lucy", 42, 6000.00, "HR", new Integer[] {1, 2, 3}));

	Table table = tableEnv
		.fromDataSet(env.fromCollection(data), "name AS d")
		.select("d");

	DataSet<Row> ds = tableEnv.toDataSet(table, Row.class);
	List<Row> results = ds.collect();
	String expected =
		"Peter\n" +
		"Anna\n" +
		"Lucy\n";
	compareResultAsText(results, expected);
}
 

@Test
public void testRetractStreamDoesNotOverwriteTableConfig() {
	StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
	DataStreamSource<Integer> elements = env.fromElements(1, 2, 3);

	StreamTableEnvironmentImpl tEnv = getStreamTableEnvironment(env, elements);

	Time minRetention = Time.minutes(1);
	Time maxRetention = Time.minutes(10);
	tEnv.getConfig().setIdleStateRetentionTime(minRetention, maxRetention);
	Table table = tEnv.fromDataStream(elements);
	tEnv.toRetractStream(table, Row.class);

	assertThat(
		tEnv.getConfig().getMinIdleStateRetentionTime(),
		equalTo(minRetention.toMilliseconds()));
	assertThat(
		tEnv.getConfig().getMaxIdleStateRetentionTime(),
		equalTo(maxRetention.toMilliseconds()));
}
 

@Test
public void testForceTypeWithTableSchema() {
	ExecutionEnvironment env = MLEnvironmentFactory.getDefault().getExecutionEnvironment();

	DataSet<Row> input = env.fromElements(Row.of("s1")).map(new GenericTypeMap());
	Table table2 = DataSetConversionUtil.toTable(MLEnvironmentFactory.DEFAULT_ML_ENVIRONMENT_ID,
		input,
		new TableSchema(
			new String[]{"word"},
			new TypeInformation[]{TypeInformation.of(Integer.class)}
		)
	);
	Assert.assertEquals(
		new TableSchema(new String[] {"word"}, new TypeInformation[] {TypeInformation.of(Integer.class)}),
		table2.getSchema()
	);

}
 

@Test
public void testInsertIntoDynamicPartition() throws Exception {
	String dbName = "default";
	String tblName = "dest";
	RowTypeInfo rowTypeInfo = createDestTable(dbName, tblName, 1);
	ObjectPath tablePath = new ObjectPath(dbName, tblName);

	TableEnvironment tableEnv = HiveTestUtils.createTableEnv();

	List<Row> toWrite = generateRecords(5);
	Table src = tableEnv.fromTableSource(new CollectionTableSource(toWrite, rowTypeInfo));
	tableEnv.registerTable("src", src);

	tableEnv.registerCatalog("hive", hiveCatalog);
	tableEnv.sqlQuery("select * from src").insertInto("hive", "default", "dest");
	tableEnv.execute("mytest");

	List<CatalogPartitionSpec> partitionSpecs = hiveCatalog.listPartitions(tablePath);
	assertEquals(toWrite.size(), partitionSpecs.size());

	verifyWrittenData(toWrite, hiveShell.executeQuery("select * from " + tblName));

	hiveCatalog.dropTable(tablePath, false);
}
 

public static void main(String[] args) throws Exception {
    ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
    BatchTableEnvironment tEnv = BatchTableEnvironment.create(env);

    DataSet<WC> input = env.fromElements(
            new WC("Hello", 1),
            new WC("zhisheng", 1),
            new WC("Hello", 1));

    tEnv.registerDataSet("WordCount", input, "word, c");

    Table table = tEnv.sqlQuery(
            "SELECT word, SUM(c) as c FROM WordCount GROUP BY word");   //注意，之前 WC 定义的是 count，但在 1.9 中 count 是关键字，所以会抛异常，改成 c ok

    DataSet<WC> result = tEnv.toDataSet(table, WC.class);

    result.print();
}
 

@Test
public void testFullScan() throws Exception {
	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment batchTableEnvironment = BatchTableEnvironment.create(env);
	ParquetTableSource tableSource = createParquetTableSource(testPath);
	((TableEnvironmentInternal) batchTableEnvironment).registerTableSourceInternal("ParquetTable", tableSource);
	String query =
		"SELECT foo " +
		"FROM ParquetTable";

	Table table = batchTableEnvironment.sqlQuery(query);
	DataSet<Row> dataSet = batchTableEnvironment.toDataSet(table, Row.class);
	List<Row> result = dataSet.collect();

	assertEquals(1000, result.size());
}
 

public static void main(String[] args) throws Exception {
    ExecutionEnvironment environment = ExecutionEnvironment.getExecutionEnvironment();
    BatchTableEnvironment tableEnvironment = BatchTableEnvironment.getTableEnvironment(environment);
    String filepath = "file:\\D:\\imooc\\新一代大数据计算引擎 Flink从入门到实战-v\\input\\sales.csv";
    //csv => DataSet
    DataSource<Sales> csv = environment.readCsvFile(filepath)
            .ignoreFirstLine()
            .pojoType(Sales.class, "transactionId", "customerId", "itemId", "amountPaid");
    //csv.print();

    Table sales = tableEnvironment.fromDataSet(csv);
    tableEnvironment.registerTable("sales", sales);
    Table resultTable = tableEnvironment.sqlQuery("select customerId, sum(amountPaid) money from sales group by customerId");

    DataSet<Row> result = tableEnvironment.toDataSet(resultTable, Row.class);
    result.print();
}
 

@Test
public void testAsFromAndToTuple() throws Exception {
	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment tableEnv = BatchTableEnvironment.create(env, config());

	Table table = tableEnv
		.fromDataSet(CollectionDataSets.get3TupleDataSet(env), $("a"), $("b"), $("c"))
		.select($("a"), $("b"), $("c"));

	TypeInformation<?> ti = new TupleTypeInfo<Tuple3<Integer, Long, String>>(
		BasicTypeInfo.INT_TYPE_INFO,
		BasicTypeInfo.LONG_TYPE_INFO,
		BasicTypeInfo.STRING_TYPE_INFO);

	DataSet<?> ds = tableEnv.toDataSet(table, ti);
	List<?> results = ds.collect();
	String expected = "(1,1,Hi)\n" + "(2,2,Hello)\n" + "(3,2,Hello world)\n" +
		"(4,3,Hello world, how are you?)\n" + "(5,3,I am fine.)\n" + "(6,3,Luke Skywalker)\n" +
		"(7,4,Comment#1)\n" + "(8,4,Comment#2)\n" + "(9,4,Comment#3)\n" + "(10,4,Comment#4)\n" +
		"(11,5,Comment#5)\n" + "(12,5,Comment#6)\n" + "(13,5,Comment#7)\n" +
		"(14,5,Comment#8)\n" + "(15,5,Comment#9)\n" + "(16,6,Comment#10)\n" +
		"(17,6,Comment#11)\n" + "(18,6,Comment#12)\n" + "(19,6,Comment#13)\n" +
		"(20,6,Comment#14)\n" + "(21,6,Comment#15)\n";
	compareResultAsText(results, expected);
}
 

@Test
public void testAsFromAndToPrivateFieldPojo() throws Exception {
	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment tableEnv = BatchTableEnvironment.create(env, config());

	List<PrivateSmallPojo> data = new ArrayList<>();
	data.add(new PrivateSmallPojo("Peter", 28, 4000.00, "Sales"));
	data.add(new PrivateSmallPojo("Anna", 56, 10000.00, "Engineering"));
	data.add(new PrivateSmallPojo("Lucy", 42, 6000.00, "HR"));

	Table table = tableEnv
		.fromDataSet(env.fromCollection(data),
			"department AS a, " +
			"age AS b, " +
			"salary AS c, " +
			"name AS d")
		.select("a, b, c, d");

	DataSet<PrivateSmallPojo2> ds = tableEnv.toDataSet(table, PrivateSmallPojo2.class);
	List<PrivateSmallPojo2> results = ds.collect();
	String expected =
		"Sales,28,4000.0,Peter\n" +
		"Engineering,56,10000.0,Anna\n" +
		"HR,42,6000.0,Lucy\n";
	compareResultAsText(results, expected);
}
 

@Test
public void testFilter() throws Exception {
	StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
	StreamTableEnvironment tableEnv = StreamTableEnvironment.create(env);
	StreamITCase.clear();

	DataStream<Tuple5<Integer, Long, Integer, String, Long>> ds = JavaStreamTestData.get5TupleDataStream(env);
	tableEnv.registerDataStream("MyTable", ds, "a, b, c, d, e");

	String sqlQuery = "SELECT a, b, e FROM MyTable WHERE c < 4";
	Table result = tableEnv.sqlQuery(sqlQuery);

	DataStream<Row> resultSet = tableEnv.toAppendStream(result, Row.class);
	resultSet.addSink(new StreamITCase.StringSink<Row>());
	env.execute();

	List<String> expected = new ArrayList<>();
	expected.add("1,1,1");
	expected.add("2,2,2");
	expected.add("2,3,1");
	expected.add("3,4,2");

	StreamITCase.compareWithList(expected);
}
 

public static void main(String[] args) throws Exception {
    StreamExecutionEnvironment blinkStreamEnv = StreamExecutionEnvironment.getExecutionEnvironment();
    blinkStreamEnv.setParallelism(1);
    EnvironmentSettings blinkStreamSettings = EnvironmentSettings.newInstance()
            .useBlinkPlanner()
            .inStreamingMode()
            .build();
    StreamTableEnvironment blinkStreamTableEnv = StreamTableEnvironment.create(blinkStreamEnv, blinkStreamSettings);

    ParameterTool parameterTool = ExecutionEnvUtil.PARAMETER_TOOL;
    Properties properties = KafkaConfigUtil.buildKafkaProps(parameterTool);
    DataStream<String> dataStream = blinkStreamEnv.addSource(new FlinkKafkaConsumer011<>(parameterTool.get("kafka.topic"), new SimpleStringSchema(), properties));
    Table table = blinkStreamTableEnv.fromDataStream(dataStream, "word");
    blinkStreamTableEnv.registerTable("kafkaDataStream", table);

    RetractStreamTableSink<Row> retractStreamTableSink = new MyRetractStreamTableSink(new String[]{"_count", "word"}, new DataType[]{DataTypes.BIGINT(), DataTypes.STRING()});
    blinkStreamTableEnv.registerTableSink("sinkTable", retractStreamTableSink);

    Table wordCount = blinkStreamTableEnv.sqlQuery("SELECT count(word) AS _count,word FROM kafkaDataStream GROUP BY word");

    wordCount.insertInto("sinkTable");

    blinkStreamTableEnv.execute("Blink Kafka Table Source");
}
 

@Test
public void testBatchTableSourceTableAPI() throws Exception {

	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment tableEnv = BatchTableEnvironment.create(env, config());
	BatchTableSource csvTable = CommonTestData.getCsvTableSource();

	((TableEnvironmentInternal) tableEnv).registerTableSourceInternal("persons", csvTable);

	Table result = tableEnv.scan("persons")
		.select($("id"), $("first"), $("last"), $("score"));

	DataSet<Row> resultSet = tableEnv.toDataSet(result, Row.class);
	List<Row> results = resultSet.collect();

	String expected = "1,Mike,Smith,12.3\n" +
		"2,Bob,Taylor,45.6\n" +
		"3,Sam,Miller,7.89\n" +
		"4,Peter,Smith,0.12\n" +
		"5,Liz,Williams,34.5\n" +
		"6,Sally,Miller,6.78\n" +
		"7,Alice,Smith,90.1\n" +
		"8,Kelly,Williams,2.34\n";

	compareResultAsText(results, expected);
}
 

@Test
public void testTableRegister() throws Exception {
	final String tableName = "MyTable";
	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment tableEnv = BatchTableEnvironment.create(env, config());

	DataSet<Tuple3<Integer, Long, String>> ds = CollectionDataSets.get3TupleDataSet(env);
	Table t = tableEnv.fromDataSet(ds);
	tableEnv.createTemporaryView(tableName, t);
	Table result = tableEnv.scan(tableName).select($("f0"), $("f1")).filter($("f0").isGreater(7));

	DataSet<Row> resultSet = tableEnv.toDataSet(result, Row.class);
	List<Row> results = resultSet.collect();
	String expected = "8,4\n" + "9,4\n" + "10,4\n" + "11,5\n" + "12,5\n" +
			"13,5\n" + "14,5\n" + "15,5\n" +
			"16,6\n" + "17,6\n" + "18,6\n" + "19,6\n" + "20,6\n" + "21,6\n";
	compareResultAsText(results, expected);
}
 

@Override
public <T> Table fromDataStream(DataStream<T> dataStream) {
	JavaDataStreamQueryOperation<T> queryOperation = asQueryOperation(
		dataStream,
		Optional.empty());

	return createTable(queryOperation);
}
 

@Test
public void testTableSourceReadAsByteArray() throws Exception {

	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	env.setParallelism(4);
	BatchTableEnvironment tableEnv = BatchTableEnvironment.create(env, new TableConfig());
	// fetch row2 from the table till the end
	HBaseTableSource hbaseTable = new HBaseTableSource(getConf(), TEST_TABLE);
	hbaseTable.addColumn(FAMILY2, F2COL1, byte[].class);
	hbaseTable.addColumn(FAMILY2, F2COL2, byte[].class);

	tableEnv.registerTableSource("hTable", hbaseTable);
	tableEnv.registerFunction("toUTF8", new ToUTF8());
	tableEnv.registerFunction("toLong", new ToLong());

	Table result = tableEnv.sqlQuery(
		"SELECT " +
			"  toUTF8(h.family2.col1), " +
			"  toLong(h.family2.col2) " +
			"FROM hTable AS h"
	);
	DataSet<Row> resultSet = tableEnv.toDataSet(result, Row.class);
	List<Row> results = resultSet.collect();

	String expected =
		"Hello-1,100\n" +
		"Hello-2,200\n" +
		"Hello-3,300\n" +
		"null,400\n" +
		"Hello-5,500\n" +
		"Hello-6,600\n" +
		"Hello-7,700\n" +
		"null,800\n";

	TestBaseUtils.compareResultAsText(results, expected);
}
 

public static Table getDenseBatchWithDoubleLabel() {

        Row[] testArray = new Row[]{
            Row.of(7, "0.0  0.0  18.0  1.0", 1.0),
            Row.of(8, "0.0  1.0  12.0  0.0", 0.0),
            Row.of(9, "1.0  0.0  15.0  0.1", 0.0),
        };

        String[] colNames = new String[]{"id", "features", "clicked"};

        return MLEnvironmentFactory.getDefault().createBatchTable(Arrays.asList(testArray), colNames);
    }
 

@Override
public Table select(String fields) {
	return table.createTable(
		table.operationTreeBuilder.project(
			ExpressionParser.parseExpressionList(fields),
			table.operationTreeBuilder.tableAggregate(
				groupKey,
				tableAggregateFunction.accept(table.lookupResolver),
				table.operationTree
			)
		));
}
 

@Test
public void testFitStreamTable() {
	Long id = MLEnvironmentFactory.getNewMLEnvironmentId();
	MLEnvironment env = MLEnvironmentFactory.get(id);
	DataStream<Integer> input = env.getStreamExecutionEnvironment().fromElements(1, 2, 3);
	Table table = env.getStreamTableEnvironment().fromDataStream(input);

	FakeTransFormer transFormer = new FakeTransFormer();
	transFormer.setMLEnvironmentId(id);
	transFormer.transform(env.getStreamTableEnvironment(), table);

	Assert.assertFalse(transFormer.batchTransformed);
	Assert.assertTrue(transFormer.streamTransformed);
}
 

private void compareSql(String query1, String query2) throws Exception {

		// Function to map row to string
		MapFunction<Row, String> mapFunction = new MapFunction<Row, String>() {

			@Override
			public String map(Row value) throws Exception {
				return value == null ? "null" : value.toString();
			}
		};

		// Execute first query and store results
		Table resultTable1 = tableEnv.sqlQuery(query1);
		DataSet<Row> resultDataSet1 = tableEnv.toDataSet(resultTable1, Row.class);
		List<String> results1 = resultDataSet1.map(mapFunction).collect();

		// Execute second query and store results
		Table resultTable2 = tableEnv.sqlQuery(query2);
		DataSet<Row> resultDataSet2 = tableEnv.toDataSet(resultTable2, Row.class);
		List<String> results2 = resultDataSet2.map(mapFunction).collect();

		// Compare results
		TestBaseUtils.compareResultCollections(results1, results2, new Comparator<String>() {

			@Override
			public int compare(String o1, String o2) {
				return o2 == null ? o1 == null ? 0 : 1 : o1.compareTo(o2);
			}
		});
	}
 

@Override
public M fit(TableEnvironment tEnv, Table input) {
	Preconditions.checkArgument(input != null, "Input CAN NOT BE null!");
	Preconditions.checkArgument(
		tableEnvOf(input) == tEnv,
		"The input table is not in the specified table environment.");
	return fit(input);
}
 

private static double sqlQuery(BatchTableEnvironment tEnv, String sql) throws Throwable {
  Stopwatch s = Stopwatch.createStarted();
  Table t2 = tEnv.sqlQuery(sql);
  System.out.println("sqlQuery result table size:" + tEnv.toDataSet(t2, Row.class).collect().size());
  s.stop();
  return s.elapsed(TimeUnit.MICROSECONDS) * 0.001;
}
 

@Override
public <T> DataStream<Tuple2<Boolean, T>> toRetractStream(
		Table table,
		Class<T> clazz,
		StreamQueryConfig queryConfig) {
	tableConfig.setIdleStateRetentionTime(
		Time.milliseconds(queryConfig.getMinIdleStateRetentionTime()),
		Time.milliseconds(queryConfig.getMaxIdleStateRetentionTime()));
	return toRetractStream(table, clazz);
}
 

private <T> TypeInformation<T> extractTypeInformation(Table table, Class<T> clazz) {
	try {
		return TypeExtractor.createTypeInfo(clazz);
	} catch (Exception ex) {
		throw new ValidationException(
			String.format(
				"Could not convert query: %s to a DataStream of class %s",
				table.getQueryOperation().asSummaryString(),
				clazz.getSimpleName()),
			ex);
	}
}
 

@Test
public void testUpsert() throws Exception {
	StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
	env.getConfig().enableObjectReuse();
	env.setStreamTimeCharacteristic(TimeCharacteristic.EventTime);
	StreamTableEnvironment tEnv = StreamTableEnvironment.create(env);

	Table t = tEnv.fromDataStream(get3TupleDataStream(env).assignTimestampsAndWatermarks(
			new AscendingTimestampExtractor<Tuple3<Integer, Long, String>>() {
				@Override
				public long extractAscendingTimestamp(Tuple3<Integer, Long, String> element) {
					return element.f0;
				}}), "id, num, text");

	tEnv.registerTable("T", t);

	String[] fields = {"cnt", "lencnt", "cTag"};
	tEnv.registerTableSink("upsertSink", JDBCUpsertTableSink.builder()
			.setOptions(JDBCOptions.builder()
					.setDBUrl(DB_URL)
					.setTableName(OUTPUT_TABLE1)
					.build())
			.setTableSchema(TableSchema.builder().fields(
					fields, new DataType[] {BIGINT(), BIGINT(), INT()}).build())
			.build());

	tEnv.sqlUpdate("INSERT INTO upsertSink SELECT cnt, COUNT(len) AS lencnt, cTag FROM" +
			" (SELECT len, COUNT(id) as cnt, cTag FROM" +
			" (SELECT id, CHAR_LENGTH(text) AS len, (CASE WHEN id > 0 THEN 1 ELSE 0 END) cTag FROM T)" +
			" GROUP BY len, cTag)" +
			" GROUP BY cnt, cTag");
	env.execute();
	check(new Row[] {
			Row.of(1, 5, 1),
			Row.of(7, 1, 1),
			Row.of(9, 1, 1)
	}, DB_URL, OUTPUT_TABLE1, fields);
}