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');
