Configurer Elasticsearch sur PostgreSQL avec Zombodb

L’objectif de ce billet est d’illustrer une configuration personnalisée d’Elasticsearch par Zombodb sur PostgreSQL.

Je passe sur l’installation qui est bien documenté sur le site de l’extension: https://github.com/zombodb/zombodb

La configuration commence après l’activation de l’extension zombodb sur la base PostgreSQL.

Pour les besoins de l’exemple voici quelques données de tests :

create table test(id integer primary key, content text);

insert into test values (1, 'pizza'), (2, 'pizzas'),( 3, 'pizz'), (4, 'developper'), (5, 'developpement'),( 6, 'developpe');

Création d’un index

CREATE INDEX idx_zdb_test
ON test
USING zombodb(zdb('test', test.ctid), zdb(test))
WITH (url='http://elasticsearch:9200/');

Configuration des « analyzers »

SELECT zdb_define_analyzer('custom_search_analyzer', '{"type": "custom", "tokenizer": "standard", "filter":["stopwords", "asciifolding" ,"lowercase", "snowball", "elision", "worddelimiter"] }');

SELECT zdb_define_analyzer('custom_analyzer','{ "type": "custom", "tokenizer":"nGram", "filter":["stopwords", "asciifolding" ,"lowercase", "snowball","elision", "worddelimiter"] }');

SELECT zdb_define_filter('snowball', '{"type": "snowball", "language": "French"}');

SELECT zdb_define_filter('elision', '{"type": "elision", "articles": ["l", "m", "t", "qu", "n", "s", "j", "d"]}');

SELECT zdb_define_filter('stopwords', '{"type": "stop", "stopwords":["_french_"], "ignore_case" : true }');

SELECT zdb_define_filter('worddelimiter', '{"type": "word_delimiter"}');

SELECT zdb_define_tokenizer('nGram', '{"type": "nGram", "min_gram": 2,"max_gram": 20}');

Configuration du mapping :

SELECT zdb_define_mapping('test', 'content', '{
"type": "string",
"store": false,
"include_in_all": true,
"search_analyzer": "custom_search_analyzer",
"index_analyzer": "custom_analyzer",
"norms": {"enabled": false}
}');

Lancer une réindexation :

reindex idx_zdb_test;

Tester

poke=# SELECT * FROM test WHERE zdb('test', test.ctid) ==> 'content:pizza';
id | content
----+---------
1 | pizz
2 | pizza
3 | pizzas
(3 lignes)
poke=# SELECT * FROM test WHERE zdb('test', test.ctid) ==> 'content:pizz';
id | content
----+---------
1 | pizz
2 | pizza
3 | pizzas
(3 lignes)
poke=# SELECT * FROM test WHERE zdb('test', test.ctid) ==> 'content:pizzas';
id | content
----+---------
1 | pizz
2 | pizza
3 | pizzas

Il est également possible de créer un DOMAIN:

CREATE DOMAIN custom_search_analyzer as text;

Pour ensuite l’utiliser directement dans PostgreSQL.

Convertir une base de données SQL Serveur vers Postgresql

Rapide retour d’expérience sur cette opération.
L’objectif : convertir une base de données SQL Serveur vers PostgreSQL (128 tables, 600 Mo de données) dans la journée.

3 hypothèses : ETL, Outils dédiés, boite à outils Linux.

Pour l’ETL j’ai assez vite abandonné l’idée. Je maitrise relativement bien Talend je choisis donc de tester la récupération du schéma depuis le Studio et l’étape semble relativement longue, en parallèle je commence donc à extraire les données comme indiqué dans la procédure. Finalement Talend Open Studio plante sur un PermGen Space.

Pour les outils dédiés, honnêtement je n’ai pas eu le temps de m’y pencher, en quelques minutes j’avais la structure de ma base PostreSQL opérationnelle.

Pour la structure

J’ai utilisé la procédure https://wiki.postgresql.org/wiki/Microsoft_SQL_Server_to_PostgreSQL_Migration_by_Ian_Harding.

J’ai rencontré les spécificités suivantes sur le fichier d’export complet :

  • remplacement du type bit par int ;
  • suppression des instructions WITH sur la ligne de création de clé primaire ,
  • modification des instructions d’alterations :
    • pour l’ajout de valeur par défaut (ALTER TABLE … ALTER COLUMN … SET …)
    • pour les contraintes de clés étrangères (ALTER TABLE … ADD CONSTRAIT …)
    • suppression des lignes de CHECK, SET, etc.
    • suppression des diacritiques dans les nom de table et de colonnes

Au final, le fichier de structure de données contient uniquement des instructions de type CREATE.

Pour tester le script il suffit de le passer jusqu’à ce qu’il n’y ait plus d’anomalie. Dans mon cas j’ai utilisé un schéma spécifique nommé : « reprise » pour l’ensemble des tables (il suffit de remplacer la valeur dbo par reprise ) et ainsi d’ajouter au début du script

DROP SCHEMA reprise CASCADE;
CREATE SCHEMA reprise;

Pour les données

J’ai réalisé un export complet de la base de données depuis le SQL Serveur Studio (structure + données) et j’ai retravaillé l’export. Ma première intuition a été de réaliser un export table par table dans un fichier délimité par des tabulations afin de l’adapter rapidement au format COPY de PostgreSQL. J’ai abandonné l’idée au vu du nombre de table et du temps à passer pour adapter correctement l’export (problème de saut de ligne dans des champs de type texte notamment).

Voici un résumé des opérations que j’ai effectué sur le fichier d’export complet :

  • encoder le fichier en UTF8 ;
  • conserver uniquement les lignes de type INSERT (grep  INSERT … > …)  (cela permet de supprimer rapidement les lignes de type print, SET, etc.) ;
  • remplacer « INSERT » par « INSERT INTO » ;
  • remplacer CAST(0x000… as DateTime) en hex_to_date(…) (dans vim : %s/CAST(\(.\{-}\)), /hex_to_date(‘\1’), /g
  • suppression des diacritiques dans les noms de tables et de colonnes ;
  • ajout des points virgules en fin de ligne.

La fonction hex_to_date :

CREATE OR REPLACE function hex_to_date(hexval text) returns timestamp as $$
  SELECT '1900-01-01 00:00:00'::date +
   (('x'||substring(x::text,3,8))::bit(32)::int::text||'days')::interval +
   ((('x'||substring(x::text,11,8))::bit(32)::int /300)::text||' seconds')::interval
  FROM (VALUES ($1)) as x(x);
$$
language sql struct immutable;

Le chargement peut s’avérer relativement long car de type INSERT et non du type COPY mais le travail de transformation est plus simple.