Fonctions approximatives Oracle 12.2 (#1)
Après avoir récemment lu plusieurs articles relatifs à l’activité sur Wikipedia (pages les plus éditées, contributeurs les plus prolifiques…), j’ai voulu me faire une idée plus précise des chiffres. A cet effet, le site dumps.wikimedia.org permet d’accéder à une mine de données brutes de tracking.
J’avais aussi en tête d’en profiter pour utiliser les nouvelles fonctionnalités de calcul approximatifs d’Oracle 12.2.
Pour cela, je me suis servi du fichier de suivi des modifications de la version anglaise de Wikipedia (enwiki-<date>-pages-logging.xml.gz accessible dans l’un des sous-dossiers de https://dumps.wikimedia.org/enwiki). Une fois décompressé, on obtient un fichier XML de 30GB:
$ ls -lh enwiki-20161201-pages-logging.xml -rw-r--r-- 1 oracle oinstall 30G Dec 16 16:47 enwiki-20161201-pages-logging.xml $
L’exploitation d’un fichier XML d’une telle taille s’avère rapidement complexe et l’objet de ce billet est d’expliquer comment le charger en base afin de pouvoir accéder efficacement aux données – ce qui sera l’objet d’un prochain article.
Le premier essai a consisté dans une tentative de chargement direct via le constructeur xmltype/bfile:
SQL> CREATE OR REPLACE DIRECTORY d_load AS '/base/oracle/oraflash/wikipedia';
Directory created.
Elapsed: 00:00:01.50
SQL>
SQL> CREATE TABLE T_WIKI
2 AS
3 SELECT XMLTYPE (
4 BFILENAME ('D_LOAD', 'enwiki-20161201-pages-logging.xml'),
5 NLS_CHARSET_ID ('UTF8'))
6 xml_f
7 FROM DUAL;
SELECT XMLTYPE (
*
ERROR at line 3:
ORA-31011: XML parsing failed
ORA-19213: error occurred in XML processing at lines 30730673
LPX-00217: invalid character 4103061439 (U+8FBFBF)
ORA-06512: at "SYS.XMLTYPE", line 296
ORA-06512: at line 1
Elapsed: 00:17:05.84
SQL>
Cela a permis de mettre en évidence l’existence dans le fichier de caractères non-pris en charge par le parser XML. Dans un second temps, j’ai essayé de charger les données bloc par bloc en utilisant la balise XML </logitem> comme délimiteur.
En effet, on peut constater que la structure du fichier correspond à une succession de blocs <logitem>…</logitem>:
$ tail -25 enwiki-20161201-pages-logging.xml
<logitem>
<id>79165294</id>
<timestamp>2016-12-04T05:17:33Z</timestamp>
<contributor>
<username>Rauk81</username>
<id>29812253</id>
</contributor>
<type>newusers</type>
<action>create</action>
<logtitle>User:Rauk81</logtitle>
<params xml:space="preserve">a:1:{s:9:"4::userid";i:29812253;}</params>
</logitem>
<logitem>
<id>79165295</id>
<timestamp>2016-12-04T05:17:43Z</timestamp>
<contributor>
<username>ShadySjin</username>
<id>29812254</id>
</contributor>
<type>newusers</type>
<action>create</action>
<logtitle>User:ShadySjin</logtitle>
<params xml:space="preserve">a:1:{s:9:"4::userid";i:29812254;}</params>
</logitem>
</mediawiki>
$
Pour cela, j’ai eu recours à une table externe:
SQL> CREATE OR REPLACE DIRECTORY d_load AS '/base/oracle/oraflash/wikipedia';
Directory created.
SQL>
SQL> CREATE TABLE ext_wikilog
2 (
3 xmlblock CLOB
4 )
5 ORGANIZATION EXTERNAL
6 (TYPE oracle_loader
7 DEFAULT DIRECTORY d_load
8 ACCESS PARAMETERS (
9 RECORDS DELIMITED BY '</logitem>'
10 CHARACTERSET al32utf8
11 SKIP 1
12 FIELDS
13 (line CHAR(4000))
14 COLUMN TRANSFORMS (
15 xmlblock FROM CONCAT (line, CONSTANT '</logitem>'))
16 )
17 LOCATION ('enwiki-20161201-pages-logging.xml'));
Table created.
SQL>
Cette table externe réalise donc le découpage du fichier et chaque ligne restituée correspond à un bloc <logitem>…</logitem>. On saute la première ligne (SKIP 1) qui contient un header XML et on convertit chaque extrait en tant que CLOB.
Il n’est malheureusement pas possible de procéder à une conversion directe en XMLTYPE car ce type n’est pas supporté par les tables externes (ORA-30656: column type not supported on external organized table).
A ce stade, pour convertir les données au format XMLTYPE, ma première idée était de passer par un ordre « insert … as select … » couplé avec une clause de DML error logging:
SQL> CREATE TABLE wikilog 2 ( 3 logitem XMLTYPE 4 ) 5 XMLTYPE logitem STORE AS SECUREFILE BINARY XML 6 NOLOGGING 7 PARALLEL 16; Table created. SQL> SQL> BEGIN 2 DBMS_ERRLOG.create_error_log (dml_table_name => 'wikilog', skip_unsupported => true); 3 END; 4 / PL/SQL procedure successfully completed. SQL> SQL> alter session enable parallel DML; Session altered. SQL> SQL> set timing on; SQL> INSERT INTO wikilog 2 SELECT /*+ parallel(t 16) */ 3 xmltype (xmlblock) logitem 4 FROM ext_wikilog t 5 LOG ERRORS INTO err$_wikilog; INSERT INTO wikilog * ERROR at line 1: ORA-12801: error signaled in parallel query server P009, instance psu888:IOSHR88D1_2 (2) ORA-29913: error in executing ODCIEXTTABLEFETCH callout ORA-31011: XML parsing failed ORA-19213: error occurred in XML processing at lines 6 LPX-00217: invalid character 4103061439 (U+8FBFBF) ORA-06512: at "SYS.XMLTYPE", line 272 ORA-06512: at line 1 Elapsed: 00:02:40.02 SQL>
Malheureusement, il s’est avéré que la fonctionnalité de DML error logging ne prenait pas en compte les exceptions de ce genre. En fait, seules quelques exceptions « classiques » sont prises en charge (violation de contrainte, problème de capacité de champ etc…).
Afin de tolérer la survenue d’exceptions sans pour autant compromettre la totalité du chargement, il ne reste plus qu’une approche PL/SQL intégrant une gestion spécifique des exceptions.
On va ainsi lire chaque fragment XML, le convertir de CLOB à XMLTYPE puis parser le résultat à l’aide d’expressions XQUERY. Les résultats seront stockés dans une table LOG_CONTRIBS. En cas de problème (notamment l’exception ORA-31011: XML parsing failed), l’enregistrement sera consigné dans une table REJETS et on passera à la ligne suivante.
SQL> CREATE TABLE rejets 2 ( 3 errcode NUMBER, 4 xml_rejet CLOB 5 ) 6 TABLESPACE tbs01; Table created. SQL> SQL> CREATE TABLE log_contribs 2 ( 3 id NUMBER, 4 tmstamp TIMESTAMP, 5 uname VARCHAR2 (150), 6 logtype VARCHAR2 (50), 7 logaction VARCHAR2 (50) 8 ) 9 TABLESPACE tbs01 10 NOLOGGING; Table created. SQL>
Le problème est que cette approche est par nature très lente (traitement ligne à ligne) or le volume à charger est conséquent (>30GB).
Deux optimisations vont être mises en œuvre:
- le traitement DML par tableau BULK COLLECT/FORALL afin de limiter autant que possible l’aspect ligne à ligne
- la parallélisation « manuelle » via l’API 12c DBMS_PARALLEL_EXECUTE
A l’instar du parallel query (PX), ce package permet de tronçonner une opération en unités parallélisables. Le découpage du travail est effectué par l’appel d’une procédure CREATE_CHUNK_* qui délimite le périmètre de chaque session parallèle. Ici, on va utiliser CREATE_CHUNK_BY_ROWID mais pour cela on va devoir au préalable copier les données de la table externe vers une table classique. En effet, les lignes d’une table externe ne disposent pas d’un ROWID:
SQL> DROP TABLE wikilog PURGE; Table dropped. SQL> SQL> set timing on; SQL> SQL> CREATE TABLE wikilog 2 NOLOGGING 3 PARALLEL 16 4 AS 5 SELECT /*+ parallel(t 16) */ 6 xmlblock logitem 7 FROM ext_wikilog t; Table created. Elapsed: 00:04:49.48 SQL> SQL> set timing off; SQL>
On peut alors utiliser cette table comme source de parallélisation. Chaque session parallèle va ainsi traiter un tronçon délimité par une tranche de ROWID (début/fin).
A ce stade, on peut créer la procédure PL/SQL qui prendra une tranche de ROWID en argument et qui sera invoquée par DBMS_PARALLEL_EXECUTE:
SQL> SQL> CREATE OR REPLACE PROCEDURE PARSE_XML (p_start_rid ROWID, p_end_rid ROWID) 2 IS 3 CURSOR C_SRC 4 IS 5 SELECT logitem 6 FROM wikilog 7 WHERE ROWID BETWEEN p_start_rid AND p_end_rid; 8 9 TYPE logitem_array IS TABLE OF c_src%ROWTYPE; 10 11 l_xml2parse logitem_array; 12 13 array_dml_err EXCEPTION; 14 PRAGMA EXCEPTION_INIT (array_dml_err, -24381); 15 16 l_errcode NUMBER; 17 l_erridx NUMBER; 18 BEGIN 19 OPEN c_src; 20 21 LOOP 22 FETCH c_src BULK COLLECT INTO l_xml2parse LIMIT 1000; 23 24 BEGIN 25 FORALL i IN 1 .. l_xml2parse.COUNT SAVE EXCEPTIONS 26 INSERT INTO log_contribs 27 SELECT XMLCAST ( 28 XMLQUERY ( 29 '/logitem/id' 30 PASSING logitem_xml RETURNING CONTENT) AS NUMBER), 31 TO_TIMESTAMP ( 32 XMLCAST ( 33 XMLQUERY ( 34 '/logitem/timestamp' 35 PASSING logitem_xml RETURNING CONTENT) AS VARCHAR (30)), 36 'YYYY-MM-DD"T"HH24:MI:SS"Z"'), 37 XMLCAST ( 38 XMLQUERY ( 39 '/logitem/contributor/username' 40 PASSING logitem_xml RETURNING CONTENT) AS VARCHAR (150)), 41 XMLCAST ( 42 XMLQUERY ( 43 '/logitem/type' 44 PASSING logitem_xml RETURNING CONTENT) AS VARCHAR (50)), 45 XMLCAST ( 46 XMLQUERY ( 47 '/logitem/action' 48 PASSING logitem_xml RETURNING CONTENT) AS VARCHAR (50)) 49 FROM (SELECT xmltype (l_xml2parse (i).logitem) 50 logitem_xml 51 FROM DUAL); 52 EXCEPTION 53 WHEN array_dml_err 54 THEN 55 FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT 56 LOOP 57 l_errcode := SQL%BULK_EXCEPTIONS (i).ERROR_CODE; 58 l_erridx := SQL%BULK_EXCEPTIONS (i).ERROR_INDEX; 59 60 INSERT INTO rejets (errcode, xml_rejet) 61 VALUES (l_errcode, l_xml2parse (l_erridx).logitem); 62 END LOOP; 63 END; 64 65 EXIT WHEN c_src%NOTFOUND; 66 END LOOP; 67 68 CLOSE c_src; 69 END; 70 / Procedure created. SQL>
Ici, l’opération est réalisée sur une machine HP ProLiant BL460c (2s24c48t) qui ne supporte pas d’autre activité que mon test. Je peux donc utiliser un DOP conséquent: 48 par exemple. Cela permet d’évaluer le nombre blocs que devra traiter chaque parallèle :
SQL> SELECT blocks / 48 2 FROM tabs 3 WHERE table_name = 'WIKILOG'; BLOCKS/48 ---------- 200571.833 SQL>
On peut alors créer la tache parallélisée via la procédure CREATE_TASK de DBMS_PARALLEL_EXECUTE et ensuite définir le périmètre d’action de chaque parallèle à l’aide de l’appel CREATE_CHUNK_BY_ROWID:
SQL> BEGIN 2 DBMS_PARALLEL_EXECUTE.create_task (task_name => 'PARSE_XML_PARAL'); 3 END; 4 / PL/SQL procedure successfully completed. SQL> BEGIN 2 DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid ( 3 task_name => 'PARSE_XML_PARAL', 4 table_owner => 'C##RAF', 5 table_name => 'WIKILOG', 6 by_row => FALSE, 7 chunk_size => 201000); 8 END; 9 / PL/SQL procedure successfully completed. SQL>
Il ne reste plus qu’à exécuter la procédure RUN_TASK:
SQL> set timing on; SQL> SQL> DECLARE 2 l_sql_stmt VARCHAR2 (32767); 3 BEGIN 4 l_sql_stmt := 'BEGIN parse_xml(:start_id, :end_id); END;'; 5 6 DBMS_PARALLEL_EXECUTE.run_task (task_name => 'PARSE_XML_PARAL', 7 sql_stmt => l_sql_stmt, 8 language_flag => DBMS_SQL.NATIVE, 9 parallel_level => 48); 10 END; 11 / PL/SQL procedure successfully completed. Elapsed: 00:46:47.22 SQL>
Le chargement dure un peu plus de 50 minutes (4 minutes de transfert EXT_WIKILOG vers WIKILOG puis 46 minutes de parsing des données vers LOG_CONTRIBS). Au final, on trouve 250 blocs non-parsable sur 77 millions d’enregistrements et la table LOG_CONTRIBS atteint une taille d’environ 4G.
SQL> SELECT errcode, COUNT (*)
2 FROM rejets
3 GROUP BY errcode;
ERRCODE COUNT(*)
---------- ----------
31011 250
SQL>
SQL> SELECT COUNT (*) FROM log_contribs;
COUNT(*)
----------
77662802
SQL>
SQL> SELECT bytes / POWER (1024, 2)
2 FROM user_segments
3 WHERE segment_name = 'LOG_CONTRIBS';
BYTES/POWER(1024,2)
-------------------
3904
SQL>