Community Articles
Find and share helpful community-sourced technical articles.
Announcements
Alert: Please see the Cloudera blog for information on the Cloudera Response to CVE-2021-4428
Labels (1)
Expert Contributor

SQOOP CONNECTIONS:

Sqoop is a tool designed to transfer data between Hadoop and relational databases. You can use Sqoop to import data from a relational database management system (RDBMS) such as MySQL or Oracle into the Hadoop Distributed File System (HDFS), transform the data in Hadoop MapReduce, and then export the data back into an RDBMS.

Reference: sqoop user guide:

https://sqoop.apache.org/docs/1.4.0-incubating/SqoopUserGuide.html

JDBC ORACLE: Examples for Import:

sqoop-import --connect jdbc:oracle:thin:@db.test.com:PORT:INSTANCE_NAME --table DW_DATAMART.HCM_EMPLOYEE_D --fields-terminated-by '\t' --lines-terminated-by '\n' --username SSANKU -P

sqoop-import --connect jdbc:oracle:thin:@db.test.com:PORT:INSTANCE_NAME --table DW_DATAMART.HCM_EMPLOYEE_D --fields-terminated-by '\t' --lines-terminated-by '\n' --username SSANKU -P

JDBC ORACLE: Example for Select:

The eval tool allows users to quickly run simple SQL queries against a database; results are printed to the console. This allows users to preview their import queries to ensure they import the data they expect.

sqoop-eval --connect jdbc:oracle:thin:@db.test.com:PORT:INSTANCE_NAME --select * from DW_DATAMART.HCM_COMPANY_D

JDBC INFORMIX: example

JDBC Informix: Examples for Import:

sqoop-import --connect jdbc:informix-sqli://4jane.soi.com:15062/common:INFORMIXSERVER=ids_4jane --driver com.informix.jdbc.IfxDriver --table portal_request_params –username username -P

Sqoop Import to HBASE table:

Examples:

sqoop-import --connect jdbc:oracle:thin:@db.test.com:PORT:INSTANCE_NAME --username ssanku --P --table DW_DATAMART.PAY_PAY_CHK_OPTION_D --hbase-table DW_DATAMART.PAY_PAY_CHK_OPTION_D --column-family cf1 --hbase-create-table

If no primary key defined on the oracle table

sqoop-import --connect jdbc:oracle:thin:@db.test.com:1725:hrlites --username ssanku --P --table PSMERCHANTID --hbase-table PSMERCHANTID --column-family cf --hbase-row-key MERCHANTID --hbase-create-table --split-by MERCHANTID

sqoop-import --connect jdbc:oracle:thin:@db.test.com:PORT:INSTANCE_NAME --username ssanku --P --table DW_DATAMART.PAY_PAYGROUP_D --hbase-table DW_DATAMART.PAY_PAYGROUP_D --column-family cf1 --hbase-create-table

sqoop-import --connect jdbc:oracle:thin:@db.test.com:1725:hrlites --username ssanku --P --table PSMERCHANTID --hbase-table PSMERCHANTID --column-family cf --hbase-create-table --split-by MERCHANTID

Sqoop Import to HIVE table from Mysql Database:

Examples:

sqoop import --connect jdbc:mysql://172.16.16.128:3306/employees -- username=hive --password=hive --driver com.mysql.jdbc.Driver --table=employees -- hive-import --hive-table=empl.employees --target-dir=wp_users_import –direct

sqoop import --connect jdbc:mysql://172.16.16.128:3306/employees -- username=hive --password=hive --driver com.mysql.jdbc.Driver --table=employees -- hive-import --hive-table=empl.salaries --target-dir=wp_users_import --direct

11,823 Views
Don't have an account?
Version history
Last update:
‎06-21-2016 08:59 PM
Updated by:
Contributors
Top Kudoed Authors