[teiid-issues] [JBoss JIRA] (TEIID-4121) Enhancing the External Materialization

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-4121?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13189829#comment-13189829 ] 

Steven Hawkins commented on TEIID-4121:
---------------------------------------

> I want to create a global STATUS table, which save all View's status, no matter where materialized table located, status will always goes to that global STATUS table. It should be better if STATUS table can be created automatically in deployment, that the datasource should be verified in advance, like H2, Mysql, Infinspan.

The reluctance to move this direction has been around scope creep - there is a lot of management that comes with presuming ownership/creation of source tables.  Also there isn't really a notion of accessing a source outside of the scope of a VDB, so without some new configuration constructs the broadest level for this would be the VDB.

> From user-friendly aspect, I feel not good for configuring lots of SQL scripts and create tables manually each time if I want to use external mat. Especially, different data sources are behave differently, for example, the STATUS table's Column "Cardinality long", in latest MariaDB, the 'long' type will silently change to 'MEDIUMTEXT', so we have to use 'bigint' instead. The same as other truncate table, alter table syntax, etc, different data source also behave differently.

Except in data migration scenarios, it would not be expected that materialized views will be retargeted.  Typically materialized tables will be associated with a source based upon their usage such that the materialization enhances push down - and that shouldn't change over time.  So yes there is more burden on the user - and it is not expected that we can abstract effectively all of the creation / post creation tasks effectively without significant feature creep.  Accounting for every possible constraint, index type (hash, function based, bit map, etc.), source specific notion of truncate/swap (which is only applicable to full snapshot), etc. is a significant undertaking even for only a small number of sources. 

> IMO, we should enhance external in this term, made it more automatically, the materialized data source should be verified in advance, during configuring one option can make all things work. If not enhance in this way, at least, the document should be enhanced.

Documentation changes can of course be made at any time.  

It seems like you would first start down this path by trying to elevate / automate the mechanics of the status table, then move to the materialization tables.

> Enhancing the External Materialization
> --------------------------------------
>
>                 Key: TEIID-4121
>                 URL: https://issues.jboss.org/browse/TEIID-4121
>             Project: Teiid
>          Issue Type: Sub-task
>          Components: Query Engine
>    Affects Versions: 9.x
>            Reporter: Kylin Soong
>            Assignee: Steven Hawkins
>             Fix For: 9.0
>
>
> The intention of move "status" table to physical database is to increase durable and fully control refresh and loading, but it increase the complexity.
> The "status" table by design should unique for whole VDB, if you look the https://teiid.gitbooks.io/documents/content/caching/External_Materialization.html#_usage_steps, the table structure:
> {code:sql}
> CREATE TABLE status
> (
>   VDBName varchar(50) not null,
>   VDBVersion integer not null,
>   SchemaName varchar(50) not null,
>   Name varchar(256) not null,
>   TargetSchemaName varchar(50),
>   TargetName varchar(256) not null,
>   Valid boolean not null,
>   LoadState varchar(25) not null,
>   Cardinality long,
>   Updated timestamp not null,
>   LoadNumber long not null,
>   PRIMARY KEY (VDBName, VDBVersion, SchemaName, Name)
> );
> {code}
> but currently, one VDB may have multiple "status" table, each view may have it's own "status" table. Further more, we can consider create status table automatically, which like internal, status create once VDB start, and configured in VDB scope.
> From finishedDeployment logic in MaterializationManager, MATERIALIZED_TABLE be used to determine whether the Mat is internal or external, But we lack the validation in metadata loading, in my previous test, the Internal Mat view configured lots of external view's properties like "status" table, the validation not throw excepton.



--
This message was sent by Atlassian JIRA
(v6.4.11#64026)