[JBoss JIRA] (TEIID-3995) SAP HANA materialization:function loadMatView with argument invalidate set to true problem
9:24 a.m.
[
https://issues.jboss.org/browse/TEIID-3995?page=com.atlassian.jira.plugin...
]
Jan Stastny commented on TEIID-3995:
------------------------------------
VDB is defined as:
{code:plain}
<?xml version="1.0" encoding="UTF-8"?>
<vdb name="saphana" version="1">
<description>For testing of materialized views</description>
<model name="Source">
<source name="Source" translator-name="hana-override"
connection-jndi-name="java:/saphana-ds" />
<metadata type="DDL"><![CDATA[
CREATE FOREIGN TABLE "JSTASTNY.DV_MATVIEWS_CHECK_TABLE" (
ID string(255) NOT NULL OPTIONS (NAMEINSOURCE '"ID"', NATIVE_TYPE
'VARCHAR'),
VDB_CREATE integer NOT NULL DEFAULT '0' OPTIONS (NAMEINSOURCE
'"VDB_CREATE"', CHAR_OCTET_LENGTH 4, NATIVE_TYPE 'INTEGER'),
VDB_DROP integer NOT NULL DEFAULT '0' OPTIONS (NAMEINSOURCE
'"VDB_DROP"', CHAR_OCTET_LENGTH 4, NATIVE_TYPE 'INTEGER'),
BEFORE_LOAD integer NOT NULL DEFAULT '0' OPTIONS (NAMEINSOURCE
'"BEFORE_LOAD"', CHAR_OCTET_LENGTH 4, NATIVE_TYPE 'INTEGER'),
AFTER_LOAD integer NOT NULL DEFAULT '0' OPTIONS (NAMEINSOURCE
'"AFTER_LOAD"', CHAR_OCTET_LENGTH 4, NATIVE_TYPE 'INTEGER'),
CONSTRAINT "_SYS_TREE_RS_#160979_#0_#P0" PRIMARY KEY(ID)
) OPTIONS (NAMEINSOURCE
'"JSTASTNY"."DV_MATVIEWS_CHECK_TABLE"', UPDATABLE TRUE);
CREATE FOREIGN TABLE "JSTASTNY.DV_MATVIEWS_CUSTOMERS" (
ID integer NOT NULL OPTIONS (NAMEINSOURCE '"ID"', CHAR_OCTET_LENGTH 4,
NATIVE_TYPE 'INTEGER'),
NAME string(255) OPTIONS (NAMEINSOURCE '"NAME"', NATIVE_TYPE
'VARCHAR'),
ADDRESS string(255) OPTIONS (NAMEINSOURCE '"ADDRESS"', NATIVE_TYPE
'VARCHAR'),
CONSTRAINT "_SYS_TREE_RS_#160987_#0_#P0" PRIMARY KEY(ID)
) OPTIONS (NAMEINSOURCE '"JSTASTNY"."DV_MATVIEWS_CUSTOMERS"',
UPDATABLE TRUE);
CREATE FOREIGN TABLE "JSTASTNY.DV_MATVIEWS_MAT_VIEW" (
CUSTOMER_ID integer NOT NULL OPTIONS (NAMEINSOURCE '"CUSTOMER_ID"',
CHAR_OCTET_LENGTH 4, NATIVE_TYPE 'INTEGER'),
TOTAL_AMOUNT integer OPTIONS (NAMEINSOURCE '"TOTAL_AMOUNT"',
CHAR_OCTET_LENGTH 4, NATIVE_TYPE 'INTEGER'),
CONSTRAINT "_SYS_TREE_RS_#160993_#0_#P0" PRIMARY KEY(CUSTOMER_ID)
) OPTIONS (NAMEINSOURCE '"JSTASTNY"."DV_MATVIEWS_MAT_VIEW"',
UPDATABLE TRUE);
CREATE FOREIGN TABLE "JSTASTNY.DV_MATVIEWS_MAT_VIEW_STAGE" (
CUSTOMER_ID integer NOT NULL OPTIONS (NAMEINSOURCE '"CUSTOMER_ID"',
CHAR_OCTET_LENGTH 4, NATIVE_TYPE 'INTEGER'),
TOTAL_AMOUNT integer OPTIONS (NAMEINSOURCE '"TOTAL_AMOUNT"',
CHAR_OCTET_LENGTH 4, NATIVE_TYPE 'INTEGER'),
CONSTRAINT "_SYS_TREE_RS_#160998_#0_#P0" PRIMARY KEY(CUSTOMER_ID)
) OPTIONS (NAMEINSOURCE
'"JSTASTNY"."DV_MATVIEWS_MAT_VIEW_STAGE"', UPDATABLE TRUE);
CREATE FOREIGN TABLE "JSTASTNY.DV_MATVIEWS_ORDERS" (
ID integer NOT NULL OPTIONS (NAMEINSOURCE '"ID"', CHAR_OCTET_LENGTH 4,
NATIVE_TYPE 'INTEGER'),
AMOUNT integer OPTIONS (NAMEINSOURCE '"AMOUNT"', CHAR_OCTET_LENGTH 4,
NATIVE_TYPE 'INTEGER'),
CUSTOMER_ID integer OPTIONS (NAMEINSOURCE '"CUSTOMER_ID"',
CHAR_OCTET_LENGTH 4, NATIVE_TYPE 'INTEGER'),
CONSTRAINT "_SYS_TREE_RS_#161003_#0_#P0" PRIMARY KEY(ID),
CONSTRAINT FKARBPDQCPCCILHM7T1D3HI3L48 FOREIGN KEY(CUSTOMER_ID) REFERENCES
JSTASTNY.DV_MATVIEWS_CUSTOMERS (ID)
) OPTIONS (NAMEINSOURCE '"JSTASTNY"."DV_MATVIEWS_ORDERS"',
UPDATABLE TRUE);
CREATE FOREIGN TABLE "JSTASTNY.DV_MATVIEWS_STATUSTABLE" (
VDBNAME string(50) NOT NULL OPTIONS (NAMEINSOURCE '"VDBNAME"',
NATIVE_TYPE 'VARCHAR'),
VDBVERSION integer NOT NULL OPTIONS (NAMEINSOURCE '"VDBVERSION"',
CHAR_OCTET_LENGTH 4, NATIVE_TYPE 'INTEGER'),
SCHEMANAME string(50) NOT NULL OPTIONS (NAMEINSOURCE '"SCHEMANAME"',
NATIVE_TYPE 'VARCHAR'),
Name string(256) NOT NULL OPTIONS (NAMEINSOURCE '"NAME"', NATIVE_TYPE
'VARCHAR'),
TARGETSCHEMANAME string(50) OPTIONS (NAMEINSOURCE
'"TARGETSCHEMANAME"', NATIVE_TYPE 'VARCHAR'),
TARGETNAME string(256) OPTIONS (NAMEINSOURCE '"TARGETNAME"',
NATIVE_TYPE 'VARCHAR'),
VALID boolean NOT NULL OPTIONS (NAMEINSOURCE '"VALID"',
CHAR_OCTET_LENGTH 1, NATIVE_TYPE 'TINYINT'),
LOADSTATE string(25) NOT NULL OPTIONS (NAMEINSOURCE '"LOADSTATE"',
NATIVE_TYPE 'VARCHAR'),
CARDINALITY long OPTIONS (NAMEINSOURCE '"CARDINALITY"',
CHAR_OCTET_LENGTH 20, NATIVE_TYPE 'BIGINT'),
UPDATED timestamp NOT NULL OPTIONS (NAMEINSOURCE '"UPDATED"',
CHAR_OCTET_LENGTH 16, NATIVE_TYPE 'TIMESTAMP'),
LOADNUMBER long NOT NULL OPTIONS (NAMEINSOURCE '"LOADNUMBER"',
CHAR_OCTET_LENGTH 20, NATIVE_TYPE 'BIGINT'),
CONSTRAINT "_SYS_TREE_RS_#161009_#0_#P0" PRIMARY KEY(VDBNAME, VDBVERSION,
SCHEMANAME, Name)
) OPTIONS (NAMEINSOURCE
'"JSTASTNY"."DV_MATVIEWS_STATUSTABLE"', UPDATABLE TRUE);
]]>
</metadata>
</model>
<model name="View" type="VIRTUAL">
<metadata type="DDL"><![CDATA[
CREATE VIEW external_long_ttl (
customer_id integer NOT NULL,
total_amount integer
) OPTIONS (MATERIALIZED 'TRUE', UPDATABLE 'FALSE',
MATERIALIZED_TABLE 'Source.JSTASTNY.dv_matviews_mat_view',
"teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
"teiid_rel:MATVIEW_STATUS_TABLE"
'Source.JSTASTNY.dv_matviews_statustable',
"teiid_rel:ON_VDB_START_SCRIPT" 'MERGE INTO
dv_matviews_check_table(id,vdb_create) SELECT id, vdb_create+1 FROM
dv_matviews_check_table WHERE id=''external_long_ttl'';',
"teiid_rel:ON_VDB_DROP_SCRIPT" 'MERGE INTO
dv_matviews_check_table(id,vdb_drop) SELECT id, vdb_drop+1 FROM dv_matviews_check_table
WHERE id=''external_long_ttl'';',
"teiid_rel:MATVIEW_LOAD_SCRIPT" 'INSERT INTO
dv_matviews_mat_view_stage(customer_id,total_amount) SELECT CONVERT(c.id,integer) AS
customer_id, CONVERT(SUM(o.amount),integer) AS total_amount FROM dv_matviews_customers c
INNER JOIN dv_matviews_orders o ON c.id = o.customer_id GROUP BY c.id;',
"teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'exec
Source.native(''truncate table dv_matviews_mat_view_stage'');MERGE INTO
dv_matviews_check_table(id,before_load) SELECT id, before_load+1 FROM
dv_matviews_check_table WHERE id=''external_long_ttl'';',
"teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'exec
Source.native(''RENAME TABLE dv_matviews_mat_view_stage TO
dv_matviews_mat_view_temp'');exec Source.native(''RENAME TABLE
dv_matviews_mat_view TO dv_matviews_mat_view_stage'');exec
Source.native(''RENAME TABLE dv_matviews_mat_view_temp TO
dv_matviews_mat_view'');MERGE INTO dv_matviews_check_table(id,after_load) SELECT
id, after_load+1 FROM dv_matviews_check_table WHERE
id=''external_long_ttl'';',
"teiid_rel:MATVIEW_ONERROR_ACTION" 'WAIT',
"teiid_rel:MATVIEW_TTL" 20000)
AS SELECT CONVERT(c.id,integer) AS customer_id,
CONVERT(SUM(o.amount),integer) AS total_amount FROM dv_matviews_customers c INNER JOIN
dv_matviews_orders o ON c.id = o.customer_id GROUP BY c.id;
]]>
</metadata>
</model>
<translator name="hana-override" type="hana">
<property name="SupportsDirectQueryProcedure" value="true"
/>
</translator>
</vdb>
{code}
The stacktrace I get is:
{code:plain}
08:18:07,640 ERROR [org.teiid.TRANSPORT] (New I/O worker #19) TEIID40113 Unhandled
exception, aborting operation: org.teiid.transport.ObjectEncoder$FailedWriteException:
org.teiid.core.TeiidRuntimeException: TEIID20001 The modeled datatype integer for column 0
doesn't match the runtime type "org.teiid.core.types.ArrayImpl". Please
ensure that the column's modeled datatype matches the expected data.
at org.teiid.transport.ObjectEncoder.handleDownstream(ObjectEncoder.java:136)
[teiid-runtime-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at org.jboss.netty.channel.Channels.write(Channels.java:704)
[netty-3.6.10.Final-redhat-1.jar:3.6.10.Final-redhat-1]
at org.jboss.netty.channel.Channels.write(Channels.java:671)
[netty-3.6.10.Final-redhat-1.jar:3.6.10.Final-redhat-1]
at org.jboss.netty.channel.AbstractChannel.write(AbstractChannel.java:248)
[netty-3.6.10.Final-redhat-1.jar:3.6.10.Final-redhat-1]
at
org.teiid.transport.SSLAwareChannelHandler$ObjectChannelImpl.write(SSLAwareChannelHandler.java:93)
[teiid-runtime-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at org.teiid.transport.SocketClientInstance.send(SocketClientInstance.java:88)
[teiid-runtime-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at org.teiid.transport.ServerWorkItem.sendResult(ServerWorkItem.java:135)
[teiid-runtime-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at org.teiid.transport.ServerWorkItem$1.onCompletion(ServerWorkItem.java:105)
[teiid-runtime-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at org.teiid.client.util.ResultsFuture.done(ResultsFuture.java:135)
[teiid-client-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at org.teiid.client.util.ResultsFuture.access$200(ResultsFuture.java:40)
[teiid-client-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at org.teiid.client.util.ResultsFuture$1.receiveResults(ResultsFuture.java:79)
[teiid-client-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at
org.teiid.dqp.internal.process.RequestWorkItem.sendResultsIfNeeded(RequestWorkItem.java:992)
[teiid-engine-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at
org.teiid.dqp.internal.process.RequestWorkItem$1.flushBatchDirect(RequestWorkItem.java:674)
[teiid-engine-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at org.teiid.query.processor.BatchCollector.flushBatch(BatchCollector.java:223)
[teiid-engine-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at
org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:194)
[teiid-engine-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at
org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146)
[teiid-engine-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at
org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:470)
[teiid-engine-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at
org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:348)
[teiid-engine-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)
[teiid-engine-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:274)
[teiid-engine-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at
org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
[teiid-engine-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
[teiid-engine-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
[teiid-engine-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
[rt.jar:1.8.0_51]
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
[rt.jar:1.8.0_51]
at java.lang.Thread.run(Thread.java:745) [rt.jar:1.8.0_51]
Caused by: org.teiid.core.TeiidRuntimeException: TEIID20001 The modeled datatype integer
for column 0 doesn't match the runtime type
"org.teiid.core.types.ArrayImpl". Please ensure that the column's modeled
datatype matches the expected data.
at org.teiid.client.BatchSerializer.writeBatch(BatchSerializer.java:878)
[teiid-client-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at org.teiid.client.ResultsMessage.writeExternal(ResultsMessage.java:319)
[teiid-client-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at java.io.ObjectOutputStream.writeExternalData(ObjectOutputStream.java:1459)
[rt.jar:1.8.0_51]
at java.io.ObjectOutputStream.writeOrdinaryObject(ObjectOutputStream.java:1430)
[rt.jar:1.8.0_51]
at java.io.ObjectOutputStream.writeObject0(ObjectOutputStream.java:1178)
[rt.jar:1.8.0_51]
at java.io.ObjectOutputStream.writeObject(ObjectOutputStream.java:348)
[rt.jar:1.8.0_51]
at org.teiid.net.socket.Message.writeExternal(Message.java:61)
[teiid-client-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at java.io.ObjectOutputStream.writeExternalData(ObjectOutputStream.java:1459)
[rt.jar:1.8.0_51]
at java.io.ObjectOutputStream.writeOrdinaryObject(ObjectOutputStream.java:1430)
[rt.jar:1.8.0_51]
at java.io.ObjectOutputStream.writeObject0(ObjectOutputStream.java:1178)
[rt.jar:1.8.0_51]
at java.io.ObjectOutputStream.writeObject(ObjectOutputStream.java:348)
[rt.jar:1.8.0_51]
at org.teiid.transport.ObjectEncoder.handleDownstream(ObjectEncoder.java:131)
[teiid-runtime-8.12.5.redhat-1.jar:8.12.5.redhat-1]
... 25 more
Caused by: java.lang.ClassCastException: org.teiid.core.types.ArrayImpl cannot be cast to
java.lang.Integer
at
org.teiid.client.BatchSerializer$IntColumnSerializer.writeObject(BatchSerializer.java:563)
[teiid-client-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at
org.teiid.client.BatchSerializer$ColumnSerializer.writeColumn(BatchSerializer.java:534)
[teiid-client-8.12.5.redhat-1.jar:8.12.5.redhat-1]
at org.teiid.client.BatchSerializer.writeBatch(BatchSerializer.java:867)
[teiid-client-8.12.5.redhat-1.jar:8.12.5.redhat-1]
... 36 more
{code}
SAP HANA materialization:function loadMatView with argument
invalidate set to true problem
------------------------------------------------------------------------------------------
Key: TEIID-3995
URL: https://issues.jboss.org/browse/TEIID-3995
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 8.12.x
Reporter: Jan Stastny
Assignee: Steven Hawkins
There appears to be an issue with loadMatView function.
Specifically when the function is invoked with invalidate=>'true' argument,
when it is expected, that invocation of this function invalidates the current contents of
the materialized view until the initiated load is completed and new data are populated.
But when calling this function as described, an error occurs:
{code:plain}
TEIID20001 The modeled datatype integer for column 0 doesn't match the runtime type
"org.teiid.core.types.ArrayImpl". Please ensure that the column's modeled
datatype matches the expected data.
{code}
Please note, that there is no column of type array in my schema.
There is declaration of the view:
{code:sql}
CREATE VIEW external_long_ttl (
customer_id integer NOT NULL,
total_amount integer
)
{code}
and the query used to load the view:
{code:sql}
"teiid_rel:MATVIEW_LOAD_SCRIPT" 'INSERT INTO
dv_matviews_mat_view_stage(customer_id,total_amount) SELECT CONVERT(c.id,integer) AS
customer_id, CONVERT(SUM(o.amount),integer) AS total_amount FROM dv_matviews_customers c
INNER JOIN dv_matviews_orders o ON c.id = o.customer_id GROUP BY c.id;',
{code}
Complete definition of the view is:
{code:sql}
CREATE VIEW external_long_ttl (
customer_id integer NOT NULL,
total_amount integer
) OPTIONS (
MATERIALIZED 'TRUE',
UPDATABLE 'FALSE',
MATERIALIZED_TABLE 'Source.JSTASTNY.dv_matviews_mat_view',
"teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
"teiid_rel:MATVIEW_STATUS_TABLE"
'Source.JSTASTNY.dv_matviews_statustable',
"teiid_rel:ON_VDB_START_SCRIPT" 'MERGE INTO
dv_matviews_check_table(id,vdb_create) SELECT id, vdb_create+1 FROM
dv_matviews_check_table WHERE id=''external_long_ttl'';',
"teiid_rel:ON_VDB_DROP_SCRIPT" 'MERGE INTO
dv_matviews_check_table(id,vdb_drop) SELECT id, vdb_drop+1 FROM dv_matviews_check_table
WHERE id=''external_long_ttl'';',
"teiid_rel:MATVIEW_LOAD_SCRIPT" 'INSERT INTO
dv_matviews_mat_view_stage(customer_id,total_amount) SELECT CONVERT(c.id,integer) AS
customer_id, CONVERT(SUM(o.amount),integer) AS total_amount FROM dv_matviews_customers c
INNER JOIN dv_matviews_orders o ON c.id = o.customer_id GROUP BY c.id;',
"teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'exec
Source.native(''truncate table dv_matviews_mat_view_stage'');MERGE INTO
dv_matviews_check_table(id,before_load) SELECT id, before_load+1 FROM
dv_matviews_check_table WHERE id=''external_long_ttl'';',
"teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'exec
Source.native(''RENAME TABLE dv_matviews_mat_view_stage TO
dv_matviews_mat_view_temp'');exec Source.native(''RENAME TABLE
dv_matviews_mat_view TO dv_matviews_mat_view_stage'');exec
Source.native(''RENAME TABLE dv_matviews_mat_view_temp TO
dv_matviews_mat_view'');MERGE INTO dv_matviews_check_table(id,after_load) SELECT
id, after_load+1 FROM dv_matviews_check_table WHERE
id=''external_long_ttl'';',
"teiid_rel:MATVIEW_ONERROR_ACTION" 'WAIT',
"teiid_rel:MATVIEW_TTL" 20000
) AS SELECT CONVERT(c.id,integer) AS customer_id, CONVERT(SUM(o.amount),integer)
AS total_amount FROM dv_matviews_customers c INNER JOIN dv_matviews_orders o ON c.id =
o.customer_id GROUP BY c.id;
{code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
3235
days inactive
3235
days old
0 comments
1 participants
participants (1)
-
Jan Stastny (JIRA)