Running Alter View SQL command giving issues

[rajkota]

Hi,

I am using Teiid 7.7 in my work. When I try to run 'alter view' SQL expression from Elcipse Data Source explorer / through a Java class against a VDB then it is not running as expected, giving the below error.

org.teiid.jdbc.TeiidSQLException: modelName."viewName" is not a valid view.

           at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:113)

           at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:70)

          at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:636)

          at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:62)

           at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:547)

          at org.teiid.client.util.ResultsFuture.addCompletionListener(ResultsFuture.java:139)

          at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:543)

          at org.teiid.jdbc.StatementImpl.executeQuery(StatementImpl.java:334)

Here is the Alter View SQL expression that I am trying to invoke:

alter view "modelName"."viewName" as SELECT col1, col2, col3, col4, col5 FROM (call myProceduretoReadFiles.getTextFiles(metadata.txt')) as F, TEXTTABLE(F.file COLUMNS col1 string, col2 string, col3 string, col4 string, col5 string HEADER 1) AS t"

Here is my view definition inside model file:

<models>

...

     <model name="modelName">

          <view name="viewName">

                              <columns>

                                        <column name="col1" type="varchar"/>

                                        <column name="col2" type="varchar"/>

                                        <column name="col3" type="varchar"/>

                                        <column name="col4" type="varchar"/>

                                        <column name="col5" type="varchar"/>

                              </columns>

                              <definition>

                     SELECT col1, col2, col3, col4, col5 FROM (call myProceduretoReadFiles.getTextFiles('metadata.txt')) as F, TEXTTABLE(F.file COLUMNS col1 string, col2 string, col3 string, col4 string, col5                     string HEADER 1)  AS t

                              </definition>

                    </view>

      </model>

<models>

When I run select * from "modelName"."viewName", then query is returning the proper response.

There might be some usage issue here while using 'alter view' and I could not able to get some more technical details on 'Alter View' usage with some samples over Teiid 7.7 documentation / forum. Please help me giving a way to make my alter view runnable.

Thanks,

Raj

[shawkins]

Is this a recursive scenario where you are running against the physical table representation of the view?  Otherwise your alter should work.

Steve

[rajkota]

Hi Steve,

I did a  quick POC on the 'alter view' by creating a view from text data, it worked fine.

But we do handle the view definitions, models programmatically and thus we get our VDB dynamically as Mark Addleman mentioned in his last post to the thread https://community.jboss.org/message/751975#751975.

Currently I am doing debug to check where our code has to take an extra step to give respect to the 'view definition towards identifying / recognizing the view.

I tried couple of things as mentioned below, but still no luck:

• table.setSupportsUpdate(true): while creating the views programatically, we are making use of  org.teiid.metadata.Table.
•          modelMetaData.addProperty("importer.useFullSchemaName", "false");

                 modelMetaData.addProperty("useCatalogName", "false");

By looking at the above error details (or message: modelName."viewName" is not a valid view) in first post, may be my questions is weird, can you please throw your thoughts on what could be went wrong / need to be handled?

In other way: In what scenarios we get above error message?

Because we are able to query the view (select * from modelName."viewName") but could not able to run the 'alter view' on the same.

Thanks,

Raj

[rareddy]

Raj,

Did you have

table.setVirtual(true);

table.setSelectTransformation(sql);

If possible post the code, where you are creating the table if above does not fix.

Also, Teiid 8.1 defines a way to define the views in DDL, instead of defining in the custom XML format as you are doing. Why not use that make your implementation lot simpler?

Ramesh..

[markaddleman]

Hi, Ramesh -

Why not use that make your implementation lot simpler?

Our app is composed of several components, each of which contains a VDB fragment and code.  The customer may choose to deploy any combination of the components.  Each of the VDB fragments contains almost the same information as a regular VDB:  models, translators and views.  At startup, our app merges these VDB fragments together into a single VDB and we deploy that.  We built this system back in the 7.x days out of fish-hooks and bailing wire.  It has gotten more robust since then but I'd love to ditch it entirely as there are still several problems with our approach.

I think we can re-use much of Teiid's built-in functionality with 8.x.  What do you think of the following approach?

1. Each component has a real VDB that is separately deployed in Teiid
2. After Teiid deploys the individual component, our application programmatically constructs (using the Admin object) a unified VDB that imports each of the component VDBs using TEIID-1366

[rareddy]

Mark,

Did not realize Raj is from your team. Yes, I understand your usecase little deeper and has strong ties with 7.x line.

I think we can re-use much of Teiid's built-in functionality with 8.x.  What do you think of the following approach?

1. Each component has a real VDB that is separately deployed in Teiid
2. After Teiid deploys the individual component, our application programmatically constructs (using the Admin object) a unified VDB that imports each of the component VDBs using TEIID-1366

1) Yes, that will be good to deploy as individual VDB

2) With TEIID-1366, you can define a composite VDB, but you do not need to pull the model fragments from other vdbs to build the composite vdb, use the "vbd-import" feature that would work better. I have been planning to write a blog on vdb-reuse soon.

Thanks

Ramesh..

[rajkota]

Hi Ramesh,

We have been using the below two properties only for materialized views.

table.setVirtual(true);

table.setSelectTransformation(sql);

I started my experiment with the simplest selectTransformation, i.e, passing 'null' to the columns of the view (-denoted by modelname.viewname) in the select query.

Now, when I set the table.setSelectTransformation(select null,null,null,null from 'modelname'.'viewname') and table.setVirtual(true) for all views (materialized  + non-materialized) ,then I am getting StackOverflowError like below on server startup:

[00:14:07.631][info][talledLocalContainer] java.lang.StackOverflowError

[00:14:07.631][info][talledLocalContainer]     at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.visit(PreOrPostOrderNavigator.java:216)

[00:14:07.631][info][talledLocalContainer]     at org.teiid.query.sql.symbol.Constant.acceptVisitor(Constant.java:131)

[00:14:07.632][info][talledLocalContainer]     at org.teiid.query.sql.navigator.AbstractNavigator.visitNode(AbstractNavigator.java:61)

[00:14:07.632][info][talledLocalContainer]     at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.visit(PreOrPostOrderNavigator.java:260)

[00:14:07.632][info][talledLocalContainer]     at org.teiid.query.sql.symbol.ExpressionSymbol.acceptVisitor(ExpressionSymbol.java:82)

[00:14:07.632][info][talledLocalContainer]     at org.teiid.query.sql.navigator.AbstractNavigator.visitNode(AbstractNavigator.java:61)

[00:14:07.632][info][talledLocalContainer]     at org.teiid.query.sql.navigator.AbstractNavigator.visitNodes(AbstractNavigator.java:72)

[00:14:07.632][info][talledLocalContainer]     at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.visit(PreOrPostOrderNavigator.java:425)

And when I set table.setSelectTransformation(select null,null,null,null from 'modelname'.'viewname')  for all views and table.setVirtual(true) only for materialized views, then I am getting the same error ('... is not a valid view') that I mentioned in my first post.

And I am copying here our code snippet to handle the table for your reference:

   final Table table = metadataFactory.addTable(viewName);

             table.setSupportsUpdate(true);

                    if (is current view is materialized)

                    {

                        table.setMaterialized(true);

                        table.setVirtual(true);

                    }

          ------------------

             logic to prepare selectTransformation goes here

          --------------

                    table.setSelectTransformation(selectTransformation);                  

                    table.setTableType(Type.View);       

        for(each column in the view){

            -----

             metadataFactory.addColumn(columnName, columntype, table);

            ---   

        }

    metadataFactory.addIndex(viewDefinition.viewName, false,  colList, table);

Thanks,

Raj

[rareddy]

Raj,

table.setMaterialized(true);

defines a view is materializied or not. You need to have 'setVirtual(true)' on all your views.

Ramesh..

[rajkota]

Hi Ramesh,

When I have table.setVirtual(true) on all views, then I am getting StackOverflowError immedeaitely after server startup as given below:

[00:14:07.631][info][talledLocalContainer] 04 Sep 2012 00:14:07,627 IST ERROR [PROCESSOR] Unexpected exception for request /9NjqBcLhLBW.0

[00:14:07.631][info][talledLocalContainer] java.lang.StackOverflowError

[00:14:07.631][info][talledLocalContainer]     at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.visit(PreOrPostOrderNavigator.java:216)

[00:14:07.631][info][talledLocalContainer]     at org.teiid.query.sql.symbol.Constant.acceptVisitor(Constant.java:131)

[00:14:07.632][info][talledLocalContainer]     at org.teiid.query.sql.navigator.AbstractNavigator.visitNode(AbstractNavigator.java:61)

[00:14:07.632][info][talledLocalContainer]     at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.visit(PreOrPostOrderNavigator.java:260)

Any idea why are we getting this error?

But still getting the same error (... is not a valid view) when our application tries to run the 'alter view' after having table.setVirtual(true) on all views.

Thanks,  Raj

[rareddy]

Can you attach the log so that I can see the whole stacktrace.

Ramesh..

[markaddleman]

I have been planning to write a blog on vdb-reuse soon.

Wonderful.  I look forward to it.  The one thing that I'm very interested in is how to dynamically determine the list of vdb-import since we don't know the list of extant components until startup (customers can shut down the server, deploy new component or remove old ones and restart).

[rajkota]

Hi Ramesh,

I have attached our server log here, please have a look.

Thanks, Raj

[shawkins]

Raj,

For the earlier exception: modelName."viewName" is not a valid view.

See also https://community.jboss.org/thread/203456, which is likely applicable here and is a limitation of using translator supplied metadata to create views on 7.7.

The StackOverflowError is likely from a recursive view definition (which I think that we lack detection of solely on the Teiid side, for views - it is detected for procedures and in Designer for views).

Steve

[rajkota]

Hi Steve,

Thanks for reply.

I just gone through the thread that you have mentioned, So we get the fix for 'invalid view' through Teiid 8.x only,but not in 7.7.x. Can I get any work arounds for my issue in Teiid 7.7.x?

Thanks, Raj

[shawkins]

Starting with 8.1 you can have a view in physical model and have it be targeted by alter.  Using 7.7 to supply a view via translator supplied metadata wasn't a fully vetted approach.  I don't think there is a workaround, but the patch from TEIID-2136 is fairly concise so you could look at back-porting it.

Steve