Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
srikanth_chinna
Contributor II
Contributor II
‎2020-07-03 05:58 PM

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

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

Labels (3)
Labels
339 Views
0 Likes
1 Solution

Accepted Solutions
srikanth_chinna
Contributor II
Contributor II
‎2020-07-09 01:52 PM
Author

Thank you my friend.

View solution in original post

339 Views
0 Likes
9 Replies
manodwhb
Champion II
Champion II
‎2020-07-04 02:56 AM

@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.

339 Views
0 Likes
manodwhb
Champion II
Champion II
‎2020-07-04 02:56 AM

@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.

339 Views
0 Likes
srikanth_chinna
Contributor II
Contributor II
‎2020-07-04 03:33 PM
Author

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

339 Views
0 Likes
srikanth_chinna
Contributor II
Contributor II
‎2020-07-04 03:33 PM
Author

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

339 Views
0 Likes
manodwhb
Champion II
Champion II
‎2020-07-04 11:18 PM

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?
339 Views
0 Likes
manodwhb
Champion II
Champion II
‎2020-07-04 11:18 PM

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?
339 Views
0 Likes
JohnRMK
Creator II
Creator II
‎2020-07-07 10:50 AM

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-orac...

 

339 Views
0 Likes
srikanth_chinna
Contributor II
Contributor II
‎2020-07-09 01:52 PM
Author

Thank you my friend.

340 Views
0 Likes
srikanth_chinna
Contributor II
Contributor II
‎2020-07-09 01:52 PM
Author

Thank you my friend.

339 Views
0 Likes