The problem:
A large scale manufacturing organization aggregates data from different sources, maintains it in a single Oracle table, and the number of records is in the order of a little over a billion. A monthly process has to fetch the data from Oracle to HDFS.
The constraint:
Ideally, only the difference for each month could be fetched. But, there is little to no control over the Oracle data source and there is no reliable way to identify the delta. Hence, all the data have to be fetched all the time.
To give a perspective, if the table is exported as a CSV from a SQL Client (say, SQL Developer), it takes more than 20 hours to download the table.
The tool:
Sqoop is the standard tool used to import data from the relational database to HDFS.
The solution:
$ sqoop import -D oracle.row.fetch.size=50000 --fetch-size 15000 --num-mappers 40 --table <schema>.<table_name> -connect <jdbc_connection_url> --username <user> -P --target-dir <hdfs_target_location> --direct --split-by <split_column>
The above command fetched the data in under 5 minutes.
The explanation:
using --direct.
When using --direct, sqoop uses OraOop, an Oracle-specific driver (Database-specific driver) instead of the standard JDBC driver, which makes it significantly faster. It brought the number down to about 4 hours from around 20 hours. This doesn't work when using --query and does work only with --table.--num-mappers
The default is 4, and this influences the degree of parallelization and the file size. Increasing the number increases the number of connections to the oracle database and also increases the number of part files written (in turn reducing the size of each part file). Setting it to 40 brought the time down to around 300 seconds.fetch size (oracle.row.fetch.size and --fetch-size)
The default is 5000. This number dictates how many records need to be processed at a time. Increasing it 10 fold does give a minor performance boost.
The caveat:
The numbers do change based on the kind of workload. So, if you are using these, you need to experiment with numbers that work for your workload.
The intuition:
It may sound technical, but it's actually a commonsense approach.
An analogy could be, say, moving logs from one city to another city.
- Keep all logs in one place before moving. (Support for only the table and not the query).
- Choose the person who knows about logs in the source city, for that person knows what's the best way to carry. (Using direct allows to use database-specific driver).
- Add more such persons to allow transfer in parallel. (Specifying the number of mappers).
- Increase the capacity of each person to carry more. (Specifying the fetch size).
The conclusion:
Bringing the time down from 20 hours to 4 hours to 5 minutes is a significant improvement to our overall process. If you know of any other sqoop optimization in this context, please do share in the comments.
`