MariaDB and OracleDB with R
Integrating formal databases (MariaDB and OracleDB) with R
Setting up Databases (MariaDB and OracleDB)
Prior to integrating the databases with R, MariaDB and Oracle 10g were successfully installed.
MariaDB and R
In order to integrate MariaDB with R, the following packages were installed and loaded to R:
"rJava", "DBI" and "RJDBC". Using JDBC() function that is provided by RJDBC package, relevant DBMS driver was designated. Moreover, four variables, (driver, url, id, password) were applied in dbConnect() function and thus got connected to database.
*There are mainly two methods, ODBC and JDBC, to connect R with databases. Out of the two methods, JDBC was chosen.
The following screenshot of RStudio highlights the connecting process.
Records of a database, 'new_db', inside the MariaDB can be examined and they can be brought into RStudio in data frame format. Reading contents can be conducted through R or SQL as shown below.
Records inside the 'items' can be queried with conditions using SQL statements. The following screenshot underscores an example.
Similarly, adding, fixing and deleting a record can be done with SQL statement through the use of R. All the execution of the statements are directly affecting the actual data of the MariaDB.
The following three screenshots highlight adding, fixing and deleting of a record.
*Adding of a new record
*Fixing of a record
*Deleting of a record
Excel file containing closing prices of SP500 and Stocks can be saved into database through the use of R as well. The following diagram highlights this.
To begin, the content of the stock data in excel file contains dates and closing prices of SP500 and other stocks.
*stock data in excel file
In order to save this data into database, a new table, 'stock_data', within database was created. Then, the excel file was read from R and the stock data was saved into the newly created table 'stock_data'. The following screenshot highlights this process.
This can be also checked with MySQL Client console as shown below.
In addition, conditional query with the data within the database can be done through the use of R as shown below.
Oracle DB with R
As mentioned before, Oracle Db 10g was installed with user 'Dong_Jun'. Similar to MariaDB, packages such as DBI, rJava and RJDBC were installed and loaded to RStudio.
*Creating user 'Dong_Jun' for Oracle DB
*Connecting Oracle DB with R
Using SQL syntax on RStudio, the database was empty; thus, a new table, 'student', was created on the Oracle database. The following screenshot of RStudio underscores this.
Similar to MariaDB, adding, fixing and deleting a record on a database can be done through R.
Lastly, conditional query is possible with SQL syntax on R as shown below.
0コメント