You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@flink.apache.org by tw...@apache.org on 2017/03/14 08:17:41 UTC
flink git commit: [FLINK-5441] [table] Directly allow SQL queries on
a Table
Repository: flink
Updated Branches:
refs/heads/master ffb8d0518 -> f333723bc
[FLINK-5441] [table] Directly allow SQL queries on a Table
This closes #3107.
Project: http://git-wip-us.apache.org/repos/asf/flink/repo
Commit: http://git-wip-us.apache.org/repos/asf/flink/commit/f333723b
Tree: http://git-wip-us.apache.org/repos/asf/flink/tree/f333723b
Diff: http://git-wip-us.apache.org/repos/asf/flink/diff/f333723b
Branch: refs/heads/master
Commit: f333723bcdaaa1cdc99dce16b00151d1c5365869
Parents: ffb8d05
Author: Jark Wu <wu...@alibaba-inc.com>
Authored: Thu Jan 12 22:26:52 2017 +0800
Committer: twalthr <tw...@apache.org>
Committed: Tue Mar 14 09:17:18 2017 +0100
----------------------------------------------------------------------
docs/dev/table_api.md | 38 ++++-
.../flink/table/api/TableEnvironment.scala | 12 +-
.../org/apache/flink/table/api/table.scala | 14 ++
.../flink/table/TableEnvironmentTest.scala | 159 ++++++++++++++-----
.../flink/table/utils/TableTestBase.scala | 3 +-
5 files changed, 174 insertions(+), 52 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/flink/blob/f333723b/docs/dev/table_api.md
----------------------------------------------------------------------
diff --git a/docs/dev/table_api.md b/docs/dev/table_api.md
index ddcc9e9..03b916c 100644
--- a/docs/dev/table_api.md
+++ b/docs/dev/table_api.md
@@ -1284,7 +1284,7 @@ SQL
----
SQL queries are specified using the `sql()` method of the `TableEnvironment`. The method returns the result of the SQL query as a `Table` which can be converted into a `DataSet` or `DataStream`, used in subsequent Table API queries, or written to a `TableSink` (see [Writing Tables to External Sinks](#writing-tables-to-external-sinks)). SQL and Table API queries can seamlessly mixed and are holistically optimized and translated into a single DataStream or DataSet program.
-A `Table`, `DataSet`, `DataStream`, or external `TableSource` must be registered in the `TableEnvironment` in order to be accessible by a SQL query (see [Registering Tables](#registering-tables)).
+A `Table`, `DataSet`, `DataStream`, or external `TableSource` must be registered in the `TableEnvironment` in order to be accessible by a SQL query (see [Registering Tables](#registering-tables)). For convenience `Table.toString()` will automatically register an unique table name under the `Table`'s `TableEnvironment` and return the table name. So it allows to call SQL directly on tables in a string concatenation (see examples below).
*Note: Flink's SQL support is not feature complete, yet. Queries that include unsupported SQL features will cause a `TableException`. The limitations of SQL on batch and streaming tables are listed in the following sections.*
@@ -1298,10 +1298,17 @@ BatchTableEnvironment tableEnv = TableEnvironment.getTableEnvironment(env);
// read a DataSet from an external source
DataSet<Tuple3<Long, String, Integer>> ds = env.readCsvFile(...);
+
+// call SQL on unregistered tables
+Table table = tableEnv.toTable(ds, "user, product, amount");
+Table result = tableEnv.sql(
+ "SELECT SUM(amount) FROM " + table + " WHERE product LIKE '%Rubber%'");
+
+// call SQL on registered tables
// register the DataSet as table "Orders"
tableEnv.registerDataSet("Orders", ds, "user, product, amount");
// run a SQL query on the Table and retrieve the result as a new Table
-Table result = tableEnv.sql(
+Table result2 = tableEnv.sql(
"SELECT SUM(amount) FROM Orders WHERE product LIKE '%Rubber%'");
{% endhighlight %}
</div>
@@ -1313,10 +1320,17 @@ val tableEnv = TableEnvironment.getTableEnvironment(env)
// read a DataSet from an external source
val ds: DataSet[(Long, String, Integer)] = env.readCsvFile(...)
+
+// call SQL on unregistered tables
+val table = ds.toTable(tableEnv, 'user, 'product, 'amount)
+val result = tableEnv.sql(
+ s"SELECT SUM(amount) FROM $table WHERE product LIKE '%Rubber%'")
+
+// call SQL on registered tables
// register the DataSet under the name "Orders"
tableEnv.registerDataSet("Orders", ds, 'user, 'product, 'amount)
// run a SQL query on the Table and retrieve the result as a new Table
-val result = tableEnv.sql(
+val result2 = tableEnv.sql(
"SELECT SUM(amount) FROM Orders WHERE product LIKE '%Rubber%'")
{% endhighlight %}
</div>
@@ -1347,10 +1361,17 @@ StreamTableEnvironment tableEnv = TableEnvironment.getTableEnvironment(env);
// ingest a DataStream from an external source
DataStream<Tuple3<Long, String, Integer>> ds = env.addSource(...);
+
+// call SQL on unregistered tables
+Table table = tableEnv.toTable(ds, "user, product, amount");
+Table result = tableEnv.sql(
+ "SELECT SUM(amount) FROM " + table + " WHERE product LIKE '%Rubber%'");
+
+// call SQL on registered tables
// register the DataStream as table "Orders"
tableEnv.registerDataStream("Orders", ds, "user, product, amount");
// run a SQL query on the Table and retrieve the result as a new Table
-Table result = tableEnv.sql(
+Table result2 = tableEnv.sql(
"SELECT product, amount FROM Orders WHERE product LIKE '%Rubber%'");
{% endhighlight %}
</div>
@@ -1362,10 +1383,17 @@ val tableEnv = TableEnvironment.getTableEnvironment(env)
// read a DataStream from an external source
val ds: DataStream[(Long, String, Integer)] = env.addSource(...)
+
+// call SQL on unregistered tables
+val table = ds.toTable(tableEnv, 'user, 'product, 'amount)
+val result = tableEnv.sql(
+ s"SELECT SUM(amount) FROM $table WHERE product LIKE '%Rubber%'")
+
+// call SQL on registered tables
// register the DataStream under the name "Orders"
tableEnv.registerDataStream("Orders", ds, 'user, 'product, 'amount)
// run a SQL query on the Table and retrieve the result as a new Table
-val result = tableEnv.sql(
+val result2 = tableEnv.sql(
"SELECT product, amount FROM Orders WHERE product LIKE '%Rubber%'")
{% endhighlight %}
</div>
http://git-wip-us.apache.org/repos/asf/flink/blob/f333723b/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/api/TableEnvironment.scala
----------------------------------------------------------------------
diff --git a/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/api/TableEnvironment.scala b/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/api/TableEnvironment.scala
index 4a36320..1dda3a8 100644
--- a/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/api/TableEnvironment.scala
+++ b/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/api/TableEnvironment.scala
@@ -97,7 +97,7 @@ abstract class TableEnvironment(val config: TableConfig) {
private lazy val typeFactory: FlinkTypeFactory = relBuilder.getTypeFactory
// a counter for unique attribute names
- private val attrNameCntr: AtomicInteger = new AtomicInteger(0)
+ private[flink] val attrNameCntr: AtomicInteger = new AtomicInteger(0)
/** Returns the table config to define the runtime behavior of the Table API. */
def getConfig = config
@@ -368,7 +368,15 @@ abstract class TableEnvironment(val config: TableConfig) {
/**
* Evaluates a SQL query on registered tables and retrieves the result as a [[Table]].
*
- * All tables referenced by the query must be registered in the TableEnvironment.
+ * All tables referenced by the query must be registered in the TableEnvironment. But
+ * [[Table.toString]] will automatically register an unique table name and return the
+ * table name. So it allows to call SQL directly on tables like this:
+ *
+ * {{{
+ * val table: Table = ...
+ * // the table is not registered to the table environment
+ * tEnv.sql(s"SELECT * FROM $table")
+ * }}}
*
* @param query The SQL query to evaluate.
* @return The result of the query as Table.
http://git-wip-us.apache.org/repos/asf/flink/blob/f333723b/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/api/table.scala
----------------------------------------------------------------------
diff --git a/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/api/table.scala b/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/api/table.scala
index a70b41d..769e940 100644
--- a/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/api/table.scala
+++ b/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/api/table.scala
@@ -809,6 +809,20 @@ class Table(
}
new WindowedTable(this, window)
}
+
+ var tableName: String = _
+
+ /**
+ * Registers an unique table name under the table environment
+ * and return the registered table name.
+ */
+ override def toString: String = {
+ if (tableName == null) {
+ tableName = "UnnamedTable$" + tableEnv.attrNameCntr.getAndIncrement()
+ tableEnv.registerTable(tableName, this)
+ }
+ tableName
+ }
}
/**
http://git-wip-us.apache.org/repos/asf/flink/blob/f333723b/flink-libraries/flink-table/src/test/scala/org/apache/flink/table/TableEnvironmentTest.scala
----------------------------------------------------------------------
diff --git a/flink-libraries/flink-table/src/test/scala/org/apache/flink/table/TableEnvironmentTest.scala b/flink-libraries/flink-table/src/test/scala/org/apache/flink/table/TableEnvironmentTest.scala
index 8ce27e8..05c2a49 100644
--- a/flink-libraries/flink-table/src/test/scala/org/apache/flink/table/TableEnvironmentTest.scala
+++ b/flink-libraries/flink-table/src/test/scala/org/apache/flink/table/TableEnvironmentTest.scala
@@ -20,6 +20,7 @@ package org.apache.flink.table
import org.apache.calcite.tools.RuleSet
import org.apache.flink.api.scala._
+import org.apache.flink.table.api.scala._
import org.apache.flink.api.common.typeinfo.BasicTypeInfo._
import org.apache.flink.api.common.typeinfo.TypeInformation
import org.apache.flink.api.java.typeutils.{TupleTypeInfo, TypeExtractor}
@@ -27,10 +28,12 @@ import org.apache.flink.table.api.{Table, TableConfig, TableEnvironment, TableEx
import org.apache.flink.table.expressions.{Alias, UnresolvedFieldReference}
import org.apache.flink.table.sinks.TableSink
import org.apache.flink.table.sources.TableSource
+import org.apache.flink.table.utils.TableTestBase
+import org.apache.flink.table.utils.TableTestUtil.{batchTableNode, term, unaryNode, binaryNode, streamTableNode}
import org.junit.Test
import org.junit.Assert.assertEquals
-class TableEnvironmentTest {
+class TableEnvironmentTest extends TableTestBase {
val tEnv = new MockTableEnvironment
@@ -82,9 +85,9 @@ class TableEnvironmentTest {
val fieldInfo = tEnv.getFieldInfo(
tupleType,
Array(
- new UnresolvedFieldReference("name1"),
- new UnresolvedFieldReference("name2"),
- new UnresolvedFieldReference("name3")
+ UnresolvedFieldReference("name1"),
+ UnresolvedFieldReference("name2"),
+ UnresolvedFieldReference("name3")
))
fieldInfo._1.zip(Array("name1", "name2", "name3")).foreach(x => assertEquals(x._2, x._1))
@@ -96,9 +99,9 @@ class TableEnvironmentTest {
val fieldInfo = tEnv.getFieldInfo(
caseClassType,
Array(
- new UnresolvedFieldReference("name1"),
- new UnresolvedFieldReference("name2"),
- new UnresolvedFieldReference("name3")
+ UnresolvedFieldReference("name1"),
+ UnresolvedFieldReference("name2"),
+ UnresolvedFieldReference("name3")
))
fieldInfo._1.zip(Array("name1", "name2", "name3")).foreach(x => assertEquals(x._2, x._1))
@@ -110,9 +113,9 @@ class TableEnvironmentTest {
tEnv.getFieldInfo(
pojoType,
Array(
- new UnresolvedFieldReference("name1"),
- new UnresolvedFieldReference("name2"),
- new UnresolvedFieldReference("name3")
+ UnresolvedFieldReference("name1"),
+ UnresolvedFieldReference("name2"),
+ UnresolvedFieldReference("name3")
))
}
@@ -121,9 +124,9 @@ class TableEnvironmentTest {
val fieldInfo = tEnv.getFieldInfo(
pojoType,
Array(
- new UnresolvedFieldReference("pf3"),
- new UnresolvedFieldReference("pf1"),
- new UnresolvedFieldReference("pf2")
+ UnresolvedFieldReference("pf3"),
+ UnresolvedFieldReference("pf1"),
+ UnresolvedFieldReference("pf2")
))
fieldInfo._1.zip(Array("pf3", "pf1", "pf2")).foreach(x => assertEquals(x._2, x._1))
@@ -134,7 +137,7 @@ class TableEnvironmentTest {
def testGetFieldInfoAtomicName1(): Unit = {
val fieldInfo = tEnv.getFieldInfo(
atomicType,
- Array(new UnresolvedFieldReference("name"))
+ Array(UnresolvedFieldReference("name"))
)
fieldInfo._1.zip(Array("name")).foreach(x => assertEquals(x._2, x._1))
@@ -146,8 +149,8 @@ class TableEnvironmentTest {
tEnv.getFieldInfo(
atomicType,
Array(
- new UnresolvedFieldReference("name1"),
- new UnresolvedFieldReference("name2")
+ UnresolvedFieldReference("name1"),
+ UnresolvedFieldReference("name2")
))
}
@@ -156,9 +159,9 @@ class TableEnvironmentTest {
val fieldInfo = tEnv.getFieldInfo(
tupleType,
Array(
- new Alias(UnresolvedFieldReference("f0"), "name1"),
- new Alias(UnresolvedFieldReference("f1"), "name2"),
- new Alias(UnresolvedFieldReference("f2"), "name3")
+ Alias(UnresolvedFieldReference("f0"), "name1"),
+ Alias(UnresolvedFieldReference("f1"), "name2"),
+ Alias(UnresolvedFieldReference("f2"), "name3")
))
fieldInfo._1.zip(Array("name1", "name2", "name3")).foreach(x => assertEquals(x._2, x._1))
@@ -170,9 +173,9 @@ class TableEnvironmentTest {
val fieldInfo = tEnv.getFieldInfo(
tupleType,
Array(
- new Alias(UnresolvedFieldReference("f2"), "name1"),
- new Alias(UnresolvedFieldReference("f0"), "name2"),
- new Alias(UnresolvedFieldReference("f1"), "name3")
+ Alias(UnresolvedFieldReference("f2"), "name1"),
+ Alias(UnresolvedFieldReference("f0"), "name2"),
+ Alias(UnresolvedFieldReference("f1"), "name3")
))
fieldInfo._1.zip(Array("name1", "name2", "name3")).foreach(x => assertEquals(x._2, x._1))
@@ -184,9 +187,9 @@ class TableEnvironmentTest {
tEnv.getFieldInfo(
tupleType,
Array(
- new Alias(UnresolvedFieldReference("xxx"), "name1"),
- new Alias(UnresolvedFieldReference("yyy"), "name2"),
- new Alias(UnresolvedFieldReference("zzz"), "name3")
+ Alias(UnresolvedFieldReference("xxx"), "name1"),
+ Alias(UnresolvedFieldReference("yyy"), "name2"),
+ Alias(UnresolvedFieldReference("zzz"), "name3")
))
}
@@ -195,9 +198,9 @@ class TableEnvironmentTest {
val fieldInfo = tEnv.getFieldInfo(
caseClassType,
Array(
- new Alias(new UnresolvedFieldReference("cf1"), "name1"),
- new Alias(new UnresolvedFieldReference("cf2"), "name2"),
- new Alias(new UnresolvedFieldReference("cf3"), "name3")
+ Alias(UnresolvedFieldReference("cf1"), "name1"),
+ Alias(UnresolvedFieldReference("cf2"), "name2"),
+ Alias(UnresolvedFieldReference("cf3"), "name3")
))
fieldInfo._1.zip(Array("name1", "name2", "name3")).foreach(x => assertEquals(x._2, x._1))
@@ -209,9 +212,9 @@ class TableEnvironmentTest {
val fieldInfo = tEnv.getFieldInfo(
caseClassType,
Array(
- new Alias(new UnresolvedFieldReference("cf3"), "name1"),
- new Alias(new UnresolvedFieldReference("cf1"), "name2"),
- new Alias(new UnresolvedFieldReference("cf2"), "name3")
+ Alias(UnresolvedFieldReference("cf3"), "name1"),
+ Alias(UnresolvedFieldReference("cf1"), "name2"),
+ Alias(UnresolvedFieldReference("cf2"), "name3")
))
fieldInfo._1.zip(Array("name1", "name2", "name3")).foreach(x => assertEquals(x._2, x._1))
@@ -223,9 +226,9 @@ class TableEnvironmentTest {
tEnv.getFieldInfo(
caseClassType,
Array(
- new Alias(new UnresolvedFieldReference("xxx"), "name1"),
- new Alias(new UnresolvedFieldReference("yyy"), "name2"),
- new Alias(new UnresolvedFieldReference("zzz"), "name3")
+ Alias(UnresolvedFieldReference("xxx"), "name1"),
+ Alias(UnresolvedFieldReference("yyy"), "name2"),
+ Alias(UnresolvedFieldReference("zzz"), "name3")
))
}
@@ -234,9 +237,9 @@ class TableEnvironmentTest {
val fieldInfo = tEnv.getFieldInfo(
pojoType,
Array(
- new Alias(new UnresolvedFieldReference("pf1"), "name1"),
- new Alias(new UnresolvedFieldReference("pf2"), "name2"),
- new Alias(new UnresolvedFieldReference("pf3"), "name3")
+ Alias(UnresolvedFieldReference("pf1"), "name1"),
+ Alias(UnresolvedFieldReference("pf2"), "name2"),
+ Alias(UnresolvedFieldReference("pf3"), "name3")
))
fieldInfo._1.zip(Array("name1", "name2", "name3")).foreach(x => assertEquals(x._2, x._1))
@@ -248,9 +251,9 @@ class TableEnvironmentTest {
val fieldInfo = tEnv.getFieldInfo(
pojoType,
Array(
- new Alias(new UnresolvedFieldReference("pf3"), "name1"),
- new Alias(new UnresolvedFieldReference("pf1"), "name2"),
- new Alias(new UnresolvedFieldReference("pf2"), "name3")
+ Alias(UnresolvedFieldReference("pf3"), "name1"),
+ Alias(UnresolvedFieldReference("pf1"), "name2"),
+ Alias(UnresolvedFieldReference("pf2"), "name3")
))
fieldInfo._1.zip(Array("name1", "name2", "name3")).foreach(x => assertEquals(x._2, x._1))
@@ -262,9 +265,9 @@ class TableEnvironmentTest {
tEnv.getFieldInfo(
pojoType,
Array(
- new Alias(new UnresolvedFieldReference("xxx"), "name1"),
- new Alias(new UnresolvedFieldReference("yyy"), "name2"),
- new Alias(new UnresolvedFieldReference("zzz"), "name3")
+ Alias(UnresolvedFieldReference("xxx"), "name1"),
+ Alias(UnresolvedFieldReference("yyy"), "name2"),
+ Alias( UnresolvedFieldReference("zzz"), "name3")
))
}
@@ -273,10 +276,78 @@ class TableEnvironmentTest {
tEnv.getFieldInfo(
atomicType,
Array(
- new Alias(new UnresolvedFieldReference("name1"), "name2")
+ Alias(UnresolvedFieldReference("name1"), "name2")
))
}
+ @Test
+ def testSqlWithoutRegisteringForBatchTables(): Unit = {
+ val util = batchTestUtil()
+ val table = util.addTable[(Long, Int, String)]("tableName", 'a, 'b, 'c)
+ util.tEnv.unregisterTable("tableName")
+
+ val sqlTable = util.tEnv.sql(s"SELECT a, b, c FROM $table WHERE b > 12")
+
+ val expected = unaryNode(
+ "DataSetCalc",
+ batchTableNode(0),
+ term("select", "a, b, c"),
+ term("where", ">(b, 12)"))
+
+ util.verifyTable(sqlTable, expected)
+
+ val table2 = util.addTable[(Long, Int, String)]('d, 'e, 'f)
+
+ val sqlTable2 = util.tEnv.sql(s"SELECT d, e, f FROM $table, $table2 WHERE c = d")
+
+ val join = unaryNode(
+ "DataSetJoin",
+ binaryNode(
+ "DataSetCalc",
+ batchTableNode(0),
+ batchTableNode(1),
+ term("select", "c")),
+ term("where", "=(c, d)"),
+ term("join", "c, d, e, f"),
+ term("joinType", "InnerJoin"))
+
+ val expected2 = unaryNode(
+ "DataSetCalc",
+ join,
+ term("select", "d, e, f"))
+
+ util.verifyTable(sqlTable2, expected2)
+ }
+
+ @Test
+ def testSqlWithoutRegisteringForStreamTables(): Unit = {
+ val util = streamTestUtil()
+ val table = util.addTable[(Long, Int, String)]("tableName", 'a, 'b, 'c)
+ util.tEnv.unregisterTable("tableName")
+
+ val sqlTable = util.tEnv.sql(s"SELECT a, b, c FROM $table WHERE b > 12")
+
+ val expected = unaryNode(
+ "DataStreamCalc",
+ streamTableNode(0),
+ term("select", "a, b, c"),
+ term("where", ">(b, 12)"))
+
+ util.verifyTable(sqlTable, expected)
+
+ val table2 = util.addTable[(Long, Int, String)]('d, 'e, 'f)
+
+ val sqlTable2 = util.tEnv.sql(s"SELECT d, e, f FROM $table2 UNION SELECT a, b, c FROM $table")
+
+ val expected2 = binaryNode(
+ "DataStreamUnion",
+ streamTableNode(1),
+ streamTableNode(0),
+ term("union", "d, e, f"))
+
+ util.verifyTable(sqlTable2, expected2)
+ }
+
}
class MockTableEnvironment extends TableEnvironment(new TableConfig) {
http://git-wip-us.apache.org/repos/asf/flink/blob/f333723b/flink-libraries/flink-table/src/test/scala/org/apache/flink/table/utils/TableTestBase.scala
----------------------------------------------------------------------
diff --git a/flink-libraries/flink-table/src/test/scala/org/apache/flink/table/utils/TableTestBase.scala b/flink-libraries/flink-table/src/test/scala/org/apache/flink/table/utils/TableTestBase.scala
index ff2c5d3..15ef2b1 100644
--- a/flink-libraries/flink-table/src/test/scala/org/apache/flink/table/utils/TableTestBase.scala
+++ b/flink-libraries/flink-table/src/test/scala/org/apache/flink/table/utils/TableTestBase.scala
@@ -58,7 +58,8 @@ abstract class TableTestUtil {
private var counter = 0
def addTable[T: TypeInformation](fields: Expression*): Table = {
- addTable[T](s"Table${counter += 1}", fields: _*)
+ counter += 1
+ addTable[T](s"Table$counter", fields: _*)
}
def addTable[T: TypeInformation](name: String, fields: Expression*): Table