You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@flink.apache.org by ja...@apache.org on 2020/01/14 03:20:14 UTC
[flink] branch release-1.10 updated: [FLINK-15567][table][docs] Add
documentation for INSERT statements for Flink SQL (#10839)
This is an automated email from the ASF dual-hosted git repository.
jark pushed a commit to branch release-1.10
in repository https://gitbox.apache.org/repos/asf/flink.git
The following commit(s) were added to refs/heads/release-1.10 by this push:
new dc0fff9 [FLINK-15567][table][docs] Add documentation for INSERT statements for Flink SQL (#10839)
dc0fff9 is described below
commit dc0fff984caffaafe52e6e42b0357cbcc46e99c9
Author: Jark Wu <ja...@apache.org>
AuthorDate: Tue Jan 14 11:19:07 2020 +0800
[FLINK-15567][table][docs] Add documentation for INSERT statements for Flink SQL (#10839)
---
docs/dev/table/sql/index.md | 1 +
docs/dev/table/sql/index.zh.md | 1 +
docs/dev/table/sql/insert.md | 181 ++++++++++++++++++++++++++++++++++++++++
docs/dev/table/sql/insert.zh.md | 178 +++++++++++++++++++++++++++++++++++++++
4 files changed, 361 insertions(+)
diff --git a/docs/dev/table/sql/index.md b/docs/dev/table/sql/index.md
index 0554054..1affb42 100644
--- a/docs/dev/table/sql/index.md
+++ b/docs/dev/table/sql/index.md
@@ -32,6 +32,7 @@ This page lists all the supported statements supported in Flink SQL for now:
- [CREATE TABLE, DATABASE, FUNCTION](create.html)
- [DROP TABLE, DATABASE, FUNCTION](drop.html)
- [ALTER TABLE, DATABASE, FUNCTION](alter.html)
+- [INSERT](insert.html)
## Data Types
diff --git a/docs/dev/table/sql/index.zh.md b/docs/dev/table/sql/index.zh.md
index 493c4b5..5cdc8d0 100644
--- a/docs/dev/table/sql/index.zh.md
+++ b/docs/dev/table/sql/index.zh.md
@@ -32,6 +32,7 @@ under the License.
- [CREATE TABLE, DATABASE, FUNCTION](create.html)
- [DROP TABLE, DATABASE, FUNCTION](drop.html)
- [ALTER TABLE, DATABASE, FUNCTION](alter.html)
+- [INSERT](insert.html)
## 数据类型
diff --git a/docs/dev/table/sql/insert.md b/docs/dev/table/sql/insert.md
new file mode 100644
index 0000000..96052ad
--- /dev/null
+++ b/docs/dev/table/sql/insert.md
@@ -0,0 +1,181 @@
+---
+title: "INSERT Statement"
+nav-parent_id: sql
+nav-pos: 5
+---
+<!--
+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.
+-->
+
+* This will be replaced by the TOC
+{:toc}
+
+INSERT statements are used to add rows to a table.
+
+## Run an INSERT statement
+
+INSERT statements are specified with the `sqlUpdate()` method of the `TableEnvironment` or executed in [SQL CLI]({{ site.baseurl }}/dev/table/sqlClient.html). The method `sqlUpdate()` for INSERT statements is a lazy execution, they will be executed only when `TableEnvironment.execute(jobName)` is invoked.
+
+The following examples show how to run an INSERT statement in `TableEnvironment` and in SQL CLI.
+
+<div class="codetabs" markdown="1">
+<div data-lang="java" markdown="1">
+{% highlight java %}
+EnvironmentSettings settings = EnvironmentSettings.newInstance()...
+TableEnvironment tEnv = TableEnvironment.create(settings);
+
+// register a source table named "Orders" and a sink table named "RubberOrders"
+tEnv.sqlUpdate("CREATE TABLE Orders (`user` BIGINT, product VARCHAR, amount INT) WITH (...)");
+tEnv.sqlUpdate("CREATE TABLE RubberOrders(product VARCHAR, amount INT) WITH (...)");
+
+// run a SQL update query on the registered source table and emit the result to registered sink table
+tEnv.sqlUpdate(
+ "INSERT INTO RubberOrders SELECT product, amount FROM Orders WHERE product LIKE '%Rubber%'");
+{% endhighlight %}
+</div>
+
+<div data-lang="scala" markdown="1">
+{% highlight scala %}
+val settings = EnvironmentSettings.newInstance()...
+val tEnv = TableEnvironment.create(settings)
+
+// register a source table named "Orders" and a sink table named "RubberOrders"
+tEnv.sqlUpdate("CREATE TABLE Orders (`user` BIGINT, product STRING, amount INT) WITH (...)")
+tEnv.sqlUpdate("CREATE TABLE RubberOrders(product STRING, amount INT) WITH (...)")
+
+// run a SQL update query on the registered source table and emit the result to registered sink table
+tEnv.sqlUpdate(
+ "INSERT INTO RubberOrders SELECT product, amount FROM Orders WHERE product LIKE '%Rubber%'")
+{% endhighlight %}
+</div>
+
+<div data-lang="python" markdown="1">
+{% highlight python %}
+settings = EnvironmentSettings.newInstance()...
+table_env = TableEnvironment.create(settings)
+
+# register a source table named "Orders" and a sink table named "RubberOrders"
+table_env.sqlUpdate("CREATE TABLE Orders (`user` BIGINT, product STRING, amount INT) WITH (...)")
+table_env.sqlUpdate("CREATE TABLE RubberOrders(product STRING, amount INT) WITH (...)")
+
+# run a SQL update query on the registered source table and emit the result to registered sink table
+table_env \
+ .sqlUpdate("INSERT INTO RubberOrders SELECT product, amount FROM Orders WHERE product LIKE '%Rubber%'")
+{% endhighlight %}
+</div>
+
+<div data-lang="SQL CLI" markdown="1">
+{% highlight sql %}
+Flink SQL> CREATE TABLE Orders (`user` BIGINT, product STRING, amount INT) WITH (...);
+[INFO] Table has been created.
+
+Flink SQL> CREATE TABLE RubberOrders(product STRING, amount INT) WITH (...);
+
+Flink SQL> SHOW TABLES;
+Orders
+RubberOrders
+
+Flink SQL> INSERT INTO RubberOrders SELECT product, amount FROM Orders WHERE product LIKE '%Rubber%';
+[INFO] Submitting SQL update statement to the cluster...
+[INFO] Table update statement has been successfully submitted to the cluster:
+{% endhighlight %}
+</div>
+</div>
+
+{% top %}
+
+## Insert from select queries
+
+Query Results can be inserted into tables by using the insert clause.
+
+### Syntax
+
+{% highlight sql %}
+
+INSERT { INTO | OVERWRITE } [catalog_name.][db_name.]table_name [PARTITION part_spec] select_statement
+
+part_spec:
+ (part_col_name1=val1 [, part_col_name2=val2, ...])
+
+{% endhighlight %}
+
+**OVERWRITE**
+
+`INSERT OVERWRITE` will overwrite any existing data in the table or partition. Otherwise, new data is appended.
+
+**PARTITION**
+
+`PARTITION` clause should contain static partition columns of this inserting.
+
+### Examples
+
+{% highlight sql %}
+-- Creates a partitioned table
+CREATE TABLE country_page_view (user STRING, cnt INT, date STRING, country STRING)
+PARTITIONED BY (date, country)
+WITH (...)
+
+-- Appends rows into the static partition (date='2019-8-30', country='China')
+INSERT INTO country_page_view PARTITION (date='2019-8-30', country='China')
+ SELECT user, cnt FROM page_view_source;
+
+-- Appends rows into partition (date, country), where date is static partition with value '2019-8-30',
+-- country is dynamic partition whose value is dynamic determined by each row.
+INSERT INTO country_page_view PARTITION (date='2019-8-30')
+ SELECT user, cnt, country FROM page_view_source;
+
+-- Overwrites rows into static partition (date='2019-8-30', country='China')
+INSERT OVERWRITE country_page_view PARTITION (date='2019-8-30', country='China')
+ SELECT user, cnt FROM page_view_source;
+
+-- Overwrites rows into partition (date, country), where date is static partition with value '2019-8-30',
+-- country is dynamic partition whose value is dynamic determined by each row.
+INSERT OVERWRITE country_page_view PARTITION (date='2019-8-30')
+ SELECT user, cnt, country FROM page_view_source;
+{% endhighlight %}
+
+
+## Insert values into tables
+
+The INSERT...VALUES statement can be used to insert data into tables directly from SQL.
+
+### Syntax
+
+{% highlight sql %}
+INSERT { INTO | OVERWRITE } [catalog_name.][db_name.]table_name VALUES values_row [, values_row ...]
+
+values_row:
+ : (val1 [, val2, ...])
+{% endhighlight %}
+
+**OVERWRITE**
+
+`INSERT OVERWRITE` will overwrite any existing data in the table. Otherwise, new data is appended.
+
+### Examples
+
+{% highlight sql %}
+
+CREATE TABLE students (name STRING, age INT, gpa DECIMAL(3, 2)) WITH (...);
+
+INSERT INTO students
+ VALUES ('fred flintstone', 35, 1.28), ('barney rubble', 32, 2.32);
+
+{% endhighlight %}
+
+{% top %}
\ No newline at end of file
diff --git a/docs/dev/table/sql/insert.zh.md b/docs/dev/table/sql/insert.zh.md
new file mode 100644
index 0000000..14ad9a4
--- /dev/null
+++ b/docs/dev/table/sql/insert.zh.md
@@ -0,0 +1,178 @@
+---
+title: "INSERT 语句"
+nav-parent_id: sql
+nav-pos: 5
+---
+<!--
+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.
+-->
+
+* This will be replaced by the TOC
+{:toc}
+
+INSERT 语句用来向表中添加行。
+
+## 执行 INSERT 语句
+
+可以使用 `TableEnvironment` 中的 `sqlUpdate()` 方法执行 INSERT 语句，也可以在 [SQL CLI]({{ site.baseurl }}/zh/dev/table/sqlClient.html) 中执行 INSERT 语句。`sqlUpdate()` 方法执行 INSERT 语句时时懒执行的，只有当`TableEnvironment.execute(jobName)`被调用时才会被执行。
+
+以下的例子展示了如何在 `TableEnvironment` 和 SQL CLI 中执行一个 INSERT 语句。
+
+<div class="codetabs" markdown="1">
+<div data-lang="java" markdown="1">
+{% highlight java %}
+EnvironmentSettings settings = EnvironmentSettings.newInstance()...
+TableEnvironment tEnv = TableEnvironment.create(settings);
+
+// 注册一个 "Orders" 源表，和 "RubberOrders" 结果表
+tEnv.sqlUpdate("CREATE TABLE Orders (`user` BIGINT, product VARCHAR, amount INT) WITH (...)");
+tEnv.sqlUpdate("CREATE TABLE RubberOrders(product VARCHAR, amount INT) WITH (...)");
+
+// 运行一个 INSERT 语句，将源表的数据输出到结果表中
+tEnv.sqlUpdate(
+ "INSERT INTO RubberOrders SELECT product, amount FROM Orders WHERE product LIKE '%Rubber%'");
+{% endhighlight %}
+</div>
+
+<div data-lang="scala" markdown="1">
+{% highlight scala %}
+val settings = EnvironmentSettings.newInstance()...
+val tEnv = TableEnvironment.create(settings)
+
+// 注册一个 "Orders" 源表，和 "RubberOrders" 结果表
+tEnv.sqlUpdate("CREATE TABLE Orders (`user` BIGINT, product STRING, amount INT) WITH (...)")
+tEnv.sqlUpdate("CREATE TABLE RubberOrders(product STRING, amount INT) WITH (...)")
+
+// 运行一个 INSERT 语句，将源表的数据输出到结果表中
+tEnv.sqlUpdate(
+ "INSERT INTO RubberOrders SELECT product, amount FROM Orders WHERE product LIKE '%Rubber%'")
+{% endhighlight %}
+</div>
+
+<div data-lang="python" markdown="1">
+{% highlight python %}
+settings = EnvironmentSettings.newInstance()...
+table_env = TableEnvironment.create(settings)
+
+# 注册一个 "Orders" 源表，和 "RubberOrders" 结果表
+table_env.sqlUpdate("CREATE TABLE Orders (`user` BIGINT, product STRING, amount INT) WITH (...)")
+table_env.sqlUpdate("CREATE TABLE RubberOrders(product STRING, amount INT) WITH (...)")
+
+# 运行一个 INSERT 语句，将源表的数据输出到结果表中
+table_env \
+ .sqlUpdate("INSERT INTO RubberOrders SELECT product, amount FROM Orders WHERE product LIKE '%Rubber%'")
+{% endhighlight %}
+</div>
+
+<div data-lang="SQL CLI" markdown="1">
+{% highlight sql %}
+Flink SQL> CREATE TABLE Orders (`user` BIGINT, product STRING, amount INT) WITH (...);
+[INFO] Table has been created.
+
+Flink SQL> CREATE TABLE RubberOrders(product STRING, amount INT) WITH (...);
+
+Flink SQL> SHOW TABLES;
+Orders
+RubberOrders
+
+Flink SQL> INSERT INTO RubberOrders SELECT product, amount FROM Orders WHERE product LIKE '%Rubber%';
+[INFO] Submitting SQL update statement to the cluster...
+[INFO] Table update statement has been successfully submitted to the cluster:
+{% endhighlight %}
+</div>
+</div>
+
+{% top %}
+
+## 将 SELECT 查询数据插入表中
+
+通过 INSERT 语句，可以将查询的结果插入到表中，
+
+### 语法
+
+{% highlight sql %}
+
+INSERT { INTO | OVERWRITE } [catalog_name.][db_name.]table_name [PARTITION part_spec] select_statement
+
+part_spec:
+ (part_col_name1=val1 [, part_col_name2=val2, ...])
+
+{% endhighlight %}
+
+**OVERWRITE**
+
+`INSERT OVERWRITE` 将会覆盖表中或分区中的任何已存在的数据。否则，新数据会追加到表中或分区中。
+
+**PARTITION**
+
+`PARTITION` 语句应该包含需要插入的静态分区列与值。
+
+### 示例
+
+{% highlight sql %}
+-- 创建一个分区表
+CREATE TABLE country_page_view (user STRING, cnt INT, date STRING, country STRING)
+PARTITIONED BY (date, country)
+WITH (...)
+
+-- 追加行到该静态分区中 (date='2019-8-30', country='China')
+INSERT INTO country_page_view PARTITION (date='2019-8-30', country='China')
+ SELECT user, cnt FROM page_view_source;
+
+-- 追加行到分区 (date, country) 中，其中 date 是静态分区 '2019-8-30'；country 是动态分区，其值由每一行动态决定
+INSERT INTO country_page_view PARTITION (date='2019-8-30')
+ SELECT user, cnt, country FROM page_view_source;
+
+-- 覆盖行到静态分区 (date='2019-8-30', country='China')
+INSERT OVERWRITE country_page_view PARTITION (date='2019-8-30', country='China')
+ SELECT user, cnt FROM page_view_source;
+
+-- 覆盖行到分区 (date, country) 中，其中 date 是静态分区 '2019-8-30'；country 是动态分区，其值由每一行动态决定
+INSERT OVERWRITE country_page_view PARTITION (date='2019-8-30')
+ SELECT user, cnt, country FROM page_view_source;
+{% endhighlight %}
+
+## 将值插入表中
+
+通过 INSERT 语句，也可以直接将值插入到表中，
+
+### 语法
+
+{% highlight sql %}
+INSERT { INTO | OVERWRITE } [catalog_name.][db_name.]table_name VALUES values_row [, values_row ...]
+
+values_row:
+ : (val1 [, val2, ...])
+{% endhighlight %}
+
+**OVERWRITE**
+
+`INSERT OVERWRITE` 将会覆盖表中的任何已存在的数据。否则，新数据会追加到表中。
+
+### 示例
+
+{% highlight sql %}
+
+CREATE TABLE students (name STRING, age INT, gpa DECIMAL(3, 2)) WITH (...);
+
+INSERT INTO students
+ VALUES ('fred flintstone', 35, 1.28), ('barney rubble', 32, 2.32);
+
+{% endhighlight %}
+
+{% top %}
\ No newline at end of file