This is an automated email from the ASF dual-hosted git repository. jark pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/flink.git
commit 5054a8e057d0e3c83c17b8c2588cdda485305435 Author: luoyuxia <[email protected]> AuthorDate: Mon Aug 29 15:21:01 2022 +0800 [FLINK-29025][docs] add transform page for Hive dialect --- .../hiveDialect/Queries/transform.md | 129 +++++++++++++++++++++ .../hiveDialect/Queries/transform.md | 129 +++++++++++++++++++++ 2 files changed, 258 insertions(+) diff --git a/docs/content.zh/docs/dev/table/hiveCompatibility/hiveDialect/Queries/transform.md b/docs/content.zh/docs/dev/table/hiveCompatibility/hiveDialect/Queries/transform.md new file mode 100644 index 00000000000..1ba9a9c0cfe --- /dev/null +++ b/docs/content.zh/docs/dev/table/hiveCompatibility/hiveDialect/Queries/transform.md @@ -0,0 +1,129 @@ +--- +title: "Transform Clause" +weight: 10 +type: docs +--- +<!-- +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. +--> + +# Transform Clause + +## Description + +The `TRANSFORM` clause allows user to transform inputs using user-specified command or script. + +## Syntax + +```sql +rowFormat + : ROW FORMAT + (DELIMITED [FIELDS TERMINATED BY char] + [COLLECTION ITEMS TERMINATED BY char] + [MAP KEYS TERMINATED BY char] + [ESCAPED BY char] + [LINES SEPARATED BY char] + | + SERDE serde_name [WITH SERDEPROPERTIES + property_name=property_value, + property_name=property_value, ...]) + +outRowFormat : rowFormat +inRowFormat : rowFormat +outRecordReader : RECORDREADER className +inRecordWriter: RECORDWRITER record_write_class + +query: + SELECT TRANSFORM '(' expression [ , ... ] ')' + ( inRowFormat )? + ( inRecordWriter )? + USING command_or_script + ( AS colName ( colType )? [, ... ] )? + ( outRowFormat )? ( outRecordReader )? +``` + +{{< hint warning >}} +**Note:** + +- `MAP ..` and `REDUCE ..` are syntactic transformations of `SELECT TRANSFORM ( ... )` in Hive dialect for such query. + So you can use `MAP` / `REDUCE` to replace `SELECT TRANSFORM`. + {{< /hint >}} + +## Parameters + +- inRowFormat + + Specific use what row format to feed to input data into the running script. + By default, columns will be transformed to `STRING` and delimited by `TAB` before feeding to the user script; + Similarly, all `NULL` values will be converted to the literal string `\N` in order to differentiate `NULL` values from empty strings. + +- outRowFormat + + Specific use what row format to read the output from the running script. + By default, the standard output of the user script will be treated as TAB-separated `STRING` columns, + any cell containing only `\N` will be re-interpreted as a `NULL`, + and then the resulting `STRING` column will be cast to the data type specified in the table declaration in the usual way. + +- inRecordWriter + Specific use what writer(fully-qualified class name) to write the input data. The default is `org.apache.hadoop.hive.ql.exec.TextRecordWriter` + +- outRecordReader + Specific use what reader(fully-qualified class name) to read the output data. The default is `org.apache.hadoop.hive.ql.exec.TextRecordReader` + +- command_or_script + Specifies a command or a path to script to process data. + + {{< hint warning >}} + **Note:** + + Add a script file and then transform input using the script is not supported yet. + {{< /hint >}} + +- colType + Specific the output of the command/script should be cast what data type. By default, it will be `STRING` data type. + + +For the clause `( AS colName ( colType )? [, ... ] )?`, please be aware the following behavior: +- If the actual number of output columns is less than user specified output columns, additional user specified out columns will be filled with NULL. +- If the actual number of output columns is more than user specified output columns, the actual output will be truncated, keeping the corresponding columns. +- If user don't specific the clause `( AS colName ( colType )? [, ... ] )?`, the default output schema is `(key: STRING, value: STRING)`. + The key column contains all the characters before the first tab and the value column contains the remaining characters after the first tab. + If there is no tab, it will return the NULL value for the second column `value`. + Note that this is different from specifying AS `key, value` because in that case, `value` will only contain the portion between the first tab and the second tab if there are multiple tabs. + + +## Examples + +```sql +CREATE TABLE src(key string, value string); +-- transform using +SELECT TRANSFORM(key, value) using 'script' from t1; + +-- transform using with specific record writer and record reader +SELECT TRANSFORM(key, value) ROW FORMAT SERDE 'MySerDe' + WITH SERDEPROPERTIES ('p1'='v1','p2'='v2') + RECORDWRITER 'MyRecordWriter' + using 'script' + ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' + RECORDREADER 'MyRecordReader' from src; + +-- use keyword MAP instead of TRANSFORM +FROM src INSERT OVERWRITE TABLE dest1 MAP src.key, CAST(src.key / 10 AS INT) using 'script' as (c1, c2); + +-- specific the output of transform +SELECT TRANSFORM(column) USING 'script' AS c1, c2; +SELECT TRANSFORM(column) USING 'script' AS(c1 INT, c2 INT); +``` diff --git a/docs/content/docs/dev/table/hiveCompatibility/hiveDialect/Queries/transform.md b/docs/content/docs/dev/table/hiveCompatibility/hiveDialect/Queries/transform.md new file mode 100644 index 00000000000..1ba9a9c0cfe --- /dev/null +++ b/docs/content/docs/dev/table/hiveCompatibility/hiveDialect/Queries/transform.md @@ -0,0 +1,129 @@ +--- +title: "Transform Clause" +weight: 10 +type: docs +--- +<!-- +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. +--> + +# Transform Clause + +## Description + +The `TRANSFORM` clause allows user to transform inputs using user-specified command or script. + +## Syntax + +```sql +rowFormat + : ROW FORMAT + (DELIMITED [FIELDS TERMINATED BY char] + [COLLECTION ITEMS TERMINATED BY char] + [MAP KEYS TERMINATED BY char] + [ESCAPED BY char] + [LINES SEPARATED BY char] + | + SERDE serde_name [WITH SERDEPROPERTIES + property_name=property_value, + property_name=property_value, ...]) + +outRowFormat : rowFormat +inRowFormat : rowFormat +outRecordReader : RECORDREADER className +inRecordWriter: RECORDWRITER record_write_class + +query: + SELECT TRANSFORM '(' expression [ , ... ] ')' + ( inRowFormat )? + ( inRecordWriter )? + USING command_or_script + ( AS colName ( colType )? [, ... ] )? + ( outRowFormat )? ( outRecordReader )? +``` + +{{< hint warning >}} +**Note:** + +- `MAP ..` and `REDUCE ..` are syntactic transformations of `SELECT TRANSFORM ( ... )` in Hive dialect for such query. + So you can use `MAP` / `REDUCE` to replace `SELECT TRANSFORM`. + {{< /hint >}} + +## Parameters + +- inRowFormat + + Specific use what row format to feed to input data into the running script. + By default, columns will be transformed to `STRING` and delimited by `TAB` before feeding to the user script; + Similarly, all `NULL` values will be converted to the literal string `\N` in order to differentiate `NULL` values from empty strings. + +- outRowFormat + + Specific use what row format to read the output from the running script. + By default, the standard output of the user script will be treated as TAB-separated `STRING` columns, + any cell containing only `\N` will be re-interpreted as a `NULL`, + and then the resulting `STRING` column will be cast to the data type specified in the table declaration in the usual way. + +- inRecordWriter + Specific use what writer(fully-qualified class name) to write the input data. The default is `org.apache.hadoop.hive.ql.exec.TextRecordWriter` + +- outRecordReader + Specific use what reader(fully-qualified class name) to read the output data. The default is `org.apache.hadoop.hive.ql.exec.TextRecordReader` + +- command_or_script + Specifies a command or a path to script to process data. + + {{< hint warning >}} + **Note:** + + Add a script file and then transform input using the script is not supported yet. + {{< /hint >}} + +- colType + Specific the output of the command/script should be cast what data type. By default, it will be `STRING` data type. + + +For the clause `( AS colName ( colType )? [, ... ] )?`, please be aware the following behavior: +- If the actual number of output columns is less than user specified output columns, additional user specified out columns will be filled with NULL. +- If the actual number of output columns is more than user specified output columns, the actual output will be truncated, keeping the corresponding columns. +- If user don't specific the clause `( AS colName ( colType )? [, ... ] )?`, the default output schema is `(key: STRING, value: STRING)`. + The key column contains all the characters before the first tab and the value column contains the remaining characters after the first tab. + If there is no tab, it will return the NULL value for the second column `value`. + Note that this is different from specifying AS `key, value` because in that case, `value` will only contain the portion between the first tab and the second tab if there are multiple tabs. + + +## Examples + +```sql +CREATE TABLE src(key string, value string); +-- transform using +SELECT TRANSFORM(key, value) using 'script' from t1; + +-- transform using with specific record writer and record reader +SELECT TRANSFORM(key, value) ROW FORMAT SERDE 'MySerDe' + WITH SERDEPROPERTIES ('p1'='v1','p2'='v2') + RECORDWRITER 'MyRecordWriter' + using 'script' + ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' + RECORDREADER 'MyRecordReader' from src; + +-- use keyword MAP instead of TRANSFORM +FROM src INSERT OVERWRITE TABLE dest1 MAP src.key, CAST(src.key / 10 AS INT) using 'script' as (c1, c2); + +-- specific the output of transform +SELECT TRANSFORM(column) USING 'script' AS c1, c2; +SELECT TRANSFORM(column) USING 'script' AS(c1 INT, c2 INT); +```
