topic Re: Can we export data from 500 different Oracle tables into CSV files in a different file server? in Talend Studio

Can we export data from 500 different Oracle tables into CSV files in a different file server?

srikanth_chinna — Fri, 03 Jul 2020 21:58:37 GMT

Hi,

Thank you for the help in advance.

I'm new to Talend, currently using Talend Enterprise edition 7.1.

My Doubt / Questions:

1. Can we export data from 500 different Oracle tables into CSV files in a different file server? If so, How and what all components i need to use in my job? Your

help is greatly appreciated.

2. How the each tables schema will be dynamically changed at run time?

My Requirement:

----Extract Data from 500 Oracle tables to CSV files and import the CSV files into Snowflake.

Requirement Explanation:

--I have 500 tables data that i need to export to csv files in a different file server.

--Each tables schema is totally different --

--Some tables have more than 30 Million records, in such case, i want to split the each csv file's data into 1000000 Million records ( I know we can do this in tFileOutPutDelimited - Advanced Settings using "Split output in several files")

Thanks again and your help is greatly appreciated.

Srikanth

Re: Can we export data from 500 different Oracle tables into CSV files in a different file server?

manodwhb — Sat, 04 Jul 2020 06:56:00 GMT

@srikanth_chinna , if you're using enterprise edition you can choose dynamic schema. but before that, you need to have all 500 table details like schema, table name you need to iterate and you can split into several file using split output in several files option.

Re: Can we export data from 500 different Oracle tables into CSV files in a different file server?

manodwhb — Sat, 04 Jul 2020 06:56:00 GMT

Re: Can we export data from 500 different Oracle tables into CSV files in a different file server?

srikanth_chinna — Sat, 04 Jul 2020 19:33:53 GMT

Hi Manohar,

Thank you for the reply.

Yes! i figured out the same yesterday and tested on few tables. Thank you for the advise though. that's helpful.

However, i'm now getting the issues in tFTPPut component . Please help.

I created some sample test files (both txt and excel) manually in my local directory and moved into the FTP file server using WinSCP and am able to move/copy the files. However, Talend job is throwing below exceptions. Kindly help.

tFTPPut_1 - File transfer fail.Permission denied

tFTPPut_1 Permission denied

3: Permission denied
at com.jcraft.jsch.ChannelSftp.throwStatusError(ChannelSftp.java:2846)
at com.jcraft.jsch.ChannelSftp._stat(ChannelSftp.java:2198)
at com.jcraft.jsch.ChannelSftp._stat(ChannelSftp.java:2215)
at com.jcraft.jsch.ChannelSftp.stat(ChannelSftp.java:2172)
at cb_git_prod_1.oracle_to_csv_all_tables_0_1.Oracle_To_CSV_All_Tables.tFTPPut_1Process(Oracle_To_CSV_All_Tables.java:3068)
at cb_git_prod_1.oracle_to_csv_all_tables_0_1.Oracle_To_CSV_All_Tables.tFixedFlowInput_1Process(Oracle_To_CSV_All_Tables.java:2594)
at cb_git_prod_1.oracle_to_csv_all_tables_0_1.Oracle_To_CSV_All_Tables.tFTPConnection_1Process(Oracle_To_CSV_All_Tables.java:1147)
at cb_git_prod_1.oracle_to_csv_all_tables_0_1.Oracle_To_CSV_All_Tables.tDBConnection_1Process(Oracle_To_CSV_All_Tables.java:868)
at cb_git_prod_1.oracle_to_csv_all_tables_0_1.Oracle_To_CSV_All_Tables.tPrejob_1Process(Oracle_To_CSV_All_Tables.java:623)
at cb_git_prod_1.oracle_to_csv_all_tables_0_1.Oracle_To_CSV_All_Tables.runJobInTOS(Oracle_To_CSV_All_Tables.java:3534)
at cb_git_prod_1.oracle_to_csv_all_tables_0_1.Oracle_To_CSV_All_Tables.main(Oracle_To_CSV_All_Tables.java:3283)
Exception in component tFTPPut_1 (Oracle_To_CSV_All_Tables)
3: Permission denied
at com.jcraft.jsch.ChannelSftp.throwStatusError(ChannelSftp.java:2846)
at com.jcraft.jsch.ChannelSftp._stat(ChannelSftp.java:2198)
at com.jcraft.jsch.ChannelSftp._stat(ChannelSftp.java:2215)
at com.jcraft.jsch.ChannelSftp.stat(ChannelSftp.java:2172)
at cb_git_prod_1.oracle_to_csv_all_tables_0_1.Oracle_To_CSV_All_Tables.tFTPPut_1Process(Oracle_To_CSV_All_Tables.java:3068)
at cb_git_prod_1.oracle_to_csv_all_tables_0_1.Oracle_To_CSV_All_Tables.tFixedFlowInput_1Process(Oracle_To_CSV_All_Tables.java:2594)
at cb_git_prod_1.oracle_to_csv_all_tables_0_1.Oracle_To_CSV_All_Tables.tFTPConnection_1Process(Oracle_To_CSV_All_Tables.java:1147)
at cb_git_prod_1.oracle_to_csv_all_tables_0_1.Oracle_To_CSV_All_Tables.tDBConnection_1Process(Oracle_To_CSV_All_Tables.java:868)
at cb_git_prod_1.oracle_to_csv_all_tables_0_1.Oracle_To_CSV_All_Tables.tPrejob_1Process(Oracle_To_CSV_All_Tables.java:623)
at cb_git_prod_1.oracle_to_csv_all_tables_0_1.Oracle_To_CSV_All_Tables.runJobInTOS(Oracle_To_CSV_All_Tables.java:3534)
at cb_git_prod_1.oracle_to_csv_all_tables_0_1.Oracle_To_CSV_All_Tables.main(Oracle_To_CSV_All_Tables.java:3283)
[statistics] disconnected

Srikanth

Re: Can we export data from 500 different Oracle tables into CSV files in a different file server?

srikanth_chinna — Sat, 04 Jul 2020 19:33:53 GMT

Hi Manohar,

Thank you for the reply.

Yes! i figured out the same yesterday and tested on few tables. Thank you for the advise though. that's helpful.

However, i'm now getting the issues in tFTPPut component . Please help.

tFTPPut_1 - File transfer fail.Permission denied

tFTPPut_1 Permission denied

Srikanth

Re: Can we export data from 500 different Oracle tables into CSV files in a different file server?

manodwhb — Sun, 05 Jul 2020 03:18:11 GMT

it is clearly saying that permission denied, you need to check the FTP credentials and have proper access to place file.

Try using filezila or winscp to connect to that ftp and are you able to copy file from your local system to that ftp location?

Re: Can we export data from 500 different Oracle tables into CSV files in a different file server?

manodwhb — Sun, 05 Jul 2020 03:18:11 GMT

Re: Can we export data from 500 different Oracle tables into CSV files in a different file server?

JohnRMK — Tue, 07 Jul 2020 14:50:34 GMT

Hello,

Here you can find all the requirement and logic

https://community.talend.com/t5/Design-and-Development/How-to-migrate-multiple-schema-data-from-oracle-db-to-postgres/m-p/222563/highlight/true#M122658

Re: Can we export data from 500 different Oracle tables into CSV files in a different file server?

srikanth_chinna — Thu, 09 Jul 2020 17:52:59 GMT

Thank you my friend.

Re: Can we export data from 500 different Oracle tables into CSV files in a different file server?

srikanth_chinna — Thu, 09 Jul 2020 17:52:59 GMT

Thank you my friend.