{
"Statement": [
{
"Resource": [
"arn:aws:s3:::<YOUR-BUCKET-NAME>*"
],
"Action": [
"s3istBucket", "s3:GetObject*"
],
"Effect": "Allow"
}
],
"Version": "2012-10-17"
}
{
"jsonpaths": [
"$['event_type']",
"$['event_timestamp']",
"$['arrival_timestamp']",
"$['event_version']",
"$['application']['app_id']",
"$['application']['package_name']",
"$['application']['version_name']",
"$['application']['version_code']",
"$['application']['title']",
"$['application']['cognito_identity_pool_id']",
"$['application']['sdk']['name']",
"$['application']['sdk']['version']",
"$['client']['client_id']",
"$['client']['cognito_id']",
"$['device']['model']",
"$['device']['make']",
"$['device']['platform']['name']",
"$['device']['platform']['version']",
"$['device']['locale']['code']",
"$['device']['locale']['language']",
"$['device']['locale']['country']",
"$['session']['session_id']",
"$['session']['start_timestamp']",
"$['session']['stop_timestamp']",
"$['monetization']['transaction']['transaction_id']",
"$['monetization']['transaction']['store']",
"$['monetization']['transaction']['item_id']",
"$['monetization']['transaction']['quantity']",
"$['monetization']['transaction']['price']['reported_price']",
"$['monetization']['transaction']['price']['amount']",
"$['monetization']['transaction']['price']['currency']['code']",
"$['monetization']['transaction']['price']['currency']['symbol']",
"$['attributes']['class']",
"$['attributes']['level']",
"$['attributes']['name']",
"$['attributes']['paying customer']",
"$['metrics']['score']",
"$['metrics']['time played']",
"$['metrics']['total spent']"
]
}
CREATE schema AWSMA;
--创建此表用于临时容纳加载自Amazon S3的事件
CREATE TABLE AWSMA.event_staging(
event_type VARCHAR(256) NOT NULL ENCODE LZO,
event_timestamp TIMESTAMP NOT NULL ENCODE LZO,
arrival_timestamp TIMESTAMP NOT NULL ENCODE LZO,
event_version CHAR(12) NULL ENCODE LZO,
application_app_id VARCHAR(64) NOT NULL ENCODE LZO,
application_package_name VARCHAR(256) NULL ENCODE LZO,
application_version_name VARCHAR(256) NULL ENCODE LZO,
application_version_code VARCHAR(256) NULL ENCODE LZO,
application_title VARCHAR(256) NULL ENCODE LZO,
application_cognito_identity_pool_id VARCHAR(64) NULL ENCODE LZO,
application_sdk_name VARCHAR(256) NULL ENCODE LZO,
application_sdk_version VARCHAR(256) NULL ENCODE LZO,
client_id VARCHAR(64) NOT NULL DISTKEY ENCODE LZO,
client_cognito_id VARCHAR(64) NULL ENCODE LZO,
device_model VARCHAR(256) NULL ENCODE LZO,
device_make VARCHAR(256) NULL ENCODE LZO,
device_platform_name VARCHAR(256) NULL ENCODE LZO,
device_platform_version VARCHAR(256) NULL ENCODE LZO,
device_locale_code VARCHAR(256) NULL ENCODE LZO,
device_locale_language VARCHAR(64) NULL ENCODE LZO,
device_locale_country VARCHAR(64) NULL ENCODE LZO,
session_id VARCHAR(64) NULL ENCODE LZO,
session_start_timestamp TIMESTAMP NULL ENCODE LZO,
session_stop_timestamp TIMESTAMP NULL ENCODE LZO,
monetization_transaction_id VARCHAR(64) NULL ENCODE LZO,
monetization_transaction_store VARCHAR(64) NULL ENCODE LZO,
monetization_transaction_item_id VARCHAR(64) NULL ENCODE LZO,
monetization_transaction_quantity FLOAT8 NULL,
monetization_transaction_price_reported VARCHAR(64) NULL ENCODE LZO,
monetization_transaction_price_amount FLOAT8 NULL,
monetization_transaction_price_currency_code VARCHAR(16) NULL ENCODE LZO,
monetization_transaction_price_currency_symbol VARCHAR(32) NULL ENCODE LZO,
a_class VARCHAR(4000),
a_level VARCHAR(4000),
a_name VARCHAR(4000),
"a_paying customer" VARCHAR(4000),
m_score float8,
"m_time played" float8,
"m_total spent" float8
)
SORTKEY ( application_app_id, event_timestamp, event_type);
--创建此表用于保存全部事件
CREATE TABLE AWSMA.event(
event_type VARCHAR(256) NOT NULL ENCODE LZO,
event_timestamp TIMESTAMP NOT NULL ENCODE LZO,
arrival_timestamp TIMESTAMP NOT NULL ENCODE LZO,
event_version CHAR(12) NULL ENCODE LZO,
application_app_id VARCHAR(64) NOT NULL ENCODE LZO,
application_package_name VARCHAR(256) NULL ENCODE LZO,
application_version_name VARCHAR(256) NULL ENCODE LZO,
application_version_code VARCHAR(256) NULL ENCODE LZO,
application_title VARCHAR(256) NULL ENCODE LZO,
application_cognito_identity_pool_id VARCHAR(64) NULL ENCODE LZO,
application_sdk_name VARCHAR(256) NULL ENCODE LZO,
application_sdk_version VARCHAR(256) NULL ENCODE LZO,
client_id VARCHAR(64) NOT NULL DISTKEY ENCODE LZO,
client_cognito_id VARCHAR(64) NULL ENCODE LZO,
device_model VARCHAR(256) NULL ENCODE LZO,
device_make VARCHAR(256) NULL ENCODE LZO,
device_platform_name VARCHAR(256) NULL ENCODE LZO,
device_platform_version VARCHAR(256) NULL ENCODE LZO,
device_locale_code VARCHAR(256) NULL ENCODE LZO,
device_locale_language VARCHAR(64) NULL ENCODE LZO,
device_locale_country VARCHAR(64) NULL ENCODE LZO,
session_id VARCHAR(64) NULL ENCODE LZO,
session_start_timestamp TIMESTAMP NULL ENCODE LZO,
session_stop_timestamp TIMESTAMP NULL ENCODE LZO,
monetization_transaction_id VARCHAR(64) NULL ENCODE LZO,
monetization_transaction_store VARCHAR(64) NULL ENCODE LZO,
monetization_transaction_item_id VARCHAR(64) NULL ENCODE LZO,
monetization_transaction_quantity FLOAT8 NULL,
monetization_transaction_price_reported VARCHAR(64) NULL ENCODE LZO,
monetization_transaction_price_amount FLOAT8 NULL,
monetization_transaction_price_currency_code VARCHAR(16) NULL ENCODE LZO,
monetization_transaction_price_currency_symbol VARCHAR(32) NULL ENCODE LZO,
a_class VARCHAR(4000),
a_level VARCHAR(4000),
a_name VARCHAR(4000),
"a_paying customer" VARCHAR(4000),
m_score float8,
"m_time played" float8,
"m_total spent" float8
)
SORTKEY ( application_app_id, event_timestamp, event_type);
--创建一套视图
CREATE OR REPLACE VIEW AWSMA.v_event AS SELECT * FROM AWSMA.event;
--首先截断该分段表
TRUNCATE AWSMA.event_staging;
--复制来自Amazon S3的事件
COPY
AWSMA.event_staging(
"event_type",
"event_timestamp",
"arrival_timestamp",
"event_version",
"application_app_id",
"application_package_name",
"application_version_name",
"application_version_code",
"application_title",
"application_cognito_identity_pool_id",
"application_sdk_name",
"application_sdk_version",
"client_id",
"client_cognito_id",
"device_model",
"device_make",
"device_platform_name",
"device_platform_version",
"device_locale_code",
"device_locale_language",
"device_locale_country",
"session_id",
"session_start_timestamp",
"session_stop_timestamp",
"monetization_transaction_id",
"monetization_transaction_store",
"monetization_transaction_item_id",
"monetization_transaction_quantity",
"monetization_transaction_price_reported",
"monetization_transaction_price_amount",
"monetization_transaction_price_currency_code",
"monetization_transaction_price_currency_symbol",
"a_class",
"a_level",
"a_name",
"a_paying customer",
"m_score",
"m_time played",
"m_total spent"
)
FROM
's3://<YOUR BUCKET NAME>/awsma/events/<APP ID>/<YEAR>/<MONTH>/<DAY>'
credentials
'aws_access_key_id=<ACCESS KEY>;aws_secret_access_key=<SECRET KEY>'
EMPTYASNULL gzip TIMEFORMAT AS 'epochmillisecs' MAXERROR AS 0
JSON AS 's3://<YOUR BUCKET NAME>/jsonpaths/eventmapping.json';
将加载事件插入至AWSMA.event表
现在各事件已经顺利进入AWSMA.event分段表,未来大家还需要向AWSMA.event表中插入更多新事件(即那些尚未进入AWSMA.event表的事件)。如此一来,我们就能够针对当前时段进行事件重新加载(举例来说,载入今年之内的全部最新事件),同时继续保持AWSMA.event表中的其它事件不受影响。
使用以下命令将来自AWSMA.event分段表内的数据加载至AWSMA.event表:
更新该列清单以添加我们需要加载的自定义指标与属性名称。
--从分段表中加载各尚未进入AWSMA.event表的事件
INSERT INTO AWSMA.event (
"event_type",
"event_timestamp",
"arrival_timestamp",
"event_version",
"application_app_id",
"application_package_name",
"application_version_name",
"application_version_code",
"application_title",
"application_cognito_identity_pool_id",
"application_sdk_name",
"application_sdk_version",
"client_id",
"client_cognito_id",
"device_model",
"device_make",
"device_platform_name",
"device_platform_version",
"device_locale_code",
"device_locale_language",
"device_locale_country",
"session_id",
"session_start_timestamp",
"session_stop_timestamp",
"monetization_transaction_id",
"monetization_transaction_store",
"monetization_transaction_item_id",
"monetization_transaction_quantity",
"monetization_transaction_price_reported",
"monetization_transaction_price_amount",
"monetization_transaction_price_currency_code",
"monetization_transaction_price_currency_symbol",
"a_class",
"a_level",
"a_name",
"a_paying customer",
"m_score",
"m_time played",
"m_total spent")
SELECT
staging."event_type",
staging."event_timestamp",
staging."arrival_timestamp",
staging."event_version",
staging."application_app_id",
staging."application_package_name",
staging."application_version_name",
staging."application_version_code",
staging."application_title",
staging."application_cognito_identity_pool_id",
staging."application_sdk_name",
staging."application_sdk_version",
staging."client_id",
staging."client_cognito_id",
staging."device_model",
staging."device_make",
staging."device_platform_name",
staging."device_platform_version",
staging."device_locale_code",
staging."device_locale_language",
staging."device_locale_country",
staging."session_id",
staging."session_start_timestamp",
staging."session_stop_timestamp",
staging."monetization_transaction_id",
staging."monetization_transaction_store",
staging."monetization_transaction_item_id",
staging."monetization_transaction_quantity",
staging."monetization_transaction_price_reported",
staging."monetization_transaction_price_amount",
staging."monetization_transaction_price_currency_code",
staging."monetization_transaction_price_currency_symbol",
staging."a_class",
staging."a_level",
staging."a_name",
staging."a_paying customer",
staging."m_score",
staging."m_time played",
staging."m_total spent"
FROM AWSMA.event_staging staging
LEFT JOIN
AWSMA.event events ON events.event_timestamp = staging.event_timestamp
AND events.application_app_id = staging.application_app_id
AND events.client_id = staging.client_id
WHERE
events.client_id IS NULL;
--创建eventReaders群组
CREATE GROUP eventReaders;
--Configure AWSMA schema privileges
GRANT usage ON schema AWSMA TO GROUP eventReaders;
GRANT SELECT ON AWSMA.v_event TO GROUP eventReaders;
--创建eventReader用户
CREATE USER <username> IN GROUP eventReaders PASSWORD '<password>';
现在开始,大家即可顺利进行查询。
从时长角度出发,三十天内活跃用户与设备。
SELECT
application_app_id AS "app id",
COUNT(DISTINCT client_id) AS "devices",
COUNT(DISTINCT client_cognito_id) AS "users",
date_trunc('day', event_timestamp) AS "day"
FROM
AWSMA.v_event
WHERE
event_type = '_session.start' AND
event_timestamp BETWEEN getdate() - 30 AND getdate() + 1
GROUP BY
"app id",
"day"
ORDER BY
"app id" ASC,
"day" DESC
SELECT
application_app_id AS "app id",
COUNT(DISTINCT client_id) AS "devices",
COUNT(DISTINCT client_cognito_id) AS "users",
device_locale_language AS "language"
FROM
AWSMA.v_event
WHERE
event_type = '_session.start' AND
event_timestamp BETWEEN getdate() - 30 AND getdate() + 1
GROUP BY
"app id",
"language"
ORDER BY
"app id" ASC,
"devices" DESC,
"language"
;
SELECT
application_app_id AS "app id",
device_platform_name AS "platform",
application_version_name AS "version name",
application_version_code AS "version code",
COUNT(DISTINCT client_id) AS "devices",
COUNT(DISTINCT client_cognito_id) AS "users"
FROM
AWSMA.v_event
WHERE
event_type = '_session.start' AND
event_timestamp BETWEEN getdate() - 30 AND getdate() + 1
GROUP BY
"app id",
"platform",
"version name",
"version code"
ORDER BY
"app id" ASC,
"platform" ASC,
"devices" DESC,
"version name" DESC,
"version code" DESC
;
SELECT
application_app_id AS "app id",
monetization_transaction_item_id AS "item id",
monetization_transaction_store AS "store",
COUNT(DISTINCT client_id) AS "devices",
COUNT(DISTINCT client_cognito_id) AS "users",
SUM(monetization_transaction_quantity) AS "quantity",
SUM(monetization_transaction_price_amount) "amount (Apple only)",
monetization_transaction_price_currency_code AS "currency (Apple only)"
FROM
AWSMA.v_event
WHERE
event_type = '_monetization.purchase' AND
event_timestamp BETWEEN getdate() - 30 AND getdate() + 1
GROUP BY
"app id",
"item id",
"currency (Apple only)",
"store"
ORDER BY
"app id" ASC,
"item id" ASC,
"quantity" DESC,
"store",
"devices" DESC
;
欢迎光临 168大数据 (http://www.bi168.cn/) | Powered by Discuz! X3.2 |