执行代码(全量)
– 这个文件主要用来保存关于数仓中各个层级的库和表的创建语句
– 业务数据库 -> ODS -> DWD -> DWS -> MySQL
– 1.2 修改一下HIVE的元数据，确保注释不会乱码
– 修改表字段注解和表注解
alter table COLUMNS_V2 modify column COMMENT varchar(256) character set utf8;
alter table TABLE_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
– 修改分区字段注解
alter table PARTITION_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8 ;
alter table PARTITION_KEYS modify column PKEY_COMMENT varchar(4000) character set utf8;
– 修改索引注解
alter table INDEX_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
CREATE DATABASE IF NOT EXISTS itcast_ods;
set hive.exec.orc.compression.strategy=COMPRESSION;
ods层
CREATE EXTERNAL TABLE IF NOT EXISTS itcast_ods.web_chat_ems (
id INT comment ‘主键’,
create_date_time STRING comment ‘数据创建时间’,
session_id STRING comment ‘七陌sessionId’,
sid STRING comment ‘访客id’,
create_time STRING comment ‘会话创建时间’,
seo_source STRING comment ‘搜索来源’,
seo_keywords STRING comment ‘关键字’,
ip STRING comment ‘IP地址’,
area STRING comment ‘地域’,
country STRING comment ‘所在国家’,
province STRING comment ‘省’,
city STRING comment ‘城市’,
origin_channel STRING comment ‘投放渠道’,
user_match STRING comment ‘所属坐席’,
manual_time STRING comment ‘人工开始时间’,
begin_time STRING comment '坐席领取时间 ',
end_time STRING comment ‘会话结束时间’,
last_customer_msg_time_stamp STRING comment ‘客户最后一条消息的时间’,
last_agent_msg_time_stamp STRING comment ‘坐席最后一下回复的时间’,
reply_msg_count INT comment ‘客服回复消息数’,
msg_count INT comment ‘客户发送消息数’,
browser_name STRING comment ‘浏览器名称’,
os_info STRING comment ‘系统名称’)
comment ‘访问会话信息表’
PARTITIONED BY(starts_time STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘\t’
stored as orc
location ‘/user/hive/warehouse/itcast_ods.db/web_chat_ems_ods’
TBLPROPERTIES (‘orc.compress’=‘ZLIB’);
ods层
CREATE EXTERNAL TABLE IF NOT EXISTS itcast_ods.web_chat_text_ems (
id INT COMMENT ‘主键来自MySQL’,
referrer STRING comment ‘上级来源页面’,
from_url STRING comment ‘会话来源页面’,
landing_page_url STRING comment ‘访客着陆页面’,
url_title STRING comment ‘咨询页面title’,
platform_description STRING comment ‘客户平台信息’,
other_params STRING comment ‘扩展字段中数据’,
history STRING comment ‘历史访问记录’
) comment ‘EMS-PV测试表’
PARTITIONED BY(start_time STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘\t’
stored as orc
location ‘/user/hive/warehouse/itcast_ods.db/web_chat_text_ems_ods’
TBLPROPERTIES (‘orc.compress’=‘ZLIB’);
dwd层
CREATE DATABASE IF NOT EXISTS itcast_dwd
WITH DBPROPERTIES ( ‘creator’ = ‘kongshuai’, ‘create_date’ = ‘2020-05-05’);
create table if not exists itcast_dwd.visit_consult_dwd(
session_id STRING comment ‘七陌sessionId’,
sid STRING comment ‘访客id’,
create_time bigint comment ‘会话创建时间’,
seo_source STRING comment ‘搜索来源’,
ip STRING comment ‘IP地址’,
area STRING comment ‘地域’,
msg_count int comment ‘客户发送消息数’,
origin_channel STRING COMMENT ‘来源渠道’,
referrer STRING comment ‘上级来源页面’,
from_url STRING comment ‘会话来源页面’,
landing_page_url STRING comment ‘访客着陆页面’,
url_title STRING comment ‘咨询页面title’,
platform_description STRING comment ‘客户平台信息’,
other_params STRING comment ‘扩展字段中数据’,
history STRING comment ‘历史访问记录’,
hourinfo string comment ‘小时’,
quarterinfo string comment ‘季度’
)
comment ‘访问咨询DWD表’
partitioned by(yearinfo String, monthinfo String, dayinfo string)
row format delimited fields terminated by ‘\t’
stored as orc
location ‘/user/hive/warehouse/itcast_dwd.db/visit_consult_dwd’
tblproperties (‘orc.compress’=‘SNAPPY’);
dws层
CREATE DATABASE IF NOT EXISTS itcast_dws
WITH DBPROPERTIES ( ‘creator’ = ‘kongshuai’, ‘create_date’ = ‘2020-05-05’);
CREATE TABLE IF NOT EXISTS itcast_dws.visit_dws (
sid_total INT COMMENT ‘根据sid去重求count’,
sessionid_total INT COMMENT ‘根据sessionid去重求count’,
ip_total INT COMMENT ‘根据IP去重求count’,
area STRING COMMENT ‘区域信息’,
seo_source STRING COMMENT ‘搜索来源’,
origin_channel STRING COMMENT ‘来源渠道’,
hourinfo STRING COMMENT ‘创建时间，统计至小时’,
quarterinfo STRING COMMENT ‘季度’,
time_str STRING COMMENT ‘时间明细’,
from_url STRING comment ‘会话来源页面’,
groupType STRING COMMENT ‘产品属性类型：1.地区；2.搜索来源；3.来源渠道；4.会话来源页面；5.总访问量’,
time_type STRING COMMENT ‘时间聚合类型：1、按小时聚合；2、按天聚合；3、按月聚合；4、按季度聚合；5、按年聚合；’)
comment ‘EMS访客日志dws表’
PARTITIONED BY(yearinfo STRING,monthinfo STRING,dayinfo STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘\t’
stored as orc
location ‘/user/hive/warehouse/itcast_dws.db/visit_dws’
TBLPROPERTIES (‘orc.compress’=‘SNAPPY’);
dwd–>dws
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
–hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
–写入时ORC压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;
INSERT INTO TABLE itcast_dwd.visit_consult_dwd partition(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
w1.session_id,
w1.sid,
UNIX_TIMESTAMP(w1.create_time, ‘yyyy-MM-dd HH:mm:ss’) AS create_time,
w1.seo_source,
w1.ip,
w1.area,
CAST(IF (w1.msg_count IS NULL, 0, w1.msg_count) AS INT) AS msg_count,
w1.origin_channel,
w2.referrer,
w2.from_url,
w2.landing_page_url,
w2.url_title,
w2.platform_description,
w2.other_params,
w2.history,
SUBSTRING(w1.create_time, 12, 2) AS hourinfo,
SUBSTRING(w1.create_time, 1, 4) AS yearinfo,
QUARTER(w1.create_time) AS quarterinfo,
SUBSTRING(w1.create_time, 6, 2) AS monthinfo,
SUBSTRING(w1.create_time, 9, 2) AS dayinfo
FROM itcast_ods.web_chat_ems AS w1 INNER JOIN itcast_ods.web_chat_text_ems AS w2
ON w1.id = w2.id;
CREATE TABLE IF NOT EXISTS itcast_dws.visit_dws (
sid_total INT COMMENT ‘根据sid去重求count’,
sessionid_total INT COMMENT ‘根据sessionid去重求count’,
ip_total INT COMMENT ‘根据IP去重求count’,
area STRING COMMENT ‘区域信息’,
seo_source STRING COMMENT ‘搜索来源’,
origin_channel STRING COMMENT ‘来源渠道’,
hourinfo STRING COMMENT ‘创建时间，统计至小时’,
time_str STRING COMMENT ‘时间明细’,
from_url STRING comment ‘会话来源页面’,
groupType STRING COMMENT ‘产品属性类型：1.地区；2.搜索来源；3.来源渠道；4.会话来源页面；5.总访问量’,
time_type STRING COMMENT ‘时间聚合类型：1、按小时聚合；2、按天聚合；3、按月聚合；4、按季度聚合；5、按年聚合；’)
comment ‘EMS访客日志dws表’
PARTITIONED BY(yearinfo STRING,quarterinfo string,monthinfo STRING,dayinfo STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘\t’
stored as orc
location ‘/user/hive/warehouse/itcast_dws.db/visit_dws’
TBLPROPERTIES (‘orc.compress’=‘SNAPPY’);
–动态分区配置
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
–hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
–写入时ORC压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;
– 统计某年的访问量
CREATE TABLE IF NOT EXISTS itcast_dws.visit_dws (
sid_total INT COMMENT ‘根据sid去重求count’,
sessionid_total INT COMMENT ‘根据sessionid去重求count’,
ip_total INT COMMENT ‘根据IP去重求count’,
area STRING COMMENT ‘区域信息’,
seo_source STRING COMMENT ‘搜索来源’,
origin_channel STRING COMMENT ‘来源渠道’,
hourinfo STRING COMMENT ‘创建时间，统计至小时’,
time_str STRING COMMENT ‘时间明细’,
from_url STRING comment ‘会话来源页面’,
groupType STRING COMMENT ‘产品属性类型：1.地区；2.搜索来源；3.来源渠道；4.会话来源页面；5.总访问量’,
time_type STRING COMMENT ‘时间聚合类型：1、按小时聚合；2、按天聚合；3、按月聚合；4、按季度聚合；5、按年聚合；’)
comment ‘EMS访客日志dws表’
PARTITIONED BY(yearinfo STRING, quarterinfo string,monthinfo STRING,dayinfo STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘\t’
stored as orc
location ‘/user/hive/warehouse/itcast_dws.db/visit_dws’
TBLPROPERTIES (‘orc.compress’=‘SNAPPY’);
INSERT INTO TABLE itcast_dws.visit_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
‘-1’ AS area,
‘-1’ AS seo_source,
‘-1’ AS origin_channel,
‘-1’ AS hourinfo,
yearinfo AS time_str,
‘-1’ AS from_url,
‘5’ AS groupType,
‘5’ AS time_type,
yearinfo,
‘-1’ AS quarterinfo,
‘-1’ AS monthinfo,
‘-1’ AS dayinfo
FROM itcast_dwd.visit_consult_dwd
GROUP BY yearinfo;
– 统计某年某季度的访问量
INSERT INTO TABLE itcast_dws.visit_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
‘-1’ AS area,
‘-1’ AS seo_source,
‘-1’ AS origin_channel,
‘-1’ AS hourinfo,
CONCAT(yearinfo, ‘-Q’, quarterinfo) AS time_str,
‘-1’ AS from_url,
‘5’ AS groupType,
‘4’ AS time_type,
yearinfo,
quarterinfo,
‘-1’ AS monthinfo,
‘-1’ AS dayinfo
FROM itcast_dwd.visit_consult_dwd
GROUP BY yearinfo, quarterinfo;
– 统计某年某季度某月的访问量
INSERT INTO TABLE itcast_dws.visit_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
‘-1’ AS area,
‘-1’ AS seo_source,
‘-1’ AS origin_channel,
‘-1’ AS hourinfo,
CONCAT(yearinfo, ‘-’, monthinfo) AS time_str,
‘-1’ AS from_url,
‘5’ AS groupType,
‘3’ AS time_type,
yearinfo,
quarterinfo,
monthinfo,
‘-1’ AS dayinfo
FROM itcast_dwd.visit_consult_dwd
GROUP BY yearinfo, quarterinfo, monthinfo;
– 统计某年某季度某月某日的访问量
INSERT INTO TABLE itcast_dws.visit_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
‘-1’ AS area,
‘-1’ AS seo_source,
‘-1’ AS origin_channel,
‘-1’ AS hourinfo,
CONCAT(yearinfo, ‘-’, monthinfo, ‘-’, dayinfo) AS time_str,
‘-1’ AS from_url,
‘5’ AS groupType,
‘2’ AS time_type,
yearinfo,
quarterinfo,
monthinfo,
dayinfo
FROM itcast_dwd.visit_consult_dwd
GROUP BY yearinfo, quarterinfo, monthinfo, dayinfo;
– 统计某年某季度某月某日某小时的访问量
INSERT INTO TABLE itcast_dws.visit_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
‘-1’ AS area,
‘-1’ AS seo_source,
‘-1’ AS origin_channel,
hourinfo,
CONCAT(yearinfo, ‘-’, monthinfo, ‘-’, dayinfo, ’ ', hourinfo) AS time_str,
‘-1’ AS from_url,
‘5’ AS groupType,
‘1’ AS time_type,
yearinfo,
quarterinfo,
monthinfo,
dayinfo
FROM itcast_dwd.visit_consult_dwd
GROUP BY yearinfo, quarterinfo, monthinfo, dayinfo, hourinfo;
– 按照时间和其它维度汇合进行聚合
– 按照时间和地区进行组合计算
– 统计某年某地区的访问量
INSERT INTO TABLE itcast_dws.visit_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
area,
‘-1’ AS seo_source,
‘-1’ AS origin_channel,
‘-1’ AS hourinfo,
yearinfo AS time_str,
‘-1’ AS from_url,
‘1’ AS groupType,
‘5’ AS time_type,
yearinfo,
‘-1’ AS quarterinfo,
‘-1’ AS monthinfo,
‘-1’ AS dayinfo
FROM itcast_dwd.visit_consult_dwd
GROUP BY yearinfo,area;
– 统计某年某季度某地区的访问量
INSERT INTO TABLE itcast_dws.visit_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
area,
‘-1’ AS seo_source,
‘-1’ AS origin_channel,
‘-1’ AS hourinfo,
CONCAT(yearinfo, ‘-Q’, quarterinfo) AS time_str,
‘-1’ AS from_url,
‘1’ AS groupType,
‘4’ AS time_type,
yearinfo,
quarterinfo,
‘-1’ AS monthinfo,
‘-1’ AS dayinfo
FROM itcast_dwd.visit_consult_dwd
GROUP BY yearinfo, quarterinfo, area;
– 统计某年某季度某月某地区的访问量
INSERT INTO TABLE itcast_dws.visit_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
area,
‘-1’ AS seo_source,
‘-1’ AS origin_channel,
‘-1’ AS hourinfo,
CONCAT(yearinfo, ‘-’, monthinfo) AS time_str,
‘-1’ AS from_url,
‘1’ AS groupType,
‘3’ AS time_type,
yearinfo,
quarterinfo,
monthinfo,
‘-1’ AS dayinfo
FROM itcast_dwd.visit_consult_dwd
GROUP BY yearinfo, quarterinfo, monthinfo, area;
– 统计某年某季度某月某天某地区的访问量
INSERT INTO TABLE itcast_dws.visit_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
area,
‘-1’ AS seo_source,
‘-1’ AS origin_channel,
‘-1’ AS hourinfo,
CONCAT(yearinfo, ‘-’, monthinfo, ‘-’, dayinfo) AS time_str,
‘-1’ AS from_url,
‘1’ AS groupType,
‘2’ AS time_type,
yearinfo,
quarterinfo,
monthinfo,
dayinfo
FROM itcast_dwd.visit_consult_dwd
GROUP BY yearinfo, quarterinfo, monthinfo, dayinfo, area;
– 统计某年某季度某月某天某小时某地区的访问量
INSERT INTO TABLE itcast_dws.visit_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
area,
‘-1’ AS seo_source,
‘-1’ AS origin_channel,
hourinfo,
CONCAT(yearinfo, ‘-’, monthinfo, ‘-’, dayinfo, ’ ', hourinfo) AS time_str,
‘-1’ AS from_url,
‘1’ AS groupType,
‘1’ AS time_type,
yearinfo,
quarterinfo,
monthinfo,
dayinfo
FROM itcast_dwd.visit_consult_dwd
GROUP BY yearinfo, quarterinfo, monthinfo, dayinfo, hourinfo, area;
– 按照时间和搜索来源进行组合计算
– 统计某年某搜索来源的访问量
INSERT INTO TABLE itcast_dws.visit_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
‘-1’ AS area,
seo_source,
‘-1’ AS origin_channel,
‘-1’ AS hourinfo,
yearinfo AS time_str,
‘-1’ AS from_url,
‘2’ AS groupType,
‘5’ AS time_type,
yearinfo,
‘-1’ AS quarterinfo,
‘-1’ AS monthinfo,
‘-1’ AS dayinfo
FROM itcast_dwd.visit_consult_dwd
GROUP BY yearinfo,seo_source;
– 统计某年某季度某搜索来源的访问量
INSERT INTO TABLE itcast_dws.visit_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
‘-1’ AS area,
seo_source,
‘-1’ AS origin_channel,
‘-1’ AS hourinfo,
CONCAT(yearinfo, ‘-Q’, quarterinfo) AS time_str,
‘-1’ AS from_url,
‘2’ AS groupType,
‘4’ AS time_type,
yearinfo,
quarterinfo,
‘-1’ AS monthinfo,
‘-1’ AS dayinfo
FROM itcast_dwd.visit_consult_dwd
GROUP BY yearinfo, quarterinfo, seo_source;
– 统计某年某季度某月某搜索来源的访问量
INSERT INTO TABLE itcast_dws.visit_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
‘-1’ AS area,
seo_source,
‘-1’ AS origin_channel,
‘-1’ AS hourinfo,
CONCAT(yearinfo, ‘-’, monthinfo) AS time_str,
‘-1’ AS from_url,
‘2’ AS groupType,
‘3’ AS time_type,
yearinfo,
quarterinfo,
monthinfo,
‘-1’ AS dayinfo
FROM itcast_dwd.visit_consult_dwd
GROUP BY yearinfo, quarterinfo, monthinfo, seo_source;
– 统计某年某季度某月某天某搜索来源的访问量
INSERT INTO TABLE itcast_dws.visit_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
‘-1’ AS area,
seo_source,
‘-1’ AS origin_channel,
‘-1’ AS hourinfo,
CONCAT(yearinfo, ‘-’, monthinfo, ‘-’, dayinfo) AS time_str,
‘-1’ AS from_url,
‘2’ AS groupType,
‘2’ AS time_type,
yearinfo,
quarterinfo,
monthinfo,
dayinfo
FROM itcast_dwd.visit_consult_dwd
GROUP BY yearinfo, quarterinfo, monthinfo, dayinfo, seo_source;
– 统计某年某季度某月某天某小时某搜索来源的访问量
INSERT INTO TABLE itcast_dws.visit_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
‘-1’ AS area,
seo_source,
‘-1’ AS origin_channel,
hourinfo,
CONCAT(yearinfo, ‘-’, monthinfo, ‘-’, dayinfo, ’ ', hourinfo) AS time_str,
‘-1’ AS from_url,
‘2’ AS groupType,
‘1’ AS time_type,
yearinfo,
quarterinfo,
monthinfo,
dayinfo
FROM itcast_dwd.visit_consult_dwd
GROUP BY yearinfo, quarterinfo, monthinfo, dayinfo, hourinfo, seo_source;
– 按照时间和来源渠道进行组合计算
– 统计某年某来源渠道的访问量
INSERT INTO TABLE itcast_dws.visit_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
‘-1’ AS area,
‘-1’ AS seo_source,
origin_channel,
‘-1’ AS hourinfo,
yearinfo AS time_str,
‘-1’ AS from_url,
‘3’ AS groupType,
‘5’ AS time_type,
yearinfo,
‘-1’ AS quarterinfo,
‘-1’ AS monthinfo,
‘-1’ AS dayinfo
FROM itcast_dwd.visit_consult_dwd
GROUP BY yearinfo,origin_channel;
– 统计某年某季度某来源渠道的访问量
INSERT INTO TABLE itcast_dws.visit_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
‘-1’ AS area,
‘-1’ AS seo_source,
origin_channel,
‘-1’ AS hourinfo,
CONCAT(yearinfo, ‘-Q’, quarterinfo) AS time_str,
‘-1’ AS from_url,
‘3’ AS groupType,
‘4’ AS time_type,
yearinfo,
quarterinfo,
‘-1’ AS monthinfo,
‘-1’ AS dayinfo
FROM itcast_dwd.visit_consult_dwd
GROUP BY yearinfo, quarterinfo, origin_channel;
– 统计某年某季度某月某来源渠道的访问量
INSERT INTO TABLE itcast_dws.visit_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
‘-1’ AS area,
‘-1’ AS seo_source,
origin_channel,
‘-1’ AS hourinfo,
CONCAT(yearinfo, ‘-’, monthinfo) AS time_str,
‘-1’ AS from_url,
‘3’ AS groupType,
‘3’ AS time_type,
yearinfo,
quarterinfo,
monthinfo,
‘-1’ AS dayinfo
FROM itcast_dwd.visit_consult_dwd
GROUP BY yearinfo, quarterinfo, monthinfo, origin_channel;
– 统计某年某季度某月某天某来源渠道的访问量
INSERT INTO TABLE itcast_dws.visit_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
‘-1’ AS area,
‘-1’ AS seo_source,
origin_channel,
‘-1’ AS hourinfo,
CONCAT(yearinfo, ‘-’, monthinfo, ‘-’, dayinfo) AS time_str,
‘-1’ AS from_url,
‘3’ AS groupType,
‘2’ AS time_type,
yearinfo,
quarterinfo,
monthinfo,
dayinfo
FROM itcast_dwd.visit_consult_dwd
GROUP BY yearinfo, quarterinfo, monthinfo, dayinfo, origin_channel;
– 统计某年某季度某月某天某小时某来源渠道的访问量
INSERT INTO TABLE itcast_dws.visit_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
‘-1’ AS area,
‘-1’ AS seo_source,
origin_channel,
hourinfo,
CONCAT(yearinfo, ‘-’, monthinfo, ‘-’, dayinfo, ’ ', hourinfo) AS time_str,
‘-1’ AS from_url,
‘3’ AS groupType,
‘1’ AS time_type,
yearinfo,
quarterinfo,
monthinfo,
dayinfo
FROM itcast_dwd.visit_consult_dwd
GROUP BY yearinfo, quarterinfo, monthinfo, dayinfo, hourinfo, origin_channel;
– 按照时间和会话来源窗口进行组合计算
– 统计某年某会话来源窗口的访问量
INSERT INTO TABLE itcast_dws.visit_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
‘-1’ AS area,
‘-1’ AS seo_source,
‘-1’ AS origin_channel,
‘-1’ AS hourinfo,
yearinfo AS time_str,
from_url,
‘4’ AS groupType,
‘5’ AS time_type,
yearinfo,
‘-1’ AS quarterinfo,
‘-1’ AS monthinfo,
‘-1’ AS dayinfo
FROM itcast_dwd.visit_consult_dwd
GROUP BY yearinfo,from_url;
– 统计某年某季度某会话来源窗口的访问量
INSERT INTO TABLE itcast_dws.visit_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
‘-1’ AS area,
‘-1’ AS seo_source,
‘-1’ AS origin_channel,
‘-1’ AS hourinfo,
CONCAT(yearinfo, ‘-Q’, quarterinfo) AS time_str,
from_url,
‘4’ AS groupType,
‘4’ AS time_type,
yearinfo,
quarterinfo,
‘-1’ AS monthinfo,
‘-1’ AS dayinfo
FROM itcast_dwd.visit_consult_dwd
GROUP BY yearinfo, quarterinfo, from_url;
– 统计某年某季度某月某会话来源窗口的访问量
INSERT INTO TABLE itcast_dws.visit_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
‘-1’ AS area,
‘-1’ AS seo_source,
‘-1’ AS origin_channel,
‘-1’ AS hourinfo,
CONCAT(yearinfo, ‘-’, monthinfo) AS time_str,
from_url,
‘4’ AS groupType,
‘3’ AS time_type,
yearinfo,
quarterinfo,
monthinfo,
‘-1’ AS dayinfo
FROM itcast_dwd.visit_consult_dwd
GROUP BY yearinfo, quarterinfo, monthinfo, from_url;
– 统计某年某季度某月某天某会话来源窗口的访问量
INSERT INTO TABLE itcast_dws.visit_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
‘-1’ AS area,
‘-1’ AS seo_source,
‘-1’ AS origin_channel,
‘-1’ AS hourinfo,
CONCAT(yearinfo, ‘-’, monthinfo, ‘-’, dayinfo) AS time_str,
from_url,
‘4’ AS groupType,
‘2’ AS time_type,
yearinfo,
quarterinfo,
monthinfo,
dayinfo
FROM itcast_dwd.visit_consult_dwd
GROUP BY yearinfo, quarterinfo, monthinfo, dayinfo, from_url;
– 统计某年某季度某月某天某小时某会话来源窗口的访问量
INSERT INTO TABLE itcast_dws.visit_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
‘-1’ AS area,
‘-1’ AS seo_source,
‘-1’ AS origin_channel,
hourinfo,
CONCAT(yearinfo, ‘-’, monthinfo, ‘-’, dayinfo, ’ ', hourinfo) AS time_str,
from_url,
‘4’ AS groupType,
‘1’ AS time_type,
yearinfo,
quarterinfo,
monthinfo,
dayinfo
FROM itcast_dwd.visit_consult_dwd
GROUP BY yearinfo, quarterinfo, monthinfo, dayinfo, hourinfo, from_url;
咨询量
CREATE TABLE IF NOT EXISTS itcast_dws.consult_dws
(
sid_total INT COMMENT ‘根据sid去重求count’,
sessionid_total INT COMMENT ‘根据sessionid去重求count’,
ip_total INT COMMENT ‘根据IP去重求count’,
area STRING COMMENT ‘区域信息’,
origin_channel STRING COMMENT ‘来源渠道’,
hourinfo STRING COMMENT ‘创建时间，统计至小时’,
time_str STRING COMMENT ‘时间明细’,
groupType STRING COMMENT ‘产品属性类型：1.地区；2.来源渠道’,
time_type STRING COMMENT ‘时间聚合类型：1、按小时聚合；2、按天聚合；3、按月聚合；4、按季度聚合；5、按年聚合；’
)
COMMENT ‘咨询量DWS宽表’
PARTITIONED BY (yearinfo string,quarterinfo string,monthinfo STRING, dayinfo string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’
STORED AS ORC
LOCATION ‘/user/hive/warehouse/itcast_dws.db/consult_dws’
TBLPROPERTIES (‘orc.compress’=‘SNAPPY’);
– 纯时间维度的咨询量
–动态分区配置
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
–hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
–写入时ORC压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;
– 统计某年的咨询量
INSERT INTO TABLE itcast_dws.consult_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
‘-1’ AS area,
‘-1’ AS origin_channel,
‘-1’ AS hourinfo,
yearinfo AS time_str,
‘3’ AS groupType,
‘5’ AS time_type,
yearinfo,
‘-1’ AS quarterinfo,
‘-1’ AS monthinfo,
‘-1’ AS dayinfo
FROM itcast_dwd.visit_consult_dwd
WHERE msg_count > 0
GROUP BY yearinfo;
– 统计某年某季度咨询量
INSERT INTO TABLE itcast_dws.consult_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
‘-1’ AS area,
‘-1’ AS origin_channel,
‘-1’ AS hourinfo,
CONCAT(yearinfo, ‘-Q’, quarterinfo) AS time_str,
‘3’ AS groupType,
‘4’ AS time_type,
yearinfo,
quarterinfo,
‘-1’ AS monthinfo,
‘-1’ AS dayinfo
FROM itcast_dwd.visit_consult_dwd
WHERE msg_count > 0
GROUP BY yearinfo, quarterinfo;
– 统计某年 某季度 某月 咨询量
INSERT INTO TABLE itcast_dws.consult_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
‘-1’ AS area,
‘-1’ AS origin_channel,
‘-1’ AS hourinfo,
CONCAT(yearinfo, ‘-’, monthinfo) AS time_str,
‘3’ AS groupType,
‘3’ AS time_type,
yearinfo,
quarterinfo,
monthinfo,
‘-1’ AS dayinfo
FROM itcast_dwd.visit_consult_dwd
WHERE msg_count > 0
GROUP BY yearinfo, quarterinfo, monthinfo;
– 统计某年 某季度 某月 某日 咨询量
INSERT INTO TABLE itcast_dws.consult_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
‘-1’ AS area,
‘-1’ AS origin_channel,
‘-1’ AS hourinfo,
CONCAT(yearinfo, ‘-’, monthinfo, ‘-’, dayinfo) AS time_str,
‘3’ AS groupType,
‘2’ AS time_type,
yearinfo,
quarterinfo,
monthinfo,
dayinfo
FROM itcast_dwd.visit_consult_dwd
WHERE msg_count > 0
GROUP BY yearinfo, quarterinfo, monthinfo, dayinfo;
– 统计某年 某季度 某月 某日 某小时 咨询量
INSERT INTO TABLE itcast_dws.consult_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
‘-1’ AS area,
‘-1’ AS origin_channel,
hourinfo,
CONCAT(yearinfo, ‘-’, monthinfo, ‘-’, dayinfo, ’ ', hourinfo) AS time_str,
‘3’ AS groupType,
‘1’ AS time_type,
yearinfo,
quarterinfo,
monthinfo,
dayinfo
FROM itcast_dwd.visit_consult_dwd
WHERE msg_count > 0
GROUP BY yearinfo, quarterinfo, monthinfo, dayinfo, hourinfo;
– 按照时间维度和地区聚合
– 略
– 统计 某年 某地区 的咨询量
INSERT INTO TABLE itcast_dws.consult_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
area,
‘-1’ AS origin_channel,
‘-1’ AS hourinfo,
yearinfo AS time_str,
‘1’ AS groupType,
‘5’ AS time_type,
yearinfo,
‘-1’ AS quarterinfo,
‘-1’ AS monthinfo,
‘-1’ AS dayinfo
FROM itcast_dwd.visit_consult_dwd
WHERE msg_count > 0
GROUP BY yearinfo, area;
– 统计 某年 某季度 某地区 咨询量
INSERT INTO TABLE itcast_dws.consult_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
area,
‘-1’ AS origin_channel,
‘-1’ AS hourinfo,
CONCAT(yearinfo, ‘-Q’, quarterinfo) AS time_str,
‘1’ AS groupType,
‘4’ AS time_type,
yearinfo,
quarterinfo,
‘-1’ AS monthinfo,
‘-1’ AS dayinfo
FROM itcast_dwd.visit_consult_dwd
WHERE msg_count > 0
GROUP BY yearinfo, quarterinfo, area;
– 统计某年 某季度 某月 某地区 咨询量
INSERT INTO TABLE itcast_dws.consult_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
area,
‘-1’ AS origin_channel,
‘-1’ AS hourinfo,
CONCAT(yearinfo, ‘-’, monthinfo) AS time_str,
‘1’ AS groupType,
‘3’ AS time_type,
yearinfo,
quarterinfo,
monthinfo,
‘-1’ AS dayinfo
FROM itcast_dwd.visit_consult_dwd
WHERE msg_count > 0
GROUP BY yearinfo, quarterinfo, monthinfo, area;
– 统计某年 某季度 某月 某日 某地区 咨询量
INSERT INTO TABLE itcast_dws.consult_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
area,
‘-1’ AS origin_channel,
‘-1’ AS hourinfo,
CONCAT(yearinfo, ‘-’, monthinfo, ‘-’, dayinfo) AS time_str,
‘1’ AS groupType,
‘2’ AS time_type,
yearinfo,
quarterinfo,
monthinfo,
dayinfo
FROM itcast_dwd.visit_consult_dwd
WHERE msg_count > 0
GROUP BY yearinfo, quarterinfo, monthinfo, dayinfo, area;
– 统计某年 某季度 某月 某日 某小时 某地区 咨询量
INSERT INTO TABLE itcast_dws.consult_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
area,
‘-1’ AS origin_channel,
hourinfo,
CONCAT(yearinfo, ‘-’, monthinfo, ‘-’, dayinfo, ’ ', hourinfo) AS time_str,
‘1’ AS groupType,
‘1’ AS time_type,
yearinfo,
quarterinfo,
monthinfo,
dayinfo
FROM itcast_dwd.visit_consult_dwd
WHERE msg_count > 0
GROUP BY yearinfo, quarterinfo, monthinfo, dayinfo, hourinfo, area;
– 统计 某年 某来源渠道 的咨询量
INSERT INTO TABLE itcast_dws.consult_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
‘-1’ AS area,
origin_channel,
‘-1’ AS hourinfo,
yearinfo AS time_str,
‘2’ AS groupType,
‘5’ AS time_type,
yearinfo,
‘-1’ AS quarterinfo,
‘-1’ AS monthinfo,
‘-1’ AS dayinfo
FROM itcast_dwd.visit_consult_dwd
WHERE msg_count > 0
GROUP BY yearinfo, origin_channel;
– 统计 某年 某季度 某来源渠道 咨询量
INSERT INTO TABLE itcast_dws.consult_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
‘-1’ AS area,
origin_channel,
‘-1’ AS hourinfo,
CONCAT(yearinfo, ‘-Q’, quarterinfo) AS time_str,
‘2’ AS groupType,
‘4’ AS time_type,
yearinfo,
quarterinfo,
‘-1’ AS monthinfo,
‘-1’ AS dayinfo
FROM itcast_dwd.visit_consult_dwd
WHERE msg_count > 0
GROUP BY yearinfo, quarterinfo, origin_channel;
– 统计某年 某季度 某月 某来源渠道 咨询量
INSERT INTO TABLE itcast_dws.consult_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
‘-1’ AS area,
origin_channel,
‘-1’ AS hourinfo,
CONCAT(yearinfo, ‘-’, monthinfo) AS time_str,
‘2’ AS groupType,
‘3’ AS time_type,
yearinfo,
quarterinfo,
monthinfo,
‘-1’ AS dayinfo
FROM itcast_dwd.visit_consult_dwd
WHERE msg_count > 0
GROUP BY yearinfo, quarterinfo, monthinfo, origin_channel;
– 统计某年 某季度 某月 某日 某来源渠道 咨询量
INSERT INTO TABLE itcast_dws.consult_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
‘-1’ AS area,
origin_channel,
‘-1’ AS hourinfo,
CONCAT(yearinfo, ‘-’, monthinfo, ‘-’, dayinfo) AS time_str,
‘2’ AS groupType,
‘2’ AS time_type,
yearinfo,
quarterinfo,
monthinfo,
dayinfo
FROM itcast_dwd.visit_consult_dwd
WHERE msg_count > 0
GROUP BY yearinfo, quarterinfo, monthinfo, dayinfo, origin_channel;
– 统计某年 某季度 某月 某日 某小时 某来源渠道 咨询量
INSERT INTO TABLE itcast_dws.consult_dws PARTITION(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
COUNT(DISTINCT sid) AS sid_total,
COUNT(DISTINCT session_id) AS sessionid_total,
COUNT(DISTINCT ip) AS ip_total,
‘-1’ AS area,
origin_channel,
hourinfo,
CONCAT(yearinfo, ‘-’, monthinfo, ‘-’, dayinfo, ’ ', hourinfo) AS time_str,
‘2’ AS groupType,
‘1’ AS time_type,
yearinfo,
quarterinfo,
monthinfo,
dayinfo
FROM itcast_dwd.visit_consult_dwd
WHERE msg_count > 0
GROUP BY yearinfo, quarterinfo, monthinfo, dayinfo, hourinfo, origin_channel;
–导出数据
1.创建数据库
create database scrm_bi default character set utf8mb4 collate utf8mb4_general_ci;
2.创建表
CREATE TABLE itcast_visit (
sid_total int(11) COMMENT ‘根据sid去重求count’,
sessionid_total int(11) COMMENT ‘根据sessionid去重求count’,
ip_total int(11) COMMENT ‘根据IP去重求count’,
area varchar(32) COMMENT ‘区域信息’,
seo_source varchar(32) COMMENT ‘搜索来源’,
origin_channel varchar(32) COMMENT ‘来源渠道’,
hourinfo varchar(32) COMMENT ‘小时信息’,
quarterinfo varchar(32) COMMENT ‘季度’,
time_str varchar(32) COMMENT ‘时间明细’,
from_url varchar(2083) comment ‘会话来源页面’,
groupType varchar(32) COMMENT ‘产品属性类型：1.地区；2.搜索来源；3.来源渠道；4.会话来源页面’,
time_type varchar(32) COMMENT ‘时间聚合类型：1、按小时聚合；2、按天聚合；3、按月聚合；4、按季度聚合；5、按年聚合；’,
yearinfo varchar(32) COMMENT ‘年信息’,
monthinfo varchar(32) COMMENT ‘月信息’,
dayinfo varchar(32) COMMENT ‘日信息’
)ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
3.hive导出到mysql
sqoop export
–connect “jdbc:mysql://192.168.10.150:3306/scrm_bi?useUnicode=true&characterEncoding=utf-8”
–username root
–password ‘123456’
–table itcast_visit
–hcatalog-database itcast_dws
–hcatalog-table visit_dws
-m 10
创建表
CREATE TABLE itcast_consult (
sid_total int(11) COMMENT ‘去重并聚合sid’,
sessionid_total int(11) COMMENT ‘去重并聚合sessionid’,
ip_total int(11) COMMENT ‘去重并聚合ip’,
area varchar(32) COMMENT ‘区域信息’,
origin_channel varchar(32) COMMENT ‘来源渠道’,
hourinfo varchar(5) COMMENT ‘创建时间，统计至小时’,
quarterinfo varchar(5) COMMENT ‘季度’,
time_str varchar(32) COMMENT ‘时间明细’,
groupType varchar(5) COMMENT ‘产品属性类型：1.地区；2.来源渠道’,
time_type varchar(5) COMMENT ‘时间聚合类型：1、按小时聚合；2、按天聚合；3、按月聚合；4、按季度聚合；5、按年聚合；’,
yearinfo varchar(5) COMMENT ‘创建时间，统计至年’,
monthinfo varchar(5) COMMENT ‘创建时间，统计至月’,
dayinfo varchar(5) COMMENT ‘创建时间，统计至天’
) COMMENT=‘客户咨询统计数据’;
hive–>mysql
sqoop export
–connect “jdbc:mysql://192.168.10.150:3306/scrm_bi?useUnicode=true&characterEncoding=utf-8”
–username root
–password 123456
–table itcast_consult
–hcatalog-database itcast_dws
–hcatalog-table consult_dws
-m 10
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
