Teiid 9.2 and the support for DDL created vdb

[gadeyne.bram]

Hi,

I just read about the DDL support to create a vdb (Developing a Virtual Database · Teiid Documentation ). I think this will be a great feature. When 9.2 CR1 is out I'll try to convert our current VDB file to a DDL version.

I have some questions about this:

1) I always configure my jdbc connections in the standalone-teiid.xml. The jdbc drivers are added as wildfly modules. e.g.

<xa-datasource jndi-name="java:jboss/datasources/CosaraDS" pool-name="Cosara" enabled="true" use-java-context="true">
                    <xa-datasource-property name="URL">
                        jdbc:mysql://...
                    </xa-datasource-property>
                    <driver>mysql</driver>
                    <security>
                        <user-name>xxx</user-name>
                        <password>xxx</password>
                    </security>
</xa-datasource>

...

<drivers>
                    <driver name="mysql" module="com.mysql.jdbc">
                        <driver-class>com.mysql.jdbc.Driver</driver-class>
                        <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
                    </driver>
</driver>

I see that in the CREATE SERVER statement I need to specify the jdbc driver file name. Can this also be the driver name? As here just "mysql"?

2) We have 2 source databases that mostly contain the same schema's and tables. In the past I've always changed the table names to reflect from whch source they are comming.

e.g. prod_P_GeneralData that refers to Patient.dbo.P_GeneralData in database1 (that we call production) and wh_P_GeneralData that refers to Patient.dbo.P_generalData in database 2 (that we call warehouse).

I could possibly now just create two schema's in the vbd named production and warehouse but then our old scripts would not work anymore.

Would it be possible to add something as a table prefix when importing the tables?

3) Is there a use statement to select a certain virtual schema when writing views/procedures? I assume that views and/or procedures that call each other must always be specified before the view/procedure that calls them?

E.g.

-- this will probably throw an error since b does not exist yet?
CREATE VIEW a .... as select 1 from b
CREATE VIEW b .... as select 1 from table_x 

4) Can we combine both DDL and XML definitions? I can imagine that in some cases it might be easier to do some things in the Teiid designer (like setting roles and privileges). But then again do some other things (like creating view and procedures) in a DDL format. Can these DDL scripts then be added to a vbd file?

5) Can the DDL be written over multiple files? I can image that this would make things more maintainable. Is there a way to tell teiid in which order to deploy the DDL scripts?

6) Is there a way to tell the importer when executing "IMPORT FOREIGN SCHEMA public FROM SERVER ..." that only a selection of the tables should be imported?

7) I think this is not in the scope of this feature but would it be possible in the future to create physical tables (alternative to temporary foreign tables) in some existing databases? We use this quite often to store some big temporary tables. Maybe in a DDL script there could be native create statement code followed by teiid import table statements?

[shawkins]

Hi Bram,

Thanks for the interest in a new feature.  Ramesh has put in a lot of work to get us here.  The hope is that we'll have things pretty well ironed out in the 9.3 release.

> Can this also be the driver name? As here just "mysql"?

Yes.  A typical pattern would look like:

CREATE SERVER my_source FOREIGN DATA WRAPPER oracle OPTIONS ("jndi-name" 'java://test-server')

That contains effectively the same information as the source tag.  However I think there is an issue with the grammar where the server type needs to be optional.

> Would it be possible to add something as a table prefix when importing the tables?

To understand correctly, you want to import from two source into the same schema?  Or do you mean you'll have separate models/schemas, but want to manipulate the Teiid naming more?

> Is there a use statement to select a certain virtual schema when writing views/procedures? I assume that views and/or procedures that call each other must always be specified before the view/procedure that calls them?

Yes, the resolving ordering will be based upon the declaration order.  "SET SCHEMA name" will allow you to set the current schema.

> Can we combine both DDL and XML definitions? I can imagine that in some cases it might be easier to do some things in the Teiid designer (like setting roles and privileges). But then again do some other things (like creating view and procedures) in a DDL format. Can these DDL scripts then be added to a vbd file?

The current plan is that if the VDB root is DDL, then everything will be DDL.  It isn't in the first commits, but yes we plan to allow a vdb zip that uses the root DDL approach - [TEIID-4724] Allow support for vdb.ddl in the .vdb files - JBoss Issue Tracker

That's been pushed to 9.3, so I need to temporarily remove references to that from the documentation.

> Can the DDL be written over multiple files? I can image that this would make things more maintainable. Is there a way to tell teiid in which order to deploy the DDL scripts?

That should be addressed with TEIID-4724 as well.

> Is there a way to tell the importer when executing "IMPORT FOREIGN SCHEMA public FROM SERVER ..." that only a selection of the tables should be imported?

Yes, all of the import options are allow on the IMPORT statement - ... INTO schema OPTIONS (...)

> I think this is not in the scope of this feature but would it be possible in the future to create physical tables (alternative to temporary foreign tables) in some existing databases? We use this quite often to store some big temporary tables. Maybe in a DDL script there could be native create statement code followed by teiid import table statements?

That's certainly possible to embedded a "create if not exists ..." that would be run at startup, but yes that would be considered a new and separate feature.

Steve

[rareddy]

Even on the "CREATE SERVER" command, I want to expand such that, you can define a jar file, and rest of the properties like url, user, and password and create the necessary data source underneath it. I had this working earlier, but I had to rollback for the deployment based vdb version. I will re-consider for 9.3.

Basically you can do everything you were able to do with XML version VDB with DDL version of VDB in what coming in 9.2. I have to say the CREATE SERVER command needs little getting used to. Will make the documentation clear if this not case already. You should create JIRA for the creation of table. Love to hear more feedback once you give it a try.

[gadeyne.bram]

Hi Steven,

Thank You for this extensive answer.

> Would it be possible to add something as a table prefix when importing the tables?

To understand correctly, you want to import from two source into the same schema?  Or do you mean you'll have separate models/schemas, but want to manipulate the Teiid naming more?

I'd like to manipulate the teiid naming more. In this case it's 2 models that each reside in their own model.xmi file. In a project a few year ago we had some software that depended on teiid but this software could only work within one schema. That's the reason that we manipulated the names so that the table names are unique over all schema's. It's also shorter when writing queries e.g.

production.dbo.P_GeneralData -> prod_P_GeneralData

warehouse.dbo.P_GeneralData -> wh_P_GeneralData

That's certainly possible to embedded a "create if not exists ..." that would be run at startup, but yes that would be considered a new and separate feature.

That would really be great!

[rareddy]

The only thing I can think of of interms of renaming is, you if you knew the name of the table, you could do

CREATE SCHEMA X;

SET SCHEMA X;

IMPORT FOREIGN SCHEMA FROM SERVER blah;

ALTER TABLE FOO RENAME TO BAR;

ALTER TABLE FOO2 RENAME TO BAR2;

IMPORT FOREIGN SCHEMA FROM SERVER blah2;

ALTER TABLE FOO3 RENAME TO BAR3,

but this requires you having knowledge about all the tables available in the sources. If that is case, you can start the right DDL itself to begin with

[shawkins]

> I'd like to manipulate the teiid naming more.

That could be something like a name templating feature for import.  Otherwise I don't see a great way to do this currently - the closest option would be a custom MetadataRepository that manipulates the already loaded Schema, which didn't have good methods for this until TEIID-4629 (remove the table change the name, re-add).

> That would really be great!

Please capture your thoughts in a JIRA.

[gadeyne.bram]

Hi,

I've just created a first ddl based vdb with a subset of our schemas. It works really fast and really well!

I've reported one bug TEIIDWEBCN-43 because after deploying a DDL based version, the console is unusable.

Some problems that I encountered, but I'm not sure if they are bugs or wrong syntaxes from my end.

After I imported some tables in a foreign schema named izalerting, I wanted to set the cardinalities for the tables. (e.g. on the table alertresult)

I tried several variations but they all say Group does not exist: alertresult

These are some variations I've tried. They are all executed immediatly after the import statement.

ALTER FOREIGN TABLE "alertresult" options (add cardinality 6000);

ALTER FOREIGN TABLE alertresult options (add cardinality 6000);

ALTER FOREIGN TABLE "izalerting.alertresult" options (add cardinality 6000);

I can however execute "select * from alertresult". So I would assume that the name is correct.

I've also tried renaming tables as Ramesh suggested.

When I try to execute the following:

ALTER TABLE users RENAME TO izalerting_users;

Then I get an error

[standalone@localhost:9990 /] deploy D:\workspaceTeiid\IZ\vdb4-vdb.ddl

{"WFLYCTL0062: Composite operation failed and was rolled back. Steps that failed:" => {"Operation step-2" => {"WFLYCTL0080: Failed services" => {"jboss.deployment.unit.\"vdb4-vdb.ddl\".PARSE" => "org.jboss.msc.service.StartException in service jboss.deployment.unit.\"vdb4-vdb.ddl\".PARSE: WFLYSRV0153: Failed to process phase PARSE of deployment \"vdb4-vdb.ddl\"    Caused by: org.teiid.metadata.ParseException: TEIID30386 org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered \"TABLE users RENAME [*]TO[*] izalerting_users;\" at line 21, column 26.Was expecting: \"column\" | \"parameter\"    Caused by: org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered \"TABLE users RENAME [*]TO[*] izalerting_users;\" at line 21, column 26.Was expecting: \"column\" | \"parameter\""},"WFLYCTL0412: Required services that are not installed:" => ["jboss.deployment.unit.\"vdb4-vdb.ddl\".PARSE"],"WFLYCTL0180: Services with missing/unavailable dependencies" => undefined}}}

The same happens when executing e.g. (I thought it might have something to do with the word users)

ALTER TABLE alertresult RENAME TO izalerting_alertresult;

[rareddy]

users is I believe is reserved word, so you need to use "users" (in quotes), you also need quotes around " izalerting_users" as if there are any special characters in the name. With ALTER you can add/remove properties, add/remove columns on foreign table, change data type, rename etc.

[gadeyne.bram]

Hi Ramesh,

Executing ALTER [FOREIGN] TABLE "alertresult" RENAME TO "izalerting_alertresult"; does result in the same error message.

Regadering the add cardinality property...

ALTER [FOREIGN] TABLE "alertresult" options (add cardinality 6000);

This complains that the "group" alertresult could not be found.

I've tried some additional versions: "vdb.izalerting.alertresult", "izalerting.alertresult","izalerting"."alertresult" and "alertresult" but it always complains about Group does not exist.

[rareddy]

if you can provide small vdb that represents this behavior, I will try to debug.

[gadeyne.bram]

Hi rareddy,

I've created a small sample to debug this.

Execute this SQL to create a MySQL table

create database tmptables;

use tmptables;

create table teiidtest(

  id int primary key not null

);

This DDL script contains the two example line's I'm trying to explain.

create database testvdb version '1.0.0';

use database testvdb version '1.0.0';

CREATE FOREIGN DATA WRAPPER mysql5;

CREATE SERVER tmptables FOREIGN DATA WRAPPER mysql5 OPTIONS ("jndi-name" 'tmptables');

CREATE SCHEMA tmptables SERVER tmptables;

SET SCHEMA tmptables;

IMPORT FOREIGN SCHEMA tmptables FROM SERVER tmptables INTO tmptables

    OPTIONS(

       importer.useFullSchemaName false,

       importer.tableTypes 'TABLE,VIEW'

);

-- uncomment this for the rename issue.

-- ALTER TABLE "teiidtest" RENAME TO "tmptbl_teiidtest";

-- uncomment this for the add property issue.

-- ALTER TABLE "teiidtest" options (add cardinality 1);

[rareddy]

Sorry for mis-leading, but TEIID-3349 only handling the ALTER on COLUMN names with RENAME. A new JIRA is required on the RENAME case. The ALTER TABLE OPTIONS seems to be bug, as to the differences in processing between XML and DDL based VDBS. So, JIRA may be needed here too.