Author: Luca.Canali@cern.ch - July 2016
Note: you don't need this step if you previously followed the training steps in the notebook MNIST_tensorflow_exp_to_oracle.ipynb
For example run this using an account with DBA privileges:
SQL> create user mnist identified by mnist default tablespace users quota unlimited on users;
SQL> grant connect, create table, create procedure to mnist;
SQL> grant read, write on directory DATA_PUMP_DIR to mnist;
impdp mnist/mnist tables=testdata,tensors directory=DATA_PUMP_DIR dumpfile=MNIST_tables.dmp
In [29]:
%%bash
sqlplus -s mnist/mnist@dbserver:1521/orcl.cern.ch <<EOF
-- create the table for test data, where the images of digits are stored as arrays of type utl_nla_array
create table testdata_array as
select a.image_id, a.label,
cast(multiset(select val from testdata where image_id=a.image_id order by val_id) as utl_nla_array_flt) image_array
from (select distinct image_id, label from testdata) a order by image_id;
-- create the table with tensor definitions, the tensors are stored as arrays of type utl_nla_array
create table tensors_array as
select a.name, cast(multiset(select val from tensors where name=a.name order by val_id) as utl_nla_array_flt) tensor_vals
from (select distinct name from tensors) a;
EOF
Notes:
In [39]:
%%bash
sqlplus -s mnist/mnist@dbserver:1521/orcl.cern.ch <<EOF
create or replace package mnist
as
-- MNIST scoring enginge in PL/SQL
-- Author: Luca.Canali@cern.ch, July 2016
g_b0_array utl_nla_array_flt;
g_W0_matrix utl_nla_array_flt;
g_b1_array utl_nla_array_flt;
g_W1_matrix utl_nla_array_flt;
function score(p_testimage_array utl_nla_array_flt) return number;
procedure init;
end;
/
create or replace package body mnist
as
procedure init
/* initialize the tensors that make up the neural network */
as
begin
SELECT tensor_vals INTO g_W0_matrix FROM tensors_array WHERE name='W0';
SELECT tensor_vals INTO g_W1_matrix FROM tensors_array WHERE name='W1';
SELECT tensor_vals INTO g_b0_array FROM tensors_array WHERE name='b0';
SELECT tensor_vals INTO g_b1_array FROM tensors_array WHERE name='b1';
end;
procedure print_debug(p_array utl_nla_array_flt)
/* useful for debugging pourposes, prints an array to screen. requires set serveroutput on */
as
begin
dbms_output.put_line('***************');
for i in 1..p_array.count loop
dbms_output.put_line('p_array(' || i ||') = ' || TO_CHAR(p_array(i),'9999.9999'));
end loop;
dbms_output.put_line('**************');
end;
function argmax(p_array utl_nla_array_flt) return integer
as
v_index number;
v_maxval float;
begin
v_index := 1;
v_maxval := p_array(v_index);
for i in 2..p_array.count loop
if ( p_array(i) > v_maxval) then
v_index := i;
v_maxval := p_array(v_index);
end if;
end loop;
return(v_index);
end;
function score(p_testimage_array utl_nla_array_flt) return number
as
v_Y0 utl_nla_array_flt;
v_output_array utl_nla_array_flt;
begin
v_Y0 := g_b0_array;
/* this is part of the computation of the hidden layer, Y0 = W0_matrix * p_test_image_array + B0 */
/* utl_nla.blas_gemv performs matrix multiplication and vector addition */
utl_nla.blas_gemv(
trans => 'N',
m => 100,
n => 784,
alpha => 1.0,
a => g_W0_matrix,
lda => 100,
x => p_testimage_array,
incx => 1,
beta => 1.0,
y => v_Y0,
incy => 1,
pack => 'C'
);
/* This is part of the computation of the hidden layer: Y0 -> sigmoid(Y0) */
for i in 1..v_Y0.count loop
v_Y0(i) := 1 / ( 1 + exp(-v_Y0(i)));
end loop;
v_output_array := g_b1_array;
/* this is part of the computation of the output layer, Y1 = W1_matrix * Y0 + B1 */
/* utl_nla.blas_gemv performs matrix multiplication and vector addition */
utl_nla.blas_gemv(
trans => 'N',
m => 10,
n => 100,
alpha => 1.0,
a => g_W1_matrix,
lda => 10,
x => v_Y0,
incx => 1,
beta => 1.0,
y => v_output_array,
incy => 1,
pack => 'C'
);
/* print_debug(v_output_array); */
/* v_output_array needs to be passed via softmax function to provide a distribution probability */
/* here we are only interested in the maximum value which gives the predicted number with an offset of 1 */
return (argmax(v_output_array) - 1);
end;
end;
/
EOF
Notes:
In [35]:
%%bash
sqlplus -s mnist/mnist@dbserver:1521/orcl.cern.ch <<EOF
exec mnist.init
select mnist.score(image_array), label from testdata_array where rownum=1;
EOF
Notes:
In [38]:
%%bash
sqlplus -s mnist/mnist@dbserver:1521/orcl.cern.ch <<EOF
exec mnist.init
set timing on
select sum(decode(mnist.score(image_array), label, 1, 0)) "Images correctly identified",
count(*) "Total number of images"
from testdata_array;
EOF
In [ ]: