2021-02-23

Visualizing tasks in Snowflake.

 I have created a number of tasks in snowflake that are have a various dependencies. 


I wanted a simple way to document these tasks, and the graph nature of how they run. 


Using a few simple queries, you can feed the output of the show tasks command into another query, and that query can be used to format the output to feed it into a tool called graphviz

Graphviz has a very simple notation for creating sophisticated images of different types of graphs. 

In this case we will use a the digraph option for a dot file to create a simple image. 

The dot notation is very simple especially when visualizing hierarchical graphs. 


Create a file in your favorite editor (notepad++) with this syntax: 


digraph G { 


Run the following queries: 

show tasks;

with task_table as (
select split_part("predecessors", '.',  3) as parent,"name" as child from (select * from table(result_scan(last_query_id(-1))))
)
select parent||'->'||child||';' from task_table where parent is not null;

The output from this command you can copy from the worksheet editor and paste between the brackets of your dot file so that the dot file will now look like this: 

 digraph G {
TASKD->TASKE;
TASKB->TASKC;
TASKA->TASKB;
TASKA->TASKI;
TASKC->TASKL;
TASKA->TASKM;
TASKG->TASKH;
TASKA->TASKN;
TASKH->TASKJ;
TASKI->TASKK;
TASKA->TASKD;
TASKE->TASKF;
TASKJ->TASKO;
TASKK->TASKP;
}

 

Save your file as something meaningful like Blog_demo.dot 

So long as you have graphviz installed correctly, once you save the file you can convert the .dot file to a png you can use for documentation like this: 

dot -Tpng Blog_demo.dot > blog_demo.png

 

And here is my anonymized graph visualization. 


Graphviz is a powerful tool for visualization, it is not so much a graph analysis tool like gephi, but it is quite sufficient for documentation, and sharing images that represent the graphs we work with every day. 


.

 

 


No comments:

Post a Comment