Servicio de consultas en Jupyter Notebook

Adobe Experience Platform le permite usar el Lenguaje de consulta estructurado (SQL) en Data Science Workspace al integrar Query Service en JupyterLab como característica estándar.

Este tutorial muestra consultas SQL de ejemplo para casos de uso comunes con el fin de explorar, transformar y analizar datos de Adobe Analytics.

Introducción

Antes de iniciar este tutorial, debe cumplir los siguientes requisitos previos:

Acceder a JupyterLab y Query Service access-jupyterlab-and-query-service

Consulta de datos query-your-data

Introduzca las siguientes consultas SQL en celdas individuales del bloc de notas. Ejecute una consulta seleccionando en su celda y después el botón reproducir. Los resultados de la consulta o los registros de errores correctos se muestran debajo de la celda ejecutada.

Cuando un bloc de notas está inactivo durante un período de tiempo prolongado, la conexión entre el bloc de notas y Query Service puede romperse. En estos casos, reinicie JupyterLab seleccionando el botón Reiniciar situado en la esquina superior derecha junto al botón de encendido.

El núcleo del bloc de notas se restablece, pero las celdas permanecen, vuelva a ejecutar todas las celdas para continuar donde lo dejó.

Recuento de visitantes por hora hourly-visitor-count

La siguiente consulta devuelve el recuento de visitantes por hora para una fecha especificada:

Consulta

%%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 TIMESTAMP = to_timestamp('{target_year}-{target_month}-{target_day}')
GROUP  BY Day, Hour
ORDER  BY Hour;

En la consulta anterior, la marca de tiempo de la cláusula WHERE está establecida para ser el valor de target_year. Incluya variables en consultas SQL al contenerlas entre llaves ({}).

La primera línea de la consulta contiene la variable opcional hourly_visitor. Los resultados de la consulta se almacenarán en esta variable como un marco de datos Pandas. Almacenar resultados en un marco de datos le permite visualizar posteriormente los resultados de la consulta utilizando un paquete Python deseado. Ejecute el siguiente código Python en una nueva celda para generar un gráfico de barras:

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)

Recuento de actividades por hora hourly-activity-count

La siguiente consulta devuelve el recuento de acciones por hora para una fecha especificada:

Consulta

%%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 TIMESTAMP = to_timestamp('{target_year}-{target_month}-{target_day}')
GROUP  BY Day, Hour
ORDER  BY Hour;

Si se ejecuta la consulta anterior, los resultados se almacenarán en hourly_actions como un marco de datos. Ejecute la siguiente función en una nueva celda para previsualizar los resultados:

hourly_actions.head()

La consulta anterior se puede modificar para que devuelva el recuento de acciones por hora de un intervalo de fechas especificado mediante operadores lógicos en la cláusula WHERE:

Consulta

%%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;

La ejecución de la consulta modificada almacena los resultados en hourly_actions_date_range como un marco de datos. Ejecute la siguiente función en una nueva celda para previsualizar los resultados:

hourly_actions_date_rage.head()

Número de eventos por sesión de visitante number-of-events-per-visitor-session

La siguiente consulta devuelve el número de eventos por sesión de visitante para una fecha especificada:

Consulta

%%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 TIMESTAMP = to_timestamp('{target_year}-{target_month}-{target_day}')
GROUP BY aaid_sess_key
ORDER BY Count DESC;

Ejecute el siguiente código Python para generar un histograma para la cantidad de eventos por sesión de visita:

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)

Páginas populares para un día determinado popular-pages-for-a-given-day

La siguiente consulta devuelve las diez páginas más populares para una fecha especificada:

Consulta

%%read_sql popular_pages -c QS_CONNECTION
SELECT web.webpagedetails.name                 AS Page_Name,
       Sum(web.webpagedetails.pageviews.value) AS Page_Views
FROM   {target_table}
WHERE TIMESTAMP = to_timestamp('{target_year}-{target_month}-{target_day}')
GROUP  BY web.webpagedetails.name
ORDER  BY page_views DESC
LIMIT  10;

Usuarios activos durante un día determinado active-users-for-a-given-day

La siguiente consulta devuelve los diez usuarios más activos para una fecha especificada:

Consulta

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

Ciudades activas por actividad de usuario active-cities-by-user-activity

La siguiente consulta devuelve las diez ciudades que están generando la mayoría de las actividades de usuario para una fecha especificada:

Consulta

%%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 TIMESTAMP = to_timestamp('{target_year}-{target_month}-{target_day}')
GROUP  BY state_city
ORDER  BY Count DESC
LIMIT  10;

Pasos siguientes

Este tutorial muestra algunos casos de uso de ejemplo para utilizar Query Service en Jupyter blocs de notas. Siga el tutorial de Analizar sus datos con Jupyter Notebooks para ver cómo se realizan operaciones similares mediante el SDK de acceso a datos.