Servizio query in Jupyter Notebook

Adobe Experience Platform consente di utilizzare SQL (Structured Query Language) in Data Science Workspace integrando Query Service in JupyterLab come funzionalità standard.

Questo tutorial illustra query SQL di esempio per casi d'uso comuni per esplorare, trasformare e analizzare i dati Adobe Analytics.

Introduzione

Prima di avviare questa esercitazione, è necessario disporre dei seguenti prerequisiti:

Accedi a JupyterLab e Query Service access-jupyterlab-and-query-service

Eseguire una query sui dati query-your-data

Immettere le seguenti query SQL nelle singole celle del blocco appunti. Esegui una query selezionando la relativa cella e quindi il pulsante play. I risultati della query o i registri di errore vengono visualizzati sotto la cella eseguita.

Quando un blocco appunti è inattivo per un periodo di tempo prolungato, la connessione tra il blocco appunti e Query Service potrebbe interrompersi. In questi casi, riavviare JupyterLab selezionando il pulsante Riavvia nell'angolo superiore destro accanto al pulsante di alimentazione.

Il kernel del notebook viene ripristinato, ma le celle rimangono, quindi riesegui tutte le celle per continuare da dove hai lasciato.

Numero di visitatori orari hourly-visitor-count

La query seguente restituisce il conteggio dei visitatori orari per una data specificata:

Query

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

Nella query precedente, la marca temporale nella clausola WHERE è impostata sul valore di target_year. Includere le variabili nelle query SQL includendole tra parentesi graffe ({}).

La prima riga della query contiene la variabile facoltativa hourly_visitor. I risultati della query verranno memorizzati in questa variabile come un dataframe Pandas. L'archiviazione dei risultati in un dataframe consente di visualizzare i risultati della query in un secondo momento utilizzando il pacchetto Python desiderato. Eseguire il codice Python seguente in una nuova cella per generare un grafico a barre:

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)

Conteggio attività orarie hourly-activity-count

La query seguente restituisce il conteggio delle azioni orarie per una data specificata:

Query

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

L'esecuzione della query precedente memorizzerà i risultati in hourly_actions come un dataframe. Esegui la funzione seguente in una nuova cella per visualizzare in anteprima i risultati:

hourly_actions.head()

È possibile modificare la query precedente per restituire il conteggio delle azioni orarie per un intervallo di date specificato utilizzando operatori logici nella clausola WHERE:

Query

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

L'esecuzione della query modificata memorizza i risultati in hourly_actions_date_range come un dataframe. Esegui la funzione seguente in una nuova cella per visualizzare in anteprima i risultati:

hourly_actions_date_rage.head()

Numero di eventi per sessione visitatore number-of-events-per-visitor-session

La query seguente restituisce il numero di eventi per sessione visitatore per una data specificata:

Query

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

Eseguire il codice Python seguente per generare un istogramma per il numero di eventi per sessione di 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)

Pagine popolari per un dato giorno popular-pages-for-a-given-day

La query seguente restituisce le dieci pagine più popolari per una data specificata:

Query

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

Utenti attivi per un dato giorno active-users-for-a-given-day

La query seguente restituisce i dieci utenti più attivi per una data specificata:

Query

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

Città attive per attività utente active-cities-by-user-activity

La query seguente restituisce le dieci città che generano la maggior parte delle attività utente per una data specificata:

Query

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

Passaggi successivi

In questo tutorial sono stati illustrati alcuni esempi di utilizzo di Query Service in Jupyter notebook. Segui l'esercitazione Analizza i tuoi dati utilizzando Jupyter Notebooks per vedere come vengono eseguite operazioni simili utilizzando l'SDK di accesso ai dati.