intégration d'un très grand fichier dans Postgres

La norme SQL évolue : tirez le meilleur d'un SGBD qui la respecte en écrivant du SQL moderne avec en complément PL/pgSQL, php, java etc.
Répondre
kermarrecSeb
Messages : 1
Enregistré le : dim. 25 févr. 2018 18:31

intégration d'un très grand fichier dans Postgres

Message par kermarrecSeb » dim. 25 févr. 2018 18:58

Bonjour.

A la suite de la sortie de la deuxième version du fichier Pwned Passwords
https://www.troyhunt.com/ive-just-launc ... version-2/
Je me suis dis que j'allais me faire le service de vérification de mot de passe moi même sans passer le service https://haveibeenpwned.com
Ceci me permettrait donc faire des requête offline.
J'ai donc récupéré le fichier contenant le hash(sha1) d'une liste de mot de passe avec le nombre d'occurrence d'utilisation : https://haveibeenpwned.com/Passwords

Ce fichier fait 31,6Go décompressé.
Je l'ai intégré dans une base Postgres à l'aide de la commande copy (https://www.postgresql.org/docs/9.2/sta ... -copy.html)
Le caractère séparateur étant ':'
L'insertion semble avoir bien fonctionné, mais je n'en suis même pas sur vue la volumétrie.
J'en arrive à un blocage. les requêtes sont interminables.

Je suis débutant en optimisation de base de données et j'aurais besoin d'un coup de main sur le type de donnée à choisir pour la table, et surtout sur l'index à appliquer ou sur la façon de faire la requête.

la table à la structure suivante :
CREATE TABLE public.password
(
id text,
"number" bigint
)
WITH (
OIDS=FALSE
);

Je vous remercie par avance, en espérant que cette question vous intéressera.

Seb

Phil
Administrateur du site
Messages : 156
Enregistré le : mar. 1 sept. 2015 00:38
Localisation : France
Contact :

Re: intégration d'un très grand fichier dans Postgres

Message par Phil » mar. 27 févr. 2018 12:38

Bonjour Seb,
Merci pour cette question et désolé pour l'attente, il y a une approbation à effectuer par l'admin pour les sujets postés lorsqu'on vient de s'inscrire et je n'avais pas vu le message hier. Sur du clé-valeur il y a d'autres solutions qu'une base PostgreSQL mais c'est tout à fait possible malgré tout.

Réponse 1 sur les types de données :
Pour le champ texte, text est OK. Pour le nombre d'occurrences du mot de passe, bigint est OK même si int suffirait probablement (2 milliards quand même)

Code : Tout sélectionner

CREATE TABLE password
(
id text,
number bigint
)
;
Pas besoin de guillemets, password, id et number ne sont pas des mots réservés par la norme SQL ou PostgreSQL : https://www.postgresql.org/docs/10/stat ... endix.html

Réponse 2 sur COPY :
La commande COPY est une bonne solution pour charger un fichier plat dans une table. Cette commande renvoie une confirmation immédiate du nombre de lignes copiées sous la forme "COPY compte" donc il est possible de savoir si c'est effectivement OK.
Après le chargement il est possible de voir un échantillon de la table avec :

Code : Tout sélectionner

select * from nom_table limit 10; 
ou encore compter le nombre de lignes avec

Code : Tout sélectionner

select count(*) from nom_table
Réponse 3 sur les SELECT :
Quelle est la syntaxe de la requête interminable ? Sur une table avec 2 champs, les requêtes ne pourront pas être très complexes. La clause where va intégrer un test sur id et/ou number. A priori plutôt sur ID qui est le mot de passe à tester ?
Si le test est une égalité simple (select * from password where id = ...) alors un index B-Tree ou un index Hash seront OK.

Code : Tout sélectionner

create index on password(id)
Le temps de réponse doit ensuite être de quelques ms au maximum pour tester sur un id particulier.
Cdlt. Phil - pgphil.ovh

Phil
Administrateur du site
Messages : 156
Enregistré le : mar. 1 sept. 2015 00:38
Localisation : France
Contact :

Re: intégration d'un très grand fichier dans Postgres

Message par Phil » mar. 27 févr. 2018 22:24

Démo sur un simple pc en utilisant le fichier v2 disponible au lien fourni https://haveibeenpwned.com/Passwords

Code : Tout sélectionner

select version();
                                                                                     version                                                                                     
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 11devel (Ubuntu 11~~devel~20180226.2331-1~418.git364de25.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit
(1 ligne)


CREATE TABLE password
(
id text,
number bigint
)
;
CREATE TABLE

copy password from '/path/to/pwned-passwords-2.0.txt' with delimiter ':';
COPY 501636842
Durée : 975988,430 ms (16:15,988)

\d+
                       Liste des relations
 Schéma |   Nom    | Type  | Propriétaire | Taille | Description 
--------+----------+-------+--------------+--------+-------------
 public | password | table | postgres     | 39 GB  | 
(1 ligne)

create index password_h1 on password using hash(id);
CREATE INDEX
Durée : 2171269,934 ms (36:11,270)

 \di+
                              Liste des relations
 Schéma |     Nom     | Type  | Propriétaire |  Table   | Taille | Description 
--------+-------------+-------+--------------+----------+--------+-------------
 public | password_h1 | index | postgres     | password | 16 GB  | 
(1 ligne)


create extension pgcrypto;
CREATE EXTENSION

explain select number from password where id = upper(encode(digest('toto', 'sha1'), 'hex'));
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Index Scan using password_h1 on password  (cost=0.00..8.02 rows=1 width=8)
   Index Cond: (id = '0B9C2625DC21EF05F6AD4DDF47C5F203837AA32C'::text)

select number from password where id = upper(encode(digest('toto', 'sha1'), 'hex'));
 number 
--------
  15320
(1 ligne)

Temps : 0,402 ms
Le chargement du fichier et la création de l'index (ici avec la méthode hash) prennent moins d'une heure.
Ensuite un petit essai avec le mot de passe toto donne 15320...comme prévu il est à présent possible d'affirmer en moins de 1ms que toto n'est pas un très bon mot de passe.
Cdlt. Phil - pgphil.ovh

Phil
Administrateur du site
Messages : 156
Enregistré le : mar. 1 sept. 2015 00:38
Localisation : France
Contact :

Re: intégration d'un très grand fichier dans Postgres

Message par Phil » ven. 23 mars 2018 16:05

Merci encore une fois pour cette question qui a indirectement permis d'améliorer la version 11 actuellement en développement : https://git.postgresql.org/gitweb/?p=po ... 877dc8ecb5
Cdlt. Phil - pgphil.ovh

Répondre