一.数仓工具
- clouderamanager
二.sqoop
- 是一款apache旗下的数据导入导出工具,主要的作用:用于将关系型数据库中数据导入到hadoop生态圈,以及hadoop生态圈数据导出到关系型数据库
- sqoop专为关系型数据库和hadoop之间的ETL而生,支持海量数据,符合项目的需求,且操作门槛低
- sqoop的使用
3.1sqoop1以client客户端的形式存在和运行,没有任务没有进程存在
3.2sqoop2以B/s服务器的形式去运行
3.3工作机制:将导入和导出命令转换为mapreduce程序来实现
3.4sqoop的验证sqoop-version
3.5sqoop抽取的两种方式:
3.6:基本操作:sqoop help帮助操作
查询关系型数据库: sqoop list-databases --connect jdbc:mysql://hadoop01:3306 --username root --password 123456
查询表:sqoop list-tables --connect jdbc:mysql://hadoop01:3306 --username root --password 123456
3.7数据全量导入hdfs:
创建测试数据库在mysql:
create database test default character set utf8mb4 collate utf8mb4_unicode_ci;
use test;
http://192.168.24.52:9010/datadev/dataware.git
create table emp
(
id int not null
primary key,
name varchar(32) null,
deg varchar(32) null,
salary int null,
dept varchar(32) null
);
INSERT INTO emp (id, name, deg, salary, dept) VALUES (1201, 'gopal', 'manager', 50000, 'TP');
INSERT INTO emp (id, name, deg, salary, dept) VALUES (1202, 'manisha', 'Proof reader', 50000, 'TP');
INSERT INTO emp (id, name, deg, salary, dept) VALUES (1203, 'khalil', 'php dev', 30000, 'AC');
INSERT INTO emp (id, name, deg, salary, dept) VALUES (1204, 'prasanth', 'php dev', 30000, 'AC');
INSERT INTO emp (id, name, deg, salary, dept) VALUES (1205, 'kranthi', 'admin', 20000, 'TP');
create table emp_add
(
id int not null
primary key,
hno varchar(32) null,
street varchar(32) null,
city varchar(32) null
);
INSERT INTO emp_add (id, hno, street, city) VALUES (1201, '288A', 'vgiri', 'jublee');
INSERT INTO emp_add (id, hno, street, city) VALUES (1202, '108I', 'aoc', 'sec-bad');
INSERT INTO emp_add (id, hno, street, city) VALUES (1203, '144Z', 'pgutta', 'hyd');
INSERT INTO emp_add (id, hno, street, city) VALUES (1204, '78B', 'old city', 'sec-bad');
INSERT INTO emp_add (id, hno, street, city) VALUES (1205, '720X', 'hitec', 'sec-bad');
create table emp_conn
(
id int not null
primary key,
phno varchar(32) null,
email varchar(32) null
);
INSERT INTO emp_conn (id, phno, email) VALUES (1201, '2356742', 'gopal@tp.com');
INSERT INTO emp_conn (id, phno, email) VALUES (1202, '1661663', 'manisha@tp.com');
INSERT INTO emp_conn (id, phno, email) VALUES (1203, '8887776', 'khalil@ac.com');
INSERT INTO emp_conn (id, phno, email) VALUES (1204, '9988774', 'prasanth@ac.com');
INSERT INTO emp_conn (id, phno, email) VALUES (1205, '1231231', 'kranthi@tp.com');
sqoop import --connect jdbc:mysql://hadoop01:3306/test --username root --password 123456 --table emp
不指定目标地址,默认存储在-m表示只有map阶段
指定文件目录emp_01:sqoop import --connect jdbc:mysql://hadoop01:3306/test --username root --password 123456 --table emp --delete-target-dir --target-dir /emp_01
指定输出map的数量: sqoop import --connect jdbc:mysql://hadoop01:3306/test --username root --password 123456 --table emp --delete-target-dir --target-dir /emp_02 -m 2 --split-by id 根据id切割
指定分割符号:sqoop import --connect jdbc:mysql://hadoop01:3306/test --username root --password 123456 --table emp --delete-target-dir --target-dir /emp_03 -m 2 --split-by id --fields-terminated-by ‘\001’
将数据导入到hive:在hive创建目标表
在hive创建库表:
CREATE DATABASE sqoophive;
use sqoophhive;
CREATE TABLE emp_hive(
id int ,
name STRING,
deg STRING,
salary int,
dept STRING
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS orc;
导入:
sqoop import --connect jdbc:mysql://hadoop01:3306/test --username root --password 123456 --table emp --fields-terminated-by ‘\001’ --hcatalog-database sqoophive --hcatalog-table emp_hive --m 1
3.7数据增量导入
数据条件导入:通过where方式,将数据增量导入到HDFS中:sqoop import --connect jdbc:mysql://hadoop01:3306/test --username root --password 123456 --table emp_add --where “city = ‘sec-bad’” --delete-target-dir --target-dir /emp_add -m 1
通过query方式将数据导入到HDFS: sqoop import --connect jdbc:mysql://hadoop01:3306/test --username root --password 123456 --query ‘select phno from emp_conn where 1=1 and $CONDITIONS’ --delete-target-dir --target-dir /emp_conn -m 1
增量输入到hive
新增两条数据:
INSERT INTO test.emp
VALUES
( 1206, '张三', 'java dev', 1000000, 'AC' );
INSERT INTO test.emp
VALUES
( 1207, 'lisi', 'bigdata dev', 3000000, 'TP' );
sqoop import --connect jdbc:mysql://hadoop01:3306/test --username root --password 123456 --query ‘select * from emp where id>1205 and $CONDITIONS’ --fields-terminated-by ‘\001’ --hcatalog-database sqoophive --hcatalog-table emp_hive --m 1
3.8数据全量导出
创建mysql表:
CREATE TABLE test.emp_mysql (
id INT(11) DEFAULT NULL,
name VARCHAR(100) DEFAULT NULL,
deg VARCHAR(100) DEFAULT NULL,
salary INT(11) DEFAULT NULL,
dept VARCHAR(10) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;
sqoop import --connect jdbc:mysql://hadoop01:3306/test --username root --password 123456 --table emp_mysql --fields-terminated-by ‘\t’ --hcatalog-database sqoophive --hcatalog-table emp_hive --m 1
三.hive的基础调优
-
HDFS的副本数量调整
一般为3个,如果数据不是特别重要建议设置2;HDFS3.0后调整为1.5通过纠删码(校验码恢复数据耗资源)
-
nodemanager的相关基础配置 :(提供计算的资源)
cpu的配置操作:配置项yarn.nodemanager.resource.cpu-vcores
默认值为8一般都需要手动调整
推荐配置:服务器多少核就配置多少核
查看cpu的核心数命令:grep ‘processor’ /proc/cpuinfo | sort -u | wc -l
yarn.nodemanager.resource.cpu-vcores
-
内存配置:yarn.nodemanager.resource.memory-mb
通过命令查看内存:free -m -
本地目录:yarn.nodemanager.local-dirs
-
mapreduce的内存配置:
-
hive的基础配置:
-
压缩的配置:
-
执行引擎选择:
四.完成ODS层数据采集操作
-
作用:对接数据源,一般和数据源保持相同粒度(一对一拷贝,不允许对业务端数据进行处理操作)
-
ODS层处于hive端
-
业务数据存储在mysql
-
技术:sqoop完成数据采集操作(有哪些表,表的结构,表字段的类型,是否使用内部表和外部表(导入后这个表是否具有控制权(删除));是否使用分区表(一般都是分区表,除非是映射原有数据,无法构建分区);存储类型以及压缩方案(列式存储:ORC;行式存储:textfile))
-
数据存储和压缩方案:
分区一般都是时间 -
数据同步方式:
6.1全量覆盖同步方式:(区域和日期表)
每次同步数据,将原有数仓中表全部删除,重新从业务端导入(变更频次不多,而且整个表数据量相对较少)不要分区表
6.2仅新增同步方式:(订单评价,登录记录表,订单组支付表)
只新增不会有变更,不需要维护历史数据
6.3新增及更新同步数据(数据既有更新又有新增,数据量大)拉链表(店铺,商圈,地址信息,店铺商品,商品分类,品牌,订单,订单详情,商品评价明细,交易记录)
6.4全量同步方式:表的数据量不大,存在新增和更新,而且不需要保留太多历史版本 -
建库建表
7.1 建库:CREATE DATABASE yp_ods;
7.2 建表:
CREATE TABLE yp_ods.t_date(
dim_date_id string COMMENT '日期',
date_code string COMMENT '日期编码',
lunar_calendar string COMMENT '农历',
year_code string COMMENT '年code',
year_name string COMMENT '年名称',
month_code string COMMENT '月份编码',
month_name string COMMENT '月份名称',
quanter_code string COMMENT '季度编码',
quanter_name string COMMENT '季度名称',
year_month string COMMENT '年月',
year_week_code string COMMENT '一年中第几周',
year_week_name string COMMENT '一年中第几周名称',
year_week_code_cn string COMMENT '一年中第几周(中国)',
year_week_name_cn string COMMENT '一年中第几周名称(中国',
week_day_code string COMMENT '周几code',
week_day_name string COMMENT '周几名称',
day_week string COMMENT '周',
day_week_cn string COMMENT '周(中国)',
day_week_num string COMMENT '一周第几天',
day_week_num_cn string COMMENT '一周第几天(中国)',
day_month_num string COMMENT '一月第几天',
day_year_num string COMMENT '一年第几天',
date_id_wow string COMMENT '与本周环比的上周日期',
date_id_mom string COMMENT '与本月环比的上月日期',
date_id_wyw string COMMENT '与本周同比的上年日期',
date_id_mym string COMMENT '与本月同比的上年日期',
first_date_id_month string COMMENT '本月第一天日期',
last_date_id_month string COMMENT '本月最后一天日期',
half_year_code string COMMENT '半年code',
half_year_name string COMMENT '半年名称',
season_code string COMMENT '季节编码',
season_name string COMMENT '季节名称',
is_weekend string COMMENT '是否周末(周六和周日)',
official_holiday_code string COMMENT '法定节假日编码',
official_holiday_name string COMMENT '法定节假日',
festival_code string COMMENT '节日编码',
festival_name string COMMENT '节日',
custom_festival_code string COMMENT '自定义节日编码',
custom_festival_name string COMMENT '自定义节日',
update_time string COMMENT '更新时间'
) COMMENT'日期维度表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC
TBLPROPERTIES('orc.compress' = 'ZLIB');
导入数据:
sqoop import --connect jdbc:mysql://192.168.88.80:3306/yipin --username root --password 123456 --table t_date --fields-terminated-by '\t' --hcatalog-database yp_ods --hcatalog-table t_date --m 1
增量同步:订单评价表
CREATE TABLE yp_ods.t_goods_evaluation
(
`id` string,
`user_id` string COMMENT '评论人id',
`store_id` string COMMENT '店铺id',
`order_id` string COMMENT '订单id',
`geval_scores` INT COMMENT '综合评分',
`geval_scores_speed` INT COMMENT '送货速度评分0-5分(配送评分)',
`geval_scores_service` INT COMMENT '服务评分0-5分',
`geval_isanony` TINYINT COMMENT '0-匿名评价,1-非匿名',
`create_user` string,
`create_time` string,
`update_user` string,
`update_time` string,
`is_valid` TINYINT COMMENT '0 :失效,1 :开启'
)
comment '商品评价表'
partitioned by (dt string) row format delimited fields terminated by '\t' stored as orc tblproperties ('orc.compress'='ZLIB');
导入:
sqoop import
–connect 'jdbc:mysql://192.168.88.80:3306/yipin
–username root
–password 123456
–query "select *, ‘2022-03-04’ as dt from t_goods_evaluation where create_time<‘2022-03-04 00:00:00’ and $CONDITIONS
–hcatalog-database yp_ods
–hcatalog-table t_goods_evaluation
-m 1
sqoop import --connect jdbc:mysql://192.168.88.80:3306/yipin --username root
–password 123456 --table t_date --fields-terminated-by ‘\t’
–hcatalog-database yp_ods --hcatalog-table t_date --m 1
后期导入:
- 新增和更新同步:店铺表
8.1 建表:
CREATE TABLE yp_ods.t_store
(
`id` string COMMENT '主键',
`user_id` string,
`store_avatar` string COMMENT '店铺头像',
`address_info` string COMMENT '店铺详细地址',
`name` string COMMENT '店铺名称',
`store_phone` string COMMENT '联系电话',
`province_id` INT COMMENT '店铺所在省份ID',
`city_id` INT COMMENT '店铺所在城市ID',
`area_id` INT COMMENT '店铺所在县ID',
`mb_title_img` string COMMENT '手机店铺 页头背景图',
北京市昌平区建材城西路金燕龙办公楼一层 电话:400-618-9090
`store_description` string COMMENT '店铺描述',
`notice` string COMMENT '店铺公告',
`is_pay_bond` TINYINT COMMENT '是否有交过保证金 1:是0:否',
`trade_area_id` string COMMENT '归属商圈ID',
`delivery_method` TINYINT COMMENT '配送方式 1 :自提 ;3 :自提加配送均可; 2 : 商家配送',
`origin_price` DECIMAL,
`free_price` DECIMAL,
`store_type` INT COMMENT '店铺类型 22天街网店 23实体店 24直营店铺 33会员专区店',
`store_label` string COMMENT '店铺logo',
`search_key` string COMMENT '店铺搜索关键字',
`end_time` string COMMENT '营业结束时间',
`start_time` string COMMENT '营业开始时间',
`operating_status` TINYINT COMMENT '营业状态 0 :未营业 ;1 :正在营业',
`create_user` string,
`create_time` string,
`update_user` string,
`update_time` string,
`is_valid` TINYINT COMMENT '0关闭,1开启,3店铺申请中',
`state` string COMMENT '可使用的支付类型:MONEY金钱支付;CASHCOUPON现金券支付',
`idCard` string COMMENT '身份证',
`deposit_amount` DECIMAL(11,2) COMMENT '商圈认购费用总额',
`delivery_config_id` string COMMENT '配送配置表关联ID',
`aip_user_id` string COMMENT '通联支付标识ID',
`search_name` string COMMENT '模糊搜索名称字段:名称_+真实名称',
`automatic_order` TINYINT COMMENT '是否开启自动接单功能 1:是 0 :否',
`is_primary` TINYINT COMMENT '是否是总店 1: 是 2: 不是',
`parent_store_id` string COMMENT '父级店铺的id,只有当is_primary类型为2时有效'
)
comment '店铺表'
partitioned by (dt string) row format delimited fields terminated by '\t' stored as orc tblproperties
('orc.compress'='ZLIB');
8.2导入数据:
首次:sqoop import --connect jdbc:mysql://hadoop01:3306/yipin --username root --password 123456 --query "select *,'2022-03-07' as dt from t_store where create_time<'2022-03-07 00:00:00' or update_time<'2022-03-07 00:00:00' and \$CONDITIONS" --hcatalog-database yp_ods --hcatalog-table t_store --m 1
后续增量:每天导入上一天的新增