Using the History Page to Learn Queries - Snowflake

0:00One of the best ways to learn is to be able

0:01to work alongside someone who knows what

0:03they're doing

0:04and can show you how to do something. With

0:06Snowflake, you can control Snowflake either

0:08using the interface or SQL commands, but

0:10with the SQL commands you really have to

0:12have some

0:12experience writing them before you can

0:14understand how they work and how to write

0:16them off the top

0:17of your head. Not many people actually do

0:18that, they use some assistive tools, but

0:20nonetheless

0:21there is actually a nice way that you can

0:23do this just by using the Snowflake

0:24interface,

0:25then looking at the query afterwards and

0:27then viewing the SQL that was written by

0:29Snowflake.

0:30Let me show you how, let's get stuck in.

0:32Okay I'm here in the Snowflake interface, I

0:34'm actually

0:34looking at the new version of the interface

0:36. If this looks very modern compared to what

0:38you're

0:38used to, you can actually switch back to

0:40the old version of the console just by

0:42hitting classic

0:42console. You have to log in again and once

0:44you've logged in again you go back to the

0:46old way of

0:47doing things and if you want to go back to

0:48the modern way of doing things then you can

0:50just go

0:51ahead and hit the snow side option just

0:52here on the top right hand side and it'll

0:54open another

0:55tab. So you can actually have them side by

0:57side, at least for now that's going to be

0:59the case,

0:59in the future that might disappear. But let

1:01me show you what I'm trying to explain. I'm

1:03still

1:04learning Snowflake and I'm still learning

1:06how to write the SQL commands to control

1:08the Snowflake

1:08system and in essence the way I'm sort of

1:10learning to do this is by using the

1:12interface to do things

1:13I would normally do, then seeing what query

1:16was generated to create that activity. Now

1:18this might

1:18not be familiar to lots of people but in

1:20essence everything you do here in the

1:22interface is actually

1:24going to create a query statement. You can

1:25actually look at it. So let's just do

1:27something

1:27very basic. Let's go to my data set here

1:29and you'll see that I have two databases. I

1:32'm just

1:32using the Snowflake trial so if you're

1:34using the trial as well you'll have exactly

1:36the same set of

1:37data. You'll see there's two databases,

1:38there's one called Snowflake and one called

1:40Snowflake

1:41sample data. I'm going to actually open

1:43Snowflake because that's the sort of

1:44central database that

1:46Snowflake used to show you information

1:47about your Snowflake instance so we can

1:49actually kind of look

1:50at the metadata that's sitting behind Snow

1:52flake in this particular area. I'll go in

1:54here to the

1:55information schema and we'll just take a

1:57look at some of the data that we've got

1:58here. We've got

1:59the databases, we've got things like

2:00columns. I'll scroll down to the tables of

2:02this. This is

2:02probably going to be the easiest one to

2:04look at and you'll see that you get this

2:05representation here.

2:06This is in fact just a preview that's been

2:09generated by the interface but if I click

2:11data

2:12preview what that actually does is it kicks

2:14off a query that's running off the compute

2:16warehouse.

2:16You can see it says here compute warehouse

2:18and you get a table. Now what that table

2:20did is it

2:21generated a SQL command so let's go and

2:23look at what that SQL command looks like.

2:25Let's go over

2:25here to activity on the left hand side and

2:27we look at the query history you'll see

2:29there's actually

2:30quite a few things that I've been running

2:31now. This is a brand new trial instance you

2:33can see that

2:34everything I've run has literally just been

2:36done in the last few minutes and so if I go

2:38to the very

2:38top query you can see that I actually get

2:41the query that was run and I get the

2:42specific SQL.

2:43It's been limited to 100 rows but in

2:45essence this is how it's querying from that

2:47particular table.

2:49So I can actually go and take that copy go

2:51to a worksheet. I have a worksheet here

2:54that's

2:54open from a few minutes ago. Let's go ahead

2:56and open that and you can see that I can

2:58actually

2:58just go ahead and paste it there. It

2:59actually had something already in there but

3:01this time I'm going

3:02to load something slightly different and I

3:04'm going to hit run just by highlighting

3:06this. I always get

3:07in the habit of highlighting then hitting

3:09run because what you don't want to do is

3:10make the

3:11mistake of basically running the entire SQL

3:12statement when you're only meant to run a

3:14small

3:15section of it. So I'm just learning to do

3:17this with most tools I'm still sort of

3:19fresh of this.

3:20If there's a better way of doing it let me

3:21know in the comments but as you can see I

3:23ran that query

3:24again ran in 1.2 seconds and you get the

3:26same table. So that's a very sort of simple

3:29way of

3:29doing this. Now okay let's go ahead and

3:31look at this in a slightly different

3:32context. Let's go

3:33back to the home page and what I'm going to

3:35do is I'm going to go to data and I'm going

3:36to go to the

3:37databases option. Now what I'm going to do

3:39is create a new database and we're going to

3:40have a

3:41look at the SQL that this creates. So let's

3:42go ahead and write the name here. So we'll

3:44call this

3:45tableau_tim_test. I can't do hyphens there

3:49so I have to do underscore test. Let's do

3:52that and

3:53we'll call this test_db and once we've done

3:56that we'll hit create. That will go ahead

3:59and create

3:59a database. Of course once the database has

4:01been created there's actually nothing in

4:03that database

4:04so you'll see we just go in there's nothing

4:06there but again we can go to the activity

4:08area look at

4:09the queries and you can see here's the SQL

4:11that was used to create that. Create

4:13database identify

4:14tableau_tim_test comment equals test_db.

4:17That's essentially what was done and so we

4:19can go ahead

4:20and delete the database and we're going to

4:22use the SQL statement to do the exact same

4:24thing again

4:24this time around. So let's go ahead back to

4:27the data area and we can go ahead go to

4:29tableau_tim_test

4:30and what we want to do is drop this. Now

4:32the new SnowSight interface is a little bit

4:34cleaner.

4:34It's sort of I find it a little bit harder

4:36to sort of get around. I prefer the old

4:38interface which

4:39was sort of familiar in some senses. So

4:41what you do is you go to the very top right

4:43hand side here

4:44you see these three dots which are never

4:46descriptive. Go ahead and select drop and

4:48this

4:48will basically go ahead and delete this

4:49database. So remove the database tableau_

4:51tim_test from the

4:52system. A version will be retained in

4:54timetable for a specified amount of time.

4:56Let's go ahead

4:57and drop that and you'll see that it

4:58disappears from this list. That in itself

5:01was a query so we

5:02can go ahead and look at that as well. So

5:03if I just go ahead and refresh this and

5:06just give it

5:06a bit of a second. Yes you'll see that the

5:09drop option came up there. So this was the

5:11SQL to drop

5:11it and this was the SQL to create it. So

5:13let's go ahead I'm going to grab this I'll

5:15rename it

5:16just to make it slightly different. Let's

5:18go to my worksheet go back into the same

5:20worksheet I was

5:20creating before and I'm just going to go

5:22ahead and paste that in there. I'm going to

5:25call this

5:25slightly different I'm going to call this

5:28tableau_tim_test_v2 it doesn't like spaces

5:31and

5:32I'll call this testdb a second time around.

5:35Okay and now that we've done that we're

5:37going to hit

5:38run just on the specific line remember

5:40highlight the line then hit run then it

5:42will go ahead and

5:43it will create the database tableau_tim_

5:45test_v2 successfully created. Let's go to

5:48our databases

5:49and check that that's worked go back go to

5:51data and there you have the test database.

5:54So if you're

5:55learning snowflake this is a really nice

5:57way to have sort of a soft landing into the

5:59technology

6:00but also understand the best way to write

6:02certain SQL statements to get them to work.

6:04Now there will be some instances where you

6:06just have to go and look at the

6:07documentation but I

6:08think this is a good place to start. You

6:10can almost go through during the trial

6:12creating lots of

6:13different sort of walkthroughs of something

6:15and then once you've got that down you can

6:17then look

6:18at your worksheet and look at okay this is

6:19everything that was done to create this

6:21setup

6:22and you can then use that save that in

6:23github or do whatever you need to to sort

6:25of perpetuate it

6:26for the future so you can learn from it. If

6:28you've enjoyed this video let me know in

6:30the comments

6:30below snowflake is slightly different to

6:31what I normally record but hey I'm learning

6:33snowflake so

6:34I thought I'd bring you along on the

6:35journey thanks for watching and I'll catch

6:36you in the next one.

6:38you

6:39[ Silence ]