sqoop从mysql导入hbase

2020-12-14 07:54:54 编辑 0 阅读量:580

## 准备
前提：安装好 sqoop、hbase。

下载jbdc驱动：mysql-connector-java-5.1.10.jar

将 mysql-connector-java-5.1.10.jar 复制到 /usr/lib/sqoop/lib/ 下

## 导入表
MySQL导入HBase命令：
```
sqoop import --connect jdbc:mysql://10.10.97.116:3306/rsearch --table researchers --hbase-table A --column-family person --hbase-row-key id --hbase-create-table --username 'root' -P
```

说明：
- connect jdbc:mysql://10.10.97.116:3306/rsearch 表示远程或者本地 Mysql 服务的URI，3306是Mysql默认监听端口，rsearch是数据库，若是其他数据库，如Oracle,只需修改URI即可。
- table researchers  表示导出rsearch数据库的researchers表。
- hbase-table A  表示在HBase中建立表A。
- column-family person 表示在表A中建立列族person。
- hbase-row-key id  表示表A的row-key是researchers表的id字段。
- hbase-create-table 表示在HBase中建立表。
- username 'root' 表示使用用户root连接Mysql。

注意：

HBase的所有节点必须能够访问MySQL数据库，不然会出现如下错误：
java.sql.SQLException: null,  message from server: "Host '10.10.104.3' is not allowed to connect to this MySQL server"

## 根据查询条件导入

另外，Sqoop还支持将任意的查询结果集导入，不使用--table、--columns和--where，使用SQL语句--query参数执行自由查询导入，但是必须指定--target-dir目录，必须指定--split-by 分隔列，同时必须使用where且在其后加个$CONDITIONS，使Sqoop进程替代为一个唯一的条件表达式达到条件查询效果，如下：
```
    [hadoopUser@secondmgt conf]$ sqoop import --connect jdbc:mysql://secondmgt:3306/spice --username hive --password hive --query  'select * from users where  id<60 and $CONDITIONS' --split-by id -m 1 --target-dir /output/query/  
    Warning: /usr/lib/hcatalog does not exist! HCatalog jobs will fail.  
    Please set $HCAT_HOME to the root of your HCatalog installation.  
    15/01/18 14:30:10 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.  
    15/01/18 14:30:10 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.  
    15/01/18 14:30:10 INFO tool.CodeGenTool: Beginning code generation  
    15/01/18 14:30:11 INFO manager.SqlManager: Executing SQL statement: select * from users where  id<60 and  (1 = 0)  
    15/01/18 14:30:11 INFO manager.SqlManager: Executing SQL statement: select * from users where  id<60 and  (1 = 0)  
    15/01/18 14:30:11 INFO manager.SqlManager: Executing SQL statement: select * from users where  id<60 and  (1 = 0)  
    15/01/18 14:30:11 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /home/hadoopUser/cloud/hadoop/programs/hadoop-2.2.0  
    Note: /tmp/sqoop-hadoopUser/compile/3488270c7f7b23dd3b556d8d185f6a82/QueryResult.java uses or overrides a deprecated API.  
    Note: Recompile with -Xlint:deprecation for details.  
    15/01/18 14:30:12 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-hadoopUser/compile/3488270c7f7b23dd3b556d8d185f6a82/QueryResult.jar  
    15/01/18 14:30:12 INFO mapreduce.ImportJobBase: Beginning query import.  
    15/01/18 14:30:12 INFO Configuration.deprecation: mapred.job.tracker is deprecated. Instead, use mapreduce.jobtracker.address  
    SLF4J: Class path contains multiple SLF4J bindings.  
    SLF4J: Found binding in [jar:file:/home/hadoopUser/cloud/hadoop/programs/hadoop-2.2.0/share/hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]  
    SLF4J: Found binding in [jar:file:/home/hadoopUser/cloud/hbase/hbase-0.96.2-hadoop2/lib/slf4j-log4j12-1.6.4.jar!/org/slf4j/impl/StaticLoggerBinder.class]  
    SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.  
    SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]  
    15/01/18 14:30:12 INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar  
    15/01/18 14:30:13 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps  
    15/01/18 14:30:13 INFO client.RMProxy: Connecting to ResourceManager at secondmgt/192.168.2.133:8032  
    15/01/18 14:30:14 INFO mapreduce.JobSubmitter: number of splits:1  
    15/01/18 14:30:14 INFO Configuration.deprecation: mapred.job.classpath.files is deprecated. Instead, use mapreduce.job.classpath.files  
    15/01/18 14:30:14 INFO Configuration.deprecation: user.name is deprecated. Instead, use mapreduce.job.user.name  
    15/01/18 14:30:14 INFO Configuration.deprecation: mapred.cache.files.filesizes is deprecated. Instead, use mapreduce.job.cache.files.filesizes  
    15/01/18 14:30:14 INFO Configuration.deprecation: mapred.cache.files is deprecated. Instead, use mapreduce.job.cache.files  
    15/01/18 14:30:14 INFO Configuration.deprecation: mapred.reduce.tasks is deprecated. Instead, use mapreduce.job.reduces  
    15/01/18 14:30:14 INFO Configuration.deprecation: mapred.output.value.class is deprecated. Instead, use mapreduce.job.output.value.class  
    15/01/18 14:30:14 INFO Configuration.deprecation: mapreduce.map.class is deprecated. Instead, use mapreduce.job.map.class  
    15/01/18 14:30:14 INFO Configuration.deprecation: mapred.job.name is deprecated. Instead, use mapreduce.job.name  
    15/01/18 14:30:14 INFO Configuration.deprecation: mapreduce.inputformat.class is deprecated. Instead, use mapreduce.job.inputformat.class  
    15/01/18 14:30:14 INFO Configuration.deprecation: mapred.output.dir is deprecated. Instead, use mapreduce.output.fileoutputformat.outputdir  
    15/01/18 14:30:14 INFO Configuration.deprecation: mapreduce.outputformat.class is deprecated. Instead, use mapreduce.job.outputformat.class  
    15/01/18 14:30:14 INFO Configuration.deprecation: mapred.cache.files.timestamps is deprecated. Instead, use mapreduce.job.cache.files.timestamps  
    15/01/18 14:30:14 INFO Configuration.deprecation: mapred.output.key.class is deprecated. Instead, use mapreduce.job.output.key.class  
    15/01/18 14:30:14 INFO Configuration.deprecation: mapred.working.dir is deprecated. Instead, use mapreduce.job.working.dir  
    15/01/18 14:30:14 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1421373857783_0016  
    15/01/18 14:30:15 INFO impl.YarnClientImpl: Submitted application application_1421373857783_0016 to ResourceManager at secondmgt/192.168.2.133:8032  
    15/01/18 14:30:15 INFO mapreduce.Job: The url to track the job: http://secondmgt:8088/proxy/application_1421373857783_0016/  
    15/01/18 14:30:15 INFO mapreduce.Job: Running job: job_1421373857783_0016  
    15/01/18 14:30:27 INFO mapreduce.Job: Job job_1421373857783_0016 running in uber mode : false  
    15/01/18 14:30:27 INFO mapreduce.Job:  map 0% reduce 0%  
    15/01/18 14:30:38 INFO mapreduce.Job:  map 100% reduce 0%  
    15/01/18 14:30:38 INFO mapreduce.Job: Job job_1421373857783_0016 completed successfully  
    15/01/18 14:30:38 INFO mapreduce.Job: Counters: 27  
            File System Counters  
                    FILE: Number of bytes read=0  
                    FILE: Number of bytes written=91814  
                    FILE: Number of read operations=0  
                    FILE: Number of large read operations=0  
                    FILE: Number of write operations=0  
                    HDFS: Number of bytes read=87  
                    HDFS: Number of bytes written=123  
                    HDFS: Number of read operations=4  
                    HDFS: Number of large read operations=0  
                    HDFS: Number of write operations=2  
            Job Counters  
                    Launched map tasks=1  
                    Other local map tasks=1  
                    Total time spent by all maps in occupied slots (ms)=33944  
                    Total time spent by all reduces in occupied slots (ms)=0  
            Map-Reduce Framework  
                    Map input records=3  
                    Map output records=3  
                    Input split bytes=87  
                    Spilled Records=0  
                    Failed Shuffles=0  
                    Merged Map outputs=0  
                    GC time elapsed (ms)=44  
                    CPU time spent (ms)=2440  
                    Physical memory (bytes) snapshot=164503552  
                    Virtual memory (bytes) snapshot=888926208  
                    Total committed heap usage (bytes)=83886080  
            File Input Format Counters  
                    Bytes Read=0  
            File Output Format Counters  
                    Bytes Written=123  
    15/01/18 14:30:38 INFO mapreduce.ImportJobBase: Transferred 123 bytes in 25.6853 seconds (4.7887 bytes/sec)  
    15/01/18 14:30:38 INFO mapreduce.ImportJobBase: Retrieved 3 records.  
```

- Sqoop使用--split-by 列名，根据此分隔工作量，默认的Sqoop将表中的关键字作为分隔列，由上导入命令可知，此处我们是以“id”作为分隔列。
- Sqoop从大部分的数据源并行的导入数据，我们可以使用-m参数控制Map tasks的数目，默认是4个，此处我们改成了1个Map task。Map task,根据整个范围的均衡大小进行操作。例如，你有一张表，关键字id范围是0-1000，默认Map tasks 是4个，Sqoop将会执行4个进程，每个进程以如下格式执行SELECT * FROM sometable WHERE id >= lo AND id < hi其中(lo, hi) set to (0, 250), (250, 500), (500, 750), and (750, 1001) 在不同的任务中。
- 注意一： 如果你的表中关键字不是根据其范围均匀的分布，就可能导致不平衡的任务。这个时候你需要明确的选择一个不同的列使用--split-by指定分隔参数。目前，Sqoop，还不支持对各个列索引进行分隔，如果一个表没有索引列或者含有多个关键字列，你必须手动的指定一个分隔列。
- 注意二：如果SQL语句中使用双引号（“”），则必须使用\$CONDITIONS代替$CONDITIONS，使你的shell不将其识别为shell自身的变量。
- 注意三：目前版本的Sqoop中，使用自由形式查询导入，只提供简单的查询，没有复杂的和“OR”条件查询在where子句中。

下面是另几个可用的示例
```
$ sqoop import --connect jdbc:mysql://cdh-slave2:3306/law --username root --password root. --query 'select id,lawlist_id,art_digit from law_rule_result2 where $CONDITIONS' -m 3 --target-dir /user/tb_doc --split-by id --hbase-table laws_doc:law_rule_result2 --hbase-row-key id  --column-family d --hbase-create-table --hbase-bulkload

$ sqoop import --connect jdbc:mysql://cdh-slave1:3306/laws_doc --username root --password root --query 'select * from court '  -m 3 --target-dir /user/tb_doc --split-by id --hbase-table laws_doc:court --hbase-row-key id  --column-family d --hbase-create-table --hbase-bulkload
```

---
参考：
- [1][Sqoop1.4.4使用SQL语句形式将MySQL数据库表中数据导入到HDFS中](http://blog.csdn.net/niityzu/article/details/42835273)
- [2][使用sqoop将MySQL数据库中的数据导入Hbase](http://blog.csdn.net/zklth/article/details/7193943)
- [3][Sqoop将mysql数据导入hbase的血与泪](https://www.cnblogs.com/sxt-zkys/archive/2017/07/26/7240041.html)
- [4][在mysql/oracle新建列将现有几列连接起来作为其值以用sqoop导入到hbase时作为rowkey](http://blog.csdn.net/liuxingjiaofu/article/details/6954751)

sqoop从mysql导入hbase

附件: