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/03/20 00:36:45 UTC
[1/2] trafodion git commit: Add Examples for *CREATE EXTERNAL TABLE
Statement* in *Trafodion SQL Reference Manual* 2
Repository: trafodion
Updated Branches:
refs/heads/master 8f7cc7cf7 -> 3e26f8621
Add Examples for *CREATE EXTERNAL TABLE Statement* in *Trafodion SQL Reference Manual* 2
Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/c91bb569
Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/c91bb569
Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/c91bb569
Branch: refs/heads/master
Commit: c91bb569fec90afd2c8bbe10e7b9136115fbb07d
Parents: 88e4752
Author: liu.yu <li...@apache.org>
Authored: Mon Mar 12 18:16:11 2018 +0800
Committer: liu.yu <li...@apache.org>
Committed: Mon Mar 12 18:16:11 2018 +0800
----------------------------------------------------------------------
.../src/asciidoc/_chapters/sql_statements.adoc | 142 +++++++++++++++++++
1 file changed, 142 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/trafodion/blob/c91bb569/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
index 48c2f4f..142af68 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
@@ -3500,6 +3500,148 @@ CREATE TABLE t2 (c1 int, c2 char (50) UPSHIFT NOT NULL) AS SELECT * FROM t1;
```
<<<
+[[create_table_examples_create_external_table]]
+==== Examples of CREATE EXTERNAL TABLE
+
+This example compares the execution time of using external table and not using external table when reading hive tables.
+
+The former takes less time than the latter, since the trafodion external table supplies upper bounds for varchar lengths, which may lead to better plans and/or run-time behavior.
+
+TIP: Either running UPDATE STATISTICS or using a trafodion external table may improve performance. To get full performance benefit, you must run UPDATE STATISTICS and use the trafodion external table.
+
+This is the definition of the *hive table* _test_mix_ which has a trafodion external table, the size is 137.6G.
+
+```
+SQL>SHOWDDL test_mix;
+
+CREATE EXTERNAL TABLE test_mix(
+ mix_id int not null not droppable primary key,
+ mix_age int,
+ mix_name string,
+ mix_timestamp01 string,
+ mix_timestamp02 string,
+ mix_other01 string,
+ mix_other02 string,
+ mix_other03 string,
+ mix_other04 string,
+ mix_other05 string,
+ mix_other06 string,
+ mix_other07 string,
+ mix_other08 string,
+ mix_other09 string,
+ mix_other10 string,
+ mix_other11 string,
+ mix_other12 string,
+ mix_other13 string,
+ mix_other14 string,
+ mix_other15 string
+ )
+row format delimited fields terminated by '|'
+ location '/user/trafodion/data/ExternalTable_data';
+-- 01-06 short 06-11medium 12-15 long
+```
+
+This is the definition of the *trafodion external table* _test_mix_, it has the same structure and size as the hive table _test_mix_.
+
+```
+SQL>SHOWDDL text_mix;
+
+CREATE EXTERNAL TABLE test_mix(
+ mix_id int,
+ mix_age int,
+ mix_name varchar(20),
+ mix_timestamp01 timestamp,
+ mix_timestamp02 varchar(20),
+ mix_other01 varchar(12),
+ mix_other02 varchar(12),
+ mix_other03 varchar(12),
+ mix_other04 varchar(12),
+ mix_other05 varchar(12),
+ mix_other06 varchar(12),
+ mix_other07 varchar(64),
+ mix_other08 varchar(64),
+ mix_other09 varchar(64),
+ mix_other10 varchar(64),
+ mix_other11 varchar(128),
+ mix_other12 varchar(128),
+ mix_other13 varchar(128),
+ mix_other14 varchar(1024),
+ mix_other15 varchar(1024)
+ )for hive.hive.test_mix;
+-- 01-06 short 07-11medium 12-15 long
+```
+
+* When executing the following query:
+
++
+```
+SELECT [LAST 1] * FROM hive.hive.test_mix WHERE mix_other02 = 'Ot';
+```
+
++
+it takes approximately *6 minutes* (average value) to get the result using the trafodion external table.
+
++
+[cols="20%,20%,20%,20%,20%",options="header"]
+|=====
+| | First Result | Second Result | Third Result | Average Value
+| Start Time | 2018/03/07 18:40:31.655159 | 2018/03/07 09:37:50.801345 | 2018/03/07 09:45:05.921706 |
+| End Time | 2018/03/07 18:49:08.879780 | 2018/03/07 09:43:16.695492 | 2018/03/07 09:48:58.251764 |
+| Elapsed Time | 2018/03/07 00:08:37.224621 | 2018/03/07 00:05:25.894147 | 2018/03/07 00:03:52.330058 | 00:06:12.23
+| Compile Time | 2018/03/07 00:00:03.497624 | 2018/03/07 00:00:11.595054 | 2018/03/07 00:00:00.551781 | 00:00:04.8
+| Execution Time | 2018/03/07 00:08:33.715742 | 2018/03/07 00:05:14.295840 | 2018/03/07 00:03:51.708673 | *00:06:12*
+|=====
+
++
+while it takes approximately *14 minutes* (average value) to get the result without using the trafodion external table.
+
++
+[cols="20%,20%,20%,20%,20%",options="header"]
+|=====
+| | First Result | Second Result | Third Result | Average Value
+| Start Time | 2018/03/07 13:33:46.722646 | 2018/03/07 14:39:30.323730 | 2018/03/07 14:54:58.177258 |
+| End Time | 2018/03/07 13:48:35.028916 | 2018/03/07 14:53:53.887911 | 2018/03/07 15:09:11.517646 |
+| Elapsed Time | 2018/03/07 00:14:48.306270 | 2018/03/07 00:14:23.564181 | 2018/03/07 00:14:13.340388 | 00:14:28.40
+| Compile Time | 2018/03/07 00:00:00.773770 | 2018/03/07 00:00:00.388777 | 2018/03/07 00:00:14.856643 | 00:00:04
+| Execution Time | 2018/03/07 00:14:47.530017 | 2018/03/07 00:14:23.146420 | 2018/03/07 00:13:58.463850 | *00:13:58*
+|=====
+
+* When executing the following query:
+
++
+```
+SELECT [LAST 1] mix_other02, substring(mix_other12 from 1 for 10) FROM hive.hive.test_mix WHERE substring(mix_other02 from 1 for 1) = 'O';
+```
+
++
+it takes approximately *6 minutes* (average value) to get the result using the trafodion external table.
+
++
+[cols="20%,20%,20%,20%,20%",options="header"]
+|=====
+| | First Result | Second Result | Third Result | Average Value
+| Start Time | 2018/03/09 14:07:59.353015 | 2018/03/09 14:16:27.725035 | 2018/03/09 14:41:01.454408 |
+| End Time | 2018/03/09 14:15:05.979546 | 2018/03/09 14:20:44.939776 | 2018/03/09 14:46:58.238246 |
+| Elapsed Time | 2018/03/09 00:07:06.626531 | 2018/03/09 00:04:17.214741 | 2018/03/09 00:05:56.783838 | 00:05:59
+| Compile Time | 2018/03/09 00:00:00.197789 | 2018/03/09 00:00:00.296705 | 2018/03/09 00:00:00.227511 | 00:00:00.23
+| Execution Time | 2018/03/09 00:07:06.411065 | 2018/03/09 00:04:16.873090 | 2018/03/09 00:05:56.554411 | *00:05:59*
+|=====
+
++
+while it takes approximately 35 minutes (average value) to get the result without using the trafodion external table.
+
++
+[cols="20%,20%,20%,20%,20%",options="header"]
+|=====
+| | First Result | Second Result | Third Result | Average Value
+| Start Time | 2018/03/09 11:01:12.676307 | 2018/03/09 11:35:54.514479 | 2018/03/09 13:15:07.006658 |
+| End Time | 2018/03/09 11:35:16.264756 | 2018/03/09 12:11:09.587147 | 2018/03/09 13:49:23.740406 |
+| Elapsed Time | 2018/03/09 00:34:03.588449 | 2018/03/09 00:35:15.072668 | 2018/03/09 00:34:16.733748 | 34:44:00
+| Compile Time | 2018/03/09 00:00:00.703053 | 2018/03/09 00:00:00.280146 | 2018/03/09 00:00:00.536929 | 00:00:00.5
+| Execution Time | 2018/03/09 00:34:02.828529 | 2018/03/09 00:35:14.743914 | 2018/03/09 00:34:16.155336 | *34:44:00*
+|=====
+
+<<<
[[create_view_statement]]
== CREATE VIEW Statement
[2/2] trafodion git commit: Merge [TRAFODION-2989] PR 1468 Add CREATE
EXTERNAL TABLE to SQL Reference
Posted by db...@apache.org.
Merge [TRAFODION-2989] PR 1468 Add CREATE EXTERNAL TABLE to SQL Reference
Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/3e26f862
Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/3e26f862
Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/3e26f862
Branch: refs/heads/master
Commit: 3e26f8621595918edb0344e63bbbbee01f574fa3
Parents: 8f7cc7c c91bb56
Author: Dave Birdsall <db...@apache.org>
Authored: Tue Mar 20 00:35:34 2018 +0000
Committer: Dave Birdsall <db...@apache.org>
Committed: Tue Mar 20 00:35:34 2018 +0000
----------------------------------------------------------------------
.../src/asciidoc/_chapters/sql_statements.adoc | 142 +++++++++++++++++++
1 file changed, 142 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/trafodion/blob/3e26f862/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
----------------------------------------------------------------------