-
1. Re: External Materialization with Postgresql
External Materialization - Teiid 8.10 (draft) - Project Documentation Editor
does have a working example I cut and pasted. Not sure about the problem. I am suspecting some misplaced character in your SQL statements. Can you double check or post it here?
-
2. Re: External Materialization with Postgresql
Hi Ramesh, below is my modified vdb:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="my-vdb" version="1">
<import-vdb name="some-vdb" version="1" import-data-policies="false"/>
<model name="test_model" visible="true" type="VIRTUAL" >
<metadata type = "DDL"><![CDATA[
CREATE VIEW test (
model_sk long not null,
model_name varchar(50) not null,
model_id varchar(50),
PRIMARY KEY (model_sk)
) OPTIONS (MATERIALIZED 'TRUE', UPDATABLE 'TRUE',
MATERIALIZED_TABLE 'pg.public.mv_test',
"teiid_rel:MATERIALIZED_STAGE_TABLE" 'pg.public.mv_test_staging',
"teiid_rel:MATVIEW_TTL" 120000,
"teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
"teiid_rel:MATVIEW_STATUS_TABLE" 'pg.public.status',
"teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'execute pg.native(''truncate table mv_test_staging'');',
"teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'execute pg.native(''ALTER TABLE mv_test RENAME TO mv_test_temp;ALTER TABLE mv_test_staging RENAME TO mv_test;ALTER TABLE mv_test_temp RENAME TO mv_test_staging;'')',
"teiid_rel:MATVIEW_SHARE_SCOPE" 'VDB',
"teiid_rel:MATVIEW_ONERROR_ACTION" 'THROW_EXCEPTION',
"teiid_rel:ON_VDB_DROP_SCRIPT" 'DELETE FROM pg.public.status WHERE Name=''test'' AND schemaname = ''test_model''')
AS SELECT convert(model_sk, long), model_name, model_id
FROM imported_model.model
WHERE criteria='something';
]]>
</metadata>
</model>
<model name="pg">
<source name="pg" translator-name="postgresql-override" connection-jndi-name="java:/mv_view"/>
</model>
<translator name="postgresql-override" type="postgresql">
<property name="SupportsNativeQueries" value="true"/>
</translator>
</vdb>
For the most part, it's very similar with the example except I try to import data from a sybase database. Like i said, if i reduce MATVIEW_BEFORE_LOAD_SCRIPT property to contain one statement, it works (?)
Another issue I ran into is the import process failed when my data contains comma (,). The internal sql statement "insert ... value ( , , ,...)" get mixed up, do you have a suggestion for me?
Thanks,
-
3. Re: External Materialization with Postgresql
Ramesh,
I was unable to make it work in 8.7.
Don't remember why, but the data federation quick start materialization is defined (not like the doc's), but as the following, and it works:
{code}
<model name="StocksMatModel" type="VIRTUAL">
<metadata type="DDL"><![CDATA[
CREATE view stockPricesMatView
(
product_id integer,
symbol string,
price bigdecimal,
company_name varchar(256)
) OPTIONS (MATERIALIZED 'TRUE', UPDATABLE 'TRUE',
MATERIALIZED_TABLE 'Accounts.h2_stock_mat',
"teiid_rel:MATVIEW_TTL" 120000,
"teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'execute accounts.native(''truncate table h2_stock_mat'');',
"teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'execute accounts.native('''')',
"teiid_rel:ON_VDB_DROP_SCRIPT" 'DELETE FROM Accounts.status WHERE Name=''stock'' AND schemaname = ''Stocks''',
"teiid_rel:MATERIALIZED_STAGE_TABLE" 'Accounts.h2_stock_mat',
"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, S.symbol, S.price, A.COMPANY_NAME
FROM Stocks.StockPrices AS S, Accounts.PRODUCT AS A
WHERE S.symbol = A.SYMBOL;
]]>
</metadata>
</model>
[code}
So I changed it to match the doc's:
{code}
<model name="StocksMatModel" type="VIRTUAL"> <metadata type="DDL"><![CDATA[ CREATE view stockPricesMatView ( product_id integer,
symbol string,
price bigdecimal,
company_name varchar(256)
) OPTIONS (MATERIALIZED 'TRUE', UPDATABLE 'TRUE', MATERIALIZED_TABLE 'Accounts.h2_stock_mat',
"teiid_rel:MATVIEW_TTL" 120000,
"teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'execute accounts.native(''truncate table mat_stock_staging'');',
"teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'execute accounts.native(''ALTER TABLE h2_stock_mat RENAME TO h2_stock_mat_temp;ALTER TABLE mat_stock_staging RENAME TO h2_stock_mat;ALTER TABLE h2_stock_mat_temp RENAME TO mat_stock_staging;'')',
"teiid_rel:ON_VDB_DROP_SCRIPT" 'DELETE FROM Accounts.status WHERE Name=''stock'' AND schemaname = ''Stocks''',
"teiid_rel:MATERIALIZED_STAGE_TABLE" 'Accounts.mat_stock_staging',
"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, S.symbol, S.price, A.COMPANY_NAME FROM Stocks.StockPrices AS S, Accounts.PRODUCT AS A
WHERE S.symbol = A.SYMBOL;
]]> </metadata> </model> {code}
and I don't see any errors on the server, and it never appears to execute. And yes, my mat_stock_staging table does exist.
-
4. Re: External Materialization with Postgresql
I executed the native in Squirrel, to confirm it works, and it did:
execute accounts.native('ALTER TABLE h2_stock_mat RENAME TO h2_stock_mat_temp;ALTER TABLE mat_stock_staging RENAME TO h2_stock_mat;ALTER TABLE h2_stock_mat_temp RENAME TO mat_stock_staging');
And when the next cycle of the materialization was to run, it truncated the staging table, but never reloaded.
-
5. Re: Re: External Materialization with Postgresql
The problem was in my documentation. The property should look like below.
"teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'execute pg.native(''ALTER TABLE mv_test RENAME TO mv_test_temp'');execute pg.native(''ALTER TABLE mv_test_staging RENAME TO mv_test'');execute pg.native(''ALTER TABLE mv_test_temp RENAME TO mv_test_staging'');',I will fix the docs.
Thanks.
-
6. Re: External Materialization with Postgresql
Thank you for your quick response, i was able to fix the issue.
Let me know if you have answer for my other issue
Another issue I ran into is the import process failed when my data contains comma (,). The internal sql statement "insert ... value ( , , ,...)" get mixed up, do you have a suggestion for me?
-
7. Re: External Materialization with Postgresql
You need to explain the issue lot more clearer than what you did to expect a decent answer
-
8. Re: Re: External Materialization with Postgresql
So basically one of my data contain comma and the mat view failed to load. Below is the stacktrace (i replace some name with test, model):
11:55:10,467 WARN [org.teiid.CONNECTOR] (Worker17_QueryProcessorQueue192) A69zk63MOr/4 Connector worker process failed for atomic-request=A69zk63MOr/4.0.60.119: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?] SQL: INSERT INTO "public"."mv_test_staging" ("model_sk", "model_name", "model_id", "name1", "name2", "name3", "name4", "name5", "name6", "name7", "name8", "name9", "name10", "name11", "name12", "name13", "name14", "name15", "name16", "name17", "name18", "name19", "name20", "name21", "name22", "name23") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]
at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:247)
at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:79)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:359) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:326) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:135) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.query.processor.relational.ProjectIntoNode.checkExitConditions(ProjectIntoNode.java:218) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.query.processor.relational.ProjectIntoNode.nextBatchDirect(ProjectIntoNode.java:129) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:136) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.query.processor.BatchIterator.finalRow(BatchIterator.java:69) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.common.buffer.AbstractTupleSource.getCurrentTuple(AbstractTupleSource.java:70) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.query.processor.BatchIterator.getCurrentTuple(BatchIterator.java:84) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.common.buffer.AbstractTupleSource.hasNext(AbstractTupleSource.java:92) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.query.processor.proc.ProcedurePlan.executePlan(ProcedurePlan.java:563) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.query.processor.proc.CreateCursorResultSetInstruction.process(CreateCursorResultSetInstruction.java:68) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.query.processor.proc.ExecDynamicSqlInstruction$1.process(ExecDynamicSqlInstruction.java:196) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.query.processor.proc.ProcedurePlan.processProcedure(ProcedurePlan.java:356) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.query.processor.proc.ProcedurePlan.nextBatchDirect(ProcedurePlan.java:283) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.query.processor.proc.ProcedurePlan.nextBatch(ProcedurePlan.java:257) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.query.processor.relational.PlanExecutionNode.nextBatchDirect(PlanExecutionNode.java:118) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:136) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:164) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:444) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:326) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:254) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.8.1.jar:8.8.1]
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214) [teiid-engine-8.8.1.jar:8.8.1]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_51]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_51]
at java.lang.Thread.run(Thread.java:744) [rt.jar:1.7.0_51]
Caused by: java.sql.BatchUpdateException: Batch entry 0 INSERT INTO "public"."mv_test_staging" ("model_sk", "model_name", "model_id", "name1", "name2", "name3", "name4", "name5", "name6", "name7", "name8", "name9", "name10", "name11", "name12", "name13", "name14", "name15", "name16", "name17", "name18", "name19", "name20", "name21", "name22", "name23") VALUES (55670000000013, R IJA, test, UNT , Z , U, test, test, U , test, test, 20131120212138, test, U , 312233843N1111652122E, 112953843, 400612122, 49REQ2673471315, 57, Unknown, test, Unknown. test. test/test / test, (e.g. test, test, model, test, Unknown, test, testl) was aborted. Call getNextException to see the cause.
at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2537)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1328)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:351)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2674)
at org.jboss.jca.adapters.jdbc.WrappedStatement.executeBatch(WrappedStatement.java:1077)
at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:210)
... 40 more
11:55:10,467 WARN [org.teiid.PROCESSOR] (Worker17_QueryProcessorQueue192) A69zk63MOr/4 TEIID30020 Processing exception for request A69zk63MOr/4.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 = viewName, updated = now(), LoadState = 'FAILED_LOAD', valid = (VARIABLES.valid) AND (NOT (invalidate)), cardinality = -1" with the SQL statement "(updateStmt || ' AND loadNumber = DVARS.loadNumber')" due to: TEIID30347 There is a recursive invocation of group 'SYSADMIN.loadMatView'. Please correct the SQL.'. Originally QueryProcessingException CommandContext.java:414. Enable more detailed logging to see the entire stacktrace.
11:55:10,467 WARN [org.teiid.PROCESSOR.MATVIEWS] (teiid-async-threads - 4) null TEIID50100 Trigger execution result: org.jboss.as.controller.OperationFailedException [ "TEIID30168 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 = viewName, updated = now(), LoadState = 'FAILED_LOAD', valid = (VARIABLES.valid) AND (NOT (invalidate)), cardinality = -1\" with the SQL statement \"(updateStmt || ' AND loadNumber = DVARS.loadNumber')\" due to: TEIID30347 There is a recursive invocation of group 'SYSADMIN.loadMatView'. Please correct the SQL." ]: org.teiid.jdbc.TeiidSQLException: TEIID50100 Trigger execution result: org.jboss.as.controller.OperationFailedException [ "TEIID30168 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 = viewName, updated = now(), LoadState = 'FAILED_LOAD', valid = (VARIABLES.valid) AND (NOT (invalidate)), cardinality = -1\" with the SQL statement \"(updateStmt || ' AND loadNumber = DVARS.loadNumber')\" due to: TEIID30347 There is a recursive invocation of group 'SYSADMIN.loadMatView'. Please correct the SQL." ]
at org.teiid.jboss.MaterializationManagementService$1.executeQuery(MaterializationManagementService.java:104) [teiid-jboss-integration-8.8.1.jar:8.8.1]
at org.teiid.runtime.MaterializationManager$QueryJob$1.run(MaterializationManager.java:267) [teiid-runtime-8.8.1.jar:8.8.1]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_51]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_51]
at java.lang.Thread.run(Thread.java:744) [rt.jar:1.7.0_51]
at org.jboss.threads.JBossThread.run(JBossThread.java:122)
Thanks
-
9. Re: Re: Re: External Materialization with Postgresql
1 of 1 people found this helpfulhaving a comma should not be a issue as it is prepared statement. The error is still not clear from
Caused by: java.sql.BatchUpdateException: Batch entry 0 INSERT INTO "public"."mv_test_staging" ("model_sk", "model_name", "model_id", "name1", "name2", "name3", "name4", "name5", "name6", "name7", "name8", "name9", "name10", "name11", "name12", "name13", "name14", "name15", "name16", "name17", "name18", "name19", "name20", "name21", "name22", "name23") VALUES (55670000000013, R IJA, test, UNT , Z , U, test, test, U , test, test, 20131120212138, test, U , 312233843N1111652122E, 112953843, 400612122, 49REQ2673471315, 57, Unknown, test, Unknown. test. test/test / test, (e.g. test, test, model, test, Unknown, test, testl) was aborted. Call getNextException to see the cause. at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2537)Can turn debug on, and see what commands being sent to the source?
BTW: when altered it seems like you cut off few columns. If in question try the command directly to the source using a any SQL client like SquirreL and see if source supports it
Ramesh..
-
10. Re: External Materialization with Postgresql
you're right, comma is not the problem. One of the columns is short on length.
Thanks for all your help
-
11. Re: External Materialization with Postgresql
For mongodb, do you know what the equivalent for
"teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'execute pg.native(''truncate table mat_actor_staging'');',I have to execute 'db.
mat_actor_staging.remove({})' but don't know the syntax (?)Thanks
-
12. Re: External Materialization with Postgresql
You need to use "Direct Query" procedure as shown in your question but to the MongoDB. See MongoDB Translator - Teiid 8.10 (draft) - Project Documentation Editor
The query you execute need to be aggregate type query of mongodb. The example you shown looks like one on mongo shell. If you can not write in aggregation type query, then translator may need another extension to support execution of shell type queries.
-
13. Re: External Materialization with Postgresql
to support external mat view we'll need to delete/rename staging table that can't be done with aggregate query. Will Teiid support shell type commands in the near future?
-
14. Re: External Materialization with Postgresql
Have not planned it, but you can enter an enhancement JIRA for it, I will see if I can get this in 8.10 release