sqoop数据库表导入步骤01

一:数据库的表数据导入hive

查看sqoop的执行进度: http://tdh-node01:8088/cluster/apps

1.查看配置文件位置：(tab键为快捷提示键)

source tdh-client/TDH-Client/init.sh 打开命令(不打开无法后续操作)
cd ynyc-bg/transaction/options-file/ 查看配置文件位置

2.

ll
会出来一系列的配置信息（小写LL）

3.返回：

cd ~
执行cd ~会返回到

4.开始导入

sqoop job \
 --create tb_logistics_purchase_import_job \                                    命名规则：尽量表名_import_job
 -- import \
 --options-file /root/ynyc-bg/transaction/options-file/commdity-177.opt \       配置文件路径
 --table tb_logistics_purchase \      
 --target-dir /sqoop/hv_transaction/tb_logistics_purchase \                     导入数据表存放路径跟外表相对应
 --null-string '\\N' \
 --null-non-string '\\N' \
 --fields-terminated-by "|" \
 --incremental lastmodified \
 --check-column create_time \
 --merge-key id \
 --last-value "1999-01-21 00:00:00" \                                           
 -m1

例子二:(与上边相比下边的–更靠近左边,所以下边的错误率更低)
重点:这三步不能少,一代那个要查看root后的文件

cd ynyc-bg/transaction/options-file/
ll
cd~

sqoop job \
--create tb_ccb_order_bill_import_job \
-- import \
--options-file /root/ynyc-bg/transaction/options-file/transaction-177.opt \
--table tb_ccb_order_bill \
--target-dir /sqoop/hv_transaction/tb_ccb_order_bill \
--null-string '\\N' \
--null-non-string '\\N' \
--fields-terminated-by "|" \
--incremental lastmodified \
--check-column update_time \
--merge-key id \
--last-value "1999-01-21 00:00:00" \
-m1

执行上边的语句直接会返回如下图,表示执行成功了

5.执行job：

sqoop job --exec tb_logistics_purchase_import_job
sqoop job --exec job名称

出现如下图的内容表示成功

6.获取数据库构表过程进行修改

varchar（108）改为 STRING ————
datetime改为TIMESTAMP————double(11,2)改为double 表头table前边添加external
删除set到not null之前的内容 表后括号外删除掉添加： row format delimited fields terminated
by ‘|’ stored as textfile location ‘导入数据表存放路径’;

修改后的代码如下：

 CREATE external TABLE `hvtext_sub_logistics_purchase` (
  `id` STRING  NOT NULL COMMENT '主键',
  `account_id` STRING  NOT NULL COMMENT '账户id',
  `par_id` STRING   DEFAULT NULL COMMENT '主表id',
  `com_code` STRING   NOT NULL COMMENT '商品编码',
  `com_name` STRING DEFAULT NULL COMMENT '商品名称',
  `purchase_num` decimal(11,2) NOT NULL COMMENT '进货数量',
  `purchase_cost` decimal(11,2) NOT NULL COMMENT '进货单价成本',
  `purchase_cost_total` decimal(11,2) NOT NULL COMMENT '进货成本',
  `supplier_id` STRING  DEFAULT NULL COMMENT '供货商id',
  `purchase_price` decimal(11,2) DEFAULT NULL COMMENT '进货售价',
  `purchase_price_total` decimal(11,2) DEFAULT NULL COMMENT '进货总售价',
  PRIMARY KEY (`id`)
) row format delimited
fields terminated by '|'
stored as textfile
location '/sqoop/hv_transaction/tb_sub_logistics_purchase';

原数据库代码如下：

CREATE TABLE `tb_sub_logistics_purchase` (
  `id` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '主键',
  `account_id` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '账户id',
  `par_id` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '主表id',
  `com_code` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '商品编码',
  `com_name` varchar(128) DEFAULT NULL COMMENT '商品名称',
  `purchase_num` decimal(11,2) NOT NULL COMMENT '进货数量',
  `purchase_cost` decimal(11,2) NOT NULL COMMENT '进货单价成本',
  `purchase_cost_total` decimal(11,2) NOT NULL COMMENT '进货成本',
  `supplier_id` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '供货商id',
  `purchase_price` decimal(11,2) DEFAULT NULL COMMENT '进货售价',
  `purchase_price_total` decimal(11,2) DEFAULT NULL COMMENT '进货总售价',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

7.在dbvisualizer里执行修改后的sql语句

二：进行导入的表没有时间戳格式的列需要用全量导入

1.查看配置文件位置：

cd ynyc-bg/transaction/options-file/

ll
会出来一系列的配置信息（小写LL）

3.返回：

cd ~

4.开始导入

//全量导入3：tb_sub_logistics_return_import_job
sqoop \
import \
--options-file /root/ynyc-bg/transaction/options-file/transaction-177.opt \
--table tb_order_bill \
--target-dir /sqoop/hv_transaction/tb_order_bills \
--null-string '\\N' \
--null-non-string '\\N' \
--fields-terminated-by "|" \
-m 3

5.不需要执行job：

sqoop job --exec tb_logistics_purchase_import_job

6.获取数据库构表过程进行修改

varchar（108）改为 STRING datetime改为TIMESTAMP
double(11,2)改为double 表头table前边添加external 删除set到not null之前的内容
表后括号外删除掉添加： row format delimited fields terminated by ‘|’ stored as
textfile location ‘导入数据表存放路径’;

7.在dbvisualizer里执行修改后的sql语句

官方文档地址:
http://sqoop.apache.org/docs/1.4.7/SqoopUserGuide.html#_purpose

三.导出:

3.1 导出的前提条件:在hive中创建表的时候(注意要跟数据库的字段要保持一致,否则会导出错误):
注意1:要导入的MySQL的表如果没有唯一型主键,就要建立两个索引用以区分导入数据(这两个索引在一起具有唯一性),否则数据库的表会筛选数据,致使数据缺失.
注意2:要在创表的table前边加上external 表名创建的是一个外表,在结束要加上:

row format delimited
fields terminated by ','
stored as textfile
location '/sqoop/member/tb_sponsor_account';

整体如下:

show create table hvtext_sponsor_account;
create external  table hvtext_sponsor_account(
  id string default null, 
  account_balance decimal(11,2) default null, 
  account_status int default null, 
  create_time timestamp default null, 
  update_time timestamp default null, 
  remark string default null
)

row format delimited
fields terminated by ','
stored as textfile
location '/sqoop/member/tb_sponsor_account';

3.2

sqoop export \
--options-file /root/ynyc-bg/transaction/options-file/realtime-177.opt \
--table tb_commodity_sale_per_day \
--input-fields-terminated-by '|' \
--export-dir '/inceptor1/user/hive/warehouse/hv_transaction.db/root/hvtext_commodity_sale_pers_days' \
--input-null-string '\\N' \
--input-null-non-string '\\N' \
--update-mode allowinsert \
--update-key account_id,short_code,create_date \
-m1

–input-null-string ‘\N’
–input-null-non-string ‘\N’
这两个很重要,如果没有传过来的null值会成 /n

思考:

vim tb_data_export_main.sh     sqoop中的作用

如上图所示要查看所要导入的数据库是否能够接收导入的数据

3.3 Sqoop抽数脚本(在网上找的,仅作参考):

sqoop export \
--connect jdbc:mysql://ip-172-31-22-86.ap-southeast-1.compute.internal:3306/test_db \
--username testuser \
--password password \
--table mytest_parquet \
--hcatalog-database default \
--hcatalog-table mytest_parquet --num-mappers 1

参数说明：

–table：MySQL库中的表名

–hcatalog-database：Hive中的库名

–hcatalog-table：Hive库中的表名，需要抽数的表

–num-mappers：执行作业的Map数

作业执行成功。

四. Hive建表的注意事项:

CREATE external TABLE `rhvtext_search_shop_bill_result` (
  `account_id` STRING NOT NULL COMMENT '商户id',
  `trans_total` decimal(11,2) DEFAULT NULL COMMENT '交易总价',
  `amount_received` decimal(11,2) DEFAULT NULL COMMENT '实收金额,实际到账',
  `amount_liquidation` decimal(11,2) DEFAULT NULL COMMENT '实际清算金额',
  `fee` decimal(11,2) DEFAULT NULL COMMENT '我方平台手续费',
  `mer_fee` decimal(11,2) DEFAULT NULL COMMENT '第三方平台手续费',
  `fee_total` decimal(11,2) DEFAULT NULL COMMENT '总手续费',
  `trade_time` date DEFAULT NULL COMMENT '交易时间,也是服务器时间',
  `org_short_name` STRING DEFAULT NULL COMMENT '机构短名称',
  `province_id` STRING DEFAULT NULL COMMENT '省机构id',
  `city_id` STRING DEFAULT NULL COMMENT '市机构id',
  `area_id` STRING DEFAULT NULL COMMENT '区机构id',
  `owner_name` STRING DEFAULT NULL COMMENT '店主名字',
  `phone_no` STRING DEFAULT NULL COMMENT '电话号码',
  `manager_name` STRING DEFAULT NULL COMMENT '所属客户经理姓名',
  `manager_id`STRING DEFAULT NULL COMMENT '所属客户经理id',
  `mamager_code` STRING DEFAULT NULL COMMENT '所属客户经理code',
  `create_time` TIMESTAMP DEFAULT NULL COMMENT '创建时间',
  `update_time` TIMESTAMP DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (`account_id`)
)row format delimited
fields terminated by '|'
stored as textfile;

五 导入导出的注意事项

1.再导入到hdfs的时候,导入语句尽量不要有:
–null-string ‘\N’
–null-non-string ‘\N’
因为有这个的话,在创建job的时候是完全没有问题的,能够导入所要导入的数据,但是如果数据库的数据更新后,你要将数据更新的hdfs上就会出现问题:Can’t parse input data: ‘\N’
这个问题困扰我很久,出现这个问题要考虑,这个问题的出现原因,创建的时候就可以为什么现在不行了呢,数据是copy的数据进行改动,那么也应该就是导入语句的问题了,哪个问题呢?大胆猜测是否是/N惹的祸,那么去掉两个空字符处理或者去掉一个测试一下,果然可行.
但是又有新的问题了,导出有这两个转义字符’\N’就没有问题?
答案:导入的时候如果用了两个//N那么就会将空的字符串转成/N给存进去,但是空的string类型的转成/N没太大问题,但是一张数据库表里有很多的列有的列并不是string,可能有decimal,double,date,datetime…这些如果被转成string的/N了,存储的时候软件就会发现date这些格式怎么是/N格式不对照就出错了.
当然又有问题了创建job的时候存储数据为什么没有出错呢?
可能是程序的问题,创建job的时候是正常的将空值给转义了,在更新的时候转义失败了(个人感觉并不一定对,有更好的答案请留言)
习惯
测试的时候会创建很多的job,job又会创建很多的hdfs上的存储文件,测试完成要将这些job和文件给删除掉.(不删除的后果,就是你测的时候要建很多的job和hdfs存储文件,数仓里就要建立新表,占用内存)
查看存储文件语句:Hadoop fs -ls <存储文件路径>
查看存储文件内容:hadoop fs -cat <存储文件路径>/<存储信息>
删除存储文件语句:Hadoop fs -rm -r <存储文件路径>