Ideally, I'm thinking an improvement to the multi-source feature so that would mean more formal metadata.  Again, the big advantage of that is the ability to add/remove sources dynamically.  I'm not sure what you mean by "the multi-source element is an internal construct - so there isn't a concept of external discriminating data" so I'm not sure how that affects the difference in the partitioned union approach versus multi-source.  I guess my question comes down to, does/would Teiid treat the multi-source model as a partitioned-union when it comes to query planning?

Here are the some relevant facts about our use case:

• Each partition represents a physical data source and each physical source is uniquely identified through one or more columns
• We'd want the physical data sources to become part of the VDB when they are activated and drop out of the VDB when the are deactivated
• Physical data sources activate & deactivate infrequently
• These physical data sources know their partition key when they are activated

Here's what I understand of the trade-offs of the two approaches that you outline:

• The multi-source model approach requires that we know the partition key at the time Admin.addSource is called.  While this could be inconvenient, I don't think it presents a big problem.
• The partitioned-union approach requires us to redeploy the VDB every time a physical source is activated or deactivated.  Not a big deal since it occurs infrequently.
• The partitioned-union approach requires us to enhance our query generation capability.  This isn't a big problem.
• The partitioned-union approach potentially allows us more data-driven partitioning and to defer the partition naming to query time.  While I like the flexibility, I'm not sure we need it.
• The partitioned-union approach would require multiple models all having identical metadata.  How does that affect memory costs?

How about this wild idea:  What if Teiid had a new model type, a shared-metadata model?  At VDB deploy time, a regular model is supplied.  At runtime, a new model could be deployed that shares its metadata with the deploy-time model.  The new model would be a separate named object within the VDB thus could be addressed specifically or, to emulate the existing multi-source model feature, it could be part of a partitioned-union.  Perhaps with this approach, we would gain all the advantages of multi-source (dynamic add/remove and lower memory cost) with the advantages of a partitioned union (deferring the partition naming to query time).

> These requirements seem some what similar to of distributed database,

That's an interesting observation and I think correct.  Our use case isn't about distributed databases since our sources all have distinct data sets which is why I'm happy with (hopefully) relatively simple extensions to the existing feature set.  Speculating a bit on distributed database storing redundant:  The cost of accessing data from one node may be significantly different than the cost on another node (I'm thinking of a lazy hierarchical storage system).  Some of the use cases get crazy complicated and I can see needing to either (1) open up the Teiid planner to allow the applications to influence the query execution or (2) moving all this problem up to the query level and making it the application's problem.

Somewhat off-topic but related to #2:  In our situation, we are growing more sophisticated logic to generate queries.  In some cases, we issue queries to Teiid in order to build the query to answer user questions.  Unfortunately, SQL isn't really sophisticated enough to concisely express these meta-query influenced queries.  As a result, we have a paper-clip and bailing wire solution using Velocity in some cases and Java code that manipulates the Teiid language objects in other cases.  As a simple but illustrative example, we have a bunch of tables that are unioned together to form a data set.  We don't know what the set of tables is a-priori so we write Velocity scripts like this:

SELECT *

FROM (

     #foreach($t in $db.query("select SchemaName, Name from sys.tables where Name='magic_table_name' and SchemaName like 'magic_schema_pattern'"))

          SELECT * FROM ${t.SchemaName}.${t.Name} AS T

          #if( $velocityHasNext ) UNION #end

     #end

) AS T1

My point is that as data partitioning schemes becomes more sophisticated, I see the need to involve application semantics in the query execution somehow and SQL is a bad solution for this sort of thing.

Anyway, this is unrelated to my more immediate problem:  multi-source or partitioned-union that supports multi-column partitions

> The template above will work just fine using a procedure an dynamic sql.  Presuming that the template is actually defining a view, then there is too much of a performance hit for encapsulating as a procedure.

We used to expose these velocity templates as tables/views backed by a custom translator which would resolve the template and connect back into Teiid to execute the query.  We have recently switched to the following pattern:

1. At startup, we define views using DDL models of the form CREATE VIEW v AS SELECT null, null, null
2. Once the VDB is ready, we execute the velocity script and issue ALTER VIEW v AS <resolved velocity template>
3. Periodically, we recheck the resolved velocity script.  If it's different than before, we re-issue the ALTER VIEW

This gets us the best of both worlds, I think.

> Can you give an example of how the multiple column partitioning would work?

Sure.  We might have a table representing the results of the Linux 'top' command.  In a cloud environment, we would have a collector running on each VM makes performance data, such as from top, available through Teiid tables.  To make the example somewhat contrived but realistic, we might have multiple groups of VMs where each group is uniquely identified by customer-id and within each group, we require that VM be uniquely named.  The source key is [customer-id, machine-name] because that uniquely identifies each collector running on each machine.  Each collector knows its customer-id and machine-name so it can publish fully-qualified data. 

We want to push queries down to the collectors rather than publish collector data to a central repository thus the importance of routing queries intelligently to collectors that match the user's specified customer-id and machine-name.  We could introduce filtering intelligence into the translator but I'd rather not.

> And then is it for application level reasons why you wouldn't want to map that to the multi-source source name construct? For example by making the source name represent data via a simple concat: "part1-part2"

We could do that but it introduces complexity either on the user or on the application query generator.  Customers often use naming conventions to subdivide their VMs, for example prefixing the name of each machine as DB* if it's a database or JBOSS* if it's a jboss server.  So, if the user wants data from all of their databases, SELECT * FROM t WHERE customer-id='xyz' AND machine-name LIKE 'DB%', the application would have to disentangle the source column.  This isn't impossible if we use XML in the source column and make clever use of the XML query facilities but it is rather complex.  

Perhaps a simpler approach would be maintaining a join-table of [customer-id, machine-name, source].  The database query from above would be represented as SELECT source-xref.customer-id, source-xref.machine-name, t.* FROM t JOIN source-xref ON top.source=source-xref.source WHERE source-xref.customer='xyz' AND source-xref.machine-name LIKE 'DB%'.  With appropriate query hints, I think that we can ensure that Teiid hits the source-xref table first to resolve all of the sources and then normal multi-source processing would handle the query routing.

> This isn't impossible if we use XML in the source column and make clever use of the XML query facilities but it is rather complex. 

It occurs to me that we could create a view on top of each multi-source table that disentangles the source-name and projects the multi-column equivalent.  This would keep our query generator logic simple but it would require a view for each table.  Maybe a clever application of a delegating translator could each the effort required to create the views...

> Perhaps allowing for a non-string type (which could even be an array) would be a possible enhancement, then you wouldn't have to parse the value, just access array fields

I see this as a minor improvement as it is a tiny bit easier for the collectors to fill out arrays of data rather than form XML.  At this stage, I'm not interested in filing a JIRA for the enhancement, though.  We can work with the XML approach first and see how well it works.

Thanks guys

> There is a new system function called tokenize

I'm not excited by using simple tokens to separate out columns because you end up having to be careful about the characters in the names themselves.

On a somewhat different topic, I'd like to expose the Admin.addSource, removeSource, etc functions in a VDB to make it easier for our clients to add/remove data sources.  It looks like the Admin object is only available when Teiid is deployed in Jboss.  Is there some way to get at the underlying constructs without going through JBoss and security?  Although it isn't a big deal (we normally deploy in Jboss) but it's easier to test with embedded server and I'm thinking of implementing different security anyway - I think we'd want to secure acces to add/remove sources by model rather than a VDB-wide check.