Configuring an ROracle Client with Autonomous Data Warehouse

This article is originally published at https://blogs.oracle.com/r/compendium/rss

For users who want to connect to Oracle Autonomous Database (ADB) from an R client today, use the ROracle package. An ROracle client can easily be configured to create a database connection. To establish the connection, both an R and ROracle installation are required, along with the Oracle Database Client and an Oracle Wallet.

Installation R and ROracle

Either open source R or Oracle R Distribution can be used to establish a connection to ADB.  When installing open source R, follow the instructions in R's Installation and Administration Guide. The instructions for installing Oracle R Distribution are in the Oracle R Installation and Administration Guide.  

The installation process for the ROracle source package is provided on CRAN and binaries for specific R versions are hosted on Oracle's Technology Network.  

Installing the Database Client

ROracle requires an installation of Oracle Database client software to enable communication between an R client and an Oracle Database. The Database client can be either Oracle Database Client or Oracle Database Instant Client:

• Oracle Database Client is distributed with Oracle Database and is based in the Oracle home of the Database.
• The Oracle Database Instant Client is a free, standalone implementation of Oracle Database Client. Oracle Instant Client is not based in an Oracle home directory and requires less disk space than Oracle Database Client. 

Detailed instructions for installing and configuring the Instant Client can be found here. Note that when running a 64-bit version of R, you also need a 64-bit version of the Oracle Client.

Configuring the ADB Wallet for use with ROracle

With Oracle Autonomous Database, establishing a connection from a client system requires an Oracle Wallet. A Wallet is a password-protected container for storing security credentials for Oracle Database connections. By storing connection credentials in an Oracle Wallet, we avoid exposing database credentials in clear text files. 

Autonomous Data Warehouse provides a pre-configured Wallet to support Oracle Net Services. The Oracle Wallet files are downloaded from the ADB UI by a service administrator. Your Administrator will also supply an associated Wallet name, Wallet user name, and Wallet password.

The required client Wallet credentials are contained in a zip file including: 

• tnsnames.ora and sqlnet.ora: Network configuration files storing connect descriptors and SQL*Net client side configuration.
• cwallet.sso and ewallet.p12: Auto-open SSO wallet and PKCS12 file. 
• keystore.jks and truststore.jks: Java keystore and truststore files. 
• ojdbc.properties: Contains the wallet related connection property required for JDBC connection.

Note that the ojdbc.properties Wallet file is not required for an ROracle connection.

Under the Oracle Instant Client, create a network/admin subdirectory hierarchy under the Oracle Client directory. Copy all of the Wallet files to this subdirectory. For example, if you installed Instant Client on Windows in C:/instantclient_18_5, the Wallet files should be saved to the directory:

C:/instantclient_18_5/network/admin

If you are using a full Oracle Client, move the file to $ORACLE_HOME/network/admin. Alternatively, place the unzipped wallet files in a secure directory and set the TNS_ADMIN environment variable to that directory name. 

Establishing a Database Connection 

Once R, ROracle, the Oracle Database Client and the ADB client Wallet files are installed and configured, you're ready to connect to ADB.  Load the ROracle package in your R session and create a database connection using the Wallet name and password:

> library(ROracle)> drv <- dbDriver("Oracle")> con <- dbConnect(drv, dbname="<wallet name>", username="<walletusername>", password="<wallet password>")

After establishing a connection, execute a quick test to ensure your environment is configured correctly.  This example creates a table from the  R data frame iris, reads the table back into an R data frame, and then disconnects from the ADB session.

> dbWriteTable(con, "IRIS", iris) [1] TRUE> df <- dbReadTable(con, "IRIS")

Select rows based upon a set condition:

> dbGetQuery(con, 'SELECT * FROM iris WHERE "Sepal.Length" < 4.6')   Sepal.Length Sepal.Width Petal.Length Petal.Width Species 1          4.3         3.0          1.1         0.1  setosa 2          4.4         3.0          1.3         0.2  setosa 3          4.4         2.9          1.4         0.2  setosa 4          4.6         3.1          1.5         0.2  setosa 5          4.4         3.2          1.3         0.2  setosa 6          4.6         3.2          1.4         0.2  setosa 7          4.6         3.6          1.0         0.2  setosa 8          4.5         2.3          1.3         0.3  setosa 9          4.6         3.4          1.4         0.3  setosa

Disconnect from Oracle Database:

> dbDisconnect(con) [1] TRUE

To see the available in the ROracle package, refer to the function guide.  For more information regarding ADB, refer to the Autonomous Data Warehouse User's Guide.

 

Thanks for visiting r-craft.org
This article is originally published at https://blogs.oracle.com/r/compendium/rss
Please visit source website for post related comments.