sqoop的import命令和export命令

最新推荐文章于 2024-05-23 10:29:42 发布

qq_42604959

最新推荐文章于 2024-05-23 10:29:42 发布

阅读量493

点赞数

CC 4.0 BY-SA版权

分类专栏：大数据文章标签： sqoop

本文链接：https://blog.youkuaiyun.com/qq_42604959/article/details/90049816

大数据专栏收录该内容

4 篇文章

订阅专栏

本文详细介绍如何使用SQOOP工具实现数据从MySQL到HDFS，再到Hive的导入过程，以及从Hive导出数据到MySQL的方法。涵盖SQOOP的import与export命令的使用，包括数据筛选、权限配置、脚本编写等实用技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

sqoop的import命令和export命令

一. import命令

数据从mysql导入到hdfs当中

前提:先在MySQL中创建到一个user表

create table user(id int primary key auto_increment,name varchar(20),addr varchar(200));

insert into user(id,name,addr) values(1,“long”,“beijing”);

执行命令

[root@linux01 sqoop]# bin/sqoop import --connect jdbc:mysql://linux02:3306/sq --username root --password 123456 --table user --target-dir /sqoop/datas --delete-target-dir --num-mappers 1 --fields-terminated-by "\t"

数据从MySQL总导入到hdfs当中进行筛选

bin/sqoop import --connect jdbc:mysql://linux02:3306/sq --username root --password 123456 --target-dir /sqoop/selectdemo --delete-target-dir --num-mappers 1 --fields-terminated-by "\t" --query 'select * from user where id<=1 and $CONDITIONS'

通过where筛选


[root@linux01 sqoop]# bin/sqoop import --connect jdbc:mysql://linux02:3306/sq --username root --password 123456 --target-dir /sqoop/selectdemo2 --delete-target-dir --num-mappers 1 --fields-terminated-by "\t" --table user --where "id<=1"

mysql导入数据到hive

前提需要先创建hive表


hive (default)> create table user_sqoop(id int,name string) row format delimited fields terminated by "\t";
OK
Time taken: 0.376 seconds
hive (default)> select * from user_sqoop;
OK
user_sqoop.id   user_sqoop.name
Time taken: 0.513 seconds

创建MySQL表user1


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sq                 |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use sq;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+--------------+
| Tables_in_sq |
+--------------+
| user         |
+--------------+
1 row in set (0.00 sec)

mysql> create table user1(id int primary key auto_increment,name varchar(20),age varchar(30));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into user1(id,name) values(1,"yangmai");
Query OK, 1 row affected (0.00 sec)

mysql> insert into user1(id,name) values(2,"long");
Query OK, 1 row affected (0.00 sec)

问题：hiveconf
解决：
vi ~/.bash_profile
export HADOOP_CLASSPATH=$HADOOP_CLASSPASS:/root/hd/hive/lib/*
mysql权限问题：
grant all privileges on *.* to root@'%' identified by "password";
flush privileges;

导入数据

 bin/sqoop import --connect jdbc:mysql://node02:3306/db_telecom --username root --password 123456 --table tb_call --num-mappers 1 --hive-import --fields-terminated-by "\t" --hive-overwrite --hive-table user_sqoop

查询hive中的表

hive (default)> select * from user_sqoop;
OK
user_sqoop.id   user_sqoop.name
1       yangmai
2       long
Time taken: 0.53 seconds, Fetched: 2 row(s)

二.export命令

从hive到处到mysql

前提:保证user1是空表

mysql> select * from user1;
Empty set (0.00 sec)

执行命令

[root@linux01 sqoop]# bin/sqoop export --connect jdbc:mysql://node02:3306/sqoopdb --username root --password 123456 --table user1 --num-mappers 1 --export-dir /user/hive/warehouse/itcast.db/	complex_array --input-fields-terminated-by "\t"

查询

mysql> select * from user1;
+----+---------+
| id | name    |
+----+---------+
|  1 | yangmai |
|  2 | long    |
+----+---------+
2 rows in set (0.00 sec)

三.sqoop打包脚本的使用

从hdfs导出到mysql

前提保证mysql中user1是空表

mysql> select * from user1;
Empty set (0.00 sec)

hdfs中的数据


[root@linux03 ~]# hadoop fs -cat /user/hive/warehouse/user_sqoop/part-m-00000
1       yangmai
2       long

编写脚本


[root@linux01 sqoopjob]# cat job_hdfs2mysql.opt
export
--connect
jdbc:mysql://linux02:3306/sq
--username
root
--password
123456
--table
user1
--num-mappers
1
--export-dir
/user/hive/warehouse/user_sqoop
--input-fields-terminated-by
"\t"

执行脚本


[root@linux01 sqoop]# bin/sqoop --options-file /root/sqoopjob/job_hdfs2mysql.opt

查看mysql中user1的数据


mysql> select * from user1;
+----+---------+
| id | name    |
+----+---------+
|  1 | yangmai |
|  2 | long    |
+----+---------+
2 rows in set (0.00 sec)

四sqoop常用命令

命令	说明
import	将数据导入到集群
export	将集群数据导出
codegen	将某数据库中表生成javaBean并打包为jar
eval	查看sql执行结果
create-hive-table	创建hive表
import-all-table	导入某个数据库中所有表到hdfs中
list-table	列出某个数据库下所有表
merge	将hdfs中不同目录下的数据合并在一起
version V	查看sqoop版本
help	查看帮助信息


  codegen            Generate code to interact with database records
  create-hive-table  Import a table definition into Hive
  eval               Evaluate a SQL statement and display the results
  export             Export an HDFS directory to a database table
  help               List available commands
  import             Import a table from a database to HDFS
  import-all-tables  Import tables from a database to HDFS
  import-mainframe   Import datasets from a mainframe server to HDFS
  job                Work with saved jobs
  list-databases     List available databases on a server
  list-tables        List available tables in a database
  merge              Merge results of incremental imports
  metastore          Run a standalone Sqoop metastore
  version            Display version information

五sqoop常用参数

参数	说明
–connect	连接关系型数据库URL
–connection-manager	指定连接管理类
–driver	JDBC的driver class
–username	连接数据库的用户名
–password	连接数据库的密码
–verbose	在控制台中打印详细信息
–help	查看帮助
–hive-import	将关系型数据库导入到hive表中
–hive-overwrite	覆盖掉hive表中已存在的数据
–create-hive-table	创建hive表
–hive-table	接入hive表
–table	指定关系型数据库的表名