How to write to a database

0:00Hey, it's Tim here. And in Tableau 2020.3,

0:03Tableau have added the capability to write

0:06to database. Now, you've always been able

0:08to connect to database for some time now.

0:11In fact, Tableau have been adding new

0:13connectors pretty much every month or so to

0:15Tableau prep.

0:16But now we can finally write that data back

0:18to a database instead of having to publish

0:20it up to Tableau server. In today's video,

0:23I'm going to be showing you the full range

0:25of features. But if you haven't watched it

0:27yet, I uploaded another video yesterday

0:29showing

0:29you how to create your own Postgres

0:31database on your laptop so that you can

0:33follow along

0:34with this particular guide. Now, that's

0:36meant to be used in cases where you just

0:39want to

0:39play around with a database on your laptop.

0:41You're not trying to do anything in a

0:42production

0:43level database. So go ahead and watch that

0:45video if you want to follow along.

0:46Otherwise,

0:47sit back, relax. I'm going to go through

0:49this in as much detail as I can. Check out

0:51the

0:51little chapters that I've got here in the

0:53bottom of the window. And yeah, let's get

0:56stuck in. So I'm here in Tableau prep, you

0:58've always been able to connect to lots of

1:00databases

1:00here on the left hand side. And you can see

1:02there's quite a long list here. I'm on

1:04Windows,

1:04so this list is a little bit longer than if

1:06you're on a Mac. Now, the key thing is we

1:08've

1:08always been able to connect to these

1:10databases, but we haven't been able to

1:11write to these

1:12databases. So for now, what I'm going to do

1:15is I'm going to close this. And if you're

1:17going to follow along, you can actually

1:20start by using one of the pre built work

1:21flows that

1:22comes with Tableau prep, I'm going to

1:23choose the superstore workflow here on the

1:25bottom

1:26left, going to go ahead and click that. And

1:28now you'll see that it opens up my workflow

1:30.

1:30Okay, so we've got a nice canvas here. And

1:32what I want to do is change one of these

1:34outputs

1:35to be a right to database output. So I'm

1:37going to go ahead and click on that output

1:39here.

1:40And you'll notice when I do that, it gives

1:42me a preview. Actually, because I haven't

1:44run this flow, when I clicked on that link,

1:46it basically ran a sample of the data

1:48through

1:48the entire workflow. And it's showing me

1:50the result here. In this case, the data is

1:52quite

1:53small. So this is actually all the data

1:55that comes through. Now the subtle addition

1:57here

1:57is in 2020.3, if you go to this output drop

2:01down just here on the left, and I click on

2:03that, you'll now see that we've got three

2:05options, publish data source, this is a

2:07capability

2:08that was introduced in previous versions.

2:10But now you've also got a database table.

2:13So we're going to go ahead and click on

2:15that and see what the options are. Now, the

2:17key

2:17thing here is when we do that, there's only

2:19certain databases that are currently

2:21supporters.

2:22And that's basically these ones, SQL Server

2:24, MySQL, Postgres, Amazon Redshift, Snow

2:26flake,

2:27Oracle and Teradata. Now, to be honest,

2:29these are the most commonly used relational

2:31databases.

2:32So it makes sense that these are some of

2:34the first databases for Tableau prep to be

2:36connecting

2:37to because they all generally use the same

2:39sort of setup in terms of how they work.

2:42They're

2:42all relational databases. So they just work

2:44in a very similar way. Yes, they have

2:46nuances,

2:47but they generally behave in the same way.

2:50And yesterday, you saw me set up a Postgres

2:53database. I'm going to go ahead and click

2:55on Postgres. Now, it's going to ask me for

2:57a few things. Now, my server is already

2:59running. I've got this open in another

3:00window. I'll

3:01show you that. So here's my Postgres

3:03database. You can see it's not really doing

3:05much. You

3:06can see that I've got a few bits of

3:07information here on the left hand side. And

3:09I have my

3:10Tableau prep table that I actually created

3:12yesterday in my demo. Let me head back to

3:15Tableau prep. Now, we're back in here. Now,

3:18the settings here are pretty simple. When

3:20you install this on your laptop, it's

3:22normally just localhost. Unless you call it

3:24something

3:24else or you set this up slightly

3:26differently, it helps if you can spell. So

3:28let's say localhost.

3:30The database I'm connecting to in my setup

3:33is actually just called Postgres. So let's

3:36just type that in. Username and password.

3:39Now, for this one, I'm sitting here and I'm

3:41thinking,

3:41can I remember my username and password? If

3:43not, you will never know because I'll edit

3:45it out. So here we go. There you go. Who

3:49knows whether I got it right? Maybe I got

3:51it wrong.

3:52Who knows? So here I am. I've connected to

3:54the database. There's localhost. And it's

3:57all working now. Now, the thing is, I need

3:59to enter a table name. All I've done is

4:00connect

4:01to it, but I haven't actually specified

4:03where I'd like the table to be saved to.

4:06And what

4:06you'll notice is as soon as I connect to it

4:08on the left hand side, you see that it

4:09actually

4:10gives me a readout of all the databases. So

4:12you can see here on the left, it's now read

4:14this list in. And at the bottom, you can

4:16see the same Tableau prep database that I

4:19showed

4:19you just a few seconds ago here on the left

4:21hand side. Now, we're not actually going to

4:23write to that. We're going to write to a

4:25different one to show you some of the

4:26capabilities that

4:27it's got. So if I click down on this

4:30selection here, it asks me to enter a table

4:33name. And

4:34again, this list here is exactly the same

4:36as this list over here. Now, in this case,

4:39I'm going to type in a new table name

4:41because I'm basically going to argue that I

4:42don't

4:43want to write to any of these tables. So

4:44let's just call this new_table. And when I

4:48do that,

4:49Tableau prep realizes that table doesn't

4:51exist and gives me this option here to

4:53create a

4:54new table. So let's go ahead and click on

4:56that. And then when we do that, now it's

4:58obviously

4:59understood, okay, this is a new table we're

5:01going to work with. I've frustratingly

5:03called

5:04it new table, which doesn't help with this

5:06video. But now we get these options here

5:08where

5:08we can see the three different ways you can

5:10write to a database. So create a table,

5:12append

5:13to table, and replace data. Okay. And these

5:16work exactly as described. So create a

5:18table

5:18means write a new table, basically always

5:21creates a table every time you run this.

5:24Append

5:25to table will always add data to the end of

5:27the table. And if you think back to 2020.2,

5:31we actually got some capabilities for

5:33incremental refreshes. And one of the

5:35options there is

5:36the ability to append data to an existing

5:39extract. And so that actually ties in

5:41really

5:42nicely with this feature. Check out my

5:44video on that as well. And then the last

5:45option

5:46is just to replace the data. This is

5:48basically like taking the same table, empt

5:50ying all that

5:51and then putting new data into it. Okay, so

5:53you can either create a table every time,

5:55add to a table, or replace the data in a

5:58table. So in this case, we're going to

6:00create a table.

6:02Okay. It will give me a warning saying,

6:04look, you're about to do some interesting

6:06things

6:06to your database, which is fine. Any

6:08existing table will be discarded, okay, if

6:11it finds

6:11this. So let's go ahead and create the

6:13table. And here we are, we're pretty much

6:15ready to

6:16go. So we've got the flow on the left hand

6:18side, and then it shows us how this is

6:20going

6:21to transition over to the database on the

6:23right hand side. Okay. And then when we hit

6:26run flow, the flow runs pretty quickly.

6:32Again, it would do because again, I'm

6:34running this

6:34on my laptop. In fact, my desktop, it's

6:37quite capable. And the data is not having

6:39to travel

6:40for, it's really only about 20 rows. So

6:43this is really not anything like a database

6:46you

6:47might experience in an enterprise setup.

6:49But there we go, we're done. What I'm going

6:51to

6:51do is I'm going to go switch over to Post

6:53gres to make sure that this is there. Okay,

6:55so

6:55just go over to the Postgres. And what I'm

6:57going to do is I'm just going to refresh

7:00this

7:00page so that we get some new information

7:03coming through. And then we basically go

7:06down to

7:06servers, Postgres. And if I go to public,

7:09if I look at tables, you'll see here that

7:12I've got my new table, and you've got all

7:15the columns that came from that particular

7:18prep flow. So that's pretty much the

7:20feature in a nutshell. It's always worth

7:23playing around

7:23with these capabilities just to see what's

7:26going on. If I now, I've created a table in

7:30this particular instance, now if I go ahead

7:32and append to the table, this is a little

7:34bit dangerous, because I'm going to

7:35basically write the same rows to the

7:37database. So this

7:38is like a sure fast way of creating a

7:40nightmare for yourself in a real database.

7:44And what

7:44you'd really want to do is put an ID to

7:46signal what time you're writing to the

7:49database so

7:50that you can go back and separate the state

7:52if you needed to. Think of it as like

7:54snapshots.

7:54Every time I write, I'm creating a snapshot

7:56. And if I give it a time, then I can

7:58actually

7:58signal which one of the snapshots I want to

8:01use. But if I don't give it any time or any

8:03detail, then I'm not going to know which

8:05rows came in at what time. So if I just go

8:08ahead

8:08here and run the flow, then I've just

8:10created a nightmare for myself. And it's

8:12actually

8:13unable to run this for me, because the

8:15details aren't sitting okay. So at least

8:18you get this

8:19sort of warning. At least you get a little

8:21bit of a heads up. If I go ahead and say

8:24write

8:24in second table here. Let's go ahead and

8:26click underscore. It's the second table.

8:29Create

8:29a new table. And then I go ahead and try

8:32and hit append to table. Again, I get

8:35another

8:35error because it hasn't found the table. So

8:38that's going to cause us another issue.

8:40However,

8:41if I go ahead, create the table again, this

8:44will succeed. Then if I go back and append

8:48to the table again, we're going to get that

8:51error. Because it's not able to actually

8:53figure

8:53out which rows came in at what time. You

8:56really need some sort of key to tell you

8:59what data

9:00is being written when. Now, unfortunately,

9:02at the time of recording of this video,

9:04although

9:04Tableau 2020.3 is out, the Tableau prep

9:08documentation has not been released. So I'm

9:10sort of flying

9:11blind here in terms of figuring out what

9:14the exact behavior is, or what you should

9:17expect

9:17to see, and what kind of errors you should

9:19expect to see because the documentation

9:21hasn't

9:21been released. But as and when that's

9:23released, check back on this video. I'll

9:24drop a link

9:25in the description and we'll be able to

9:27sort that out. Okay, that's pretty much it.

9:29Now

9:29that you're able to write to the database,

9:31I'd love to know what some of the things

9:32you're

9:32trying to do with your Tableau prep flows.

9:34To be honest, this has felt like the

9:36feature

9:37that a lot of people were waiting for. Up

9:39until now, Tableau prep has kind of been

9:40lacking

9:41that pizazz, that sort of punch. And

9:43publishing data sources to Tableau server

9:46was sort of

9:47like a middle ground. But in a real

9:49enterprise context, you normally want to

9:51put your data

9:52in a database because then it's available

9:54to other systems and other setups in your

9:57organizations that aren't necessarily

9:59running Tableau, and they can all use that.

10:01But now

10:02we've got databases, and we've now got the

10:04ability to write databases. This is

10:06hopefully

10:06going to be a great feature. Okay, thanks

10:08for watching. Thank you for being really

10:10patient

10:10through this slightly longer than usual

10:13video. If you've liked the video, let me

10:15know. If

10:15you don't like the video, let me know in

10:17the comments below. Let me know what we can

10:19improve,

10:20and we'll try and get to that soon.