[
https://issues.apache.org/jira/browse/TRAFODION-19?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14735250#comment-14735250
]
ASF GitHub Bot commented on TRAFODION-19:
-----------------------------------------
Github user sureshsubbiah commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/74#discussion_r38956114
--- Diff: core/sql/regress/hive/EXPECTED009 ---
@@ -266,10 +276,301 @@ A B C
*** ERROR[8822] The statement was not prepared.
>>
+>>set schema "_HV_HIVE_";
+
+--- SQL operation complete.
+>>
+>>-- all these creates should fail, they are not supported yet
+>>create table hive_customer like hive.hive.customer;
+
+*** ERROR[1118] Creating object TRAFODION."_HV_HIVE_".HIVE_CUSTOMER is not
allowed in a reserved system schema.
+
+--- SQL operation failed with errors.
+>>create table newtable1 like hive.hive.customer;
+
+*** ERROR[1118] Creating object TRAFODION."_HV_HIVE_".NEWTABLE1 is not
allowed in a reserved system schema.
+
+--- SQL operation failed with errors.
+>>create table newtable2 like customer;
+
+*** ERROR[1118] Creating object TRAFODION."_HV_HIVE_".NEWTABLE2 is not
allowed in a reserved system schema.
+
+--- SQL operation failed with errors.
+>>create table newtable3 (a int);
+
+*** ERROR[1118] Creating object TRAFODION."_HV_HIVE_".NEWTABLE3 is not
allowed in a reserved system schema.
+
+--- SQL operation failed with errors.
+>>get tables;
+
+Tables in Schema TRAFODION._HV_HIVE_
+====================================
+
+CUSTOMER
+ITEM
+PROMOTION
+
+--- SQL operation complete.
+>>
+>>-- test creates with a different default schema
+>>create schema hive_t009;
+
+--- SQL operation complete.
+>>set schema hive_t009;
+
+--- SQL operation complete.
+>>
+>>-- these creates fail
+>>create table hive_customer like hive.hive.customer;
+
+*** ERROR[1010] The statement just entered is currently not supported.
+
+--- SQL operation failed with errors.
+>>create table newtable1 like hive.hive.customer;
+
+*** ERROR[1010] The statement just entered is currently not supported.
+
+--- SQL operation failed with errors.
+>>create external table seabase.customer like hive.hive.customer;
+
+*** ERROR[1180] Trying to create an external HIVE table with a different
schema or table name (SEABASE) than the source table (HIVE). The external
schema and table name must be the same as the source.
+
+--- SQL operation failed with errors.
+>>create external table customer1 like hive.hive.customer;
+
+*** ERROR[1180] Trying to create an external HIVE table with a different
schema or table name (CUSTOMER1) than the source table (CUSTOMER). The
external schema and table name must be the same as the source.
+
+--- SQL operation failed with errors.
+>>create table t009t1 like "_HV_SCH_T009_".t009t1;
+
+*** ERROR[1010] The statement just entered is currently not supported.
+
+--- SQL operation failed with errors.
+>>create table t009t2 as select * from "_HV_SCH_T009_".t009t2;
+
+*** ERROR[4258] Trying to access external table
TRAFODION."_HV_SCH_T009_".T009T2 through its external name format. Please use
the native table name.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>
+>>-- this create succeeds
+>>create table t009t1 as select * from hive.sch_t009.t009t1;
+
+--- 10 row(s) inserted.
+>>
+>>get tables;
+
+Tables in Schema TRAFODION.HIVE_T009
+====================================
+
+T009T1
+
+--- SQL operation complete.
+>>drop table t009t1;
+
+--- SQL operation complete.
+>>
>>drop external table "_HV_HIVE_".customer;
--- SQL operation complete.
>>drop external table item for hive.hive.item;
--- SQL operation complete.
+>>
+>>obey TEST009(test_hive2);
+>>-- drop data from the hive table and recreate with 4 columns
+>>-- this causes the external table to be invalid
+>>
+>>-- cleanup data from the old table, and create/load data with additional
column
+>>sh regrhadoop.ksh fs -rm /user/hive/exttables/t009t1/*;
+>>sh regrhive.ksh -v -f $REGRTSTDIR/TEST009_b.hive.sql;
+>>
+>>-- should fail - column mismatch
+>>select count(*) from hive.sch_t009.t009t1;
+
+*** ERROR[3078] The column list for HIVE.SCH_T009.T009T1 does not match
its external table representation defined as TRAFODION."_HV_SCH_T009_".T009T1.
--- End diff --
Should this be a warning instead of an error? The exernal table is not
being directly used here. Should the user be stopped and forced to make sure
that the two definitions are consistent?
> External tables for HIVE
> ------------------------
>
> Key: TRAFODION-19
> URL: https://issues.apache.org/jira/browse/TRAFODION-19
> Project: Apache Trafodion
> Issue Type: New Feature
> Reporter: Roberta Marton
> Assignee: Roberta Marton
> Attachments: external_table.docx
>
> Original Estimate: 336h
> Remaining Estimate: 336h
>
> Trafodion supports selecting, loading from, listing, and describing native
> HIVE tables. HIVE tables are identified by specifying a special catalog and
> schema named HIVE.HIVE.
> To select from a HIVE table named t; specify an implicit or explicit name,
> such as HIVE.HIVE.t, in a Trafodion SQL statement.
> set schema HIVE.HIVE;
> select * from t; -- implicit table name
> set schema trafodion.seabase;
> select * from HIVE.HIVE.t; -- explicit table name
> Trafodion interprets the special HIVE.HIVE catalog and schema name to be a
> native HIVE table. During preparation of an SQL statement referencing a
> native HIVE table, Trafodion contacts HIVE and obtains a description of the
> table. It then creates an internal description (NATable) of the HIVE table.
> The NATable definition is used by the compiler and code generation process to
> prepare the plan. Trafodion does not store any details in Trafodion metadata.
>
> Several Trafodion commands today, would work more effectively if we allow
> native HIVE table to be partially described in Trafodion. That is, store
> their definitions in Trafodion metadata. This JIRA describes a proposal to
> allow HIVE tables to be registered in Trafodion metadata by specifying the
> “CREATE EXTERNAL <table> TABLE …” syntax.
> Proposal
> Allow HIVE tables to be registered in Trafodion metadata through the EXTERNAL
> TABLE create option.
> CREATE EXTERNAL TABLE [IF NOT EXISTS] table LIKE hive-source-table;
> DROP EXTERNAL TABLE [IF EXISTS] table;
> hive-source-table - native HIVE table to be registered in the Trafodion
> metadata. The hive-source-table has to exist.
> table - table stored in Trafodion metadata. Initially, the table name should
> be the same as the hive-source-table name.
> <Is there any added value to make the table name different than the
> hive-source-table name?>
> The default catalog and schema names for HIVE tables are HIVE.HIVE (defined
> in ComSmallDefs as HIVE_SYSTEM_CATALOG and HIVE_SYSTEM_SCHEMA).
> To change the description, the external table needs to be dropped then
> recreated. ALTER EXTERNAL TABLE is not supported.
> The following command’s behavior changes for external tables:
> • UPDATE STATISTICS – an external table can be used to gather statistics
> for HIVE tables
> • SHOWDDL – will now be allowed on external tables
> • GRANT and REVOKE – privileges can be specified for external tables
> • SELECT and LOAD – will be allowed on external tables
> Design notes:
> When an external table is created, Trafodion:
> Reads its metadata to see if the table already exists.
> If exists and IF NOT EXISTS is specified, returns success
> If exists and IF NOT EXISTS is not specified, returns an error
> Gets the table description from HIVE
> If the source table exists, it reads HIVE metadata through existing
> interfaces
> If the source table does not exist, returns an error
> Stores the description in its metadata
> When an external table is dropped, Trafodion:
> Reads its metadata to see if the table exists
> If it does not exists and IF EXISTS is specified, returns success
> If it does not exists and IF EXISTS is not specied, returns success
> Removes the table description from its metadata found in the system
> schema (_MD_) and privilege manager schema (“_PRIVMGR_MD_”), and removing
> data from the histograms table (HIVE).
> When a query is executed, Trafodion:
> Gets the description of the table by reading metadata
> If not defined then behavior is the same as today, information is
> retrieved directly from HIVE.
> If it is defined then metadata information is retrieved from
> Trafodion.
> Creates a NATable structure from the table description, a new flag will
> be added to indicate whether or not it is an external table.
> Gets histogram information
> If histograms exist, then they are used during plan generation
> If no histograms exist, then default histograms are generated.
> Only external tables can have histograms
> Generates and executes the query – similar to current behavior
> External tables can be the target of an UPDATE STATISTICS command. When an
> external table is specified in an UPDATE STATISTICS command, Trafodion:
> gets the table description via the NATable structure as defined above
> returns an error If it is not an external table
> gathers statistics by accessing the HIVE table
> stores the results in histogram metadata in the HIVE schema
> The NATable structure contains privilege information that is checked during
> compilation. If authorization is enabled, the generation of the NATable
> structure gathers privileges:
> if it is an external table
> If privileges exist in Trafodion metadata, they are returned
> If not, then HIVE metadata is accessed to retrieve privileges
> If it is not an external table, HIVE metadata is accessed to retrieve
> privileges
> Today, SHOWDDL returns an error if a HIVE table is specified. For external
> tables, SHOWDDL displays the description of HIVE tables from data gathered
> from Trafodion metadata.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
