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.
Tableau | Data prep | Data engineering