0:00Hey, it's Tim here. And in today's video, I
0:01'm going to be doing something slightly
0:03different.
0:03One of the most common bits of feedback I
0:05've been receiving in my comments is that
0:07most of my demos
0:08are done using Excel. And essentially, I'm
0:11connecting Tableau or Tableau prep directly
0:14to
0:14Excel files. And that doesn't simulate the
0:16experiences that most people have in their
0:18businesses. So what I'm doing in this video
0:20is I'm actually going to show you the setup
0:23for a
0:23Postgres database that I'm going to be
0:25using in the videos in the future. Instead
0:28of just setting
0:28it up, I thought I'd make a video out of it
0:30and show you how you can do the same thing.
0:32I'm also
0:32going to be referencing this video in
0:34future videos if you want to set up a
0:36database for
0:36yourself so you can follow along. Okay, let
0:39's get stuck in. So the first question we
0:41need to answer
0:42is why Postgres? Well, there's not really
0:44that much logic to it. It's just something
0:47that I've,
0:47I guess I'm more common with and I see more
0:49often in terms of day-to-day usage. If I
0:52head over to
0:53the Amazon website here, they have a page
0:55on the relational databases that they have
0:58available.
0:59And they have a list here. This is pretty
1:01much a universal list of the key databases
1:03that are used
1:04in enterprise set-ups. Obviously, they have
1:06their own Amazon Aurora, which is kind of
1:08like a hybrid
1:09relational database in some senses. But
1:12there's obviously Postgres, MySQL, MariaDB,
1:15MariaDB, Oracle and SQL Server. Now, one of
1:19the reasons I'm gravitating toward Postgres
1:22is simply
1:23because it's open source. MySQL is also
1:25open source. And I'm sure the others have
1:27developer
1:27versions so you don't actually need to pay
1:29to have them running on your desktop. But
1:32the key thing is
1:33Postgres just comes up again and again in
1:35lots of enterprise instances that I come
1:38across. So
1:39I just thought let's set something up that
1:40's as close to something that's actually
1:42going to be
1:42used in real life. And I also know in terms
1:44of Tableau, it's got really good spatial
1:46support.
1:46So you can install a spatial plugin so you
1:49can have a database with spatial objects in
1:52there
1:52as well. So we're going to go through the
1:54set-up of installing all of this. Slightly
1:56longer video
1:57than normal, but let's get stuck in. Okay,
1:59so the first thing we need to do is to go
2:01to the
2:01Postgres website, postgressql.org. And
2:04essentially, I don't normally click on the
2:07download button on
2:08the homepage because this typically sends
2:10you to the enterprise solution, which is
2:11not what you want
2:12to do. You want to get the free version. So
2:15let's head on to the download page here at
2:17the top.
2:18And you obviously want to pick the right
2:19thing for your platform. Now, I'm doing
2:20this video for
2:21Windows as per the title of the video. I
2:23will maybe do a video for the Mac as well.
2:25So if you
2:26want to see that in the future, drop a
2:28comment down below. And I'm going to hit
2:30Windows as an
2:31option there. Now, it's important to know
2:33which version you're installing. Now,
2:35because I'm just
2:36going to be using Tableau or Tableau Prep
2:38on my laptop or on my desktop, it doesn't
2:41actually
2:41really matter. I don't have to worry too
2:43much about compatibility unless I start
2:45trying to
2:46connect to it from elsewhere. So I'm just
2:48going to go ahead and install version 12.
2:50So if I hit
2:51download the installer, it takes me to this
2:54page, which is hosted on enterprisedb.com.
2:57And you just
2:57go ahead and install the latest version,
3:00which in this case is 12.4. Go ahead,
3:02download that. That
3:03will obviously kick off the download. In
3:05this case, I'm going to save it to my
3:07desktop. And
3:08you'll actually see I've already got this
3:10file on my desktop here. So you'll see this
3:11. I've got this
3:12unconfirmed download over here because I've
3:14already got the file here on the top left
3:17of my window.
3:19Now, once you have the file on your desktop
3:23, it's time to run the install. And it's
3:25important to
3:25bear in mind a couple of things. When you
3:27set this up, you're going to need a few
3:28bits of information
3:29to have. First of all, your Windows
3:31username and password. We're going to need
3:33this a little later
3:34on to set things up. And secondly, it's
3:36good to have your password manager open
3:38because you're
3:39going to need to write down certain
3:40passwords because you don't want to forget
3:42these. Because
3:43honestly, the quickest way to reset these
3:46is just to install it again. So remember to
3:49note
3:49these things down as we go through the
3:51install so you know what they are in the
3:52future. Let's go
3:53ahead and double click the installer. I'm
3:55going to delete this one. You can see it's
3:56a duplicate.
3:57So let's just go ahead and do that. Let's
3:59double click the installer and minimize the
4:01browser window.
4:02So you can see here it's opened up the
4:09installer. I'm just going to go ahead and
4:11click next.
4:13And you obviously get a little sort of
4:15interface to ask you what components to
4:17install. They're
4:17all ticked. So go ahead and click next.
4:20Click next and it will actually kick off
4:23the install.
4:24It'll give you a list of what it's about to
4:26install. And when that's done, you can
4:29basically
4:30just let that run. Please ensure PG admin
4:33server is shut down before proceeding. I
4:36actually have
4:37PG admin running somewhere. I think it is
4:39shut down. I'm pretty sure it's shut down.
4:41So there
4:42we go. So I had this installed earlier on
4:45and I uninstalled it just for this video.
4:48And then
4:48I'm installing it again to make sure
4:50everything works. So let's just let this
4:52finish.
4:52Okay, I'm back and we're nearly at the end
4:57of the install. I'm just going to let this
5:00last step finish and then we're pretty much
5:03ready to go ahead and start trying to
5:05figure out how to
5:06set this up. Okay, so we're pretty much
5:10done. If I look at the instructions here, "
5:14Completing the
5:15PostgreSQL setup wizard." Setup has
5:17finished installing the PostgreSQL on your
5:19computer.
5:20Launch Stack Builder at exit. So this is
5:23basically the next step. The Stack Builder
5:26essentially
5:26allows you to build your Postgres
5:27environment. So we're going to go ahead and
5:29do that. Let's
5:30hit finish. And you can see Stack Builder
5:32has launched almost straight away. Now the
5:35key thing
5:35here is obviously we're going to choose a
5:37database type. And I previously had Post
5:39gres 9.4 installed
5:40on this and I've also had Postgres 12
5:43previously installed. So it's actually
5:45picked that up
5:46directly here. So I'm going to go ahead and
5:47select Postgres 12 in this particular case.
5:50Then I'm going to hit next. Now if you want
5:53to install any plugins, you can go ahead
5:56and select
5:56them from this list. There's various
5:58categories and if I expand one of these,
6:00you'll see these
6:01are some sort of add-ons that can be added
6:03on. Now the only one I'm actually
6:04interested in is
6:05a spatial one because Tableau has some
6:07spatial objects capabilities. So I want to
6:10be able to
6:11connect to a database and see that
6:13functionality. So I'll install that. In
6:15terms of everything else,
6:17you don't really need it. I'm not going to
6:19be doing anything else. And you can always
6:20come back here
6:21and install these in the future. You don't
6:23have to do this when you launch. It's
6:25obviously easier
6:25when you do it here, but you can do this
6:28later. So just go ahead and hit next. And
6:30then it obviously
6:31asks where to download these plugins
6:33because these plugins are not part of Post
6:35gres. They're sort of
6:36an add-on to Postgres. So go ahead, click
6:39next, click next. And you basically start
6:42the installation
6:42process for each of these bundles. I've
6:44only got one here. I'm going to ask it to
6:46create a spatial
6:47database. So I'll have something there
6:49rather than having to create something
6:51myself. Just allows you
6:52to make sure that everything's there. And
6:54then we can actually use this in Tableau to
6:55check that it's
6:56installed properly. So go ahead and hit
6:58next. It's going to ask for the destination
7:01folder for this.
7:02And then it's going to ask for the password
7:05. Now, in my case, I actually had a master
7:07password
7:08before. Now, when you install Postgres for
7:10the very first time in your machine, it
7:11will ask you
7:12for a master password very early on. If you
7:14set that up, this is the same password that
7:16you
7:16basically need to use. So this is mine.
7:20Okay, I'm going to hit next. And then
7:24obviously it's called
7:26Postgres sample. I'm just going to call
7:28this spatial_sample. So it's just a little
7:34bit easier
7:35and nicer to read. I'll use a lowercase s
7:38and hit install. Now we'll let that run.
7:43Okay, would you like us to register GDL
7:51data environment variable? If you needed
7:54for raster
7:54transformation to work properly, this will
7:56overwrite existing settings if you have
7:58them.
7:59Yes, these are obviously bundles that they
8:01're suggesting. This one, post_gis_enabled_
8:06drivers
8:07to common drivers. These look like image
8:09drivers. I'm just going to go ahead and
8:11click yes,
8:12enable out db rasters environment needs to
8:15be set to one. Okay, yes. It's always worth
8:18reading
8:19though. Sometimes they sneak in an option
8:21to install like a bit of software into
8:23these things.
8:23So always read those things, make sure they
8:25're not sneaking something in. Go ahead and
8:28hit close,
8:28and that's it. We've pretty much finished
8:30the installation and we're now ready to
8:32basically
8:32set it up. If I go to my start menu here
8:35and I go to the Postgres option, which is
8:38Postgres 12 here,
8:40and hit pg_admin4, this will open up the
8:43Postgres management tools. It's important
8:46to go here just
8:46to make sure everything's running and
8:48everything's working. Okay, so let me enter
8:50my master password.
8:51Click okay. Then yeah, that's pretty much
8:56it. So if I just expand this, never save
9:02that,
9:03Postgres 12, and it looks like everything's
9:06running great. If I go to schemas,
9:09you'll see that I actually potentially have
9:11a table here called Tableau Prep, which I
9:14created before.
9:15So it's kept my data and my information
9:17from the previous installation, but now you
9:19can see
9:20this is there, it's running, it's ready to
9:22work. So if I open up Tableau,
9:24I'll go ahead and open Tableau Prep at the
9:28same time so you can see that as well.
9:31And then here if we just hit Postgres, you
9:33can see that it's got the sample that I
9:36previously
9:36connected to. I just need to enter my
9:39master password. Actually, I didn't call
9:42this Postgres
9:4330, I called it something else. So let's
9:46just double check that here. Let's see what
9:49the
9:49database should be called. Postgres sample,
9:52this is called spatial sample. So the old
9:54one is called
9:55that, but this one is called spatial sample
9:57. So let's go ahead and connect to that.
10:04The master password for your database. You
10:08can of course create alternate usernames
10:10and passwords,
10:11but I'll just go ahead and create the
10:13default one that I created when I set it up
10:15as the admin.
10:16And here we have it. We have Postgres
10:18running on our machine and we're ready to
10:20go. We can connect
10:21to these fields. We can do what we
10:23typically do in Tableau, drag and drop, and
10:26we have these
10:27wonderful capabilities available to us. And
10:30the cool thing here is now that we have
10:32this,
10:32we can go ahead and actually start also
10:35investigating how databases work in the
10:38future.
10:38So whilst I'm doing all these things in the
10:41background, you can see that my Postgres
10:43database
10:44instance is obviously started to do things
10:46like transactions and it's loading and sort
10:48of logging
10:49what's going on. So I can obviously
10:51interrogate the SQL being run behind these.
10:53I'm not a Postgres
10:54expert, so I don't know where that is. That
10:56's going to be a learning journey for me as
10:57well.
10:58But that's pretty much it. That's pretty
11:00much it in a nutshell. We're just going to
11:01check the
11:02Tableau prep can connect to it as well. Let
11:04me just close this open up a previous
11:06workflow that
11:06I actually adapted already. And what we're
11:10going to do is connect to a database table,
11:12select Postgres. And this one, obviously,
11:15we're going to need to give it the database
11:17,
11:17so local host. And this one, the database
11:22is called spatial_sample. And the username
11:27and password is, in this case, is postgres
11:32and my master password.
11:34And go ahead, hit sign in. This might be
11:36the wrong password. Yes, I thought so,
11:38because I'm
11:38missing an exclamation mark. Hit sign in
11:41and we're in. And so here we have the
11:43database and we can,
11:45of course, connect to the spatial_sample,
11:48create another one, just call it test. And
11:51we're just
11:52going to make sure that it's there, create
11:53a new table. We don't want it to append. We
11:56want it to
11:56replace the data every single time. Hit
11:58replace. I'll do another video to go
11:59through this in more
12:00detail. This is a new feature in 2020.3 for
12:03Tableau prep. I'm just going to hit run
12:05flow.
12:06And we should now have that database in our
12:11table. So let's go ahead to spatial_samples
12:14,
12:14go to the schemas, go down to, oh, not sche
12:17mas, actually. Is it the schemas? Yeah, it
12:20should be
12:20correct. Oh, why am I not seeing what I'm
12:23expecting here? Why have I gone wrong here?
12:25So
12:26hold on. Postgres, schemas, yeah, public.
12:29Okay, so this is where I should go. I am in
12:31the right place.
12:32Schemas, tables, and there we are. There we
12:36have the table that we had in Tableau prep.
12:41And you
12:41can see the fields there are available to
12:43us. And so we can actually go ahead, go
12:46full circle,
12:48go ahead, go back, go in here, just search,
12:53test. See, if I hit search, this might
12:56refresh this
12:57connection. No. Okay, so let's click edit
13:00connection, click sign in. I'm hoping that
13:04refreshes this list. There we go. And then
13:06hopefully I can connect to that new table
13:08again.
13:08So I'll go ahead and remove this just by
13:11going out here, remove that.
13:16And then once that's gone, we can drag in
13:18the test table we just published in Tableau
13:20prep,
13:20just to test everything's working. And it's
13:23not going to be like lightning fast like a
13:26normal database, but we should have some
13:28information available to us. It says just
13:30put sales region,
13:31and then we can just put sales. And there
13:34we have it. We have a working database on
13:37our machine.
13:37We've created it, we've tested it in Table
13:40au, we've created some data in Tableau prep,
13:43we've published that to the database, then
13:45we've connected to it again in Tableau.
13:47So that's pretty much it. We did that. The
13:49recording here says 15 or so minutes. So
13:52hopefully that's been a useful video.
13:54Follow along. If you've got any questions
13:56at all,
13:57drop them in the comments below. And we'll
13:58try and address this in the future. In the
14:01next video,
14:02I'm going to be showing you the new feature
14:03in 2020.3 for Tableau prep where you can
14:06write to
14:06databases. So stay tuned for that. And
14:09hopefully if you're using this video for
14:10one of my other
14:11tutorials, hopefully you find this helpful
14:13for learning a little bit of SQL and how
14:15databases
14:16work. Okay, if you've enjoyed this video,
14:18you know what to do. If you haven't, drop a
14:20comment below,
14:20let me know what you'd like to see in the
14:22future. I'll catch you in the next one.
Tableau | Data engineering | Data prep