Java Code Examples for org.apache.flink.table.api.java.BatchTableEnvironment#toDataSet()

@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 testSelectFromTable() throws Exception {
	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment tableEnv = BatchTableEnvironment.create(env, config());

	DataSet<Tuple3<Integer, Long, String>> ds = CollectionDataSets.get3TupleDataSet(env);
	Table in = tableEnv.fromDataSet(ds, "a,b,c");
	tableEnv.registerTable("T", in);

	String sqlQuery = "SELECT a, c FROM T";
	Table result = tableEnv.sqlQuery(sqlQuery);

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

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

	Table table = tableEnv.fromDataSet(CollectionDataSets.get3TupleDataSet(env), "f2");

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

@Test
public void testBatchTableSourceTableAPI() throws Exception {

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

	tableEnv.registerTableSource("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 testJoin() throws Exception {
	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment tableEnv = BatchTableEnvironment.create(env, config());

	DataSet<Tuple3<Integer, Long, String>> ds1 = CollectionDataSets.getSmall3TupleDataSet(env);
	DataSet<Tuple5<Integer, Long, Integer, String, Long>> ds2 = CollectionDataSets.get5TupleDataSet(env);

	tableEnv.registerDataSet("t1", ds1, "a, b, c");
	tableEnv.registerDataSet("t2", ds2, "d, e, f, g, h");

	String sqlQuery = "SELECT c, g FROM t1, t2 WHERE b = e";
	Table result = tableEnv.sqlQuery(sqlQuery);

	DataSet<Row> resultSet = tableEnv.toDataSet(result, Row.class);
	List<Row> results = resultSet.collect();
	String expected = "Hi,Hallo\n" + "Hello,Hallo Welt\n" + "Hello world,Hallo Welt\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 testJoin() throws Exception {
	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment tableEnv = BatchTableEnvironment.create(env, config());

	DataSet<Tuple3<Integer, Long, String>> ds1 = CollectionDataSets.getSmall3TupleDataSet(env);
	DataSet<Tuple5<Integer, Long, Integer, String, Long>> ds2 = CollectionDataSets.get5TupleDataSet(env);

	tableEnv.registerDataSet("t1", ds1, "a, b, c");
	tableEnv.registerDataSet("t2", ds2, "d, e, f, g, h");

	String sqlQuery = "SELECT c, g FROM t1, t2 WHERE b = e";
	Table result = tableEnv.sqlQuery(sqlQuery);

	DataSet<Row> resultSet = tableEnv.toDataSet(result, Row.class);
	List<Row> results = resultSet.collect();
	String expected = "Hi,Hallo\n" + "Hello,Hallo Welt\n" + "Hello world,Hallo Welt\n";
	compareResultAsText(results, expected);
}
 

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

	String sqlQuery = "VALUES (1, 'Test', TRUE, DATE '1944-02-24', 12.4444444444444445)," +
		"(2, 'Hello', TRUE, DATE '1944-02-24', 12.666666665)," +
		"(3, 'World', FALSE, DATE '1944-12-24', 12.54444445)";
	Table result = tableEnv.sqlQuery(sqlQuery);

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

	List<Row> results = resultSet.collect();
	String expected = "3,World,false,1944-12-24,12.5444444500000000\n" +
		"2,Hello,true,1944-02-24,12.6666666650000000\n" +
		// Calcite converts to decimals and strings with equal length
		"1,Test ,true,1944-02-24,12.4444444444444445\n";
	compareResultAsText(results, expected);
}
 

@Test
public void testBatchTableSourceTableAPI() throws Exception {

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

	tableEnv.registerTableSource("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 testFullScan() throws Exception {
	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment batchTableEnvironment = BatchTableEnvironment.create(env);
	ParquetTableSource tableSource = createParquetTableSource(testPath);
	batchTableEnvironment.registerTableSource("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 {

		// set up execution environment
		ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
		BatchTableEnvironment tEnv = BatchTableEnvironment.create(env);

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

		// register the DataSet as table "WordCount"
		tEnv.registerDataSet("WordCount", input, "word, frequency");

		// run a SQL query on the Table and retrieve the result as a new Table
		Table table = tEnv.sqlQuery(
			"SELECT word, SUM(frequency) as frequency FROM WordCount GROUP BY word");

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

		result.print();
	}
 

@Test
public void testRegisterWithFields() 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);
	tableEnv.registerDataSet(tableName, ds, "a, b, c");
	Table t = tableEnv.scan(tableName);

	Table result = t.select("a, b, c");

	DataSet<Row> resultSet = tableEnv.toDataSet(result, Row.class);
	List<Row> results = resultSet.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);
}
 

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("Ciao", 1),
			new WC("Hello", 1));

	Table table = tEnv.fromDataSet(input);

	Table filtered = table
			.groupBy("word")
			.select("word, frequency.sum as frequency")
			.filter("frequency = 2");

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

	result.print();
}
 

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

	List<Either<String, Integer>> data = new ArrayList<>();
	data.add(new Either.Left<>("Hello"));
	data.add(new Either.Right<>(42));
	data.add(new Either.Left<>("World"));

	Table table = tableEnv
		.fromDataSet(
			env.fromCollection(
				data,
				TypeInformation.of(new TypeHint<Either<String, Integer>>() { })
			),
			"either")
		.select("either");

	DataSet<Row> ds = tableEnv.toDataSet(table, Row.class);
	List<Row> results = ds.collect();
	String expected =
		"Left(Hello)\n" +
		"Left(World)\n" +
		"Right(42)\n";
	compareResultAsText(results, expected);
}
 

@Test(expected = TableException.class)
public void testCustomCalciteConfig() {
	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment tableEnv = BatchTableEnvironment.create(env, config());

	CalciteConfig cc = new CalciteConfigBuilder()
			.replaceLogicalOptRuleSet(RuleSets.ofList())
			.replacePhysicalOptRuleSet(RuleSets.ofList())
			.build();
	tableEnv.getConfig().setCalciteConfig(cc);

	DataSet<Tuple3<Integer, Long, String>> ds = CollectionDataSets.get3TupleDataSet(env);
	Table t = tableEnv.fromDataSet(ds);
	tableEnv.toDataSet(t, Row.class);
}
 

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

	// Must fail since class is not static
	Table t = tableEnv.fromDataSet(env.fromElements(1, 2, 3), "number");
	tableEnv.toDataSet(t, MyNonStatic.class);
}
 

@Test
public void testAsFromAndToPojo() 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),
			"department AS a, " +
			"age AS b, " +
			"salary AS c, " +
			"name AS d," +
			"roles AS e")
		.select("a, b, c, d, e");

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

@Test(expected = TableException.class)
public void testCustomCalciteConfig() {
	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment tableEnv = BatchTableEnvironment.create(env, config());

	PlannerConfig cc = new CalciteConfigBuilder()
			.replaceLogicalOptRuleSet(RuleSets.ofList())
			.replacePhysicalOptRuleSet(RuleSets.ofList())
			.build();
	tableEnv.getConfig().setPlannerConfig(cc);

	DataSet<Tuple3<Integer, Long, String>> ds = CollectionDataSets.get3TupleDataSet(env);
	Table t = tableEnv.fromDataSet(ds);
	tableEnv.toDataSet(t, Row.class);
}
 

/**
 * Collects batch result depends on the {@link #planner} context.
 */
private List<Row> collectBatchResult(Table table) throws Exception {
	TableImpl tableImpl = (TableImpl) table;
	if (OLD_PLANNER.equals(planner)) {
		BatchTableEnvironment batchTableEnv = (BatchTableEnvironment) tableImpl.getTableEnvironment();
		DataSet<Row> resultSet = batchTableEnv.toDataSet(table, Row.class);
		return resultSet.collect();
	} else {
		return JavaScalaConversionUtil.toJava(TableUtil.collect(tableImpl));
	}
}
 

private void testTableSourceBatchDescriptor(Stream stream, PravegaConfig pravegaConfig) throws Exception {
    ExecutionEnvironment execEnvRead = ExecutionEnvironment.getExecutionEnvironment();
    // Can only use Legacy Flink planner for BatchTableEnvironment
    BatchTableEnvironment tableEnv = BatchTableEnvironment.create(execEnvRead);
    execEnvRead.setParallelism(1);

    Schema schema = new Schema()
            .field("user", DataTypes.STRING())
            .field("uri", DataTypes.STRING())
            // Note: LocalDateTime is not supported in legacy Flink planner, bridged to Timestamp with the data source.
            // See https://issues.apache.org/jira/browse/FLINK-16693 for more information.
            .field("accessTime", DataTypes.TIMESTAMP(3).bridgedTo(Timestamp.class));

    Pravega pravega = new Pravega();

    pravega.tableSourceReaderBuilder()
            .withReaderGroupScope(stream.getScope())
            .forStream(stream)
            .withPravegaConfig(pravegaConfig);

    ConnectTableDescriptor desc = tableEnv.connect(pravega)
            .withFormat(new Json().failOnMissingField(false))
            .withSchema(schema);

    final Map<String, String> propertiesMap = desc.toProperties();
    final TableSource<?> source = TableFactoryService.find(BatchTableSourceFactory.class, propertiesMap)
            .createBatchTableSource(propertiesMap);

    String tableSourcePath = tableEnv.getCurrentDatabase() + "." + "MyTableRow";

    ConnectorCatalogTable<?, ?> connectorCatalogSourceTable = ConnectorCatalogTable.source(source, true);

    tableEnv.getCatalog(tableEnv.getCurrentCatalog()).get().createTable(
            ObjectPath.fromString(tableSourcePath),
            connectorCatalogSourceTable, false);

    String sqlQuery = "SELECT user, count(uri) from MyTableRow GROUP BY user";

    Table result = tableEnv.sqlQuery(sqlQuery);

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

    List<Row> results = resultSet.collect();
    log.info("results: {}", results);

    boolean compare = compare(results, getExpectedResultsRetracted());
    assertTrue("Output does not match expected result", compare);
}