[resolved] Print retrieved schemas

Five Stars QR
Five Stars

[resolved] Print retrieved schemas

Hello !
I would like to export/print the schemas of the tables of a remote database (more than 100 tables).
I already managed to retrieve all the schemas (using this tutorial : ).
Now i want for each schema, for each column, get the column name and the data type, size, and nullability.
Is there a way to loop through all the schemas ?
How can I get those data in a flow ?
Or is there a simpler way to achieve this task with Talend ?
I look forward to your answers Smiley Happy

Accepted Solutions
Five Stars QR
Five Stars

Re: [resolved] Print retrieved schemas

Hi,
Thank you Sabrina for your advice ; following your idea i managed to retrieve my info using this process :
NB: the queries are written for Oracle SQL.
Firstly i read the table names from my database :"SELECT DISTINCT table_name FROM user_tab_columns".
After that, with tJavaRow i store "input_row.table_name" in a context variable, and send it as the output "output_row.table_name".
Then i use tFlowToIterate to loop through the table_name's.
I execute this query to retrieve the columns' info : "SELECT table_name, column_name, data_type, data_length, data_precision, nullable FROM user_tab_columns WHERE table_name = '" + ((String)globalMap.get("row2.table_name")) + "'"
Finally, i write the output in csv files, whose names are iterated through the context variable.
Give back to Caesar what is Caesar's, i was inspired by that blog  : www.talendfreelancer.com/2013/09/talend-tflowtoiterate.html

All Replies
Moderator

Re: [resolved] Print retrieved schemas

Hi,
Have you tried to output your tables as csv files to print your columns?
The work flow should be:t<DB>Input-->tfileoutputdelimited.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Sixteen Stars

Re: [resolved] Print retrieved schemas

There is an easy way of doing this....but it might be frowned upon by Talend. It is entirely safe (so long as you follow these instructions to the letter).
1) Go to your workspace, find the project folder within that and then navigate to the "metadata" folder within that.
2) If this is some metadata from a database, double click on the "connections" folder.
3) In the "connections" folder, you will see all of your database connections. You need to copy the ".item" file that is named the same as your db connection which holds your schemas.
4) Place the copy in another folder away from your workspace.
Now you are safe to do what you want to the copy.
This is a simple XML file. You will need to work out the schema of this file for yourself, but it is very simple. You can get all of the information you require from this file using a Talend job.
Hope this helps. 
Five Stars QR
Five Stars

Re: [resolved] Print retrieved schemas

Hi,
Thank you Sabrina for your advice ; following your idea i managed to retrieve my info using this process :
NB: the queries are written for Oracle SQL.
Firstly i read the table names from my database :"SELECT DISTINCT table_name FROM user_tab_columns".
After that, with tJavaRow i store "input_row.table_name" in a context variable, and send it as the output "output_row.table_name".
Then i use tFlowToIterate to loop through the table_name's.
I execute this query to retrieve the columns' info : "SELECT table_name, column_name, data_type, data_length, data_precision, nullable FROM user_tab_columns WHERE table_name = '" + ((String)globalMap.get("row2.table_name")) + "'"
Finally, i write the output in csv files, whose names are iterated through the context variable.
Give back to Caesar what is Caesar's, i was inspired by that blog  : www.talendfreelancer.com/2013/09/talend-tflowtoiterate.html
Five Stars QR
Five Stars

Re: [resolved] Print retrieved schemas

Hi rhall_2.0,
I tried your workaround. In my opinion it is a quick way to get the info but tedious to format and export.
Although it doesn't suit my needs, thank you for your suggestion !
Sixteen Stars

Re: [resolved] Print retrieved schemas

Not a problem QR. Just so that you know, everything about Talend Jobs is stored in the .item files. I use this process to perform fast audits on my jobs, metadata, etc, using Talend. This can be really useful if you need to find something out about your jobs and you have a lot of jobs in your project. With regard to the difficulty in exporting and formatting, I guess I don't see this since I use it quite a bit. 
Five Stars QR
Five Stars

Re: [resolved] Print retrieved schemas

rhall_2.0,
Thanks for the tip. I spent a few minutes browsing the .item of my project. Out of curiosity, what kind of information would you look for about jobs in the .item files, that you cannot find (or with more difficulty) in Talend ?
Also, do you use the XML components of Talend to import/export that info ?
Sixteen Stars

Re: [resolved] Print retrieved schemas

One example that comes to mind is based on a rule we had at one of my clients. The rule was that all tRunJob components have the "Transmit whole context" option set to TRUE. There was some concern over whether this had been done in all jobs. We had over 100 jobs and this would take a couple of hours at least to check this by opening each job and checking the tRunJob components used (as we all know, Talend Studio is not the fastest). We had a base job for looking through the .item files which we could parameterise to get different info. A 10 minute change to a copy of this job  enabled us to find 3 jobs which did not meet this rule and we were able to isolate the components which needed changing. 
Five Stars QR
Five Stars

Re: [resolved] Print retrieved schemas

Thank you rhall_2.0 for your example ! It is a clever way to perform this kind of task, i'll keep that in a corner of my head, i'm sure it will be useful someday !