Here we are at the last part for this series. To recap, in the first 2.5 parts of this series (part 1, part 2, part 2.5) I focused on setting up a graph database in SQL Server, and querying the metadata to discover the nodes and edges that define the structure of the graph. In the third part, I shifted over to R and embedded the SQL Server cursor script in an R file to retrieve data for manipulation. Now with this final section, I’ll show you how to use Rich Iannone’s DiagrammeR package to generate a graph visualization. At the left is a visualization I created of a Microsoft sample graph database that I used along with the one I created in the first parts of this article to test this out. You can get the code from Microsoft’s website at this link.
Below is the additional R code required to generate the visualization. A couple of Tidyverse packages (tibble amd magrittr) and the DiagrammeR package and needed and all are available on the CRAN repository. Copy and paste the code below right after the last line of the part 3 code that ends with this:
DROP TABLE ##NodeEdgeXRef; ') ## New code
# Read in distinct node names and IDs into a tibble and call the column nodes, and nodelist <- tibble(nodeName = union(nodes_edges$FromName, nodes_edges$ToName), nodeId = union(nodes_edges$FromNode, nodes_edges$ToNode)) # Create the graph object i_graph <- create_graph() %>% ## Get the nodelist tibble and define it as a table for the create_graph function add_nodes_from_table( table = nodelist, label_col = nodeName) %>% ## Define the To and From edge names add_edges_from_table( table = nodes_edges, from_col = FromNode, to_col = ToNode, from_to_map = nodeId) %>% ## set the node fontsize to 5. Adjust larger or smaller depending on table name length set_node_attrs( node_attr = fontsize, values = 5 ) %>% ## Set the node name text so it will display on the nodes set_edge_attrs( edge_attr = value, values = nodes_edges$EdgeName) %>% ## Set the edge fontsize to 5. Adjust larger or smaller depending on table name length set_edge_attrs( edge_attr = fontsize, values = 5) %>% ## Set the edge name values between nodes. set_edge_attr_to_display( edges = 1:(nrow(nodes_edges)), attr = value, default = NA); ## Display the graph render_graph(i_graph, layout = "nicely")
That’s it! Save the script and run it, and you should see the graph I showed last time for the StudentClass demo database, or if you use the Microsoft sample, the visualization at the top of this page. I am continue to explore Graph Databases and will post more information as I develop it. I’m also happy to say that Microsoft has already announced additional features for graph databases in SQL Server 2019.