Home » RDBMS Server » Server Utilities » oracle solution for sqoop (oracle 12c exadata)
oracle solution for sqoop [message #677410] Wed, 18 September 2019 00:08 Go to next message
pa1sas3
Messages: 29
Registered: June 2005
Junior Member
Is there a utility that can generate csv/text files from the SQLs.
Requirement is - perform ETL (using sql query) for a bunch of sqls and generate output in csv files to be consumed by other systems.
Want to replace "sqoop using oozie and transformation in Hive" using Oracle SQLs and writing the output to csv.
Re: oracle solution for sqoop [message #677412 is a reply to message #677410] Wed, 18 September 2019 00:37 Go to previous messageGo to next message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Is there a utility that can generate csv/text files from the SQLs.

Many; SQL*Plus, SQL Developer, TOAD...

Re: oracle solution for sqoop [message #677413 is a reply to message #677410] Wed, 18 September 2019 01:29 Go to previous messageGo to next message
John Watson
Messages: 8075
Registered: January 2010
Location: Global Village
Senior Member
orclz>
orclz> set markup csv on
orclz> select * from scott.emp;

"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7369,"SMITH","CLERK",7902,"1980-12-17:00:00:00",800,,20
7499,"ALLEN","SALESMAN",7698,"1981-02-20:00:00:00",1600,300,30
7521,"WARD","SALESMAN",7698,"1981-02-22:00:00:00",1250,500,30
7566,"JONES","MANAGER",7839,"1981-04-02:00:00:00",2975,,20
7654,"MARTIN","SALESMAN",7698,"1981-09-28:00:00:00",1250,1400,30
7698,"BLAKE","MANAGER",7839,"1981-05-01:00:00:00",2850,,30
7782,"CLARK","MANAGER",7839,"1981-06-09:00:00:00",2450,,10
7788,"SCOTT","ANALYST",7566,"1987-04-19:00:00:00",3000,,20
7839,"KING","PRESIDENT",,"1981-11-17:00:00:00",5000,,10
7844,"TURNER","SALESMAN",7698,"1981-09-08:00:00:00",1500,0,30
7876,"ADAMS","CLERK",7788,"1987-05-23:00:00:00",1100,,20
7900,"JAMES","CLERK",7698,"1981-12-03:00:00:00",950,,30
7902,"FORD","ANALYST",7566,"1981-12-03:00:00:00",3000,,20
7934,"MILLER","CLERK",7782,"1982-01-23:00:00:00",1300,,10

14 rows selected.

orclz>
Re: oracle solution for sqoop [message #677421 is a reply to message #677413] Wed, 18 September 2019 07:46 Go to previous messageGo to next message
pa1sas3
Messages: 29
Registered: June 2005
Junior Member
Thanks all..but there are millions of rows as output for each SQL and there are 50 such SQLs for which I need to generate output.
>>> sqoop using oozie and transformation in Hive" >> its a big data requirement.

So, spooling is the only way or is there a way to do it efficiently?
Re: oracle solution for sqoop [message #677422 is a reply to message #677421] Wed, 18 September 2019 08:00 Go to previous message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Any tool you'll use you have to 1) execute SQL to read the rows, 2) write them into a/several files, I don't think there is a tool more efficient than other ones for this, just configure it to be efficient (parameters like fetch array size).

Previous Topic: SQL*Loader - wildcard character
Next Topic: ORA 39000 | ORA 39142
Goto Forum:
  


Current Time: Sun Dec 08 17:45:37 CST 2019