虚拟属性和维度表

本文档所描述的内容属于神策分析的高级使用功能，涉及较多技术细节，适用于对相关功能有经验的用户参考。如果对文档内容有疑惑，请咨询神策值班同学获取一对一的协助。

自 1.11 版本开始，神策分析已经支持虚拟属性功能，同时还支持使用第三方的维度表来对已接入的事件和属性进行进一步的扩展，该功能可以大大的增强神策分析对于复杂业务需求的处理能力。
1.17 版本以前，创建虚拟属性和维度表均需要在部署神策系统的服务器上操作，1.17 及之后版本，支持在元数据管理内创建虚拟属性。

请先 ssh 到部署了神策服务的任意一台机器，在 sa_cluster 账户下执行创建虚拟属性或者维度表的命令。

虚拟属性

所谓虚拟属性，是指在数据入库之后通过 SQL 表达式对已有的事件属性和用户属性进行二次加工，产生一个新的属性值。

注意：
events 表中的 time 和 event 属于特殊字段，目前支持对 time 字段二次加工，具体可参考应用场景 5 举例。不支持对 event 字段二次加工提取虚拟属性。
创建虚拟属性时 SQL 表达式最长不能超过 1024 个字符。
虚拟属性名不可以和元数据中已存在的事件属性 / 用户属性同名。

应用场景 1：属性抽取

例如我们现在有一个事件属性是 $url，希望从 $url 中抽取出 q= 的属性，作为 search_keyword 来进行分析，那么我们可以这样创建虚拟属性：

scaadmin external_view external_property add \
-p default \
-n search_keyword \
-c '搜索关键词' \
-e "parse_url(events.\$url, 'QUERY', 'q')" \
-t STRING

其中，-p 是神策系统的项目名称, -n 是外部属性在神策系统中的英文名称，-c 是中文名称，-e 是对应的 SQL 表达式，-t 表示数据类型。

我们在 -e 参数中使用了一个 parse_url 的 SQL 函数来进行参数提取，这样在分析的时候使用 search_keyword 属性即可实现相关的分析需求。

应用场景 2：属性合并

假设我们在埋点的时候埋了两个属性：item_id 和 item_id_1，但实际上它们是一个含义，希望在使用的时候进行合并，也可以使用虚拟属性功能来定义：

scaadmin external_view external_property add \
-p default \
-n new_item_id \
-c 'Item Id' \
-e "coalesce(events.item_id, events.item_id_1)" \
-t STRING

这里的 coalesce 是一个标准 SQL 函数，返回所有参数中第一个非 NULL 的值，这样我们在使用的时候只要使用 new_item_id 来进行分析就可以达到属性合并的目的。

应用场景 3：高精度小数

默认情况下，神策分析的 NUMBER 类型只支持小数点之后 3 位，如果需要支持高精度类型，可以使用扩展属性的方式来实现。具体做法如下：

将需要支持高精度的内容以字符串的类型来发送给神策分析，以避免精度丢失，以 Java SDK 为例：

Map properties = new HashMap();
properties.put("big_number", "123.12312345");
sa.track(distinctId, true, "TestBigNumber", properties);

创建一个高精度类型的虚拟属性

# 这里假设传入的原始字符串属性为 big_number，创建的高精度属性为 big_number_decimal
# decimal 参数中的 38 表示总的数据位数，16 表示小数点之后的位数
scaadmin external_view external_property add \
-p default \
-n big_number_decimal \
-c '高精度数字' \
-e 'cast(events.big_number as decimal(38,16))' \
-t number

使用 big_number_decimal 属性进行相关的分析

应用场景 4：联合去重

在神策分析中，我们支持对某个属性进行去重数的计算，但是不直接支持对两个或者更多的属性进行去重。如果有这类需求，也可以使用虚拟属性的方式来实现，即定义一个新的属性，它的值是需要去重的多个属性的组合。

例如，我们想计算不同用户浏览不同商品的去重次数（即一个用户浏览同一个商品不重复计数，但是浏览不同的商品需要计数），那么可以定义一个虚拟属性如下：

scaadmin external_view external_property add \
-p default \
-n user_and_product \
-c '用户ID + 商品ID' \
-e "concat(cast(events.user_id as string), events.product_id)" \
-t STRING

然后在神策分析中，查看这个属性的去重数即可得到对应的指标。

应用场景 5：时间聚合

默认情况下，神策分析支持天、周、月等时间聚合方式，如果想要其它的时间聚合方式，也可以使用虚拟属性实现。例如，如果想要按照 "周X" 来对数据进行分析，可以从 time 属性中提取出一个 day_of_week 的属性：

scaadmin external_view external_property add \
-p default \
-n day_of_week \
-e 'dayofweek(time)' \
-t NUMBER

dayofweek 函数求得的数值，1 表示星期天，7 表示星期六，2-6 表示星期一至星期五。类似的，也可以用 extract(hour from time) 表达式来提取时间中的小时部分。

应用场景 6：`用户属性`

神策支持使用 users 表中的用户属性提取虚拟属性，比如根据用户属性 Birthday ，得到用户的年龄属性，可参考如下示例：

scaadmin external_view external_property add \
-p default \
-n user_age \
-u true \
-c "用户年龄" \
-e "cast (extract(year FROM now()) -extract(year FROM EPOCH_TO_TIMESTAMP(users.Birthday)) as int)" \
-t NUMBER

注意：

使用用户属性创建虚拟属性时，需要添加 -u 参数，并且在表达式内用到的用户属性注明是用户属性。
不支持使用 user 表中的用户属性和维度表关联。

维度表

除了基于已经埋点的属性来直接创建虚拟属性之外，我们还可以结合第三方维度表来创建更复杂的虚拟属性应用。

假设我们在神策分析中有一个 pay_order 事件，同时该事件有 product_id、product_name 等属性。现在我们希望在分析的时候使用 product 的更多其它维度来进行分析（例如 product_manufacturer），但是这些维度并没有在埋点的时候打入神策系统中，这个时候就可以引入维度表来满足这个需求。

使用 items 表作为维度表

注意：神策分析 1.14 及之后版本才支持此方式。

如果希望启用 items 表，首先需要通过 SDK 提供的 itemSet 接口进行 Item 信息的上报。以 Java SDK 为例：

Map properties = new LinkedHashMap<>();
properties.put("product_name", "iPhone 8");
properties.put("product_manufacturer", "Apple");
properties.put("price", 998.88);
// 参数分别为 item_type、item_id、自定义属性
sensorsAnalytics.itemSet("product", "T12345", properties);

注意：item_type 可以用于区分不同的 item 类型，比如 movie、muisic 等，item_id 区分同一个 item 类型下面的不同的 Item。

然后，我们就可以在自定义查询功能中使用这个表：

SELECT * FROM items LIMIT 10

接下来我们建立 items 和 events 表的关联：

scaadmin external_view external_dimension_table add \
-p default \
-t items \
-e "events.product_id = items.item_id AND items.item_type = 'product'"

注意：
这里是使用 product_id 字段进行关联，同时限定了 item_type。如果同时存在多种 item_type，那么需要建立多次不同的关联，具体请参考一张维度表使用不同的关联条件。
维度表关联的字段必须为主键，若不是主键且维度表数据有重复的情况下，会导致在神策分析查询的数据增多。
items 表关联时，必须同时使用 item_id 和 item_type 进行关联。若只指定 item_id ，没有指定 item_type，item_id 有重复的情况下，会导致在神策分析查询的数据增多。

最后，使用 items 表中的 product_manufacturer 来创建虚拟属性即可：

scaadmin external_view external_property add \
-p default \
-n product_manufacturer \
-c '产品制造商' \
-e items.product_manufacturer \
-t STRING

至此，我们已经可以在神策系统的所有分析功能中使用 pay_order 进行分析的时候，看到 product_manufacturer 属性，并使用这个属性进行任意的分析工作。

如果需要对维度表和虚拟属性进行删除、更新等管理操作，可以直接执行不带参数的命令查看相关的帮助：

scaadmin external_view external_property

自定义维度表

除了引用 items 表之外，我们也可以手动创建维度表。

1.创建新的维度表，可执行命令：

scaadmin dimension_table create -p {项目英文名} -t {维度表名} -k {维度表主键} -c {维度表字段及类型}
//sp 1.18+,sca  0.3.2+,低于该版本请切换左上方版本号到 2.2 以下版本

可使用如下命令查看版本号

spadmin upgrader version

本命令执行后可在维度表数据库中创建一张新的维度表。书写格式参考：

参数	参数详情	例
-p	项目英文名	-p defalut 其中，default 为项目英文名。无法确定需要创建维度表的项目名时，参考下图，神策访问地址里 project ={项目英文名}
-t	维度表名表名称请自定义，仅支持英文字母、下划线、数字，且建议以英文开头，总长度不超过 100 字符	-t product_info
-k	维度表主键如果是多主键，写多个字段用英文逗号分隔。	-k product_id
-c	维度表字段及字段类型注意加引号，格式为字段名字段类型，字段之间用英文逗号分隔	-c 'product_id STRING, product_manufacturer STRING ' 支持的数据类型有 BIGINT, BINARY, BOOLEAN, INT,DATE, DECIMAL, REAL, FLOAT, INTEGER, SMALLINT, STRING, TINYINT, VARCHAR,TIMESTAMP

执行实例参考：

scaadmin dimension_table create \
-p default \
-t product_info \
-k product_id \
-c "product_id STRING, product_manufacturer STRING"

执行完成后，即创建一张维度表（kudu 表），可根据返回获得维度表名称，例如：dimension_table_default.product_info。如果执行失败可能因为当前版本较低不支持该功能，请联系神策技术支持确认。

注意：这里的维度表必须使用 Kudu 或者 HDFS 的 Parquet 文件格式来存储，否则无法支持全部特性。

如需删除维度表，请使用：

scaadmin  dimension_table drop -t  <表名>   -p  <项目名> 
例如：scaadmin  dimension_table drop -t product_info  -p default
 //默认如果存在表关联关系则不支持删除，如果需要强行删除，可加 --skip_check 参数，加上 --skip_check 参数之后，即使表存在关联关系，也仍然强制执行删除。

如需列出维度表，请使用：

scaadmin  dimension_table list -p <项目名>

2.创建成功后的维度表，例如 dimension_table_default.product_info（ dimension_table_default 是数据库名，product_table 为表名），可使用如下方法进行查看和添加数据：

使用 imapla-shell

impala-shell

查看创建的维度表

use dimension_table_default;
show tables;

可查看到我们已经创建的 product_info 表。然后，我们需要准备好这张维度表的数据，通常应该是从其它业务数据库或者数据仓库中导入进来。具体可以使用 impala-shell 导入 SQL 文件，或者 JDBC 等多种方式来进行，例如我们可以直接插入几条数据：

INSERT INTO dimension_table_default.product_info VALUES ('124', 'Xiaomi'), ('123', 'Apple');

如果需要插入的数据量比较大，建议使用批量文件导入的方式。首先需要创建一个文本格式的表，并指定分隔符，例如:

CREATE TABLE dimensions.raw_csv_product_info (
	product_id STRING,
	product_manufacturer STRING
) ROW FORMAT DELIMITED FIELDS TERMINATED by ',' LOCATION '/tmp/raw_csv_product_info/';

然后上传已经准备好的逗号分隔的文本数据文件：

hdfs dfs -put data.csv /tmp/raw_csv_product_info/

刷新 CSV 表，并执行 INSERT 把数据导入 Kudu 即可。

REFRESH dimensions.raw_csv_product_info;
INSERT INTO dimension_table_default.product_info SELECT * FROM dimensions.raw_csv_product_info;

3.在准备好维度表数据之后，把该维度表加入神策系统中

scaadmin external_view external_dimension_table add \
-p default \
-t dimension_table_default.product_info \
-e 'events.product_id = dimension_table_default.product_info.product_id'

其中，-p 是神策系统的项目名称，-t 参数是维度表的完整名称， -e 参数表示该维度表和事件表（events）的关联关系，即 SQL 中进行 JOIN 的条件。

4.在定义了维度表之后，我们就可以把该维度表（即 product_info）中的具体字段作为一个虚拟属性加入神策系统中

scaadmin external_view external_property add \
-p default \
-n product_manufacturer \
-c '产品制造商' \
-e dimension_table_default.product_info.product_manufacturer \
-t STRING

至此，我们已经可以在神策系统的所有分析功能中使用 pay_order 进行分析的时候，看到 product_manufacturer 属性，并使用这个属性进行任意的分析工作。

一张维度表使用不同的关联条件

如果同一张维度表需要使用不同的关联条件，那么需要在新增维度表的时候使用别名。具体的方式为在原有的表面后面加上 #1 、#2、#a、#b等，注意别名分隔请使用 # 号。例如：

scaadmin external_view external_dimension_table add \
-p default \
-t dimension_table_default.product_info#1 \
-e 'events.item_id = dimension_table_default.product_info#1.item_id'

添加完成之后，后面在其它命令引用这张表时也需要使用 dimension_table_default.product_info#1。

限制与约束

查询性能

由于关联维度表需要使用 JOIN，虽然神策的查询引擎已经对这个类型的 JOIN 做了一定程度的优化，但是相比直接使用原始的事件属性依然会有比较显著的性能降低，具体的性能和维度表的大小、JOIN 的条件等都有关系。因此，我们建议在直接使用事件属性可以满足需求的情况下，不要使用维度表；同时，应当保证维度表的行数在百万以内，以尽量降低 JOIN 带来的额外性能损耗。

缓存一致性

目前为止，神策系统的缓存机制依然是基于事件数据的变更来实现的，这个机制中暂时没有考虑到维度表的数据变化带来的影响。因此，如果维度表的数据发生了变更（例如进行 Update 或者 Insert），查询结果可能还会使用旧的缓存数据，这个时候需要强制刷新才能得到正确的结果。

常见问题

新增的虚拟属性在页面不可见

在添加完虚拟属性之后，可能会发现在某些事件的属性选择界面无法看到对应的虚拟属性，但是在自定义查询里确可以看到。出现这种情况，是因为默认情况下虚拟属性只会和包含了所有引用属性的事件进行绑定。

例如，在应用场景 2 中，new_item_id 这个虚拟属性引用了 item_id 和 item_id_1，所以也只有同时包含这两个属性的事件才能看到 new_item_id 属性。

在 1.14 版本之后，如果有特殊需求，也可以在添加虚拟属性时单独指定一个关联属性，例如 item_name，这样在任何包含了 item_name 的事件里，都可以看到新的 new_item_id 属性。例如：

scaadmin external_view external_property add \
-p default \
-n new_item_id \
-c 'Item Id' \
-e "coalesce(events.item_id, events.item_id_1)" \
-r "item_name" \
-t STRING

其中，-r不填写是默认情况（这样虚拟属性只会和包含了所有引用属性的事件进行绑定），-r具体属性值的时候（设置关联属性-r就是包含该属性的事件会添加虚拟属性），-r为空("")的时候（这种情况下所有事件里都可以看到这个虚拟属性）

新增虚拟属性之后隐藏原属性

例如在属性合并的场景中，我们把 item_id 和 item_id_1 合并成了 new_item_id，这个时候可能希望隐藏旧的 item_id、item_id_1。那么只需要直接在元数据管理中隐藏即可，引用的属性隐藏之后并不会影响虚拟属性的正常工作。

如何创建日期(DATETIME)类型虚拟属性

创建虚拟属性是时间类型的字段，要求创建虚拟属性的表达式（-e）得到的结果是一个 bigint 类型的时间戳，再设置虚拟属性类型 (-t) 为 DATETIME 类型。例如，原本记录了一个 oldtimestamp 是数值类型，存储的是时间戳，为了便于分析，可以使用虚拟属性来新建一个 datetime 类型的属性方便进行日期比较的分析。如果是上传维度表再设置虚拟属性，那么要求上传的维度表里存储的就是时间戳。

scaadmin external_view external_property add \
-p default \
-n newdate \
-c 'newdate' \
-e "cast(events.oldtimestamp as bigint)" \
-t DATETIME

如何创建布尔(BOOL)类型虚拟属性

创建虚拟属性是布尔类型的字段，要求创建虚拟属性的表达式（-e）得到的结果是一个 bigint 类型且值为 0、1 ,再设置虚拟属性类型 (-t) 为 BOOL 类型。例如，针对所有的支付记录直接区分是否是“有效支付”和“无效支付” ,“无效支付”指得是支付金额值为 0 或者 null 的订单，其他归为“有效支付”。

scaadmin external_view external_property add \
-p default \
-n is_valueable_paid \
-c '是否有效支付' \
-e "cast((case when (events.ActualPaidAmount is not null or events.ActualPaidAmount=0) then '1' else '0' end) as bigint)" \
-t BOOL

如何创建集合(LIST) 类型虚拟属性

维度表中的属性类型对应 STRING 类型，虚拟属性设置为 LIST 类型。LIST 类型虚拟属性创建举例：

创建维度表的时候，设置 list_name 为 STRING 类型：

CREATE DATABASE dimensions;

CREATE TABLE dimensions.listceshi (
	list_id STRING NOT NULL,
	list_name STRING NULL,
	...
	PRIMARY KEY (list_id)
)
PARTITION BY HASH (product_id) PARTITIONS 3
STORED AS KUDU TBLPROPERTIES ('kudu.master_addresses'='${kudu_master_host}:7051');

传值时如果 LIST 的值有多个值，需要用换行符号\n 分割：

INSERT INTO dimensions.listceshi VALUES ('124', 'apple\nbanana\npeer'), ('123', 'apple\nbanana\nbeer')

退出 impala，用工具建立好维度表和 events 表的关联关系后，再创建 LIST 类型虚拟属性：

scaadmin external_view external_property add \
-p default \
-n list_name \
-c '列表项目' \
-e dimensions.listceshi.list_name \
-t LIST

测试维度表的关联条件

注意：1.14 版本之后可用

对于比较复杂的关联条件，建议先使用 impala-shell 执行 SQL 来进行测试，以保证结果的正确性。可以直接使用 JOIN 语法进行，例如：

/*sa(test_project)*/ 
SELECT dimensions.product_info.product_manufacturer FROM
events 
LEFT JOIN dimensions.product_info
ON events.product_id = dimensions.product_info.product_id
WHERE date = CURRENT_DATE()
LIMIT 100

如何更新虚拟属性/维度表

更新虚拟属性

scaadmin external_view external_property update \
-p default \
-n new_item_id \
-c 'Item Id' \
-e "coalesce(events.item_id, events.item_id_1)" \
-r "item_name" \
-t STRING