Community Articles

Find and share helpful community-sourced technical articles.
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

12,901 Views
Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.
Version history
Last update:
‎06-21-2016 08:59 PM
Updated by:
Contributors
Top Kudoed Authors