Page 3: Creating dynamic diagrams with Mermaid

The JavaScript-based tool Mermaid can be used to generate images in Polyglot Notebooks. However, Mermaid does not support variables. Therefore, at this point the Mermaid code is generated dynamically using C#, based on the output of the SQL query. The generated Mermaid code can call a command in the Mermaid subkernel, which then generates the graphic.

The required variables have already been split from the SQL subkernel at this point. The product ID can be used to generate the basis for the pie chart including the heading in a string (see Listing 4).

All entries from the result of the query are now appended to this base one after the other in a loop to then execute asynchronous code on the Mermaid subkernel with the Kernel.Root.SendAsync() command.

#!share --from sql-DemoKernel QueryResult
#!share --from sql-DemoKernel productId

var mermaidCodeStringBuilder = new StringBuilder();
mermaidCodeStringBuilder.AppendLine($"""
pie showData
    title Customers who bought product '{productId}'

"""
);

foreach (var element in QueryResult[0].Data){
    var customerId = element.FirstOrDefault(x => x.Key == "CustomerID").Value ?? "Unknown";
    var orderQty = element.FirstOrDefault(x => x.Key == "OrderQty").Value ?? "0";
    mermaidCodeStringBuilder.AppendLine($"\"{customerId}\": {orderQty}");
}

await Kernel.Root.SendAsync(new SubmitCode(mermaidCodeStringBuilder.ToString(), "mermaid"));

Listing 4: Generating Mermaid code and calling the Mermaid subkernel in a C# cell

The pie chart generated by Mermaid in Figure 5 shows which customer has purchased product 988 how often and is therefore affected by the recall.

Comparison of file formats

The two file formats .ipynb and .dib are available for Polyglot notebooks. The .ipynb is the classic Jupyter notebook format, which saves the output of the code in the file. Output here means the output of executed code, which may contain sensitive data, such as secrets or personal or company-related data, and makes it difficult to check notebooks into source code management systems. It must therefore be ensured that the output is deleted. The format ensures compatibility with the Jupyter format and makes it possible to edit notebooks in other applications, such as JupyterLab.

The .dib format, on the other hand, saves the code in its own structure, which is incompatible with Jupyter products. However, the advantage of this format is that the notebook does not contain the output of the executed code, which makes it easier to work with sensitive data such as secrets and a code management system such as Git. In addition, the structure is flatter than the JSON structure of .ipynb files, making it easier to track code changes during the code review.

If compatibility with the Jupyter format is not required, the .dib format should be used due to the advantages described.

Stumbling blocks and limitations

Unfortunately, there are a few stumbling blocks and limitations that were noticed when creating the example used here. Firstly, there is the aforementioned problem with the .ipynb file format, which is not specific to Polyglot notebooks. The alternative format from Poylglot Notebooks (.dib) is a suitable solution. In addition, the official documentation requires improvement in various places, has gaps and delays the actual goal of implementation. Since Mermaid and HTML do not support variables, the dynamic generation of diagrams or HTML is cumbersome.

A bigger problem, however, is that the Polyglot notebook kernel can enter a faulty state under unknown circumstances and require a kernel restart. If IDE features such as code completion or syntax and error highlighting no longer work, this can be remedied by temporarily selecting a different code cell and then switching back. If necessary, a complete restart of VS Code is required.