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

芒果1年前技术文章593

创建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;


相关文章

MySQL 二进制安装

MySQL 二进制安装

一、前言● 介绍:业务环境安装 MySQL 一般都会通过二进制来安装,今天就记录一下业务环境二进制安装 MySQL 过程。● 环境:CentOS 7 安装:MySQL 5.7+二、安装 MySQL1....

Flume使用详解

Flume使用详解

一、Flume概念Flume 是 Cloudera 提供的日志收集系统,具有分布式、高可靠、高可用性等特点,对海量 日志采集、聚合和传输,Flume 支持在日志系统中定制各类数据发送方,同时,Flum...

Clickhouse MergeTree异常数据处理

说明clickhouse mergetree的数据文件如果遇到数据损坏,可能会导致clickhouse无法启动。本文章说明如何处理这类问题。测试我们先人为模拟破坏mergetree数据文件:detac...

数据湖技术之iceberg(七)Spark管理iceberg表

数据湖技术之iceberg(七)Spark管理iceberg表

1.SparkSQL设置catalog配置以下操作主要是SparkSQL操作Iceberg,同样Spark中支持两种Catalog的设置:hive和hadoop,Hive Catalog就是icebe...

使用CoreDNS搭建DNS服务器

使用CoreDNS搭建DNS服务器

简介CoreDNS是一个DNS服务器/转发器,用Go编写,可以链接插件。每个插件执行一个 (DNS) 功能。CoreDNS是云原生计算基金会毕业的项目。CoreDNS是一个快速灵活的DNS服务器。这里...

大数据集群部署规划(二)硬件配置要求

CPU(虚拟机建议配置*2)X86服务器:最低配置:双路4核Intel处理器。推荐配置:双路8核Intel处理器。ARM服务器:双路32核ARM处理器Bit-mode64位内存(虚拟机不建议超分)物理...

发表评论    

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