Discussion :

[Keyboardist]

Bonjour à tous,

dans le cadre d'un projet, je dois implémenter une application de data mining dans un contexte de sécurité Web.

En effet, le but de ce projet est de récupérer un gros volume de log Apache (+20 millions de lignes/jour) et d'extraire des informations pertinentes (provenance des IP, listes des user-agents, etc.) afin de déterminer des patterns d'attaques potentielles (DDOS, etc.).

Une fois les données traitées (il restera certainement une bonne douzaine de millions de lignes/jour), je voudrais pouvoir les enregistrer dans une BD.

J'ai commencé à regarder les solutions usuelles qui utilisent du relationnel (MySQL, PostgreSQL, H2, etc.), mais je me demandais si vous aviez un avis sur d'autres types de modèle (NoSQL par exemple) qui serait peut être plus pertinents au niveau des performances, de la gestion d'une telle volumétrie ou encore de la facilité d'implémentation et de maintenance.

Au niveau de ce que je veux stocker, voilà typiquement le format de log que je récupère (les logs Apache sont formalisés):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

127.0.0.1 - frank [10/Oct/2000:13:55:36 -0700] "GET /apache_pb.gif HTTP/1.0" 200 2326 "http://www.example.com/start.html" "Mozilla/4.08 [en] (Win98; I ;Nav)"

Une fois enregistrés après traitement (imaginons par exemple que le referer ne m'intéresse pas), les logs passeront dans une moulinette de Data Mining (pas d'algo. en vue pour l'instant) pour générer de la connaissance (combien de fois l'utilisateur s'est connecté dans un certain laps de temps? provenance géographique des IP? etc.).

In fine, je pense que la BD (si je pars sur du relationnel) aura plusieurs dizaines de millions de lignes, une douzaine de colonnes par tables et quelques tables liées (une table pour les logs traitées, une table pour les IP, etc.)

Je sais que la réponse à ma question deviendra plus claire une fois que j'aurais bien dessiné la structure de la BD (ce que je garde/ce que je ne garde pas, comment j'agrège les données, etc.), mais j'ai néanmoins quelques questions pour être sûr que je ne part pas dans la mauvaise direction:
- Selon vous, est-ce qu'un modèle relationnel est adéquat pour ce type de traitement?
- Si oui, est ce qu'une solution comme MySQL pourrait supporter la volumétrie (il faudrait garder l'historique des logs au moins sur 3 semaines)

Au niveau des contraintes:
- Je pense implémenter en Python (de préférence, mais rien de 100% obligatoire)
- Il faudrait un SGBD libre
- Il faudrait si possible que l'on puisse compresser les tables (mais rien d'obligatoire non plus).

Au niveau Python, j'ai des vues sur PyTables (http://pytables.github.com/index.html) qui pourrait répondre aux contraintes volumétriques, mais qui pourrait ne pas être adéquat au niveau schéma de BD (c'est du hiérarchique).

Pour éviter les problèmes, je tiens à mentionner que je ne cherche pas de choses toutes faites (par exemple des analyseurs de logs Apache en GUI), il me faudrait juste un avis objectif sur la viabilité d'une utilisation d'un SGBD relationnel pour mon cas d'utilisation.

Merci d'avance pour vos réponses.
Amicalement.

[SQLpro]

Ce qui est sûr c'est qu'avec un volume aussi important il faut utiliser un SGBD décisionnel et non un SGBDR relationnel. Donc exit MySQL, PostGreSQL... Quand au NoSQL ce n'est pas non plus fait pour du décisionnel....

Donc il vous reste des outils comme MS SQL Server avec Analysis Services (sans doute la solution la moins cher), terradata ou Oracle décisionnel.

Sachez que sur de tels volumes avec les vues indexées, le stockage OLAP (matrice creuses) les, pré calculs stockées, les temps de réponses standards sont de quelques secondes au plus pour des centaines de millions de lignes à brasser.
Le tout étant de faire un bon modèle de données en étoile.

A lire : http://www.legrandbi.com/2011/01/gar...drant-bi-2011/

A +

[Keyboardist]

Merci pour cette réponse rapide,
je vais regarder ça.

Effectivement, le BI semble être adapté à cette volumétrie.

[Jester]

In fine, je pense que la BD (si je pars sur du relationnel) aura plusieurs dizaines de millions de lignes, une douzaine de colonnes par tables et quelques tables liées (une table pour les logs traitées, une table pour les IP, etc.)

C'est moins que ce que j'ai et je fonctionne sur un postgresql sur un serveur très peu performant. Ca peut prendre du temps (le scan de 150M de lignes peut prendre 2 minutes), mais j'ai des pré-aggrégations qui couvrent la plupart des requêtes usuelles. J'ai prototypé une migration sur Greenplum (fork de postgresql pour la BI) et un serveur plus "normal" et c'est vraiment très performant.

Je connais d'autres personnes faisant de même sur MySQL. MySQL sera à la traine pour des requêtes expertes en SQL.

Votre "génération de la connaissance" se rapproche fortement de ce que je fait dans de simple requêtes SQL via un ETL pour enrichir les données. Analysis Services ne sera probablement pas utile (je ne vois pas du moins).

[Keyboardist]

Mea culpa, j'ai oublié de préciser que je ne veux pas faire de scan "on the fly" (type Intrusion Detection System) mais de l'analyse journalière à partir de log déjà produit (par exemple les log d'hier).

Donc au niveau du temps de calcul, je peux me satisfaire d'un résultat qui sort en quelques heures (entre le moment ou je passe les logs et le moment ou je récupère le rapport, c'est à dire en prenant en compte la partie filtrage des logs, data mining, génération et enregistrement de la "connaissance", etc.).

En tout cas merci de votre aide

[Keyboardist]

Bonjour,

j'ai commencé à jouer avec PostgreSQL et Python pour mon projet et je suis en train de faire un modèle en étoile.

J'ai découpé mes logs de façon à avoir une table de faits ne contenant que des clés étrangères:

faits (#ID_temps, #ID_request, #ID_HTTP_infos, #ID_source, etc.)

ID_temps: clé étrangère d'une table dimension pour avoir une vision temporelle
ID_source: clé étrangère d'une table dimension pour avoir une vision sur la "provenance" des clients.
etc.

D'une manière un peu plus détaillée, on a:
temps(ID_temps, jour, semaine, mois, année)

Je voulais savoir si je dois mettre une clé primaire dans ma table de fait.
Sur le principe du modèle en étoile, je ne pense pas que cela soit nécessaire. Néanmoins, si on prend l'exemple du temps, je ne peux pas discriminer de manière unique l'ID_temps dans la mesure ou mes logs ne sont pas hyper-précis (j'ai beaucoup de log qui auront le même ID_temps étant donné que la précision est à la seconde). Ce problème s'applique aussi aux autres tables dimensions (IP du client pour la table source, etc.).

Si je décide de prendre une clé primaire plus grande, comme par exemple la somme de 2 ou n attributs dans chaque table dimension, je me retrouve avec le même problème (il y a beaucoup moins de chance que deux clés soient identiques, mais cela peut arriver).

Je voulais donc rajouter une clé primaire dans la table de faits (par exemple un ID unique de type bigserial) et le greffer aux clés primaires des tables dimensions:

faits(ID_unique, ID_temps, ID_source ID_request, etc.)
temps(#ID_unique, ID_temps, ID_request, etc.)

et de manière similaire:
faits(ID_unique, ID_source, ID_temps, ID_request, etc.)
source(#ID_unique, ID_sources, pays, etc.)
...

J'aimerais donc avoir votre avis la dessus (je ne suis absolument pas un ténor en BD et la BI est très nouvelle pour moi, mais ce principe de clé primaire de dimension dont une partie est une clé étrangère de la table de fait me choque un peu... donc désolé si c'est une ineptie^^ )

Merci d'avance pour vos réponses
Cordialement

PS: Je viens de me rendre compte que ce dernier post n'est plus trop adéquat dans cette partie du forum... est ce qu'il faut le déplacer (ou ouvrir un autre post dans la rubrique idoine?)

[SQLpro]

Il serait mieux d'ouvrir un autre post.

Toute table à besoin d'une clef primaire. En cas d'absence ce n'est pas une relation, et elle se comportera comme un vulgaire fichier (lecture séquenctielle).

Le mieux est de faire un auto incrément et de constituer la clef primaire sur cet auto incrément avec un index CLUSTER.

A +

[Keyboardist]

Merci pour les tuyaux, je vais regarder cette histoire de cluster.

C'est vrai que sémantiquement c'est idiot de ne pas avoir de clé primaire étant donné que ça enlève tout l'intérêt du relationnel.

Si besoin je ferais un autre post dans la bonne rubrique (je mettrais celui-ci en résolu une fois que j'aurais quelques benchmark et résultats de perfs, ça peut toujours intéresser quelqu'un qui passerait par là ).

Cordialement.

[Jester]

En général c'est contre productif pour un table de fait (ça réduit le débit de données utiles lues en séquentiel), mais il y a des cas où cela peut être utile :
http://www.kimballgroup.com/html/des...rogateKeys.pdf

Le fait de ne pas avoir de clé primaire n'est pas forcément un problème pour une table de fait mais ça complique la maintenance (update, controle d'intégrité, ...).

[SQLpro]

En général c'est contre productif pour un table de fait (ça réduit le débit de données utiles lues en séquentiel), mais il y a des cas où cela peut être utile :
http://www.kimballgroup.com/html/des...rogateKeys.pdf

Le fait de ne pas avoir de clé primaire n'est pas forcément un problème pour une table de fait mais ça complique la maintenance (update, controle d'intégrité, ...).

Tout dépend si le SGBD est doté d'un moteur décisionnel et sait traiter correctement un "star join". Or PostGreSQL n'est absolument pas dédié au décisionnel !

A +

[Jester]

J'ai du mal à voir en quoi une clé primaire sur la table de fait sert au "star join optimization" de SQL Server.

Sur nombre de décisionnelles à ma disposition, Postgresql et SQL Server ont le même plan d'exécution, lookup hash map des dimensions et c'est surtout bound par la lecture séquentielle de la table de fait.

Mais cela reste important de vérifier que le plan d'exécution fait bien usage des hash map join.