An R Shiny App to Upload Database Tables
Check out {octopus}; the R package implementation of this shiny app.
Introduction
Have you ever needed to quickly upload a CSV file to a database but find that such an operation is unavailable in your SQL editor? My coworkers and I run into this problem often.
While writing a few lines of R code to upload a table was easy enough for me, it wasn’t for my coworkers with little programming experience.
So, I set out to build a simple Shiny application that would help my coworkers connect to a database and upload a CSV file. Along the way I also built in features to view tables, delete tables, send queries, and manage connections.
Getting Started
The first screen you see when you begin running the app is the connections page.
You will not see any connections loaded at first but you can easily add your connection credentials by clicking the Manage Connections button. These credentials will be saved in a csv file on your local computer and not available on the internet. The next time you run the app they will be automatically populated.
Once you connect to a database you will be able to select a schema and table. You can choose to view tables, delete tables, upload CSV files, or send queries.
If you choose to view the table or send a query, then you’ll see the result in a modal popup. If the number of rows of the result is less than 50,000 then you will be able to download it.
Running From GitHub
The other nice part about this Shiny App is that the code is hosted on GitHub so anyone can run it with the simple command:
shiny::runGitHub("database_app", "MCodrescu")
However, keep in mind that you will need to have the following R packages installed on your computer: shiny, shinyjs, readr, RPostgres, dplyr, glue, janitor, shinyAce, and DT.
Check it out and let me know what you think! Note: I am not a professional security expert so please use this application at your own risk.
Conclusion
This application has made it significantly easier for my coworkers and I to move data in and out of our team’s database. At the time of writing, the application supports Postgres and Teradata but I hope to include others in the future.
Thanks!
