sqoop测试oracle数据库的连接使用

浏览数：41 / 时间：2015年06月12日

测试oracle数据库的连接使用①连接oracle数据库，列出所有的数据库

[hadoop@eb179 sqoop]$sqoop list-databases--connect jdbc 10.1.69.173:1521:ORCLBI --username huangq -P
或者sqoop list-databases--connect jdbc racle:thin10.1.69.173:1521:ORCLBI --username huangq--password 123456

或者MySQL：sqoop list-databases --connectjdbc:mysql://172.19.17.119:3306/ --username hadoop --password hadoop

Warning: /home/hadoop/sqoop/../hcatalog does not exist!HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /home/hadoop/sqoop/../accumulo does not exist! Accumulo imports willfail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
Warning: $HADOOP_HOME is deprecated.
14/08/17 11:59:24 INFO sqoop.Sqoop: Running Sqoop version: 1.4.5
Enter password:
14/08/17 11:59:27 INFO oracle.OraOopManagerFactory: Data Connector for Oracleand Hadoop is disabled.
14/08/17 11:59:27 INFO manager.SqlManager: Using default fetchSize of 1000
14/08/17 11:59:51 INFO manager.OracleManager: Time zone has been set to GMT
MRDRP
MKFOW_QH

②Oracle数据库的表导入到HDFS

注意：

默认情况下会使用4个map任务，每个任务都会将其所导入的数据写到一个单独的文件中，4个文件位于同一目录，本例中 -m1表示只使用一个map任务文本文件不能保存为二进制字段，并且不能区分null值和字符串值"null" 执行下面的命令后会生成一个ENTERPRISE.java文件，可以通过ls ENTERPRISE.java查看，代码生成是sqoop导入过程的必要部分，sqoop在将源数据库中的数据写到HDFS前，首先会用生成的代码将其进行反序列化

[hadoop@eb179~]$ sqoop import --connect jdbc racle:thin10.1.69.173:1521:ORCLBI--username huangq --password 123456 --table ORD_UV -m 1 --target-dir/user/sqoop/test --direct-split-size 67108864
Warning: /home/hadoop/sqoop/../hcatalog does not exist! HCatalog jobs willfail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /home/hadoop/sqoop/../accumulo does not exist! Accumulo imports willfail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
Warning: $HADOOP_HOME is deprecated.
14/08/17 15:21:34 INFO sqoop.Sqoop: Running Sqoop version: 1.4.5
14/08/17 15:21:34 WARN tool.BaseSqoopTool: Setting your password on thecommand-line is insecure. Consider using -P instead.
14/08/17 15:21:34 INFO oracle.OraOopManagerFactory: Data Connector for Oracleand Hadoop is disabled.
14/08/17 15:21:34 INFO manager.SqlManager: Using default fetchSize of 1000
14/08/17 15:21:34 INFO tool.CodeGenTool: Beginning code generation
14/08/17 15:21:46 INFO manager.OracleManager: Time zone has been set to GMT
14/08/17 15:21:46 INFO manager.SqlManager: Executing SQL statement: SELECT t.*FROM ORD_UV t WHERE 1=0
14/08/17 15:21:46 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is/home/hadoop/hadoop
Note: /tmp/sqoop-hadoop/compile/328657d577512bd2c61e07d66aaa9bb7/ORD_UV.javauses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
14/08/17 15:21:47 INFO orm.CompilationManager: Writing jar file:/tmp/sqoop-hadoop/compile/328657d577512bd2c61e07d66aaa9bb7/ORD_UV.jar
14/08/17 15:21:47 INFO manager.OracleManager: Time zone has been set to GMT
14/08/17 15:21:47 INFO manager.OracleManager: Time zone has been set to GMT
14/08/17 15:21:47 INFO mapreduce.ImportJobBase: Beginning import of ORD_UV
14/08/17 15:21:47 INFO manager.OracleManager: Time zone has been set to GMT
14/08/17 15:21:49 INFO db.DBInputFormat: Using read commited transactionisolation
14/08/17 15:21:49 INFO mapred.JobClient: Running job: job_201408151734_0027
14/08/17 15:21:50 INFO mapred.JobClient:  map 0% reduce 0%
14/08/17 15:22:12 INFO mapred.JobClient:  map 100% reduce 0%
14/08/17 15:22:17 INFO mapred.JobClient: Job complete: job_201408151734_0027
14/08/17 15:22:17 INFO mapred.JobClient: Counters: 18
14/08/17 15:22:17 INFO mapred.JobClient: Job Counters
14/08/17 15:22:17 INFO mapred.JobClient:    SLOTS_MILLIS_MAPS=15862
14/08/17 15:22:17 INFO mapred.JobClient:    Total time spent by allreduces waiting after reserving slots (ms)=0
14/08/17 15:22:17 INFO mapred.JobClient:    Total time spent by allmaps waiting after reserving slots (ms)=0
14/08/17 15:22:17 INFO mapred.JobClient:    Launched map tasks=1
14/08/17 15:22:17 INFO mapred.JobClient:    SLOTS_MILLIS_REDUCES=0
14/08/17 15:22:17 INFO mapred.JobClient: File Output FormatCounters
14/08/17 15:22:17 INFO mapred.JobClient:    Bytes Written=1472
14/08/17 15:22:17 INFO mapred.JobClient: FileSystemCounters
14/08/17 15:22:17 INFO mapred.JobClient:    HDFS_BYTES_READ=87
14/08/17 15:22:17 INFO mapred.JobClient:    FILE_BYTES_WRITTEN=33755
14/08/17 15:22:17 INFO mapred.JobClient:    HDFS_BYTES_WRITTEN=1472
14/08/17 15:22:17 INFO mapred.JobClient: File Input FormatCounters
14/08/17 15:22:17 INFO mapred.JobClient:    Bytes Read=0
14/08/17 15:22:17 INFO mapred.JobClient: Map-Reduce Framework
14/08/17 15:22:17 INFO mapred.JobClient:    Map input records=81
14/08/17 15:22:17 INFO mapred.JobClient:    Physical memory (bytes)snapshot=192405504
14/08/17 15:22:17 INFO mapred.JobClient:    Spilled Records=0
14/08/17 15:22:17 INFO mapred.JobClient:    CPU time spent (ms)=1540
14/08/17 15:22:17 INFO mapred.JobClient:    Total committed heapusage (bytes)=503775232
14/08/17 15:22:17 INFO mapred.JobClient:    Virtual memory (bytes)snapshot=2699571200
14/08/17 15:22:17 INFO mapred.JobClient:    Map output records=81
14/08/17 15:22:17 INFO mapred.JobClient:    SPLIT_RAW_BYTES=87
14/08/17 15:22:17 INFO mapreduce.ImportJobBase: Transferred 1.4375 KB in29.3443 seconds (50.1631 bytes/sec)
14/08/17 15:22:17 INFO mapreduce.ImportJobBase: Retrieved 81 records.

③数据导出Oracle和HBase

使用export可将hdfs中数据导入到远程数据库中

export --connectjdbc racle:thin 192.168.**.**:**:**--username **--password=** -m1tableVEHICLE--export-dir /user/root/VEHICLE

向Hbase导入数据

sqoop import --connect jdbc racle:thin 192.168.**.**:**:**--username**--password=**--m1 --table VEHICLE --hbase-create-table --hbase-table VEHICLE--hbase-row-key ID--column-family VEHICLEINFO --split-by ID

更多精彩内容请关注：http://bbs.superwu.cn

关注超人学院微信二维码：技术分享