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

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

@Test
public void testBatchTableSourceSQL() throws Exception {
	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment tableEnv = BatchTableEnvironment.create(env, config());
	BatchTableSource csvTable = CommonTestData.getCsvTableSource();

	tableEnv.registerTableSource("persons", csvTable);

	Table result = tableEnv
		.sqlQuery("SELECT `last`, FLOOR(id), score * 2 FROM persons WHERE score < 20");

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

	String expected = "Smith,1,24.6\n" +
		"Miller,3,15.78\n" +
		"Smith,4,0.24\n" +
		"Miller,6,13.56\n" +
		"Williams,8,4.68\n";

	compareResultAsText(results, expected);
}
 

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

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

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

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

@Test
public void testScanWithProjectionAndFilter() 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 WHERE bar.spam >= 30 AND CARDINALITY(arr) >= 1 AND arr[1] <= 50";

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

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

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

@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 testScanWithProjectionAndFilter() throws Exception {

	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment tEnv = BatchTableEnvironment.create(env);

	OrcTableSource orc = OrcTableSource.builder()
		.path(getPath(TEST_FILE_FLAT))
		.forOrcSchema(TEST_SCHEMA_FLAT)
		.build();
	tEnv.registerTableSource("OrcTable", orc);

	String query =
		"SELECT " +
			"MIN(_col4), MAX(_col4), " +
			"MIN(_col3), MAX(_col3), " +
			"MIN(_col0), MAX(_col0), " +
			"MIN(_col2), MAX(_col2), " +
			"COUNT(*) " +
			"FROM OrcTable " +
			"WHERE (_col0 BETWEEN 4975 and 5024 OR _col0 BETWEEN 9975 AND 10024) AND _col1 = 'F'";
	Table t = tEnv.sqlQuery(query);

	DataSet<Row> dataSet = tEnv.toDataSet(t, Row.class);
	List<Row> result = dataSet.collect();

	assertEquals(1, result.size());
	assertEquals(
		"1500,6000,2 yr Degree,Unknown,4976,10024,D,W,50",
		result.get(0).toString());
}
 

@Test
public void testFullScan() throws Exception {

	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment tEnv = BatchTableEnvironment.create(env);

	OrcTableSource orc = OrcTableSource.builder()
		.path(getPath(TEST_FILE_FLAT))
		.forOrcSchema(TEST_SCHEMA_FLAT)
		.build();
	tEnv.registerTableSource("OrcTable", orc);

	String query =
		"SELECT COUNT(*), " +
			"MIN(_col0), MAX(_col0), " +
			"MIN(_col1), MAX(_col1), " +
			"MIN(_col2), MAX(_col2), " +
			"MIN(_col3), MAX(_col3), " +
			"MIN(_col4), MAX(_col4), " +
			"MIN(_col5), MAX(_col5), " +
			"MIN(_col6), MAX(_col6), " +
			"MIN(_col7), MAX(_col7), " +
			"MIN(_col8), MAX(_col8) " +
		"FROM OrcTable";
	Table t = tEnv.sqlQuery(query);

	DataSet<Row> dataSet = tEnv.toDataSet(t, Row.class);
	List<Row> result = dataSet.collect();

	assertEquals(1, result.size());
	assertEquals(
		"1920800,1,1920800,F,M,D,W,2 yr Degree,Unknown,500,10000,Good,Unknown,0,6,0,6,0,6",
		result.get(0).toString());
}
 

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;
}
 

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

@Test
public void testFullScan() throws Exception {

	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment tEnv = BatchTableEnvironment.create(env);

	OrcTableSource orc = OrcTableSource.builder()
		.path(getPath(TEST_FILE_FLAT))
		.forOrcSchema(TEST_SCHEMA_FLAT)
		.build();
	tEnv.registerTableSource("OrcTable", orc);

	String query =
		"SELECT COUNT(*), " +
			"MIN(_col0), MAX(_col0), " +
			"MIN(_col1), MAX(_col1), " +
			"MIN(_col2), MAX(_col2), " +
			"MIN(_col3), MAX(_col3), " +
			"MIN(_col4), MAX(_col4), " +
			"MIN(_col5), MAX(_col5), " +
			"MIN(_col6), MAX(_col6), " +
			"MIN(_col7), MAX(_col7), " +
			"MIN(_col8), MAX(_col8) " +
		"FROM OrcTable";
	Table t = tEnv.sqlQuery(query);

	DataSet<Row> dataSet = tEnv.toDataSet(t, Row.class);
	List<Row> result = dataSet.collect();

	assertEquals(1, result.size());
	assertEquals(
		"1920800,1,1920800,F,M,D,W,2 yr Degree,Unknown,500,10000,Good,Unknown,0,6,0,6,0,6",
		result.get(0).toString());
}
 

/**
 * Validates the use of Pravega Table Descriptor to generate the source/sink Table factory to
 * write and read from Pravega stream using {@link BatchTableEnvironment}
 * @throws Exception
 */
@Test
public void testBatchTableUsingDescriptor() throws Exception {

    final String scope = setupUtils.getScope();
    final String streamName = "stream";
    Stream stream = Stream.of(scope, streamName);
    this.setupUtils.createTestStream(stream.getStreamName(), 1);

    ExecutionEnvironment env = ExecutionEnvironment.createLocalEnvironment();
    env.setParallelism(1);
    BatchTableEnvironment tableEnv = BatchTableEnvironment.create(env);

    PravegaConfig pravegaConfig = setupUtils.getPravegaConfig();

    Pravega pravega = new Pravega();
    pravega.tableSinkWriterBuilder()
            .withRoutingKeyField("category")
            .forStream(stream)
            .withPravegaConfig(pravegaConfig);
    pravega.tableSourceReaderBuilder()
            .withReaderGroupScope(stream.getScope())
            .forStream(stream)
            .withPravegaConfig(pravegaConfig);

    ConnectTableDescriptor desc = tableEnv.connect(pravega)
            .withFormat(new Json().failOnMissingField(false))
            .withSchema(new Schema().
                    field("category", DataTypes.STRING()).
                    field("value", DataTypes.INT()));
    desc.createTemporaryTable("test");

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

    Table table = tableEnv.fromDataSet(env.fromCollection(SAMPLES));

    String tableSinkPath = tableEnv.getCurrentDatabase() + "." + "PravegaSink";

    ConnectorCatalogTable<?, ?> connectorCatalogTableSink = ConnectorCatalogTable.sink(sink, true);

    tableEnv.getCatalog(tableEnv.getCurrentCatalog()).get().createTable(
            ObjectPath.fromString(tableSinkPath),
            connectorCatalogTableSink, false);

    table.insertInto("PravegaSink");
    env.execute();

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

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

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

    // select some sample data from the Pravega-backed table, as a view
    Table view = tableEnv.sqlQuery("SELECT * FROM samples WHERE category IN ('A','B')");

    // convert the view to a dataset and collect the results for comparison purposes
    List<SampleRecord> results = tableEnv.toDataSet(view, SampleRecord.class).collect();
    Assert.assertEquals(new HashSet<>(SAMPLES), new HashSet<>(results));
}
 

@Test
public void testGenericTable() throws Exception {
	ExecutionEnvironment execEnv = ExecutionEnvironment.createLocalEnvironment(1);
	BatchTableEnvironment tableEnv = BatchTableEnvironment.create(execEnv);

	tableEnv.registerCatalog("myhive", hiveCatalog);

	TableSchema schema = TableSchema.builder()
		.field("name", DataTypes.STRING())
		.field("age", DataTypes.INT())
		.build();

	FormatDescriptor format = new OldCsv()
		.field("name", Types.STRING())
		.field("age", Types.INT());

	CatalogTable source =
		new CatalogTableBuilder(
			new FileSystem().path(this.getClass().getResource("/csv/test.csv").getPath()),
			schema)
		.withFormat(format)
		.inAppendMode()
		.withComment("Comment.")
		.build();

	Path p = Paths.get(tempFolder.newFolder().getAbsolutePath(), "test.csv");

	CatalogTable sink =
		new CatalogTableBuilder(
			new FileSystem().path(p.toAbsolutePath().toString()),
			schema)
			.withFormat(format)
			.inAppendMode()
			.withComment("Comment.")
			.build();

	hiveCatalog.createTable(
		new ObjectPath(HiveCatalog.DEFAULT_DB, sourceTableName),
		source,
		false
	);

	hiveCatalog.createTable(
		new ObjectPath(HiveCatalog.DEFAULT_DB, sinkTableName),
		sink,
		false
	);

	Table t = tableEnv.sqlQuery(
		String.format("select * from myhive.`default`.%s", sourceTableName));

	List<Row> result = tableEnv.toDataSet(t, Row.class).collect();

	// assert query result
	assertEquals(
		Arrays.asList(
			Row.of("1", 1),
			Row.of("2", 2),
			Row.of("3", 3)),
		result
	);

	tableEnv.sqlUpdate(
		String.format("insert into myhive.`default`.%s select * from myhive.`default`.%s",
			sinkTableName,
			sourceTableName));
	tableEnv.execute("myjob");

	// assert written result
	File resultFile = new File(p.toAbsolutePath().toString());
	BufferedReader reader = new BufferedReader(new FileReader(resultFile));
	String readLine;
	for (int i = 0; i < 3; i++) {
		readLine = reader.readLine();
		assertEquals(String.format("%d,%d", i + 1, i + 1), readLine);
	}

	// No more line
	assertNull(reader.readLine());
}
 

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