How to import view tables with Teiid Designer and make them accessible via odata

[cjohn001]

Hello together,

as I am currently having problems to get expand operations efficiently working with Teiid ontop of a mysql database, I tried to create a view table which holds the joined data to let mysql handle the join operation rather than Teiid. Unfortunately, when using "Teiid Connection >> Source Model" the view table from my database is not imported at all. So far I always used this approach to import my database structure as I always got errors in the import of foreign keys when using the "JDBC Database >> Source Model" importer. I have now tried to use "JDBC Database >> Source Model" again to see if this might be a way to import the view table. Well, the view table is imported, but now two questions are arising.

1. I again get errors for my foreign keys, but I do not understand what say denote. As a matter of principle the foreign keys seem to work at least in respect to navigation properties that are generated for them. Maybe someone can explain why I see errors for foreign keys in the Teiid-Designer Diagram, and how I can get rid of them. The following image shows the problem.

As a further result, the connections between the tables in the package diagram, which normally result from the foreign keys do not exist in the imported structure. The following 2 images show what I mean. The left image is the result when using the jdbc importer and the right image when using  "Teiid Connection >> Source Model"

2. Even so the view table (first image, table named view1) appears in the import, I cannot access it via odata. For the view table no EntityType is generated in Metadata.xml and it seems I cannot access the table in any way via a collection. So my question is, what I am doing wrong? Or are view tables not supported by Teiid?

[rareddy]

You can check problems/errors tab to see what are the errors.

If view table does not have a primary key or unique key defined on it it will not be exposed through odata.

Unfortunately we won't be doing any feature work on Designer. Also only critical errors may be considered. It is recommended you switch over to dynamic vdbs with ddl.

Ramesh..

[cjohn001]

Hello Ramesh,

do you have any kind of tutorial at hand which shows how to use dynamic vdbs with ddl? Examples for tables, views, row-based security contstraints, and procedures would be great I assume I would have to write them by hand? Or is there tool support for these things, besides Beetle Studio? I had a hard time with Beetle Studio in the past. If Beetle studio is the way to go, it would be great do have a dockerized version available which simply does the job. As I could not get Beetle Studio in the past I moved to Teiid Designer.

I still have not made any progress with the stored procedure stuff, even so there is some documentation available ( even not enough for me to get things up and running), I am worrying that things get even worse switching to ddl were I do not know of  decent tools support

[rareddy]

I do recommend using the DDL, that is where we are moving to.

We are moving away from standalone Beetle studio to be integrated with Syndesis, so I do not recommend using it. The VDB semantics and all the DDL are documented here [1] and [2]. If you are working with a single database it is a lot easier working without a Designer.  BTW, you do not have to start fresh, you can export your current VDB in Designer as XML based VDB.  Which can be further converted to DDL based one using techniques defined here [3] as a starting point.

For stored Procedures see [4]. Basically Teiid has whole Procedure language constructs like T*SQL for you to write your procedure.

[1] DDL VDB · GitBook

[2] Schema Object DDL · GitBook

[3] teiid-openshift-examples/README.md at master · rareddy/teiid-openshift-examples · GitHub

[4] Virtual Procedures · GitBook

[blafond]

I agree with Ramesh around moving to DDL.

In Designer, the requirement for FK is to have the table/view Key columns defined AND the primary key column of the referenced table.  If there are no "links" in the diagram, then this would also indicate that the UniqueKey isn't set.

[cjohn001]

Hello Bary,

I unfortunately cannot follow you. The 2 picture of the imported database were created from the same database using two different importers,

1. jdbc importer, messing up the foreign keys

2. "Teiid Connection >> Source Model" messing up the view table

Even so I remove the view the jdbc importer messes up the foreign keys. As the second option to import via "Teiid Connection >> Source Model" is working, I would asume this is a bug in the jdbc importer.

Note: As I am now stuck with designer for the last two weeks now, I will investigate into the DDL stuff, even so I do not see any benefits yet but rather a big disadvantage of losing the importer functionality.

I followed Rameshs description regarding conversion of my xml vdb. This seems to have worked. I am wondering now, if you guys could recommend an efficient way to develop  with the DDL files. I mean using a text editor and loading the file into teiid is probably not the most efficient way to develop stored procedures as this will make things very difficult to debug. So how does your toolchain look like to do things efficiently? Thanks for your help

[rareddy]

>Even so I remove the view the jdbc importer messes up the foreign keys. As the second option to import via "Teiid Connection >> Source Model" is working, I would asume this is a bug in the jdbc importer.

This does use the importer defined in the Teiid's translator to import the metadata. So, what is the issue in using this?

> I do not see any benefits yet but rather a big disadvantage of losing the importer functionality.

as mentioned above, the impoter functionality is also part of Teiid without Designer,  so what we are losing is importing the metadata from design time to deploy time. Obviously, an UI.

>. I mean using a text editor and loading the file into teiid is probably not the most efficient way to develop stored procedures as this will make things very difficult to debug. So how does your toolchain look like to do things efficiently?

Right now we want to solve this with good documentation of DDL, in our experience most SQL developers, irrespective of Teiid develop their schema manually. BTW, Designer does not provide any debugging but does provide quick validation of query. So, if you want use Designer to start with, once done convert to DDL and start using for minor changes.

[cjohn001]

Hello Ramesh,

thanks for the Feedback.

Well, metadata loading at deploy time rather than design time sounds like an advantage. This is than actually also the answer for how I can deal with the CARDINALITY settings. Means I just need to restart Teiid

In this case I will not further investigate the Designer topic.

I went through the documentation, yesterday. As the syntax seems to be quite close to SQL I think I will be able to handle it without UI

Maybe one further question to the DDL files. I have not found an answer to the question in the docs yet. Seems that I do not need to explicitely specify all tables in the ddl file but can rather let Teiid Import the database structure for me. I mean with

IMPORT FOREIGN SCHEMA FROM SERVER xxx INTO yyy

If I go this way, is it than possible, to reference tables and columns in the DDL below the import statement? I mean, if I write stored procedures in the vdb.ddl or do grant priviledges on tables or rows, do I have to explictely define the structure of the database beforehand to be able to reference entities in the stored procedures below? Or can I simple use the Import functionality and than just add the Teiid specific stuff ontop?

[rhn-engineering-rareddy]

> This is than actually also the answer for how I can deal with the CARDINALITY settings. Means I just need to restart Teiid

You do not need to restart, you can add in DDL like "ALTER VIEW foo ADD OPTION (CARDINALITY xyz)" to add cardinality after the "IMPORT FOREIGN .." statement.

> If I go this way, is it than possible, to reference tables and columns in the DDL below the import statement?

Yes.

> can I simple use the Import functionality and than just add the Teiid specific stuff ontop?

Yes, you can. However if your schema does not change often or not too cumbersome to define you can define it using the DDL then you have full flexibility to edit it, however you need to make sure it is representative of the source.