A Snowflake development environment — Part 1 — UIs

Question: How long does it take to build a professional grade web application showing actual Snowflake data?

An application showing the storage used in Snowflake per table

With the AppContainer for Snowflake about ten minutes.

And with professional grade the award winning design language of SAP Fiori is meant. Just a lot easier. So let me take you through the process…

Above page is described via XML: At the top should be a Select control with the list of all Snowflake schema names and below an am4Chart of type TreeChart with data from Snowflake’s TABLES data dictionary view.

Everything else is provided by the AppContainer. In particular:

• Webserver: The AppContainer is in fact a web application, provided as container for simplicity.
• Login: Users are authenticated against the Snowflake database.
• Json Restful endpoint: Every table, view and procedure of Snowflake is accessible via Restful endpoints.
• oData endpoints: Every table and view of Snowflake can be read via the oData protocol.
• Security: Is inherited from Snowflake.
• UI5 controls: The OpenUI5 library is installed, other libraries can be added by the user.
• am4Charts: An UI5 control is provided to embed the am4Charts library for visualizations — much more powerful than what UI5 provides out of the box.

Try it out!

For a quick test, the only required step is to start the container image, e.g. via docker. In this command the JDBCURL must be changed to match your Snowflake account and database.

docker run -d -p 80:8080 -e JDBCURL=jdbc:snowflake://tn0815.eu-central-1.snowflakecomputing.com/?db=MYDATABASE \
  rtdi/appcontainer:latest-snowflake

With the AppContainer up and running it can be accessed via the browser. The home page shows all available applications, the Design Time Browser being the most important one.

Because developing applications requires access to Snowflake, when clicking on the Design Time Browser, a login page is shown and the user identifies himself with the Snowflake username and password.

In the browser directories and files are created.

The index.view.xml file describes the layout of the page to write.

The concrete file content for the application is below, but most of it is just layout. The only parts actually needed are the View → Floorplan → Select & Chart.

(A graphical editor is in the works, see Editor link in the Design Time Browser. It just got a bit outdated with all the simplifications implemented recently.)

This XML describes that there should be a Select control and its data comes from the Json endpoint ui5rest/catalog/schemas. Below should be a am4chart control with data retrieved via oData from the url ui5odata/INFORMATION_SCHEMA/TABLES.

The only other interesting part are the models and how they related to the fields. At the bottom are two models defined, one with the modelName “master” of type JSONModel and another of type oDataModel. The latter has a filter value1='{master>schemaname}' referencing the schemaname field of the master model. This value is updated by the select control (selectKey='{master>/schemaname}' ) as well and the result is that whenever the user selects a different value, the master model field is updated and hence the filter does change and hence the data is re-read.

The chart takes it configuration from a Json file named StorageDistribution.json in the same folder.

Here as well, only the dataFields are mandatory, the other is optional layout (adding the table name instead of relying on tool tips only).

Once both files are created, the application can be called via a matching URL. The Design Time Browser provides the URL when open the run link next to the view definition.