So BigQuery Architecture. This is the best part of the lecture, right? What makes BigQuery special? And it's these three key innovations. And this is going to be really fun to dive into, even if you're a SQL guru, because you get to look at the underpinnings of what makes big BigQuery, BigQuery. So first and foremost, columns and records and data, are actually stored as individual compressed columns inside of what, again, I like to call the massive hard drive in the cloud, which is Google Colossus. That's the latest massive, scalable, storage layer. Second, and we will go into combi-storage and what that means in just a second. Second, the actual table itself, it's not one skyscraper tall, single table. That massive table, billions and billions of rows, is actually broken up and sharded into a bunch of small little pieces, which are then called upon to execute units of work in a massively parallel fashion which we're going to as part of that section. And then third, in answer to the first part that I brought up, where joins are apparently a necessity in the relational model, not so with BigQuery because you can actually store these parent-child relationships directly within the same table. I like to think of this as, if you can imagine for our IRS example, right? If you had an organizational details, and you wanted to drill into like, you remember that plus sign on some of those things that you've seen where you want to drill into details? Like drill from the parent and expand the child details, like drilling into all of Kaiser's filings for 2012, 2013, 2014, 2015? You can actually do that and store that architecturally in a concept that's called nested and repeated rows. So, these are three massive topics, and we're going to approach all of them in detail and you're going to get a nice lab after this practicing all of the SQL mainly as that third part, for the syntax and nested repeated fields. All right, who's ready to get started? So, first and foremost, BigQuery is column-based. What does that actually look like? So, as you can imagine, if it's column-based, we're storing individual columns. Now what does that mean as far as performance from reading a lot of these data values from disk, right? So, in traditional databases, you're storing individual records. So if you have a very, very wide record, take our IRS tax filing example, where you have like 200 columns, and the way that's stored out traditionally on disk, is each individual record is stored as an individual record out on disk. And, when you query that record, you're pulling up all of the bytes of all of the different columns that are present. Even if you just wanted to get three out of the 200 columns, that record itself cannot be broken apart. And realizing that, Google pioneered the way for column-oriented storage, in that Dremel white paper that we talked about back in 2010. And is continuously innovating on how to actually have new compression methodologies for storing these columns in the smallest for file size on disk and getting it at the absolute fastest speed. Now what does that mean for you? So at the end of the day, selecting only a few columns means you're only actually getting the performance hit for how big those individual columns are. So, back in our example, I used e-commerce products and if there a column that has product reviews, and the product reviews each field has like a paragraph of text, right? Lots of bytes. If you're not selecting that column, and you're just selecting the customer who ordered it, and how much they paid, you're just returning those two particular columns. So it's a mindset shift in thinking instead of record, it's actually being stored as an individual column on the disk. And one of the final points that we'll cover here is, with the column focus, you can actually be storing those related values, right? In SQL, you are largely going to be selecting those individual columns, and that's going to be very fast to pull all of those values because they're actually stored that way kind of vertically, right? As all those different column fields, as opposed to individual records that you have to access all of the record content and then kind of combine multiple of those records at the same time as you're producing your output for the user. Okay, so column-oriented storage is a key concept.
