-
1. Re: How to exclude some tables from loading into teiid
rareddy Feb 27, 2012 12:22 PM (in response to rakeshsagar)Are you Designer or Dynamic VDBs?
In Designer
- Remove the unwanted tables from your source model
In Dynamic VDB
- Provide the import property to filter the table name. See http://docs.jboss.org/teiid/7.6.0.Final/reference/en-US/html_single/#d0e9521 and look for "tableNamePattern"
Ramesh..
-
2. Re: How to exclude some tables from loading into teiid
rakeshsagar Feb 28, 2012 4:49 AM (in response to rareddy)Hi Ramesh,
Thanks for your reply. I am using the Dynamic VDB.
Is there any property like "excludeTables" that we can mention in the model definition so that only those tables can be excluded.
For example I have 50 tables and I want to exclude only one table then we need to list all the 49 tables seperated by comma in the importer.tableNamePattern property.
Pleasee let me know how can we achieve this?
Thanks,
Rakesh.
-
3. Re: How to exclude some tables from loading into teiid
rareddy Feb 28, 2012 8:38 AM (in response to rakeshsagar)No, there is no such property.
See http://docs.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getTables%28java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String[]%29 for the "importer.tableNamePattern", as underlying JDBC interface uses the above method.
Ramesh..
-
4. Re: How to exclude some tables from loading into teiid
shawkins Feb 28, 2012 9:10 AM (in response to rareddy)Rakesh,
Can you log an enhancement request for an excludeTables/excludeProcedures importer property? Presumably it would be something like a regular expression that when it matches the table will not be imported.
Thanks,
Steve
-
5. Re: How to exclude some tables from loading into teiid
shawkins Mar 1, 2012 4:23 PM (in response to shawkins) -
6. Re: How to exclude some tables from loading into teiid
rakeshsagar Mar 8, 2012 9:37 AM (in response to shawkins)Hi Steven,
I had taken a look at this issue and its status is resolved.
Is the code for this included in 7.7.x branch??
Thanks,
Rakesh.
-
7. Re: How to exclude some tables from loading into teiid
rareddy Mar 8, 2012 10:12 AM (in response to rakeshsagar)Rakesh,
It is fixed on 8.0 on the trunk. You can always check the "Fix Version/s" field on the JIRA to see where the issue has been fixed. Also, if you look under "source" tab you will see the files that have been modified.
Ramesh..
-
8. Re: How to exclude some tables from loading into teiid
rakeshsagar Mar 8, 2012 10:36 AM (in response to rareddy)Hi Ramesh,
Thanks for the information.
The property importer.tableNamePattern only takes a string and so we can include only one table. and it doesn't take a regular expression as input.
Can we have an enhancement request for this property to accept regular expression and include only those tables that match the pattern.
Thanks,
Rakesh
-
9. Re: How to exclude some tables from loading into teiid
rareddy Mar 8, 2012 10:53 AM (in response to rakeshsagar)importer.tableNamePattern takes on the pattern matching same as the on JDBC DatabaseMetadata class. See the description about TableNamePattern on class description.
Ramesh..
-
10. Re: How to exclude some tables from loading into teiid
rakeshsagar Mar 8, 2012 11:37 AM (in response to rareddy)Hi Ramesh,
This is the description.
tableNamePattern
- a table name pattern; must match the table name as it is stored in the database.I tried using the sample program and it did not return any tables when I used the pattern.
The sample program is
DatabaseMetaData dbMetadata = connection.getMetaData();
ResultSet rs = dbMetadata.getTables(null, null, ".*", null);
Ideally this should return all the tables in the database. But it doesn't return any tables.
If I specify the table name instead of the pattern it only retrieves the table.
Please correct me if anything is wrong.
Thanks,
Rakesh
-
11. Re: How to exclude some tables from loading into teiid
shawkins Mar 8, 2012 11:46 AM (in response to rareddy)Rakesh,
As Ramesh is point you to, tableNamePattern is a LIKE patten string, so it does support wildcards - but not regular expressions. You can then also use the exclude regular expression if that isn't quite general enough. It can easily be flipped into an inclusion filter, with a regex that uses negative lookahead: (?!<inclusion pattern>).*
Steve
-
12. Re: How to exclude some tables from loading into teiid
yjma2001 Aug 2, 2012 3:35 PM (in response to shawkins)Steve:
Checking the issue https://issues.jboss.org/browse/TEIID-1959., it looks like that excludeTables property has been added into teiid 8.0 already. But I have question about how to include multiple tables.
For example, I have a big oracle database, but I only want expose following three database table through my dynamic VDB, how to use the tableNameParttern to do it? or do you know there are some other way to do it?
Here are sample tables
dbo.t_customers
dbo.t_sales
dbo.t_orders
What is the import.tableNamePattern looks like?
Thanks
Jack
-
13. Re: How to exclude some tables from loading into teiid
shawkins Aug 2, 2012 4:03 PM (in response to yjma2001)Jack,
See the previuos post. tableNamePattern is a SQL LIKE pattern passed to DatabaseMetadata.getTables. So in your case you could use t\_% (note that you have to escape the _ with whatever the appropriate escape character for your source is). However if you have other tables matching t_, then they will get included as well. To turn the exlcude regular expression into inclusion, use something like (?!dbo\.t_((customers)|(sales)|(orders))).* That will give you just those three tables even if the tableNamePattern allows more.
Steve