CREATE TABLE IF NOT EXISTS ods_log_tracker(
    ip STRING COMMENT 'client ip address',
    user STRING,
    accesstime string,
    method STRING COMMENT 'HTTP request type, such as GET POST...',
    url STRING,
    protocol STRING,
    status BIGINT COMMENT 'HTTP reponse code from server',
    byte_cnt BIGINT,
    referer STRING,
    agent STRING)
PARTITIONED BY(dt STRING);

CREATE TABLE IF NOT EXISTS dw_log_detail(
    ip STRING COMMENT 'client ip address',
    accesstime string,
    method STRING COMMENT 'HTTP request type, such as GET POST...',
    url STRING,
    protocol STRING,
    status BIGINT COMMENT 'HTTP reponse code from server',
    byte_cnt BIGINT,
    referer STRING COMMENT 'referer domain',
    agent STRING,
    device STRING COMMENT 'android|iphone|ipad...',
    identity STRING  COMMENT 'identify: user, crawler, feed')
PARTITIONED BY(dt STRING);

CREATE TABLE IF NOT EXISTS dim_user_info(
    uid STRING COMMENT 'unique user id',
    ip STRING COMMENT 'client ip address',
	city string comment 'city',
	protocol STRING,
    device STRING,
    identity STRING  COMMENT 'user, crawler, feed',
    agent STRING)
PARTITIONED BY(dt STRING);

CREATE TABLE IF NOT EXISTS dw_log_fact(
    uid STRING COMMENT 'unique user id',
    accesstime string,
    method STRING COMMENT 'HTTP request type, such as GET POST...',
    url STRING,
    status BIGINT COMMENT 'HTTP reponse code from server',
    byte_cnt BIGINT,
    referer STRING)
PARTITIONED BY(dt STRING);

CREATE TABLE IF NOT EXISTS adm_user_measures(
    device STRING COMMENT 'such as android, iphone, ipad...',
    pv BIGINT,
    uv BIGINT)
PARTITIONED BY(dt STRING);

CREATE TABLE adm_refer_info(
    referer STRING,
    referer_count BIGINT)
PARTITIONED BY(dt STRING);

CREATE TABLE adm_user_info(
    city STRING,
    user_count BIGINT)
PARTITIONED BY(dt STRING);

AnalyticDB表创建

操作步骤

步骤1      进入阿里云数加AnalyticDB管控台，开通并创建数据库确定。

步骤1      点击操作栏中的进入，进入DMS for AnalyticDB。

步骤2      创建AnalyticDB表组，具体如下：

create tablegroup coolshell_log options(minRedundancy=2 executeTimeout=30000);

步骤3      创建AnalyticDB数据表，DDL语句分别如下。

create tablegroup coolshell_log options(minRedundancy=2 executeTimeout=30000);
CREATE TABLE adm_user_measures(
    device varchar COMMENT 'such as android, iphone, ipad...',
    pv BIGINT,
    uv BIGINT)
PARTITION BY HASH KEY(device)
PARTITION NUM 50
SUBPARTITION BY LIST(dt bigint)
SUBPARTITION OPTIONS (available_partition_num = 30)
tablegroup coolshell_log;

CREATE TABLE adm_refer_info(
    referer varchar,
    referer_count BIGINT)
PARTITION BY HASH KEY(referer)
PARTITION NUM 50
SUBPARTITION BY LIST(dt bigint)
SUBPARTITION OPTIONS (available_partition_num = 30)
tablegroup coolshell_log;

CREATE TABLE adm_user_info(
    city varchar,
    user_count BIGINT)
PARTITION BY HASH KEY(city)
PARTITION NUM 50
SUBPARTITION BY LIST(dt bigint)
SUBPARTITION OPTIONS (available_partition_num = 30)
tablegroup coolshell_log;

新建ODPS SQL任务

---adm_refer_info中的处理逻辑---
INSERT OVERWRITE TABLE adm_refer_info PARTITION (dt='${bdp.system.bizdate}')
SELECT referer
	, COUNT(*) AS referer_cnt
FROM dw_log_fact
WHERE LENGTH(referer) > 1
	AND dt = '${bdp.system.bizdate}'
GROUP BY referer;

--adm_user_measures中的处理逻辑---
INSERT OVERWRITE TABLE adm_user_measures PARTITION (dt='${bdp.system.bizdate}')
SELECT u.device
	, COUNT(*) AS pv
	, COUNT(DISTINCT u.uid) AS uv
FROM dw_log_fact f
JOIN dim_user_info u
ON f.uid = u.uid
	AND u.identity = 'user'
	AND f.dt = '${bdp.system.bizdate}'
	AND u.dt = '${bdp.system.bizdate}'
GROUP BY u.device;

--adm_user_info中的处理逻辑—
INSERT OVERWRITE TABLE adm_user_info PARTITION (dt='${bdp.system.bizdate}')
SELECT city
	, COUNT(*) AS user_count
FROM dim_user_info
where dt=${bdp.system.bizdate}
GROUP BY city;

INSERT OVERWRITE TABLE dw_log_fact PARTITION (dt=${bdp.system.bizdate})
SELECT u.uid
	, d.accesstime
	, d.method
	, d.url
	, d.status
	, d.byte_cnt
	, d.referer
FROM dw_log_detail d
JOIN dim_user_info u
ON (d.ip = u.ip
	AND d.protocol = u.protocol
	AND d.agent = u.agent) and d.dt = ${bdp.system.bizdate}	AND u.dt =${bdp.system.bizdate};

创建自定义函数

需要通过自定义函数-Java UDF来处理IP，将IP地址转化为地域region。具体的jar包详见附件。创建自定义函数的具体操作流程详见：https://help.aliyun.com/document_detail/30270.html

配置项说明如下：

数据导出AnalyticDB

经过上述步骤，数据加工逻辑已经可以正常执行，那么需要进行数据导出工作。创建三个同步任务将adm数据集市层的数据导入至分析型数据库中，供后续Quick BI更高效的洞察数据。    

选择数据源为ADS，填写配置信息并测试连通性通过后，点击 确定 保存配置。（其中AccessID和AccessKey都是大数据开发套件对应项目的生产账号）

在MaxCompute console中需要对garuda_build@aliyun.com 与 garuda_data@aliyun.com。如下进行：    

add user ALIYUN$garuda_build@aliyun.com;
add user ALIYUN$garuda_data@aliyun.com;
grant describe,select on table adm_refer_info to user ALIYUN$garuda_build@aliyun.com;
grant describe,select on table adm_refer_info to user ALIYUN$garuda_data@aliyun.com;
grant describe,select on table adm_user_measures to user ALIYUN$garuda_data@aliyun.com;
grant describe,select on table adm_user_measures to user ALIYUN$garuda_build@aliyun.com;
grant describe,select on table adm_user_info to user ALIYUN$garuda_build@aliyun.com;
grant describe,select on table adm_user_info to user ALIYUN$garuda_data@aliyun.com;

创建AnalyticDB数据源

创建数据集

操作步骤

分析数据集

操作步骤

创建图表模板

操作步骤

点击左侧 模板 进入，选择空白图表模板。按照自己需要的布局进行。

针对每个图标可以在右侧进行关联数据集，如来自工作表..等。最终实现的效果如下：