How to Write Data Wrangling Result Back to PostgreSQL in R and Exploratory

Sometimes you want to write your data wrangling or even data analysis result back to your databases like PostgreSQL. And this is relatively simple to do in R and in Exploratory.

I’m going to demonstrate this by using R custom script feature in Exploratory following the two steps below.

• Create a Custom R Function
• Call the R Function as part of Data Wrangling steps

Here is how.

Create a Custom R Function

Cick plus button next to Script at the left hand side pane to open the R Script editor.

And enter name in the Create Script Dialog.

Then type in the below script. Make sure to replace host, port, dbname, user, password to point to your database. (This example is for PostgreSQL)

writebackToPostgre <- function(df){
  # get connection
  library("DBI")
  library("RPostgres")
    drv <- RPostgres::Postgres()
  conn <- RPostgres::dbConnect(drv, dbname="test", user = "myuser", password = "mypassword", host = "myhost", port = 5432)
  DBI::dbWriteTable(conn, "writeback_test", df)
  DBI::dbDisconnect(conn)
  # Make sure to return df at the end.
  df
}

This is an R function that connects to a specified database and write a given data frame data back to the database. At the end, it will return the same data frame that it receives so that this function can be used as part of any data wrangling steps.

Call the Custom Function As a Custom Command

Now it’s time to call the R function we have just created above. To call, you want to get into a Command Input mode by selecting ‘Custom Command’ from the Plus menu like below.

Then type in writebackToPostgre() in the input field.

As mentioned above, this function takes a data frame data from the previous step and writes back to a specified database (PostgreSQL in this case). Once that’s done, it will return the same data frame data.

Once you click green Run button, the data will be saved to your database.

How to stop Write Back

Now, probably you don’t want to write the data back to the database every time you open this data frame or update the data wrangling steps.

For this, you can temporary disable the ‘write back’ step.

Make sure you have selected the Write Back step at the right hand side data wrangling step.

Click the Disable Step icon.

This will temporally disable this step so that this step will be ignored when it runs the data wrangling steps until you enable it back.

This is just an example of writing the data wrangling or analysis result back to Amazon Redshift database. But by using the R custom function feature you can do a lot more. There is another post about how to save the result in JSON format , though that is now supported from the menu! ;)

Exporting data in JSON:

Try it for yourself!

If you don’t have Exploratory Desktop yet, you can sign up from here for free. If you are currently a student or teacher, then it’s free!

Learn Data Science without Programming

If you are interested in learning various powerful Data Science methods ranging from Machine Learning, Statistics, Data Visualization, and Data Wrangling without programming, go visit our Booster Training home page and enroll today!