{"id":278,"date":"2015-07-13T07:30:22","date_gmt":"2015-07-13T07:30:22","guid":{"rendered":"http:\/\/blog.tiran.info\/?p=278"},"modified":"2015-07-13T07:30:22","modified_gmt":"2015-07-13T07:30:22","slug":"connexion-roracle","status":"publish","type":"post","link":"https:\/\/blog.tiran.stream\/?p=278","title":{"rendered":"Connexion R\/Oracle"},"content":{"rendered":"<p style=\"text-align: justify;\">Il est possible d&rsquo;interconnecter R avec un SGBD Oracle afin de traiter directement les informations stock\u00e9es en base. On \u00e9vite ainsi une \u00e9tape fastidieuse d&rsquo;\u00e9change de donn\u00e9es: d\u00e9chargement dans un fichier externe (csv, XML etc&#8230;) puis rechargement dans R.\u00a0La m\u00e9thode pr\u00e9sent\u00e9e ici est bas\u00e9e sur le package ROracle.<\/p>\n<p style=\"text-align: justify;\">A noter que j&rsquo;utilise un t\u00e9l\u00e9chargement et une installation manuelle des composants car ma machine n&rsquo;acc\u00e8de pas directement \u00e0 internet. Dans le cas contraire, il est tout \u00e0 fait possible d&rsquo;utiliser la commande install.packages depuis la console R.<\/p>\n<p style=\"text-align: justify;\"><strong>T\u00e9l\u00e9chargements des packages depuis le site du CRAN :<\/strong><\/p>\n<ul style=\"text-align: justify;\">\n<li>RTools: <a href=\"http:\/\/cran.r-project.org\/bin\/windows\/Rtools\/\" target=\"_blank\">http:\/\/cran.r-project.org\/bin\/windows\/Rtools\/<\/a><\/li>\n<li>DBI: <a href=\"http:\/\/cran.r-project.org\/web\/packages\/DBI\/index.html\" target=\"_blank\">http:\/\/cran.r-project.org\/web\/packages\/DBI\/index.html<\/a><\/li>\n<li>ROracle: <a href=\"http:\/\/cran.r-project.org\/web\/packages\/ROracle\/index.html\" target=\"_blank\">http:\/\/cran.r-project.org\/web\/packages\/ROracle\/index.html<\/a><\/li>\n<\/ul>\n<p style=\"text-align: justify;\"><strong>Installation de Rtools<\/strong><\/p>\n<p style=\"text-align: justify;\">L&rsquo;installation ne pr\u00e9sente aucune difficult\u00e9 et la plupart des choix par d\u00e9faut conviennent. Par pr\u00e9f\u00e9rence personnelle, je modifie n\u00e9anmoins la destination d&rsquo;installation pour C:\\Program Files\\R\\Rtools :<\/p>\n<p style=\"text-align: justify;\"><a href=\"https:\/\/blog.tiran.stream\/wp-content\/uploads\/2015\/06\/Inst-Loc.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-281\" src=\"https:\/\/blog.tiran.stream\/wp-content\/uploads\/2015\/06\/Inst-Loc-300x233.png\" alt=\"Inst-Loc\" width=\"400\" height=\"310\" \/><\/a><br \/>\nD&rsquo;autre part, j\u2019autorise l\u2019installeur \u00e0 modifier le PATH du syst\u00e8me pour y inclure le chemin des binaires de Rtools :<\/p>\n<p style=\"text-align: justify;\"><a href=\"https:\/\/blog.tiran.stream\/wp-content\/uploads\/2015\/06\/Edit-Path.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-279\" src=\"https:\/\/blog.tiran.stream\/wp-content\/uploads\/2015\/06\/Edit-Path.png\" alt=\"Edit-Path\" width=\"400\" height=\"310\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify;\"><strong>Configuration des variables d&rsquo;environnement<\/strong><\/p>\n<p style=\"text-align: justify;\">Deux variables d&rsquo;environnement OCI_LIB64 et OCI_INC doivent \u00eatre valoris\u00e9es en fonction des chemins d&rsquo;installation du client Oracle.<\/p>\n<p style=\"text-align: justify;\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-285\" src=\"https:\/\/blog.tiran.stream\/wp-content\/uploads\/2015\/06\/Var-Evt.png\" alt=\"Var-Evt\" width=\"316\" height=\"350\" \/><\/p>\n<p style=\"text-align: justify;\">Dans mon cas, l&rsquo;ORACLE_HOME est C:\\Oracle\\product\\12.1.0\\dbhome_1 :<\/p>\n<ul style=\"text-align: justify;\">\n<li>OCI_LIB64=C:\\Oracle\\product\\12.1.0\\dbhome_1\\BIN<\/li>\n<li>OCI_INC=C:\\Oracle\\product\\12.1.0\\dbhome_1\\oci\\include<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify;\"><strong>Installation des packages<\/strong><\/p>\n<p style=\"text-align: justify;\">L&rsquo;installation est r\u00e9alis\u00e9e en ligne de commande en tant qu&rsquo;administrateur (clic-droit sur l&rsquo;ic\u00f4ne \u00ab Invite de commande \u00bb puis \u00ab\u00a0Ex\u00e9cuter en tant qu&rsquo;administrateur\u00a0\u00bb).<\/p>\n<p style=\"text-align: justify;\">La distribution Oracle install\u00e9e sur ma machine est en 64 bits, j&rsquo;utilise donc les binaires R x64 pour les \u00e9tapes d\u2019installation suivantes:<\/p>\n<p style=\"text-align: justify;\"><a href=\"https:\/\/blog.tiran.stream\/wp-content\/uploads\/2015\/06\/Inst-Pkg.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-282 size-full\" src=\"https:\/\/blog.tiran.stream\/wp-content\/uploads\/2015\/06\/Inst-Pkg.png\" alt=\"Inst-Pkg\" width=\"933\" height=\"419\" \/><\/a><\/p>\n<ul style=\"text-align: justify;\">\n<li>Installation de DBI<\/li>\n<\/ul>\n<p style=\"text-align: justify;\"><a href=\"https:\/\/blog.tiran.stream\/wp-content\/uploads\/2015\/06\/Inst-DBI.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-280 size-full\" src=\"https:\/\/blog.tiran.stream\/wp-content\/uploads\/2015\/06\/Inst-DBI.png\" alt=\"Inst-DBI\" width=\"933\" height=\"419\" \/><\/a><\/p>\n<ul style=\"text-align: justify;\">\n<li>Installation de ROracle<\/li>\n<\/ul>\n<p style=\"text-align: justify;\"><a href=\"https:\/\/blog.tiran.stream\/wp-content\/uploads\/2015\/06\/Inst-ROracle.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-283 size-full\" src=\"https:\/\/blog.tiran.stream\/wp-content\/uploads\/2015\/06\/Inst-ROracle.png\" alt=\"Inst-ROracle\" width=\"933\" height=\"491\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify;\"><strong>Test de la connectivit\u00e9<\/strong><\/p>\n<p style=\"text-align: justify;\"><a href=\"https:\/\/blog.tiran.stream\/wp-content\/uploads\/2015\/06\/RConsole1.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-301 size-full\" src=\"https:\/\/blog.tiran.stream\/wp-content\/uploads\/2015\/06\/RConsole1.png\" alt=\"RConsole\" width=\"765\" height=\"615\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>On peut donc directement acc\u00e9der les donn\u00e9es d\u2019un SGBD Oracle \u00e0 partir de R. En revanche, il faut bien garder \u00e0 l\u2019esprit que les donn\u00e9es sont ing\u00e9r\u00e9es (via un fetch) par R. Elles montent donc en m\u00e9moire sur le poste client et cela peut poser des probl\u00e8mes pour les datasets tr\u00e8s volumineux.<\/p>\n<p style=\"text-align: justify;\">C\u2019est la limite de cette m\u00e9thode (et de R en version communautaire plus largement). Ce probl\u00e8me est adress\u00e9 par la distribution R maintenue par Oracle : ORE (pour Oracle R Enterprise) qui est en mesure de d\u00e9l\u00e9guer les op\u00e9rations lourdes au SGBD lui-m\u00eame. A suivre dans un prochain billet\u2026<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Il est possible d&rsquo;interconnecter R avec un SGBD Oracle afin de traiter directement les informations stock\u00e9es en base. On \u00e9vite<\/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":[6,12],"tags":[],"class_list":["post-278","post","type-post","status-publish","format-standard","hentry","category-oracle","category-r"],"_links":{"self":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/posts\/278","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=278"}],"version-history":[{"count":0,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/posts\/278\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=278"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=278"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=278"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}