Sqoop将MySQL数据导入到HDFS和Hive中

雨点打透心脏的1/2处 2024-04-17 06:23 7阅读 0赞

# 一、将数据从mysql导入 HDFS #

**sqoop import --connect jdbc:mysql://192.168.76.1:3306/workflow --username root --password a --table project**

报错

tool.ImportTool: Encountered IOException running import job: java.io.IOException: No columns to generate for ClassWriter  
分析:  jdbc驱动问题，注意: mysql版本及jdbc驱动版本

请使用  mysql-connector-java-5.1.46.jar，   将它复制到 sqoop/lib下.

日志分析

19/07/31 21:27:17 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `project` AS t LIMIT 1
    19/07/31 21:27:17 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `project` AS t LIMIT 1
    19/07/31 21:27:17 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /usr/local/hadoop275
    Note: /tmp/sqoop-root/compile/904dde1997bf14585ed20790f49bf50f/project.java uses or overrides a deprecated API.
    Note: Recompile with -Xlint:deprecation for details.
    19/07/31 21:27:21 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-root/compile/904dde1997bf14585ed20790f49bf50f/project.jar
    19/07/31 21:27:21 WARN manager.MySQLManager: It looks like you are importing from mysql.
    19/07/31 21:27:21 WARN manager.MySQLManager: This transfer can be faster! Use the --direct
    19/07/31 21:27:21 WARN manager.MySQLManager: option to exercise a MySQL-specific fast path.
    19/07/31 21:27:21 INFO manager.MySQLManager: Setting zero DATETIME behavior to convertToNull (mysql)
    19/07/31 21:27:21 INFO mapreduce.ImportJobBase: Beginning import of project
    19/07/31 21:27:21 INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar
    19/07/31 21:27:22 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps
    19/07/31 21:27:22 INFO client.RMProxy: Connecting to ResourceManager at master/192.168.76.144:18040
    19/07/31 21:27:34 INFO db.DBInputFormat: Using read commited transaction isolation
    19/07/31 21:27:34 INFO db.DataDrivenDBInputFormat: BoundingValsQuery: SELECT MIN(`id`), MAX(`id`) FROM `project`
    19/07/31 21:27:34 INFO db.IntegerSplitter: Split size: 0; Num splits: 4 from: 1 to: 3
    19/07/31 21:27:35 INFO mapreduce.JobSubmitter: number of splits:3
    19/07/31 21:27:35 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1564545564450_0001
    19/07/31 21:27:36 INFO impl.YarnClientImpl: Submitted application application_1564545564450_0001
    19/07/31 21:27:36 INFO mapreduce.Job: The url to track the job: http://master:18088/proxy/application_1564545564450_0001/
    19/07/31 21:27:36 INFO mapreduce.Job: Running job: job_1564545564450_0001
    19/07/31 21:27:50 INFO mapreduce.Job: Job job_1564545564450_0001 running in uber mode : false
    19/07/31 21:27:50 INFO mapreduce.Job:  map 0% reduce 0%
    19/07/31 21:27:58 INFO mapreduce.Job:  map 33% reduce 0%
    19/07/31 21:28:07 INFO mapreduce.Job:  map 67% reduce 0%
    19/07/31 21:28:08 INFO mapreduce.Job:  map 100% reduce 0%
    19/07/31 21:28:08 INFO mapreduce.Job: Job job_1564545564450_0001 completed successfully
    19/07/31 21:28:08 INFO mapreduce.Job: Counters: 31
    	File System Counters
    		FILE: Number of bytes read=0
    		FILE: Number of bytes written=418683
    		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=180
    		HDFS: Number of read operations=12
    		HDFS: Number of large read operations=0
    		HDFS: Number of write operations=6
    	Job Counters 
    		Killed map tasks=1
    		Launched map tasks=3
    		Other local map tasks=3
    		Total time spent by all maps in occupied slots (ms)=36242
    		Total time spent by all reduces in occupied slots (ms)=0
    		Total time spent by all map tasks (ms)=36242
    		Total vcore-milliseconds taken by all map tasks=36242
    		Total megabyte-milliseconds taken by all map tasks=37111808
    	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)=554
    		CPU time spent (ms)=3130
    		Physical memory (bytes) snapshot=329420800
    		Virtual memory (bytes) snapshot=6247133184
    		Total committed heap usage (bytes)=91422720
    	File Input Format Counters 
    		Bytes Read=0
    	File Output Format Counters 
    		Bytes Written=180
    19/07/31 21:28:08 INFO mapreduce.ImportJobBase: Transferred 180 bytes in 46.5489 seconds (3.8669 bytes/sec)
    19/07/31 21:28:08 INFO mapreduce.ImportJobBase: Retrieved 3 records.

测试以上导入是否成功:  
      ** hadoop dfs -ls /user/root/project**

**       hadoop dfs -cat /user/root/project/part\***

指定hdfs保存路径.   /test1   没有用表名作路径  
       **sqoop import --connect jdbc:mysql://192.168.76.1:3306/workflow --username root --password a --table project --target-dir /test1**

只导入id , name列   /test2  
**       sqoop import --connect jdbc:mysql://192.168.76.1:3306/workflow --username root --password a --table project --target-dir /test2 --columns 'id,name'**

增量导入    添加了数据: append  
       **sqoop import --connect jdbc:mysql://192.168.76.1:3306/workflow --username root --password a --table project --target-dir /test3**

**       sqoop import --connect jdbc:mysql://192.168.76.1:3306/workflow --username root --password a  --driver com.mysql.jdbc.Driver --target-dir /test3 --direct  --table project -m 3 --check-column id --incremental append --last-value 3**

# 二、将MySQL数据库中的表数据导入到Hive中 #

### **Sqoop 导入关系型数据到 Hive 的过程是先导入到 hdfs，然后再 load 进入 Hive** ###

### sqoop 提供了直接将 mysql 数据导入 Hive 的功能，以下为直接导入 ###

**sqoop import --connect jdbc:mysql://****192.168.76.1****:3306/kuwomusic --username root --password a --table kuwo\_music  --target-dir /user/test --hive-import --create-hive-table --where 'id<100'**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMTg0MTQ2_size_16_color_FFFFFF_t_70][]

执行成功

查看Hive中数据

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMTg0MTQ2_size_16_color_FFFFFF_t_70 1][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMTg0MTQ2_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/16/e9c7bedcd8244830ac093640a9518ddc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMTg0MTQ2_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/16/59f266c0006c43cd84b158645e5ca8b0.png