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

The following examples show how to use org.apache.flink.table.api.java.BatchTableEnvironment#registerDataSet() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: JavaSqlITCase.java    From flink with Apache License 2.0 6 votes vote down vote up 
@Test
public void testAggregation() throws Exception {
	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment tableEnv = BatchTableEnvironment.create(env, config());

	DataSet<Tuple3<Integer, Long, String>> ds = CollectionDataSets.get3TupleDataSet(env);
	tableEnv.registerDataSet("AggTable", ds, "x, y, z");

	String sqlQuery = "SELECT sum(x), min(x), max(x), count(y), avg(x) FROM AggTable";
	Table result = tableEnv.sqlQuery(sqlQuery);

	DataSet<Row> resultSet = tableEnv.toDataSet(result, Row.class);
	List<Row> results = resultSet.collect();
	String expected = "231,1,21,21,11";
	compareResultAsText(results, expected);
}
Example 2
Source File: WordCountSQL.java    From flink-learning with Apache License 2.0 6 votes vote down vote up 
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();
}
Example 3
Source File: FlinkCollectionsEnvBenchMark.java    From marble with Apache License 2.0 6 votes vote down vote up 
public double runSqlForSingleTable(int limit, String sql) throws Throwable {
  Stopwatch s = Stopwatch.createStarted();
  try (Connection connection = BenchMarkUtil.getDBConnection()) {
    String fetchSql = BenchMarkUtil.generateFetchSql("item1", "i_item_sk", limit);
    ResultSet resultSet = connection
        .createStatement()
        .executeQuery(fetchSql);
    RowTypeInfo rowTypeInfo = typeOfJdbc(resultSet.getMetaData());
    ExecutionEnvironment env = ExecutionEnvironment.createCollectionsEnvironment();
    BatchTableEnvironment tEnv = TableEnvironment.getTableEnvironment(env);
    DataSet ds = env.createInput(
        JDBCInputFormat.buildJDBCInputFormat()
            .setDrivername(BenchMarkUtil.DB_DRIVER)
            .setDBUrl(BenchMarkUtil.DB_CONNECTION_URL)
            .setQuery(fetchSql)
            .setRowTypeInfo(rowTypeInfo)
            .finish()
    );
    ds.collect();
    tEnv.registerDataSet("item1", ds);
    s.stop();
    return s.elapsed(TimeUnit.MICROSECONDS) * 0.001 + sqlQuery(tEnv, sql);
  }
}
Example 4
Source File: WordCountSQL.java    From flink with Apache License 2.0 6 votes vote down vote up 
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();
	}
Example 5
Source File: JavaTableEnvironmentITCase.java    From flink with Apache License 2.0 6 votes vote down vote up 
@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);
}
Example 6
Source File: JavaTableEnvironmentITCase.java    From flink with Apache License 2.0 6 votes vote down vote up 
@Test
public void testSimpleRegister() 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);
	Table t = tableEnv.scan(tableName);

	Table result = t.select("f0, f1");

	DataSet<Row> resultSet = tableEnv.toDataSet(result, Row.class);
	List<Row> results = resultSet.collect();
	String expected = "1,1\n" + "2,2\n" + "3,2\n" + "4,3\n" + "5,3\n" + "6,3\n" + "7,4\n" +
			"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);
}
Example 7
Source File: JavaSqlITCase.java    From flink with Apache License 2.0 6 votes vote down vote up 
@Test
public void testMap() throws Exception {
	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment tableEnv = BatchTableEnvironment.create(env, config());

	List<Tuple2<Integer, Map<String, String>>> rows = new ArrayList<>();
	rows.add(new Tuple2<>(1, Collections.singletonMap("foo", "bar")));
	rows.add(new Tuple2<>(2, Collections.singletonMap("foo", "spam")));

	TypeInformation<Tuple2<Integer, Map<String, String>>> ty = new TupleTypeInfo<>(
		BasicTypeInfo.INT_TYPE_INFO,
		new MapTypeInfo<>(BasicTypeInfo.STRING_TYPE_INFO, BasicTypeInfo.STRING_TYPE_INFO));

	DataSet<Tuple2<Integer, Map<String, String>>> ds1 = env.fromCollection(rows, ty);
	tableEnv.registerDataSet("t1", ds1, "a, b");

	String sqlQuery = "SELECT b['foo'] FROM t1";
	Table result = tableEnv.sqlQuery(sqlQuery);

	DataSet<Row> resultSet = tableEnv.toDataSet(result, Row.class);
	List<Row> results = resultSet.collect();
	String expected = "bar\n" + "spam\n";
	compareResultAsText(results, expected);
}
Example 8
Source File: JavaSqlITCase.java    From flink with Apache License 2.0 6 votes vote down vote up 
@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);
}
Example 9
Source File: JavaSqlITCase.java    From Flink-CEPplus with Apache License 2.0 6 votes vote down vote up 
@Test
public void testFilterFromDataSet() throws Exception {
	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment tableEnv = BatchTableEnvironment.create(env, config());

	DataSet<Tuple3<Integer, Long, String>> ds = CollectionDataSets.get3TupleDataSet(env);
	tableEnv.registerDataSet("DataSetTable", ds, "x, y, z");

	String sqlQuery = "SELECT x FROM DataSetTable WHERE z LIKE '%Hello%'";
	Table result = tableEnv.sqlQuery(sqlQuery);

	DataSet<Row> resultSet = tableEnv.toDataSet(result, Row.class);
	List<Row> results = resultSet.collect();
	String expected = "2\n" + "3\n" + "4";
	compareResultAsText(results, expected);
}
Example 10
Source File: JavaSqlITCase.java    From flink with Apache License 2.0 6 votes vote down vote up 
@Test
public void testFilterFromDataSet() throws Exception {
	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment tableEnv = BatchTableEnvironment.create(env, config());

	DataSet<Tuple3<Integer, Long, String>> ds = CollectionDataSets.get3TupleDataSet(env);
	tableEnv.registerDataSet("DataSetTable", ds, "x, y, z");

	String sqlQuery = "SELECT x FROM DataSetTable WHERE z LIKE '%Hello%'";
	Table result = tableEnv.sqlQuery(sqlQuery);

	DataSet<Row> resultSet = tableEnv.toDataSet(result, Row.class);
	List<Row> results = resultSet.collect();
	String expected = "2\n" + "3\n" + "4";
	compareResultAsText(results, expected);
}
Example 11
Source File: WordCountSQL.java    From flink-learning with Apache License 2.0 6 votes vote down vote up 
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();
}
Example 12
Source File: WordCountSQL.java    From Flink-CEPplus with Apache License 2.0 6 votes vote down vote up 
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();
	}
Example 13
Source File: JavaTableEnvironmentITCase.java    From Flink-CEPplus with Apache License 2.0 6 votes vote down vote up 
@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);
}
Example 14
Source File: JavaTableEnvironmentITCase.java    From Flink-CEPplus with Apache License 2.0 6 votes vote down vote up 
@Test
public void testSimpleRegister() 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);
	Table t = tableEnv.scan(tableName);

	Table result = t.select("f0, f1");

	DataSet<Row> resultSet = tableEnv.toDataSet(result, Row.class);
	List<Row> results = resultSet.collect();
	String expected = "1,1\n" + "2,2\n" + "3,2\n" + "4,3\n" + "5,3\n" + "6,3\n" + "7,4\n" +
			"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);
}
Example 15
Source File: JavaSqlITCase.java    From Flink-CEPplus with Apache License 2.0 6 votes vote down vote up 
@Test
public void testMap() throws Exception {
	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment tableEnv = BatchTableEnvironment.create(env, config());

	List<Tuple2<Integer, Map<String, String>>> rows = new ArrayList<>();
	rows.add(new Tuple2<>(1, Collections.singletonMap("foo", "bar")));
	rows.add(new Tuple2<>(2, Collections.singletonMap("foo", "spam")));

	TypeInformation<Tuple2<Integer, Map<String, String>>> ty = new TupleTypeInfo<>(
		BasicTypeInfo.INT_TYPE_INFO,
		new MapTypeInfo<>(BasicTypeInfo.STRING_TYPE_INFO, BasicTypeInfo.STRING_TYPE_INFO));

	DataSet<Tuple2<Integer, Map<String, String>>> ds1 = env.fromCollection(rows, ty);
	tableEnv.registerDataSet("t1", ds1, "a, b");

	String sqlQuery = "SELECT b['foo'] FROM t1";
	Table result = tableEnv.sqlQuery(sqlQuery);

	DataSet<Row> resultSet = tableEnv.toDataSet(result, Row.class);
	List<Row> results = resultSet.collect();
	String expected = "bar\n" + "spam\n";
	compareResultAsText(results, expected);
}
Example 16
Source File: JavaSqlITCase.java    From Flink-CEPplus with Apache License 2.0 6 votes vote down vote up 
@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);
}
Example 17
Source File: JavaSqlITCase.java    From Flink-CEPplus with Apache License 2.0 6 votes vote down vote up 
@Test
public void testAggregation() throws Exception {
	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment tableEnv = BatchTableEnvironment.create(env, config());

	DataSet<Tuple3<Integer, Long, String>> ds = CollectionDataSets.get3TupleDataSet(env);
	tableEnv.registerDataSet("AggTable", ds, "x, y, z");

	String sqlQuery = "SELECT sum(x), min(x), max(x), count(y), avg(x) FROM AggTable";
	Table result = tableEnv.sqlQuery(sqlQuery);

	DataSet<Row> resultSet = tableEnv.toDataSet(result, Row.class);
	List<Row> results = resultSet.collect();
	String expected = "231,1,21,21,11";
	compareResultAsText(results, expected);
}
Example 18
Source File: JavaTableEnvironmentITCase.java    From Flink-CEPplus with Apache License 2.0 5 votes vote down vote up 
@Test(expected = TableException.class)
public void testRegisterExistingDatasetTable() throws Exception {
	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment tableEnv = BatchTableEnvironment.create(env, config());

	DataSet<Tuple3<Integer, Long, String>> ds = CollectionDataSets.get3TupleDataSet(env);
	tableEnv.registerDataSet("MyTable", ds);
	DataSet<Tuple5<Integer, Long, Integer, String, Long>> ds2 =
			CollectionDataSets.getSmall5TupleDataSet(env);
	// Must fail. Name is already used for different table.
	tableEnv.registerDataSet("MyTable", ds2);
}
Example 19
Source File: JavaTableEnvironmentITCase.java    From flink with Apache License 2.0 5 votes vote down vote up 
@Test(expected = TableAlreadyExistException.class)
public void testRegisterExistingDatasetTable() throws Exception {
	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment tableEnv = BatchTableEnvironment.create(env, config());

	DataSet<Tuple3<Integer, Long, String>> ds = CollectionDataSets.get3TupleDataSet(env);
	tableEnv.registerDataSet("MyTable", ds);
	DataSet<Tuple5<Integer, Long, Integer, String, Long>> ds2 =
			CollectionDataSets.getSmall5TupleDataSet(env);
	// Must fail. Name is already used for different table.
	tableEnv.registerDataSet("MyTable", ds2);
}
Example 20
Source File: FlinkCollectionsEnvBenchMark.java    From marble with Apache License 2.0 5 votes vote down vote up 
public double runSqlForJoin(int limit, String sql) throws Throwable {
  Stopwatch s = Stopwatch.createStarted();
  try (Connection connection = BenchMarkUtil.getDBConnection()) {
    ExecutionEnvironment env = ExecutionEnvironment.createCollectionsEnvironment();
    BatchTableEnvironment tEnv = TableEnvironment.getTableEnvironment(env);
    String fetchSql1 = BenchMarkUtil.generateFetchSql("item1", "i_item_sk", limit);
    ResultSet resultSet1 = connection
        .createStatement()
        .executeQuery(fetchSql1);
    RowTypeInfo rowTypeInfo1 = typeOfJdbc(resultSet1.getMetaData());
    DataSet ds1 = env.createInput(
        JDBCInputFormat.buildJDBCInputFormat()
            .setDrivername(BenchMarkUtil.DB_DRIVER)
            .setDBUrl(BenchMarkUtil.DB_CONNECTION_URL)
            .setQuery(fetchSql1)
            .setRowTypeInfo(rowTypeInfo1)
            .finish()
    );
    ds1.collect();
    tEnv.registerDataSet("item1", ds1);

    String fetchSql2 = BenchMarkUtil.generateFetchSql("item2", "i_item_sk", limit);
    ResultSet resultSet2 = connection
        .createStatement()
        .executeQuery(fetchSql2);
    RowTypeInfo rowTypeInfo2 = typeOfJdbc(resultSet2.getMetaData());
    DataSet ds2 = env.createInput(
        JDBCInputFormat.buildJDBCInputFormat()
            .setDrivername(BenchMarkUtil.DB_DRIVER)
            .setDBUrl(BenchMarkUtil.DB_CONNECTION_URL)
            .setQuery(fetchSql2)
            .setRowTypeInfo(rowTypeInfo2)
            .finish()
    );
    ds2.collect();
    tEnv.registerDataSet("item2", ds2);
    s.stop();
    return s.elapsed(TimeUnit.MICROSECONDS) * 0.001 + sqlQuery(tEnv, sql);
  }
}