Sqoop Import HDFS

Sqoop import应用场景——密码访问

 

　注：测试用表为本地数据库中的表

 

1.明码访问

sqoop list-databases \
 --connect jdbc:mysql://202.193.60.117/dataweb \
 --username root \
 --password 20134997

 2.交互式密码

sqoop list-databases \

--connect jdbc:mysql://202.193.60.117/dataweb \
 --username root \
 --P

 

3.文件授权密码

sqoop list-databases \
 --connect jdbc:mysql://202.193.60.117/dataweb \
 --username root \
 --password-file /usr/hadoop/.password

 　　在运行之前先要在指定路径下创建.password文件。

[hadoop@centpy ~]$ cd /usr/hadoop/
[hadoop@centpy hadoop]$ ls
flume  hadoop-2.6.0  sqoop
[hadoop@centpy hadoop]$ echo -n "20134997" > .password
[hadoop@centpy hadoop]$ ls -a
.  ..  flume  hadoop-2.6.0  .password  sqoop
[hadoop@centpy hadoop]$ more .password 
20134997
[hadoop@centpy hadoop]$ chmod 400 .password //根据官方文档说明赋予400权限

 　　测试运行之后一定会报以下错误：

18/06/21 16:12:48 WARN tool.BaseSqoopTool: Failed to load password file
java.io.IOException: The provided password file /usr/hadoop/.password does not exist!
    at org.apache.sqoop.util.password.FilePasswordLoader.verifyPath(FilePasswordLoader.java:51)
    at org.apache.sqoop.util.password.FilePasswordLoader.loadPassword(FilePasswordLoader.java:85)
    at org.apache.sqoop.util.CredentialsUtil.fetchPasswordFromLoader(CredentialsUtil.java:81)
    at org.apache.sqoop.util.CredentialsUtil.fetchPassword(CredentialsUtil.java:66)
    at org.apache.sqoop.tool.BaseSqoopTool.applyCredentialsOptions(BaseSqoopTool.java:1040)
    at org.apache.sqoop.tool.BaseSqoopTool.applyCommonOptions(BaseSqoopTool.java:995)
    at org.apache.sqoop.tool.ListDatabasesTool.applyOptions(ListDatabasesTool.java:76)
    at org.apache.sqoop.tool.SqoopTool.parseArguments(SqoopTool.java:435)
    at org.apache.sqoop.Sqoop.run(Sqoop.java:131)
    at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
    at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:179)
    at org.apache.sqoop.Sqoop.runTool(Sqoop.java:218)
    at org.apache.sqoop.Sqoop.runTool(Sqoop.java:227)
    at org.apache.sqoop.Sqoop.main(Sqoop.java:236)
Error while loading password file: The provided password file /usr/hadoop/.password does not exist!

　　为了解决该错误，我们需要将.password文件放到HDFS上面去，这样就能找到该文件了。

[hadoop@centpy hadoop]$ hdfs dfs -ls /
Found 12 items
drwxr-xr-x   - Zimo   supergroup          0 2018-05-12 10:57 /actor
drwxr-xr-x   - Zimo   supergroup          0 2018-05-08 16:51 /counter
drwxr-xr-x   - hadoop supergroup          0 2018-06-19 15:55 /flume
drwxr-xr-x   - hadoop hadoop              0 2018-04-14 14:20 /hdfsOutput
drwxr-xr-x   - Zimo   supergroup          0 2018-05-12 15:01 /join
drwxr-xr-x   - hadoop supergroup          0 2018-04-25 10:43 /maven
drwxr-xr-x   - Zimo   supergroup          0 2018-05-09 09:32 /mergeSmallFiles
drwxrwxrwx   - hadoop supergroup          0 2018-04-13 22:10 /phone
drwxr-xr-x   - hadoop hadoop              0 2018-04-14 14:43 /test
drwx------   - hadoop hadoop              0 2018-04-13 22:10 /tmp
drwxr-xr-x   - hadoop hadoop              0 2018-04-14 14:34 /weather
drwxr-xr-x   - hadoop hadoop              0 2018-05-07 10:44 /weibo
[hadoop@centpy hadoop]$ hdfs dfs -mkdir -p /user/hadoop
[hadoop@centpy hadoop]$ hdfs dfs -put .password /user/hadoop
[hadoop@centpy hadoop]$ hdfs dfs -chmod 400 /user/hadoop/.password

　　现在测试运行一下，注意路径改为HDFS上的/user/hadoop。

[hadoop@centpy hadoop-2.6.0]$ sqoop list-databases  --connect jdbc:mysql://202.193.60.117/dataweb  --username root  --password-file /user/hadoop/.password
Warning: /usr/hadoop/sqoop/../hbase does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
Warning: /usr/hadoop/sqoop/../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /usr/hadoop/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
Warning: /usr/hadoop/sqoop/../zookeeper does not exist! Accumulo imports will fail.
Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation.
18/06/21 16:22:12 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6
18/06/21 16:22:14 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
information_schema
dataweb
mysql
performance_schema
test

　　可以看到成功了。

 

 Sqoop import应用场景——导入全表

1.不指定目录

sqoop import \
 --connect jdbc:mysql://202.193.60.117/dataweb \
 --username root \
 --password-file /user/hadoop/.password \
 --table user_info

 　运行过程如下

18/06/21 16:36:20 INFO client.RMProxy: Connecting to ResourceManager at /0.0.0.0:8032
18/06/21 16:36:24 INFO db.DBInputFormat: Using read commited transaction isolation
18/06/21 16:36:24 INFO db.DataDrivenDBInputFormat: BoundingValsQuery: SELECT MIN(`id`), MAX(`id`) FROM `user_info`
18/06/21 16:36:25 INFO mapreduce.JobSubmitter: number of splits:3
18/06/21 16:36:25 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1529567189245_0001
18/06/21 16:36:26 INFO impl.YarnClientImpl: Submitted application application_1529567189245_0001
18/06/21 16:36:27 INFO mapreduce.Job: The url to track the job: http://centpy:8088/proxy/application_1529567189245_0001/
18/06/21 16:36:27 INFO mapreduce.Job: Running job: job_1529567189245_0001
18/06/21 16:36:45 INFO mapreduce.Job: Job job_1529567189245_0001 running in uber mode : false
18/06/21 16:36:45 INFO mapreduce.Job:  map 0% reduce 0%
18/06/21 16:37:11 INFO mapreduce.Job:  map 33% reduce 0%
18/06/21 16:37:12 INFO mapreduce.Job:  map 67% reduce 0%
18/06/21 16:37:13 INFO mapreduce.Job:  map 100% reduce 0%
18/06/21 16:37:14 INFO mapreduce.Job: Job job_1529567189245_0001 completed successfully
18/06/21 16:37:14 INFO mapreduce.Job: Counters: 30
    File System Counters
        FILE: Number of bytes read=0
        FILE: Number of bytes written=371994
        FILE: Number of read operations=0
        FILE: Number of large read operations=0
        FILE: Number of write operations=0
        HDFS: Number of bytes read=295
        HDFS: Number of bytes written=44
        HDFS: Number of read operations=12
        HDFS: Number of large read operations=0
        HDFS: Number of write operations=6
    Job Counters 
        Launched map tasks=3
        Other local map tasks=3
        Total time spent by all maps in occupied slots (ms)=70339
        Total time spent by all reduces in occupied slots (ms)=0
        Total time spent by all map tasks (ms)=70339
        Total vcore-seconds taken by all map tasks=70339
        Total megabyte-seconds taken by all map tasks=72027136
    Map-Reduce Framework
        Map input records=3
        Map output records=3
        Input split bytes=295
        Spilled Records=0
        Failed Shuffles=0
        Merged Map outputs=0
        GC time elapsed (ms)=2162
        CPU time spent (ms)=3930
        Physical memory (bytes) snapshot=303173632
        Virtual memory (bytes) snapshot=6191120384
        Total committed heap usage (bytes)=85327872
    File Input Format Counters 
        Bytes Read=0
    File Output Format Counters 
        Bytes Written=44
18/06/21 16:37:14 INFO mapreduce.ImportJobBase: Transferred 44 bytes in 54.3141 seconds (0.8101 bytes/sec)
18/06/21 16:37:14 INFO mapreduce.ImportJobBase: Retrieved 3 records.

 　　再查看一下HDFS下的运行结果

[hadoop@centpy hadoop-2.6.0]$ hdfs dfs -cat /user/hadoop/user_info/part-m-*
1,admin,123,1
2,hello,456,0
3,hahaha,haha,0

　　运行结果和数据库内容匹配。

 

以上就是博主为大家介绍的这一板块的主要内容，这都是博主自己的学习过程，希望能给大家带来一定的指导作用，有用的还望大家点个支持，如果对你没用也望包涵，有错误烦请指出。如有期待可关注博主以第一时间获取更新哦，谢谢！