Show Menu
主题×

Jupyter笔记本中的查询服务

Adobe Experience Platform 通过将查询服务作为标准功能集成到JupyterLab中,允许您在数据科学工作区中使用结构化查询语言(SQL)。
本教程演示了用于浏览、转换和分析数据的常见用例的SQL查询 Adobe Analytics 示例。

入门指南

在开始本教程之前,您必须具有以下先决条件:

访问JupyterLab和查询服务

  1. Experience Platform ,从左 侧导航 列导航到“笔记本”。 请稍等片刻,JupyterLab将加载。
    如果未自动显示新的启动器选项卡,请通过单击文件打开新的启动器选项卡, 然后选 择新 建启动器
  2. 在“启动器”选项卡中, 单击 Python 3环境中的“空白”图标以打开空笔记本。
    Python 3目前是笔记本电脑中唯一受支持的查询服务环境。
  3. 在左侧选择边栏上,单击“数 ”图标,多次单 击“数据集 ”目录以列表所有数据集。
  4. 查找要 Adobe Analytics 浏览的数据集并右键单击列表,单击“笔记本 中的查询数据 ”,在空笔记本中生成SQL查询。
  5. 单击包含该函数的第一个生成的单 qs_connect() 元格,并通过单击播放按钮来执行它。 此函数在笔记本实例与查询服务之间建立连接。
  6. 从第二个生 Adobe Analytics 成的SQL查询中复制数据集名称,它将是后面的值 FROM
  7. 单击+按钮插入新的笔记本 单元 格。
  8. 在新单元格中复制、粘贴和执行以下导入语句。 这些语句将用于可视化您的数据:
    import plotly.plotly as py
    import plotly.graph_objs as go
    from plotly.offline import iplot
    
    
  9. 然后,在新单元格中复制并粘贴以下变量。 根据需要修改其值,然后执行它们。
    target_table = "your Adobe Analytics dataset name"
    target_year = "2019"
    target_month = "04"
    target_day = "01"
    
    
    • target_table : 数据集的 Adobe Analytics 名称。
    • target_year : 目标数据的特定年份。
    • target_month : 目标的特定月份。
    • target_day : 目标数据的特定日期。
    您可以随时更改这些值。 执行此操作时,请确保为要应用的更改执行变量单元格。

查询数据

在单个笔记本单元格中输入以下SQL查询。 通过单击查询的单元格,然后单击播放按 。 成功的查询结果或错误日志显示在执行的单元格下方。
当笔记本处于长期非活动状态时,笔记本与查询服务之间的连接可能会中断。 在这种情况下,请单击右上角的 Power (电源)按钮,重新启动JupyterLab。
笔记本内核将重置,但单元格将保留,重 新运行 所有单元格以继续您离开的位置。

每小时访客计数

以下查询返回指定日期的小时访客计数:

查询

%%read_sql hourly_visitor -c QS_CONNECTION
SELECT Substring(timestamp, 1, 10)                               AS Day,
       Substring(timestamp, 12, 2)                               AS Hour, 
       Count(DISTINCT concat(enduserids._experience.aaid.id, 
                             _experience.analytics.session.num)) AS Visit_Count 
FROM   {target_table}
WHERE _acp_year = {target_year} 
      AND _acp_month = {target_month}  
      AND _acp_day = {target_day}
GROUP  BY Day, Hour
ORDER  BY Hour;

在上述查询中,子 _acp_year 句中 WHERE 的目标设置为值 target_year 。 通过将变量包含在大括号()中,在SQL查询中 {} 包括变量。
查询的第一行包含可选变量 hourly_visitor 。 查询结果将作为Pactys数据帧存储在此变量中。 将结果存储在查询帧中允许您以后使用所需的Python包可视化数据结果。 在新单元格中执行以下Python代码以生成条形图:
trace = go.Bar(
    x = hourly_visitor['Hour'],
    y = hourly_visitor['Visit_Count'],
    name = "Visitor Count"
)
layout = go.Layout(
    title = 'Visit Count by Hour of Day',
    width = 1200,
    height = 600,
    xaxis = dict(title = 'Hour of Day'),
    yaxis = dict(title = 'Count')
)
fig = go.Figure(data = [trace], layout = layout)
iplot(fig)

每小时活动计数

以下查询返回指定日期的小时活动计数:

查询

%%read_sql hourly_actions -d -c QS_CONNECTION
SELECT Substring(timestamp, 1, 10)                        AS Day,
       Substring(timestamp, 12, 2)                        AS Hour, 
       Count(concat(enduserids._experience.aaid.id, 
                    _experience.analytics.session.num,
                    _experience.analytics.session.depth)) AS Count 
FROM   {target_table}
WHERE  _acp_year = {target_year} 
       AND _acp_month = {target_month}  
       AND _acp_day = {target_day}
GROUP  BY Day, Hour
ORDER  BY Hour;

执行上述查询会将结果存储 hourly_actions 为数据帧。 在新单元格中执行以下函数以预览结果:
hourly_actions.head()

可以修改上述查询,以使用WHERE子句中的逻辑运算符返回指定日期范围的每小时 操作 计数:

查询

%%read_sql hourly_actions_date_range -d -c QS_CONNECTION
SELECT Substring(timestamp, 1, 10)                        AS Day,
       Substring(timestamp, 12, 2)                        AS Hour, 
       Count(concat(enduserids._experience.aaid.id, 
                    _experience.analytics.session.num,
                    _experience.analytics.session.depth)) AS Count 
FROM   {target_table}
WHERE  timestamp >= TO_TIMESTAMP('2019-06-01 00', 'YYYY-MM-DD HH')
       AND timestamp <= TO_TIMESTAMP('2019-06-02 23', 'YYYY-MM-DD HH')
GROUP  BY Day, Hour
ORDER  BY Hour;

执行修改后的查询会将结果存储 hourly_actions_date_range 为数据帧。 在新单元格中执行以下函数以预览结果:
hourly_actions_date_rage.head()

每个事件会话的访客数

以下查询返回指定日期的每个访客会话的事件数:

查询

%%read_sql events_per_session -c QS_CONNECTION
SELECT concat(enduserids._experience.aaid.id, 
              '-#', 
              _experience.analytics.session.num) AS aaid_sess_key, 
       Count(timestamp)                          AS Count 
FROM   {target_table}
WHERE  _acp_year = {target_year} 
       AND _acp_month = {target_month}  
       AND _acp_day = {target_day}
GROUP BY aaid_sess_key
ORDER BY Count DESC;

执行以下Python代码,为每次访问会话的事件数生成直方图:
data = [go.Histogram(x = events_per_session['Count'])]

layout = go.Layout(
    title = 'Histogram of Number of Events per Visit Session',
    xaxis = dict(title = 'Number of Events'),
    yaxis = dict(title = 'Count')
)

fig = go.Figure(data = data, layout = layout)
iplot(fig)

给定日期的活动用户数

以下查询返回指定日期的十个最活跃的用户:

查询

%%read_sql active_users -c QS_CONNECTION
SELECT enduserids._experience.aaid.id AS aaid, 
       Count(timestamp)               AS Count
FROM   {target_table}
WHERE  _acp_year = {target_year}
       AND _acp_month = {target_month}
       AND _acp_day = {target_day}
GROUP  BY aaid
ORDER  BY Count DESC
LIMIT  10;

按用户活动划分的活跃城市

以下查询返回在指定日期生成大多数用户活动的十个城市:

查询

%%read_sql active_cities -c QS_CONNECTION
SELECT concat(placeContext.geo.stateProvince, ' - ', placeContext.geo.city) AS state_city, 
       Count(timestamp)                                                     AS Count
FROM   {target_table}
WHERE  _acp_year = {target_year}
       AND _acp_month = {target_month}
       AND _acp_day = {target_day}
GROUP  BY state_city
ORDER  BY Count DESC
LIMIT  10;

后续步骤

本教程演示了在Jupyter笔记本中利用查询服务的一些示例用例。 请按照 使用Jupyter Notebooks教程分析数据 ,了解如何使用Data Access SDK执行类似操作。