dbplotR

KO | EN

gitlite — search

#javascript #python #hacktoberfest #react #ai #typescript #llm #go #golang #android #machine-learning #rust #deep-learning #linux

★ 20 Open GitHub ↗

Shiny app allowing users to produce interactive graphs directly from a database connection

// repository documentation

Was this content helpful?

★ 0 (0 ratings)

dbplotR
=======

`dbplotR` is a shiny app allowing users to produce interactive graphs directly from a database connection.

![dbplotR-demo](https://raw.githubusercontent.com/dashee87/dbplotR/master/dbplotR-image.png)

Inspired by the [Big Query Visualiser app](https://github.com/MarkEdmondson1234/BigQuery-Visualiser), the user interface is generated by [shiny](https://github.com/rstudio/shiny), while [DT](https://rstudio.github.io/DT/shiny.html) and [plotly](https://github.com/ropensci/plotly) enable you to explore your data with state of the art interactive graphs.

[RODBC](https://cran.r-project.org/web/packages/RODBC/index.html) takes care of the database connection. For more information on how to perform database queries in R via an RODBC connection, please check [here](https://support.rstudio.com/hc/en-us/articles/214510788-Setting-up-R-to-connect-to-SQL-Server-) or [here](http://brazenly.blogspot.co.uk/2014/05/r-how-to-connect-r-to-database-oracle.html) (or the regular posts on Stack Overflow).

Tutorial
--------

### Required Packages

`dbplotR` requires numerous packages, which must be installed before the app can be used. If unsure, install the packages by running the following code:

``` r
install.packages("shiny")
install.packages("shinydashboard")
install.packages("shinybs")
install.packages("DT")
install.packages("plotly")
install.packages("dplyr")
install.packages("RODBC")
```

### Running the App

To run `dbplotR`, you have two options:

The other option is run the program directly from your R console via Github.

``` r
runGitHub( "dbplotR", "dashee87")
```

Or you can copy the R files to your computer and run the program locally (or deploy it to your server).

Note: To export static images from `dbplotR`, you'll need a [plotly account](https://plot.ly/accounts/login/?next=/settings) to access its API. You must then provide your username and API key to `dbplotR`, by running the following code before you run `dbplotR`.

``` r
Sys.setenv("plotly_username" = "YOUR USER NAME")
Sys.setenv("plotly_api_key" = "YOUR API KEY")
```

More information on static image export with plotly can be found [here](https://plot.ly/r/static-image-export/).

### Security

This app relies on the input and output of sensitive information (passwords, database data, etc). Such information could be intercepted and the database compromised if some precautions are not in place. Running the app on your local host (either by downloading the files or running `runGitHub("dbplotR", "dashee87")`) is probably the safest option (ensure shiny to configured to run on 127.0.0.1 (the local host)). If deployed to a server, [Shiny Server Pro](https://www.rstudio.com/products/shiny-server-pro/) features authentication, while authentication can be added to the open source version by following the instructions [here](https://auth0.com/blog/adding-authentication-to-shiny-open-source-edition/).

[SQL injections](http://shiny.rstudio.com/articles/sql-injections.html) are another security concern. This is when a security vulnerability is exploited to send unwanted SQL queries to the database. To minimise its susceptibility to such attacks, the range of SQL queries permitted in `dbplotR` is severely restricted (e.g. only select queries, semicolons are prohibited).

Status
------

This package is under active development and is currently subject to regular updates.

### Issues

If you spot any bugs (and I don't doubt you will) or have a suggestion for future versions, then I'd love to hear from you.

[Submit issues/requests here](https://github.com/dashee87/dbplotR/issues).

### Future

Short term:

-   Improve some UI features (button alignment, table rendering, etc)
-   Allow alternative database packages (see [here](http://brazenly.blogspot.co.uk/2014/05/r-how-to-connect-r-to-database-oracle.html)) and uploading of CSVs
-   Add [maps](https://plot.ly/r/choropleth-maps/) and other cool plotly graphs

Longer term:

I know it's called `dbplotR`, but I'm very keen to expand the app into statistical analysis and machine learning.

-   Hypothesis Testing (e.g. t-tests)
-   Regression
-   Forecasting (e.g. ARIMA)
-   Clustering (e.g. k-means, PCA, SVMs)

Disclaimer
----------

As stated in the security section, reckless use of `dbplotR` could leave your entire database exposed. The user is fully responsible for such problems.

dbplotR

Explore Similar Repositories

Was this content helpful?

Recent Feedback

Download README