Okay, so let's walk through that little bit of a demo where you can see actually the nuts and bolts of how to create these both native big core ingestion permanent tables and also those external data connections to something like Google Spreadsheets or to a CSV inside of Google Cloud Storage. Okay, so let's do a quick demo. We're going to cover three of the most popular ways to ingest data in the BigQuery. Just didn't get a familiarity with how to do it as part of the web UI. First and foremost, ingesting a CSV file that you've already uploaded to Google Cloud Storage and with the buckets that you have, let's cover that first. So, to prepare, we need to first upload our CSV data into a Google Cloud Storage bucket. I've already done that. I have an example bucket. I have just a very small CSV file example here. And we need to get the link to where that is actually stored in our bucket and the link is actually just going to be GS which is google cloud storage slash your bucket name slash where that actual file name is. I made this public which means everyone can access it. So just keep it in mind, you might not want to do that. Copying that, I'm actually going to paste that in here. And one of the things that I want you to get very familiar with is changing the web protocol instead of being over the web. It's actually going to be from Google storage. This is our Cloud link that we're going to copy into our clipboards. And again, this is for when you actually practice this yourself. One of the main caveats again is just getting the data path correctly. This is the bucket name that you've created. And this is the actual CSV file including the extension that you're going to be ingesting. All right, so we've got our data source. It is on Google Cloud storage and it can't wait to make its way to the BigQuery. So to prepare a table for us this ultimately is going to reside in a new permanent table. And again, there's two different ways that we're going to query in. We're setting up a permanent table versus setting up an external data source. Right now, we're going to actually ingest that data into a BigQuery manage storage. And that's called a native BigQuery table. Create new table. And it is going to be a CSV. But, it actually is going to be from, instead of uploading it as a file upload. It's going to be from Google Cloud storage. And we'll cover a Google Drive example after this one. So Google Cloud Storage, we have that storage bucket name. And again, super common error that a lot of folks do here is when you copy over that hyperlink, they get rid of that https and just the only thing they should have here is your bucket name and then the actual file name with the extension and then the GS and the slash slash to start. It is the file format of CSV. We need to specify a table and we're going to store this. This imports from Google Cloud storage, the last part of our demo. And here is where you can actually specify whether or not you actually ingest or bring that data into BigQuery managed storage, it's called the native table, or set up an external data connection for when you actually run SQL queries. Those will be passed directly onto the data source where you have the performance disadvantages of not having your data be able to be cached because it's not stored inside of BigQuery managed storage. And we'll cover an example doing that on the Google Spreadsheet in just a moment. Okay, we have this schema which is the field names largely, automatically detected or we can specify that ourselves. I'm going to go ahead and just use automatically detect but you can manually type out as well. And again, keep in mind that a BigQuery will sometimes do its best and guess at some of the different field types but it's up to you to verify those. And if you have any header rows to skip, I just know the data found we have, the first row is a header row in our CSV and we want to much like when we are creating a permanent table, you have those same options here, right at the table with empty, append to the table. I'm going to choose overwrite table. And I already know it doesn't exist, just looking at it here. I just want to have that as part of this import or ingestion job and leave the rest of the options as the defaults. And let's go ahead and create this table. Immediately, you'll see that job is kicked off. And here are some of the recent jobs we've done before. And ingesting data into BigQuery, if you remember from the pricing module that we covered in the previous course, is free. So a check mark which means it has been loaded. The job's completed. And you can actually see the table here. Import from GCS is here. The schema is automatically detected. If this scheme was not correctly auto detected, you can rerun the job with adjusted schema which is exactly what we're going to do. String field zero, string field one. Those aren't the correct schema names. So what we wanted to do is actually specify that instead of letting an audit detect, this is just the schema, it's going to be a name field and a message field. And those are in the header rows right there. And if you don't automatically set up the schema detection, you're given a few other additional options here, like how many header rows to skip, how is your data deliminated, the rest of them we're just going to be leave as they are. So let's try creating that table again. Another load job is firing. Load job has been completed. And now, we have the name and the message. And you have the preview option again here. And again, if the option to preview is there, that's a surefire way to tell that your data is actually natively stored inside of that BigQuery manage storage. So we're going to preview that data and we've got a message, "Hi, this data is currently stored in a CSV on Google Cloud Storage and is going to be queried directly." In this particular case, we actually have ingested it permanently into BigQuery. The message is a little bit false here. But if we did want to query it directly, I'll show you how to do that next. I'm going to go ahead and create a brand new table, same exercise except this time, I want to say, connect directly to Google Cloud storage. And this is going to be on Google Cloud storage as our location. It's going to be a CSV file still on my clipboard. I still have the path to the bucket and the file name. And if you are doing the same job over and over again, you can actually repeat a previous job taht you've loaded, which is nice. If you're doing this at like a weekly cadence, you're batch loading in a ton of data and that's pretty easy to do through there. And instead of a native table, we're actually going to set up an external data connection. We're going to a specify a name and message because those are the two field names in our schema. And I'm going to go ahead and create, instead of, you are technically creating a table but it's more of like a pointer, right? Because that data is not actually being stored. So you saw how quickly that created it, right? And that job, there's no job that was actually kind of quick got queued off to bring in that data in. And you don't see the option to preview that data because that data is not stored inside a BigQuery. But what is stored inside a BigQuery is the configuration to where you can go out and get that data. Here if you look at the configuration, it's pulling in from this URI on Google Cloud storage and has all the different options there. And you can query it directly. And we're going to overwrite the query in the query window. And you can see that when you actually are running this query, it's passing through the BigQuery and Google Cloud Storage APIs to run that query directly on that CSV that's stored inside of Google Cloud storage. And now, the message is actually valid because the CSV is actually stored on Google Cloud storage and being queried directly. And again, to kind of recap, we just created a permanent table and ingested the data from the CSV file that was on Google Cloud storage. And then, we set up this external data connection where you'll lose the performance benefits of not being able to actually cache the data. I want to keep writing this query. And every single time it's reaching out to Google Cloud storage and poignancy as VC, normally, where you would expect that running the same query multiple times would get you that cached item here but you cannot cache data that's not stored natively inside a BigQuery. One more quick demo, we're going to do a connection to Google Drive. So, let's create our Google Drive Spreadsheet specified on Google Drive. Google Drive default, immediately if you saw that changed, it is an external connection. So when I copy my drive link that I've created earlier, it is a Google sheet. Same schema. And when I create that table, points are here as well. It's the same basic principle. Instead of a bucket link to Google Cloud storage, you know have a URI that reaches out to Google Drive and brings that data in. But same general concept of having an external data source connection. So you can query that table much like you would here, like we did previously. And you have the same pitfall of it could take longer to process because it has to run through that drive API to reach out and get that data. Here, I've actually got a pop up. So we actually need to authorize the drive API or something like that just happened behind the scenes to make sure that BigQuery can actually talk to the Google Spreadsheet file. It took a whopping 20 seconds mainly because the pop up I had authorize. And then, you had these measures that's returned. In this particular case, we're querying it directly as an external data source for your Google Drive file. And you get this is just to get you sort of familiar with all the different options that are available to you. Last one least, what a lot of you may be doing is uploading files locally. So creating a new table. I'm just getting a really descriptive with the table names here. It's going to be a file upload when I choose the file. This is an example CSV import. Let's see if it can do a good job of automatically detecting that schema with a header row. Overwrite that table that if it exists. Go ahead and create that table. Now we have four tables. It's loading from my local desktop in my downloads folder. And now we have uploaded CSV from desktop as a table. And here, again, you see the preview but the automatic schema detection, again, we can just go back and edit that. And then, rerun our load job, being sure to specify the file where we're loading it from. Overwrite to table if it exists. It kicks off another job. And now, we have the uploaded CSV from desktop as a correct table. And you can see the previewed message because that data again is stored inside of native BigQuery storage. And this data originally came from CSV that we had on our desktop. But now, it is stored natively inside the BigQuery. So you have four kind of common ways and each has their own different advantages. But the best way to use BigQuery for the highest performance, unless you have an edge case where you're doing like a one time extract transform load process is to take the best use of that Google BigQuery manage storage and ingest all that data into that service. All right. So back into the lectures for a quick recap. And then, we'll try this ourselves in a Qwiklab. Now as you've seen, there's a variety of ways to get your new data into BigQuery. We covered loading and storing this data as new permanent tables which have the benefit of being fully managed. We also looked at querying external data directly and why this may be useful for a one time extract transform and load jobs. Lastly, you can stream individual records into BigQuery through the API. Next up is our lab. we're going to practice creating new data sets, loading external data into tables, and running queries on this new data.
