数据仓库项目(第三节)用户数据数仓建设、数仓分析前置知识

1300-黄同学

发表文章数:85

热门标签

,
首页 » 大数据 » 正文

用户数据数仓建设

hive安装参考hadoop离线阶段(第十三节)数据仓库、hive简介、hive安装和hive的三种交互模式

创建ODS层表和数据导入

创建表前,先准备好数据库,create database gmall;use gmall;

创建启动日志表ods_start_log

1、创建表

CREATE EXTERNAL TABLE  `ods_start_log`(`line` string) PARTITIONED BY (`dt` string);

2、将采集到hdfs上的数据导入

load data inpath '/origin_data/gmall/log/topic_start/2019-02-10' 
into table gmall.ods_start_log partition(dt='2019-02-10');

创建事件日志表ods_event_log

1、创建表

CREATE EXTERNAL TABLE  `ods_event_log`(`line` string)
PARTITIONED BY (`dt` string);

2、将采集到hdfs上的数据导入

load data inpath '/origin_data/gmall/log/topic_event/2019-02-10' 
into table gmall.ods_event_log partition(dt='2019-02-10');

ODS层加载数据脚本

编写脚本来实现批量创建表和加载数据

#!/bin/bash

# 定义变量方便修改
APP=gmall
hive=/opt/module/hive/bin/hive

# 如果是输入的日期按照取输入日期;如果没输入日期取当前时间的前一天
if [ -n $1 ] ;then
 log_date=$1
else 
 log_date=`date  -d "-1 day"  +%F`  
fi 

echo "===日志日期为 $log_date==="
$hive -e "load data inpath '/origin_data/gmall/log/topic_start/$log_date' into table "$APP".ods_start_log partition(dt='$log_date')"
$hive -e "load data inpath '/origin_data/gmall/log/topic_event/$log_date' into table "$APP".ods_event_log partition(dt='$log_date')"

创建DWD层表和数据导入

创建表

1、创建启动日志基础明细表

CREATE EXTERNAL TABLE `dwd_base_start_log`(
`mid_id` string,
`user_id` string, 
`version_code` string, 
`version_name` string, 
`lang` string, 
`source` string, 
`os` string, 
`area` string, 
`model` string,
`brand` string, 
`sdk_version` string, 
`gmail` string, 
`height_width` string, 
`app_time` string, 
`network` string, 
`lng` string, 
`lat` string, 
`event_name` string, 
`event_json` string, 
`server_time` string)
PARTITIONED BY (`dt` string);

2、创建事件日志基础明细表

CREATE EXTERNAL TABLE `dwd_base_event_log`(
`mid_id` string,
`user_id` string, 
`version_code` string, 
`version_name` string, 
`lang` string, 
`source` string, 
`os` string, 
`area` string, 
`model` string,
`brand` string, 
`sdk_version` string, 
`gmail` string, 
`height_width` string, 
`app_time` string, 
`network` string, 
`lng` string, 
`lat` string, 
`event_name` string, 
`event_json` string, 
`server_time` string)
PARTITIONED BY (`dt` string);

自定义hive函数

目的:通过自定义UDF函数,将ODS层表中的json字符串解析为字段形式的数据。
ODS层表中的json字符串示例:

1611806690259|
{"cm":
	{"ln":"-117.3",
	"sv":"V2.7.3",
	"os":"8.1.9",
	"g":"2659DRSE@gmail.com",
	"mid":"m9688",
	"nw":"3G",
	"l":"en",
	"vc":"11",
	"hw":"750*1134",
	"ar":"MX",
	"uid":"u4376",
	"t":"1611788158710",
	"la":"-20.7",
	"md":"Huawei-5",
	"vn":"1.3.4",
	"ba":"Huawei",
	"sr":"F"},
"ap":"gmall",
"et":
	[{"ett":"1611721537819",
		"en":"newsdetail",
		"kv":
			{"entry":"3",
			"newsid":"n0802",
			"news_staytime":"18",
			"loading_time":"14",
			"action":"1",
			"showtype":"5",
			"category":"15",
			"type1":"433"}},
	{"ett":"1611776100426",
	"en":"loading",
	"kv":{
		"extend2":"",
		"loading_time":"24",
		"action":"2",
		"extend1":"",
		"type":"2",
		"type1":"542",
		"loading_way":"1"}},
	{"ett":"1611751810436",
	"en":"error",
	"kv":
		{"errorDetail":"at cn.lift.dfdfdf.control.CommandUtil.getInfo(CommandUtil.java:67)//n at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)//n at java.lang.reflect.Method.invoke(Method.java:606)//n","errorBrief":"at cn.lift.dfdf.web.AbstractBaseController.validInbound(AbstractBaseController.java:72)"}},
	{"ett":"1611801954388",
	"en":"comment",
	"kv":
		{"p_comment_id":4,
		"addtime":"1611759886198",
		"praise_count":455,
		"other_id":1,
		"comment_id":7,
		"reply_count":85,
		"userid":5,
		"content":"混酞汞加新砰定种风癌却舅"}},
	{"ett":"1611779650622",
	"en":"praise",
	"kv":
		{"target_id":3,
		"id":8,"type":4,
		"add_time":"1611750601059",
		"userid":2}
	}]
}

1、创建maven项目
pom如下:

<repositories>
    <repository>
        <id>cloudera</id>
        <url>https://repository.cloudera.com/artifactory/cloudera-repos/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>org.apache.hive</groupId>
        <artifactId>hive-exec</artifactId>
        <version>1.1.0-cdh5.14.0</version>
    </dependency>
</dependencies>
<build>
    <plugins>
        <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-compiler-plugin</artifactId>
            <version>3.0</version>
            <configuration>
                <source>1.8</source>
                <target>1.8</target>
                <encoding>UTF-8</encoding>
            </configuration>
        </plugin>
        <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-shade-plugin</artifactId>
            <version>2.2</version>
            <executions>
                <execution>
                    <phase>package</phase>
                    <goals>
                        <goal>shade</goal>
                    </goals>
                    <configuration>
                        <filters>
                            <filter>
                                <artifact>*:*</artifact>
                                <excludes>
                                    <exclude>META-INF/*.SF</exclude>
                                    <exclude>META-INF/*.DSA</exclude>
                                    <exclude>META-INF/*/RSA</exclude>
                                </excludes>
                            </filter>
                        </filters>
                    </configuration>
                </execution>
            </executions>
        </plugin>
    </plugins>
</build>

2、自定义UDF函数(解析公共字段)
解析json中key为cm的数据,生成公共字段

import org.apache.commons.lang.StringUtils;
import org.apache.hadoop.hive.ql.exec.UDF;
import org.json.JSONException;
import org.json.JSONObject;


public class BaseFieldUDF extends UDF
{
    public String evaluate(String line, String jsonkeysString) {

        // 0 准备一个sb
        StringBuilder sb = new StringBuilder();

        // 1 切割jsonkeys  mid uid vc vn l sr os ar md
        String[] jsonkeys = jsonkeysString.split(",");

        // 2 处理line   服务器时间 | json
        String[] logContents = line.split("//|");

        // 3 合法性校验
        if (logContents.length != 2 || StringUtils.isBlank(logContents[1])) {
            return "";
        }

        // 4 开始处理json
        try {
            JSONObject jsonObject = new JSONObject(logContents[1]);

            // 获取cm里面的对象
            JSONObject base = jsonObject.getJSONObject("cm");

            // 循环遍历取值
            for (int i = 0; i < jsonkeys.length; i++) {
                String filedName = jsonkeys[i].trim();

                if (base.has(filedName)) {
                    sb.append(base.getString(filedName)).append("/t");
                } else {
                    sb.append("").append("/t");
                }
            }

            sb.append(jsonObject.getString("et")).append("/t");
            sb.append(logContents[0]).append("/t");
        } catch (JSONException e) {
            e.printStackTrace();
        }

        return sb.toString();
    }

    //使用main函数主要用于模拟数据测试。
    public static void main(String[] args) {

        String line = "1541217850324|{/"cm/":{/"mid/":/"m7856/"," +
                "/"uid/":/"u8739/",/"ln/":/"-74.8/",/"sv/":/"V2.2.2/"," +
                "/"os/":/"8.1.3/",/"g/":/"P7XC9126@gmail.com/",/"nw/":/"3G/",/"l/":/"es/"," +
                "/"vc/":/"6/",/"hw/":/"640*960/",/"ar/":/"MX/",/"t/":/"1541204134250/"," +
                "/"la/":/"-31.7/",/"md/":/"huawei-17/",/"vn/":/"1.1.2/",/"sr/":/"O/",/"ba/":/"Huawei/"}," +
                "/"ap/":/"weather/",/"et/":[{/"ett/":/"1541146624055/",/"en/":/"display/"," +
                "/"kv/":{/"newsid/":/"n4195/",/"copyright/":/"ESPN/",/"content_provider/":/"CNN/"," +
                "/"extend2/":/"5/",/"action/":/"2/",/"extend1/":/"2/",/"place/":/"3/"," +
                "/"showtype/":/"2/",/"category/":/"72/",/"newstype/":/"5/"}},{/"ett/":/"1541213331817/"," +
                "/"en/":/"loading/",/"kv/":{/"extend2/":/"/",/"loading_time/":/"15/",/"action/":/"3/"," +
                "/"extend1/":/"/",/"type1/":/"/",/"type/":/"3/",/"loading_way/":/"1/"}}," +
                "{/"ett/":/"1541126195645/",/"en/":/"ad/",/"kv/":{/"entry/":/"3/",/"show_style/":/"0/"," +
                "/"action/":/"2/",/"detail/":/"325/",/"source/":/"4/",/"behavior/":/"2/",/"content/":/"1/"," +
                "/"newstype/":/"5/"}},{/"ett/":/"1541202678812/",/"en/":/"notification/"," +
                "/"kv/":{/"ap_time/":/"1541184614380/",/"action/":/"3/",/"type/":/"4/",/"content/":/"/"}}," +
                "{/"ett/":/"1541194686688/",/"en/":/"active_background/",/"kv/":{/"active_source/":/"3/"}}]}";
        String x = new BaseFieldUDF().evaluate(line,
                "mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,nw,ln,la,t");
        System.out.println(x);
    }

}

3、自定义UDTF函数(解析具体事件字段)
解析json字符串中的en。
函数构思如下图:
数据仓库项目(第三节)用户数据数仓建设、数仓分析前置知识

import org.apache.commons.lang.StringUtils;
import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;
import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
import org.json.JSONArray;
import org.json.JSONException;

import java.util.ArrayList;

public class EventJsonUDTF extends GenericUDTF {

    //该方法中,我们将指定输出参数的名称和参数类型:
    @Override
    public StructObjectInspector initialize(ObjectInspector[] argOIs) throws UDFArgumentException {

        ArrayList<String> fieldNames = new ArrayList<String>();
        ArrayList<ObjectInspector> fieldOIs = new ArrayList<ObjectInspector>();

        fieldNames.add("event_name");
        fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
        fieldNames.add("event_json");
        fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);

        return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames, fieldOIs);
    }

    //输入1条记录,输出若干条结果
    @Override
    public void process(Object[] objects) throws HiveException {

        // 获取传入的et
        String input = objects[0].toString();

        // 如果传进来的数据为空,直接返回过滤掉该数据
        if (StringUtils.isBlank(input)) {
            return;
        } else {

            try {
                // 获取一共有几个事件(ad/facoriters)
                JSONArray ja = new JSONArray(input);

                if (ja == null)
                    return;

                // 循环遍历每一个事件
                for (int i = 0; i < ja.length(); i++) {
                    String[] result = new String[2];

                    try {
                        // 取出每个的事件名称(ad/facoriters)
                        result[0] = ja.getJSONObject(i).getString("en");

                        // 取出每一个事件整体
                        result[1] = ja.getString(i);
                    } catch (JSONException e) {
                        continue;
                    }

                    // 将结果返回
                    forward(result);
                }
            } catch (JSONException e) {
                e.printStackTrace();
            }
        }
    }

    //当没有记录处理的时候该方法会被调用,用来清理代码或者产生额外的输出
    @Override
    public void close() throws HiveException {

    }
}

代码编写完成后,将项目打包,将不含依赖的包发送到hive的lib目录下。
4、将自定义hive函数加入到hive中

hive (gmall)> add jar /opt/module/hive/lib/hivefunction-1.0-SNAPSHOT.jar;
hive (gmall)> 
create temporary function base_analizer as 'com.atguigu.udf.BaseFieldUDF';

create temporary function flat_analizer as 'com.atguigu.udtf.EventJsonUDTF';

导入DWD层的数据

首先在hive启用动态插入分区数据:

#一般来说,只需要第二句即可
set hive.exec.dynamici.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

如果不打开动态插入分区数据,那么在导入数据时手动指定分区也可以。

1、导入启动日志基础明细表的数据

insert overwrite table dwd_base_start_log 
PARTITION (dt)
select
mid_id,
user_id,
version_code,
version_name,
lang,
source ,
os ,
area ,
model ,
brand ,
sdk_version ,
gmail ,
height_width ,
app_time ,
network ,
lng ,
lat ,
event_name , 
event_json , 
server_time , 
dt  
 from
(
select
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[0]   as mid_id,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[1]   as user_id,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[2]   as version_code,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[3]   as version_name,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[4]   as lang,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[5]   as source,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[6]   as os,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[7]   as area,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[8]   as model,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[9]   as brand,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[10]   as sdk_version,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[11]  as gmail,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[12]  as height_width,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[13]  as app_time,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[14]  as network,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[15]  as lng,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[16]  as lat,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[17]  as ops,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[18]  as server_time,
dt 
from ods_start_log where dt='2019-02-10'  and base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la')<>'' 
) sdk_log lateral view flat_analizer(ops) tmp_k as event_name, event_json;

2、导入事件日志基础明细表的数据

insert overwrite table dwd_base_event_log 
PARTITION (dt)
select
mid_id,
user_id,
version_code,
version_name,
lang,
source ,
os ,
area ,
model ,
brand ,
sdk_version ,
gmail ,
height_width ,
app_time ,
network ,
lng ,
lat ,
event_name , 
event_json , 
server_time , 
dt  
 from
(
select
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[0]   as mid_id,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[1]   as user_id,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[2]   as version_code,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[3]   as version_name,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[4]   as lang,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[5]   as source,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[6]   as os,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[7]   as area,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[8]   as model,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[9]   as brand,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[10]   as sdk_version,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[11]  as gmail,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[12]  as height_width,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[13]  as app_time,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[14]  as network,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[15]  as lng,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[16]  as lat,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[17]  as ops,
split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'/t')[18]  as server_time,
dt 
from ods_event_log where dt='2019-02-10'  and base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la')<>'' 
) sdk_log lateral view flat_analizer(ops) tmp_k as event_name, event_json;

DWD层子表

下面多处会用到hive内置函数get_json_object,具体用法参考【Hive】解析json(get_json_object)【转载】
1、商品点击表

CREATE EXTERNAL TABLE `dwd_display_log`(
`mid_id` string,
`user_id` string, 
`version_code` string, 
`version_name` string, 
`lang` string, 
`source` string, 
`os` string, 
`area` string, 
`model` string,
`brand` string, 
`sdk_version` string, 
`gmail` string, 
`height_width` string, 
`app_time` string, 
`network` string, 
`lng` string, 
`lat` string, 
action string,
newsid string,
place string,
extend1 string,
category string,
`server_time` string
)
PARTITIONED BY (dt string);


insert overwrite table dwd_display_log
PARTITION (dt)
select 
mid_id,
user_id,
version_code,
version_name,
lang,
source,
os,
area,
model,
brand,
sdk_version,
gmail,
height_width,
app_time,
network,
lng,
lat,
get_json_object(event_json,'$.kv.action') action,
get_json_object(event_json,'$.kv.newsid') newsid,
get_json_object(event_json,'$.kv.place') place,
get_json_object(event_json,'$.kv.extend1') extend1,
get_json_object(event_json,'$.kv.category') category,
server_time,
dt
from dwd_base_event_log 
where dt='2019-02-10' and event_name='display';

2、商品详情页表

CREATE EXTERNAL TABLE `dwd_newsdetail_log`(
`mid_id` string,
`user_id` string, 
`version_code` string, 
`version_name` string, 
`lang` string, 
`source` string, 
`os` string, 
`area` string, 
`model` string,
`brand` string, 
`sdk_version` string, 
`gmail` string, 
`height_width` string, 
`app_time` string,  
`network` string, 
`lng` string, 
`lat` string, 
entry string,
action string,
newsid string,
showtype string,
news_staytime string,
loading_time string,
type1 string,
category string,
`server_time` string)
PARTITIONED BY (dt string);


insert overwrite table dwd_newsdetail_log
PARTITION (dt)
select 
mid_id,
user_id,
version_code,
version_name,
lang,
source,
os,
area,
model,
brand,
sdk_version,
gmail,
height_width,
app_time,
network,
lng,
lat,
get_json_object(event_json,'$.kv.entry') entry,
get_json_object(event_json,'$.kv.action') action,
get_json_object(event_json,'$.kv.newsid') newsid,
get_json_object(event_json,'$.kv.showtype') showtype,
get_json_object(event_json,'$.kv.news_staytime') news_staytime,
get_json_object(event_json,'$.kv.loading_time') loading_time,
get_json_object(event_json,'$.kv.type1') type1,
get_json_object(event_json,'$.kv.category') category,
server_time,
dt
from dwd_base_event_log 
where dt='2019-02-10' and event_name='newsdetail';

3、商品列表页表

CREATE EXTERNAL TABLE `dwd_loading_log`(
`mid_id` string,
`user_id` string, 
`version_code` string, 
`version_name` string, 
`lang` string, 
`source` string, 
`os` string, 
`area` string, 
`model` string,
`brand` string, 
`sdk_version` string, 
`gmail` string,
`height_width` string,  
`app_time` string,
`network` string, 
`lng` string, 
`lat` string, 
action string,
loading_time string,
loading_way string,
extend1 string,
extend2 string,
type string,
type1 string,
`server_time` string)
PARTITIONED BY (dt string);


insert overwrite table dwd_loading_log
PARTITION (dt)
select 
mid_id,
user_id,
version_code,
version_name,
lang,
source,
os,
area,
model,
brand,
sdk_version,
gmail,
height_width,
app_time,
network,
lng,
lat,
get_json_object(event_json,'$.kv.action') action,
get_json_object(event_json,'$.kv.loading_time') loading_time,
get_json_object(event_json,'$.kv.loading_way') loading_way,
get_json_object(event_json,'$.kv.extend1') extend1,
get_json_object(event_json,'$.kv.extend2') extend2,
get_json_object(event_json,'$.kv.type') type,
get_json_object(event_json,'$.kv.type1') type1,
server_time,
dt
from dwd_base_event_log 
where dt='2019-02-10' and event_name='loading';

4、广告表

CREATE EXTERNAL TABLE `dwd_ad_log`(
`mid_id` string,
`user_id` string, 
`version_code` string, 
`version_name` string, 
`lang` string, 
`source` string, 
`os` string, 
`area` string, 
`model` string,
`brand` string, 
`sdk_version` string, 
`gmail` string, 
`height_width` string,  
`app_time` string,
`network` string, 
`lng` string, 
`lat` string, 
entry string,
action string,
content string,
detail string,
ad_source string,
behavior string,
newstype string,
show_style string,
`server_time` string)
PARTITIONED BY (dt string);


insert overwrite table dwd_ad_log
PARTITION (dt)
select 
mid_id,
user_id,
version_code,
version_name,
lang,
source,
os,
area,
model,
brand,
sdk_version,
gmail,
height_width,
app_time,
network,
lng,
lat,
get_json_object(event_json,'$.kv.entry') entry,
get_json_object(event_json,'$.kv.action') action,
get_json_object(event_json,'$.kv.content') content,
get_json_object(event_json,'$.kv.detail') detail,
get_json_object(event_json,'$.kv.source') ad_source,
get_json_object(event_json,'$.kv.behavior') behavior,
get_json_object(event_json,'$.kv.newstype') newstype,
get_json_object(event_json,'$.kv.show_style') show_style,
server_time,
dt
from dwd_base_event_log 
where dt='2019-02-10' and event_name='ad';

5、消息通知表

CREATE EXTERNAL TABLE `dwd_notification_log`(
`mid_id` string,
`user_id` string, 
`version_code` string, 
`version_name` string, 
`lang` string,
`source` string, 
`os` string, 
`area` string, 
`model` string,
`brand` string, 
`sdk_version` string, 
`gmail` string, 
`height_width` string,  
`app_time` string,
`network` string, 
`lng` string, 
`lat` string, 
action string,
noti_type string,
ap_time string,
content string,
`server_time` string
)
PARTITIONED BY (dt string);


insert overwrite table dwd_notification_log
PARTITION (dt)
select 
mid_id,
user_id,
version_code,
version_name,
lang,
source,
os,
area,
model,
brand,
sdk_version,
gmail,
height_width,
app_time,
network,
lng,
lat,
get_json_object(event_json,'$.kv.action') action,
get_json_object(event_json,'$.kv.noti_type') noti_type,
get_json_object(event_json,'$.kv.ap_time') ap_time,
get_json_object(event_json,'$.kv.content') content,
server_time,
dt
from dwd_base_event_log 
where dt='2019-02-10' and event_name='notification';

6、用户前台活跃表

CREATE EXTERNAL TABLE `dwd_active_foreground_log`(
`mid_id` string,
`user_id` string, 
`version_code` string,
`version_name` string, 
`lang` string, 
`source` string, 
`os` string, 
`area` string, 
`model` string,
`brand` string, 
`sdk_version` string, 
`gmail` string, 
`height_width` string,
`app_time` string, 
`network` string, 
`lng` string, 
`lat` string, 
active_source string,
`server_time` string)
PARTITIONED BY (dt string);

insert overwrite table dwd_active_foreground_log
PARTITION (dt)
select 
mid_id,
user_id,
version_code,
version_name,
lang,
source,
os,
area,
model,
brand,
sdk_version,
gmail,
height_width,
app_time,
network,
lng,
lat,
get_json_object(event_json,'$.kv.active_source') active_source,
server_time,
dt
from dwd_base_event_log 
where dt='2019-02-10' and event_name='active_foreground';

7、用户后台活跃表

CREATE EXTERNAL TABLE `dwd_active_background_log`(
`mid_id` string,
`user_id` string, 
`version_code` string, 
`version_name` string, 
`lang` string, 
`source` string, 
`os` string, 
`area` string, 
`model` string,
`brand` string, 
`sdk_version` string, 
`gmail` string,
 `height_width` string,  
`app_time` string,
`network` string, 
`lng` string, 
`lat` string, 
active_source string,
`server_time` string
)
PARTITIONED BY (dt string);


insert overwrite table dwd_active_background_log
PARTITION (dt)
select 
mid_id,
user_id,
version_code,
version_name,
lang,
source,
os,
area,
model,
brand,
sdk_version,
gmail,
height_width,
app_time,
network,
lng,
lat,
get_json_object(event_json,'$.kv.active_source') active_source,
server_time,
dt
from dwd_base_event_log 
where dt='2019-02-10' and event_name='active_background';

8、评论表

CREATE EXTERNAL TABLE `dwd_comment_log`(
`mid_id` string,
`user_id` string, 
`version_code` string, 
`version_name` string, 
`lang` string, 
`source` string, 
`os` string, 
`area` string, 
`model` string,
`brand` string, 
`sdk_version` string, 
`gmail` string, 
`height_width` string,  
`app_time` string,
`network` string, 
`lng` string, 
`lat` string, 
comment_id int, 
userid int, 
p_comment_id int, 
content string, 
addtime string, 
other_id int, 
praise_count int,
reply_count int,
`server_time` string
)
PARTITIONED BY (dt string);


insert overwrite table dwd_comment_log
PARTITION (dt)
select 
mid_id,
user_id,
version_code,
version_name,
lang,
source,
os,
area,
model,
brand,
sdk_version,
gmail,
height_width,
app_time,
network,
lng,
lat,
get_json_object(event_json,'$.kv.comment_id') comment_id,
get_json_object(event_json,'$.kv.userid') userid,
get_json_object(event_json,'$.kv.p_comment_id') p_comment_id,
get_json_object(event_json,'$.kv.content') content,
get_json_object(event_json,'$.kv.addtime') addtime,
get_json_object(event_json,'$.kv.other_id') other_id,
get_json_object(event_json,'$.kv.praise_count') praise_count,
get_json_object(event_json,'$.kv.reply_count') reply_count,
server_time,
dt
from dwd_base_event_log 
where dt='2019-02-10' and event_name='comment';

9、收藏表

CREATE EXTERNAL TABLE `dwd_favorites_log`(
`mid_id` string,
`user_id` string, 
`version_code` string, 
`version_name` string, 
`lang` string, 
`source` string, 
`os` string, 
`area` string, 
`model` string,
`brand` string, 
`sdk_version` string, 
`gmail` string, 
`height_width` string,  
`app_time` string,
`network` string, 
`lng` string, 
`lat` string, 
id int, 
course_id int, 
userid int,
add_time string,
`server_time` string
)
PARTITIONED BY (dt string);


insert overwrite table dwd_favorites_log
PARTITION (dt)
select 
mid_id,
user_id,
version_code,
version_name,
lang,
source,
os,
area,
model,
brand,
sdk_version,
gmail,
height_width,
app_time,
network,
lng,
lat,
get_json_object(event_json,'$.kv.id') id,
get_json_object(event_json,'$.kv.course_id') course_id,
get_json_object(event_json,'$.kv.userid') userid,
get_json_object(event_json,'$.kv.add_time') add_time,
server_time,
dt
from dwd_base_event_log 
where dt='2019-02-10' and event_name='favorites';

10、点赞表

CREATE EXTERNAL TABLE `dwd_praise_log`(
`mid_id` string,
`user_id` string, 
`version_code` string, 
`version_name` string, 
`lang` string, 
`source` string, 
`os` string, 
`area` string, 
`model` string,
`brand` string, 
`sdk_version` string, 
`gmail` string, 
`height_width` string,  
`app_time` string,
`network` string, 
`lng` string, 
`lat` string, 
id string, 
userid string, 
target_id string,
type string,
add_time string,
`server_time` string
)
PARTITIONED BY (dt string);


insert overwrite table dwd_praise_log
PARTITION (dt)
select 
mid_id,
user_id,
version_code,
version_name,
lang,
source,
os,
area,
model,
brand,
sdk_version,
gmail,
height_width,
app_time,
network,
lng,
lat,
get_json_object(event_json,'$.kv.id') id,
get_json_object(event_json,'$.kv.userid') userid,
get_json_object(event_json,'$.kv.target_id') target_id,
get_json_object(event_json,'$.kv.type') type,
get_json_object(event_json,'$.kv.add_time') add_time,
server_time,
dt
from dwd_base_event_log 
where dt='2019-02-10' and event_name='praise';

11、启动日志表

CREATE EXTERNAL TABLE `dwd_start_log`(
`mid_id` string,
`user_id` string, 
`version_code` string, 
`version_name` string, 
`lang` string, 
`source` string, 
`os` string, 
`area` string, 
`model` string,
`brand` string, 
`sdk_version` string, 
`gmail` string, 
`height_width` string,  
`app_time` string,
`network` string, 
`lng` string, 
`lat` string, 
entry string, 
open_ad_type string, 
action string, 
loading_time string, 
detail string, 
extend1 string, 
`server_time` string
)
PARTITIONED BY (dt string);


insert overwrite table dwd_start_log
PARTITION (dt)
select 
mid_id,
user_id,
version_code,
version_name,
lang,
source,
os,
area,
model,
brand,
sdk_version,
gmail,
height_width,
app_time,
network,
lng,
lat,
get_json_object(event_json,'$.kv.entry') entry,
get_json_object(event_json,'$.kv.open_ad_type') open_ad_type,
get_json_object(event_json,'$.kv.action') action,
get_json_object(event_json,'$.kv.loading_time') loading_time,
get_json_object(event_json,'$.kv.detail') detail,
get_json_object(event_json,'$.kv.extend1') extend1,
server_time,
dt
from dwd_base_start_log 
where dt='2019-02-10' and event_name='start';

12、错误日志表

CREATE EXTERNAL TABLE `dwd_error_log`(
`mid_id` string,
`user_id` string, 
`version_code` string, 
`version_name` string, 
`lang` string, 
`source` string, 
`os` string, 
`area` string, 
`model` string,
`brand` string, 
`sdk_version` string, 
`gmail` string, 
`height_width` string,  
`app_time` string,
`network` string, 
`lng` string, 
`lat` string, 
errorBrief string, 
errorDetail string, 
`server_time` string)
PARTITIONED BY (dt string);


insert overwrite table dwd_error_log
PARTITION (dt)
select 
mid_id,
user_id,
version_code,
version_name,
lang,
source,
os,
area,
model,
brand,
sdk_version,
gmail,
height_width,
app_time,
network,
lng,
lat,
get_json_object(event_json,'$.kv.errorBrief') errorBrief,
get_json_object(event_json,'$.kv.errorDetail') errorDetail,
server_time,
dt
from dwd_base_event_log 
where dt='2019-02-10' and event_name='error';

DWD层加载数据脚本

由于DWD层表数量很大,可以使用脚本来批量导入数据,如此可以省去每天更新数据时的大量重复操作,脚本如下:

#!/bin/bash

# 定义变量方便修改
APP=gmall
hive=/opt/module/hive/bin/hive

# 如果是输入的日期按照取输入日期;如果没输入日期取当前时间的前一天
if [ -n $1 ] ;then
	log_date=$1
else 
	log_date=`date  -d "-1 day"  +%F`  
fi 
sql="

set hive.exec.dynamic.partition.mode=nonstrict;

insert overwrite table "$APP".dwd_display_log
PARTITION (dt)
select 
	mid_id,
	user_id,
	version_code,
	version_name,
	lang,
	source,
	os,
	area,
	model,
	brand,
	sdk_version,
	gmail,
	height_width,
	app_time,
	network,
	lng,
	lat,
	get_json_object(event_json,'$.kv.action') action,
	get_json_object(event_json,'$.kv.newsid') newsid,
	get_json_object(event_json,'$.kv.place') place,
	get_json_object(event_json,'$.kv.extend1') extend1,
	get_json_object(event_json,'$.kv.category') category,
	server_time,
	dt
from "$APP".dwd_base_event_log 
where dt='$log_date' and event_name='display';


insert overwrite table "$APP".dwd_newsdetail_log
PARTITION (dt)
select 
	mid_id,
	user_id,
	version_code,
	version_name,
	lang,
	source,
	os,
	area,
	model,
	brand,
	sdk_version,
	gmail,
	height_width,
	app_time,
	network,
	lng,
	lat,
	get_json_object(event_json,'$.kv.entry') entry,
	get_json_object(event_json,'$.kv.action') action,
	get_json_object(event_json,'$.kv.newsid') newsid,
	get_json_object(event_json,'$.kv.showtype') showtype,
	get_json_object(event_json,'$.kv.news_staytime') news_staytime,
	get_json_object(event_json,'$.kv.loading_time') loading_time,
	get_json_object(event_json,'$.kv.type1') type1,
	get_json_object(event_json,'$.kv.category') category,
	server_time,
	dt
from "$APP".dwd_base_event_log 
where dt='$log_date' and event_name='newsdetail';


insert overwrite table "$APP".dwd_loading_log
PARTITION (dt)
select 
	mid_id,
	user_id,
	version_code,
	version_name,
	lang,
	source,
	os,
	area,
	model,
	brand,
	sdk_version,
	gmail,
	height_width,
	app_time,
	network,
	lng,
	lat,
	get_json_object(event_json,'$.kv.action') action,
	get_json_object(event_json,'$.kv.loading_time') loading_time,
	get_json_object(event_json,'$.kv.loading_way') loading_way,
	get_json_object(event_json,'$.kv.extend1') extend1,
	get_json_object(event_json,'$.kv.extend2') extend2,
	get_json_object(event_json,'$.kv.type') type,
	get_json_object(event_json,'$.kv.type1') type1,
	server_time,
	dt
from "$APP".dwd_base_event_log 
where dt='$log_date' and event_name='loading';


insert overwrite table "$APP".dwd_ad_log
PARTITION (dt)
select 
	mid_id,
	user_id,
	version_code,
	version_name,
	lang,
	source,
	os,
	area,
	model,
	brand,
	sdk_version,
	gmail,
	height_width,
	app_time,
	network,
	lng,
	lat,
	get_json_object(event_json,'$.kv.entry') entry,
	get_json_object(event_json,'$.kv.action') action,
	get_json_object(event_json,'$.kv.content') content,
	get_json_object(event_json,'$.kv.detail') detail,
	get_json_object(event_json,'$.kv.source') ad_source,
	get_json_object(event_json,'$.kv.behavior') behavior,
	get_json_object(event_json,'$.kv.newstype') newstype,
	get_json_object(event_json,'$.kv.show_style') show_style,
	server_time,
	dt
from "$APP".dwd_base_event_log 
where dt='$log_date' and event_name='ad';


insert overwrite table "$APP".dwd_notification_log
PARTITION (dt)
select 
	mid_id,
	user_id,
	version_code,
	version_name,
	lang,
	source,
	os,
	area,
	model,
	brand,
	sdk_version,
	gmail,
	height_width,
	app_time,
	network,
	lng,
	lat,
	get_json_object(event_json,'$.kv.action') action,
	get_json_object(event_json,'$.kv.noti_type') noti_type,
	get_json_object(event_json,'$.kv.ap_time') ap_time,
	get_json_object(event_json,'$.kv.content') content,
	server_time,
	dt
from "$APP".dwd_base_event_log 
where dt='$log_date' and event_name='notification';


insert overwrite table "$APP".dwd_active_foreground_log
PARTITION (dt)
select 
	mid_id,
	user_id,
	version_code,
	version_name,
	lang,
	source,
	os,
	area,
	model,
	brand,
	sdk_version,
	gmail,
	height_width,
	app_time,
	network,
	lng,
	lat,
	get_json_object(event_json,'$.kv.active_source') active_source,
	server_time,
	dt
from "$APP".dwd_base_event_log 
where dt='$log_date' and event_name='active_background';


insert overwrite table "$APP".dwd_active_background_log
PARTITION (dt)
select 
	mid_id,
	user_id,
	version_code,
	version_name,
	lang,
	source,
	os,
	area,
	model,
	brand,
	sdk_version,
	gmail,
	height_width,
	app_time,
	network,
	lng,
	lat,
	get_json_object(event_json,'$.kv.active_source') active_source,
	server_time,
	dt
from "$APP".dwd_base_event_log 
where dt='$log_date' and event_name='active_background';


insert overwrite table "$APP".dwd_comment_log
PARTITION (dt)
select 
	mid_id,
	user_id,
	version_code,
	version_name,
	lang,
	source,
	os,
	area,
	model,
	brand,
	sdk_version,
	gmail,
	height_width,
	app_time,
	network,
	lng,
	lat,
	get_json_object(event_json,'$.kv.comment_id') comment_id,
	get_json_object(event_json,'$.kv.userid') userid,
	get_json_object(event_json,'$.kv.p_comment_id') p_comment_id,
	get_json_object(event_json,'$.kv.content') content,
	get_json_object(event_json,'$.kv.addtime') addtime,
	get_json_object(event_json,'$.kv.other_id') other_id,
	get_json_object(event_json,'$.kv.praise_count') praise_count,
	get_json_object(event_json,'$.kv.reply_count') reply_count,
	server_time,
	dt
from "$APP".dwd_base_event_log 
where dt='$log_date' and event_name='comment';


insert overwrite table "$APP".dwd_favorites_log
PARTITION (dt)
select 
	mid_id,
	user_id,
	version_code,
	version_name,
	lang,
	source,
	os,
	area,
	model,
	brand,
	sdk_version,
	gmail,
	height_width,
	app_time,
	network,
	lng,
	lat,
	get_json_object(event_json,'$.kv.id') id,
	get_json_object(event_json,'$.kv.course_id') course_id,
	get_json_object(event_json,'$.kv.userid') userid,
	get_json_object(event_json,'$.kv.add_time') add_time,
	server_time,
	dt
from "$APP".dwd_base_event_log 
where dt='$log_date' and event_name='favorites';


insert overwrite table "$APP".dwd_praise_log
PARTITION (dt)
select 
	mid_id,
	user_id,
	version_code,
	version_name,
	lang,
	source,
	os,
	area,
	model,
	brand,
	sdk_version,
	gmail,
	height_width,
	app_time,
	network,
	lng,
	lat,
	get_json_object(event_json,'$.kv.id') id,
	get_json_object(event_json,'$.kv.userid') userid,
	get_json_object(event_json,'$.kv.target_id') target_id,
	get_json_object(event_json,'$.kv.type') type,
	get_json_object(event_json,'$.kv.add_time') add_time,
	server_time,
	dt
from "$APP".dwd_base_event_log 
where dt='$log_date' and event_name='praise';


insert overwrite table "$APP".dwd_start_log
PARTITION (dt)
select 
	mid_id,
	user_id,
	version_code,
	version_name,
	lang,
	source,
	os,
	area,
	model,
	brand,
	sdk_version,
	gmail,
	height_width,
	app_time,
	network,
	lng,
	lat,
	get_json_object(event_json,'$.kv.entry') entry,
	get_json_object(event_json,'$.kv.open_ad_type') open_ad_type,
	get_json_object(event_json,'$.kv.action') action,
	get_json_object(event_json,'$.kv.loading_time') loading_time,
	get_json_object(event_json,'$.kv.detail') detail,
	get_json_object(event_json,'$.kv.extend1') extend1,
	server_time,
	dt
from "$APP".dwd_base_start_log 
where dt='$log_date' and event_name='start';


insert overwrite table "$APP".dwd_error_log
PARTITION (dt)
select 
	mid_id,
	user_id,
	version_code,
	version_name,
	lang,
	source,
	os,
	area,
	model,
	brand,
	sdk_version,
	gmail,
	height_width,
	app_time,
	network,
	lng,
	lat,
	get_json_object(event_json,'$.kv.errorBrief') errorBrief,
	get_json_object(event_json,'$.kv.errorDetail') errorDetail,
	server_time,
	dt
from "$APP".dwd_base_event_log 
where dt='$log_date' and event_name='error';

"

$hive -e "$sql"

数仓分析前置知识

业务术语

  1. 用户
    用户以设备为判断标准,在移动统计中,每个独立设备认为是一个独立用户。Android系统根据IMEI号,IOS系统根据OpenUDID来标识一个独立用户,每部手机一个用户。
  2. 新增用户
    首次联网使用应用的用户。如果一个用户首次打开某app,那这个用户定义为新增用户;卸载再安装的设备,不会被算作一次新增。新增用户包括日新增用户、周新增用户、月新增用户。
  3. 活跃用户
    打开应用的用户即为活跃用户,不考虑用户的使用情况。每天一台设备打开多次会被计为一个活跃用户。
  4. 周(月)活跃用户
    某个自然周(月)内启动过应用的用户,该周(月)内的多次启动只记一个活跃用户。
  5. 月活跃率
    月活跃用户与截止到该月累计的用户总和之间的比例。
  6. 沉默用户
    用户仅在安装当天(次日)启动一次,后续时间无再启动行为。该指标可以反映新增用户质量和用户与APP的匹配程度。
  7. 版本分布
    不同版本的周内各天新增用户数,活跃用户数和启动次数。利于判断App各个版本之间的优劣和用户行为习惯。
  8. 本周回流用户
    上周未启动过应用,本周启动了应用的用户。
  9. 连续n周活跃用户
    连续n周,每周至少启动一次。
  10. 忠诚用户
    连续活跃5周以上的用户
  11. 连续活跃用户
    连续2周及以上活跃的用户
  12. 近期流失用户
    连续n(2<= n <= 4)周没有启动应用的用户。(第n+1周没有启动过)
  13. 留存用户
    某段时间内的新增用户,经过一段时间后,仍然使用应用的被认作是留存用户;这部分用户占当时新增用户的比例即是留存率。例如,5月份新增用户200,这200人在6月份启动过应用的有100人,7月份启动过应用的有80人,8月份启动过应用的有50人;则5月份新增用户一个月后的留存率是50%,二个月后的留存率是40%,三个月后的留存率是25%。
  14. 用户新鲜度
    每天启动应用的新老用户比例,即新增用户数占活跃用户数的比例。
  15. 单次使用时长
    每次启动使用的时间长度。
  16. 日使用时长
    累计一天内的使用时间长度。
  17. 启动次数计算标准
    IOS平台应用退到后台就算一次独立的启动;Android平台我们规定,两次启动之间的间隔小于30秒,被计算一次启动。用户在使用过程中,若因收发短信或接电话等退出应用30秒又再次返回应用中,那这两次行为应该是延续而非独立的,所以可以被算作一次使用行为,即一次启动。业内大多使用30秒这个标准,但用户还是可以自定义此时间间隔。

系统函数

collect_set函数

collect_set 函数,有两个作用,第一个是去重,去除group by后的重复元素, 第二个是形成一个集合,将group by后属于同一组的第三列集合起来成为一个集合,如果不需要去重则使用collect_list。

函数具体介绍和用法参考HIVE中关于collect_set与explode函数【转载】

日期处理函数

1)date_format函数(根据格式整理日期)

hive (gmall)> select date_format('2019-02-10','yyyy-MM');

结果如下:

2019-02

2)date_add函数(加减日期)

hive (gmall)> select date_add('2019-02-10',-1);

结果如下:

2019-02-09

hive (gmall)> select date_add('2019-02-10',1);

结果如下:

2019-02-11

3)next_day函数
(1)取当前天的下一周的周一

hive (gmall)> select next_day('2019-02-12','MO')

结果如下:

2019-02-18

(2)取当前周的周一

hive (gmall)> select date_add(next_day('2019-02-12','MO'),-7);

结果如下:

2019-02-11

4)last_day函数(求当月最后一天日期)

hive (gmall)> select last_day('2019-02-10');

结果如下:

2019-02-28

未经允许不得转载:作者:1300-黄同学, 转载或复制请以 超链接形式 并注明出处 拜师资源博客
原文地址:《数据仓库项目(第三节)用户数据数仓建设、数仓分析前置知识》 发布于2021-02-06

分享到:
赞(0) 打赏

评论 抢沙发

评论前必须登录!

  注册



长按图片转发给朋友

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏

Vieu3.3主题
专业打造轻量级个人企业风格博客主题!专注于前端开发,全站响应式布局自适应模板。

登录

忘记密码 ?

您也可以使用第三方帐号快捷登录

Q Q 登 录
微 博 登 录