R¶

Data import R¶

Using the logger component of the DMP, measurement data can be imported directly from the ORACLE database into R worksheets/scripts. This eliminates the intermediate step of first exporting the data to CSV tables in order to import them again, which is a time-consuming process. In addition, it is possible to work with the latest measurement data available in the database, provided that the data source in R is updated.

Requirements¶

Technical requirements¶

R x64 from version 3.1.0
ORACLE Client on the (Windows) Client
Access to the UFZ Intranet

These requirements are currently met on the Windows terminal server msg, which can be accessed via RDP client.

Administrative requirements¶

The necessary database views and login data will be set up by the database administrator if required. The setup can be requested informally by sending an e-mail to wkdv-logger@ufz.de, specifying the data project. The sender of the request should be a project member authorized as administrator in the data project.

Step-by-step guide¶

1. Set up external database connection¶

See Administrative Requirements.

2. Download R connection file from logger component¶

There is no need for manual configuration to establish the connection from R. Necessary connection parameters can be imported by an "R File". The configuration file is generated dynamically in the logger component and can be downloaded there, as soon as the database connection is set up, using the Download Database Query File button in the logger overview.

For downloading the connection file, the user must be assigned the right May read level 1 data in the data project.

3. Install missing packages¶

DBI package¶

Open R and install the DBI package if it is not already present.

ROracle package¶

This package is located under Y:\UFZ\ROracle.

There are 2 packed files. Unpack the ROracle folder contained in ROracle_1.2-1.zip into the same directory where DBI is located.

4. Open connection file in R¶

To use the connection to the database in R you have to execute the downloaded file in your R script. To do this, use the source command.

There are 2 ways to do this:

You move the downloaded file into the working directory of R. You can find out the working directory by executing the command getwd() in R. Afterwards you can execute the source command.

source('name_of_the_file.r'); 2. Instead of moving the downloaded file, you can also specify the complete file path. The full path includes the path to the folder where the downloaded file is located and the name of the file. After that you can execute the source command again. It is important to replace the "\" (backslash) in the path with a "/" (slash).

run('path/to/file/name_of_file.r');

5. Load preview¶

Use the following commands to load the first 50 records for control purposes.

source("R_weather_station_reservoir_observatory_Rappbode_asd.R")

sql <- paste("SELECT * FROM ", ViewName)

rs <- dbSendQuery(con, sql , bulk_read = 10000L)

data <- fetch(rs, n = 50)

data

6. Load data¶

Loading all data at once is not recommended, as it takes a long time and may cause errors. If it is still necessary, the following method is recommended:

source("R_weather_station_reservoir_observatory_Rappbode_asd.R")

sql <- paste("SELECT * FROM ", ViewName," where timestamp_measurement > to_date('01.01.1990','dd.mm.yyyy') ")

rs <- dbSendQuery(con, sql , bulk_read = 10000L)

while (!dbHasCompleted(rs)) {

data <- fetch(rs, n = 5000)

}

data

7. SQL - short explanation¶

The database connection script you downloaded provides you with 2 variables:

con - Connection object to the database
ViewName - Name of the logger from which the data is obtained

The request to the database is stored in sql.

With dbSendQuery the query is sent to the database and the result is stored rs.

To access the data it is transferred via fetch(rs) in data.

If fetch(rs , n = x) is called, x rows of the result are transferred to data.

Constraints and other SQL examples¶

All possible constraint parameters can be found under Constraint parameters.

For more SQL examples, see Examples.

For more methods of the ROracle package, see ROracle.