In my last post, Deploying Data Mining Models using Model Export and Import, we explored using DBMS_DATA_MINING.EXPORT_MODEL and DBMS_DATA_MINING.IMPORT_MODEL to enable moving a model from one system to another.
In this post, we'll look at two distributed scenarios that make use of
this capability and a tip for easily moving models from one machine to
another using only Oracle Database, not an external file transport
mechanism, such as FTP.
The first scenario, consider a company with geographically distributed
business units, each collecting and managing their data locally for the
products they sell. Each business unit has in-house data analysts that
build models to predict which products to recommend to customers in
their space. A central telemarketing business unit also uses these
models to score new customers locally using data collected over the
phone. Since the models recommend different products, each customer is
scored using each model. This is depicted in Figure 1.Figure 1: Target instance importing multiple remote models for local scoring
In the second scenario, consider multiple hospitals that collect data
on patients with certain types of cancer. The data collection is
standardized, so each hospital collects the same patient demographic
and other health / tumor data, along with the clinical diagnosis.
Instead of each hospital building it's own models, the data is pooled
at a central data analysis lab where a predictive model is built. Once
completed, the model is distributed to hospitals, clinics, and doctor
offices who can score patient data locally.Figure 2: Multiple target instances importing the same model from a source instance for local scoring
Since this blog focuses on model export and import, we'll only discuss
what is necessary to move a model from one database to another. Here, we use the package DBMS_FILE_TRANSFER, which can move files
between Oracle
databases.
The script is fairly straightforward, but requires setting up a
database link and directory objects. We saw how to create directory
objects in the previous post.
To create a database link to the source database from the target, we can use, for example:
create database link SOURCE1_LINK connect to <schema> identified by <password> using 'SOURCE1';
Note that 'SOURCE1' refers to the service name of the remote database entry in your tnsnames.ora file.
From SQL*Plus, first connect to the remote database and export the
model. Note that the model_file_name does not include the .dmp
extension. This is because export_model appends "01" to this name.
Next, connect to the local database and invoke DBMS_FILE_TRANSFER.GET_FILE and import the model. Note that "01" is eliminated in the target system file name.
connect <source_schema>/<password>@SOURCE1_LINK;
BEGIN
DBMS_DATA_MINING.EXPORT_MODEL ('EXPORT_FILE_NAME' || '.dmp',
'MY_SOURCE_DIR_OBJECT',
'name =''MY_MINING_MODEL''');
END;
connect <target_schema>/<password>;
BEGIN
DBMS_FILE_TRANSFER.GET_FILE ('MY_SOURCE_DIR_OBJECT',
'EXPORT_FILE_NAME' || '01.dmp',
'SOURCE1_LINK',
'MY_TARGET_DIR_OBJECT',
'EXPORT_FILE_NAME' || '.dmp' );
DBMS_DATA_MINING.IMPORT_MODEL ('EXPORT_FILE_NAME' || '.dmp',
'MY_TARGET_DIR_OBJECT');
END;
To clean up afterward, you may want to drop the exported .dmp file at
the source and the transferred file at the target. For example,
utl_file.fremove('&directory_name', '&model_file_name' || '.dmp');
