Java BatchTableEnvironment.sqlQuery方法代码示例

import org.apache.flink.table.api.java.BatchTableEnvironment; //导入方法依赖的package包/类
@Test
public void testTableSourceFullScan() throws Exception {

	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	env.setParallelism(4);
	BatchTableEnvironment tableEnv = TableEnvironment.getTableEnvironment(env, new TableConfig());
	HBaseTableSource hbaseTable = new HBaseTableSource(getConf(), TEST_TABLE);
	hbaseTable.addColumn(FAMILY1, F1COL1, Integer.class);
	hbaseTable.addColumn(FAMILY2, F2COL1, String.class);
	hbaseTable.addColumn(FAMILY2, F2COL2, Long.class);
	hbaseTable.addColumn(FAMILY3, F3COL1, Double.class);
	hbaseTable.addColumn(FAMILY3, F3COL2, Boolean.class);
	hbaseTable.addColumn(FAMILY3, F3COL3, String.class);
	tableEnv.registerTableSource("hTable", hbaseTable);

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

	String expected =
		"10,Hello-1,100,1.01,false,Welt-1\n" +
		"20,Hello-2,200,2.02,true,Welt-2\n" +
		"30,Hello-3,300,3.03,false,Welt-3\n" +
		"40,null,400,4.04,true,Welt-4\n" +
		"50,Hello-5,500,5.05,false,Welt-5\n" +
		"60,Hello-6,600,6.06,true,Welt-6\n" +
		"70,Hello-7,700,7.07,false,Welt-7\n" +
		"80,null,800,8.08,true,Welt-8\n";

	TestBaseUtils.compareResultAsText(results, expected);
}
 

import org.apache.flink.table.api.java.BatchTableEnvironment; //导入方法依赖的package包/类
@Test
public void testBatchTableSourceSQL() throws Exception {
	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment tableEnv = TableEnvironment.getTableEnvironment(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);
}
 

import org.apache.flink.table.api.java.BatchTableEnvironment; //导入方法依赖的package包/类
@Test
public void testValues() throws Exception {
	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment tableEnv = TableEnvironment.getTableEnvironment(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);
}
 

import org.apache.flink.table.api.java.BatchTableEnvironment; //导入方法依赖的package包/类
@Test
public void testSelectFromTable() throws Exception {
	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment tableEnv = TableEnvironment.getTableEnvironment(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);
}
 

import org.apache.flink.table.api.java.BatchTableEnvironment; //导入方法依赖的package包/类
@Test
public void testFilterFromDataSet() throws Exception {
	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment tableEnv = TableEnvironment.getTableEnvironment(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);
}
 

import org.apache.flink.table.api.java.BatchTableEnvironment; //导入方法依赖的package包/类
@Test
public void testAggregation() throws Exception {
	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment tableEnv = TableEnvironment.getTableEnvironment(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);
}
 

import org.apache.flink.table.api.java.BatchTableEnvironment; //导入方法依赖的package包/类
@Test
public void testJoin() throws Exception {
	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment tableEnv = TableEnvironment.getTableEnvironment(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);
}
 

import org.apache.flink.table.api.java.BatchTableEnvironment; //导入方法依赖的package包/类
@Test
public void testMap() throws Exception {
	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment tableEnv = TableEnvironment.getTableEnvironment(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);
}
 

import org.apache.flink.table.api.java.BatchTableEnvironment; //导入方法依赖的package包/类
public static void main(String[] args) throws Exception {

		// set up execution environment
		ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
		BatchTableEnvironment tEnv = TableEnvironment.getTableEnvironment(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();
	}
 

import org.apache.flink.table.api.java.BatchTableEnvironment; //导入方法依赖的package包/类
@Test
public void testTableSourceProjection() throws Exception {

	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	env.setParallelism(4);
	BatchTableEnvironment tableEnv = TableEnvironment.getTableEnvironment(env, new TableConfig());
	HBaseTableSource hbaseTable = new HBaseTableSource(getConf(), TEST_TABLE);
	hbaseTable.addColumn(FAMILY1, F1COL1, Integer.class);
	hbaseTable.addColumn(FAMILY2, F2COL1, String.class);
	hbaseTable.addColumn(FAMILY2, F2COL2, Long.class);
	hbaseTable.addColumn(FAMILY3, F3COL1, Double.class);
	hbaseTable.addColumn(FAMILY3, F3COL2, Boolean.class);
	hbaseTable.addColumn(FAMILY3, F3COL3, String.class);
	tableEnv.registerTableSource("hTable", hbaseTable);

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

	String expected =
		"10,1.01,false,Welt-1\n" +
		"20,2.02,true,Welt-2\n" +
		"30,3.03,false,Welt-3\n" +
		"40,4.04,true,Welt-4\n" +
		"50,5.05,false,Welt-5\n" +
		"60,6.06,true,Welt-6\n" +
		"70,7.07,false,Welt-7\n" +
		"80,8.08,true,Welt-8\n";

	TestBaseUtils.compareResultAsText(results, expected);
}
 

import org.apache.flink.table.api.java.BatchTableEnvironment; //导入方法依赖的package包/类
@Test
public void testTableSourceFieldOrder() throws Exception {

	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	env.setParallelism(4);
	BatchTableEnvironment tableEnv = TableEnvironment.getTableEnvironment(env, new TableConfig());
	HBaseTableSource hbaseTable = new HBaseTableSource(getConf(), TEST_TABLE);
	// shuffle order of column registration
	hbaseTable.addColumn(FAMILY2, F2COL1, String.class);
	hbaseTable.addColumn(FAMILY3, F3COL1, Double.class);
	hbaseTable.addColumn(FAMILY1, F1COL1, Integer.class);
	hbaseTable.addColumn(FAMILY2, F2COL2, Long.class);
	hbaseTable.addColumn(FAMILY3, F3COL2, Boolean.class);
	hbaseTable.addColumn(FAMILY3, F3COL3, String.class);
	tableEnv.registerTableSource("hTable", hbaseTable);

	Table result = tableEnv.sqlQuery(
		"SELECT * FROM hTable AS h"
	);
	DataSet<Row> resultSet = tableEnv.toDataSet(result, Row.class);
	List<Row> results = resultSet.collect();

	String expected =
		"Hello-1,100,1.01,false,Welt-1,10\n" +
		"Hello-2,200,2.02,true,Welt-2,20\n" +
		"Hello-3,300,3.03,false,Welt-3,30\n" +
		"null,400,4.04,true,Welt-4,40\n" +
		"Hello-5,500,5.05,false,Welt-5,50\n" +
		"Hello-6,600,6.06,true,Welt-6,60\n" +
		"Hello-7,700,7.07,false,Welt-7,70\n" +
		"null,800,8.08,true,Welt-8,80\n";

	TestBaseUtils.compareResultAsText(results, expected);
}
 

import org.apache.flink.table.api.java.BatchTableEnvironment; //导入方法依赖的package包/类
@Test
public void testTableSourceReadAsByteArray() throws Exception {

	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	env.setParallelism(4);
	BatchTableEnvironment tableEnv = TableEnvironment.getTableEnvironment(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);
}
 

import org.apache.flink.table.api.java.BatchTableEnvironment; //导入方法依赖的package包/类
@Test
public void testFullScan() throws Exception {

	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment tEnv = TableEnvironment.getTableEnvironment(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());
}
 

import org.apache.flink.table.api.java.BatchTableEnvironment; //导入方法依赖的package包/类
@Test
public void testScanWithProjectionAndFilter() throws Exception {

	ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
	BatchTableEnvironment tEnv = TableEnvironment.getTableEnvironment(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());
}