Analytic databases are the industry standard for maintaining large data sets. As a data practitioner, it is essential to interact with these databases. The octopus package aims to be the default tool for interacting with and managing databases.
One of the most important goals of the octopus package is multilingual support for various databases. The name octopus analogizes the many arms of an octopus to the many compatible connection types of the package.
Under the hood, the octopus package starts a shiny application that receives input from the browser. It takes that input and translates it to the specific SQL dialect for the database you are working with.
Take, for example, the following two ways of querying data from a table. In Postgres, you would specify the exact table name using quotations.
However, in MySQL you would specify the exact table name using back ticks.
Subtle differences like this can be annoying to remember, but you can simply click the View button in the octopus interface and you will get a preview of the table regardless of the SQL dialect.
Here is a list of the currently supported databases.
Let’s demonstrate how to explore a database using the DBI package alone versus using the octopus package. Let’s begin by connecting to a MySQL database that we want to explore.
We can list all the tables available in the database by using
#  "actor" "actor_info" #  "address" "category" #  "city" "country" #  "customer" "customer_list" #  "film" "film_actor" #  "film_category" "film_list" #  "film_text" "inventory" #  "language" "nicer_but_slower_film_list" #  "payment" "rental" #  "sales_by_film_category" "sales_by_store" #  "staff" "staff_list" #  "store"
To see the details of a table we can use
DBI::dbReadTable() to read the entire table into our R
Using this approach works fine for a single table, but what if we want to view many tables, or even different schemas? We would need to copy and paste our code and change the table name each time.
Rewriting our code repeatedly becomes tedious; especially if we don’t know exactly where the data is in the database that we need.
Let’s try a different approach. Let’s use octopus to interact with the database through the application interface.
Now we can easily scroll through the different tables in the database and preview them using the View button. We also know exactly how many rows a table has without having to bring the data into our R session.