五、使用阿里云产品进行数据可视化(Quick BI)
结果看板
https://bi.aliyuncs.com/token3rd/dashboard/view/pc.htm?pageId=40459871-839c-4eaa-b48d-68a08823e7f9&accessToken=48ebc87e5d0a5c9b709598bb771ee918&dd_orientation=auto全国各省浏览量
接下来我们导入新的数据文件到ODS中，执行base_area.sql文件运行在MySQL数据库中，生成表数据并加载到Hive中，生成编码表！
使用DataX导入工具进行导入操作，并创建Hive表。
创建Hive表
create external table if not exists ods_nshop.ods_01_base_area (
id int COMMENT 'id标识',
area_code string COMMENT '省份编码',
province_name string COMMENT '省份名称',
iso string COMMENT 'ISO编码'
)row format delimited fields terminated by ','
stored as TextFile
location '/shujia/bigdata17/data/nshop/ods/ods_01_base_area/'注意：首先解压DataX的安装包到服务器，并导入MySQL的驱动包，即可使用！！！
创建Job任务，导入脚本
{
"job": {
"setting": {
"speed": {
"channel": 3
},
"errorLimit": {
"record": 0,
"percentage": 0.02
}
},
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"writeMode": "insert",
"username": "root",
"password": "123456",
"column": [
"id",
"area_code",
"province_name",
"iso"
],
"splitPk": "id",
"connection": [
{
"table": [
"base_area"
],
"jdbcUrl": [
"jdbc:mysql://master:3306/nshop"
]
}
]
}
},
"writer": {
"name": "hdfswriter",
"parameter": {
"defaultFS": "hdfs://master:9000",
"fileType": "text",
"path": "/shujia/bigdata17/data/nshop/ods/ods_01_base_area/",
"fileName": "base_area_txt",
"column": [
{
"name": "id",
"type": "int"
},
{
"name": "area_code",
"type": "string"
},
{
"name": "province_name",
"type": "string"
},
{
"name": "iso",
"type": "string"
}
],
"writeMode": "append",
"fieldDelimiter": ","
}
}
}
]
}
}执行命令
[root@master script]# datax.py m.json最后创建指标统计表
create external table if not exists ads_nshop.ads_nshop_customer(
user_view_count int comment '每个用户浏览次数',
provience string comment '省份'
) partitioned by (bdp_day string)
row format delimited fields terminated by ','
stored as TextFile
location '/shujia/bigdata17/data/nshop/ads/operation/ads_nshop_customer/';执行SQL
insert overwrite table ads_nshop.ads_nshop_customer partition(bdp_day='20220630')
select
sum(a.view_count),
c.province_name
from dws_nshop.dws_nshop_ulog_view a
join ods_nshop.ods_02_customer b
on a.user_id=b.customer_id
join ods_nshop.ods_01_base_area c
on b.customer_natives=c.area_code
where a.bdp_day='20220630'
group by c.province_name;
平台浏览统计
将流量类指标平台PUV同步MySQL中，首先构建MySQL表
CREATE TABLE `ads_nshop_flowpu_stat` (
`uv` int DEFAULT NULL,
`pv` int DEFAULT NULL,
`pv_avg` double DEFAULT NULL
)同步脚本
{
"job": {
"setting": {
"speed": {
"channel": 3
}
},
"content": [
{
"reader": {
"name": "hdfsreader",
"parameter": {
"path": "/shujia/bigdata17/data/nshop/ads/operation/ads_nshop_flow/bdp_day=${dt}/*",
"defaultFS": "hdfs://master:9000",
"column": [
{
"index": 0,
"type": "Long"
},
{
"index": 1,
"type": "Long"
},
{
"index": 2,
"type": "Double"
}
],
"fileType": "text",
"encoding": "UTF-8",
"fieldDelimiter": ","
}
},
"writer": {
"name": "mysqlwriter",
"parameter": {
"writeMode": "insert",
"username": "root",
"password": "123456",
"column": [
"uv",
"pv",
"pv_avg"
],
"connection": [
{
"jdbcUrl": "jdbc:mysql://master:3306/nshop",
"table": [
"ads_nshop_flowpu_stat"
]
}
]
}
}
}
]
}
}执行命令
[root@master script]# datax.py demo2.json -p "-Ddt=20220630"
平台搜索热词统计
创建MySQL表
CREATE TABLE `ads_nshop_search_keys` (
`search_keys` varchar(255) DEFAULT NULL,
`gender` varchar(255) DEFAULT NULL,
`age_range` varchar(255) DEFAULT NULL,
`os` varchar(255) DEFAULT NULL,
`manufacturer` varchar(255) DEFAULT NULL,
`area_code` varchar(255) DEFAULT NULL,
`search_users` int DEFAULT NULL,
`search_records` int DEFAULT NULL,
`search_orders` varchar(255) DEFAULT NULL,
`search_targets` int DEFAULT NULL
)同步脚本
{
"job": {
"setting": {
"speed": {
"channel": 3
}
},
"content": [
{
"reader": {
"name": "hdfsreader",
"parameter": {
"path": "/shujia/bigdata17/data/nshop/ads/operation/ads_nshop_search_keys/bdp_day=${dt}/*",
"defaultFS": "hdfs://master:9000",
"column": [
{
"index": 0,
"type": "string"
},
{
"index": 1,
"type": "string"
},
{
"index": 2,
"type": "string"
},
{
"index": 3,
"type": "string"
},
{
"index": 4,
"type": "string"
},
{
"index": 5,
"type": "string"
},
{
"index": 6,
"type": "long"
},
{
"index": 7,
"type": "long"
},
{
"index": 8,
"type": "string"
},
{
"index": 9,
"type": "long"
}
],
"fileType": "text",
"encoding": "UTF-8",
"fieldDelimiter": ","
}
},
"writer": {
"name": "mysqlwriter",
"parameter": {
"writeMode": "insert",
"username": "root",
"password": "123456",
"column": [
"search_keys",
"gender",
"age_range",
"os",
"manufacturer",
"area_code",
"search_users",
"search_records",
"search_orders",
"search_targets"
],
"connection": [
{
"jdbcUrl": "jdbc:mysql://master:3306/nshop",
"table": [
"ads_nshop_search_keys"
]
}
]
}
}
}
]
}
}执行命令
[root@master datax]# datax.py demo3.json -p "-Ddt=20220630"更新热词搜索对照表，导入shop_code.sql文件到数据库
然后进行匹配热词信息表进行数据重写，首先构建新的表
说明：这里我们要将导出的表去除空值处理
CREATE TABLE `ads_nshop_search_keys_2` (
`search_keys` varchar(255) DEFAULT NULL,
`gender` varchar(255) DEFAULT NULL,
`age_range` varchar(255) DEFAULT NULL,
`os` varchar(255) DEFAULT NULL,
`manufacturer` varchar(255) DEFAULT NULL,
`area_code` varchar(255) DEFAULT NULL,
`search_users` int DEFAULT NULL,
`search_records` int DEFAULT NULL,
`search_orders` varchar(255) DEFAULT NULL,
`search_targets` int DEFAULT NULL
)执行SQL
insert into ads_nshop_search_keys_2(search_keys ,gender,age_range,os,manufacturer,area_code,search_users,search_records,search_orders,search_targets)
SELECT name as search_keys ,gender,age_range,os,manufacturer,area_code,search_users,search_records,search_orders,search_targets FROM ads_nshop_search_keys JOIN shop_code ON search_keys=idCREATE TABLE `ads_nshop_search_keys_3` (
`search_keys` varchar(255) DEFAULT NULL,
`search_records` int DEFAULT NULL
)
insert into ads_nshop_search_keys_3(search_keys,search_records)
select search_keys,sum(search_records) from ads_nshop_search_keys_2 group by search_keys;
广告投放类指标统计
修改SQL语句
1、现在hive中创建一张类别对照表
create external table if not exists ods_nshop.dim_shop_code(
id int comment '类别编号',
name string comment '类别'
)
row format delimited fields terminated by ','
stored as TextFile
location '/shujia/bigdata17/data/nshop/ods/operation/dim_shop_code/';
2、编写dataX文件
{
"job": {
"setting": {
"speed": {
"channel": 3
},
"errorLimit": {
"record": 0,
"percentage": 0.02
}
},
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"writeMode": "insert",
"username": "root",
"password": "123456",
"column": [
"id",
"name"
],
"splitPk": "id",
"connection": [
{
"table": [
"shop_code"
],
"jdbcUrl": [
"jdbc:mysql://master:3306/nshop"
]
}
]
}
},
"writer": {
"name": "hdfswriter",
"parameter": {
"defaultFS": "hdfs://master:9000",
"fileType": "text",
"path": "/shujia/bigdata17/data/nshop/ods/operation/dim_shop_code/",
"fileName": "base_area_txt",
"column": [
{
"name": "id",
"type": "int"
},
{
"name": "name",
"type": "string"
}
],
"writeMode": "append",
"fieldDelimiter": ","
}
}
}
]
}
}
3、创建与维表进行结合的广告投放表
create external table if not exists ads_nshop.ads_nshop_release_stat2(
device_type string comment '设备类型',
os string comment '手机系统',
customer_gender TINYINT comment '性别：1男 0女',
age_range string comment '年龄段',
province_name string comment '省份',
release_sources string comment '投放渠道',
name string comment '投放浏览产品分类',
visit_total_customers int comment '总访客数',
visit_total_counts int comment '总访问次数'
)partitioned by (bdp_day string)
row format delimited fields terminated by ','
stored as TextFile
location '/shujia/bigdata17/data/nshop/ads/operation/ads_nshop_release_stat2/';
4、添加数据
insert overwrite table ads_nshop.ads_nshop_release_stat2 partition(bdp_day='20220630')
select
a.device_type,
a.os,
a.customer_gender,
a.age_range,
b.province_name,
a.release_sources,
c.name,
a.visit_total_customers,
a.visit_total_counts
from ads_nshop.ads_nshop_release_stat a
join ods_nshop.ods_01_base_area b
on a.customer_natives=b.area_code
join ods_nshop.dim_shop_code c
on a.release_category=c.id
where a.bdp_day='20220630';
总体运营指标统计
修改SQL语句重写插入数据
insert overwrite table ads_nshop.ads_nshop_oper_stat partition(bdp_day='20220630')
select
a.customer_gender,
a.customer_age_range,
e.province_name,
c.category_code,
count(distinct b.order_id) ,
count(distinct b.order_id) / sum(d.view_count),
sum(b.payment_money),
sum(b.district_money),
sum(b.shipping_money),
sum(b.payment_money) / count(distinct b.customer_id)
from ods_nshop.ods_02_customer a
join dwd_nshop.dwd_nshop_orders_details b
on a.customer_id=b.customer_id
join ods_nshop.dim_pub_product c
on b.supplier_code=c.supplier_code
join dws_nshop.dws_nshop_ulog_view d
on b.customer_id=d.user_id
join ods_nshop.ods_01_base_area e
on a.customer_natives=e.area_code
where d.bdp_day='20220630'
group by
a.customer_gender,
a.customer_age_range,
e.province_name,
c.category_code;
支付统计TopN
修改SQL
线上支付 10 网上银行 11 微信 12 支付宝 |线下支付(货到付款) 20 '
insert overwrite table ads_nshop.ads_nshop_pay_stat_topn partition(bdp_day='20220630')
select
case when b.pay_type='10' then '网上银行' when b.pay_type='11' then '微信' when b.pay_type='12' then '支付宝 ' else '货到付款' end,
e.province_name,
count(distinct b.pay_id),
sum(b.pay_amount) as pay_sum
from ods_nshop.ods_02_customer a
join ods_nshop.ods_02_orders_pay_records b
on a.customer_id=b.customer_id
join ods_nshop.ods_01_base_area e
on a.customer_natives=e.area_code
group by
e.province_name,
b.pay_type order by pay_sum;
已为社区贡献1条内容
所有评论(0)