You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by db...@apache.org on 2018/01/11 19:06:05 UTC
[2/5] trafodion git commit: Incorporate comments and Add more examples
Incorporate comments and Add more examples
Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/5e4b4761
Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/5e4b4761
Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/5e4b4761
Branch: refs/heads/master
Commit: 5e4b4761900a2f53655faba6f89c66ca3ee38e0f
Parents: c3e5db7
Author: liu.yu <yu...@esgyn.cn>
Authored: Mon Jan 8 14:18:24 2018 +0800
Committer: liu.yu <yu...@esgyn.cn>
Committed: Mon Jan 8 14:18:24 2018 +0800
----------------------------------------------------------------------
.../src/asciidoc/_chapters/sql_utilities.adoc | 76 +++++++++++++++++++-
1 file changed, 73 insertions(+), 3 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/trafodion/blob/5e4b4761/docs/sql_reference/src/asciidoc/_chapters/sql_utilities.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_utilities.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_utilities.adoc
index 13561ce..55b2712 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/sql_utilities.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/sql_utilities.adoc
@@ -650,7 +650,7 @@ 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.
+* The examples below demonstrate how the LOAD Statement behaves without and with `CONTINUE ON ERROR`, when ignorable data conversion errors occur.
+
Suppose that we have two tables (source_table and target_table) like this:
@@ -696,7 +696,7 @@ CREATE TABLE TRAFODION.SEABASE.TARGET_TABLE
```
+
-Load data from source_table into target_table when `CONTINUE ON ERROR` is not specified, the operation fails with data conversion error.
+When loading data from source_table into target_table if `CONTINUE ON ERROR` is not specified, the operation fails with a data conversion error.
+
```
@@ -713,7 +713,7 @@ Task: LOADING DATA Status: Started Time: 2018-01-03 16:15:53.250909
```
+
-Load data from source_table into target_table when `CONTINUE ON ERROR` is specified, the operation succeeds after ignorable data conversion error.
+When loading data from source_table into target_table if `CONTINUE ON ERROR` is specified, the operation succeeds after ignorable data conversion errors.
+
```
@@ -755,6 +755,76 @@ A B
--- 3 row(s) selected.
```
+* Suppose that we have two same tables (source_table and target_table) as shown in the first example.
+
+** This example explains how the LOAD statement loads data and logs error rows to the default directory `user/trafodion/bulkload/logs`.
++
+```
+SQL>LOAD WITH LOG ERROR ROWS 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:23:03.142862
+Task: CLEANUP Status: Ended Time: 2018-01-03 16:23:03.151725
+Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.009
+Logging Location: /user/trafodion/bulkload/logs/ERR_TRAFODION.SEABASE.TARGET_TABLE_20180103_082303
+Task: LOADING DATA Status: Started Time: 2018-01-03 16:23:03.151815
+ Rows Processed: 6
+ Error Rows: 3
+Task: LOADING DATA Status: Ended Time: 2018-01-03 16:23:03.920270
+Task: LOADING DATA Status: Ended Elapsed Time: 00:00:00.768
+Task: COMPLETION Status: Started Time: 2018-01-03 16:23:03.920313
+ Rows Loaded: 3
+Task: COMPLETION Status: Ended Time: 2018-01-03 16:23:04.301579
+Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.381
+
+--- SQL operation complete.
+
+SQL>select * from target_table;
+A B
+----------- ----
+ 4 dd
+ 5 ee
+ 6 fff
+--- 3 row(s) selected.
+
+[root@cent-1 bin]$ hdfs dfs -ls /user/trafodion/bulkload/logs/
+Found 1 items
+drwxr-xr-x - trafodion trafodion 0 2018-01-13 16:23
+/user/trafodion/bulkload/logs/ERR_TRAFODION.SEABASE.TARGET_TABLE_20180103_082303
+```
+
+** This example shows how the LOAD statement loads and logs error rows to the specified directory `user/trafodion/bulkload/error_log`.
++
+```
+SQL>LOAD WITH LOG ERROR ROWS TO '/BULKLOAD/ERROR_LOG' 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 17:19:43.436689
+Task: CLEANUP Status: Ended Time: 2018-01-03 17:19:43.456761
+Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.020
+Logging Location: /bulkload/error_log/ERR_TRAFODION.SEABASE.TARGET_TABLE_20180103_091943
+Task: LOADING DATA Status: Started Time: 2018-01-03 17:19:43.456804
+ Rows Processed: 6
+ Error Rows: 3
+Task: LOADING DATA Status: Ended Time: 2018-01-03 17:19:43.722825
+Task: LOADING DATA Status: Ended Elapsed Time: 00:00:00.266
+Task: COMPLETION Status: Started Time: 2018-01-03 17:19:43.722868
+ Rows Loaded: 3
+Task: COMPLETION Status: Ended Time: 2018-01-03 17:19:44.591544
+Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.869
+
+--- SQL operation complete.
+
+[root@cent-1 bin]$ hdfs dfs -ls /bulkload/error_log
+Found 1 items
+drwxr-xr-x - trafodion trafodion 0 2018-01-03 17:19
+/bulkload/error_log/ERR_TRAFODION.SEABASE.TARGET_TABLE_20180103_091943
+```
+
* For customer demographics data residing in
`/hive/tpcds/customer_demographics`, create an external Hive table using
the following Hive SQL: