- 2 days ago
Category
🤖
TechTranscript
00:00Hey friends, welcome back.
00:16In the last video we have seen that we can use an external tool, for instance SQL Server
00:20Management Studio or Azure Data Studio or any other kind of tool to connect to the SQL
00:25endpoint of our Lakehouse and read data.
00:27Remember, it is a read-only endpoint, meaning we cannot write data back.
00:31But what if you say, I don't want to use an external tool, I want to use SQL for my tables
00:36directly in Fabric.
00:38We can also do this simply by going back to our workspace first, so the Fabric trial in
00:43my case.
00:44And then we see that our Lakehouse, remember this, always comes with two additional things,
00:50which is the semantic model, which will be relevant for Power BI reports, so make this
00:54bigger, the default model here, and also the SQL Analytics endpoint.
00:57And this endpoint we can use in order to create data using SQL directly in Fabric.
01:03So if you're a SQL expert or seasoned SQL user, then of course now we can try this out yourself.
01:08So let's do that.
01:09Let's actually open the SQL endpoint, click on Fabric Lakehouse here, and then this is the
01:14view we should see.
01:15It might be also, in this case, closed here, but of course, if you go there in the schema
01:20section, then you find here the schemas.
01:22In this case, I only have the default schema, which is DBO, but if we would have create several
01:26schemas, we would should find them here as well.
01:29Now inside the schema, the current schema here are our tables, as you can see here.
01:33This is the table section, and we can see our three tables in here.
01:37Now in order to write now a SQL query, we can either go on the top here, new SQL query,
01:42you can go there, either this one or this one, or also a SQL query in a notebook, that's
01:48also possible.
01:49But for now, let's do it quite easy by simply going to one of the tables, in this case, this
01:54one here, let me make this bigger, PBI training, and I can take up the three dots, and then
01:59it works exactly, normally as in SQL Server Management Studio, they can also simply generate
02:03the SQL query like this.
02:04So select top 100, and if I tick this option, then this SQL query, it gets executed just in
02:10a moment, and then we should see actually the results down there, as you can see.
02:14So this is the content of the table, and you can see that this is exactly what we get from
02:19the SQL query, which you can also modify if we want to do that.
02:22So hopefully you can see that inside the SQL endpoint, you can also write your SQL queries
02:27directly here in Fabric.
02:28You don't need an external tool if you don't want to, but you have both options for availability.
02:33So next to the query, of course, we could modify that now.
02:36You can also create, you probably see that already, views here, for instance.
02:40So if you want to, for instance, only have certain kinds of, well, subset of your table,
02:44right, you could oftentimes, we use views for this, and this could be one of these options.
02:49So if, for instance, we say, let's actually move this for now, and say, this is, let's
02:54create a view.
02:55If I say, for instance, create view, and you can see there also some kind of intelligence
03:00here, so you can press a tab key here, and then give the view a name.
03:04So let's say, my view, as an example.
03:06So, and then as, right, and then we can actually create any kind of select statement, for instance.
03:12So you select, and let's say star from, in this case, one more time, our PBI training,
03:17PBI training file, and then simply use a where clause here and say, for instance, for the
03:21ship mode is DHL.
03:23So where ship mode, so ship mode, let's select this here, is equal to, and then we specify
03:29DHL, so DHL, like this, and then that's basically our view, right?
03:34Of course, it could be more complex, but the main idea is to show you that you can create
03:38a view, so a subset of your data.
03:40You can also join data together in the view, right?
03:42You can do more complex SQL if you want to, but let's actually execute this for now, select
03:47it, let's click on run, and you see that the view or the statement has been executed,
03:52and now if we take a look at the views we have, you can see there is my view, right?
03:56And you can also query it, just to take a look at this, so here, no SQL query, right?
04:00You can go there, and let's just execute this for now, click on 100, and you can see that
04:05now we only have ship mode DHL in this, right?
04:07So that's exactly how we specify the view, and of course, we can also order the view, we
04:12can drop the view if you want to do that, that's also possible, right?
04:15So simply by typing in here, drop view, so drop, drop view, and then the view name, in
04:23this case, it's my view, right, like this, and now if we would execute this, we would
04:28run this, click on run, right, you can see it's executed, and now the view is down here,
04:33right?
04:34No view anymore.
04:35We can also create functions in SQL, and we create store procedures, so if you, as I
04:39said, are more into SQL, you can do all the things in here as well, which you probably
04:44are familiar with, if you have used SQL Server Management Studio, any kind of tool before.
04:49The only thing which you need to keep in mind always when you deal with the lake house is
04:53that the lake house is a read-only endpoint.
04:55That's something you need to keep in mind, right?
04:57But beside this, you have all the options here, and you can also see that the SQL queries
05:01here, these ones here are also stored under queries, and of course, you can also, if you
05:05want to rename them, you can delete them if you don't need them anymore, or move to shared
05:09queries.
05:10So you can also store your queries just for later use if you want to do that.
05:14Now, what if you don't want to write SQL code, or maybe you don't know how to write
05:18SQL code?
05:19Either way, there's also an option, which allows us to simply drag and drop and create
05:23the SQL code using Microsoft Fabric, so without us writing the SQL.
05:28How to do this?
05:29Well, for that, we need to go to a new SQL query up here, and then there's an option,
05:34a new visual query.
05:35That's the second one.
05:36So let's click this option in here, and then you find a similar experience to the data
05:42flow chain 2.
05:43So 2PowerQuery.
05:44And the way it works is we need to build a query by dragging our tables here.
05:48And what I found is the easiest way to get data in here is not dragging the table or try
05:53to drag it, but instead click on the three dots and then simply insert into Canvas.
05:58So if you take this option, then the data gets dragged in here, as you can see.
06:02And now I have here my PPI training, the source, the database, and the table.
06:06And down there, there's my preview of the data in here.
06:10And by the way, this preview, as you can see here, can also be saved as another table.
06:14So after transformation logic, I can store it as a new table in here.
06:18So if I go to save as table, all I would need to do is defining the schema, the table name,
06:23and then I can save it.
06:24That will be one option to also save the transformed data in the new table.
06:29The second option, also kind of interesting, is download Excel file.
06:32The reason I mention this is still in today's time, a lot of people want to download the
06:36data.
06:37They want to have it in Excel, and this is also possible.
06:39Now, but for the SQL transformations, as I said, let's say I don't know how to write
06:44SQL, or I don't want to write SQL code.
06:46Now of course, I could do the transformation directly in here.
06:49I can click on a plus symbol here, like this, and then I can say I'd like to choose specific
06:53columns, for instance.
06:55So I can take this option and say, I don't want to see, for instance, the order ID, customer
06:59ID, product and employee ID.
07:01So all the ID columns I don't want to see, for instance, also supply ID, get rid of it.
07:05So let's click OK.
07:07And watch what happens in the background.
07:08Currently, we still have order ID, customer ID, product and employee ID.
07:12But if I now click OK, now the data gets updated, and you can see that now it starts with the
07:16standard price.
07:17And if you want to see the data, what it looked before this step, simply click on the prior
07:21step, and then you'll see that there the columns are still included.
07:25And after that, choosing columns, they are gone now.
07:29And this, of course, can be now continued.
07:31So if I click on the plus symbol here, I can say next, I would like to, for instance, keep
07:34the top rows.
07:35By the way, I could also, of course, use the ribbon here.
07:38I can also say here reduce and then keep top rows.
07:41Select this option and say, I would like to only keep the 13 top rows.
07:45Click OK.
07:46And now you see that kept up row step.
07:48And I can see that now, just in a second, there are only the first 13 entries in here, right?
07:54So this can also be done either by the ribbon menu or by clicking the plus symbol that also
07:58works.
07:59Now, next to this, there's also, of course, an option to union data and join data.
08:05And if you have not much experience with data flow gen 2, you probably don't know the terms
08:09because the terms are a little bit different.
08:11But union is appending data and joining is merging data.
08:15Meaning, as an example, if I would say I'd like to join or merge the data from diabetes
08:21into the PPI training, I could simply click on the three dots, click on insert into canvas.
08:27So now I have a second table here.
08:29And now I could, if I want to do this, click on a plus symbol here.
08:32Let's say in this case, I would like to join the data, the two tables.
08:36So I could say, I'd like to merge them.
08:38So merge queries is the terminology here.
08:41And then I could specify, okay, my diabetes table is on the left side.
08:45On the right side of the tables is the PPI training.
08:48And then I just need to specify, okay, for instance, the age here with this column here, right?
08:52Doesn't make any sense.
08:53I just want to show you how it practically would work.
08:56So this is then the connection between those.
08:58You can also select several columns if you want to do that.
09:01So if you join a multiple columns, that will also work.
09:03And then you specify simply join type in here, right?
09:05Right order, full order, inner, left, untie, and right.
09:08These exist.
09:10So let's cancel this for now because this would not make any sense.
09:12It was just an example.
09:15So let me just remove this, click on the three dots, and then I would like to delete it.
09:19By the way, as I said, appending queries, this would mean with union tables.
09:24So this is appending queries.
09:26So let's just delete this for now.
09:28Yes, I'd like to delete it.
09:29And let's take a look back at our original data files.
09:32So this one, we have only made two transformations.
09:35Of course, in a real business project, this could involve several SQL and more advanced
09:40SQL transformations, including, as I said, joining as well as union of data.
09:45Now, if you are interested in the SQL, which is generated, you can now go inside the View
09:50SQL, this option here.
09:52And then you see that this is basically the SQL code, which gets created.
09:55So in our case, just select the top 13.
09:58And then, of course, we just select the columns we want, because I have chosen specific columns.
10:02The ID columns, I filled it out, right?
10:03So that's why they are not chosen here.
10:05And this statement can either be copied to clipboard.
10:08So if you want to save it for later usage or share with a colleague, you can do that.
10:12And you can also edit the SQL script directly in here if you want to do that as well.
10:16So it's also possible.
10:17So hopefully you've seen that this visual query, it is kind of similar to the dataflowgen2 experience
10:24and made probably most for people who either don't want to write SQL or maybe also those
10:30who can't write SQL.
10:31But there's one distinct difference between this visual editor and the dataflowgen2, which
10:38I like to highlight here.
10:39The dataflowgen2 has much more transformation options than these, which are rated in here.
10:46And the reason for this is kind of simple, because the dataflowgen2 engine is not based
10:51on SQL, meaning we can do transformations in there, which are not supported by SQL.
10:56In here, in the visual query editor inside the SQL analytics endpoint, so from the lake house,
11:02this is based on SQL.
11:04So that means we can only do transformations, which also exist in SQL.
11:08That's why we have less transmission than here.
11:10So if a transformation is not supported in SQL, you won't find it up here.
11:14So that's the main difference between the dataflowgen2 and here, this visual query editor.
11:19So even if they look similar, they are different because there are more transformations inside
11:23the dataflowgen2.
11:24Just want to mention it.
11:25Okay.
11:26So that is it for this video.
11:28The last option, I'd like to just briefly mention it, it's kind of quite new, is new SQL
11:34query in notebook, but this actually is only the same as we have already seen, because
11:40basically what we're doing here, we create a Jupyter notebook, and we will do this in
11:44the course later on as well.
11:45But then we just write normal SQL code.
11:48So the difference here is not really given between the new SQL query in here and create
11:53a notebook and write your SQL query in there.
11:55That's why I will not cover it in detail.
11:57But if you've got questions, of course, feel free to let me know.
12:00So that's it for this video.
12:02If you've got questions, let me know.
12:04Otherwise, thanks for watching, and I'll see you in the next video.
12:06Until then, best guys.
12:07Thanks, guys.
Recommended
1:16:45
|
Up next
0:33
13:32
12:23
0:36
24:07
0:46
Be the first to comment