[teiid-issues] [JBoss JIRA] (TEIID-4121) Enhancing the External Materialization
Kylin Soong (JIRA)
issues at jboss.org
Mon May 30 06:28:00 EDT 2016
[ https://issues.jboss.org/browse/TEIID-4121?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13244676#comment-13244676 ]
Kylin Soong commented on TEIID-4121:
------------------------------------
In my test I have only defined the ATVIEW_LOAD_SCRIPT to "insert into target_table select * from matview option nocache matview" as below VDB
{code}
CREATE VIEW SAMPLEMATVIEW (
id varchar,
a varchar,
b varchar,
c varchar,
PRIMARY KEY (id, a, b)
) OPTIONS (MATERIALIZED 'TRUE', UPDATABLE 'TRUE',
MATERIALIZED_TABLE 'Accounts.SampleTable_mat',
"teiid_rel:MATVIEW_TTL" 5000,
"teiid_rel:MATVIEW_LOAD_SCRIPT" 'execute accounts.native(''insert into SampleTable_mat select * from SAMPLEMATVIEW option nocache SAMPLEMATVIEW'');',
"teiid_rel:ON_VDB_DROP_SCRIPT" 'DELETE FROM Accounts.status WHERE Name=''SAMPLEMATVIEW'' AND schemaname = ''SampleModel''',
"teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
"teiid_rel:MATVIEW_STATUS_TABLE" 'status',
"teiid_rel:MATVIEW_SHARE_SCOPE" 'NONE',
"teiid_rel:MATVIEW_ONERROR_ACTION" 'THROW_EXCEPTION'
)
AS
SELECT A.id, A.a, A.b, A.c FROM Accounts.SampleTable AS A;
{code}
It throw a exception
{code}
2016-05-30 18:21 598 WARN [org.teiid.PROCESSOR.MATVIEWS] (main) TEIID31155 Typically materialization properties teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT, teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT are required to move data from MATVIEW_STATUS_TABLE to MATERIALIZED_TABLE
2016-05-30 18:21 082 WARN [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue11) Connector worker process failed for atomic-request=zw8Ke3fhlIZk.0.72.3
2016-05-30 18:21 087 WARN [org.teiid.PROCESSOR] (Worker3_QueryProcessorQueue11) TEIID30020 Processing exception for request zw8Ke3fhlIZk.0 'TEIID30168 Couldn't execute the dynamic SQL command "EXECUTE IMMEDIATE (updateStmt || ' AND loadNumber = DVARS.loadNumber') USING loadNumber = VARIABLES.loadNumber, vdbName = VARIABLES.vdbName, vdbVersion = VARIABLES.vdbVersion, schemaName = schemaName, viewName = loadMatView.viewName, updated = now(), LoadState = 'FAILED_LOAD', valid = (VARIABLES.valid) AND (NOT (invalidate)), cardinality = -1" with the SQL statement "UPDATE status SET LoadNumber = DVARS.LoadNumber, LoadState = DVARS.LoadState, valid = DVARS.valid, Updated = DVARS.updated, Cardinality = DVARS.cardinality WHERE VDBName = DVARS.vdbName AND VDBVersion = DVARS.vdbVersion AND schemaName = DVARS.schemaName AND Name = DVARS.viewName AND loadNumber = DVARS.loadNumber" due to: TEIID30347 There is a recursive invocation of group 'SYSADMIN.loadMatView'. Please correct the SQL.'. Originally QueryProcessingException CommandContext.java:436. Enable more detailed logging to see the entire stacktrace.
2016-05-30 18:21 088 WARN [org.teiid.PROCESSOR.MATVIEWS] (Worker3_QueryProcessorQueue11) org.teiid.api.exception.query.QueryProcessingException: TEIID30168 Couldn't execute the dynamic SQL command "EXECUTE IMMEDIATE (updateStmt || ' AND loadNumber = DVARS.loadNumber') USING loadNumber = VARIABLES.loadNumber, vdbName = VARIABLES.vdbName, vdbVersion = VARIABLES.vdbVersion, schemaName = schemaName, viewName = loadMatView.viewName, updated = now(), LoadState = 'FAILED_LOAD', valid = (VARIABLES.valid) AND (NOT (invalidate)), cardinality = -1" with the SQL statement "UPDATE status SET LoadNumber = DVARS.LoadNumber, LoadState = DVARS.LoadState, valid = DVARS.valid, Updated = DVARS.updated, Cardinality = DVARS.cardinality WHERE VDBName = DVARS.vdbName AND VDBVersion = DVARS.vdbVersion AND schemaName = DVARS.schemaName AND Name = DVARS.viewName AND loadNumber = DVARS.loadNumber" due to: TEIID30347 There is a recursive invocation of group 'SYSADMIN.loadMatView'. Please correct the SQL.
2016-05-30 18:21 389 WARN [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue19) Connector worker process failed for atomic-request=kzifEvzYPMaZ.0.77.7
2016-05-30 18:21 391 WARN [org.teiid.PROCESSOR] (Worker3_QueryProcessorQueue19) TEIID30020 Processing exception for request kzifEvzYPMaZ.0 'TEIID30168 Couldn't execute the dynamic SQL command "EXECUTE IMMEDIATE (updateStmt || ' AND loadNumber = DVARS.loadNumber') USING loadNumber = VARIABLES.loadNumber, vdbName = VARIABLES.vdbName, vdbVersion = VARIABLES.vdbVersion, schemaName = schemaName, viewName = loadMatView.viewName, updated = now(), LoadState = 'FAILED_LOAD', valid = (VARIABLES.valid) AND (NOT (invalidate)), cardinality = -1" with the SQL statement "UPDATE status SET LoadNumber = DVARS.LoadNumber, LoadState = DVARS.LoadState, valid = DVARS.valid, Updated = DVARS.updated, Cardinality = DVARS.cardinality WHERE VDBName = DVARS.vdbName AND VDBVersion = DVARS.vdbVersion AND schemaName = DVARS.schemaName AND Name = DVARS.viewName AND loadNumber = DVARS.loadNumber" due to: TEIID30347 There is a recursive invocation of group 'SYSADMIN.loadMatView'. Please correct the SQL.'. Originally QueryProcessingException CommandContext.java:436. Enable more detailed logging to see the entire stacktrace.
2016-05-30 18:21 392 WARN [org.teiid.PROCESSOR.MATVIEWS] (Worker3_QueryProcessorQueue19) org.teiid.api.exception.query.QueryProcessingException: TEIID30168 Couldn't execute the dynamic SQL command "EXECUTE IMMEDIATE (updateStmt || ' AND loadNumber = DVARS.loadNumber') USING loadNumber = VARIABLES.loadNumber, vdbName = VARIABLES.vdbName, vdbVersion = VARIABLES.vdbVersion, schemaName = schemaName, viewName = loadMatView.viewName, updated = now(), LoadState = 'FAILED_LOAD', valid = (VARIABLES.valid) AND (NOT (invalidate)), cardinality = -1" with the SQL statement "UPDATE status SET LoadNumber = DVARS.LoadNumber, LoadState = DVARS.LoadState, valid = DVARS.valid, Updated = DVARS.updated, Cardinality = DVARS.cardinality WHERE VDBName = DVARS.vdbName AND VDBVersion = DVARS.vdbVersion AND schemaName = DVARS.schemaName AND Name = DVARS.viewName AND loadNumber = DVARS.loadNumber" due to: TEIID30347 There is a recursive invocation of group 'SYSADMIN.loadMatView'. Please correct the SQL.
{code}
> 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: Kylin Soong
> 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)
More information about the teiid-issues
mailing list