Ingest specific SQL tables from a schema

Hi Nikhil,

Thank you for pointing that out. I am aware of this option. However, in this specific case, I only know the name of tables that I want to ingest and not the others that exist in this particular schema. For eg: I know the name of the 6 tables that I want to ingest and there are a total of 200 tables in the schema. So essentially, I don’t know the name of the other 194 tables that I want to exclude from ingestion. Hence in this scenario, the “exclude tables” option can become very time consuming, which is why I was exploring if there’s any other way around to this.

Thanks for your response!

Thank you for your response Jeff. It is indeed strange that Collibra has not included this feature because this seems to be one of the most important and basic ones. I would be glad if you could share how you did it. It might point me into the right direction.

Thank You
Rahul Nair

Rahul:
We use Epic’s Electronic Health Record. It’s database has over 25,000 tables. I’ve developed a desktop application that screen-scrapes a table’s data dictionary in Epic and creates the Collibra upload worksheets. We have ingested 300+ tables out of the 25,000+ this way,. However, we have to manually re-ingest a table when it changes. I’m waiting to hear from Alvin if his solution scales. That would be best, but I’m not sure how we could load 300 table names into a connection string.

Hi Jeffrey,

This is indeed an interesting solution and it might be very helpful for me in the near future. Thank you for sharing your approach!

Collibra expects us to suggest this in their ideation platform to take up for enhancements based on # upvotes received, in case you have not created one already.

I agree, I could not wait and found workarounds. I created an ideation ticket for selectively profiling from the ingested schema’s and seems like it is planned now. In case this is something you want you may want to vote for it.

FYI: Ideation Platform – Collibra | Product Resource Center

Hi Alvin,

This looks interesting. Could you please elaborate a bit? What tool are you using that has the “Connection details” screen you are displaying?

Thank you!

Thanks Alvin! This looks interesting. I see that you are passing the arguments in the “Tables” connection string slightly differently than I was. I believe this should work for me as well. Let me try this out and report back the results. Thanks again!

That’s interesting ! Even though support suggested to use fully qualified names when i created the support ticket, it still did not work. Finally it was concluded that selective ingestion/profiling is not available for ingestion via Job Server.

By any chance are you ingesting via EDGE?

Good question

Each property in the connection string can hold a lot of information so the short answer is yes, the solution does scale.

Here’s a quick test I just did for 200 dummy tables I created in that same HumanResources Schema :

Here’s what I’ve put in the Tables string (I’ve selected tables 0-198):

Here’s the Catalog config UI:

And here are the results which only include the dummy tables I created (and none of the original tables in HumanResources):

Ah you’re using Oracle. So every JDBC driver is different and many have their own properties you can add to the connection string so of course, a SQL solution probably won’t work for Oracle.

Your best bet would be to tackle this problem through permissions management on the Oracle side.

The recommended route in your case would be to assign the relevant permissions to the Oracle DB user (that you’re using to connect from the Catalog to Oracle) to just the 300 tables and not the 25,000 - if possible.

The catalog will only ingest tables it has permissions to select, so if the Oracle DB user can only select 300 tables, only 300 tables will be ingested.

I appreciate that adding permissions to 300 or so tables isn’t particularly ideal, but it would be a one time activity.

This holds true for all connections, I’d say, if you can, it’s best practise to use a Service Account, dedicated for connections to Data Sources via Collibra, and for that account to only have access to data it needs access to.

If you’d like more help on Oracle specifically, I’d be happy to pick up on this separately, equally, give support a nudge and they’ll steer you in the right direction.

Cheers!

Alvin

Yep - we use a dedicated Collibra service account for all database accesses. The table permissions idea might work but I’d like to see if we can keep this off the dba’s table. I’ll check the Oracle Tables property documentation (unless you’d like to be involved as well). This is a huge issue in healthcare. If we can crack this nut it will be a BIG win for a lot of orgs.

Alvin:
Wondering if you’ve had any luck with this? I’d be glad to work with Oracle directly if you want. This would be a HUGE win for healthcare clients.
Regards,
Jeff