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;
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://localhost:3306/test?useSSL=false --table Card --username root --password root --hive-table lining_test.mysql_Card;
5、查看hive lining_test库表
hive> use lining_test;
OK
Time taken: 0.28 seconds
hive> show tables;
OK
emp
emp_part
mysql_card
Time taken: 0.067 seconds, Fetched: 3 row(s)
hive>
5、查看mysql_card表结构
hive> desc mysql_card;
OK
card_id int
card_name string
card_center string
card_status string
card_student string
card_member string
card_contractdate string
card_contractcoin int
card_contractleavecoin double
card_contractstopcoin int
card_order string
card_discount double
card_expiredate string
card_restcoin double
card_restleavecoin double
card_reststopcoin int
card_comment string
Time taken: 0.876 seconds, Fetched: 17 row(s)
hive>
6、查看mysql_card表中的数据条数
hive> select count(card_id) cnt from mysql_card;
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks determined at compile time: 1
In order to change the average load for a reducer (in bytes):set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:set mapreduce.job.reduces=<number>
Starting Job = job_1594787927823_0002, Tracking URL = http://lining05:8088/proxy/application_1594787927823_0002/
Kill Command = /opt/modules/cdh/hadoop-2.5.0-cdh5.3.6/bin/hadoop job -kill job_1594787927823_0002
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2020-07-15 19:22:53,102 Stage-1 map = 0%, reduce = 0%
2020-07-15 19:23:18,956 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.65 sec
2020-07-15 19:23:27,633 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 2.85 sec
MapReduce Total cumulative CPU time: 2 seconds 850 msec
Ended Job = job_1594787927823_0002
MapReduce Jobs Launched:
Stage-Stage-1: Map: 1 Reduce: 1 Cumulative CPU: 2.85 sec HDFS Read: 261 HDFS Write: 2 SUCCESS
Total MapReduce CPU Time Spent: 2 seconds 850 msec
OK
0
Time taken: 49.849 seconds, Fetched: 1 row(s)
hive>
没有导入数据！
7、使用sqoop向mysql_card中导入数据
sqoop import --connect jdbc:mysql://localhost:3306/test?useSSL=false --username root --password root --table Card --hive-import --hive-table lining_test.mysql_card