[
https://issues.apache.org/jira/browse/TRAFODION-2439?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15828515#comment-15828515
]
ASF GitHub Bot commented on TRAFODION-2439:
-------------------------------------------
Github user DaveBirdsall commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/913#discussion_r96701030
--- Diff: core/sql/regress/hive/EXPECTED007 ---
@@ -0,0 +1,370 @@
+>>obey TEST007(setup);
+>>set schema hive.hive;
+
+--- SQL operation complete.
+>>cqd HIVE_MAX_STRING_LENGTH '20' ;
+
+--- SQL operation complete.
+>>
+>>obey TEST007(tests);
+>>-- tests for views on hive tables
+>>sh echo "drop table thive1;" > TEST007_junk;
+>>sh regrhive.ksh -f TEST007_junk;
+>>sh echo "create table thive1(a int, b int);" > TEST007_junk;
+>>sh regrhive.ksh -f TEST007_junk;
+>>sh echo "insert into thive1 values (1, 2);" > TEST007_junk;
+>>sh regrhive.ksh -f TEST007_junk;
+>>
+>>sh echo "drop table thive2;" > TEST007_junk;
+>>sh regrhive.ksh -f TEST007_junk;
+>>sh echo "create table thive2(a int, b int);" > TEST007_junk;
+>>sh regrhive.ksh -f TEST007_junk;
+>>sh echo "insert into thive2 values (1, 2);" > TEST007_junk;
+>>sh regrhive.ksh -f TEST007_junk;
+>>
+>>create external table thive2 for hive.hive.thive2;
+
+--- SQL operation complete.
+>>
+>>create view trafodion.sch.vhive1 as select * from thive1;
+
+--- SQL operation complete.
+>>showddl hive.hive.thive1;
+
+/* Hive DDL */
+CREATE TABLE THIVE1
+ (
+ A int
+ , B int
+ )
+ stored as textfile
+;
+
+/* Trafodion DDL */
+
+CREATE EXTERNAL TABLE THIVE1
+ FOR HIVE.HIVE.THIVE1
+;
+
+--- SQL operation complete.
+>>
+>>create view trafodion.sch.vhive11 as select * from thive2;
+
+--- SQL operation complete.
+>>create view trafodion.sch.vhive2 as select x.a, y.b from thive1 x,
thive2 y
++> where x.a < 2 and x.b = y.b;
+
+--- SQL operation complete.
+>>create view trafodion.sch.vhive3 as select * from trafodion.sch.vhive2;
+
+--- SQL operation complete.
+>>
+>>-- view on hive and traf tables
+>>drop table if exists trafodion.sch.thbase1;
+
+--- SQL operation complete.
+>>create table trafodion.sch.thbase1 (aa int, bb int);
+
+--- SQL operation complete.
+>>create view trafodion.sch.vhivehbase as select * from
++> trafodion.sch.thbase1, thive2 where a = aa;
+
+--- SQL operation complete.
+>>insert into trafodion.sch.thbase1 values (1,2);
+
+--- 1 row(s) inserted.
+>>invoke trafodion.sch.vhivehbase;
+
+-- Definition of Trafodion view TRAFODION.SCH.VHIVEHBASE
+-- Definition current Mon Jan 16 18:43:12 2017
+
+ (
+ AA INT DEFAULT NULL
+ , BB INT DEFAULT NULL
+ , A INT DEFAULT NULL
+ , B INT DEFAULT NULL
+ )
+
+--- SQL operation complete.
+>>select * from trafodion.sch.vhivehbase;
+
+AA BB A B
+----------- ----------- ----------- -----------
+
+ 1 2 1 2
+
+--- 1 row(s) selected.
+>>
+>>select * from thive1;
+
+A B
+----------- -----------
+
+ 1 2
+
+--- 1 row(s) selected.
+>>insert into trafodion.sch.vhive1 values (3,4);
+
+--- 1 row(s) inserted.
+>>select * from thive1;
+
+A B
+----------- -----------
+
+ 1 2
+ 3 4
+
+--- 2 row(s) selected.
+>>
+>>prepare s from select * from trafodion.sch.vhive1;
+
+--- SQL command prepared.
+>>explain options 'f' s;
+
+LC RC OP OPERATOR OPT DESCRIPTION CARD
+---- ---- ---- -------------------- -------- --------------------
---------
+
+1 . 2 root
1.00E+002
+. . 1 hive_scan THIVE1
1.00E+002
+
+--- SQL operation complete.
+>>execute s;
+
+A B
+----------- -----------
+
+ 1 2
+ 3 4
+
+--- 2 row(s) selected.
+>>
+>>prepare s from select * from trafodion.sch.vhive2;
+
+--- SQL command prepared.
+>>explain options 'f' s;
+
+LC RC OP OPERATOR OPT DESCRIPTION CARD
+---- ---- ---- -------------------- -------- --------------------
---------
+
+3 . 4 root
1.65E+003
+2 1 3 hybrid_hash_join
1.65E+003
+. . 2 hive_scan THIVE2
1.00E+002
+. . 1 hive_scan THIVE1
3.29E+001
+
+--- SQL operation complete.
+>>execute s;
+
+A B
+----------- -----------
+
+ 1 2
+
+--- 1 row(s) selected.
+>>
+>>prepare s from select * from trafodion.sch.vhive3;
+
+--- SQL command prepared.
+>>explain options 'f' s;
+
+LC RC OP OPERATOR OPT DESCRIPTION CARD
+---- ---- ---- -------------------- -------- --------------------
---------
+
+3 . 4 root
1.65E+003
+2 1 3 hybrid_hash_join
1.65E+003
+. . 2 hive_scan THIVE2
1.00E+002
+. . 1 hive_scan THIVE1
3.29E+001
+
+--- SQL operation complete.
+>>execute s;
+
+A B
+----------- -----------
+
+ 1 2
+
+--- 1 row(s) selected.
+>>
+>>get tables in view trafodion.sch.vhive1;
+
+Tables in View SCH.VHIVE1
+=========================
+
+HIVE.HIVE.THIVE1
+
+--- SQL operation complete.
+>>get tables in view trafodion.sch.vhive11;
+
+Tables in View SCH.VHIVE11
+==========================
+
+HIVE.HIVE.THIVE2
+
+--- SQL operation complete.
+>>
+>>get all tables in view trafodion.sch.vhive3;
+
+Tables in View SCH.VHIVE3
+=========================
+
+HIVE.HIVE.THIVE1
+HIVE.HIVE.THIVE2
+
+--- SQL operation complete.
+>>get views in view trafodion.sch.vhive3;
+
+Views in View SCH.VHIVE3
+========================
+
+TRAFODION.SCH.VHIVE2
+
+--- SQL operation complete.
+>>get views on table hive.hive.thive1;
+
+Views on Table _HV_HIVE_.THIVE1
+===============================
+
+SCH.VHIVE1
+SCH.VHIVE2
+
+--- SQL operation complete.
+>>get all views on table hive.hive.thive2;
+
+Views on Table _HV_HIVE_.THIVE2
+===============================
+
+SCH.VHIVE11
+SCH.VHIVE2
+SCH.VHIVE3
+SCH.VHIVEHBASE
+
+--- SQL operation complete.
+>>
+>>drop external table thive2 for hive.hive.thive2 cascade;
+
+--- SQL operation complete.
+>>showddl hive.hive.thive2;
+
+/* Hive DDL */
+CREATE TABLE THIVE2
+ (
+ A int
+ , B int
+ )
+ stored as textfile
+;
+
+--- SQL operation complete.
+>>showddl trafodion.sch.vhive3;
+
+*** ERROR[4082] Object TRAFODION.SCH.VHIVE3 does not exist or is
inaccessible.
+
+--- SQL operation failed with errors.
+>>get all views on table hive.hive.thive2;
+
+--- SQL operation complete.
+>>
+>>obey TEST007(error_tests);
+>>
+>>create view trafodion.sch.vhive4 as select x.a, y.b from thive1 x,
thive1 y;
+
+--- SQL operation complete.
+>>
+>>-- insert not allowed
+>>insert into trafodion.sch.vhive4 values (3,4);
+
+*** ERROR[4027] Table or view TRAFODION.SCH.VHIVE4 does not permit
insertions.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>
+>>-- del/upd not allowed
+>>delete from trafodion.sch.vhive1;
+
+*** ERROR[4223] Update/Delete on Hive table is not supported in this
software version.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>update trafodion.sch.vhive1 set b = 1;
+
+*** ERROR[4223] Update/Delete on Hive table is not supported in this
software version.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>
+>>-- traf view is not updatable
+>>delete from trafodion.sch.vhive4;
+
+*** ERROR[4028] Table or view TRAFODION.SCH.VHIVE4 is not updatable.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>
+>>-- traf view must be in traf cat/sch
+>>create view vhive5 as select * from thive1;
+
+*** ERROR[3242] This statement is not supported. Reason: DDL operations
can only be done on trafodion or external tables.
--- End diff --
Might be a little clearer to say that views can only be created in a
trafodion schema. (The DDL operation is on two objects, thive1 and vhive5, but
thive1 is not limited to being a trafodion or external table, and vhive5
doesn't exist yet.)
> add/externalize support to create trafodion views on hive tables
> ----------------------------------------------------------------
>
> Key: TRAFODION-2439
> URL: https://issues.apache.org/jira/browse/TRAFODION-2439
> Project: Apache Trafodion
> Issue Type: New Feature
> Reporter: Anoop Sharma
> Assignee: Anoop Sharma
> Priority: Minor
>
> this enhancement is to add support for creating views on hive tables.
> Views will be created in trafodion schema and can be accessed as
> a regular view.
> This jira will be updated with details on semantics and restrictions
> of creating views on hive tables.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
