使用Sqoop将数据从MySQL导入HBase (二)

芒果1年前技术文章621

创建hbase表
create_namespace 'data';
create 'data:data', {NAME => 'cf1'}, {NAME => 'cf2'}, {NAME => 'cf3'};
972DD8CA-50E1-4172-A60E-F3308A8B2903.png
使用sqoop导入(遇到了问题 查看问题2)
sqoop import \
--connect jdbc:mysql://172.16.121.194:3306/test?zeroDateTimeBehavior=CONVERT_TO_NULL \
--username root \
--password 123456 \
--table data2 \
--hbase-table data:data \
--column-family cf1 \
--hbase-row-key code \
-m 1
-----下面这个不用建立表
sqoop import --connect jdbc:mysql://172.16.121.194:3306/test?zeroDateTimeBehavior=CONVERT_TO_NULL --username root --password 123456 --table data2 --hbase-table data --column-family info --hbase-row-key code --hbase-create-table --columns "province,code,city,district,year,model,company,brand,type,use_type,seat_count,engine_no,displacement,power,fuel_type,total_weight,kerb_mass,max_mass,tire_size,tire_count,length,width,height,note,extra_company,original_model,production_company,vehicle_type,register_date,owner_sex" --hbase-create-table --split-by code
image.png
查看 hbase表
scan 'data:data'
image.png
问题1、sqoop从mysql导入hbase报错
2023-12-22 14:58:20,545 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps
Exception in thread "main" java.lang.NoSuchMethodError: org.apache.hadoop.hbase.client.HBaseAdmin.<init>(Lorg/apache/hadoop/conf/Configuration;)V
    at org.apache.sqoop.mapreduce.HBaseImportJob.jobSetup(HBaseImportJob.java:163)
    at org.apache.sqoop.mapreduce.ImportJobBase.runImport(ImportJobBase.java:268)
    at org.apache.sqoop.manager.SqlManager.importTable(SqlManager.java:692)
    at org.apache.sqoop.manager.MySQLManager.importTable(MySQLManager.java:127)
    at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:520)
    at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:628)
    at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
    at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
    at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
    at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
    at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)
    at org.apache.sqoop.Sqoop.main(Sqoop.java:252)
解决方法
查看错误,因为对应HBase版本太高导致。Index of /dist/hbase/1.6.0
根据连接下载hbase1.6版本,本地解压,将lib文件夹中所有jar包上传至$SQOOP_HOME/lib文件夹中
问题2、sqoop从mysql导入hbase报错
因为sqoop导入不能出现空值所以需要转换
使用IFNULL()函数将空值转换为指定的替代值(例如"NA”):
创建daata2的表
CREATE TABLE data2 (
    province VARCHAR(255),
    code INT,
    city VARCHAR(255),
    district VARCHAR(255),
    year INT,
    model VARCHAR(255),
    company VARCHAR(255),
    brand VARCHAR(255),
    type VARCHAR(255),
    use_type VARCHAR(255),
    seat_count INT,
    engine_no VARCHAR(255),
    displacement FLOAT,
    power FLOAT,
    fuel_type VARCHAR(255),
    total_weight FLOAT,
    kerb_mass FLOAT,
    max_mass FLOAT,
    tire_size VARCHAR(255),
    tire_count INT,
    length FLOAT,
    width FLOAT,
    height FLOAT,
    note VARCHAR(255),
    extra_company VARCHAR(255),
    original_model VARCHAR(255),
    production_company VARCHAR(255),
    vehicle_type VARCHAR(255),
    register_date INT,
    owner_sex VARCHAR(255)
);
将空值转换并插入到另一个MySQL表中:
INSERT INTO data2 (province, code, city, district, year, model, company, brand, type, use_type, seat_count, engine_no, displacement, power, fuel_type, total_weight, kerb_mass, max_mass, tire_size, tire_count, length, width, height, note, extra_company, original_model, production_company, vehicle_type, register_date, owner_sex)
SELECT
    IFNULL(province, 'NA') AS province,
    IFNULL(code, -1) AS code,
    IFNULL(city, 'NA') AS city,
    IFNULL(district, 'NA') AS district,
    IFNULL(year, -1) AS year,
    IFNULL(model, 'NA') AS model,
    IFNULL(company, 'NA') AS company,
    IFNULL(brand, 'NA') AS brand,
    IFNULL(type, 'NA') AS type,
    IFNULL(use_type, 'NA') AS use_type,
    IFNULL(seat_count, -1) AS seat_count,
    IFNULL(engine_no, 'NA') AS engine_no,
    IFNULL(displacement, -1.0) AS displacement,
    IFNULL(power, -1.0) AS power,
    IFNULL(fuel_type, 'NA') AS fuel_type,
    IFNULL(total_weight, -1.0) AS total_weight,
    IFNULL(kerb_mass, -1.0) AS kerb_mass,
    IFNULL(max_mass, -1.0) AS max_mass,
    IFNULL(tire_size, 'NA') AS tire_size,
    IFNULL(tire_count, -1) AS tire_count,
    IFNULL(length, -1.0) AS length,
    IFNULL(width, -1.0) AS width,
    IFNULL(height, -1.0) AS height,
    IFNULL(note, 'NA') AS note,
    IFNULL(extra_company, 'NA') AS extra_company,
    IFNULL(original_model, 'NA') AS original_model,
    IFNULL(production_company, 'NA') AS production_company,
    IFNULL(vehicle_type, 'NA') AS vehicle_type,
    IFNULL(register_date, -1) AS register_date,
    IFNULL(owner_sex, 'NA') AS owner_sex
FROM data;


相关文章

hbase无法执行脚本停止服务问题分析

hbase无法执行脚本停止服务问题分析

问题现象:hbase执行stop-hbase.sh无法停止hbase进程定位过程:1.     执行脚本发现脚本在执行了停止命令后一直在循环查询hbase状态2...

MySQL 在线开启 GTID

MySQL 在线开启 GTID

描述生产环境上也会遇到需要开启 GTID ,有什么风险?如何在线开启?本篇 SOP 将介绍。GTID 限制由于基于 GTID 复制依赖于事务,所有开启 GTID 时,有些 MySQL 特性不支持:事务...

Clickhouse表引擎介绍

Clickhouse表引擎介绍

引擎分类ClickHouse表引擎一共分为四个系列,分别是Log、MergeTree、Integration、Special。其中包含了两种特殊的表引擎Replicated、Distributed,功...

CDP实操--集群扩容

CDP实操--集群扩容

一、前提准备工作1.确保OS的yum源可以正常使用,通过yum repolist命令可以查看到匹配的OS的所有包2.确保Cloudera Manager的yum源运行正常3.hosts文件配置,需要将...

trino组件对接alluxio(三)

trino组件对接alluxio(三)

本文是基于已经部署了trino和alluxio的基础上,进行的trino与alluxio的组件对接,alluxio已经开启了高可用模式。安装部署1、增加alluxio配置在core-site.xml和...

MySQL运维实战之Clone插件(10.2)Clone插件原理

MySQL运维实战之Clone插件(10.2)Clone插件原理

clone插件实现clone操作主要分为几个阶段:1、初始阶段。初始阶段,会开启页面跟踪(Page Tracking)。开启页面跟踪后,修改过的页面的编号会被记录下来。页面的修改可分为两个阶段:首先在...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。