Write to Database using Tableau Prep and R

Hello Friends!

I know this functionality was just announced at the keynote for Tableau Prep at #Data19, but I’m impatient. So I developed a fairly simple workaround using R.

This is broken into 3 parts – the R section, the database section, and the needs of Tableau Prep.

My Setup

I have Tableau Prep 2019.3 and R 3.5.3. and inserting my data into a MariaDB database. For those that don’t know, MariaDB is an open-source version of MySQL, spurred up when MySQL was bought by Oracle.

The Tableau Prep Section

For Tableau Prep, we’re going to be loading the Excel file from #SportsVizSunday LIVE. We are loading the “Data” tab, and only a couple columns. You can add others, but I chose 3.

1.) R doesn’t like spaces in the column names, so once you load it into Tableau Prep, remove the white space in the column names. I added underscores, but you can just remove the white space also.

write_to_db_01

2.) I chose the school, mapping and total_expenses fields for a quick example.

3.) Add a Script step and an Output step like so:

write_to_db_02

I just setup a quick output to write to a csv file (you won’t need it).

Setting up the Table in the Database

Next we need to create the table in the database. If you need to know how to download MariaDB, here’s the website and follow the instructions in the install file.

Once you get it up, I have a simple database called tableau_test and used a simple create table SQL to create the table structure:

CREATE TABLE `ncaa_spend` (
`school2` VARCHAR(250) NULL DEFAULT NULL,
`mapping2` VARCHAR(250) NULL DEFAULT NULL,
`total_exp` INT(11) NULL DEFAULT NULL
)
COLLATE=’latin1_swedish_ci’
ENGINE=InnoDB
;

Setting up the R Script

For this task, A lot of the heavy lifting is done in R, and it’s really not that heavy.

You need the helper library, DBI, and after that the script is fairly simple.

library(DBI)

df2 <- data.frame(school2 = as.character(df$school)
,mapping2 = as.character(df$mapping)
,total_exp = as.integer(df$total_expenses));

con <- dbConnect(RMariaDB::MariaDB(), host = "localhost", user = "root", password = "root", dbname="tableau_test")
dbAppendTable(con, "ncaa_spend", df2)
dbDisconnect(con)

We then wrap it in a helper function, so we can use it in Prep:

write_table_prep2 <- function(df) {

library(DBI)

df2 <- data.frame(school2 = as.character(df$school)
,mapping2 = as.character(df$mapping)
,total_exp = as.integer(df$total_expenses));

con <- dbConnect(RMariaDB::MariaDB(), host = "localhost", user = "root", password = "root", dbname="tableau_test")
dbAppendTable(con, "ncaa_spend", df2)
dbDisconnect(con)
return(df2)
}

We also need our Tableau Prep helper function so we can define the new variables we created.

getOutputSchema <- function() {

return (data.frame (
school2 = prep_string (),
mapping2 = prep_string (),
total_exp = prep_int()

));
};

I then plopped it in an R file called write_to_db_prep.R

Putting it all together

In the script section of Tableau Prep, specify your R file and function. Here’s what mine looks like:

write_to_db_03

By clicking run, you should have data in your table and data in a csv file, which you can discard.

write_to_db_04

That’s it! Now you can insert data into a database using Tableau Prep!

Any questions, reach out!

-Paul

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s