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