Repository: flink Updated Branches: refs/heads/tableDocs [created] e5f24713a
http://git-wip-us.apache.org/repos/asf/flink/blob/e5f24713/docs/dev/table_api.md ---------------------------------------------------------------------- diff --git a/docs/dev/table_api.md b/docs/dev/table_api.md deleted file mode 100644 index 6a5ceee..0000000 --- a/docs/dev/table_api.md +++ /dev/null @@ -1,6015 +0,0 @@ ---- -title: "Table and SQL" -is_beta: true -nav-parent_id: libs -nav-pos: 0 ---- -<!-- -Licensed to the Apache Software Foundation (ASF) under one -or more contributor license agreements. See the NOTICE file -distributed with this work for additional information -regarding copyright ownership. The ASF licenses this file -to you under the Apache License, Version 2.0 (the -"License"); you may not use this file except in compliance -with the License. You may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 - -Unless required by applicable law or agreed to in writing, -software distributed under the License is distributed on an -"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY -KIND, either express or implied. See the License for the -specific language governing permissions and limitations -under the License. ---> - -**Table API and SQL are experimental features** - -The Table API is a SQL-like expression language for relational stream and batch processing that can be easily embedded in Flink's DataSet and DataStream APIs (Java and Scala). -The Table API and SQL interface operate on a relational `Table` abstraction, which can be created from external data sources, or existing DataSets and DataStreams. With the Table API, you can apply relational operators such as selection, aggregation, and joins on `Table`s. - -`Table`s can also be queried with regular SQL, as long as they are registered (see [Registering Tables](#registering-tables)). The Table API and SQL offer equivalent functionality and can be mixed in the same program. When a `Table` is converted back into a `DataSet` or `DataStream`, the logical plan, which was defined by relational operators and SQL queries, is optimized using [Apache Calcite](https://calcite.apache.org/) and transformed into a `DataSet` or `DataStream` program. - -* This will be replaced by the TOC -{:toc} - -Using the Table API and SQL ----------------------------- - -The Table API and SQL are part of the *flink-table* Maven project. -The following dependency must be added to your project in order to use the Table API and SQL: - -{% highlight xml %} -<dependency> - <groupId>org.apache.flink</groupId> - <artifactId>flink-table{{ site.scala_version_suffix }}</artifactId> - <version>{{site.version }}</version> -</dependency> -{% endhighlight %} - -*Note: The Table API is currently not part of the binary distribution. See linking with it for cluster execution [here]({{ site.baseurl }}/dev/linking.html).* - - -Registering Tables --------------------------------- - -`TableEnvironment`s have an internal table catalog to which tables can be registered with a unique name. After registration, a table can be accessed from the `TableEnvironment` by its name. - -*Note: `DataSet`s or `DataStream`s can be directly converted into `Table`s without registering them in the `TableEnvironment`.* - -### Register a DataSet - -A `DataSet` is registered as a `Table` in a `BatchTableEnvironment` as follows: - -<div class="codetabs" markdown="1"> -<div data-lang="java" markdown="1"> -{% highlight java %} -ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment(); -BatchTableEnvironment tableEnv = TableEnvironment.getTableEnvironment(env); - -// register the DataSet cust as table "Customers" with fields derived from the dataset -tableEnv.registerDataSet("Customers", cust); - -// register the DataSet ord as table "Orders" with fields user, product, and amount -tableEnv.registerDataSet("Orders", ord, "user, product, amount"); -{% endhighlight %} -</div> - -<div data-lang="scala" markdown="1"> -{% highlight scala %} -val env = ExecutionEnvironment.getExecutionEnvironment -val tableEnv = TableEnvironment.getTableEnvironment(env) - -// register the DataSet cust as table "Customers" with fields derived from the dataset -tableEnv.registerDataSet("Customers", cust) - -// register the DataSet ord as table "Orders" with fields user, product, and amount -tableEnv.registerDataSet("Orders", ord, 'user, 'product, 'amount) -{% endhighlight %} -</div> -</div> - -*Note: The name of a `DataSet` `Table` must not match the `^_DataSetTable_[0-9]+` pattern which is reserved for internal use only.* - -### Register a DataStream - -A `DataStream` is registered as a `Table` in a `StreamTableEnvironment` as follows: - -<div class="codetabs" markdown="1"> -<div data-lang="java" markdown="1"> -{% highlight java %} -StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment(); -StreamTableEnvironment tableEnv = TableEnvironment.getTableEnvironment(env); - -// register the DataStream cust as table "Customers" with fields derived from the datastream -tableEnv.registerDataStream("Customers", cust); - -// register the DataStream ord as table "Orders" with fields user, product, and amount -tableEnv.registerDataStream("Orders", ord, "user, product, amount"); -{% endhighlight %} -</div> - -<div data-lang="scala" markdown="1"> -{% highlight scala %} -val env = StreamExecutionEnvironment.getExecutionEnvironment -val tableEnv = TableEnvironment.getTableEnvironment(env) - -// register the DataStream cust as table "Customers" with fields derived from the datastream -tableEnv.registerDataStream("Customers", cust) - -// register the DataStream ord as table "Orders" with fields user, product, and amount -tableEnv.registerDataStream("Orders", ord, 'user, 'product, 'amount) -{% endhighlight %} -</div> -</div> - -*Note: The name of a `DataStream` `Table` must not match the `^_DataStreamTable_[0-9]+` pattern which is reserved for internal use only.* - -### Register a Table - -A `Table` that originates from a Table API operation or a SQL query is registered in a `TableEnvironment` as follows: - -<div class="codetabs" markdown="1"> -<div data-lang="java" markdown="1"> -{% highlight java %} -// works for StreamExecutionEnvironment identically -ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment(); -BatchTableEnvironment tableEnv = TableEnvironment.getTableEnvironment(env); - -// convert a DataSet into a Table -Table custT = tableEnv - .toTable(custDs, "name, zipcode") - .where("zipcode = '12345'") - .select("name"); - -// register the Table custT as table "custNames" -tableEnv.registerTable("custNames", custT); -{% endhighlight %} -</div> - -<div data-lang="scala" markdown="1"> -{% highlight scala %} -// works for StreamExecutionEnvironment identically -val env = ExecutionEnvironment.getExecutionEnvironment -val tableEnv = TableEnvironment.getTableEnvironment(env) - -// convert a DataSet into a Table -val custT = custDs - .toTable(tableEnv, 'name, 'zipcode) - .where('zipcode === "12345") - .select('name) - -// register the Table custT as table "custNames" -tableEnv.registerTable("custNames", custT) -{% endhighlight %} -</div> -</div> - -A registered `Table` that originates from a Table API operation or SQL query is treated similarly as a view as known from relational DBMS, i.e., it can be inlined when optimizing the query. - -### Register an external Table using a TableSource - -An external table is registered in a `TableEnvironment` using a `TableSource` as follows: - -<div class="codetabs" markdown="1"> -<div data-lang="java" markdown="1"> -{% highlight java %} -// works for StreamExecutionEnvironment identically -ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment(); -BatchTableEnvironment tableEnv = TableEnvironment.getTableEnvironment(env); - -TableSource custTS = new CsvTableSource("/path/to/file", ...); - -// register a `TableSource` as external table "Customers" -tableEnv.registerTableSource("Customers", custTS); -{% endhighlight %} -</div> - -<div data-lang="scala" markdown="1"> -{% highlight scala %} -// works for StreamExecutionEnvironment identically -val env = ExecutionEnvironment.getExecutionEnvironment -val tableEnv = TableEnvironment.getTableEnvironment(env) - -val custTS: TableSource = new CsvTableSource("/path/to/file", ...) - -// register a `TableSource` as external table "Customers" -tableEnv.registerTableSource("Customers", custTS) - -{% endhighlight %} -</div> -</div> - -A `TableSource` can provide access to data stored in various storage systems such as databases (MySQL, HBase, ...), file formats (CSV, Apache Parquet, Avro, ORC, ...), or messaging systems (Apache Kafka, RabbitMQ, ...). - -Currently, Flink provides the `CsvTableSource` to read CSV files and various `TableSources` to read JSON or Avro objects from Kafka. -A custom `TableSource` can be defined by implementing the `BatchTableSource` or `StreamTableSource` interface. - -### Available Table Sources - -| **Class name** | **Maven dependency** | **Batch?** | **Streaming?** | **Description** -| `CsvTableSouce` | `flink-table` | Y | Y | A simple source for CSV files. -| `Kafka08JsonTableSource` | `flink-connector-kafka-0.8` | N | Y | A Kafka 0.8 source for JSON data. -| `Kafka08AvroTableSource` | `flink-connector-kafka-0.8` | N | Y | A Kafka 0.8 source for Avro data. -| `Kafka09JsonTableSource` | `flink-connector-kafka-0.9` | N | Y | A Kafka 0.9 source for JSON data. -| `Kafka09AvroTableSource` | `flink-connector-kafka-0.9` | N | Y | A Kafka 0.9 source for Avro data. -| `Kafka010JsonTableSource` | `flink-connector-kafka-0.10` | N | Y | A Kafka 0.10 source for JSON data. -| `Kafka010AvroTableSource` | `flink-connector-kafka-0.10` | N | Y | A Kafka 0.10 source for Avro data. - -All sources that come with the `flink-table` dependency can be directly used by your Table programs. For all other table sources, you have to add the respective dependency in addition to the `flink-table` dependency. - -#### KafkaJsonTableSource - -To use the Kafka JSON source, you have to add the Kafka connector dependency to your project: - - - `flink-connector-kafka-0.8` for Kafka 0.8, - - `flink-connector-kafka-0.9` for Kafka 0.9, or - - `flink-connector-kafka-0.10` for Kafka 0.10, respectively. - -You can then create the source as follows (example for Kafka 0.8): -<div class="codetabs" markdown="1"> -<div data-lang="java" markdown="1"> -{% highlight java %} -// specify JSON field names and types -TypeInformation<Row> typeInfo = Types.ROW( - new String[] { "id", "name", "score" }, - new TypeInformation<?>[] { Types.INT(), Types.STRING(), Types.DOUBLE() } -); - -KafkaJsonTableSource kafkaTableSource = new Kafka08JsonTableSource( - kafkaTopic, - kafkaProperties, - typeInfo); -{% endhighlight %} -</div> - -<div data-lang="scala" markdown="1"> -{% highlight scala %} -// specify JSON field names and types -val typeInfo = Types.ROW( - Array("id", "name", "score"), - Array(Types.INT, Types.STRING, Types.DOUBLE) -) - -val kafkaTableSource = new Kafka08JsonTableSource( - kafkaTopic, - kafkaProperties, - typeInfo) -{% endhighlight %} -</div> -</div> - -By default, a missing JSON field does not fail the source. You can configure this via: - -```java -// Fail on missing JSON field -tableSource.setFailOnMissingField(true); -``` - -You can work with the Table as explained in the rest of the Table API guide: - -```java -tableEnvironment.registerTableSource("kafka-source", kafkaTableSource); -Table result = tableEnvironment.ingest("kafka-source"); -``` - -#### KafkaAvroTableSource - -The `KafkaAvroTableSource` allows you to read Avro's `SpecificRecord` objects from Kafka. - -To use the Kafka Avro source, you have to add the Kafka connector dependency to your project: - - - `flink-connector-kafka-0.8` for Kafka 0.8, - - `flink-connector-kafka-0.9` for Kafka 0.9, or - - `flink-connector-kafka-0.10` for Kafka 0.10, respectively. - -You can then create the source as follows (example for Kafka 0.8): -<div class="codetabs" markdown="1"> -<div data-lang="java" markdown="1"> -{% highlight java %} -// pass the generated Avro class to the TableSource -Class<? extends SpecificRecord> clazz = MyAvroType.class; - -KafkaAvroTableSource kafkaTableSource = new Kafka08AvroTableSource( - kafkaTopic, - kafkaProperties, - clazz); -{% endhighlight %} -</div> - -<div data-lang="scala" markdown="1"> -{% highlight scala %} -// pass the generated Avro class to the TableSource -val clazz = classOf[MyAvroType] - -val kafkaTableSource = new Kafka08AvroTableSource( - kafkaTopic, - kafkaProperties, - clazz) -{% endhighlight %} -</div> -</div> - -#### CsvTableSource - -The `CsvTableSource` is already included in `flink-table` without additional dependecies. - -The easiest way to create a `CsvTableSource` is by using the enclosed builder `CsvTableSource.builder()`, the builder has the following methods to configure properties: - - - `path(String path)` Sets the path to the CSV file, required. - - `field(String fieldName, TypeInformation<?> fieldType)` Adds a field with the field name and field type information, can be called multiple times, required. The call order of this method defines also the order of the fields in a row. - - `fieldDelimiter(String delim)` Sets the field delimiter, `","` by default. - - `lineDelimiter(String delim)` Sets the line delimiter, `"\n"` by default. - - `quoteCharacter(Character quote)` Sets the quote character for String values, `null` by default. - - `commentPrefix(String prefix)` Sets a prefix to indicate comments, `null` by default. - - `ignoreFirstLine()` Ignore the first line. Disabled by default. - - `ignoreParseErrors()` Skip records with parse error instead to fail. Throwing an exception by default. - -You can create the source as follows: - -<div class="codetabs" markdown="1"> -<div data-lang="java" markdown="1"> -{% highlight java %} -CsvTableSource csvTableSource = CsvTableSource - .builder() - .path("/path/to/your/file.csv") - .field("name", Types.STRING()) - .field("id", Types.INT()) - .field("score", Types.DOUBLE()) - .field("comments", Types.STRING()) - .fieldDelimiter("#") - .lineDelimiter("$") - .ignoreFirstLine() - .ignoreParseErrors() - .commentPrefix("%"); -{% endhighlight %} -</div> - -<div data-lang="scala" markdown="1"> -{% highlight scala %} -val csvTableSource = CsvTableSource - .builder - .path("/path/to/your/file.csv") - .field("name", Types.STRING) - .field("id", Types.INT) - .field("score", Types.DOUBLE) - .field("comments", Types.STRING) - .fieldDelimiter("#") - .lineDelimiter("$") - .ignoreFirstLine - .ignoreParseErrors - .commentPrefix("%") -{% endhighlight %} -</div> -</div> - -You can work with the Table as explained in the rest of the Table API guide in both stream and batch `TableEnvironment`s: - -<div class="codetabs" markdown="1"> -<div data-lang="java" markdown="1"> -{% highlight java %} -tableEnvironment.registerTableSource("mycsv", csvTableSource); - -Table streamTable = streamTableEnvironment.ingest("mycsv"); - -Table batchTable = batchTableEnvironment.scan("mycsv"); -{% endhighlight %} -</div> - -<div data-lang="scala" markdown="1"> -{% highlight scala %} -tableEnvironment.registerTableSource("mycsv", csvTableSource) - -val streamTable = streamTableEnvironment.ingest("mycsv") - -val batchTable = batchTableEnvironment.scan("mycsv") -{% endhighlight %} -</div> -</div> - -Registering external Catalogs --------------------------------- - -An external catalog is defined by the `ExternalCatalog` interface and provides information about databases and tables such as their name, schema, statistics, and access information. An `ExternalCatalog` is registered in a `TableEnvironment` as follows: - -<div class="codetabs" markdown="1"> -<div data-lang="java" markdown="1"> -{% highlight java %} -// works for StreamExecutionEnvironment identically -ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment(); -BatchTableEnvironment tableEnv = TableEnvironment.getTableEnvironment(env); - -ExternalCatalog customerCatalog = new InMemoryExternalCatalog(); - -// register the ExternalCatalog customerCatalog -tableEnv.registerExternalCatalog("Customers", customerCatalog); -{% endhighlight %} -</div> - -<div data-lang="scala" markdown="1"> -{% highlight scala %} -// works for StreamExecutionEnvironment identically -val env = ExecutionEnvironment.getExecutionEnvironment -val tableEnv = TableEnvironment.getTableEnvironment(env) - -val customerCatalog: ExternalCatalog = new InMemoryExternalCatalog - -// register the ExternalCatalog customerCatalog -tableEnv.registerExternalCatalog("Customers", customerCatalog) - -{% endhighlight %} -</div> -</div> - -Once registered in a `TableEnvironment`, all tables defined in a `ExternalCatalog` can be accessed from Table API or SQL queries by specifying their full path (`catalog`.`database`.`table`). - -Currently, Flink provides an `InMemoryExternalCatalog` for demo and testing purposes. However, the `ExternalCatalog` interface can also be used to connect catalogs like HCatalog or Metastore to the Table API. - -Table API ----------- -The Table API provides methods to apply relational operations on DataSets and Datastreams both in Scala and Java. - -The central concept of the Table API is a `Table` which represents a table with relational schema (or relation). Tables can be created from a `DataSet` or `DataStream`, converted into a `DataSet` or `DataStream`, or registered in a table catalog using a `TableEnvironment`. A `Table` is always bound to a specific `TableEnvironment`. It is not possible to combine Tables of different TableEnvironments. - -<div class="codetabs" markdown="1"> -<div data-lang="java" markdown="1"> -When using Flink's Java DataSet API, DataSets are converted to Tables and Tables to DataSets using a `TableEnvironment`. -The following example shows: - -- how a `DataSet` is converted to a `Table`, -- how relational queries are specified, and -- how a `Table` is converted back to a `DataSet`. - -{% highlight java %} -public class WC { - - public WC(String word, int count) { - this.word = word; this.count = count; - } - - public WC() {} // empty constructor to satisfy POJO requirements - - public String word; - public int count; -} - -... - -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)); - -Table table = tEnv.fromDataSet(input); - -Table wordCounts = table - .groupBy("word") - .select("word, count.sum as count"); - -DataSet<WC> result = tableEnv.toDataSet(wordCounts, WC.class); -{% endhighlight %} - -With Java, expressions must be specified by Strings. The embedded expression DSL is not supported. - -{% highlight java %} -ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment(); -BatchTableEnvironment tableEnv = TableEnvironment.getTableEnvironment(env); - -// register the DataSet cust as table "Customers" with fields derived from the dataset -tableEnv.registerDataSet("Customers", cust) - -// register the DataSet ord as table "Orders" with fields user, product, and amount -tableEnv.registerDataSet("Orders", ord, "user, product, amount"); -{% endhighlight %} - -Please refer to the Javadoc for a full list of supported operations and a description of the expression syntax. -</div> - -<div data-lang="scala" markdown="1"> -The Table API is enabled by importing `org.apache.flink.table.api.scala._`. This enables -implicit conversions to convert a `DataSet` or `DataStream` to a Table. The following example shows: - -- how a `DataSet` is converted to a `Table`, -- how relational queries are specified, and -- how a `Table` is converted back to a `DataSet`. - -{% highlight scala %} -import org.apache.flink.api.scala._ -import org.apache.flink.table.api.scala._ - -case class WC(word: String, count: Int) - -val env = ExecutionEnvironment.getExecutionEnvironment -val tEnv = TableEnvironment.getTableEnvironment(env) - -val input = env.fromElements(WC("hello", 1), WC("hello", 1), WC("ciao", 1)) -val expr = input.toTable(tEnv) -val result = expr - .groupBy('word) - .select('word, 'count.sum as 'count) - .toDataSet[WC] -{% endhighlight %} - -The expression DSL uses Scala symbols to refer to field names and code generation to -transform expressions to efficient runtime code. Please note that the conversion to and from -Tables only works when using Scala case classes or Java POJOs. Please refer to the [Type Extraction and Serialization]({{ site.baseurl }}/internals/types_serialization.html) section -to learn the characteristics of a valid POJO. - -Another example shows how to join two Tables: - -{% highlight scala %} -case class MyResult(a: String, d: Int) - -val input1 = env.fromElements(...).toTable(tEnv).as('a, 'b) -val input2 = env.fromElements(...).toTable(tEnv, 'c, 'd) - -val joined = input1.join(input2) - .where("a = c && d > 42") - .select("a, d") - .toDataSet[MyResult] -{% endhighlight %} - -Notice, how the field names of a Table can be changed with `as()` or specified with `toTable()` when converting a DataSet to a Table. In addition, the example shows how to use Strings to specify relational expressions. - -Creating a `Table` from a `DataStream` works in a similar way. -The following example shows how to convert a `DataStream` to a `Table` and filter it with the Table API. - -{% highlight scala %} -import org.apache.flink.api.scala._ -import org.apache.flink.table.api.scala._ - -val env = StreamExecutionEnvironment.getExecutionEnvironment -val tEnv = TableEnvironment.getTableEnvironment(env) - -val inputStream = env.addSource(...) -val result = inputStream - .toTable(tEnv, 'a, 'b, 'c) - .filter('a === 3) -val resultStream = result.toDataStream[Row] -{% endhighlight %} - -Please refer to the Scaladoc for a full list of supported operations and a description of the expression syntax. -</div> -</div> - -{% top %} - - -### Access a registered Table - -A registered table can be accessed from a `TableEnvironment` as follows: - -- `tEnv.scan("tName")` scans a `Table` that was registered as `"tName"` in a `BatchTableEnvironment`. -- `tEnv.ingest("tName")` ingests a `Table` that was registered as `"tName"` in a `StreamTableEnvironment`. - -{% top %} - -### Table API Operators - -The Table API features a domain-specific language to execute language-integrated queries on structured data in Scala and Java. -This section gives a brief overview of the available operators. You can find more details of operators in the [Javadoc](http://flink.apache.org/docs/latest/api/java/org/apache/flink/table/api/Table.html). - -<div class="codetabs" markdown="1"> -<div data-lang="java" markdown="1"> - -<table class="table table-bordered"> - <thead> - <tr> - <th class="text-left" style="width: 20%">Operators</th> - <th class="text-center">Description</th> - </tr> - </thead> - - <tbody> - <tr> - <td><strong>Select</strong></td> - <td> - <p>Similar to a SQL SELECT statement. Performs a select operation.</p> -{% highlight java %} -Table in = tableEnv.fromDataSet(ds, "a, b, c"); -Table result = in.select("a, c as d"); -{% endhighlight %} - <p>You can use star (<code>*</code>) to act as a wild card, selecting all of the columns in the table.</p> -{% highlight java %} -Table result = in.select("*"); -{% endhighlight %} - </td> - </tr> - - <tr> - <td><strong>As</strong></td> - <td> - <p>Renames fields.</p> -{% highlight java %} -Table in = tableEnv.fromDataSet(ds, "a, b, c"); -Table result = in.as("d, e, f"); -{% endhighlight %} - </td> - </tr> - - <tr> - <td><strong>Where / Filter</strong></td> - <td> - <p>Similar to a SQL WHERE clause. Filters out rows that do not pass the filter predicate.</p> -{% highlight java %} -Table in = tableEnv.fromDataSet(ds, "a, b, c"); -Table result = in.where("b = 'red'"); -{% endhighlight %} -or -{% highlight java %} -Table in = tableEnv.fromDataSet(ds, "a, b, c"); -Table result = in.filter("a % 2 = 0"); -{% endhighlight %} - </td> - </tr> - <tr> - <td><strong>GroupBy</strong></td> - <td> - <p>Similar to a SQL GROUPBY clause. Groups the rows on the grouping keys, with a following aggregation - operator to aggregate rows group-wise.</p> -{% highlight java %} -Table in = tableEnv.fromDataSet(ds, "a, b, c"); -Table result = in.groupBy("a").select("a, b.sum as d"); -{% endhighlight %} - </td> - </tr> - - <tr> - <td><strong>Join</strong></td> - <td> - <p>Similar to a SQL JOIN clause. Joins two tables. Both tables must have distinct field names and at least one equality join predicate must be defined through join operator or using a where or filter operator.</p> -{% highlight java %} -Table left = tableEnv.fromDataSet(ds1, "a, b, c"); -Table right = tableEnv.fromDataSet(ds2, "d, e, f"); -Table result = left.join(right).where("a = d").select("a, b, e"); -{% endhighlight %} - </td> - </tr> - - <tr> - <td><strong>LeftOuterJoin</strong></td> - <td> - <p>Similar to a SQL LEFT OUTER JOIN clause. Joins two tables. Both tables must have distinct field names and at least one equality join predicate must be defined.</p> -{% highlight java %} -Table left = tableEnv.fromDataSet(ds1, "a, b, c"); -Table right = tableEnv.fromDataSet(ds2, "d, e, f"); -Table result = left.leftOuterJoin(right, "a = d").select("a, b, e"); -{% endhighlight %} - </td> - </tr> - - <tr> - <td><strong>RightOuterJoin</strong></td> - <td> - <p>Similar to a SQL RIGHT OUTER JOIN clause. Joins two tables. Both tables must have distinct field names and at least one equality join predicate must be defined.</p> -{% highlight java %} -Table left = tableEnv.fromDataSet(ds1, "a, b, c"); -Table right = tableEnv.fromDataSet(ds2, "d, e, f"); -Table result = left.rightOuterJoin(right, "a = d").select("a, b, e"); -{% endhighlight %} - </td> - </tr> - - <tr> - <td><strong>FullOuterJoin</strong></td> - <td> - <p>Similar to a SQL FULL OUTER JOIN clause. Joins two tables. Both tables must have distinct field names and at least one equality join predicate must be defined.</p> -{% highlight java %} -Table left = tableEnv.fromDataSet(ds1, "a, b, c"); -Table right = tableEnv.fromDataSet(ds2, "d, e, f"); -Table result = left.fullOuterJoin(right, "a = d").select("a, b, e"); -{% endhighlight %} - </td> - </tr> - - <tr> - <td><strong>Union</strong></td> - <td> - <p>Similar to a SQL UNION clause. Unions two tables with duplicate records removed. Both tables must have identical field types.</p> -{% highlight java %} -Table left = tableEnv.fromDataSet(ds1, "a, b, c"); -Table right = tableEnv.fromDataSet(ds2, "a, b, c"); -Table result = left.union(right); -{% endhighlight %} - </td> - </tr> - - <tr> - <td><strong>UnionAll</strong></td> - <td> - <p>Similar to a SQL UNION ALL clause. Unions two tables. Both tables must have identical field types.</p> -{% highlight java %} -Table left = tableEnv.fromDataSet(ds1, "a, b, c"); -Table right = tableEnv.fromDataSet(ds2, "a, b, c"); -Table result = left.unionAll(right); -{% endhighlight %} - </td> - </tr> - - <tr> - <td><strong>Intersect</strong></td> - <td> - <p>Similar to a SQL INTERSECT clause. Intersect returns records that exist in both tables. If a record is present one or both tables more than once, it is returned just once, i.e., the resulting table has no duplicate records. Both tables must have identical field types.</p> -{% highlight java %} -Table left = tableEnv.fromDataSet(ds1, "a, b, c"); -Table right = tableEnv.fromDataSet(ds2, "d, e, f"); -Table result = left.intersect(right); -{% endhighlight %} - </td> - </tr> - - <tr> - <td><strong>IntersectAll</strong></td> - <td> - <p>Similar to a SQL INTERSECT ALL clause. IntersectAll returns records that exist in both tables. If a record is present in both tables more than once, it is returned as many times as it is present in both tables, i.e., the resulting table might have duplicate records. Both tables must have identical field types.</p> -{% highlight java %} -Table left = tableEnv.fromDataSet(ds1, "a, b, c"); -Table right = tableEnv.fromDataSet(ds2, "d, e, f"); -Table result = left.intersectAll(right); -{% endhighlight %} - </td> - </tr> - - <tr> - <td><strong>Minus</strong></td> - <td> - <p>Similar to a SQL EXCEPT clause. Minus returns records from the left table that do not exist in the right table. Duplicate records in the left table are returned exactly once, i.e., duplicates are removed. Both tables must have identical field types.</p> -{% highlight java %} -Table left = tableEnv.fromDataSet(ds1, "a, b, c"); -Table right = tableEnv.fromDataSet(ds2, "a, b, c"); -Table result = left.minus(right); -{% endhighlight %} - </td> - </tr> - - <tr> - <td><strong>MinusAll</strong></td> - <td> - <p>Similar to a SQL EXCEPT ALL clause. MinusAll returns the records that do not exist in the right table. A record that is present n times in the left table and m times in the right table is returned (n - m) times, i.e., as many duplicates as are present in the right table are removed. Both tables must have identical field types.</p> -{% highlight java %} -Table left = tableEnv.fromDataSet(ds1, "a, b, c"); -Table right = tableEnv.fromDataSet(ds2, "a, b, c"); -Table result = left.minusAll(right); -{% endhighlight %} - </td> - </tr> - - <tr> - <td><strong>Distinct</strong></td> - <td> - <p>Similar to a SQL DISTINCT clause. Returns records with distinct value combinations.</p> -{% highlight java %} -Table in = tableEnv.fromDataSet(ds, "a, b, c"); -Table result = in.distinct(); -{% endhighlight %} - </td> - </tr> - - <tr> - <td><strong>Order By</strong></td> - <td> - <p>Similar to a SQL ORDER BY clause. Returns records globally sorted across all parallel partitions.</p> -{% highlight java %} -Table in = tableEnv.fromDataSet(ds, "a, b, c"); -Table result = in.orderBy("a.asc"); -{% endhighlight %} - </td> - </tr> - - <tr> - <td><strong>Limit</strong></td> - <td> - <p>Similar to a SQL LIMIT clause. Limits a sorted result to a specified number of records from an offset position. Limit is technically part of the Order By operator and thus must be preceded by it.</p> -{% highlight java %} -Table in = tableEnv.fromDataSet(ds, "a, b, c"); -Table result = in.orderBy("a.asc").limit(3); // returns unlimited number of records beginning with the 4th record -{% endhighlight %} -or -{% highlight java %} -Table in = tableEnv.fromDataSet(ds, "a, b, c"); -Table result = in.orderBy("a.asc").limit(3, 5); // returns 5 records beginning with the 4th record -{% endhighlight %} - </td> - </tr> - - </tbody> -</table> - -</div> -<div data-lang="scala" markdown="1"> - -<table class="table table-bordered"> - <thead> - <tr> - <th class="text-left" style="width: 20%">Operators</th> - <th class="text-center">Description</th> - </tr> - </thead> - - <tbody> - <tr> - <td><strong>Select</strong></td> - <td> - <p>Similar to a SQL SELECT statement. Performs a select operation.</p> -{% highlight scala %} -val in = ds.toTable(tableEnv, 'a, 'b, 'c); -val result = in.select('a, 'c as 'd); -{% endhighlight %} - <p>You can use star (<code>*</code>) to act as a wild card, selecting all of the columns in the table.</p> -{% highlight scala %} -val in = ds.toTable(tableEnv, 'a, 'b, 'c); -val result = in.select('*); -{% endhighlight %} - </td> - </tr> - - <tr> - <td><strong>As</strong></td> - <td> - <p>Renames fields.</p> -{% highlight scala %} -val in = ds.toTable(tableEnv).as('a, 'b, 'c); -{% endhighlight %} - </td> - </tr> - - <tr> - <td><strong>Where / Filter</strong></td> - <td> - <p>Similar to a SQL WHERE clause. Filters out rows that do not pass the filter predicate.</p> -{% highlight scala %} -val in = ds.toTable(tableEnv, 'a, 'b, 'c); -val result = in.filter('a % 2 === 0) -{% endhighlight %} -or -{% highlight scala %} -val in = ds.toTable(tableEnv, 'a, 'b, 'c); -val result = in.where('b === "red"); -{% endhighlight %} - </td> - </tr> - - <tr> - <td><strong>GroupBy</strong></td> - <td> - <p>Similar to a SQL GROUPBY clause. Groups rows on the grouping keys, with a following aggregation - operator to aggregate rows group-wise.</p> -{% highlight scala %} -val in = ds.toTable(tableEnv, 'a, 'b, 'c); -val result = in.groupBy('a).select('a, 'b.sum as 'd); -{% endhighlight %} - </td> - </tr> - - <tr> - <td><strong>Join</strong></td> - <td> - <p>Similar to a SQL JOIN clause. Joins two tables. Both tables must have distinct field names and an equality join predicate must be defined using a where or filter operator.</p> -{% highlight scala %} -val left = ds1.toTable(tableEnv, 'a, 'b, 'c); -val right = ds2.toTable(tableEnv, 'd, 'e, 'f); -val result = left.join(right).where('a === 'd).select('a, 'b, 'e); -{% endhighlight %} - </td> - </tr> - - <tr> - <td><strong>LeftOuterJoin</strong></td> - <td> - <p>Similar to a SQL LEFT OUTER JOIN clause. Joins two tables. Both tables must have distinct field names and at least one equality join predicate must be defined.</p> -{% highlight scala %} -val left = tableEnv.fromDataSet(ds1, 'a, 'b, 'c) -val right = tableEnv.fromDataSet(ds2, 'd, 'e, 'f) -val result = left.leftOuterJoin(right, 'a === 'd).select('a, 'b, 'e) -{% endhighlight %} - </td> - </tr> - - <tr> - <td><strong>RightOuterJoin</strong></td> - <td> - <p>Similar to a SQL RIGHT OUTER JOIN clause. Joins two tables. Both tables must have distinct field names and at least one equality join predicate must be defined.</p> -{% highlight scala %} -val left = tableEnv.fromDataSet(ds1, 'a, 'b, 'c) -val right = tableEnv.fromDataSet(ds2, 'd, 'e, 'f) -val result = left.rightOuterJoin(right, 'a === 'd).select('a, 'b, 'e) -{% endhighlight %} - </td> - </tr> - - <tr> - <td><strong>FullOuterJoin</strong></td> - <td> - <p>Similar to a SQL FULL OUTER JOIN clause. Joins two tables. Both tables must have distinct field names and at least one equality join predicate must be defined.</p> -{% highlight scala %} -val left = tableEnv.fromDataSet(ds1, 'a, 'b, 'c) -val right = tableEnv.fromDataSet(ds2, 'd, 'e, 'f) -val result = left.fullOuterJoin(right, 'a === 'd).select('a, 'b, 'e) -{% endhighlight %} - </td> - </tr> - - <tr> - <td><strong>Union</strong></td> - <td> - <p>Similar to a SQL UNION clause. Unions two tables with duplicate records removed, both tables must have identical field types.</p> -{% highlight scala %} -val left = ds1.toTable(tableEnv, 'a, 'b, 'c); -val right = ds2.toTable(tableEnv, 'a, 'b, 'c); -val result = left.union(right); -{% endhighlight %} - </td> - </tr> - - <tr> - <td><strong>UnionAll</strong></td> - <td> - <p>Similar to a SQL UNION ALL clause. Unions two tables, both tables must have identical field types.</p> -{% highlight scala %} -val left = ds1.toTable(tableEnv, 'a, 'b, 'c); -val right = ds2.toTable(tableEnv, 'a, 'b, 'c); -val result = left.unionAll(right); -{% endhighlight %} - </td> - </tr> - - <tr> - <td><strong>Intersect</strong></td> - <td> - <p>Similar to a SQL INTERSECT clause. Intersect returns records that exist in both tables. If a record is present in one or both tables more than once, it is returned just once, i.e., the resulting table has no duplicate records. Both tables must have identical field types.</p> -{% highlight scala %} -val left = ds1.toTable(tableEnv, 'a, 'b, 'c); -val right = ds2.toTable(tableEnv, 'e, 'f, 'g); -val result = left.intersect(right); -{% endhighlight %} - </td> - </tr> - - <tr> - <td><strong>IntersectAll</strong></td> - <td> - <p>Similar to a SQL INTERSECT ALL clause. IntersectAll returns records that exist in both tables. If a record is present in both tables more than once, it is returned as many times as it is present in both tables, i.e., the resulting table might have duplicate records. Both tables must have identical field types.</p> -{% highlight scala %} -val left = ds1.toTable(tableEnv, 'a, 'b, 'c); -val right = ds2.toTable(tableEnv, 'e, 'f, 'g); -val result = left.intersectAll(right); -{% endhighlight %} - </td> - </tr> - - <tr> - <td><strong>Minus</strong></td> - <td> - <p>Similar to a SQL EXCEPT clause. Minus returns records from the left table that do not exist in the right table. Duplicate records in the left table are returned exactly once, i.e., duplicates are removed. Both tables must have identical field types.</p> -{% highlight scala %} -val left = ds1.toTable(tableEnv, 'a, 'b, 'c); -val right = ds2.toTable(tableEnv, 'a, 'b, 'c); -val result = left.minus(right); -{% endhighlight %} - </td> - </tr> - - <tr> - <td><strong>MinusAll</strong></td> - <td> - <p>Similar to a SQL EXCEPT ALL clause. MinusAll returns the records that do not exist in the right table. A record that is present n times in the left table and m times in the right table is returned (n - m) times, i.e., as many duplicates as are present in the right table are removed. Both tables must have identical field types.</p> -{% highlight scala %} -val left = ds1.toTable(tableEnv, 'a, 'b, 'c); -val right = ds2.toTable(tableEnv, 'a, 'b, 'c); -val result = left.minusAll(right); -{% endhighlight %} - </td> - </tr> - - <tr> - <td><strong>Distinct</strong></td> - <td> - <p>Similar to a SQL DISTINCT clause. Returns records with distinct value combinations.</p> -{% highlight scala %} -val in = ds.toTable(tableEnv, 'a, 'b, 'c); -val result = in.distinct(); -{% endhighlight %} - </td> - </tr> - - <tr> - <td><strong>Order By</strong></td> - <td> - <p>Similar to a SQL ORDER BY clause. Returns records globally sorted across all parallel partitions.</p> -{% highlight scala %} -val in = ds.toTable(tableEnv, 'a, 'b, 'c); -val result = in.orderBy('a.asc); -{% endhighlight %} - </td> - </tr> - - <tr> - <td><strong>Limit</strong></td> - <td> - <p>Similar to a SQL LIMIT clause. Limits a sorted result to a specified number of records from an offset position. Limit is technically part of the Order By operator and thus must be preceded by it.</p> -{% highlight scala %} -val in = ds.toTable(tableEnv, 'a, 'b, 'c); -val result = in.orderBy('a.asc).limit(3); // returns unlimited number of records beginning with the 4th record -{% endhighlight %} -or -{% highlight scala %} -val in = ds.toTable(tableEnv, 'a, 'b, 'c); -val result = in.orderBy('a.asc).limit(3, 5); // returns 5 records beginning with the 4th record -{% endhighlight %} - </td> - </tr> - - </tbody> -</table> -</div> -</div> - -{% top %} - -### Expression Syntax -Some of the operators in previous sections expect one or more expressions. Expressions can be specified using an embedded Scala DSL or as Strings. Please refer to the examples above to learn how expressions can be specified. - -This is the EBNF grammar for expressions: - -{% highlight ebnf %} - -expressionList = expression , { "," , expression } ; - -expression = timeIndicator | overConstant | alias ; - -alias = logic | ( logic , "as" , fieldReference ) | ( logic , "as" , "(" , fieldReference , { "," , fieldReference } , ")" ) ; - -logic = comparison , [ ( "&&" | "||" ) , comparison ] ; - -comparison = term , [ ( "=" | "==" | "===" | "!=" | "!==" | ">" | ">=" | "<" | "<=" ) , term ] ; - -term = product , [ ( "+" | "-" ) , product ] ; - -product = unary , [ ( "*" | "/" | "%") , unary ] ; - -unary = [ "!" | "-" ] , composite ; - -composite = over | nullLiteral | suffixed | atom ; - -suffixed = interval | cast | as | if | functionCall ; - -interval = timeInterval | rowInterval ; - -timeInterval = composite , "." , ("year" | "years" | "month" | "months" | "day" | "days" | "hour" | "hours" | "minute" | "minutes" | "second" | "seconds" | "milli" | "millis") ; - -rowInterval = composite , "." , "rows" ; - -cast = composite , ".cast(" , dataType , ")" ; - -dataType = "BYTE" | "SHORT" | "INT" | "LONG" | "FLOAT" | "DOUBLE" | "BOOLEAN" | "STRING" | "DECIMAL" | "SQL_DATE" | "SQL_TIME" | "SQL_TIMESTAMP" | "INTERVAL_MONTHS" | "INTERVAL_MILLIS" | ( "PRIMITIVE_ARRAY" , "(" , dataType , ")" ) | ( "OBJECT_ARRAY" , "(" , dataType , ")" ) ; - -as = composite , ".as(" , fieldReference , ")" ; - -if = composite , ".?(" , expression , "," , expression , ")" ; - -functionCall = composite , "." , functionIdentifier , [ "(" , [ expression , { "," , expression } ] , ")" ] ; - -atom = ( "(" , expression , ")" ) | literal | fieldReference ; - -fieldReference = "*" | identifier ; - -nullLiteral = "Null(" , dataType , ")" ; - -timeIntervalUnit = "YEAR" | "YEAR_TO_MONTH" | "MONTH" | "DAY" | "DAY_TO_HOUR" | "DAY_TO_MINUTE" | "DAY_TO_SECOND" | "HOUR" | "HOUR_TO_MINUTE" | "HOUR_TO_SECOND" | "MINUTE" | "MINUTE_TO_SECOND" | "SECOND" ; - -timePointUnit = "YEAR" | "MONTH" | "DAY" | "HOUR" | "MINUTE" | "SECOND" | "QUARTER" | "WEEK" | "MILLISECOND" | "MICROSECOND" ; - -over = composite , "over" , fieldReference ; - -overConstant = "current_row" | "current_range" | "unbounded_row" | "unbounded_row" ; - -timeIndicator = fieldReference , "." , ( "proctime" | "rowtime" ) ; - -{% endhighlight %} - -Here, `literal` is a valid Java literal, `fieldReference` specifies a column in the data (or all columns if `*` is used), and `functionIdentifier` specifies a supported scalar function. The -column names and function names follow Java identifier syntax. Expressions specified as Strings can also use prefix notation instead of suffix notation to call operators and functions. - -If working with exact numeric values or large decimals is required, the Table API also supports Java's BigDecimal type. In the Scala Table API decimals can be defined by `BigDecimal("123456")` and in Java by appending a "p" for precise e.g. `123456p`. - -In order to work with temporal values the Table API supports Java SQL's Date, Time, and Timestamp types. In the Scala Table API literals can be defined by using `java.sql.Date.valueOf("2016-06-27")`, `java.sql.Time.valueOf("10:10:42")`, or `java.sql.Timestamp.valueOf("2016-06-27 10:10:42.123")`. The Java and Scala Table API also support calling `"2016-06-27".toDate()`, `"10:10:42".toTime()`, and `"2016-06-27 10:10:42.123".toTimestamp()` for converting Strings into temporal types. *Note:* Since Java's temporal SQL types are time zone dependent, please make sure that the Flink Client and all TaskManagers use the same time zone. - -Temporal intervals can be represented as number of months (`Types.INTERVAL_MONTHS`) or number of milliseconds (`Types.INTERVAL_MILLIS`). Intervals of same type can be added or subtracted (e.g. `1.hour + 10.minutes`). Intervals of milliseconds can be added to time points (e.g. `"2016-08-10".toDate + 5.days`). - -{% top %} - -### Windows - -The Table API is a declarative API to define queries on batch and streaming tables. Projection, selection, and union operations can be applied both on streaming and batch tables without additional semantics. Aggregations on (possibly) infinite streaming tables, however, can only be computed on finite groups of records. Window aggregates group rows into finite groups based on time or row-count intervals and evaluate aggregation functions once per group. For batch tables, windows are a convenient shortcut to group records by time intervals. - -Windows are defined using the `window(w: Window)` clause and require an alias, which is specified using the `as` clause. In order to group a table by a window, the window alias must be referenced in the `groupBy(...)` clause like a regular grouping attribute. -The following example shows how to define a window aggregation on a table. - -<div class="codetabs" markdown="1"> -<div data-lang="java" markdown="1"> -{% highlight java %} -Table table = input - .window([Window w].as("w")) // define window with alias w - .groupBy("w") // group the table by window w - .select("b.sum"); // aggregate -{% endhighlight %} -</div> - -<div data-lang="scala" markdown="1"> -{% highlight scala %} -val table = input - .window([w: Window] as 'w) // define window with alias w - .groupBy('w) // group the table by window w - .select('b.sum) // aggregate -{% endhighlight %} -</div> -</div> - -In streaming environments, window aggregates can only be computed in parallel if they group on one or more attributes in addition to the window, i.e., the `groupBy(...)` clause references a window alias and at least one additional attribute. A `groupBy(...)` clause that only references a window alias (such as in the example above) can only be evaluated by a single, non-parallel task. -The following example shows how to define a window aggregation with additional grouping attributes. - -<div class="codetabs" markdown="1"> -<div data-lang="java" markdown="1"> -{% highlight java %} -Table table = input - .window([Window w].as("w")) // define window with alias w - .groupBy("w, a") // group the table by attribute a and window w - .select("a, b.sum"); // aggregate -{% endhighlight %} -</div> - -<div data-lang="scala" markdown="1"> -{% highlight scala %} -val table = input - .window([w: Window] as 'w) // define window with alias w - .groupBy('w, 'a) // group the table by attribute a and window w - .select('a, 'b.sum) // aggregate -{% endhighlight %} -</div> -</div> - -The `Window` parameter defines how rows are mapped to windows. `Window` is not an interface that users can implement. Instead, the Table API provides a set of predefined `Window` classes with specific semantics, which are translated into underlying `DataStream` or `DataSet` operations. The supported window definitions are listed below. Window properties such as the start and end timestamp of a time window can be added in the select statement as a property of the window alias as `w.start` and `w.end`, respectively. - -<div class="codetabs" markdown="1"> -<div data-lang="java" markdown="1"> -{% highlight java %} -Table table = input - .window([Window w].as("w")) // define window with alias w - .groupBy("w, a") // group the table by attribute a and window w - .select("a, w.start, w.end, b.count"); // aggregate and add window start and end timestamps -{% endhighlight %} -</div> - -<div data-lang="scala" markdown="1"> -{% highlight scala %} -val table = input - .window([w: Window] as 'w) // define window with alias w - .groupBy('w, 'a) // group the table by attribute a and window w - .select('a, 'w.start, 'w.end, 'b.count) // aggregate and add window start and end timestamps -{% endhighlight %} -</div> -</div> - -#### Tumble (Tumbling Windows) - -A tumbling window assigns rows to non-overlapping, continuous windows of fixed length. For example, a tumbling window of 5 minutes groups rows in 5 minutes intervals. Tumbling windows can be defined on event-time, processing-time, or on a row-count. - -Tumbling windows are defined by using the `Tumble` class as follows: - -<table class="table table-bordered"> - <thead> - <tr> - <th class="text-left" style="width: 20%">Method</th> - <th class="text-left" style="width: 20%">Required?</th> - <th class="text-left">Description</th> - </tr> - </thead> - - <tbody> - <tr> - <td><code>over</code></td> - <td>Required.</td> - <td>Defines the length the window, either as time or row-count interval.</td> - </tr> - <tr> - <td><code>on</code></td> - <td>Required for streaming event-time windows and windows on batch tables.</td> - <td>Defines the time mode for streaming tables (<code>rowtime</code> is a logical system attribute); for batch tables, the time attribute on which records are grouped.</td> - </tr> - <tr> - <td><code>as</code></td> - <td>Required.</td> - <td>Assigns an alias to the window. The alias is used to reference the window in the following <code>groupBy()</code> clause and optionally to select window properties such as window start or end time in the <code>select()</code> clause.</td> - </tr> - </tbody> -</table> - -<div class="codetabs" markdown="1"> -<div data-lang="java" markdown="1"> -{% highlight java %} -// Tumbling Event-time Window -.window(Tumble.over("10.minutes").on("rowtime").as("w")); - -// Tumbling Processing-time Window -.window(Tumble.over("10.minutes").as("w")); - -// Tumbling Row-count Window -.window(Tumble.over("10.rows").as("w")); -{% endhighlight %} -</div> - -<div data-lang="scala" markdown="1"> -{% highlight scala %} -// Tumbling Event-time Window -.window(Tumble over 10.minutes on 'rowtime as 'w) - -// Tumbling Processing-time Window -.window(Tumble over 10.minutes as 'w) - -// Tumbling Row-count Window -.window(Tumble over 10.rows as 'w) -{% endhighlight %} -</div> -</div> - -#### Slide (Sliding Windows) - -A sliding window has a fixed size and slides by a specified slide interval. If the slide interval is smaller than the window size, sliding windows are overlapping. Thus, rows can be assigned to multiple windows. For example, a sliding window of 15 minutes size and 5 minute slide interval assigns each row to 3 different windows of 15 minute size, which are evaluated in an interval of 5 minutes. Sliding windows can be defined on event-time, processing-time, or on a row-count. - -Sliding windows are defined by using the `Slide` class as follows: - -<table class="table table-bordered"> - <thead> - <tr> - <th class="text-left" style="width: 20%">Method</th> - <th class="text-left" style="width: 20%">Required?</th> - <th class="text-left">Description</th> - </tr> - </thead> - - <tbody> - <tr> - <td><code>over</code></td> - <td>Required.</td> - <td>Defines the length of the window, either as time or row-count interval.</td> - </tr> - <tr> - <td><code>every</code></td> - <td>Required.</td> - <td>Defines the slide interval, either as time or row-count interval. The slide interval must be of the same type as the size interval.</td> - </tr> - <tr> - <td><code>on</code></td> - <td>Required for event-time windows and windows on batch tables.</td> - <td>Defines the time mode for streaming tables (<code>rowtime</code> is a logical system attribute); for batch tables, the time attribute on which records are grouped</td> - </tr> - <tr> - <td><code>as</code></td> - <td>Required.</td> - <td>Assigns an alias to the window. The alias is used to reference the window in the following <code>groupBy()</code> clause and optionally to select window properties such as window start or end time in the <code>select()</code> clause.</td> - </tr> - </tbody> -</table> - -<div class="codetabs" markdown="1"> -<div data-lang="java" markdown="1"> -{% highlight java %} -// Sliding Event-time Window -.window(Slide.over("10.minutes").every("5.minutes").on("rowtime").as("w")); - -// Sliding Processing-time window -.window(Slide.over("10.minutes").every("5.minutes").as("w")); - -// Sliding Row-count window -.window(Slide.over("10.rows").every("5.rows").as("w")); -{% endhighlight %} -</div> - -<div data-lang="scala" markdown="1"> -{% highlight scala %} -// Sliding Event-time Window -.window(Slide over 10.minutes every 5.minutes on 'rowtime as 'w) - -// Sliding Processing-time window -.window(Slide over 10.minutes every 5.minutes as 'w) - -// Sliding Row-count window -.window(Slide over 10.rows every 5.rows as 'w) -{% endhighlight %} -</div> -</div> - -#### Session (Session Windows) - -Session windows do not have a fixed size but their bounds are defined by an interval of inactivity, i.e., a session window is closes if no event appears for a defined gap period. For example a session window with a 30 minute gap starts when a row is observed after 30 minutes inactivity (otherwise the row would be added to an existing window) and is closed if no row is added within 30 minutes. Session windows can work on event-time or processing-time. - -A session window is defined by using the `Session` class as follows: - -<table class="table table-bordered"> - <thead> - <tr> - <th class="text-left" style="width: 20%">Method</th> - <th class="text-left" style="width: 20%">Required?</th> - <th class="text-left">Description</th> - </tr> - </thead> - - <tbody> - <tr> - <td><code>withGap</code></td> - <td>Required.</td> - <td>Defines the gap between two windows as time interval.</td> - </tr> - <tr> - <td><code>on</code></td> - <td>Required for event-time windows and windows on batch tables.</td> - <td>Defines the time mode for streaming tables (<code>rowtime</code> is a logical system attribute); for batch tables, the time attribute on which records are grouped</td> - </tr> - <tr> - <td><code>as</code></td> - <td>Required.</td> - <td>Assigns an alias to the window. The alias is used to reference the window in the following <code>groupBy()</code> clause and optionally to select window properties such as window start or end time in the <code>select()</code> clause.</td> - </tr> - </tbody> -</table> - -<div class="codetabs" markdown="1"> -<div data-lang="java" markdown="1"> -{% highlight java %} -// Session Event-time Window -.window(Session.withGap("10.minutes").on("rowtime").as("w")); - -// Session Processing-time Window -.window(Session.withGap("10.minutes").as("w")); -{% endhighlight %} -</div> - -<div data-lang="scala" markdown="1"> -{% highlight scala %} -// Session Event-time Window -.window(Session withGap 10.minutes on 'rowtime as 'w) - -// Session Processing-time Window -.window(Session withGap 10.minutes as 'w) -{% endhighlight %} -</div> -</div> - -#### Limitations - -Currently the following features are not supported yet: - -- Row-count windows on event-time -- Non-grouped session windows on batch tables -- Sliding windows on batch tables - -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)). 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.* - -### SQL on Batch Tables - -<div class="codetabs" markdown="1"> -<div data-lang="java" markdown="1"> -{% highlight java %} -ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment(); -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 result2 = tableEnv.sql( - "SELECT SUM(amount) FROM Orders WHERE product LIKE '%Rubber%'"); -{% endhighlight %} -</div> - -<div data-lang="scala" markdown="1"> -{% highlight scala %} -val env = ExecutionEnvironment.getExecutionEnvironment -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 result2 = tableEnv.sql( - "SELECT SUM(amount) FROM Orders WHERE product LIKE '%Rubber%'") -{% endhighlight %} -</div> -</div> - -#### Limitations - -The current version supports selection (filter), projection, inner equi-joins, grouping, aggregates, and sorting on batch tables. - -Among others, the following SQL features are not supported, yet: - -- Timestamps and intervals are limited to milliseconds precision -- Interval arithmetic is currenly limited -- Non-equi joins and Cartesian products -- Efficient grouping sets - -*Note: Tables are joined in the order in which they are specified in the `FROM` clause. In some cases the table order must be manually tweaked to resolve Cartesian products.* - -### SQL on Streaming Tables - -SQL queries can be executed on streaming Tables (Tables backed by `DataStream` or `StreamTableSource`) like standard SQL. - -<div class="codetabs" markdown="1"> -<div data-lang="java" markdown="1"> -{% highlight java %} -StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment(); -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 result2 = tableEnv.sql( - "SELECT product, amount FROM Orders WHERE product LIKE '%Rubber%'"); -{% endhighlight %} -</div> - -<div data-lang="scala" markdown="1"> -{% highlight scala %} -val env = StreamExecutionEnvironment.getExecutionEnvironment -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 result2 = tableEnv.sql( - "SELECT product, amount FROM Orders WHERE product LIKE '%Rubber%'") -{% endhighlight %} -</div> -</div> - -#### Limitations - -Joins, set operations, and non-windowed aggregations are not supported yet. -`UNNEST` supports only arrays and does not support `WITH ORDINALITY` yet. - -{% top %} - -### Group Windows - -Group windows are defined in the `GROUP BY` clause of a SQL query. Just like queries with regular `GROUP BY` clauses, queries with a `GROUP BY` clause that includes a group window function compute a single result row per group. The following group windows functions are supported for SQL on batch and streaming tables. - -<table class="table table-bordered"> - <thead> - <tr> - <th class="text-left" style="width: 30%">Group Window Function</th> - <th class="text-left">Description</th> - </tr> - </thead> - - <tbody> - <tr> - <td><code>TUMBLE(time_attr, interval)</code></td> - <td>Defines a tumbling time window. A tumbling time window assigns rows to non-overlapping, continuous windows with a fixed duration (<code>interval</code>). For example, a tumbling window of 5 minutes groups rows in 5 minutes intervals. Tumbling windows can be defined on event-time (stream + batch) or processing-time (stream).</td> - </tr> - <tr> - <td><code>HOP(time_attr, interval, interval)</code></td> - <td>Defines a hopping time window (called sliding window in the Table API). A hopping time window has a fixed duration (second <code>interval</code> parameter) and hops by a specified hop interval (first <code>interval</code> parameter). If the hop interval is smaller than the window size, hopping windows are overlapping. Thus, rows can be assigned to multiple windows. For example, a hopping window of 15 minutes size and 5 minute hop interval assigns each row to 3 different windows of 15 minute size, which are evaluated in an interval of 5 minutes. Hopping windows can be defined on event-time (stream + batch) or processing-time (stream).</td> - </tr> - <tr> - <td><code>SESSION(time_attr, interval)</code></td> - <td>Defines a session time window. Session time windows do not have a fixed duration but their bounds are defined by a time <code>interval</code> of inactivity, i.e., a session window is closed if no event appears for a defined gap period. For example a session window with a 30 minute gap starts when a row is observed after 30 minutes inactivity (otherwise the row would be added to an existing window) and is closed if no row is added within 30 minutes. Session windows can work on event-time (stream + batch) or processing-time (stream).</td> - </tr> - </tbody> -</table> - -For SQL queries on streaming tables, the `time_attr` argument of the group window function must be one of the `rowtime()` or `proctime()` time-indicators, which distinguish between event or processing time, respectively. For SQL on batch tables, the `time_attr` argument of the group window function must be an attribute of type `TIMESTAMP`. - -#### Selecting Group Window Start and End Timestamps - -The start and end timestamps of group windows can be selected with the following auxiliary functions: - -<table class="table table-bordered"> - <thead> - <tr> - <th class="text-left" style="width: 40%">Auxiliary Function</th> - <th class="text-left">Description</th> - </tr> - </thead> - - <tbody> - <tr> - <td> - <code>TUMBLE_START(time_attr, interval)</code><br/> - <code>HOP_START(time_attr, interval, interval)</code><br/> - <code>SESSION_START(time_attr, interval)</code><br/> - </td> - <td>Returns the start timestamp of the corresponding tumbling, hopping, and session window.</td> - </tr> - <tr> - <td> - <code>TUMBLE_END(time_attr, interval)</code><br/> - <code>HOP_END(time_attr, interval, interval)</code><br/> - <code>SESSION_END(time_attr, interval)</code><br/> - </td> - <td>Returns the end timestamp of the corresponding tumbling, hopping, and session window.</td> - </tr> - </tbody> -</table> - -Note that the auxiliary functions must be called with exactly same arguments as the group window function in the `GROUP BY` clause. - -The following examples show how to specify SQL queries with group windows on streaming tables. - -<div class="codetabs" markdown="1"> -<div data-lang="java" markdown="1"> -{% highlight java %} -StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment(); -StreamTableEnvironment tableEnv = TableEnvironment.getTableEnvironment(env); - -// ingest a DataStream from an external source -DataStream<Tuple3<Long, String, Integer>> ds = env.addSource(...); -// register the DataStream as table "Orders" -tableEnv.registerDataStream("Orders", ds, "user, product, amount"); - -// compute SUM(amount) per day (in event-time) -Table result1 = tableEnv.sql( - "SELECT user, " + - " TUMBLE_START(rowtime(), INTERVAL '1' DAY) as wStart, " + - " SUM(amount) FROM Orders " + - "GROUP BY TUMBLE(rowtime(), INTERVAL '1' DAY), user"); - -// compute SUM(amount) per day (in processing-time) -Table result2 = tableEnv.sql( - "SELECT user, SUM(amount) FROM Orders GROUP BY TUMBLE(proctime(), INTERVAL '1' DAY), user"); - -// compute every hour the SUM(amount) of the last 24 hours in event-time -Table result3 = tableEnv.sql( - "SELECT product, SUM(amount) FROM Orders GROUP BY HOP(rowtime(), INTERVAL '1' HOUR, INTERVAL '1' DAY), product"); - -// compute SUM(amount) per session with 12 hour inactivity gap (in event-time) -Table result4 = tableEnv.sql( - "SELECT user, " + - " SESSION_START(rowtime(), INTERVAL '12' HOUR) AS sStart, " + - " SESSION_END(rowtime(), INTERVAL '12' HOUR) AS snd, " + - " SUM(amount) " + - "FROM Orders " + - "GROUP BY SESSION(rowtime(), INTERVAL '12' HOUR), user"); - -{% endhighlight %} -</div> - -<div data-lang="scala" markdown="1"> -{% highlight scala %} -val env = StreamExecutionEnvironment.getExecutionEnvironment -val tableEnv = TableEnvironment.getTableEnvironment(env) - -// read a DataStream from an external source -val ds: DataStream[(Long, String, Int)] = env.addSource(...) -// register the DataStream under the name "Orders" -tableEnv.registerDataStream("Orders", ds, 'user, 'product, 'amount) - -// compute SUM(amount) per day (in event-time) -val result1 = tableEnv.sql( - """ - |SELECT - | user, - | TUMBLE_START(rowtime(), INTERVAL '1' DAY) as wStart, - | SUM(amount) - | FROM Orders - | GROUP BY TUMBLE(rowtime(), INTERVAL '1' DAY), user - """.stripMargin) - -// compute SUM(amount) per day (in processing-time) -val result2 = tableEnv.sql( - "SELECT user, SUM(amount) FROM Orders GROUP BY TUMBLE(proctime(), INTERVAL '1' DAY), user") - -// compute every hour the SUM(amount) of the last 24 hours in event-time -val result3 = tableEnv.sql( - "SELECT product, SUM(amount) FROM Orders GROUP BY HOP(rowtime(), INTERVAL '1' HOUR, INTERVAL '1' DAY), product") - -// compute SUM(amount) per session with 12 hour inactivity gap (in event-time) -val result4 = tableEnv.sql( - """ - |SELECT - | user, - | SESSION_START(rowtime(), INTERVAL '12' HOUR) AS sStart, - | SESSION_END(rowtime(), INTERVAL '12' HOUR) AS sEnd, - | SUM(amount) - | FROM Orders - | GROUP BY SESSION(rowtime(), INTERVAL '12' HOUR), user - """.stripMargin) - -{% endhighlight %} -</div> -</div> - -{% top %} - -### SQL Syntax - -Flink uses [Apache Calcite](https://calcite.apache.org/docs/reference.html) for SQL parsing. Currently, Flink SQL only supports query-related SQL syntax and only a subset of the comprehensive SQL standard. The following BNF-grammar describes the supported SQL features: - -``` - -query: - values - | { - select - | selectWithoutFrom - | query UNION [ ALL ] query - | query EXCEPT query - | query INTERSECT query - } - [ ORDER BY orderItem [, orderItem ]* ] - [ LIMIT { count | ALL } ] - [ OFFSET start { ROW | ROWS } ] - [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY] - -orderItem: - expression [ ASC | DESC ] - -select: - SELECT [ ALL | DISTINCT ] - { * | projectItem [, projectItem ]* } - FROM tableExpression - [ WHERE booleanExpression ] - [ GROUP BY { groupItem [, groupItem ]* } ] - [ HAVING booleanExpression ] - -selectWithoutFrom: - SELECT [ ALL | DISTINCT ] - { * | projectItem [, projectItem ]* } - -projectItem: - expression [ [ AS ] columnAlias ] - | tableAlias . * - -tableExpression: - tableReference [, tableReference ]* - | tableExpression [ NATURAL ] [ LEFT | RIGHT | FULL ] JOIN tableExpression [ joinCondition ] - -joinCondition: - ON booleanExpression - | USING '(' column [, column ]* ')' - -tableReference: - tablePrimary - [ [ AS ] alias [ '(' columnAlias [, columnAlias ]* ')' ] ] - -tablePrimary: - [ TABLE ] [ [ catalogName . ] schemaName . ] tableName - | LATERAL TABLE '(' functionName '(' expression [, expression ]* ')' ')' - | UNNEST '(' expression ')' - -values: - VALUES expression [, expression ]* - -groupItem: - expression - | '(' ')' - | '(' expression [, expression ]* ')' - | CUBE '(' expression [, expression ]* ')' - | ROLLUP '(' expression [, expression ]* ')' - | GROUPING SETS '(' groupItem [, groupItem ]* ')' -``` - -For a better definition of SQL queries within a Java String, Flink SQL uses a lexical policy similar to Java: - -- The case of identifiers is preserved whether or not they are quoted. -- After which, identifiers are matched case-sensitively. -- Unlike Java, back-ticks allow identifiers to contain non-alphanumeric characters (e.g. <code>"SELECT a AS `my field` FROM t"</code>). - - -{% top %} - -### Reserved Keywords - -Although not every SQL feature is implemented yet, some string combinations are already reserved as keywords for future use. If you want to use one of the following strings as a field name, make sure to surround them with backticks (e.g. `` `value` ``, `` `count` ``). - -{% highlight sql %} - -A, ABS, ABSOLUTE, ACTION, ADA, ADD, ADMIN, AFTER, ALL, ALLOCATE, ALLOW, ALTER, ALWAYS, AND, ANY, ARE, ARRAY, AS, ASC, ASENSITIVE, ASSERTION, ASSIGNMENT, ASYMMETRIC, AT, ATOMIC, ATTRIBUTE, ATTRIBUTES, AUTHORIZATION, AVG, BEFORE, BEGIN, BERNOULLI, BETWEEN, BIGINT, BINARY, BIT, BLOB, BOOLEAN, BOTH, BREADTH, BY, C, CALL, CALLED, CARDINALITY, CASCADE, CASCADED, CASE, CAST, CATALOG, CATALOG_NAME, CEIL, CEILING, CENTURY, CHAIN, CHAR, CHARACTER, CHARACTERISTICTS, CHARACTERS, CHARACTER_LENGTH, CHARACTER_SET_CATALOG, CHARACTER_SET_NAME, CHARACTER_SET_SCHEMA, CHAR_LENGTH, CHECK, CLASS_ORIGIN, CLOB, CLOSE, COALESCE, COBOL, COLLATE, COLLATION, COLLATION_CATALOG, COLLATION_NAME, COLLATION_SCHEMA, COLLECT, COLUMN, COLUMN_NAME, COMMAND_FUNCTION, COMMAND_FUNCTION_CODE, COMMIT, COMMITTED, CONDITION, CONDITION_NUMBER, CONNECT, CONNECTION, CONNECTION_NAME, CONSTRAINT, CONSTRAINTS, CONSTRAINT_CATALOG, CONSTRAINT_NAME, CONSTRAINT_SCHEMA, CONSTRUCTOR, CONTAINS, CONTINUE, CONVERT, CORR, CORRESPONDING, COUN T, COVAR_POP, COVAR_SAMP, CREATE, CROSS, CUBE, CUME_DIST, CURRENT, CURRENT_CATALOG, CURRENT_DATE, CURRENT_DEFAULT_TRANSFORM_GROUP, CURRENT_PATH, CURRENT_ROLE, CURRENT_SCHEMA, CURRENT_TIME, CURRENT_TIMESTAMP, CURRENT_TRANSFORM_GROUP_FOR_TYPE, CURRENT_USER, CURSOR, CURSOR_NAME, CYCLE, DATA, DATABASE, DATE, DATETIME_INTERVAL_CODE, DATETIME_INTERVAL_PRECISION, DAY, DEALLOCATE, DEC, DECADE, DECIMAL, DECLARE, DEFAULT, DEFAULTS, DEFERRABLE, DEFERRED, DEFINED, DEFINER, DEGREE, DELETE, DENSE_RANK, DEPTH, DEREF, DERIVED, DESC, DESCRIBE, DESCRIPTION, DESCRIPTOR, DETERMINISTIC, DIAGNOSTICS, DISALLOW, DISCONNECT, DISPATCH, DISTINCT, DOMAIN, DOUBLE, DOW, DOY, DROP, DYNAMIC, DYNAMIC_FUNCTION, DYNAMIC_FUNCTION_CODE, EACH, ELEMENT, ELSE, END, END-EXEC, EPOCH, EQUALS, ESCAPE, EVERY, EXCEPT, EXCEPTION, EXCLUDE, EXCLUDING, EXEC, EXECUTE, EXISTS, EXP, EXPLAIN, EXTEND, EXTERNAL, EXTRACT, FALSE, FETCH, FILTER, FINAL, FIRST, FIRST_VALUE, FLOAT, FLOOR, FOLLOWING, FOR, FOREIGN, FORTRAN, FOUND, FRAC_SECOND, F REE, FROM, FULL, FUNCTION, FUSION, G, GENERAL, GENERATED, GET, GLOBAL, GO, GOTO, GRANT, GRANTED, GROUP, GROUPING, HAVING, HIERARCHY, HOLD, HOUR, IDENTITY, IMMEDIATE, IMPLEMENTATION, IMPORT, IN, INCLUDING, INCREMENT, INDICATOR, INITIALLY, INNER, INOUT, INPUT, INSENSITIVE, INSERT, INSTANCE, INSTANTIABLE, INT, INTEGER, INTERSECT, INTERSECTION, INTERVAL, INTO, INVOKER, IS, ISOLATION, JAVA, JOIN, K, KEY, KEY_MEMBER, KEY_TYPE, LABEL, LANGUAGE, LARGE, LAST, LAST_VALUE, LATERAL, LEADING, LEFT, LENGTH, LEVEL, LIBRARY, LIKE, LIMIT, LN, LOCAL, LOCALTIME, LOCALTIMESTAMP, LOCATOR, LOWER, M, MAP, MATCH, MATCHED, MAX, MAXVALUE, MEMBER, MERGE, MESSAGE_LENGTH, MESSAGE_OCTET_LENGTH, MESSAGE_TEXT, METHOD, MICROSECOND, MILLENNIUM, MIN, MINUTE, MINVALUE, MOD, MODIFIES, MODULE, MONTH, MORE, MULTISET, MUMPS, NAME, NAMES, NATIONAL, NATURAL, NCHAR, NCLOB, NESTING, NEW, NEXT, NO, NONE, NORMALIZE, NORMALIZED, NOT, NULL, NULLABLE, NULLIF, NULLS, NUMBER, NUMERIC, OBJECT, OCTETS, OCTET_LENGTH, OF, OFFSET, OLD, O N, ONLY, OPEN, OPTION, OPTIONS, OR, ORDER, ORDERING, ORDINALITY, OTHERS, OUT, OUTER, OUTPUT, OVER, OVERLAPS, OVERLAY, OVERRIDING, PAD, PARAMETER, PARAMETER_MODE, PARAMETER_NAME, PARAMETER_ORDINAL_POSITION, PARAMETER_SPECIFIC_CATALOG, PARAMETER_SPECIFIC_NAME, PARAMETER_SPECIFIC_SCHEMA, PARTIAL, PARTITION, PASCAL, PASSTHROUGH, PATH, PERCENTILE_CONT, PERCENTILE_DISC, PERCENT_RANK, PLACING, PLAN, PLI, POSITION, POWER, PRECEDING, PRECISION, PREPARE, PRESERVE, PRIMARY, PRIOR, PRIVILEGES, PROCEDURE, PUBLIC, QUARTER, RANGE, RANK, READ, READS, REAL, RECURSIVE, REF, REFERENCES, REFERENCING, REGR_AVGX, REGR_AVGY, REGR_COUNT, REGR_INTERCEPT, REGR_R2, REGR_SLOPE, REGR_SXX, REGR_SXY, REGR_SYY, RELATIVE, RELEASE, REPEATABLE, RESET, RESTART, RESTRICT, RESULT, RETURN, RETURNED_CARDINALITY, RETURNED_LENGTH, RETURNED_OCTET_LENGTH, RETURNED_SQLSTATE, RETURNS, REVOKE, RIGHT, ROLE, ROLLBACK, ROLLUP, ROUTINE, ROUTINE_CATALOG, ROUTINE_NAME, ROUTINE_SCHEMA, ROW, ROWS, ROW_COUNT, ROW_NUMBER, SAVEPOINT, SCALE , SCHEMA, SCHEMA_NAME, SCOPE, SCOPE_CATALOGS, SCOPE_NAME, SCOPE_SCHEMA, SCROLL, SEARCH, SECOND, SECTION, SECURITY, SELECT, SELF, SENSITIVE, SEQUENCE, SERIALIZABLE, SERVER, SERVER_NAME, SESSION, SESSION_USER, SET, SETS, SIMILAR, SIMPLE, SIZE, SMALLINT, SOME, SOURCE, SPACE, SPECIFIC, SPECIFICTYPE, SPECIFIC_NAME, SQL, SQLEXCEPTION, SQLSTATE, SQLWARNING, SQL_TSI_DAY, SQL_TSI_FRAC_SECOND, SQL_TSI_HOUR, SQL_TSI_MICROSECOND, SQL_TSI_MINUTE, SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_SECOND, SQL_TSI_WEEK, SQL_TSI_YEAR, SQRT, START, STATE, STATEMENT, STATIC, STDDEV_POP, STDDEV_SAMP, STREAM, STRUCTURE, STYLE, SUBCLASS_ORIGIN, SUBMULTISET, SUBSTITUTE, SUBSTRING, SUM, SYMMETRIC, SYSTEM, SYSTEM_USER, TABLE, TABLESAMPLE, TABLE_NAME, TEMPORARY, THEN, TIES, TIME, TIMESTAMP, TIMESTAMPADD, TIMESTAMPDIFF, TIMEZONE_HOUR, TIMEZONE_MINUTE, TINYINT, TO, TOP_LEVEL_COUNT, TRAILING, TRANSACTION, TRANSACTIONS_ACTIVE, TRANSACTIONS_COMMITTED, TRANSACTIONS_ROLLED_BACK, TRANSFORM, TRANSFORMS, TRANSLATE, TRANSLATION, TREAT, TRIGGER, TRIGGER_CATALOG, TRIGGER_NAME, TRIGGER_SCHEMA, TRIM, TRUE, TYPE, UESCAPE, UNBOUNDED, UNCOMMITTED, UNDER, UNION, UNIQUE, UNKNOWN, UNNAMED, UNNEST, UPDATE, UPPER, UPSERT, USAGE, USER, USER_DEFINED_TYPE_CATALOG, USER_DEFINED_TYPE_CODE, USER_DEFINED_TYPE_NAME, USER_DEFINED_TYPE_SCHEMA, USING, VALUE, VALUES, VARBINARY, VARCHAR, VARYING, VAR_POP, VAR_SAMP, VERSION, VIEW, WEEK, WHEN, WHENEVER, WHERE, WIDTH_BUCKET, WINDOW, WITH, WITHIN, WITHOUT, WORK, WRAPPER, WRITE, XML, YEAR, ZONE - -{% endhighlight %} - -{% top %} - -Data Types ----------- - -The Table API is built on top of Flink's DataSet and DataStream API. Internally, it also uses Flink's `TypeInformation` to distinguish between types. The Table API does not support all Flink types so far. All supported simple types are listed in `org.apache.flink.table.api.Types`. The following table summarizes the relation between Table API types, SQL types, and the resulting Java class. - -| Table API | SQL | Java type | -| :--------------------- | :-------------------------- | :--------------------- | -| `Types.STRING` | `VARCHAR` | `java.lang.String` | -| `Types.BOOLEAN` | `BOOLEAN` | `java.lang.Boolean` | -| `Types.BYTE` | `TINYINT` | `java.lang.Byte` | -| `Types.SHORT` | `SMALLINT` | `java.lang.Short` | -| `Types.INT` | `INTEGER, INT` | `java.lang.Integer` | -| `Types.LONG` | `BIGINT` | `java.lang.Long` | -| `Types.FLOAT` | `REAL, FLOAT` | `java.lang.Float` | -| `Types.DOUBLE` | `DOUBLE` | `java.lang.Double` | -| `Types.DECIMAL` | `DECIMAL` | `java.math.BigDecimal` | -| `Types.DATE` | `DATE` | `java.sql.Date` | -| `Types.TIME` | `TIME` | `java.sql.Time` | -| `Types.TIMESTAMP` | `TIMESTAMP(3)` | `java.sql.Timestamp` | -| `Types.INTERVAL_MONTHS`| `INTERVAL YEAR TO MONTH` | `java.lang.Integer` | -| `Types.INTERVAL_MILLIS`| `INTERVAL DAY TO SECOND(3)` | `java.lang.Long` | -| `Types.PRIMITIVE_ARRAY`| `ARRAY` | e.g. `int[]` | -| `Types.OBJECT_ARRAY` | `ARRAY` | e.g. `java.lang.Byte[]`| -| `Types.MAP` | `MAP` | `java.util.HashMap` | - - -Advanced types such as generic types, composite types (e.g. POJOs or Tuples), and array types (object or primitive arrays) can be fields of a row. - -Generic types are treated as a black box within Table API and SQL yet. - -Composite types, however, are fully supported types where fields of a composite type can be accessed using the `.get()` operator in Table API and dot operator (e.g. `MyTable.pojoColumn.myField`) in SQL. Composite types can also be flattened using `.flatten()` in Table API or `MyTable.pojoColumn.*` in SQL. - -Array types can be accessed using the `myArray.at(1)` operator in Table API and `myArray[1]` operator in SQL. Array literals can be created using `array(1, 2, 3)` in Table API and `ARRAY[1, 2, 3]` in SQL. - -{% top %} - -Built-in Functions ----------------- - -Both the Table API and SQL come with a set of built-in functions for data transformations. This section gives a brief overview of the available functions so far. - -<div class="codetabs" markdown="1"> -<div data-lang="java" markdown="1"> - -<table class="table table-bordered"> - <thead> - <tr> - <th class="text-left" style="width: 40%">Comparison functions</th> - <th class="text-center">Description</th> - </tr> - </thead> - - <tbody> - - <tr> - <td> - {% highlight java %} -ANY === ANY -{% endhighlight %} - </td> - <td> - <p>Equals.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -ANY !== ANY -{% endhighlight %} - </td> - <td> - <p>Not equal.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -ANY > ANY -{% endhighlight %} - </td> - <td> - <p>Greater than.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -ANY >= ANY -{% endhighlight %} - </td> - <td> - <p>Greater than or equal.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -ANY < ANY -{% endhighlight %} - </td> - <td> - <p>Less than.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -ANY <= ANY -{% endhighlight %} - </td> - <td> - <p>Less than or equal.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -ANY.isNull -{% endhighlight %} - </td> - <td> - <p>Returns true if the given expression is null.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -ANY.isNotNull -{% endhighlight %} - </td> - <td> - <p>Returns true if the given expression is not null.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -STRING.like(STRING) -{% endhighlight %} - </td> - <td> - <p>Returns true, if a string matches the specified LIKE pattern. E.g. "Jo_n%" matches all strings that start with "Jo(arbitrary letter)n".</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -STRING.similar(STRING) -{% endhighlight %} - </td> - <td> - <p>Returns true, if a string matches the specified SQL regex pattern. E.g. "A+" matches all strings that consist of at least one "A".</p> - </td> - </tr> - - </tbody> -</table> - -<table class="table table-bordered"> - <thead> - <tr> - <th class="text-left" style="width: 40%">Logical functions</th> - <th class="text-center">Description</th> - </tr> - </thead> - - <tbody> - - <tr> - <td> - {% highlight java %} -boolean1 || boolean2 -{% endhighlight %} - </td> - <td> - <p>Returns true if <i>boolean1</i> is true or <i>boolean2</i> is true. Supports three-valued logic.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -boolean1 && boolean2 -{% endhighlight %} - </td> - <td> - <p>Returns true if <i>boolean1</i> and <i>boolean2</i> are both true. Supports three-valued logic.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -!BOOLEAN -{% endhighlight %} - </td> - <td> - <p>Returns true if boolean expression is not true; returns null if boolean is null.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -BOOLEAN.isTrue -{% endhighlight %} - </td> - <td> - <p>Returns true if the given boolean expression is true. False otherwise (for null and false).</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -BOOLEAN.isFalse -{% endhighlight %} - </td> - <td> - <p>Returns true if given boolean expression is false. False otherwise (for null and true).</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -BOOLEAN.isNotTrue -{% endhighlight %} - </td> - <td> - <p>Returns true if the given boolean expression is not true (for null and false). False otherwise.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -BOOLEAN.isNotFalse -{% endhighlight %} - </td> - <td> - <p>Returns true if given boolean expression is not false (for null and true). False otherwise.</p> - </td> - </tr> - - </tbody> -</table> - - -<table class="table table-bordered"> - <thead> - <tr> - <th class="text-left" style="width: 40%">Arithmetic functions</th> - <th class="text-center">Description</th> - </tr> - </thead> - - <tbody> - - <tr> - <td> - {% highlight java %} -+ numeric -{% endhighlight %} - </td> - <td> - <p>Returns <i>numeric</i>.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -- numeric -{% endhighlight %} - </td> - <td> - <p>Returns negative <i>numeric</i>.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -numeric1 + numeric2 -{% endhighlight %} - </td> - <td> - <p>Returns <i>numeric1</i> plus <i>numeric2</i>.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -numeric1 - numeric2 -{% endhighlight %} - </td> - <td> - <p>Returns <i>numeric1</i> minus <i>numeric2</i>.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -numeric1 * numeric2 -{% endhighlight %} - </td> - <td> - <p>Returns <i>numeric1</i> multiplied by <i>numeric2</i>.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -numeric1 / numeric2 -{% endhighlight %} - </td> - <td> - <p>Returns <i>numeric1</i> divided by <i>numeric2</i>.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -numeric1.power(numeric2) -{% endhighlight %} - </td> - <td> - <p>Returns <i>numeric1</i> raised to the power of <i>numeric2</i>.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -NUMERIC.abs() -{% endhighlight %} - </td> - <td> - <p>Calculates the absolute value of given value.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -numeric1 % numeric2 -{% endhighlight %} - </td> - <td> - <p>Returns the remainder (modulus) of <i>numeric1</i> divided by <i>numeric2</i>. The result is negative only if <i>numeric1</i> is negative.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -NUMERIC.sqrt() -{% endhighlight %} - </td> - <td> - <p>Calculates the square root of a given value.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -NUMERIC.ln() -{% endhighlight %} - </td> - <td> - <p>Calculates the natural logarithm of given value.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -NUMERIC.log10() -{% endhighlight %} - </td> - <td> - <p>Calculates the base 10 logarithm of given value.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -NUMERIC.exp() -{% endhighlight %} - </td> - <td> - <p>Calculates the Euler's number raised to the given power.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -NUMERIC.ceil() -{% endhighlight %} - </td> - <td> - <p>Calculates the smallest integer greater than or equal to a given number.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -NUMERIC.floor() -{% endhighlight %} - </td> - <td> - <p>Calculates the largest integer less than or equal to a given number.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -NUMERIC.sin() -{% endhighlight %} - </td> - <td> - <p>Calculates the sine of a given number.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -NUMERIC.cos() -{% endhighlight %} - </td> - <td> - <p>Calculates the cosine of a given number.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -NUMERIC.tan() -{% endhighlight %} - </td> - <td> - <p>Calculates the tangent of a given number.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -NUMERIC.cot() -{% endhighlight %} - </td> - <td> - <p>Calculates the cotangent of a given number.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -NUMERIC.asin() -{% endhighlight %} - </td> - <td> - <p>Calculates the arc sine of a given number.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -NUMERIC.acos() -{% endhighlight %} - </td> - <td> - <p>Calculates the arc cosine of a given number.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -NUMERIC.atan() -{% endhighlight %} - </td> - <td> - <p>Calculates the arc tangent of a given number.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -NUMERIC.degrees() -{% endhighlight %} - </td> - <td> - <p>Converts <i>numeric</i> from radians to degrees.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -NUMERIC.radians() -{% endhighlight %} - </td> - <td> - <p>Converts <i>numeric</i> from degrees to radians.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -NUMERIC.sign() -{% endhighlight %} - </td> - <td> - <p>Calculates the signum of a given number.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -NUMERIC.round(INT) -{% endhighlight %} - </td> - <td> - <p>Rounds the given number to <i>integer</i> places right to the decimal point.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -pi() -{% endhighlight %} - </td> - <td> - <p>Returns a value that is closer than any other value to pi.</p> - </td> - </tr> - - </tbody> -</table> - -<table class="table table-bordered"> - <thead> - <tr> - <th class="text-left" style="width: 40%">String functions</th> - <th class="text-center">Description</th> - </tr> - </thead> - - <tbody> - - <tr> - <td> - {% highlight java %} -STRING + STRING -{% endhighlight %} - </td> - <td> - <p>Concatenates two character strings.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -STRING.charLength() -{% endhighlight %} - </td> - <td> - <p>Returns the length of a String.</p> - </td> - </tr> - - <tr> - <td> - {% highlight java %} -STRING.upperCase() -{% endhighlight %} - </td> - <td> - <p>Returns all of the characters in a string in <TRUNCATED>
