{"id":782,"date":"2016-12-16T07:05:43","date_gmt":"2016-12-16T07:05:43","guid":{"rendered":"http:\/\/blog.tiran.info\/?p=782"},"modified":"2016-12-16T07:05:43","modified_gmt":"2016-12-16T07:05:43","slug":"fonctions-approximatives-oracle-12-2-1","status":"publish","type":"post","link":"https:\/\/blog.tiran.stream\/?p=782","title":{"rendered":"Fonctions approximatives Oracle 12.2 (#1)"},"content":{"rendered":"

Apr\u00e8s avoir r\u00e9cemment lu plusieurs articles relatifs \u00e0 l’activit\u00e9 sur Wikipedia (pages les plus \u00e9dit\u00e9es<\/a>, contributeurs les plus prolifiques<\/a>…), j’ai voulu me faire une id\u00e9e plus pr\u00e9cise des chiffres. A cet effet, le site dumps.wikimedia.org<\/a> permet d’acc\u00e9der \u00e0 une mine de donn\u00e9es brutes de tracking.<\/p>\n

J’avais aussi en t\u00eate d’en profiter pour utiliser les nouvelles fonctionnalit\u00e9s de calcul approximatifs d’Oracle 12.2<\/a>.<\/p>\n

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<\/a>). Une fois d\u00e9compress\u00e9, on obtient un fichier XML de 30GB:<\/p>\n

$ ls -lh enwiki-20161201-pages-logging.xml\n-rw-r--r-- 1 oracle oinstall 30G Dec 16 16:47 enwiki-20161201-pages-logging.xml\n$\n<\/pre>\n
L’exploitation d’un fichier XML d’une telle taille s’av\u00e8re rapidement complexe et l’objet de ce billet est d’expliquer comment le charger en base afin de pouvoir acc\u00e9der efficacement aux donn\u00e9es – ce qui sera l’objet\u00a0d’un prochain article.<\/p>\n
Le premier essai a consist\u00e9 dans une tentative de chargement direct via le constructeur xmltype\/bfile<\/a>:<\/p>\n
SQL> CREATE OR REPLACE DIRECTORY d_load AS '\/base\/oracle\/oraflash\/wikipedia';\n\nDirectory created.\n\nElapsed: 00:00:01.50\nSQL>\nSQL> CREATE TABLE T_WIKI\n  2  AS\n  3      SELECT XMLTYPE (\n  4                 BFILENAME ('D_LOAD', 'enwiki-20161201-pages-logging.xml'),\n  5                 NLS_CHARSET_ID ('UTF8'))\n  6                 xml_f\n  7        FROM DUAL;\n    SELECT XMLTYPE (\n           *\nERROR at line 3:\nORA-31011: XML parsing failed\nORA-19213: error occurred in XML processing at lines 30730673\nLPX-00217: invalid character 4103061439 (U+8FBFBF)\nORA-06512: at "SYS.XMLTYPE", line 296\nORA-06512: at line 1\n\n\nElapsed: 00:17:05.84\nSQL>\n<\/pre>\n
Cela a permis de mettre en \u00e9vidence l’existence dans le fichier de caract\u00e8res non-pris en charge par le parser XML. Dans un second temps, j’ai essay\u00e9 de charger les donn\u00e9es bloc par bloc en utilisant la balise XML <\/logitem> comme d\u00e9limiteur.<\/p>\n
En effet, on peut constater que la structure du fichier correspond \u00e0 une succession de blocs <logitem>…<\/logitem>:<\/p>\n
$ tail -25 enwiki-20161201-pages-logging.xml\n  <logitem>\n    <id>79165294<\/id>\n    <timestamp>2016-12-04T05:17:33Z<\/timestamp>\n    <contributor>\n      <username>Rauk81<\/username>\n      <id>29812253<\/id>\n    <\/contributor>\n    <type>newusers<\/type>\n    <action>create<\/action>\n    <logtitle>User:Rauk81<\/logtitle>\n    <params xml:space="preserve">a:1:{s:9:&quot;4::userid&quot;;i:29812253;}<\/params>\n  <\/logitem>\n  <logitem>\n    <id>79165295<\/id>\n    <timestamp>2016-12-04T05:17:43Z<\/timestamp>\n    <contributor>\n      <username>ShadySjin<\/username>\n      <id>29812254<\/id>\n    <\/contributor>\n    <type>newusers<\/type>\n    <action>create<\/action>\n    <logtitle>User:ShadySjin<\/logtitle>\n    <params xml:space="preserve">a:1:{s:9:&quot;4::userid&quot;;i:29812254;}<\/params>\n  <\/logitem>\n<\/mediawiki>\n$\n<\/pre>\n
Pour cela, j’ai eu recours \u00e0 une table externe:<\/p>\n
SQL> CREATE OR REPLACE DIRECTORY d_load AS '\/base\/oracle\/oraflash\/wikipedia';\n\nDirectory created.\n\nSQL>\nSQL> CREATE TABLE ext_wikilog\n  2  (\n  3      xmlblock   CLOB\n  4  )\n  5  ORGANIZATION EXTERNAL\n  6      (TYPE oracle_loader\n  7            DEFAULT DIRECTORY d_load\n  8                ACCESS PARAMETERS (\n  9                    RECORDS DELIMITED BY '<\/logitem>'\n 10                    CHARACTERSET al32utf8\n 11                    SKIP 1\n 12                    FIELDS\n 13                    (line CHAR(4000))\n 14                    COLUMN TRANSFORMS (\n 15                        xmlblock FROM CONCAT (line, CONSTANT '<\/logitem>'))\n 16                )\n 17            LOCATION ('enwiki-20161201-pages-logging.xml'));\n\nTable created.\n\nSQL>\n<\/pre>\n
Cette table externe r\u00e9alise donc le d\u00e9coupage du fichier et chaque ligne restitu\u00e9e correspond \u00e0 un bloc <logitem>…<\/logitem>. On saute la premi\u00e8re ligne (SKIP 1) qui contient un header XML et on convertit chaque extrait en tant que CLOB.<\/p>\n
Il n’est malheureusement pas possible de proc\u00e9der \u00e0 une conversion directe en XMLTYPE<\/a> car ce type n’est pas support\u00e9 par les tables externes (ORA-30656: column type not supported on external organized table).<\/p>\n
A ce stade, pour convertir les donn\u00e9es au format XMLTYPE<\/a>, ma premi\u00e8re id\u00e9e \u00e9tait de passer par un ordre \u00ab\u00a0insert … as select …\u00a0\u00bb coupl\u00e9 avec une clause de DML error logging<\/a>:<\/p>\n
SQL> CREATE TABLE wikilog\n  2  (\n  3      logitem   XMLTYPE\n  4  )\n  5  XMLTYPE logitem STORE AS SECUREFILE BINARY XML\n  6  NOLOGGING\n  7  PARALLEL 16;\n\nTable created.\n\nSQL>\nSQL> BEGIN\n  2      DBMS_ERRLOG.create_error_log (dml_table_name => 'wikilog', skip_unsupported => true);\n  3  END;\n  4  \/\n\nPL\/SQL procedure successfully completed.\n\nSQL>\nSQL> alter session enable parallel DML;\n\nSession altered.\n\nSQL> \nSQL> set timing on;\nSQL> INSERT INTO wikilog\n  2      SELECT \/*+ parallel(t 16) *\/\n  3            xmltype (xmlblock) logitem\n  4        FROM ext_wikilog t\n  5          LOG ERRORS INTO err$_wikilog;\nINSERT INTO wikilog\n*\nERROR at line 1:\nORA-12801: error signaled in parallel query server P009, instance\npsu888:IOSHR88D1_2 (2)\nORA-29913: error in executing ODCIEXTTABLEFETCH callout\nORA-31011: XML parsing failed\nORA-19213: error occurred in XML processing at lines 6\nLPX-00217: invalid character 4103061439 (U+8FBFBF)\nORA-06512: at "SYS.XMLTYPE", line 272\nORA-06512: at line 1\n\nElapsed: 00:02:40.02\nSQL> \n<\/pre>\n
Malheureusement, il s’est av\u00e9r\u00e9 que la fonctionnalit\u00e9 de DML error logging ne prenait pas en compte les exceptions de ce genre. En fait, seules quelques exceptions \u00ab\u00a0classiques\u00a0\u00bb sont prises en charge (violation de contrainte, probl\u00e8me de capacit\u00e9 de champ etc…).<\/p>\n
Afin de tol\u00e9rer la survenue d’exceptions sans pour autant compromettre la totalit\u00e9 du chargement, il ne reste plus qu’une approche PL\/SQL int\u00e9grant une gestion sp\u00e9cifique des exceptions.<\/p>\n
On va ainsi lire chaque fragment XML, le convertir de CLOB \u00e0 XMLTYPE puis parser le r\u00e9sultat \u00e0 l’aide d’expressions XQUERY<\/a>. Les r\u00e9sultats seront stock\u00e9s dans une table LOG_CONTRIBS. En cas de probl\u00e8me (notamment l’exception ORA-31011: XML parsing failed), l’enregistrement sera consign\u00e9 dans une table REJETS et on passera \u00e0 la ligne suivante.<\/p>\n
SQL> CREATE TABLE rejets\n  2  (\n  3      errcode     NUMBER,\n  4      xml_rejet   CLOB\n  5  )\n  6  TABLESPACE tbs01;\n\nTable created.\n\nSQL>\nSQL> CREATE TABLE log_contribs\n  2  (\n  3      id          NUMBER,\n  4      tmstamp     TIMESTAMP,\n  5      uname       VARCHAR2 (150),\n  6      logtype     VARCHAR2 (50),\n  7      logaction   VARCHAR2 (50)\n  8  )\n  9  TABLESPACE tbs01\n 10  NOLOGGING;\n\nTable created.\n\nSQL>\n<\/pre>\n
Le probl\u00e8me est que cette approche est par nature tr\u00e8s lente (traitement ligne \u00e0 ligne) or le volume \u00e0 charger est cons\u00e9quent (>30GB).<\/p>\n
Deux optimisations vont \u00eatre mises en \u0153uvre:<\/p>\n