You are viewing a plain text version of this content. The canonical link for it is here.
Posted to codereview@trafodion.apache.org by liuyu000 <gi...@git.apache.org> on 2018/01/08 03:26:10 UTC
[GitHub] trafodion pull request #1377: [TRAFODION-2889] Add Examples of LOAD Statemen...
GitHub user liuyu000 opened a pull request:
https://github.com/apache/trafodion/pull/1377
[TRAFODION-2889] Add Examples of LOAD Statement for *Trafodion SQL Reference Manual*
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/liuyu000/incubator-trafodion LoadStatement3
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/trafodion/pull/1377.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #1377
----
commit c3e5db740e613fa975c75e906d6abf6f14b5c032
Author: liu.yu <yu...@...>
Date: 2018-01-08T03:15:53Z
Add Examples of LOAD Statement for *Trafodion SQL Reference Manual*
----
---
[GitHub] trafodion pull request #1377: [TRAFODION-2889] Add Examples of LOAD Statemen...
Posted by liuyu000 <gi...@git.apache.org>.
Github user liuyu000 commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1377#discussion_r160078124
--- Diff: docs/sql_reference/src/asciidoc/_chapters/sql_utilities.adoc ---
@@ -650,6 +650,111 @@ Tables must be created/dropped/altered through Hive itself.
[[load_examples]]
=== Examples of LOAD
+* This example demonstrates the LOAD statement continues loading data from source_table into target_table after ignorable data conversion error.
+
++
+Suppose that we have two tables (source_table and target_table) like this:
+
++
+```
+SQL>SELECT * FROM source_table;
+A B
+----------- ----
+
+ 1 aaaa
+ 2 bbbb
+ 3 cccc
+ 4 dd
+ 5 ee
+ 6 fff
+--- 6 row(s) selected.
+
+SQL>SHOWDDL source_table;
+CREATE TABLE TRAFODION.SEABASE.SOURCE_TABLE
+ (
+ A INT DEFAULT NULL NOT SERIALIZED
+ , B CHAR(4) CHARACTER SET ISO88591 COLLATE
+ DEFAULT DEFAULT NULL NOT SERIALIZED
+ )
+ ATTRIBUTES ALIGNED FORMAT
+;
+--- SQL operation complete.
+
+SQL>SELECT * FROM target_table;
+--- 0 row(s) selected.
+
+SQL>SHOWDDL target_table;
+CREATE TABLE TRAFODION.SEABASE.TARGET_TABLE
+ (
+ A INT DEFAULT NULL NOT SERIALIZED
+ , B CHAR(3) CHARACTER SET ISO88591 COLLATE
+ DEFAULT DEFAULT NULL NOT SERIALIZED
+ )
+ ATTRIBUTES ALIGNED FORMAT
+;
+--- SQL operation complete.
+```
+
++
+Load data from source_table into target_table when `CONTINUE ON ERROR` is not specified, the operation fails with data conversion error.
+
++
+```
+SQL>LOAD INTO target_table SELECT * FROM source_table;
+
+UTIL_OUTPUT
+---------------------------------------------------------------------------------------------
+Task: LOAD Status: Started Object: TRAFODION.SEABASE.TARGET_TABLE
+Task: CLEANUP Status: Started Time: 2018-01-03 16:15:53.222441
+Task: CLEANUP Status: Ended Time: 2018-01-03 16:15:53.250826
+Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.028
+Task: LOADING DATA Status: Started Time: 2018-01-03 16:15:53.250909
+*** ERROR[8402] A string overflow occurred during the evaluation of a character expression. Conversion of Source Type:CHAR(REC_BYTE_F_ASCII,4 BYTES,ISO88591) Source Value:aaaa to Target Type:CHAR(REC_BYTE_F_ASCII,3 BYTES,ISO88591). [2018-01-03 16:15:54]
+```
+
++
+Load data from source_table into target_table when `CONTINUE ON ERROR` is specified, the operation succeeds after ignorable data conversion error.
--- End diff --
Thanks Dave, your comments have been incorporated :)
---
[GitHub] trafodion pull request #1377: [TRAFODION-2889] Add Examples of LOAD Statemen...
Posted by liuyu000 <gi...@git.apache.org>.
Github user liuyu000 commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1377#discussion_r160078089
--- Diff: docs/sql_reference/src/asciidoc/_chapters/sql_utilities.adoc ---
@@ -650,6 +650,111 @@ Tables must be created/dropped/altered through Hive itself.
[[load_examples]]
=== Examples of LOAD
+* This example demonstrates the LOAD statement continues loading data from source_table into target_table after ignorable data conversion error.
--- End diff --
Thanks Dave, your comments have been incorporated :)
---
[GitHub] trafodion pull request #1377: [TRAFODION-2889] Add Examples of LOAD Statemen...
Posted by asfgit <gi...@git.apache.org>.
Github user asfgit closed the pull request at:
https://github.com/apache/trafodion/pull/1377
---
[GitHub] trafodion pull request #1377: [TRAFODION-2889] Add Examples of LOAD Statemen...
Posted by traflm <gi...@git.apache.org>.
Github user traflm commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1377#discussion_r160078085
--- Diff: docs/sql_reference/src/asciidoc/_chapters/sql_utilities.adoc ---
@@ -650,6 +650,111 @@ Tables must be created/dropped/altered through Hive itself.
[[load_examples]]
=== Examples of LOAD
+* This example demonstrates the LOAD statement continues loading data from source_table into target_table after ignorable data conversion error.
+
++
+Suppose that we have two tables (source_table and target_table) like this:
--- End diff --
remove the 'like this'?
Or 'like these'?
I am not sure :-)
---
[GitHub] trafodion pull request #1377: [TRAFODION-2889] Add Examples of LOAD Statemen...
Posted by DaveBirdsall <gi...@git.apache.org>.
Github user DaveBirdsall commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1377#discussion_r160072865
--- Diff: docs/sql_reference/src/asciidoc/_chapters/sql_utilities.adoc ---
@@ -650,6 +650,111 @@ Tables must be created/dropped/altered through Hive itself.
[[load_examples]]
=== Examples of LOAD
+* This example demonstrates the LOAD statement continues loading data from source_table into target_table after ignorable data conversion error.
--- End diff --
Grammar is not quite right... adding the word "how" just before "the LOAD statement" will fix it. But this doesn't quite match the content below... the content shows what happens when we don't supply CONTINUE ON ERROR, then it shows a contrasting example with CONTINUE ON ERROR. Possible wordsmith: "The examples below demonstrate how the LOAD statement behaves without and with CONTINUE ON ERROR, when ignorable data conversion errors occur."
---
[GitHub] trafodion pull request #1377: [TRAFODION-2889] Add Examples of LOAD Statemen...
Posted by traflm <gi...@git.apache.org>.
Github user traflm commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1377#discussion_r160081220
--- Diff: docs/sql_reference/src/asciidoc/_chapters/sql_utilities.adoc ---
@@ -650,6 +650,111 @@ Tables must be created/dropped/altered through Hive itself.
[[load_examples]]
=== Examples of LOAD
+* This example demonstrates the LOAD statement continues loading data from source_table into target_table after ignorable data conversion error.
+
++
+Suppose that we have two tables (source_table and target_table) like this:
--- End diff --
you are right ^_^
I learned something new.
---
[GitHub] trafodion pull request #1377: [TRAFODION-2889] Add Examples of LOAD Statemen...
Posted by DaveBirdsall <gi...@git.apache.org>.
Github user DaveBirdsall commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1377#discussion_r160072959
--- Diff: docs/sql_reference/src/asciidoc/_chapters/sql_utilities.adoc ---
@@ -650,6 +650,111 @@ Tables must be created/dropped/altered through Hive itself.
[[load_examples]]
=== Examples of LOAD
+* This example demonstrates the LOAD statement continues loading data from source_table into target_table after ignorable data conversion error.
+
++
+Suppose that we have two tables (source_table and target_table) like this:
+
++
+```
+SQL>SELECT * FROM source_table;
+A B
+----------- ----
+
+ 1 aaaa
+ 2 bbbb
+ 3 cccc
+ 4 dd
+ 5 ee
+ 6 fff
+--- 6 row(s) selected.
+
+SQL>SHOWDDL source_table;
+CREATE TABLE TRAFODION.SEABASE.SOURCE_TABLE
+ (
+ A INT DEFAULT NULL NOT SERIALIZED
+ , B CHAR(4) CHARACTER SET ISO88591 COLLATE
+ DEFAULT DEFAULT NULL NOT SERIALIZED
+ )
+ ATTRIBUTES ALIGNED FORMAT
+;
+--- SQL operation complete.
+
+SQL>SELECT * FROM target_table;
+--- 0 row(s) selected.
+
+SQL>SHOWDDL target_table;
+CREATE TABLE TRAFODION.SEABASE.TARGET_TABLE
+ (
+ A INT DEFAULT NULL NOT SERIALIZED
+ , B CHAR(3) CHARACTER SET ISO88591 COLLATE
+ DEFAULT DEFAULT NULL NOT SERIALIZED
+ )
+ ATTRIBUTES ALIGNED FORMAT
+;
+--- SQL operation complete.
+```
+
++
+Load data from source_table into target_table when `CONTINUE ON ERROR` is not specified, the operation fails with data conversion error.
+
++
+```
+SQL>LOAD INTO target_table SELECT * FROM source_table;
+
+UTIL_OUTPUT
+---------------------------------------------------------------------------------------------
+Task: LOAD Status: Started Object: TRAFODION.SEABASE.TARGET_TABLE
+Task: CLEANUP Status: Started Time: 2018-01-03 16:15:53.222441
+Task: CLEANUP Status: Ended Time: 2018-01-03 16:15:53.250826
+Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.028
+Task: LOADING DATA Status: Started Time: 2018-01-03 16:15:53.250909
+*** ERROR[8402] A string overflow occurred during the evaluation of a character expression. Conversion of Source Type:CHAR(REC_BYTE_F_ASCII,4 BYTES,ISO88591) Source Value:aaaa to Target Type:CHAR(REC_BYTE_F_ASCII,3 BYTES,ISO88591). [2018-01-03 16:15:54]
+```
+
++
+Load data from source_table into target_table when `CONTINUE ON ERROR` is specified, the operation succeeds after ignorable data conversion error.
--- End diff --
"When loading data..." is better. Also "ignorable data conversion errors" is better.
---
[GitHub] trafodion pull request #1377: [TRAFODION-2889] Add Examples of LOAD Statemen...
Posted by liuyu000 <gi...@git.apache.org>.
Github user liuyu000 commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1377#discussion_r160078097
--- Diff: docs/sql_reference/src/asciidoc/_chapters/sql_utilities.adoc ---
@@ -650,6 +650,111 @@ Tables must be created/dropped/altered through Hive itself.
[[load_examples]]
=== Examples of LOAD
+* This example demonstrates the LOAD statement continues loading data from source_table into target_table after ignorable data conversion error.
+
++
+Suppose that we have two tables (source_table and target_table) like this:
+
++
+```
+SQL>SELECT * FROM source_table;
+A B
+----------- ----
+
+ 1 aaaa
+ 2 bbbb
+ 3 cccc
+ 4 dd
+ 5 ee
+ 6 fff
+--- 6 row(s) selected.
+
+SQL>SHOWDDL source_table;
+CREATE TABLE TRAFODION.SEABASE.SOURCE_TABLE
+ (
+ A INT DEFAULT NULL NOT SERIALIZED
+ , B CHAR(4) CHARACTER SET ISO88591 COLLATE
+ DEFAULT DEFAULT NULL NOT SERIALIZED
+ )
+ ATTRIBUTES ALIGNED FORMAT
+;
+--- SQL operation complete.
+
+SQL>SELECT * FROM target_table;
+--- 0 row(s) selected.
+
+SQL>SHOWDDL target_table;
+CREATE TABLE TRAFODION.SEABASE.TARGET_TABLE
+ (
+ A INT DEFAULT NULL NOT SERIALIZED
+ , B CHAR(3) CHARACTER SET ISO88591 COLLATE
+ DEFAULT DEFAULT NULL NOT SERIALIZED
+ )
+ ATTRIBUTES ALIGNED FORMAT
+;
+--- SQL operation complete.
+```
+
++
+Load data from source_table into target_table when `CONTINUE ON ERROR` is not specified, the operation fails with data conversion error.
--- End diff --
Thanks Dave, your comments have been incorporated :)
---
[GitHub] trafodion pull request #1377: [TRAFODION-2889] Add Examples of LOAD Statemen...
Posted by DaveBirdsall <gi...@git.apache.org>.
Github user DaveBirdsall commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1377#discussion_r160072925
--- Diff: docs/sql_reference/src/asciidoc/_chapters/sql_utilities.adoc ---
@@ -650,6 +650,111 @@ Tables must be created/dropped/altered through Hive itself.
[[load_examples]]
=== Examples of LOAD
+* This example demonstrates the LOAD statement continues loading data from source_table into target_table after ignorable data conversion error.
+
++
+Suppose that we have two tables (source_table and target_table) like this:
+
++
+```
+SQL>SELECT * FROM source_table;
+A B
+----------- ----
+
+ 1 aaaa
+ 2 bbbb
+ 3 cccc
+ 4 dd
+ 5 ee
+ 6 fff
+--- 6 row(s) selected.
+
+SQL>SHOWDDL source_table;
+CREATE TABLE TRAFODION.SEABASE.SOURCE_TABLE
+ (
+ A INT DEFAULT NULL NOT SERIALIZED
+ , B CHAR(4) CHARACTER SET ISO88591 COLLATE
+ DEFAULT DEFAULT NULL NOT SERIALIZED
+ )
+ ATTRIBUTES ALIGNED FORMAT
+;
+--- SQL operation complete.
+
+SQL>SELECT * FROM target_table;
+--- 0 row(s) selected.
+
+SQL>SHOWDDL target_table;
+CREATE TABLE TRAFODION.SEABASE.TARGET_TABLE
+ (
+ A INT DEFAULT NULL NOT SERIALIZED
+ , B CHAR(3) CHARACTER SET ISO88591 COLLATE
+ DEFAULT DEFAULT NULL NOT SERIALIZED
+ )
+ ATTRIBUTES ALIGNED FORMAT
+;
+--- SQL operation complete.
+```
+
++
+Load data from source_table into target_table when `CONTINUE ON ERROR` is not specified, the operation fails with data conversion error.
--- End diff --
Grammar. Suggest replacing "Load data..." with "When loading data...". Also "a data conversion error" is slightly better.
---
[GitHub] trafodion pull request #1377: [TRAFODION-2889] Add Examples of LOAD Statemen...
Posted by liuyu000 <gi...@git.apache.org>.
Github user liuyu000 commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1377#discussion_r160079583
--- Diff: docs/sql_reference/src/asciidoc/_chapters/sql_utilities.adoc ---
@@ -650,6 +650,111 @@ Tables must be created/dropped/altered through Hive itself.
[[load_examples]]
=== Examples of LOAD
+* This example demonstrates the LOAD statement continues loading data from source_table into target_table after ignorable data conversion error.
+
++
+Suppose that we have two tables (source_table and target_table) like this:
--- End diff --
Thanks Ming :)
"this" represents the following situation (whole, singular).
Two similar examples from other manuals are attched for your reference:
1. The example in _Oracle SQL Reference Manual_
2. The example in _MySQL SQL Reference Manual_
---