What is the fastest way to generate a CSV file from Oracle Database tables?

0
votes

I have a table in oracle database with some 700 million records around 5gb size. I have to run a job daily to replicate the oracle table to snowflake. Since there is no direct connector in snowflake to oracle database first I have to extract data from oracle database to a csv file and then upload that csv file to snowflake using python script. I tried sqlplus to extract data to csv. But sqlplus is taking ages to extract the data. so my questions are: 1. What is the fastest way to extract data from oracle database? 2. Since I have to run a job daily to update this oracle table into snowflake, Is there any other way to complete this task?

Any help is appreciated.

2
oraclesnowflake-cloud-data-platform
Are you really trying to write 700 million records to a CSV file? – APC
CSV file is just an interface i am thinking. My main agenda is write those 7oo million rewcords to snowflake. My oracle table gets updated daily and i have to update it daily in the snowflake too? – vishnu vardhan
I thought csv could be one solution in order to perform this task because there is no direct connector to oracle database from snowflake – vishnu vardhan
Have you looked at something like this? – Alex Poole
Personally, I'd look at using Python with two connectors. Open connections to both DBs and avoid writing to an intermediary disk. Use appropriate tuning, e.g. cx-oracle.readthedocs.io/en/latest/user_guide/… – Christopher Jones

2 Answers

1
votes

Have you tried SQLCLI? It supports spooling in CSV format. Please check for sample script:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9536328100346697722

You can download it from: https://www.oracle.com/database/technologies/appdev/sqlcl.html

After you get the CSV, you can use COPY command of Snowflake to ingest the data (instead of using Python scripts):

https://docs.snowflake.com/en/sql-reference/sql/copy-into-table.html

Of course, there are other ways. For example you can use Spark, or an ETL tool such as Talend which can connect both Oracle, and Snowflake.

This one shows how Talend can be used to migrate from MySQL to Snowflake but the idea is same for Oracle:

https://www.talend.com/resources/migrate-cloud-database-snowflake/

As I see, there is also a good recommendation about Golden Gate in the comments.

0
votes

Extarcting 700M records from source system will takes some times, My suggestion for history load is to use any ETL tool or Sqoop to extract your data from source system.

  • If your using any ETL tools you can connect snowflake using either JDBC or ODBC driver that will push your data from Oracle to snowflake.
  • If have hadoop you can use sqoop to extract data and then push your data to snowflake using COPY INTO command.