Sqoop 实验

最新推荐文章于 2024-03-02 11:02:01 发布

vanni01

最新推荐文章于 2024-03-02 11:02:01 发布

阅读量392

点赞数

分类专栏： Hbase Sqoop hive

本文链接：https://blog.csdn.net/vanni01/article/details/107368100

版权

hive 同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

Hbase Sqoop

3 篇文章 0 订阅

订阅专栏

1、使用windows客户端登录mysql建表
修改虚拟机mysql的远程登录权限

mysql -uroot -proot;
use mysql;
show tables;
update user set host='%' where host = 'localhost';
flush pricilges;

给本地ip 192.168.198.1及lining05授权

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.198.1' IDENTIFIED BY 'root' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'lining05' IDENTIFIED BY 'root' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'root' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.198.131' IDENTIFIED BY 'root' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.198.132' IDENTIFIED BY 'root' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.198.133' IDENTIFIED BY 'root' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'lining06' IDENTIFIED BY 'root' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'lining07' IDENTIFIED BY 'root' WITH GRANT OPTION;

修改mysql的链接有效时间

show global variables like 'wait_timeout';
set global wait_timeout=1814400;

2、使用sqlyog连接，批量导入业务系统数据表，作为实验基础
在这里插入图片描述
3、使用sqoop查看mysql test数据库中的表

sqoop list-tables --connect jdbc:mysql://localhost:3306/test?useSSL=false --username root --password root

Admin
Bonus
Card
Center
CenterQA
ClassSeat
Consume
Course
CourseClass
CourseSeries
Dev
Dimension
Finance
Lesson
LessonArrange
Manager
MarketCollect
MarketInvite
Member
Order
Product
ProductSub
Project
ProjectSub
Promote
Remark
Resource
SaleConsult
SaleLost
Saler
Student
Teacher
TeacherTrial
TrainingClass

4、将MySQL的test.Card表结构复制到Hive的lining_test库中，表名为mysql_Card

sqoop create-hive-table --connect jdbc:mysql://192.168.198.131:3306/test?useSSL=false --table Member --username root --password root --hive-table lining_test.db_member --fields-terminated-by "\t"   --lines-terminated-by '\n'

5、查看hive lining_test库表

hive> use lining_test;
OK
Time taken: 0.28 seconds
hive> show tables;
OK
emp
emp_part
db_member
Time taken: 0.067 seconds, Fetched: 3 row(s)
hive>

5、查看db_member表结构

hive> desc db_member;
FAILED: SemanticException [Error 10001]: Table not found db_member
hive> use lining_test;
OK
Time taken: 0.037 seconds
hive> desc db_member;
OK
member_id               int
member_name             string
member_tel              string
member_birthday         string
member_sex              string
member_password         string
member_wxopenid         string
member_address          string
member_certificate      string
member_status           string
member_isadmin          string
member_ismanager        string
member_isdev            string
member_isteacher        string
member_isstudent        string
member_issaler          string
Time taken: 0.262 seconds, Fetched: 16 row(s)
hive>

6、查看db_member表中的数据条数

hive> select count(member_id) cnt from db_member;
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2020-07-16 13:27:33,821 Stage-1 map = 0%,  reduce = 0%
2020-07-16 13:28:04,266 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 19.38 sec
2020-07-16 13:28:15,747 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 21.12 sec
MapReduce Total cumulative CPU time: 21 seconds 120 msec
Ended Job = job_1594787927823_0011
MapReduce Jobs Launched:
Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 21.12 sec   HDFS Read: 261 HDFS Write: 2 SUCCESS
Total MapReduce CPU Time Spent: 21 seconds 120 msec
OK
0
Time taken: 55.22 seconds, Fetched: 1 row(s)
hive>

没有导入数据！
7、使用sqoop向mysql_member中导入数据

sqoop import --connect jdbc:mysql://192.168.198.131:3306/test?useSSL=false --username root --password root --table Member --hive-import --hive-table lining_test.mysql_member --fields-terminated-by '\t'

此处必须注意mysql-connector版本问题，并且填写IP 而非localhost
另外，如果在前面建表时已指定fields-terminated-by参数，此处可省略

20/07/16 16:42:41 INFO mapreduce.Job:  map 0% reduce 0%
20/07/16 16:42:51 INFO mapreduce.Job:  map 25% reduce 0%
20/07/16 16:42:54 INFO mapreduce.Job:  map 50% reduce 0%
20/07/16 16:42:58 INFO mapreduce.Job:  map 100% reduce 0%
20/07/16 16:42:58 INFO mapreduce.Job: Job job_1594880497460_0003 completed successfully

8、在hive中select count(card_id) cnid from mysql_member;

select count(card_id) cnid from mysql_member;

Hadoop job information for Stage-1: number of mappers: 2; number of reducers: 1
2020-07-15 23:19:46,706 Stage-1 map = 0%,  reduce = 0%
2020-07-15 23:20:07,268 Stage-1 map = 50%,  reduce = 0%, Cumulative CPU 5.98 sec
2020-07-15 23:20:27,891 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 12.34 sec
2020-07-15 23:20:37,416 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 13.45 sec
MapReduce Total cumulative CPU time: 13 seconds 450 msec
Ended Job = job_1594787927823_0008
MapReduce Jobs Launched:
Stage-Stage-1: Map: 2  Reduce: 1   Cumulative CPU: 13.45 sec   HDFS Read: 543095 HDFS Write: 5 SUCCESS
Total MapReduce CPU Time Spent: 13 seconds 450 msec
OK
1084
Time taken: 64.332 seconds, Fetched: 1 row(s)
hive>

9、查看hdfs文件/opt/modules/cdh/hive-0.13.1-cdh5.3.6/warehouse/lining_test.db/db_member
在这里插入图片描述

数据导入成功！
10、备份并删除Member表中数据，将hive中的表db_member 导出到mysql中

delete from test.Member;

sqoop export --username root --password root --connect 'jdbc:mysql://192.168.198.131:3306/test?useUnicode=true&characterEncoding=utf-8' --table Member --export-dir /opt/modules/cdh/hive-0.13.1-cdh5.3.6/warehouse/lining_test.db/db_member --fields-terminated-by '\t' --lines-terminated-by '\n'

此处注意指明useUnicode=true&characterEncoding=utf-8 否则中文乱码
在这里插入图片描述
数据导入成功！

11、使用query参数将Card表中card_id 小于 2000000 的记录据导入HDFS

sqoop import --connect 'jdbc:mysql://192.168.198.131:3306/test?useSSL=false' --username root --password root --m 1 --split-by card_id --query 'select card_id,card_member,card_contractcoin from test.Card where card_id < 2000000 and $CONDITIONS' --target-dir '/user/root/Card' --delete-target-dir --fields-terminated-by '\t'

此处：没有主键的表要指明split-by参数, 使用query 参数是，查询条件中必须包含where 且条件最后必须加 and $CONDITIONS
在这里插入图片描述

数据导入hdfs成功！

12、使用Sqoop将 test.CourseClass 表中数据导入 HBase 表中。使用member_id split-by

sqoop import --connect 'jdbc:mysql://192.168.198.131:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf-8' --username root --password root --table CourseClass --split-by courseclass_id --hbase-table hbase_courseclass --hbase-row-key courseclass_id --hbase-create-table --column-family courseclass --delete-target-dir --fields-terminated-by '\t'

20/07/17 08:28:23 INFO mapreduce.Job: Running job: job_1594880497460_0012
20/07/17 08:30:55 INFO mapreduce.Job: Job job_1594880497460_0012 running in uber mode : false
20/07/17 08:30:55 INFO mapreduce.Job:  map 0% reduce 0%
20/07/17 08:33:35 INFO mapreduce.Job:  map 75% reduce 0%
20/07/17 08:34:20 INFO mapreduce.Job:  map 100% reduce 0%
20/07/17 08:34:25 INFO mapreduce.Job: Job job_1594880497460_0012 completed successfully

13、查看数据

hbase shell
hbase(main):002:0>list
=> ["hbase_courseclass"]

hbase(main):002:0>describe "hbase_courseclass"
DESCRIPTION                                          ENABLED
 'hbase_courseclass', {NAME => 'courseclass', DATA_B true
 LOCK_ENCODING => 'NONE', BLOOMFILTER => 'ROW', REPL
 ICATION_SCOPE => '0', VERSIONS => '1', COMPRESSION
 => 'NONE', MIN_VERSIONS => '0', TTL => 'FOREVER', K
 EEP_DELETED_CELLS => 'false', BLOCKSIZE => '65536',
  IN_MEMORY => 'false', BLOCKCACHE => 'true'}

hbase(main):003:0>scan "hbase_courseclass"

14、转 HBASE JAVA实验项目

vanni01

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Sqoop 实验

1、使用windows客户端登录mysql建表修改虚拟机mysql的远程登录权限mysql -uroot -proot;use mysql;show tables;update user set host='%' where host = 'localhost';flush pricilges;给本地ip 192.168.198.1及lining05授权GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.198.1' IDENTIFIED BY
复制链接

扫一扫