-
Notifications
You must be signed in to change notification settings - Fork 0
02_GettingData
April 27, 2018 - Mike McMahon (mike.mcmahon@dfo-mpo.gc.ca)
This function connects to Oracle and extracts some key database tables directly to the folder you specify via data.dir. Everytime you load or reload a particular dataset, it will load the data in the state as it was downloaded - your filtering never impacts the data you downloaded. For historical datasets, you should never need to re-extract them.
For databases that are continually being loaded, you can re-extract the data whenever you want - overwriting the previous version you had.
Two different packages might come to mind when extracting data from Oracle using R - RODBC and ROracle. RODBC seems to be much more common, but extractions using ROracle are about 5x faster. For this reason, this package can use either. RODBC is used by default, but the parameter use.pkg = 'roracle' allows the user to use ROracle instead.
For RODBC to work, please install the "MAR BIO ODBC Configuration" application from the DFO Application Catalogue. It will say that installation failed, but it generally works anyways. Please ensure that you are set to use 32-bit R, or you'll see a message that says something about an "architecture mismatch". You can get RODBC working with 64-bit R, but that's beyond the scope of this document.
library(Mar.datawrangling)
# Select the datasource you want and
# choose where you want the data stored
get_data('rv', data.dir ="/some/folder") You will now be prompted for your oracle credentials Assuming you have them, you'll see something like...
Looked in '/some/folder' for required *.rdata files, but you are missing the following:
[1] "RV.GSCAT" "RV.GSDET" "RV.GSINF" "RV.GSMISSIONS" "RV.GSSPECIES" "RV.GSSTRATUM" "RV.GSXTYPE"
Press E to extract all the data, or any other key to cancel: Assuming the directory stated above is an appropriate place, you would type "E" to extract the data.
Only after you've elected to extract the data does the package verify that you are actually allowed to view the data you indicated.
Successfully connected to Oracle via RODBC
Verifying access to RV.FGP_TOWS_NW2 ... success
Verifying access to RV.GSCAT ... success
Verifying access to RV.GSDET ... success
Verifying access to RV.GSINF ... success
Verifying access to RV.GSMISSIONS ... success
Verifying access to RV.GSSPECIES ... success
Verifying access to RV.GSSTRATUM ... success
Verifying access to RV.GSXTYPE ... success
...If any of these checks say "failed", instead of "success", you will not be able to do the extractions. Please check with Mike.McMahon@dfo-mpo.gc.ca to see if you can get permissions to the problematic Oracle objects. Mike may not be allowed to grant you access himself, but he'll know who to check with.
Once you've done a successful extraction, you can always load the data using the same command, get_data(data.dir ="/some/folder"). This includes cases where you've applied a filter incorrectly and want to restore the data in the environment back to its virgin state.
Loading data...
Loaded RV.GSCAT... (Data modified 45 days ago.)
Loaded RV.GSINF... (Data modified 45 days ago.)
Loaded RV.GSDET... (Data modified 45 days ago.)
Loaded RV.GSMISSIONS... (Data modified 45 days ago.)
Loaded RV.GSSTRATUM... (Data modified 45 days ago.)
Loaded RV.GSXTYPE... (Data modified 45 days ago.)
Loaded RV.GSSPECIES... (Data modified 45 days ago.)
Loaded RV.FGP_TOWS_NW2... (Data modified 45 days ago.)
5 seconds to load...The data in the example above was extracted 45 days ago. If you know that new data has been added to the database since your original extraction, you might want to re-extract the data, via:
get_data('rv', data.dir ="/some/folder", force.extract = TRUE) This will overwrite your local copies of the data.