R, Python 분석과 프로그래밍의 친구 (by R Friend)

----------------------------------------------

-- Graph Analytics with Greenplum and MADlib

----------------------------------------------

-- CREATE VERTEX TABLE

DROP TABLE IF EXISTS vertex;

CREATE TABLE vertex(

id INTEGER

) DISTRIBUTED RANDOMLY;

INSERT INTO vertex 

VALUES

(0), 

(1), 

(2), 

(3), 

(4),

(5),

(6);

select * from vertex

-- CREATE EDGE TABLE

DROP TABLE IF EXISTS edge;

CREATE TABLE edge(

src INTEGER,

dest INTEGER,

user_id INTEGER

)

DISTRIBUTED BY (user_id);

INSERT INTO edge 

VALUES 

(0, 1, 1), (0, 2, 1), -- user id 1

(0, 4, 1), (1, 2, 1),

(1, 3, 1), (2, 3, 1),

(2, 5, 1), (2, 6, 1),

(3, 0, 1), (4, 0, 1),

(5, 6, 1), (6, 3, 1),

(0, 1, 2), (0, 2, 2), -- user id 2

(0, 4, 2), (1, 2, 2),

(1, 3, 2), (2, 3, 2),

(3, 0, 2), (4, 0, 2),

(5, 6, 2), (6, 3, 2);

select * from edge;

-- (1) Compute the PageRank with All IDs

DROP TABLE IF EXISTS pagerank_out, pagerank_out_summary;

SELECT madlib.pagerank(

'vertex' -- Vertex table

, 'id' -- Vertex id column

, 'edge' -- Edge table

, 'src=src, dest=dest' -- Comma delimited string of edge arguments

, 'pagerank_out' -- Output table of RageRank

, NULL -- Default damping factor (0.85)

); 

SELECT * FROM pagerank_out ORDER BY pagerank DESC;

-- (2) Compute the PageRank of vertices associated with each user using the grouping feature

DROP TABLE IF EXISTS pagerank_gr_out, pagerank_gr_out_summary;

SELECT madlib.pagerank(

'vertex' -- Vertex table

, 'id' -- Vertex id column

, 'edge' -- Edge table

, 'src=src, dest=dest' -- Comma delimited string of edge arguments

, 'pagerank_gr_out' -- Output table of PageRank

, NULL -- Default damping factor (0.85)

, NULL -- Default max iterations (100)

, 0.00000001 -- Threshold

, 'user_id'); -- Grouping column name

SELECT * FROM pagerank_gr_out ORDER BY user_id, pagerank DESC;

-- (3) Personalized PageRank of vertices {2, 4}

DROP TABLE IF EXISTS pagerank_pers_out, pagerank_pers_out_summary;

SELECT madlib.pagerank(

'vertex' -- Vertex table

, 'id' -- Vertex id column

, 'edge' -- Edge table

, 'src=src, dest=dest' -- Comma delimited string of edge arguments

, 'pagerank_pers_out' -- Output table of PageRank

, NULL -- Default damping factor (0.85)

, NULL

-- Default max iterations (100)

, NULL -- Default Threshold (1/number of vertices*1000)

, NULL -- No Grouping

, '{2, 4}' -- Personalization vertices

);

SELECT * FROM pagerank_pers_out ORDER BY pagerank DESC;

SELECT * FROM pagerank_pers_out_summary;

#!/usr/bin/env python2

# -*- coding: utf-8 -*-

"""

Created on Tuesday Jau 29 2019

@author: Hongdon Lee

"""

#%% network visualization using Graphviz

import numpy as np

import pandas as pd

import pygraphviz as pgv

def run_query(query):

     import pandas as pd

     import psycopg2 as pg

    # DB Connection

     conn = pg.connect(host='localhost',

                       port='5432', 

                       dbname='gpadmin', 

                       user='gpadmin', 

                       password='pivotal')

     # Get a DataFrame

     query_result = pd.read_sql(query, conn)

     conn.close()

     return query_result

#%% Network Edge Table

query = """

select a.*, b.pagerank 

    from edge a

    left outer join pagerank_out b on a.src = b.id

;

"""

edge_pagerank = run_query(query)

#%% PageRank Values

query = """

select * from pagerank_out;

"""

pagerank_out  = run_query(query)

#%% NW Visualization using Graphviz with different size proportional to PageRank

import pygraphviz as pgv

# Generating the output flow_graph with PyGraphviz

flow_graph = pgv.AGraph(strict=False, directed=True) # directed graph

# Flow Direction(Left to Right, or Top to Bottom)

flow_graph.graph_attr['rankdir'] = 'LR' # from Left to Right

# Node Shape

flow_graph.node_attr['shape'] = 'circle'

# Making node with different size proportional to PageRank

for i in range(len(pagerank_out)):

    label_text = str(pagerank_out.id[i]) + '\n(' + str(pagerank_out.pagerank[i].round(decimals=2)) + ')'

    node_width = pagerank_out.pagerank[i]*10

    node_height = pagerank_out.pagerank[i]*10

    flow_graph.add_node(str(pagerank_out.id[i]), 

                        label=label_text, 

                        **{'width': str(node_width), 

                           'height': str(node_height)})

# Adding edge with different color by user_id

colors = ['blue', 'red']

for i in range(len(edge_pagerank)):

    if edge_pagerank.user_id[i] == 1:

        color_text = colors[0]

    else:

        color_text = colors[1]

    flow_graph.add_edge(str(edge_pagerank.src[i])

                        , str(edge_pagerank.dest[i])

                        , color = color_text)

#----- Finally, Draw the Network Diagram using dot program :-)

flow_graph.draw("/Users/ihongdon/Documents/nw_diagram.png", prog='dot')