{"id":923,"date":"2017-03-06T07:25:25","date_gmt":"2017-03-06T07:25:25","guid":{"rendered":"http:\/\/blog.tiran.info\/?p=923"},"modified":"2017-03-06T07:25:25","modified_gmt":"2017-03-06T07:25:25","slug":"oracle-r-enterprise-3","status":"publish","type":"post","link":"https:\/\/blog.tiran.stream\/?p=923","title":{"rendered":"Oracle R Enterprise (#3)"},"content":{"rendered":"

Oracle R Enterprise \u00e9tant en place, je peux reprendre les tests de comptage la ou je les avais laiss\u00e9 dans un article pr\u00e9c\u00e9dent<\/a>…<\/p>\n

\u00a0\n> library(ORE)\nLoading required package: OREbase\nLoading required package: OREcommon\n\nAttaching package: \u2018OREbase\u2019\n\nThe following objects are masked from \u2018package:base\u2019:\n\n    cbind, data.frame, eval, interaction, order, paste, pmax, pmin, rbind, table\n\nLoading required package: OREembed\nLoading required package: OREstats\nLoading required package: MASS\nLoading required package: OREgraphics\nLoading required package: OREeda\nLoading required package: OREmodels\nLoading required package: OREdm\nLoading required package: lattice\nLoading required package: OREpredict\nLoading required package: ORExml\n> \n> ore.connect(user="c##raf", password="Password1#", conn_string="\/\/clorai2-scan:1521\/pdb_iotst04")\n<\/pre>\n
A ce stade, on voit une session connect\u00e9e \u00e0 la base:<\/p>\n
\u00a0\nSQL> column username format a8\nSQL> column sql_id format a13\nSQL> column sql_text format a90\nSQL>\nSQL> SELECT username,\n  2         a.sql_id,\n  3         b.sql_text\n  4    FROM v$session a, v$sql b\n  5   WHERE a.program = 'rsession.exe' AND a.sql_id = b.sql_id(+);\n\nUSERNAME SQL_ID        SQL_TEXT\n-------- ------------- ------------------------------------------------------------------------------------------\nC##RAF\n\nSQL>\n<\/pre>\n
A l’aide d’ore.sync<\/a>, on peut alors cr\u00e9er des structures \u00ab\u00a0proxy\u00a0\u00bb pour acc\u00e9der aux tables de la base (dans cet exemple, je pr\u00e9cise l’unique table qui m’int\u00e9resse: LOG_CONTRIBS). Ces derni\u00e8res sont de type ore.frame<\/a> et se manipulent comme des data.frame:<\/p>\n
\u00a0\n> ore.attach()\n> ore.sync(table="LOG_CONTRIBS")\n> \n> class(LOG_CONTRIBS)\n[1] "ore.frame"\nattr(,"package")\n[1] "OREbase"\n> \n<\/pre>\n
Ainsi, pour faire un comptage exact du nombre de contributeurs distincts \u00e0 Wikipedia, je peut utiliser la formule R length(unique(<df>)).<\/p>\n
\u00a0\n> system.time(NB_DISTINCT <- length(unique(LOG_CONTRIBS$UNAME)))\n   user  system elapsed \n   0.00    0.00   50.39 \n> NB_DISTINCT\n[1] 28839445\n> \n<\/pre>\n
Etant donn\u00e9 que LOG_CONTRIBS est un proxy pour la table de m\u00eame nom, la formule est automatiquement convertie en code SQL. On peut facilement le v\u00e9rifier \u00e0 l’aide de la m\u00eame requ\u00eate que celle ex\u00e9cut\u00e9e plus haut:<\/p>\n
\u00a0\nSQL> SELECT username,\n  2         a.sql_id,\n  3         b.sql_text\n  4    FROM v$session a, v$sql b\n  5   WHERE a.program = 'rsession.exe' AND a.sql_id = b.sql_id(+);\n\nUSERNAME SQL_ID        SQL_TEXT\n-------- ------------- ------------------------------------------------------------------------------------------\nC##RAF   cq264yx9bt1fp with OBJ31_1 as ( select \/*+ no_merge(t) *\/  "ID" VAL001,"TMSTAMP" VAL002,"UNAME" VAL003,"\n                       LOGTYPE" VAL004,"LOGACTION" VAL005 from "C##RAF"."LOG_CONTRIBS" t  ),OBJ31_2 as ( select \/\n                       *+ no_merge(t) *\/  "UNAME" VAL001 from "C##RAF"."LOG_CONTRIBS" t  ) , LEV31_2 as ( select\n                       row_number() over (order by to_char(VAL001)) id, to_char(VAL001) name from OBJ31_2 where V\n                       AL001 is not null group by to_char(VAL001) ),OBJ31_3 as ( select VAL001 NAME001, VAL001  f\n                       rom OBJ31_2 group by VAL001  ) , LEV31_3 as ( select id, name from LEV31_2 ),OBJ31_4 as (\n                       select \/*+ no_merge(t) *\/ 1 NAME001, count(*) VAL001 from OBJ31_3 t  )   select  *  from\n                       OBJ31_4 t order by NAME001\n\nSQL>\n<\/pre>\n
Le r\u00e9sultat du comptage est restitu\u00e9 en 50 secondes, ce qui correspond \u00e0 la dur\u00e9e que nous avions obtenu directement en SQL.<\/p>\n
Pour le comptage approximatif, je n’ai pas trouv\u00e9 de package R impl\u00e9mentant l’algorithme d’HyperLogLog. Ici, j’utilise donc directement une requ\u00e8te SQL au sein d’un bloc ore.doEval<\/a>.
\nCette construction est relativement lourde mais elle permet de montrer l’op\u00e9ration de connexion implicite \u00e0 l’aide de l’appel \u00ab\u00a0dbConnect(Extproc())\u00a0\u00bb.<\/p>\n
\u00a0\n> ore.doEval(function() {\n+   ore.disconnect()\n+   library(tictoc)\n+   tic()\n+   con1 <- dbConnect(Extproc())\n+   res1 <- dbGetQuery(con1, "select approx_count_distinct(UNAME) from LOG_CONTRIBS")\n+   dbDisconnect(con1)\n+   exectime <- toc()\n+   exectime <- exectime$toc - exectime$tic\n+   print(paste("Count distinct: ", res1, " - Duree :", exectime, "secondes"))\n+ }, ore.connect = TRUE)\n[1] "Count distinct:  30284800  - Duree : 12.18 secondes"\n> \n> ore.disconnect()\n> \n<\/pre>\n
On notera aussi la r\u00e9f\u00e9rence au package tictoc (qui offre des fonctions de timing). Pour pouvoir utiliser ce dernier dans des appels ORE, il faut que son chargement soit mentionn\u00e9 explicitement dans le bloc (\u00ab\u00a0library(tictoc)\u00a0\u00bb). De plus, le package doit avoir \u00e9t\u00e9 pr\u00e9alablement ajout\u00e9 \u00e0 la distribution R cot\u00e9 serveur:<\/p>\n
\u00a0\noracle@psu888: \/home\/oracle [IOSHR88D1_2]# ORE CMD INSTALL \/home\/oracle\/ORE\/tictoc_1.0.tar.gz\n* installing to library \u2018\/soft\/oracle\/product\/rdbms\/12.2.0.1\/R\/library\u2019\n* installing *source* package \u2018tictoc\u2019 ...\n** package \u2018tictoc\u2019 successfully unpacked and MD5 sums checked\n** R\n** inst\n** preparing package for lazy loading\n** help\n*** installing help indices\n  converting help for package \u2018tictoc\u2019\n    finding HTML links ... done\n    Stack                                   html\n    tic                                     html\n    tictoc                                  html\n** building package indices\n** testing if installed package can be loaded\n* DONE (tictoc)\noracle@psu888: \/home\/oracle [IOSHR88D1_2]#\n<\/pre>\n
Bien s\u00fbr, ici , on aurait plut\u00f4t tout int\u00e9r\u00eat \u00e0 utiliser une ex\u00e9cution SQL directe via ROracle plut\u00f4t qu’un bloc ore.doEval!<\/p>\n","protected":false},"excerpt":{"rendered":"
Oracle R Enterprise \u00e9tant en place, je peux reprendre les tests de comptage la ou je les avais laiss\u00e9 dans<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"colormag_page_container_layout":"default_layout","colormag_page_sidebar_layout":"default_layout","footnotes":""},"categories":[9],"tags":[],"class_list":["post-923","post","type-post","status-publish","format-standard","hentry","category-oracle-r-enterprise"],"_links":{"self":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/posts\/923","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=923"}],"version-history":[{"count":0,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/posts\/923\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=923"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=923"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=923"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}