Using the Netezza Analytics Matrix Engine

Felt like writing something about the topic due to the lack of available examples out there in the internet.
-- initialize the engine
CALL NZM..INITIALIZE();
--create some random matrix
CALL NZM..CREATE_RANDOM_MATRIX('A', 10, 10);
CALL NZM..CREATE_RANDOM_MATRIX('B', 10, 10);
--let's try adding the matrix. Put the result in matrix C
CALL NZM..ADD('A', 'B', 'C');
--now let's see the result -> create a table from the matrix
CALL NZM..CREATE_TABLE_FROM_MATRIX('C', 'TABLE_C');
--look at the content.
select * from table_c order by row, col;
--to check the results, let's create additional tables from matrix A and B and calculate manually.
CALL NZM..CREATE_TABLE_FROM_MATRIX('B', 'TABLE_B');
select * from table_b order by row, col;
CALL NZM..CREATE_MATRIX_FROM_TABLE('TABLE_A', 'A', 10, 10);
select * from table_b order by row, col;
--let's try some real-life application.
/*  Begin Ego Network Centrality Calculation  (based on Borgatti, 2004)*/
/* formula for ego betweenness
A^2*[1-A], where A is the adjacency matrix of node 'i'.
*/
/*
suppose we have an adjacency matrix in EGO_NTWK_3. Content of the table must be in "row, col, value" format so that it could be converted to matrix later. Below is some sample data:
row,col,value
1,1,0
1,2,1
1,3,1
1,4,1
1,5,1
2,1,1
2,2,0
2,3,1
2,4,0
2,5,0
3,1,1
3,2,1
3,3,0
3,4,1
3,5,0
4,1,1
4,2,0
4,3,1
4,4,0
4,5,0
5,1,1
5,2,0
5,3,0
5,4,0
5,5,0
*/
select * from EGO_NTWK_3;
--create matrix from adjacency matrix table
call nzm..create_matrix_from_table('EGO_NTWK_3', 'EGO_NTWK', 5, 5);
--get geodesics of length 2, A^2
CALL nzm..mtx_pow2('EGO_NTWK',2,'EGO_NTWK_SQ');
--create ones matrix
call nzm..create_ones_matrix('ONES',nzm..get_num_rows('EGO_NTWK'),nzm..get_num_rows('EGO_NTWK'));
--preparing [1-A]
CALL NZM..SUBTRACT('ONES', 'EGO_NTWK', '1_MINUS_EGO');
--calculating A^2*[1-A]
CALL NZM..MULTIPLY_ELEMENTS('EGO_NTWK_SQ', '1_MINUS_EGO', 'RESULT');
--transfer result in a new table
CALL NZM..CREATE_TABLE_FROM_MATRIX('RESULT', 'TABLE_R');
--clear all matrix for future calculations
CALL NZM..DELETE_ALL_MATRICES;
--get reciprocal of matrix for values above diagonal (since below diagonal are just duplicates). The sum is the ego betweenness score for 'i'.
select sum(reciprocal) from (
select row,col,1/value as reciprocal from table_r
where col < row
and value != 0
order by row, col
) A;
/* The ego-betweenness score should be 3.5 */


For more details on the subject, check out the guides by IBM:

1. Netezza Matrix Engine Reference Guide
2. IBM Netezza Analytics Matrix Engine Developer's Guide

Comments

Popular posts from this blog

HIVE: Both Left and Right Aliases Encountered in Join

Assign select result to variable in Netezza stored procedure

Splitting value in Netezza using array_split