Big Data & BI : Social Travel Analysis – 3ième partie

  Ce billet est rédigé par Romain Casteres, consultant de la société DCube. Il documente les démonstrations réalisées lors de la session #BigData dans le cadre des Journées SQL Server 2012.

Je vous en souhaite une bonne lecture.

--Philippe

---

Cet article traite des processus pour capturer, transformer, enrichir et visualiser des données issues du réseau social Twitter depuis Windows Azure, Hadoop (Windows Azure HD Insight), SSIS (SQL Server Integration Services), SSAS (SQL Server Analysis Services), et Excel. Il aborde pour cela différents modes d’analyses.

Dans une première partie de cet article, nous avons illustré l’analyse des 5 dernières secondes via Stream Insight.

Nous nous sommes ensuite intéressés dans une seconde partie à l’analyse avec 0 préparation mais 13 h avant consultation des résultats. Nous avons montré dans ce cadre comment récupérer des Tweets en relation avec le voyage à partir d’un Worker Role dans Windows Azure pour les sauvegarder dans une Blob Storage Azure (espace de stockage dans le Cloud).

Dans cette troisième et dernière partie de l’article, nous abordons enfin l’analyse décisionnelle avec 6 mois de préparation, mais 1 seconde d’attente avant consultation des tableaux de bord. Je vous montrerai dans la suite de ce billet en particulier comment les intégrer dans un Framework Big Data pour les analyser depuis Excel.

Dans cette partie, je vais à partir de données mises en forme dans Hadoop (Windows Azure HDInsight) :

• Alimenter un système d’information décisionnelle,
• Rajouter de l’intelligence à nos données,
• Les mettre à disposition des utilisateurs via un cube de type tabulaire.

Création de tables Hive

drop table my_tweets;

create table my_tweets

(

id string,

created_at_year string,

created_at_month string,

created_at_day string,

created_at_time string,

text string,

is_a_retweet string,

retweet_count string,

longitude string,

latitude string,

source string,

id_user string,

screen_name string,

name string,

followers_count string,

friends_count string,

lang string,

profile_image_url string,

hashtags array<string>,

user_mentions array<string>

)

partitioned by (partition_key string);

set hive.exec.dynamic.partition=true;

set hive.exec.dynamic.partition.mode=nonstrict;

INSERT OVERWRITE TABLE my_tweets

partition (partition_key)

SELECT DISTINCT

    CASE

        WHEN LENGTH(id) = 0 then Null

        ELSE COALESCE(id, Null)

    END AS id,

        CASE

      WHEN LENGTH(created_at_year) = 0 then Null

        ELSE COALESCE(created_at_year, Null)

    END AS created_at_year,

        CASE

        WHEN LENGTH(created_at_month) = 0 then Null

        ELSE COALESCE(created_at_month, Null)

    END AS created_at_month,

        CASE

        WHEN LENGTH(created_at_day) = 0 then Null

        ELSE COALESCE(created_at_day, Null)

    END AS created_at_day,

        CASE

        WHEN LENGTH(created_at_time) = 0 then Null

        ELSE COALESCE(created_at_time, Null)

    END AS created_at_time,

    CASE

        WHEN LENGTH(text) = 0 then Null

        ELSE COALESCE(text, Null)

    END AS text,

        COALESCE(is_a_retweet, Null) AS is_a_retweet,

        CASE

        WHEN LENGTH(retweet_count) = 0 then Null

        ELSE COALESCE(retweet_count, Null)

  END AS retweet_count,

        CASE

        WHEN LENGTH(get_json_object(coordinates, '$.coordinates[0]')) = 0 then Null

        ELSE COALESCE(get_json_object(coordinates, '$.coordinates[0]'), Null)

    END AS longitude,

        CASE

        WHEN LENGTH(get_json_object(coordinates, '$.coordinates[1]')) = 0 then Null

        ELSE COALESCE(get_json_object(coordinates, '$.coordinates[1]'), Null)

    END AS latitude,

    CASE

        WHEN LENGTH(split(source, '>')[1]) = 0 then Null

        ELSE COALESCE(split(source, '>')[1], Null)

    END AS source,

        CASE

        WHEN LENGTH(id_user) = 0 then Null

        ELSE COALESCE(id_user, Null)

    END AS id_user,

        CASE

        WHEN LENGTH(screen_name) = 0 then Null

        ELSE COALESCE(screen_name, Null)

    END AS screen_name,

        CASE

        WHEN LENGTH(name) = 0 then Null

        ELSE COALESCE(name, Null)

    END AS name,

        CASE

        WHEN LENGTH(followers_count) = 0 then Null

        ELSE COALESCE(followers_count, Null)

    END AS followers_count,

    CASE

        WHEN LENGTH(friends_count) = 0 then Null

        ELSE COALESCE(friends_count, Null)

    END AS friends_count,

        CASE

        WHEN LENGTH(lang) = 0 then Null

        ELSE COALESCE(lang, Null)

    END AS lang,

        CASE

        WHEN LENGTH(profile_image_url) = 0 then Null

        ELSE COALESCE(profile_image_url, Null)

    END AS profile_image_url,

Hashtags,

user_mentions,

partition_key

FROM tweets

where (coordinates is not null) AND (followers_count > 800);

Remarque : On sélectionne les Tweets des personnes les plus influentes à savoir ceux qui ont plus de 800 Followers.

Comme un Tweet peut avoir plusieurs Tags et un Tag peut apparaître dans plusieurs Tweets, on associe l’identifiant d’un Tweet à ses différents Tags sur plusieurs lignes.

drop table twitter_tags;

create table twitter_tags

(id string, tag string)

partitioned by (partition_key string);

 

insert overwrite table twitter_tags

partition (partition_key)

select

id,

tag,

partition_key

from my_tweets

LATERAL VIEW explode(hashtags) tagTable as tag

where length(coalesce(tag,'')) > 0;

De même pour les Mentions :

drop table twitter_mentions;

create table twitter_mentions

(id string, mention string) partitioned by (partition_key string);

 

insert overwrite table twitter_mentions

partition (partition_key)

select

id,

mention,

partition_key

from my_tweets

LATERAL VIEW explode(user_mentions) mentionsTable as mention

where length(coalesce(mention,'')) > 0;

Chargement d’une base de données locale

Nous allons utiliser SSIS (SQL Server Integration Services) afin d’alimenter les tables temporaires suivantes :

Télécharger le Script T-SQL de création des tables : CREATE_ODS_TABLES.sql

Création d’une connexion de type ADO.NET et alimentation des tables :

        

Récupération des sentiments des Tweets

Sentiment140 est une API en ligne permettant la récupération des sentiments des Tweets que nous lui passons en paramètre :

L’API attend le format JSON suivant :

{"data": [{"text": "I love Titanic.", "id": 1234},
{"text": "I hate Titanic.", "id": 4567}]}

Et sa réponse sera de la forme :

{"data": [{"text": "I love Titanic.", "id": 1234, "polarity":4},

        {"text": "I hate Titanic.", "id": 4567, "polarity":0}]}

Les valeurs de Polarity sont :

• 0 pour un sentiment négatif ;
• 2 pour un sentiment neuter ;
• 4 pour un sentiment positif ;

Le mode gratuit limite l’envoie des fichiers, nous allons donc découper en N fichiers de 10 000 lignes la totalité des Tweets :

Pour chaque fichier découpé, la tâche de script VB « Load & Download Sentiment140 » envoie le fichier au format attendu et télécharge la réponse de l’API.

Pour chacun des fichiers reçus : une base de données temporaire est alimentée et une sauvegarde est réalisée sur le Blob Storage Azure.

Pour effectuer la sauvegarde dans l’ASV, j’ai installé les composants développés par Rémi Olivier : https://code.msdn.microsoft.com/SSIS-Packages-Sample-for-2ffd9c32

Voir Livre Blanc : Leveraging a Hadoop cluster from SQL Server Integration Services (SSIS)

Télécharger le projet SSIS : JSS2012 SSIS.rar

Chargement d’un DataWarehouse

Voici le MPD :

Télécharger le Script T-SQL de création des tables : CREATE_DWH_TABLES.sql

Afin d’alimenter les dimensions géographique j’ai utilisé une source de données provenant de l’Azure Data Market : Worldwide Historical Weather Data

Télécharger la source : Weather Data Stations.csv

Notez que la dimension DIM_CITY comporte une colonne calculée persistante CIT_GEOGRAPHY de type ‘Geography’ : ([geography]::Point([CIT_LATITUDE],[CIT_LONGITUTE],(4326)))

Chaque ville est maintenant représentée par un point géo-spatial.

Certains Tweets ont été géo-localisés et possèdent une latitude et une longitude. Grâce à la procédure suivante (à créer dans notre DataWarehouse), nous allons pouvoir lier les Tweets aux villes les plus proches :

CREATE FUNCTION [dbo].[ReturnCity] (@longitude nvarchar(500), @latitude nvarchar(500))

 RETURNS Int

 BEGIN

 DECLARE @ID_CITY as Int

 SELECT TOP (1) @ID_CITY= [DIM_CITY].[PK_ID_CITY] FROM [DIM_CITY]

 WHERE [CIT_GEOGRAPHY].STDistance(geography::Point(@latitude,@longitude, 4326)) IS NOT NULL

 ORDER BY [CIT_GEOGRAPHY].STDistance(geography::Point(@latitude, @longitude, 4326))

 RETURN @ID_CITY

 END

Lors de l’intégration des données dans la table de fait « FT_ACTIVITY » nous feront appel à la procédure pour récupérer les identifiants des villes les plus proches.

Création d’un cube Tabular

Dans SQL Server Data Tools, créez un nouveau projet de type Tabulaire et importez les dimensions et la table de faits :

Importation de données provenant de Windows Azure marketplace

La source de données Weather Trends International contient un historique des températures quotidiennes, des précipitations, la vitesse du vent, … Pour la plupart des villes du monde !

Nous allons importer dans notre cube la table GetDailyHistoricalData via l’assistant Windows Azure Data Market :

  

Voici le résultat :

Ajout de nouvelles mesure et colonnes calculées en DAX

Dans la table de fait :

• Ajout d’une colonne calculée « Sentiment »

=IF([ACT_SENTIMENT140]=1;"Positive";IF([ACT_SENTIMENT140]=0;"Neutral";"Negative"))

• Ajout d’une colonne calculée « Degre_C »

=LOOKUPVALUE(

DIM_WEATHER[Degre_C];

DIM_WEATHER[DateTime]; RELATED(DIM_TIME[Date]);

DIM_WEATHER[Location]; FT_ACTIVITY[FK_ID_CITY]

)

• Ajouts de nouvelles mesures :

Total Tweets:=COUNTROWS(DISTINCT(FT_ACTIVITY[PK_ID_ACT]))

Total Hashtags:=CALCULATE(

    COUNT(REF_ACT_TAG[FK_ID_TAG]);

    SUMMARIZE(REF_ACT_TAG;FT_ACTIVITY[PK_ID_ACT])

)

• …

Voici le schéma final de mon cube :

Télécharger la source : JSS2012 SSAS.rar

Navigation dans le cube via Excel

Après avoir déployé le cube, connectez-vous à celui-ci depuis Excel et créez des tableaux et graphiques croisés dynamiques :

Ajout de rapports Power View :

On remarque que lorsque la température augmente à Atlanta, le nombre de Tweets positifs augmente :)

Utilisation de la visualisation Scatter avec animation sur la dimension Temps (par heure):

Répartition des Tweets par jour de la semaine :

Télécharger la source : MySocialTravel.xlsx

Complémentarité Big Data & BI

Création d’une nouvelle table Hive :

drop table top_twitter_info;

create table top_twitter_info

(

        id_user int,

        created_at string,

        url string,

        description string ,

        followers_count int

);

 

insert overwrite table top_twitter_info

SELECT DISTINCT

        CASE

        WHEN LENGTH(get_json_object(json_response, '$.user.id')) = 0 then Null

        ELSE COALESCE(get_json_object(json_response, '$.user.id'), Null)

    END AS id_user,

        concat(

        substr (get_json_object(json_response, '$.user.created_at'),5,3)

        ,' ',

        substr (get_json_object(json_response, '$.user.created_at'),27,4)

        ) AS created_at,

        CASE

        WHEN LENGTH(get_json_object(json_response, '$.user.url')) = 0 then Null

        ELSE COALESCE(get_json_object(json_response, '$.user.url'), Null)

    END AS url,

        CASE

        WHEN LENGTH(get_json_object(json_response, '$.user.description')) = 0 then Null

        ELSE COALESCE(get_json_object(json_response, '$.user.description'), Null)

    END AS description

FROM raw_tweets

ORDER BY followers_count DESC

LIMIT 10

Pour montrer la complémentarité entre la Big Data et la Business Intelligence, prenons un exemple :

Un utilisateur cherche à analyser les utilisateurs les plus influents. Pour faire cela, il crée un tableau croisé dynamique comme celui-ci :

Suite à la lecture du tableau, il se demande en combien de temps OscarDLeon a développé son E-Réputation ?

Problématique : les dates de création des comptes Tweeters des utilisateurs n’a pas été rapatriées dans le cube !

Qu'à cela ne tienne ! Les données sont toujours présentes dans notre cluster Hadoop et depuis Excel l’utilisateur va pouvoir consolider les données issues du cube et du cluster Hadoop !

Remarque : Je pense que dans un avenir proche les données de granularité fine seront stockées dans des frameworks Big Data (car peu chers), qu’elles seront pour les plus intéressantes d’entre elles, regroupées, consolidées et agrégées à des fins de Reporting. Les utilisateurs finaux auront accès aux données brutes, non agrégées et non prises en compte par les SID (Système d’Information Décisionnelle) et ils n’auront pas forcément besoin de l’IT pour les analyser (0 préparation, mais 13 h avant consultation des résultats).

Bonus : NodeXL

NodeXL est un add-in Excel permettant d’analyser sous forme de graphiques les réseaux sociaux. Nous allons l’utiliser sur nos données issues d’Hadoop pour mettre à disposition des utilisateurs une nouvelle forme de restitution.

Vous pouvez le télécharger gratuitement ICI et apprendre à l’utiliser avec le livre Analyzing Social Media Networks with NodeXL.

J’ai, dans un premier temps, créé et alimenté deux tables dans SQL Server :

• Edges : elle contient la liste des personnes ayant twitté le mot Travel, le nombre d’occurrences du mot et la moyenne des sentiments des tweets associés.
• Vertices : elle contient les personnes ayant twitté le mot Travel et leurs avatars.

Télécharger la source : CREATE_NODEXL_TABLES.sql

Configuration des connections :

Et voici le résultat :

• Le mot Travel est au centre du graph et les personnes l’ayant tweeté gravite autour.
• L’épaisseur des liens est proportionnelle aux nombres de fois que le mot Travel apparaît dans leurs Tweets.
• La couleur des liens représente une moyenne des sentiments qu’il ressort des Tweets ayant le mot Travel.

Télécharger le fichier Excel : NodeXL - SocialTravel.xlsx

En guise de conclusion

Avec l'explosion des données le business de la Big Data est en pleine expansion. Certaines données se monétise et d’autres deviennent publiques, le déluge est belle et bien lancé !

Mais toutes les données ne sont pas porteuses de la même valeur, l'enjeu sera donc de pouvoir les hiérarchiser.

Je pense que les données seront de plus en plus utilisées à d'autres fins que celles pour lesquelles elles ont été produites ! La Big Data offre cette capacité d’analyse et de prédictions, les pronostics parlent d’eux même :

Vous pourrez retrouver aux prochains Microsoft TechDays 2013 plusieurs sessions sur le BigData, je vous y donne rendez-vous !

Romain Casteres

---

Le site Web des Microsoft TechDays 2013 est en ligne. Vous pouvez encore vous inscrire et participez gratuitement à ces sessions, ainsi qu’à plus de 300 autres conférences techniques et décideurs pour répondre à toutes les problématiques actuelles de l’informatiq ue.