CYBERTEC PostgreSQL Logo

Visualizing data in PostgreSQL with R Shiny

09.2017 / Category: / Tags:

R is a free Open Source programming language and statistics package that is excellent for data analysis, statistics and graphics. It has been developed in the 90s and its community has steadily grown over the years. Developers and analysts can use R to analyze data from many different sources including PostgreSQL. This article shows, how an easy R application can be written to visualize data in a trivial PostgreSQL database. For the actual visualization, we have used an extension to R, which is called Shiny. It allows you to write web applications extremely fast using very little code.

A simple PostgreSQL driven web application in R Shiny

For the sake of simplicity we create a simple table containing just two columns:

To keep things simple just 10 rows have been added to the table. R does not provide built-in relational database access capabilities. Instead various interfaces are available through the Comprehensive R Archive Network (CRAN). Support for PostgreSQL is already available as well.

Our entire R Shiny application consists of just 3 files: global.R, server.R and ui.R. Here is the content of global.R:

What happens here is that a couple of R libraries are included into our little program. Then a pooled connection to PostgreSQL is opened. Our program can then use the connection to fetch some data from our simplistic database.

In the next step the user interface (UI) can already be implemented: Again R Shiny offers really simple means to draw the layout. Our page will consist of a main panel and a sidebar, which will hold two input sliders, which are fed by those two SQL queries, you have just seen in global.R.
The goal of our application is to look for data in the database and to apply those filters represented by our sliders.

Here is the content of ui.R:

Now that global functions and the GUI are in place, we can turn our attention to server.R, which will contain all the serverside logic to actually create the data displayed in our data table. We run a dynamic SQL statement and feed the output of the SQL directly to the data table, which is really easy to do in R Shiny:

Running the R Shiny code

Hacking up a small Shiny application is really easy and running it is not hard either:

First we start a simple R shell and call “shiny::runApp”. If all libraries are in place a browser window will open and the application is ready for use. In case libraries are missing, R will error out and you have to install those missing components from CRAN.

r shiny 1
r shiny 2
r shiny 3

Leave a Reply

Your email address will not be published. Required fields are marked *

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram