Générer un identifiant de session grâce à DuckDB

A travers cet article, je vais vous montrer comment corréler des données évènementielles autours d’un identifiant virtuel.

Dans mon exemple, les données évènementielles sont les requêtes de nos utilisateurs, où nous souhaitons reproduire la notion de session définit par Google Analytics.

Voici l’interprétation que l’on retiendra pour une session :

Google Analytics définit une session comme un groupe de données enregistrées au cours d’une seule visite sur le site.
Une session commence lorsque l’utilisateur arrive pour la première fois sur le site et se termine après 30 minutes d’inactivité suivant la dernière requête du même utilisateur.

Les données brutes représentent plusieurs dizaines de millions de lignes, réparties au sein de plusieurs fichiers .parquet dont voici la structure utile à notre sujet :

Le visitor_id est un identifiant que l’on attribut à un utilisateur par l’intermédiaire d’un cookie, il est conservé sur une période dépassant largement la notion de session.

L’objectif est donc d’ajouter une nouvelle colonne contenant un session_id commun à l’ensemble des requêtes répondant à la notion d’une session.

Le résultat final doit ressembler à cela :

L’approche technique

Le processus est découpé en plusieurs étapes, organisées techniquement par des CTE.

1. Création d’une colonne qui indique le nombre de minutes séparant deux requêtes consécutives pour un même utilisateur : lag_time
2. Attribution d’un ID à la première requête de session : start_session_id. Pour déterminer cette première requête, il suffit de voir si la requête précédente a plus de 30min ou s’il n’y a pas de précédent.
3. Propagation du l’ID aux requêtes suivantes en créant une nouvelle colonne session_id dont la valeur est fonction de deux cas :
   1. Si la requête courante possède un start_session_id, on utilise cette valeur.
   2. Si non, on utilise la première valeur start_session_id non NULL des requêtes précédentes.

Pour pouvoir se déplacer sur la requête précédente, il faut utiliser la fonction lag des WINDOW Functions et ignorer les valeurs NULL, ce que DuckDB nous permet de faire 😇.

La requête SQL

with  
  
source as (  
    select  
        id as request_id,  
        date,  
        visitor_id  
    from 'seeds/tracking-visits/2023-*.parquet'  
),  
  
requests as (  
    select  
        request_id,  
        visitor_id,  
        date,  
        date_diff(  
            'minute',  
            lag(date) over (  
                partition by visitor_id  
                order by date  
            ),  
            date
        ) as lag_time -- minutes séparant deux requêtes consécutives du même utilisateur.  
    from source  
  
),  
  
start_session_flagged as (  
    select  
        *,  
        if(  
            (lag_time is null or lag_time > 30),  
            sha256(concat(date, visitor_id)),  
            null  
        ) as start_session_id -- création d'un identifiant prédictible pour le début de session
    from requests  
),  
  
session_flagged as (  
    select  
        *,  
        if (
            start_session_id is not null,
            start_session_id,  
            lag(start_session_id, 1 IGNORE NULLS) OVER (  
                partition by visitor_id  
                order by date  
            )  
        ) as session_id -- transposition de l'identifiant de session
    from start_session_flagged  
)  
  
select * EXCLUDE (lag_time, start_session_id)  
from session_flagged

Encore une fois DuckDB 🔥🔥🔥

J’utilise DuckDB depuis plusieurs mois pour réaliser deux types de traitement :

• Construction d’analyses ad-hoc, principalement via Jupyter Notebook
• Améliorer la qualité des données brutes avant ingestion dans le data-warehouse, ces tâches sont quotidiennes et orchestrées par Kestra.

Comme à chaque fois que je manipule des millions de ligne en moins de 10 secondes, j’ai cet effet Wahou si caractéristique de DuckDB.
Pour rappel : 30M de lignes sur 12 fichiers .parquet qui représentent 1.5GB sur disque, le processus ci-dessus c’est moins de 8 secondes 🤩.

Outre ces performances de folies, j’ai pu faire les traitements directement sur mon environnement de travail sans aucune liaison avec Internet, niveau productivité c’est imbatable !