[
https://issues.apache.org/jira/browse/TRAFODION-19?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14628755#comment-14628755
]
Roberta Marton commented on TRAFODION-19:
-----------------------------------------
Observed the following issue while adding external support for HIVE tables:
In the NATableDB:: get method where HIVE metadata is retrieved, the following
code snippet exists:
NAString defSchema =
ActiveSchemaDB().getDefaults().getValue(HIVE_DEFAULT_SCHEMA);
defSchema.toUpper();
struct hive_tbl_desc* htbl;
NAString tableNameInt = corrName.getQualifiedNameObj().getObjectName();
NAString schemaNameInt = corrName.getQualifiedNameObj().getSchemaName();
if (corrName.getQualifiedNameObj().getUnqualifiedSchemaNameAsAnsiString()
== defSchema)
schemaNameInt = hiveMetaDB_->getDefaultSchemaName();
This assumes that most people will be specifying hive tables as
HIVE.HIVE.<table>, the cqd HIVE_DEFAULT_SCHEMA will be set to HIVE, and that in
HIVE they will be located in the default schema.
If a customer wants to use a different schema, all they need to do is specify
the name in the query: select * from hive.hive-schema.table;
In my hive environment:
show schemas:
default
hive
user1_schema
show schemas in user1_schema;
t1
show tables in hive;
mytable
The following statements work as expected:
select * from hive.user1_schema.t1;
select count(*) from hive."default".customer; -- schema name must be delimited
But if I change HIVE_DEFAULT_SCHEMA to a different value, things don't work as
I would expect:
cqd HIVE_DEFAULT_SCHEMA 'user1_schema';
select * from hive.user1_schema.t1; Hive returns "failed to retrieve metadata.
or:
cqd HIVE_DEFAULT_SCHEMA 'user1_schema'
select * from hive.hive.user1_schema.t1; Trafodion returns: Object HIVE.HIVE.T1
does not exist in hive metadata.
I assume then that users should not change HIVE_DEFAULT_SCHEMA and use HIVE (or
"default") as the schema name when wanting tables in the default schema.
But what if the customer happens to have a schema named hive? There is no way
to get information on this.
select * from hive.hive.mytable; Trafodion returns Object HIVE.HIVE.MYTABLE
does not exist in hive metadata.
In general, the default schema is 'HIVE', and Trafodion should accept a hive
schema name.
This issue will be addressed as part of adding external table support. Do we
need a separate JIRA to track this?
> 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
> 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)
