 Alright, so this video is just going to be a little recap. I got a comment, actually a couple comments, on a recent video about how this command I showed works. And I thought I could just take it piece by piece in this video, and you guys can let me know if this is helpful. So I, I think I'll work backwards. So the last command in here is calling tidyViewer. Let me see if I've got a CSV. Alright, I do have a CSV. So with tidyViewer, what you can do, well there's a couple ways we can do this. We can either cat a CSV file, so like Star Wars, for example. Normally if you cat, you just kind of get everything dumped onto the screen. I'm going to decrease the font a little. There we go. So that's what you get with cat. It's, it's a little bit messy, so tidyViewer is a way to just kind of look at the data without having to wade through all of that mess. So I could cat Star Wars to, to tidyViewer. Usually more, usually the syntax is more to use tidyViewer and then just say the CSV file and you get some output. Now this output normally is all right with my standard font. But there is a little bit of overflow just because my font is huge and the default in tidyViewer is to show 25 columns. I can change that. I can say, hey, tidyViewer, the number of columns, sorry, the number of rows I want you to print is 10. And I get something a little bit nicer. Again, it mostly does handle the overflow well, but the font is a little bit big. I'm going to go just a little bit smaller just so we get something a little nicer. Still going to look squished though, but this is the idea. So tidyViewer is just taking our CSV. I'm making it look nice. So as long as there's a CSV, tidyViewer can make it look nice. So I'm going to try also with cat really quick. If I remember the syntax here, cat to tidyViewer. And again, we'll limit to, I'll just do limit to five. That's what it looks like. So that's the tidyViewer part. And now going backwards, I am using the duck db command. And you can't really see what's happening with duck db. If I just type duck db, it'll put me into this run line mode. And I can run different commands in duck db. But this isn't really being utilized so much. What's happening is that duck db is being pushed a SQL file. So as long as you have a SQL file that duck db can understand, it will use it, and it'll interact with it. So let me think of something I can do here. So let me take New York flights. Let's do, well, let's see, let's actually go to duck db mode. Duck db and then select star from read CSV auto. And we're going to read nycflights.csv. And we're also going to say that header equals true. I think it's with a capital T. Sometimes I can't remember this in text too well. Oh, and semicolon, boom. All right, so that is reading all of the flights data. That is a little too much, so I'm just gonna cancel that. Let's do, let me decrease the font again. I'm sorry if this is getting hard to read, but I want to be able to fit lots of this command on one line. So select star from read CSV auto header true, and let's just do like a limit 10. Still a lot of data, kind of a mess. And that's why we push it to, to tidy viewer is that this is a CSV, it's a little bit hard to read, but we are able to run SQL. Maybe if I knew a column name, I can select a column name just to make it a little bit cleaner. Let me exit out of this again, and see I'm gonna pass the flights data to tidy viewer. All right, so maybe I'll just select this depth time, departure time, just to make it clear what's going on. So instead of select star, select depth time. Boom, there we go. So just selecting one of the columns from New York flights and limiting it to just a few observations here. Let's see, maybe I'll just, for fun, select year month, day, depth time, just to drive the point home some more. Year, month, day, there we go, that's a CSV. So like I was saying, we aren't actually pushing, like we aren't entering this mode inductee B. We're pushing a SQL file. So I'm gonna, can I copy this easily? I don't have a great way to copy this. Gonna get out of DuckDB mode and write a little NYC flights. Let's call this Duck, NYC flights. So this SQL is going to be valid DuckDB SQL, and there are lots of different SQL dialects. So for example, I don't, to my knowledge, I don't think SQLite comes with a read CSV auto command. You'd have to use an additional module, but DuckDB comes with that included already. All right, so I'm gonna go into Envim and start making this Duck NYC flights SQL, a lot like what we were just doing on the command line. So select star, well, let's do select year, month, day, depth time, whoop, depth time from, gonna make this look a little bit nicer, from let's do a read CSV auto here, NYC flights.csv, and there is a header. And limit 10, you guys get to see all my typos and make sure that semicolon's in there. So let's cat that file. And that's valid, I think that's valid, DuckDB SQL. All right, so how do we push this to DuckDB? Can we just do cat DuckDB flights and then use the DuckDB executable? Yes, that works. So we can push SQL to DuckDB and it will execute that SQL file. And then we can pipe that to TidyViewer and get a nicely formatted file. Now, it's not making a big difference here just because there isn't that many columns we don't have to worry about overflow logic and the columns are simple integers except the last column which is a float, but it's still nice. It's nice to be able to push things to TidyViewer in the end. So we can take a SQL file, push it to DuckDB and that SQL file reads our CSV and is able to do any kind of SQL that DuckDB allows. Now what I was showing at the video wasn't just stopping there though. PRQL is a completely different syntax from DuckDB and that's what's kind of cool. I'm gonna look up PRQL right now. So in PRQL there's kind of this more terse syntax but I really like it. I think this is a really clean grammar. Lots of SQL statements will end with a from statement or the from statement is in the middle somewhere. But usually when you're working with data manipulation libraries in R or Python you are reading first and then doing your manipulations. So it's almost natural. It feels natural to want to read or grab the data first. So that's one thing that you see in PRQL is that you're getting the data first and then you're doing different things with it. So filter is like where in SQL. And if you're used to tidyverse this is literally like filter in the tidyverse. So some Boolean expression is given to filter. Derive is to make new columns. So in the tidyverse this is like mutate but you're just making new column names and providing some kind of a logic. Here they're running filter again and then there's a grouping and within that grouping what's happening in this query is some aggregation. So grouping by customer ID and then calculating the average total the sum of the income and count. And then sort can take a list of different columns to sort and then I think the minus puts in descending order take is like limit and then you can continue to do join. So anyways PRQL is a really nice language. The thing is that PRQL isn't doing any SQL. You type text and then it spits out SQL. It's not actually performing the operations in the backend. So what you do is you write something in PRQL and you can spit out some valid SQL and some other language. And one of the ports that you can use is DuckDB. And that's what's really cool. So DuckDB is really nice because it's very portable. It's an embedded database. You can use standard SQL syntax if you want which I do think is really good. They've got excellent handling of strings and dates. They come, DuckDB comes with a lot of good functions. Some nice things about PRQL are stated here on this homepage. It's concise, it's database agnostic. So you can take the same PRQL and as long as there's a supported backend for it that PRQL can be translated to other SQL syntaxes. The thing that I personally like though is just the grammar. It feels really close to dplyr and I just like the grammar of dplyr. I think it's a good way to go. And I also like that it lets me use my favorite database tool, DuckDB. So back to the video, I was writing PRQL and then saying, hey PRQL, I want you to compile into SQL and send that SQL to DuckDB. So let's take a look at what that PRQL file looked like again. This was, let's see nvmprql query. All right, so this is all PRQL. If I were to send this as is to DuckDB, it would fail. In fact, I'll go ahead and do that right now. So this is going to be cat PRQL query and then push it to DuckDB. It doesn't work, it doesn't know what this syntax is. So what I do is I have PRQL compile it. So before it hits DuckDB, I do PRQL compile, so this is a command line utility that you've got to install PRQL C and then the command compile and then it spits out a new SQL. So if you look here, this is not at all what we wrote in PRQL syntax, but this is something hopefully that DuckDB can understand. In the bottom here, there's a commented out statement that says what version of PRQL we're using. And so since it's commented out, DuckDB would ignore it. So let's try this, let's do cat PRQL and cat that to DuckDB. And we got something. And then we can cap that to tidy viewer and we have a pretty printed output. Now I have something else in here, which is enter ENTR. ENTR is a command line utility that will automatically run once it sees a file changes. So what you can do is you can echo a file, any kind of file to enter and it will start watching that file. And if you save it and the change has been made, it will rerun whatever you're telling it to run. So what I'm doing is I'm saying enter. I want you to look at PRQL SQL because that's what I'm gonna be editing. When I make a change and save it, what you're going to do is you're going to take that file, push it to be compiled by PRQL and then push that to DuckDB, which will actually run the statements and then push that to tidy viewer which will make a nice display. So in that way, without having to do, without having to use a fancy database manager or a lot of infrastructure, you're able to do lo-fi data manipulation in a very terse syntax. So I'm gonna break this down one more time, kind of in slow motion, but I'm gonna put all the pieces together as I'm typing this. So echo PRQL query. So all this is doing is echoing the file, not the contents of it, just the file, echo this file. So we're gonna push that to enter so enter knows to watch that file. So enter shell, this is a shell mode that we're putting it into. So echo PRQL SQL, enter shell with a C flag. And now what we're putting in quotes is everything that was just explained. We're gonna say PRQL compile, PRQL query dot SQL, and then pipe that to DuckDB, then pipe that to tidy viewer. And there's no tidy viewer. And there's no need to do the dash end command here just because all we're returning is a one row output. And there it goes. So let me think, what do I wanna do here? I'll go ahead and start making edits here. So let's end Vim into PRQL query. Let's say I just wanna call this total, total thing. So I'm gonna call in w to save in Vim. So I made a change and I saved, I should see this over in my other window. There we go, total thing. So that's now updated. And that's all I've got. Thanks for watching.