Learn how to use BigQuery ML on Google Cloud Platform to predict the outcome of horse races 🏇 - Machine learning skill not required. https://fireship.io/lessons/bigquery-ml-quickstart/

- Big Query https://cloud.google.com/bigquery/
- DataLab https://cloud.google.com/datalab/
- Horse Racing Dataset https://www.kaggle.com/lukebyrne/horses-for-courses
2024-02-04T06:54:09.656Z
nova-2
2-general-nova
2024-01-09.29447
30089e05-99d1-4376-b32e-c263170674af
9191dab8-43bd-4b74-8fc2-f18be584f2e5
a50afd7c74b7fb4dfefba9f4122ac2cd90ba98a7c04395110225c06555f4a627
67875a7f-c9c4-48a0-aa55-5bdb8a91c34a
deprecated
One of my favorite things to do with technology is make money.
And by that, of course, I'm talking about gambling money.
The information in here is worth 1,000,000 and I'm giving it to you.
That's very nice.
Thank you very much.
Now why don't you make like a tree and get out of here.
Several months ago, Google released a machine learning pipeline for their popular data warehouse, BigQuery.
In today's video, you'll learn about SQL, Machine Learning, and Cloud Infrastructure, with the end goal of building a predictive model that will help you determine the outcome of horse races.
If you're new to the channel, like and subscribe and you can find a full walkthrough on Fireship IO.
Now, I don't mean to disappoint you, but you probably won't get rich betting on horses, but by the end of this video, you will know how to upload your data to BigQuery and then run machine learning models against it without needing to be a data scientist.
And the model we'll build will not predictive value, but horseracing is still very random, so use this at your own risk.
First of all, what is BigQuery and why would you It is a serverless data warehouse that allows you to store massive amounts of data, and then query that data with standard SQL It provides a centralized place to put all of your data, which might include things like Firestore, Excel spreadsheets, legacy databases, etcetera.
Then you can make queries against this data to use it for combined reporting, and as we'll see later in the video, for machine learning.
Just keep in mind it's not a production database, so you wouldn't use it in your front end applications.
For that, you would use something like Firestore or a regular SQL database.
To get started, you just need to have a Google Cloud Platform or a Firebase project, and you can find BigQuery under the big data panel, and then I recommend pinning it to the top just so it's easy to access on the console.
Now BigQuery is designed to scale the huge amounts of data, and by huge, I mean petabytes of data.
So when you make a query, it will queue that up as a job in the background.
But before we can get to that point, we need to upload some data to BigQuery.
There are many different ways to do this, but we're just going to upload a CSV file to cloud storage and then connect it as a table to BigQuery.
We're going to grab our horse racing data from a site called Kaggle.
It's a platform that allows companies to host their own data science competitions and also provides a large number of free datasets that we can use.
Most importantly, they have this horses for courses dataset, which contains about 250,000 rows of horse racing samples.
Go ahead and download the horses dot CSV file that you find on this page.
Now like a lot of datasets, it's not perfect.
And one thing I noticed is that this dataset has mismatched data types, which causes errors when it goes into BigQuery.
Just like a regular SQL database, BigQuery has a strict schema, which means we can't have mismatched data types in our columns.
We could fix this by writing some Python code, or we could just open it up in cell and format the number columns as numbers.
Now that our data is properly formatted, the next step is to upload it to a cloud storage bucket.
Go to the storage tab on GCP, and then you can create a new bucket or use an existing bucket if you have one.
Then we'll upload the horse's CSV file, which is about a 180 megabytes.
If you have a file that's less than 10 megabytes, you can skip this step and just upload it directly to BigQuery.
Now getting back to BigQuery, the first thing we'll do is click on the add data button and then create new dataset.
Give your dataset a name and then you can just select the default options here.
A dataset is just a collection of tables which again you can just think of as SQL tables.
Go ahead and click the create table button, and we'll connect the file that we just uploaded to the cloud storage bucket.
Now normally with SQL, you would have to define your schema manually, which, of course, we don't have for this dataset.
There's at least 50 different features or columns on that horse's spreadsheet, and it would really suck if we had to go through and define that schema manually.
Luckily, BigQuery has this thing called schema auto detect, which will randomly sample some of the rows and try to infer their data type.
So make sure you have that selected.
And we'll wanna make sure that it skips the header row, and we can do that by setting the skip header rows to 1.
After that, you can click create table
and that will queue up a job in the background to
create this table.
You can monitor the progress of the job, which should only take a couple of seconds in this case, and it will also report any errors if it fails to upload the table.
If everything worked, you should have a table that looks like this where the field names match the header row of the CSV file.
You can look at the schema and you can also preview the actual values in the table here as well.
The The next thing we'll do is make an actual SQL query against this dataset.
We can do this directly in the console, but keep in mind you could also do this from the command line or with one of the client libraries.
First, we'll click on create table to set up some of the boilerplate code, and we'll start the query with a select statement, which will select specific fields from the table.
In this particular dataset, it is the position value that we're trying to predict.
That is the result that a horse took in a given race, so 0 would be a scratch or a non place.
First would be the winner, second would be the runner-up, and so on.
We'll also select the horse's sex and age from this table so we can see if that has any relationship to the position.
We also wanna make sure that sex is not null, of course, and we'll limit it to 10,000 records just to make it easier to work with in Data Studio.
We can go ahead and run this query, and it will run a job in the background.
That will provide us with a new table with the queried results.
Now there's a lot more we round.
That will provide us with a new table with the query results.
Now there's a lot more we could get into with SQL queries, like joining multiple tables together and things like that.
But when it comes to machine learning, the first thing you generally want to do is explore your data.
And BigQuery makes this really easy because it's integrated with another service called Data Studio.
By clicking on export, we can transfer this data to Data Studio and do a visual analysis on it.
Basically, we're taking the query that we just created and visualizing it.
Everything in Data Studio is just point and click or drag and drop.
And what I'm doing here is creating a bar chart to see if the sex of the horse has any relationship to its position in the races.
And what we can see here is that Colts and Phillies have a higher chance of positioning well in a race.
That means this field is probably a useful feature to include in our machine learning model.
Now we could train our model directly from the BigQuery console, but I wanna show you a really cool tool called Datalab.
When I work on machine learning problems, I like to use Python Notebooks.
And Datalab basically gives you a Python notebook with unlimited computing resources.
To set up a Datalab, the first thing we'll do is activate a shell session, then run Datalab create followed by whatever name you wanna give it.
Then it's going to ask you to select a region, then it will take you through the steps to set up an SSH tunnel.
And that's just a way for you to access a Python notebook that's connected to your cloud resources in the browser.
That'll take a few minutes to set up, But once it's complete, you can go to the Compute Engine tab, and you'll be able to see the actual virtual machine that it created.
Keep in mind that virtual machines accrue cost, so you'll wanna shut this down after you're done with this tutorial.
Once it's done, go ahead and click on the web preview button and then select change port.
It runs on port 8,801, so go ahead and click change in preview and you should be good to go.
Now you have this playground for all your data analysis and processing needs, and it's already wired up with your data from BigQuery.
And even though we're in a Python environment, we're only going to be writing a tiny bit of Python code.
The cool thing about notebooks is that you can run code on a cell by cell basis.
If you're not already familiar with them, I recommend checking out some of the examples and running the code just to get a feel for how it works.
Now I'm starting from a new notebook, And I first wanna make sure that we have access to our BigQuery table.
We use the b q command and call tables list, and that should list out the table that you have in BigQuery.
And you can also use the help command just to list out all of the commands available on the b q namespace.
Now that we know the name of our table, let's go ahead and make a query against it in this Python notebook.
We use the b q query command, and then we can just write a standard SQL statement.
We can go ahead and click play, then it should take a second to run and then output the result of that query in the notebook.
And we finally reached the point where BigQuery ML comes into play.
We're going to write a special SQL statement that makes a query while at the same time creating a machine learning model based on that data.
We use the create or replace model statement and then give it a name of ML model.
And it can live in the same dataset as our horses table.
From here, we can pass in a variety of different options.
And first, we'll set the model type to logistic regression.
In machine learning, this is a classification problem because we're trying to predict a specific label, which is either 1st place, 2nd place, 3rd, and so one.
The other option is Linear Regression, which you would use if you have a continuous value.
For example, the measurement of length which could have any infinite number of values depending on how precise you go.
And confusingly because we have a classification problem, we use the logistic regression model type.
The next thing we need to do is tell it what our input label is or, in other words, the thing we're trying to predict which is the position field.
Once we have the options defined, we can just make a query to our table using select and from.
And BigQuery will automatically do all of the machine learning stuff under the hood, like separate your data into a training and validation set, it will choose the hyperparameters for the algorithm and it will run multiple training iterations based on what it thinks is fast.
That'll take a minute.
And then if we go back to the BigQuery console, you can see that we now have our machine learning model in our dataset.
The next thing we need to do is determine if our machine learning model is any good.
We'll run another query.
This time, we'll select all the fields from the model, and then we'll run ML dot evaluate.
And we'll point it to the ML model in our dataset.
Then from inside here, we can make a query to the actual horse's data and evaluate it based on that model.
This is going to print out a variety of different metrics, but the one we're concerned about is log loss.
That's the value that we're trying to minimize.
You can also keep an eye on the area under the curve.
It's currently at 0.5, which means our model is completely random or in other words, completely worthless.
How do we make this model better?
Well, in our case, we're only using the sex in the age of the horse to determine the outcome.
There's a lot more data in this dataset, so let's go ahead and feed that into the machine learning model to see if we get better results.
So I'm selecting a bunch of other fields like the handicap weight, penalty, barrier, and stuff like that.
If we have high quality data, then these fields should increase the predictive value of the model.
When we retrain and reevaluate the model, you can see the log loss now decreases to 0.29, and the area under the curve increases to 0.7.
Now we have a model that we should be able to take to the racetrack to make an unlimited amount of money.
Now we can make predictions on new data by using the mlpredict statement.
You would use this one by creating a new table with some new data that you acquired at the racetrack.
Using a similar schema, you would feed it into this machine learning model, and it would give you predictions on that new data.
In this example here, I'm just randomly sampling 10 items from our existing dataset.
And you can see the result of that query is a prediction for the position of the horse in that race.
I'm gonna go ahead and wrap things up there.
If this video helped you, please like and subscribe.
And if you want access to even more content, consider becoming a pro member at Fireship I l.
Thanks for watching, and I will talk to you soon.
All you gotta do is bet on the winner and you'll never lose.

Speaker 0: One of my favorite things to do with technology is make money. And by that, of course, I'm talking about gambling money.

Speaker 1: The information in here is worth 1,000,000 and I'm giving it to you. That's very nice. Thank you very much. Now why don't you make like a tree and get out of here.

Speaker 0: Several months ago, Google released a machine learning pipeline for their popular data warehouse, BigQuery. In today's video, you'll learn about SQL, Machine Learning, and Cloud Infrastructure, with the end goal of building a predictive model that will help you determine the outcome of horse races. If you're new to the channel, like and subscribe and you can find a full walkthrough on Fireship IO. Now, I don't mean to disappoint you, but you probably won't get rich betting on horses, but by the end of this video, you will know how to upload your data to BigQuery and then run machine learning models against it without needing to be a data scientist. And the model we'll build will not predictive value, but horseracing is still very random, so use this at your own risk.

First of all, what is BigQuery and why would you It is a serverless data warehouse that allows you to store massive amounts of data, and then query that data with standard SQL It provides a centralized place to put all of your data, which might include things like Firestore, Excel spreadsheets, legacy databases, etcetera. Then you can make queries against this data to use it for combined reporting, and as we'll see later in the video, for machine learning. Just keep in mind it's not a production database, so you wouldn't use it in your front end applications. For that, you would use something like Firestore or a regular SQL database. To get started, you just need to have a Google Cloud Platform or a Firebase project, and you can find BigQuery under the big data panel, and then I recommend pinning it to the top just so it's easy to access on the console.

Now BigQuery is designed to scale the huge amounts of data, and by huge, I mean petabytes of data. So when you make a query, it will queue that up as a job in the background. But before we can get to that point, we need to upload some data to BigQuery. There are many different ways to do this, but we're just going to upload a CSV file to cloud storage and then connect it as a table to BigQuery. We're going to grab our horse racing data from a site called Kaggle.

It's a platform that allows companies to host their own data science competitions and also provides a large number of free datasets that we can use. Most importantly, they have this horses for courses dataset, which contains about 250,000 rows of horse racing samples. Go ahead and download the horses dot CSV file that you find on this page. Now like a lot of datasets, it's not perfect. And one thing I noticed is that this dataset has mismatched data types, which causes errors when it goes into BigQuery.

Just like a regular SQL database, BigQuery has a strict schema, which means we can't have mismatched data types in our columns. We could fix this by writing some Python code, or we could just open it up in cell and format the number columns as numbers. Now that our data is properly formatted, the next step is to upload it to a cloud storage bucket. Go to the storage tab on GCP, and then you can create a new bucket or use an existing bucket if you have one. Then we'll upload the horse's CSV file, which is about a 180 megabytes.

If you have a file that's less than 10 megabytes, you can skip this step and just upload it directly to BigQuery. Now getting back to BigQuery, the first thing we'll do is click on the add data button and then create new dataset. Give your dataset a name and then you can just select the default options here. A dataset is just a collection of tables which again you can just think of as SQL tables. Go ahead and click the create table button, and we'll connect the file that we just uploaded to the cloud storage bucket.

Now normally with SQL, you would have to define your schema manually, which, of course, we don't have for this dataset. There's at least 50 different features or columns on that horse's spreadsheet, and it would really suck if we had to go through and define that schema manually. Luckily, BigQuery has this thing called schema auto detect, which will randomly sample some of the rows and try to infer their data type. So make sure you have that selected. And we'll wanna make sure that it skips the header row, and we can do that by setting the skip header rows to 1.

After that, you can click create table

Speaker 2: and that will queue up a job in the background to

Speaker 0: create this table. You can monitor the progress of the job, which should only take a couple of seconds in this case, and it will also report any errors if it fails to upload the table. If everything worked, you should have a table that looks like this where the field names match the header row of the CSV file. You can look at the schema and you can also preview the actual values in the table here as well. The The next thing we'll do is make an actual SQL query against this dataset.

We can do this directly in the console, but keep in mind you could also do this from the command line or with one of the client libraries. First, we'll click on create table to set up some of the boilerplate code, and we'll start the query with a select statement, which will select specific fields from the table. In this particular dataset, it is the position value that we're trying to predict. That is the result that a horse took in a given race, so 0 would be a scratch or a non place. First would be the winner, second would be the runner-up, and so on.

We'll also select the horse's sex and age from this table so we can see if that has any relationship to the position. We also wanna make sure that sex is not null, of course, and we'll limit it to 10,000 records just to make it easier to work with in Data Studio. We can go ahead and run this query, and it will run a job in the background. That will provide us with a new table with the queried results. Now there's a lot more we round.

That will provide us with a new table with the query results. Now there's a lot more we could get into with SQL queries, like joining multiple tables together and things like that. But when it comes to machine learning, the first thing you generally want to do is explore your data. And BigQuery makes this really easy because it's integrated with another service called Data Studio. By clicking on export, we can transfer this data to Data Studio and do a visual analysis on it.

Basically, we're taking the query that we just created and visualizing it. Everything in Data Studio is just point and click or drag and drop. And what I'm doing here is creating a bar chart to see if the sex of the horse has any relationship to its position in the races. And what we can see here is that Colts and Phillies have a higher chance of positioning well in a race. That means this field is probably a useful feature to include in our machine learning model.

Now we could train our model directly from the BigQuery console, but I wanna show you a really cool tool called Datalab. When I work on machine learning problems, I like to use Python Notebooks. And Datalab basically gives you a Python notebook with unlimited computing resources. To set up a Datalab, the first thing we'll do is activate a shell session, then run Datalab create followed by whatever name you wanna give it. Then it's going to ask you to select a region, then it will take you through the steps to set up an SSH tunnel.

And that's just a way for you to access a Python notebook that's connected to your cloud resources in the browser. That'll take a few minutes to set up, But once it's complete, you can go to the Compute Engine tab, and you'll be able to see the actual virtual machine that it created. Keep in mind that virtual machines accrue cost, so you'll wanna shut this down after you're done with this tutorial. Once it's done, go ahead and click on the web preview button and then select change port. It runs on port 8,801, so go ahead and click change in preview and you should be good to go.

Now you have this playground for all your data analysis and processing needs, and it's already wired up with your data from BigQuery. And even though we're in a Python environment, we're only going to be writing a tiny bit of Python code. The cool thing about notebooks is that you can run code on a cell by cell basis. If you're not already familiar with them, I recommend checking out some of the examples and running the code just to get a feel for how it works. Now I'm starting from a new notebook, And I first wanna make sure that we have access to our BigQuery table.

We use the b q command and call tables list, and that should list out the table that you have in BigQuery. And you can also use the help command just to list out all of the commands available on the b q namespace. Now that we know the name of our table, let's go ahead and make a query against it in this Python notebook. We use the b q query command, and then we can just write a standard SQL statement. We can go ahead and click play, then it should take a second to run and then output the result of that query in the notebook.

And we finally reached the point where BigQuery ML comes into play. We're going to write a special SQL statement that makes a query while at the same time creating a machine learning model based on that data. We use the create or replace model statement and then give it a name of ML model. And it can live in the same dataset as our horses table. From here, we can pass in a variety of different options.

And first, we'll set the model type to logistic regression. In machine learning, this is a classification problem because we're trying to predict a specific label, which is either 1st place, 2nd place, 3rd, and so one. The other option is Linear Regression, which you would use if you have a continuous value. For example, the measurement of length which could have any infinite number of values depending on how precise you go. And confusingly because we have a classification problem, we use the logistic regression model type.

The next thing we need to do is tell it what our input label is or, in other words, the thing we're trying to predict which is the position field. Once we have the options defined, we can just make a query to our table using select and from. And BigQuery will automatically do all of the machine learning stuff under the hood, like separate your data into a training and validation set, it will choose the hyperparameters for the algorithm and it will run multiple training iterations based on what it thinks is fast. That'll take a minute. And then if we go back to the BigQuery console, you can see that we now have our machine learning model in our dataset.

The next thing we need to do is determine if our machine learning model is any good. We'll run another query. This time, we'll select all the fields from the model, and then we'll run ML dot evaluate. And we'll point it to the ML model in our dataset. Then from inside here, we can make a query to the actual horse's data and evaluate it based on that model.

This is going to print out a variety of different metrics, but the one we're concerned about is log loss. That's the value that we're trying to minimize. You can also keep an eye on the area under the curve. It's currently at 0.5, which means our model is completely random or in other words, completely worthless. How do we make this model better?

Well, in our case, we're only using the sex in the age of the horse to determine the outcome. There's a lot more data in this dataset, so let's go ahead and feed that into the machine learning model to see if we get better results. So I'm selecting a bunch of other fields like the handicap weight, penalty, barrier, and stuff like that. If we have high quality data, then these fields should increase the predictive value of the model. When we retrain and reevaluate the model, you can see the log loss now decreases to 0.29, and the area under the curve increases to 0.7.

Now we have a model that we should be able to take to the racetrack to make an unlimited amount of money. Now we can make predictions on new data by using the mlpredict statement. You would use this one by creating a new table with some new data that you acquired at the racetrack. Using a similar schema, you would feed it into this machine learning model, and it would give you predictions on that new data. In this example here, I'm just randomly sampling 10 items from our existing dataset.

And you can see the result of that query is a prediction for the position of the horse in that race. I'm gonna go ahead and wrap things up there. If this video helped you, please like and subscribe. And if you want access to even more content, consider becoming a pro member at Fireship I l. Thanks for watching, and I will talk to you soon.

Speaker 1: All you gotta do is bet on the winner and you'll never lose.
One of my favorite things to do with technology is make money. And by that, of course, I'm talking about gambling money. The information in here is worth 1,000,000 and I'm giving it to you. That's very nice. Thank you very much. Now why don't you make like a tree and get out of here. Several months ago, Google released a machine learning pipeline for their popular data warehouse, BigQuery. In today's video, you'll learn about SQL, Machine Learning, and Cloud Infrastructure, with the end goal of building a predictive model that will help you determine the outcome of horse races. If you're new to the channel, like and subscribe and you can find a full walkthrough on Fireship IO. Now, I don't mean to disappoint you, but you probably won't get rich betting on horses, but by the end of this video, you will know how to upload your data to BigQuery and then run machine learning models against it without needing to be a data scientist. And the model we'll build will not predictive value, but horseracing is still very random, so use this at your own risk. First of all, what is BigQuery and why would you It is a serverless data warehouse that allows you to store massive amounts of data, and then query that data with standard SQL It provides a centralized place to put all of your data, which might include things like Firestore, Excel spreadsheets, legacy databases, etcetera. Then you can make queries against this data to use it for combined reporting, and as we'll see later in the video, for machine learning. Just keep in mind it's not a production database, so you wouldn't use it in your front end applications. For that, you would use something like Firestore or a regular SQL database. To get started, you just need to have a Google Cloud Platform or a Firebase project, and you can find BigQuery under the big data panel, and then I recommend pinning it to the top just so it's easy to access on the console. Now BigQuery is designed to scale the huge amounts of data, and by huge, I mean petabytes of data.
databases
machine learning
streaming
data management
gambling
data
racing
deep learning
technology
google
So when you make a query, it will queue that up as a job in the background. But before we can get to that point, we need to upload some data to BigQuery. There are many different ways to do this, but we're just going to upload a CSV file to cloud storage and then connect it as a table to BigQuery.
We're going to grab our horse racing data from a site called Kaggle. It's a platform that allows companies to host their own data science competitions and also provides a large number of free datasets that we can use. Most importantly, they have this horses for courses dataset, which contains about 250,000 rows of horse racing samples. Go ahead and download the horses dot CSV file that you find on this page. Now like a lot of datasets, it's not perfect. And one thing I noticed is that this dataset has mismatched data types, which causes errors when it goes into BigQuery. Just like a regular SQL database, BigQuery has a strict schema, which means we can't have mismatched data types in our columns. We could fix this by writing some Python code, or we could just open it up in cell and format the number columns as numbers. Now that our data is properly formatted, the next step is to upload it to a cloud storage bucket.
databases
data analysis
data
data management
Go to the storage tab on GCP, and then you can create a new bucket or use an existing bucket if you have one. Then we'll upload the horse's CSV file, which is about a 180 megabytes. If you have a file that's less than 10 megabytes, you can skip this step and just upload it directly to BigQuery.
Now getting back to BigQuery, the first thing we'll do is click on the add data button and then create new dataset. Give your dataset a name and then you can just select the default options here. A dataset is just a collection of tables which again you can just think of as SQL tables. Go ahead and click the create table button, and we'll connect the file that we just uploaded to the cloud storage bucket. Now normally with SQL, you would have to define your schema manually, which, of course, we don't have for this dataset. There's at least 50 different features or columns on that horse's spreadsheet, and it would really suck if we had to go through and define that schema manually. Luckily, BigQuery has this thing called schema auto detect, which will randomly sample some of the rows and try to infer their data type.
databases
data management
data
So make sure you have that selected. And we'll wanna make sure that it skips the header row, and we can do that by setting the skip header rows to 1. After that, you can click create table and that will queue up a job in the background to create this table. You can monitor the progress of the job, which should only take a couple of seconds in this case, and it will also report any errors if it fails to upload the table.
If everything worked, you should have a table that looks like this where the field names match the header row of the CSV file. You can look at the schema and you can also preview the actual values in the table here as well. The The next thing we'll do is make an actual SQL query against this dataset. We can do this directly in the console, but keep in mind you could also do this from the command line or with one of the client libraries. First, we'll click on create table to set up some of the boilerplate code, and we'll start the query with a select statement, which will select specific fields from the table. In this particular dataset, it is the position value that we're trying to predict. That is the result that a horse took in a given race, so 0 would be a scratch or a non place. First would be the winner, second would be the runner-up, and so on. We'll also select the horse's sex and age from this table so we can see if that has any relationship to the position. We also wanna make sure that sex is not null, of course, and we'll limit it to 10,000 records just to make it easier to work with in Data Studio. We can go ahead and run this query, and it will run a job in the background. That will provide us with a new table with the queried results. Now there's a lot more we round. That will provide us with a new table with the query results. Now there's a lot more we could get into with SQL queries, like joining multiple tables together and things like that. But when it comes to machine learning, the first thing you generally want to do is explore your data. And BigQuery makes this really easy because it's integrated with another service called Data Studio. By clicking on export, we can transfer this data to Data Studio and do a visual analysis on it. Basically, we're taking the query that we just created and visualizing it. Everything in Data Studio is just point and click or drag and drop. And what I'm doing here is creating a bar chart to see if the sex of the horse has any relationship to its position in the races. And what we can see here is that Colts and Phillies have a higher chance of positioning well in a race. That means this field is probably a useful feature to include in our machine learning model. Now we could train our model directly from the BigQuery console, but I wanna show you a really cool tool called Datalab. When I work on machine learning problems, I like to use Python Notebooks. And Datalab basically gives you a Python notebook with unlimited computing resources. To set up a Datalab, the first thing we'll do is activate a shell session, then run Datalab create followed by whatever name you wanna give it. Then it's going to ask you to select a region, then it will take you through the steps to set up an SSH tunnel.
databases
machine learning
data analysis
data
race
deep learning
scouting
gender
data management
racing
visualization
programming
And that's just a way for you to access a Python notebook that's connected to your cloud resources in the browser. That'll take a few minutes to set up, But once it's complete, you can go to the Compute Engine tab, and you'll be able to see the actual virtual machine that it created. Keep in mind that virtual machines accrue cost, so you'll wanna shut this down after you're done with this tutorial. Once it's done, go ahead and click on the web preview button and then select change port. It runs on port 8,801, so go ahead and click change in preview and you should be good to go. Now you have this playground for all your data analysis and processing needs, and it's already wired up with your data from BigQuery.
And even though we're in a Python environment, we're only going to be writing a tiny bit of Python code. The cool thing about notebooks is that you can run code on a cell by cell basis. If you're not already familiar with them, I recommend checking out some of the examples and running the code just to get a feel for how it works.
programming
Now I'm starting from a new notebook, And I first wanna make sure that we have access to our BigQuery table. We use the b q command and call tables list, and that should list out the table that you have in BigQuery.
And you can also use the help command just to list out all of the commands available on the b q namespace. Now that we know the name of our table, let's go ahead and make a query against it in this Python notebook. We use the b q query command, and then we can just write a standard SQL statement. We can go ahead and click play, then it should take a second to run and then output the result of that query in the notebook. And we finally reached the point where BigQuery ML comes into play. We're going to write a special SQL statement that makes a query while at the same time creating a machine learning model based on that data. We use the create or replace model statement and then give it a name of ML model. And it can live in the same dataset as our horses table. From here, we can pass in a variety of different options. And first, we'll set the model type to logistic regression. In machine learning, this is a classification problem because we're trying to predict a specific label, which is either 1st place, 2nd place, 3rd, and so one. The other option is Linear Regression, which you would use if you have a continuous value.
machine learning
modeling
data
data analysis
databases
For example, the measurement of length which could have any infinite number of values depending on how precise you go. And confusingly because we have a classification problem, we use the logistic regression model type. The next thing we need to do is tell it what our input label is or, in other words, the thing we're trying to predict which is the position field.
Once we have the options defined, we can just make a query to our table using select and from. And BigQuery will automatically do all of the machine learning stuff under the hood, like separate your data into a training and validation set, it will choose the hyperparameters for the algorithm and it will run multiple training iterations based on what it thinks is fast. That'll take a minute. And then if we go back to the BigQuery console, you can see that we now have our machine learning model in our dataset. The next thing we need to do is determine if our machine learning model is any good. We'll run another query. This time, we'll select all the fields from the model, and then we'll run ML dot evaluate. And we'll point it to the ML model in our dataset. Then from inside here, we can make a query to the actual horse's data and evaluate it based on that model. This is going to print out a variety of different metrics, but the one we're concerned about is log loss. That's the value that we're trying to minimize. You can also keep an eye on the area under the curve. It's currently at 0.5, which means our model is completely random or in other words, completely worthless. How do we make this model better? Well, in our case, we're only using the sex in the age of the horse to determine the outcome. There's a lot more data in this dataset, so let's go ahead and feed that into the machine learning model to see if we get better results. So I'm selecting a bunch of other fields like the handicap weight, penalty, barrier, and stuff like that. If we have high quality data, then these fields should increase the predictive value of the model. When we retrain and reevaluate the model, you can see the log loss now decreases to 0.29, and the area under the curve increases to 0.7. Now we have a model that we should be able to take to the racetrack to make an unlimited amount of money. Now we can make predictions on new data by using the mlpredict statement. You would use this one by creating a new table with some new data that you acquired at the racetrack. Using a similar schema, you would feed it into this machine learning model, and it would give you predictions on that new data. In this example here, I'm just randomly sampling 10 items from our existing dataset. And you can see the result of that query is a prediction for the position of the horse in that race. I'm gonna go ahead and wrap things up there. If this video helped you, please like and subscribe. And if you want access to even more content, consider becoming a pro member at Fireship I l. Thanks for watching, and I will talk to you soon. All you gotta do is bet on the winner and you'll never lose.
machine learning
race
statistics
data
data analysis
youtube
modeling
racing
databases
artificial intelligence
neural networks
measurement
deep learning
fitness
fertility
entertainment
media
One of my favorite things to do with technology is make money. And by that, of course, I'm talking about gambling money. The information in here is worth 1,000,000 and I'm giving it to you. That's very nice. Thank you very much. Now why don't you make like a tree and get out of here. Several months ago, Google released a machine learning pipeline for their popular data warehouse, BigQuery. In today's video, you'll learn about SQL, Machine Learning, and Cloud Infrastructure, with the end goal of building a predictive model that will help you determine the outcome of horse races. If you're new to the channel, like and subscribe and you can find a full walkthrough on Fireship IO. Now, I don't mean to disappoint you, but you probably won't get rich betting on horses, but by the end of this video, you will know how to upload your data to BigQuery and then run machine learning models against it without needing to be a data scientist. And the model we'll build will not predictive value, but horseracing is still very random, so use this at your own risk. First of all, what is BigQuery and why would you It is a serverless data warehouse that allows you to store massive amounts of data, and then query that data with standard SQL It provides a centralized place to put all of your data, which might include things like Firestore, Excel spreadsheets, legacy databases, etcetera. Then you can make queries against this data to use it for combined reporting, and as we'll see later in the video, for machine learning. Just keep in mind it's not a production database, so you wouldn't use it in your front end applications. For that, you would use something like Firestore or a regular SQL database. To get started, you just need to have a Google Cloud Platform or a Firebase project, and you can find BigQuery under the big data panel, and then I recommend pinning it to the top just so it's easy to access on the console. Now BigQuery is designed to scale the huge amounts of data, and by huge, I mean petabytes of data. So when you make a query, it will queue that up as a job in the background. But before we can get to that point, we need to upload some data to BigQuery. There are many different ways to do this, but we're just going to upload a CSV file to cloud storage and then connect it as a table to BigQuery. We're going to grab our horse racing data from a site called Kaggle. It's a platform that allows companies to host their own data science competitions and also provides a large number of free datasets that we can use. Most importantly, they have this horses for courses dataset, which contains about 250,000 rows of horse racing samples. Go ahead and download the horses dot CSV file that you find on this page. Now like a lot of datasets, it's not perfect. And one thing I noticed is that this dataset has mismatched data types, which causes errors when it goes into BigQuery. Just like a regular SQL database, BigQuery has a strict schema, which means we can't have mismatched data types in our columns. We could fix this by writing some Python code, or we could just open it up in cell and format the number columns as numbers. Now that our data is properly formatted, the next step is to upload it to a cloud storage bucket. Go to the storage tab on GCP, and then you can create a new bucket or use an existing bucket if you have one. Then we'll upload the horse's CSV file, which is about a 180 megabytes. If you have a file that's less than 10 megabytes, you can skip this step and just upload it directly to BigQuery. Now getting back to BigQuery, the first thing we'll do is click on the add data button and then create new dataset. Give your dataset a name and then you can just select the default options here. A dataset is just a collection of tables which again you can just think of as SQL tables. Go ahead and click the create table button, and we'll connect the file that we just uploaded to the cloud storage bucket. Now normally with SQL, you would have to define your schema manually, which, of course, we don't have for this dataset. There's at least 50 different features or columns on that horse's spreadsheet, and it would really suck if we had to go through and define that schema manually. Luckily, BigQuery has this thing called schema auto detect, which will randomly sample some of the rows and try to infer their data type. So make sure you have that selected. And we'll wanna make sure that it skips the header row, and we can do that by setting the skip header rows to 1. After that, you can click create table and that will queue up a job in the background to create this table. You can monitor the progress of the job, which should only take a couple of seconds in this case, and it will also report any errors if it fails to upload the table. If everything worked, you should have a table that looks like this where the field names match the header row of the CSV file. You can look at the schema and you can also preview the actual values in the table here as well. The The next thing we'll do is make an actual SQL query against this dataset. We can do this directly in the console, but keep in mind you could also do this from the command line or with one of the client libraries. First, we'll click on create table to set up some of the boilerplate code, and we'll start the query with a select statement, which will select specific fields from the table. In this particular dataset, it is the position value that we're trying to predict. That is the result that a horse took in a given race, so 0 would be a scratch or a non place. First would be the winner, second would be the runner-up, and so on. We'll also select the horse's sex and age from this table so we can see if that has any relationship to the position. We also wanna make sure that sex is not null, of course, and we'll limit it to 10,000 records just to make it easier to work with in Data Studio. We can go ahead and run this query, and it will run a job in the background. That will provide us with a new table with the queried results. Now there's a lot more we round. That will provide us with a new table with the query results. Now there's a lot more we could get into with SQL queries, like joining multiple tables together and things like that. But when it comes to machine learning, the first thing you generally want to do is explore your data. And BigQuery makes this really easy because it's integrated with another service called Data Studio. By clicking on export, we can transfer this data to Data Studio and do a visual analysis on it. Basically, we're taking the query that we just created and visualizing it. Everything in Data Studio is just point and click or drag and drop. And what I'm doing here is creating a bar chart to see if the sex of the horse has any relationship to its position in the races. And what we can see here is that Colts and Phillies have a higher chance of positioning well in a race. That means this field is probably a useful feature to include in our machine learning model. Now we could train our model directly from the BigQuery console, but I wanna show you a really cool tool called Datalab. When I work on machine learning problems, I like to use Python Notebooks. And Datalab basically gives you a Python notebook with unlimited computing resources. To set up a Datalab, the first thing we'll do is activate a shell session, then run Datalab create followed by whatever name you wanna give it. Then it's going to ask you to select a region, then it will take you through the steps to set up an SSH tunnel. And that's just a way for you to access a Python notebook that's connected to your cloud resources in the browser. That'll take a few minutes to set up, But once it's complete, you can go to the Compute Engine tab, and you'll be able to see the actual virtual machine that it created. Keep in mind that virtual machines accrue cost, so you'll wanna shut this down after you're done with this tutorial. Once it's done, go ahead and click on the web preview button and then select change port. It runs on port 8,801, so go ahead and click change in preview and you should be good to go. Now you have this playground for all your data analysis and processing needs, and it's already wired up with your data from BigQuery. And even though we're in a Python environment, we're only going to be writing a tiny bit of Python code. The cool thing about notebooks is that you can run code on a cell by cell basis. If you're not already familiar with them, I recommend checking out some of the examples and running the code just to get a feel for how it works. Now I'm starting from a new notebook, And I first wanna make sure that we have access to our BigQuery table. We use the b q command and call tables list, and that should list out the table that you have in BigQuery. And you can also use the help command just to list out all of the commands available on the b q namespace. Now that we know the name of our table, let's go ahead and make a query against it in this Python notebook. We use the b q query command, and then we can just write a standard SQL statement. We can go ahead and click play, then it should take a second to run and then output the result of that query in the notebook. And we finally reached the point where BigQuery ML comes into play. We're going to write a special SQL statement that makes a query while at the same time creating a machine learning model based on that data. We use the create or replace model statement and then give it a name of ML model. And it can live in the same dataset as our horses table. From here, we can pass in a variety of different options. And first, we'll set the model type to logistic regression. In machine learning, this is a classification problem because we're trying to predict a specific label, which is either 1st place, 2nd place, 3rd, and so one. The other option is Linear Regression, which you would use if you have a continuous value. For example, the measurement of length which could have any infinite number of values depending on how precise you go. And confusingly because we have a classification problem, we use the logistic regression model type. The next thing we need to do is tell it what our input label is or, in other words, the thing we're trying to predict which is the position field. Once we have the options defined, we can just make a query to our table using select and from. And BigQuery will automatically do all of the machine learning stuff under the hood, like separate your data into a training and validation set, it will choose the hyperparameters for the algorithm and it will run multiple training iterations based on what it thinks is fast. That'll take a minute. And then if we go back to the BigQuery console, you can see that we now have our machine learning model in our dataset. The next thing we need to do is determine if our machine learning model is any good. We'll run another query. This time, we'll select all the fields from the model, and then we'll run ML dot evaluate. And we'll point it to the ML model in our dataset. Then from inside here, we can make a query to the actual horse's data and evaluate it based on that model. This is going to print out a variety of different metrics, but the one we're concerned about is log loss. That's the value that we're trying to minimize. You can also keep an eye on the area under the curve. It's currently at 0.5, which means our model is completely random or in other words, completely worthless. How do we make this model better? Well, in our case, we're only using the sex in the age of the horse to determine the outcome. There's a lot more data in this dataset, so let's go ahead and feed that into the machine learning model to see if we get better results. So I'm selecting a bunch of other fields like the handicap weight, penalty, barrier, and stuff like that. If we have high quality data, then these fields should increase the predictive value of the model. When we retrain and reevaluate the model, you can see the log loss now decreases to 0.29, and the area under the curve increases to 0.7. Now we have a model that we should be able to take to the racetrack to make an unlimited amount of money. Now we can make predictions on new data by using the mlpredict statement. You would use this one by creating a new table with some new data that you acquired at the racetrack. Using a similar schema, you would feed it into this machine learning model, and it would give you predictions on that new data. In this example here, I'm just randomly sampling 10 items from our existing dataset. And you can see the result of that query is a prediction for the position of the horse in that race. I'm gonna go ahead and wrap things up there. If this video helped you, please like and subscribe. And if you want access to even more content, consider becoming a pro member at Fireship I l. Thanks for watching, and I will talk to you soon. All you gotta do is bet on the winner and you'll never lose.
One
one
of
of
my
my
favorite
favorite
things
things
to
to
do
do
with
with
technology
technology
is
is
make
make
money.
money
And
and
by
by
that,
that
of
of
course,
course
I'm
i'm
talking
talking
about
about
gambling
gambling
money.
money
The
the
information
information
in
in
here
here
is
is
worth
worth
1,000,000
1,000,000
and
and
I'm
i'm
giving
giving
it
it
to
to
you.
you
That's
that's
very
very
nice.
nice
Thank
thank
you
you
very
very
much.
much
Now
now
why
why
don't
don't
you
you
make
make
like
like
a
a
tree
tree
and
and
get
get
out
out
of
of
here.
here
Several
several
months
months
ago,
ago
Google
google
released
released
a
a
machine
machine
learning
learning
pipeline
pipeline
for
for
their
their
popular
popular
data
data
warehouse,
warehouse
BigQuery.
bigquery
In
in
today's
today's
video,
video
you'll
you'll
learn
learn
about
about
SQL,
sql
Machine
machine
Learning,
learning
and
and
Cloud
cloud
Infrastructure,
infrastructure
with
with
the
the
end
end
goal
goal
of
of
building
building
a
a
predictive
predictive
model
model
that
that
will
will
help
help
you
you
determine
determine
the
the
outcome
outcome
of
of
horse
horse
races.
races
If
if
you're
you're
new
new
to
to
the
the
channel,
channel
like
like
and
and
subscribe
subscribe
and
and
you
you
can
can
find
find
a
a
full
full
walkthrough
walkthrough
on
on
Fireship
fireship
IO.
io
Now,
now
I
i
don't
don't
mean
mean
to
to
disappoint
disappoint
you,
you
but
but
you
you
probably
probably
won't
won't
get
get
rich
rich
betting
betting
on
on
horses,
horses
but
but
by
by
the
the
end
end
of
of
this
this
video,
video
you
you
will
will
know
know
how
how
to
to
upload
upload
your
your
data
data
to
to
BigQuery
bigquery
and
and
then
then
run
run
machine
machine
learning
learning
models
models
against
against
it
it
without
without
needing
needing
to
to
be
be
a
a
data
data
scientist.
scientist
And
and
the
the
model
model
we'll
we'll
build
build
will
will
not
not
predictive
predictive
value,
value
but
but
horseracing
horseracing
is
is
still
still
very
very
random,
random
so
so
use
use
this
this
at
at
your
your
own
own
risk.
risk
First
first
of
of
all,
all
what
what
is
is
BigQuery
bigquery
and
and
why
why
would
would
you
you
It
it
is
is
a
a
serverless
serverless
data
data
warehouse
warehouse
that
that
allows
allows
you
you
to
to
store
store
massive
massive
amounts
amounts
of
of
data,
data
and
and
then
then
query
query
that
that
data
data
with
with
standard
standard
SQL
sql
It
it
provides
provides
a
a
centralized
centralized
place
place
to
to
put
put
all
all
of
of
your
your
data,
data
which
which
might
might
include
include
things
things
like
like
Firestore,
firestore
Excel
excel
spreadsheets,
spreadsheets
legacy
legacy
databases,
databases
etcetera.
etcetera
Then
then
you
you
can
can
make
make
queries
queries
against
against
this
this
data
data
to
to
use
use
it
it
for
for
combined
combined
reporting,
reporting
and
and
as
as
we'll
we'll
see
see
later
later
in
in
the
the
video,
video
for
for
machine
machine
learning.
learning
Just
just
keep
keep
in
in
mind
mind
it's
it's
not
not
a
a
production
production
database,
database
so
so
you
you
wouldn't
wouldn't
use
use
it
it
in
in
your
your
front
front
end
end
applications.
applications
For
for
that,
that
you
you
would
would
use
use
something
something
like
like
Firestore
firestore
or
or
a
a
regular
regular
SQL
sql
database.
database
To
to
get
get
started,
started
you
you
just
just
need
need
to
to
have
have
a
a
Google
google
Cloud
cloud
Platform
platform
or
or
a
a
Firebase
firebase
project,
project
and
and
you
you
can
can
find
find
BigQuery
bigquery
under
under
the
the
big
big
data
data
panel,
panel
and
and
then
then
I
i
recommend
recommend
pinning
pinning
it
it
to
to
the
the
top
top
just
just
so
so
it's
it's
easy
easy
to
to
access
access
on
on
the
the
console.
console
Now
now
BigQuery
bigquery
is
is
designed
designed
to
to
scale
scale
the
the
huge
huge
amounts
amounts
of
of
data,
data
and
and
by
by
huge,
huge
I
i
mean
mean
petabytes
petabytes
of
of
data.
data
So
so
when
when
you
you
make
make
a
a
query,
query
it
it
will
will
queue
queue
that
that
up
up
as
as
a
a
job
job
in
in
the
the
background.
background
But
but
before
before
we
we
can
can
get
get
to
to
that
that
point,
point
we
we
need
need
to
to
upload
upload
some
some
data
data
to
to
BigQuery.
bigquery
There
there
are
are
many
many
different
different
ways
ways
to
to
do
do
this,
this
but
but
we're
we're
just
just
going
going
to
to
upload
upload
a
a
CSV
csv
file
file
to
to
cloud
cloud
storage
storage
and
and
then
then
connect
connect
it
it
as
as
a
a
table
table
to
to
BigQuery.
bigquery
We're
we're
going
going
to
to
grab
grab
our
our
horse
horse
racing
racing
data
data
from
from
a
a
site
site
called
called
Kaggle.
kaggle
It's
it's
a
a
platform
platform
that
that
allows
allows
companies
companies
to
to
host
host
their
their
own
own
data
data
science
science
competitions
competitions
and
and
also
also
provides
provides
a
a
large
large
number
number
of
of
free
free
datasets
datasets
that
that
we
we
can
can
use.
use
Most
most
importantly,
importantly
they
they
have
have
this
this
horses
horses
for
for
courses
courses
dataset,
dataset
which
which
contains
contains
about
about
250,000
250,000
rows
rows
of
of
horse
horse
racing
racing
samples.
samples
Go
go
ahead
ahead
and
and
download
download
the
the
horses
horses
dot
dot
CSV
csv
file
file
that
that
you
you
find
find
on
on
this
this
page.
page
Now
now
like
like
a
a
lot
lot
of
of
datasets,
datasets
it's
it's
not
not
perfect.
perfect
And
and
one
one
thing
thing
I
i
noticed
noticed
is
is
that
that
this
this
dataset
dataset
has
has
mismatched
mismatched
data
data
types,
types
which
which
causes
causes
errors
errors
when
when
it
it
goes
goes
into
into
BigQuery.
bigquery
Just
just
like
like
a
a
regular
regular
SQL
sql
database,
database
BigQuery
bigquery
has
has
a
a
strict
strict
schema,
schema
which
which
means
means
we
we
can't
can't
have
have
mismatched
mismatched
data
data
types
types
in
in
our
our
columns.
columns
We
we
could
could
fix
fix
this
this
by
by
writing
writing
some
some
Python
python
code,
code
or
or
we
we
could
could
just
just
open
open
it
it
up
up
in
in
cell
cell
and
and
format
format
the
the
number
number
columns
columns
as
as
numbers.
numbers
Now
now
that
that
our
our
data
data
is
is
properly
properly
formatted,
formatted
the
the
next
next
step
step
is
is
to
to
upload
upload
it
it
to
to
a
a
cloud
cloud
storage
storage
bucket.
bucket
Go
go
to
to
the
the
storage
storage
tab
tab
on
on
GCP,
gcp
and
and
then
then
you
you
can
can
create
create
a
a
new
new
bucket
bucket
or
or
use
use
an
an
existing
existing
bucket
bucket
if
if
you
you
have
have
one.
one
Then
then
we'll
we'll
upload
upload
the
the
horse's
horse's
CSV
csv
file,
file
which
which
is
is
about
about
a
a
180
180
megabytes.
megabytes
If
if
you
you
have
have
a
a
file
file
that's
that's
less
less
than
than
10
10
megabytes,
megabytes
you
you
can
can
skip
skip
this
this
step
step
and
and
just
just
upload
upload
it
it
directly
directly
to
to
BigQuery.
bigquery
Now
now
getting
getting
back
back
to
to
BigQuery,
bigquery
the
the
first
first
thing
thing
we'll
we'll
do
do
is
is
click
click
on
on
the
the
add
add
data
data
button
button
and
and
then
then
create
create
new
new
dataset.
dataset
Give
give
your
your
dataset
dataset
a
a
name
name
and
and
then
then
you
you
can
can
just
just
select
select
the
the
default
default
options
options
here.
here
A
a
dataset
dataset
is
is
just
just
a
a
collection
collection
of
of
tables
tables
which
which
again
again
you
you
can
can
just
just
think
think
of
of
as
as
SQL
sql
tables.
tables
Go
go
ahead
ahead
and
and
click
click
the
the
create
create
table
table
button,
button
and
and
we'll
we'll
connect
connect
the
the
file
file
that
that
we
we
just
just
uploaded
uploaded
to
to
the
the
cloud
cloud
storage
storage
bucket.
bucket
Now
now
normally
normally
with
with
SQL,
sql
you
you
would
would
have
have
to
to
define
define
your
your
schema
schema
manually,
manually
which,
which
of
of
course,
course
we
we
don't
don't
have
have
for
for
this
this
dataset.
dataset
There's
there's
at
at
least
least
50
50
different
different
features
features
or
or
columns
columns
on
on
that
that
horse's
horse's
spreadsheet,
spreadsheet
and
and
it
it
would
would
really
really
suck
suck
if
if
we
we
had
had
to
to
go
go
through
through
and
and
define
define
that
that
schema
schema
manually.
manually
Luckily,
luckily
BigQuery
bigquery
has
has
this
this
thing
thing
called
called
schema
schema
auto
auto
detect,
detect
which
which
will
will
randomly
randomly
sample
sample
some
some
of
of
the
the
rows
rows
and
and
try
try
to
to
infer
infer
their
their
data
data
type.
type
So
so
make
make
sure
sure
you
you
have
have
that
that
selected.
selected
And
and
we'll
we'll
wanna
wanna
make
make
sure
sure
that
that
it
it
skips
skips
the
the
header
header
row,
row
and
and
we
we
can
can
do
do
that
that
by
by
setting
setting
the
the
skip
skip
header
header
rows
rows
to
to
1.
1
After
after
that,
that
you
you
can
can
click
click
create
create
table
table
and
and
that
that
will
will
queue
queue
up
up
a
a
job
job
in
in
the
the
background
background
to
to
create
create
this
this
table.
table
You
you
can
can
monitor
monitor
the
the
progress
progress
of
of
the
the
job,
job
which
which
should
should
only
only
take
take
a
a
couple
couple
of
of
seconds
seconds
in
in
this
this
case,
case
and
and
it
it
will
will
also
also
report
report
any
any
errors
errors
if
if
it
it
fails
fails
to
to
upload
upload
the
the
table.
table
If
if
everything
everything
worked,
worked
you
you
should
should
have
have
a
a
table
table
that
that
looks
looks
like
like
this
this
where
where
the
the
field
field
names
names
match
match
the
the
header
header
row
row
of
of
the
the
CSV
csv
file.
file
You
you
can
can
look
look
at
at
the
the
schema
schema
and
and
you
you
can
can
also
also
preview
preview
the
the
actual
actual
values
values
in
in
the
the
table
table
here
here
as
as
well.
well
The
the
The
the
next
next
thing
thing
we'll
we'll
do
do
is
is
make
make
an
an
actual
actual
SQL
sql
query
query
against
against
this
this
dataset.
dataset
We
we
can
can
do
do
this
this
directly
directly
in
in
the
the
console,
console
but
but
keep
keep
in
in
mind
mind
you
you
could
could
also
also
do
do
this
this
from
from
the
the
command
command
line
line
or
or
with
with
one
one
of
of
the
the
client
client
libraries.
libraries
First,
first
we'll
we'll
click
click
on
on
create
create
table
table
to
to
set
set
up
up
some
some
of
of
the
the
boilerplate
boilerplate
code,
code
and
and
we'll
we'll
start
start
the
the
query
query
with
with
a
a
select
select
statement,
statement
which
which
will
will
select
select
specific
specific
fields
fields
from
from
the
the
table.
table
In
in
this
this
particular
particular
dataset,
dataset
it
it
is
is
the
the
position
position
value
value
that
that
we're
we're
trying
trying
to
to
predict.
predict
That
that
is
is
the
the
result
result
that
that
a
a
horse
horse
took
took
in
in
a
a
given
given
race,
race
so
so
0
0
would
would
be
be
a
a
scratch
scratch
or
or
a
a
non
non
place.
place
First
first
would
would
be
be
the
the
winner,
winner
second
second
would
would
be
be
the
the
runner-up,
runner-up
and
and
so
so
on.
on
We'll
we'll
also
also
select
select
the
the
horse's
horse's
sex
sex
and
and
age
age
from
from
this
this
table
table
so
so
we
we
can
can
see
see
if
if
that
that
has
has
any
any
relationship
relationship
to
to
the
the
position.
position
We
we
also
also
wanna
wanna
make
make
sure
sure
that
that
sex
sex
is
is
not
not
null,
null
of
of
course,
course
and
and
we'll
we'll
limit
limit
it
it
to
to
10,000
10,000
records
records
just
just
to
to
make
make
it
it
easier
easier
to
to
work
work
with
with
in
in
Data
data
Studio.
studio
We
we
can
can
go
go
ahead
ahead
and
and
run
run
this
this
query,
query
and
and
it
it
will
will
run
run
a
a
job
job
in
in
the
the
background.
background
That
that
will
will
provide
provide
us
us
with
with
a
a
new
new
table
table
with
with
the
the
queried
queried
results.
results
Now
now
there's
there's
a
a
lot
lot
more
more
we
we
round.
round
That
that
will
will
provide
provide
us
us
with
with
a
a
new
new
table
table
with
with
the
the
query
query
results.
results
Now
now
there's
there's
a
a
lot
lot
more
more
we
we
could
could
get
get
into
into
with
with
SQL
sql
queries,
queries
like
like
joining
joining
multiple
multiple
tables
tables
together
together
and
and
things
things
like
like
that.
that
But
but
when
when
it
it
comes
comes
to
to
machine
machine
learning,
learning
the
the
first
first
thing
thing
you
you
generally
generally
want
want
to
to
do
do
is
is
explore
explore
your
your
data.
data
And
and
BigQuery
bigquery
makes
makes
this
this
really
really
easy
easy
because
because
it's
it's
integrated
integrated
with
with
another
another
service
service
called
called
Data
data
Studio.
studio
By
by
clicking
clicking
on
on
export,
export
we
we
can
can
transfer
transfer
this
this
data
data
to
to
Data
data
Studio
studio
and
and
do
do
a
a
visual
visual
analysis
analysis
on
on
it.
it
Basically,
basically
we're
we're
taking
taking
the
the
query
query
that
that
we
we
just
just
created
created
and
and
visualizing
visualizing
it.
it
Everything
everything
in
in
Data
data
Studio
studio
is
is
just
just
point
point
and
and
click
click
or
or
drag
drag
and
and
drop.
drop
And
and
what
what
I'm
i'm
doing
doing
here
here
is
is
creating
creating
a
a
bar
bar
chart
chart
to
to
see
see
if
if
the
the
sex
sex
of
of
the
the
horse
horse
has
has
any
any
relationship
relationship
to
to
its
its
position
position
in
in
the
the
races.
races
And
and
what
what
we
we
can
can
see
see
here
here
is
is
that
that
Colts
colts
and
and
Phillies
phillies
have
have
a
a
higher
higher
chance
chance
of
of
positioning
positioning
well
well
in
in
a
a
race.
race
That
that
means
means
this
this
field
field
is
is
probably
probably
a
a
useful
useful
feature
feature
to
to
include
include
in
in
our
our
machine
machine
learning
learning
model.
model
Now
now
we
we
could
could
train
train
our
our
model
model
directly
directly
from
from
the
the
BigQuery
bigquery
console,
console
but
but
I
i
wanna
wanna
show
show
you
you
a
a
really
really
cool
cool
tool
tool
called
called
Datalab.
datalab
When
when
I
i
work
work
on
on
machine
machine
learning
learning
problems,
problems
I
i
like
like
to
to
use
use
Python
python
Notebooks.
notebooks
And
and
Datalab
datalab
basically
basically
gives
gives
you
you
a
a
Python
python
notebook
notebook
with
with
unlimited
unlimited
computing
computing
resources.
resources
To
to
set
set
up
up
a
a
Datalab,
datalab
the
the
first
first
thing
thing
we'll
we'll
do
do
is
is
activate
activate
a
a
shell
shell
session,
session
then
then
run
run
Datalab
datalab
create
create
followed
followed
by
by
whatever
whatever
name
name
you
you
wanna
wanna
give
give
it.
it
Then
then
it's
it's
going
going
to
to
ask
ask
you
you
to
to
select
select
a
a
region,
region
then
then
it
it
will
will
take
take
you
you
through
through
the
the
steps
steps
to
to
set
set
up
up
an
an
SSH
ssh
tunnel.
tunnel
And
and
that's
that's
just
just
a
a
way
way
for
for
you
you
to
to
access
access
a
a
Python
python
notebook
notebook
that's
that's
connected
connected
to
to
your
your
cloud
cloud
resources
resources
in
in
the
the
browser.
browser
That'll
that'll
take
take
a
a
few
few
minutes
minutes
to
to
set
set
up,
up
But
but
once
once
it's
it's
complete,
complete
you
you
can
can
go
go
to
to
the
the
Compute
compute
Engine
engine
tab,
tab
and
and
you'll
you'll
be
be
able
able
to
to
see
see
the
the
actual
actual
virtual
virtual
machine
machine
that
that
it
it
created.
created
Keep
keep
in
in
mind
mind
that
that
virtual
virtual
machines
machines
accrue
accrue
cost,
cost
so
so
you'll
you'll
wanna
wanna
shut
shut
this
this
down
down
after
after
you're
you're
done
done
with
with
this
this
tutorial.
tutorial
Once
once
it's
it's
done,
done
go
go
ahead
ahead
and
and
click
click
on
on
the
the
web
web
preview
preview
button
button
and
and
then
then
select
select
change
change
port.
port
It
it
runs
runs
on
on
port
port
8,801,
8,801
so
so
go
go
ahead
ahead
and
and
click
click
change
change
in
in
preview
preview
and
and
you
you
should
should
be
be
good
good
to
to
go.
go
Now
now
you
you
have
have
this
this
playground
playground
for
for
all
all
your
your
data
data
analysis
analysis
and
and
processing
processing
needs,
needs
and
and
it's
it's
already
already
wired
wired
up
up
with
with
your
your
data
data
from
from
BigQuery.
bigquery
And
and
even
even
though
though
we're
we're
in
in
a
a
Python
python
environment,
environment
we're
we're
only
only
going
going
to
to
be
be
writing
writing
a
a
tiny
tiny
bit
bit
of
of
Python
python
code.
code
The
the
cool
cool
thing
thing
about
about
notebooks
notebooks
is
is
that
that
you
you
can
can
run
run
code
code
on
on
a
a
cell
cell
by
by
cell
cell
basis.
basis
If
if
you're
you're
not
not
already
already
familiar
familiar
with
with
them,
them
I
i
recommend
recommend
checking
checking
out
out
some
some
of
of
the
the
examples
examples
and
and
running
running
the
the
code
code
just
just
to
to
get
get
a
a
feel
feel
for
for
how
how
it
it
works.
works
Now
now
I'm
i'm
starting
starting
from
from
a
a
new
new
notebook,
notebook
And
and
I
i
first
first
wanna
wanna
make
make
sure
sure
that
that
we
we
have
have
access
access
to
to
our
our
BigQuery
bigquery
table.
table
We
we
use
use
the
the
b
b
q
q
command
command
and
and
call
call
tables
tables
list,
list
and
and
that
that
should
should
list
list
out
out
the
the
table
table
that
that
you
you
have
have
in
in
BigQuery.
bigquery
And
and
you
you
can
can
also
also
use
use
the
the
help
help
command
command
just
just
to
to
list
list
out
out
all
all
of
of
the
the
commands
commands
available
available
on
on
the
the
b
b
q
q
namespace.
namespace
Now
now
that
that
we
we
know
know
the
the
name
name
of
of
our
our
table,
table
let's
let's
go
go
ahead
ahead
and
and
make
make
a
a
query
query
against
against
it
it
in
in
this
this
Python
python
notebook.
notebook
We
we
use
use
the
the
b
b
q
q
query
query
command,
command
and
and
then
then
we
we
can
can
just
just
write
write
a
a
standard
standard
SQL
sql
statement.
statement
We
we
can
can
go
go
ahead
ahead
and
and
click
click
play,
play
then
then
it
it
should
should
take
take
a
a
second
second
to
to
run
run
and
and
then
then
output
output
the
the
result
result
of
of
that
that
query
query
in
in
the
the
notebook.
notebook
And
and
we
we
finally
finally
reached
reached
the
the
point
point
where
where
BigQuery
bigquery
ML
ml
comes
comes
into
into
play.
play
We're
we're
going
going
to
to
write
write
a
a
special
special
SQL
sql
statement
statement
that
that
makes
makes
a
a
query
query
while
while
at
at
the
the
same
same
time
time
creating
creating
a
a
machine
machine
learning
learning
model
model
based
based
on
on
that
that
data.
data
We
we
use
use
the
the
create
create
or
or
replace
replace
model
model
statement
statement
and
and
then
then
give
give
it
it
a
a
name
name
of
of
ML
ml
model.
model
And
and
it
it
can
can
live
live
in
in
the
the
same
same
dataset
dataset
as
as
our
our
horses
horses
table.
table
From
from
here,
here
we
we
can
can
pass
pass
in
in
a
a
variety
variety
of
of
different
different
options.
options
And
and
first,
first
we'll
we'll
set
set
the
the
model
model
type
type
to
to
logistic
logistic
regression.
regression
In
in
machine
machine
learning,
learning
this
this
is
is
a
a
classification
classification
problem
problem
because
because
we're
we're
trying
trying
to
to
predict
predict
a
a
specific
specific
label,
label
which
which
is
is
either
either
1st
1st
place,
place
2nd
2nd
place,
place
3rd,
3rd
and
and
so
so
one.
one
The
the
other
other
option
option
is
is
Linear
linear
Regression,
regression
which
which
you
you
would
would
use
use
if
if
you
you
have
have
a
a
continuous
continuous
value.
value
For
for
example,
example
the
the
measurement
measurement
of
of
length
length
which
which
could
could
have
have
any
any
infinite
infinite
number
number
of
of
values
values
depending
depending
on
on
how
how
precise
precise
you
you
go.
go
And
and
confusingly
confusingly
because
because
we
we
have
have
a
a
classification
classification
problem,
problem
we
we
use
use
the
the
logistic
logistic
regression
regression
model
model
type.
type
The
the
next
next
thing
thing
we
we
need
need
to
to
do
do
is
is
tell
tell
it
it
what
what
our
our
input
input
label
label
is
is
or,
or
in
in
other
other
words,
words
the
the
thing
thing
we're
we're
trying
trying
to
to
predict
predict
which
which
is
is
the
the
position
position
field.
field
Once
once
we
we
have
have
the
the
options
options
defined,
defined
we
we
can
can
just
just
make
make
a
a
query
query
to
to
our
our
table
table
using
using
select
select
and
and
from.
from
And
and
BigQuery
bigquery
will
will
automatically
automatically
do
do
all
all
of
of
the
the
machine
machine
learning
learning
stuff
stuff
under
under
the
the
hood,
hood
like
like
separate
separate
your
your
data
data
into
into
a
a
training
training
and
and
validation
validation
set,
set
it
it
will
will
choose
choose
the
the
hyperparameters
hyperparameters
for
for
the
the
algorithm
algorithm
and
and
it
it
will
will
run
run
multiple
multiple
training
training
iterations
iterations
based
based
on
on
what
what
it
it
thinks
thinks
is
is
fast.
fast
That'll
that'll
take
take
a
a
minute.
minute
And
and
then
then
if
if
we
we
go
go
back
back
to
to
the
the
BigQuery
bigquery
console,
console
you
you
can
can
see
see
that
that
we
we
now
now
have
have
our
our
machine
machine
learning
learning
model
model
in
in
our
our
dataset.
dataset
The
the
next
next
thing
thing
we
we
need
need
to
to
do
do
is
is
determine
determine
if
if
our
our
machine
machine
learning
learning
model
model
is
is
any
any
good.
good
We'll
we'll
run
run
another
another
query.
query
This
this
time,
time
we'll
we'll
select
select
all
all
the
the
fields
fields
from
from
the
the
model,
model
and
and
then
then
we'll
we'll
run
run
ML
ml
dot
dot
evaluate.
evaluate
And
and
we'll
we'll
point
point
it
it
to
to
the
the
ML
ml
model
model
in
in
our
our
dataset.
dataset
Then
then
from
from
inside
inside
here,
here
we
we
can
can
make
make
a
a
query
query
to
to
the
the
actual
actual
horse's
horse's
data
data
and
and
evaluate
evaluate
it
it
based
based
on
on
that
that
model.
model
This
this
is
is
going
going
to
to
print
print
out
out
a
a
variety
variety
of
of
different
different
metrics,
metrics
but
but
the
the
one
one
we're
we're
concerned
concerned
about
about
is
is
log
log
loss.
loss
That's
that's
the
the
value
value
that
that
we're
we're
trying
trying
to
to
minimize.
minimize
You
you
can
can
also
also
keep
keep
an
an
eye
eye
on
on
the
the
area
area
under
under
the
the
curve.
curve
It's
it's
currently
currently
at
at
0.5,
0.5
which
which
means
means
our
our
model
model
is
is
completely
completely
random
random
or
or
in
in
other
other
words,
words
completely
completely
worthless.
worthless
How
how
do
do
we
we
make
make
this
this
model
model
better?
better
Well,
well
in
in
our
our
case,
case
we're
we're
only
only
using
using
the
the
sex
sex
in
in
the
the
age
age
of
of
the
the
horse
horse
to
to
determine
determine
the
the
outcome.
outcome
There's
there's
a
a
lot
lot
more
more
data
data
in
in
this
this
dataset,
dataset
so
so
let's
let's
go
go
ahead
ahead
and
and
feed
feed
that
that
into
into
the
the
machine
machine
learning
learning
model
model
to
to
see
see
if
if
we
we
get
get
better
better
results.
results
So
so
I'm
i'm
selecting
selecting
a
a
bunch
bunch
of
of
other
other
fields
fields
like
like
the
the
handicap
handicap
weight,
weight
penalty,
penalty
barrier,
barrier
and
and
stuff
stuff
like
like
that.
that
If
if
we
we
have
have
high
high
quality
quality
data,
data
then
then
these
these
fields
fields
should
should
increase
increase
the
the
predictive
predictive
value
value
of
of
the
the
model.
model
When
when
we
we
retrain
retrain
and
and
reevaluate
reevaluate
the
the
model,
model
you
you
can
can
see
see
the
the
log
log
loss
loss
now
now
decreases
decreases
to
to
0.29,
0.29
and
and
the
the
area
area
under
under
the
the
curve
curve
increases
increases
to
to
0.7.
0.7
Now
now
we
we
have
have
a
a
model
model
that
that
we
we
should
should
be
be
able
able
to
to
take
take
to
to
the
the
racetrack
racetrack
to
to
make
make
an
an
unlimited
unlimited
amount
amount
of
of
money.
money
Now
now
we
we
can
can
make
make
predictions
predictions
on
on
new
new
data
data
by
by
using
using
the
the
mlpredict
mlpredict
statement.
statement
You
you
would
would
use
use
this
this
one
one
by
by
creating
creating
a
a
new
new
table
table
with
with
some
some
new
new
data
data
that
that
you
you
acquired
acquired
at
at
the
the
racetrack.
racetrack
Using
using
a
a
similar
similar
schema,
schema
you
you
would
would
feed
feed
it
it
into
into
this
this
machine
machine
learning
learning
model,
model
and
and
it
it
would
would
give
give
you
you
predictions
predictions
on
on
that
that
new
new
data.
data
In
in
this
this
example
example
here,
here
I'm
i'm
just
just
randomly
randomly
sampling
sampling
10
10
items
items
from
from
our
our
existing
existing
dataset.
dataset
And
and
you
you
can
can
see
see
the
the
result
result
of
of
that
that
query
query
is
is
a
a
prediction
prediction
for
for
the
the
position
position
of
of
the
the
horse
horse
in
in
that
that
race.
race
I'm
i'm
gonna
gonna
go
go
ahead
ahead
and
and
wrap
wrap
things
things
up
up
there.
there
If
if
this
this
video
video
helped
helped
you,
you
please
please
like
like
and
and
subscribe.
subscribe
And
and
if
if
you
you
want
want
access
access
to
to
even
even
more
more
content,
content
consider
consider
becoming
becoming
a
a
pro
pro
member
member
at
at
Fireship
fireship
I
i
l.
l
Thanks
thanks
for
for
watching,
watching
and
and
I
i
will
will
talk
talk
to
to
you
you
soon.
soon
All
all
you
you
gotta
gotta
do
do
is
is
bet
bet
on
on
the
the
winner
winner
and
and
you'll
you'll
never
never
lose.
lose
success
In this conversation, the speakers discuss the benefits of BigQuery for data warehouses and recommend cloud storage files for datasets. They also explain how to create a new dataset and run a query against it, creating a Python notebook and running a SQL statement to create a machine learning model based on the data. The speakers also explain how to use the machine learning model to make predictions on new data and suggest becoming a pro member at Fireship I l.
Predict Horse Races with BigQuery #ML
One of my favorite things to do with technology is make money. And by that, of course, I'm talking about gambling money. The information in here is worth 1,000,000 and I'm giving it to you. That's very nice. Thank you very much. Now why don't you make like a tree and get out of here. Several months ago, Google released a machine learning pipeline for their popular data warehouse, BigQuery. In today's video, you'll learn about SQL, Machine Learning, and Cloud Infrastructure, with the end goal of building a predictive model that will help you determine the outcome of horse races. If you're new to the channel, like and subscribe and you can find a full walkthrough on Fireship IO. Now, I don't mean to disappoint you, but you probably won't get rich betting on horses, but by the end of this video, you will know how to upload your data to BigQuery and then run machine learning models against it without needing to be a data scientist. And the model we'll build will not predictive value, but horseracing is still very random, so use this at your own risk. First of all, what is BigQuery and why would you It is a serverless data warehouse that allows you to store massive amounts of data, and then query that data with standard SQL It provides a centralized place to put all of your data, which might include things like Firestore, Excel spreadsheets, legacy databases, etcetera. Then you can make queries against this data to use it for combined reporting, and as we'll see later in the video, for machine learning. Just keep in mind it's not a production database, so you wouldn't use it in your front end applications. For that, you would use something like Firestore or a regular SQL database. To get started, you just need to have a Google Cloud Platform or a Firebase project, and you can find BigQuery under the big data panel, and then I recommend pinning it to the top just so it's easy to access on the console. Now BigQuery is designed to scale the huge amounts of data, and by huge, I mean petabytes of data. So when you make a query, it will queue that up as a job in the background. But before we can get to that point, we need to upload some data to BigQuery. There are many different ways to do this, but we're just going to upload a CSV file to cloud storage and then connect it as a table to BigQuery. We're going to grab our horse racing data from a site called Kaggle. It's a platform that allows companies to host their own data science competitions and also provides a large number of free datasets that we can use. Most importantly, they have this horses for courses dataset, which contains about 250,000 rows of horse racing samples. Go ahead and download the horses dot CSV file that you find on this page. Now like a lot of datasets, it's not perfect. And one thing I noticed is that this dataset has mismatched data types, which causes errors when it goes into BigQuery. Just like a regular SQL database, BigQuery has a strict schema, which means we can't have mismatched data types in our columns. We could fix this by writing some Python code, or we could just open it up in cell and format the number columns as numbers. Now that our data is properly formatted, the next step is to upload it to a cloud storage bucket. Go to the storage tab on GCP, and then you can create a new bucket or use an existing bucket if you have one. Then we'll upload the horse's CSV file, which is about a 180 megabytes. If you have a file that's less than 10 megabytes, you can skip this step and just upload it directly to BigQuery. Now getting back to BigQuery, the first thing we'll do is click on the add data button and then create new dataset. Give your dataset a name and then you can just select the default options here. A dataset is just a collection of tables which again you can just think of as SQL tables. Go ahead and click the create table button, and we'll connect the file that we just uploaded to the cloud storage bucket. Now normally with SQL, you would have to define your schema manually, which, of course, we don't have for this dataset. There's at least 50 different features or columns on that horse's spreadsheet, and it would really suck if we had to go through and define that schema manually. Luckily, BigQuery has this thing called schema auto detect, which will randomly sample some of the rows and try to infer their data type. So make sure you have that selected. And we'll wanna make sure that it skips the header row, and we can do that by setting the skip header rows to 1. After that, you can click create table and that will queue up a job in the background to create this table. You can monitor the progress of the job, which should only take a couple of seconds in this case, and it will also report any errors if it fails to upload the table. If everything worked, you should have a table that looks like this where the field names match the header row of the CSV file. You can look at the schema and you can also preview the actual values in the table here as well. The The next thing we'll do is make an actual SQL query against this dataset. We can do this directly in the console, but keep in mind you could also do this from the command line or with one of the client libraries. First, we'll click on create table to set up some of the boilerplate code, and we'll start the query with a select statement, which will select specific fields from the table. In this particular dataset, it is the position value that we're trying to predict. That is the result that a horse took in a given race, so 0 would be a scratch or a non place. First would be the winner, second would be the runner-up, and so on. We'll also select the horse's sex and age from this table so we can see if that has any relationship to the position. We also wanna make sure that sex is not null, of course, and we'll limit it to 10,000 records just to make it easier to work with in Data Studio. We can go ahead and run this query, and it will run a job in the background. That will provide us with a new table with the queried results. Now there's a lot more we round. That will provide us with a new table with the query results. Now there's a lot more we could get into with SQL queries, like joining multiple tables together and things like that. But when it comes to machine learning, the first thing you generally want to do is explore your data. And BigQuery makes this really easy because it's integrated with another service called Data Studio. By clicking on export, we can transfer this data to Data Studio and do a visual analysis on it. Basically, we're taking the query that we just created and visualizing it. Everything in Data Studio is just point and click or drag and drop. And what I'm doing here is creating a bar chart to see if the sex of the horse has any relationship to its position in the races. And what we can see here is that Colts and Phillies have a higher chance of positioning well in a race. That means this field is probably a useful feature to include in our machine learning model. Now we could train our model directly from the BigQuery console, but I wanna show you a really cool tool called Datalab. When I work on machine learning problems, I like to use Python Notebooks. And Datalab basically gives you a Python notebook with unlimited computing resources. To set up a Datalab, the first thing we'll do is activate a shell session, then run Datalab create followed by whatever name you wanna give it. Then it's going to ask you to select a region, then it will take you through the steps to set up an SSH tunnel. And that's just a way for you to access a Python notebook that's connected to your cloud resources in the browser. That'll take a few minutes to set up, But once it's complete, you can go to the Compute Engine tab, and you'll be able to see the actual virtual machine that it created. Keep in mind that virtual machines accrue cost, so you'll wanna shut this down after you're done with this tutorial. Once it's done, go ahead and click on the web preview button and then select change port. It runs on port 8,801, so go ahead and click change in preview and you should be good to go. Now you have this playground for all your data analysis and processing needs, and it's already wired up with your data from BigQuery. And even though we're in a Python environment, we're only going to be writing a tiny bit of Python code. The cool thing about notebooks is that you can run code on a cell by cell basis. If you're not already familiar with them, I recommend checking out some of the examples and running the code just to get a feel for how it works. Now I'm starting from a new notebook, And I first wanna make sure that we have access to our BigQuery table. We use the b q command and call tables list, and that should list out the table that you have in BigQuery. And you can also use the help command just to list out all of the commands available on the b q namespace. Now that we know the name of our table, let's go ahead and make a query against it in this Python notebook. We use the b q query command, and then we can just write a standard SQL statement. We can go ahead and click play, then it should take a second to run and then output the result of that query in the notebook. And we finally reached the point where BigQuery ML comes into play. We're going to write a special SQL statement that makes a query while at the same time creating a machine learning model based on that data. We use the create or replace model statement and then give it a name of ML model. And it can live in the same dataset as our horses table. From here, we can pass in a variety of different options. And first, we'll set the model type to logistic regression. In machine learning, this is a classification problem because we're trying to predict a specific label, which is either 1st place, 2nd place, 3rd, and so one. The other option is Linear Regression, which you would use if you have a continuous value. For example, the measurement of length which could have any infinite number of values depending on how precise you go. And confusingly because we have a classification problem, we use the logistic regression model type. The next thing we need to do is tell it what our input label is or, in other words, the thing we're trying to predict which is the position field. Once we have the options defined, we can just make a query to our table using select and from. And BigQuery will automatically do all of the machine learning stuff under the hood, like separate your data into a training and validation set, it will choose the hyperparameters for the algorithm and it will run multiple training iterations based on what it thinks is fast. That'll take a minute. And then if we go back to the BigQuery console, you can see that we now have our machine learning model in our dataset. The next thing we need to do is determine if our machine learning model is any good. We'll run another query. This time, we'll select all the fields from the model, and then we'll run ML dot evaluate. And we'll point it to the ML model in our dataset. Then from inside here, we can make a query to the actual horse's data and evaluate it based on that model. This is going to print out a variety of different metrics, but the one we're concerned about is log loss. That's the value that we're trying to minimize. You can also keep an eye on the area under the curve. It's currently at 0.5, which means our model is completely random or in other words, completely worthless. How do we make this model better? Well, in our case, we're only using the sex in the age of the horse to determine the outcome. There's a lot more data in this dataset, so let's go ahead and feed that into the machine learning model to see if we get better results. So I'm selecting a bunch of other fields like the handicap weight, penalty, barrier, and stuff like that. If we have high quality data, then these fields should increase the predictive value of the model. When we retrain and reevaluate the model, you can see the log loss now decreases to 0.29, and the area under the curve increases to 0.7. Now we have a model that we should be able to take to the racetrack to make an unlimited amount of money. Now we can make predictions on new data by using the mlpredict statement. You would use this one by creating a new table with some new data that you acquired at the racetrack. Using a similar schema, you would feed it into this machine learning model, and it would give you predictions on that new data. In this example here, I'm just randomly sampling 10 items from our existing dataset. And you can see the result of that query is a prediction for the position of the horse in that race. I'm gonna go ahead and wrap things up there. If this video helped you, please like and subscribe. And if you want access to even more content, consider becoming a pro member at Fireship I l. Thanks for watching, and I will talk to you soon. All you gotta do is bet on the winner and you'll never lose.
In this conversation, the speakers discuss the benefits of BigQuery for data warehouses and recommend cloud storage files for datasets. They also explain how to create a new dataset and run a query against it, creating a Python notebook and running a SQL statement to create a machine learning model based on the data. The speakers also explain how to use the machine learning model to make predictions on new data and suggest becoming a pro member at Fireship I l.

        [INST]
        You are youtuber called Fireship you make engaging high-intensity and entertaining coding tutorials and tech news. 
        you covers a wide range of topics relevant to programmers, aiming to help them learn and improve their skills quickly.
        
        Given the title of the video : Predict Horse Races with BigQuery #ML 
        and a small summary : In this conversation, the speakers discuss the benefits of BigQuery for data warehouses and recommend cloud storage files for datasets. They also explain how to create a new dataset and run a query against it, creating a Python notebook and running a SQL statement to create a machine learning model based on the data. The speakers also explain how to use the machine learning model to make predictions on new data and suggest becoming a pro member at Fireship I l.
        [/INST]
        
        Generate the video : One of my favorite things to do with technology is make money. And by that, of course, I'm talking about gambling money. The information in here is worth 1,000,000 and I'm giving it to you. That's very nice. Thank you very much. Now why don't you make like a tree and get out of here. Several months ago, Google released a machine learning pipeline for their popular data warehouse, BigQuery. In today's video, you'll learn about SQL, Machine Learning, and Cloud Infrastructure, with the end goal of building a predictive model that will help you determine the outcome of horse races. If you're new to the channel, like and subscribe and you can find a full walkthrough on Fireship IO. Now, I don't mean to disappoint you, but you probably won't get rich betting on horses, but by the end of this video, you will know how to upload your data to BigQuery and then run machine learning models against it without needing to be a data scientist. And the model we'll build will not predictive value, but horseracing is still very random, so use this at your own risk. First of all, what is BigQuery and why would you It is a serverless data warehouse that allows you to store massive amounts of data, and then query that data with standard SQL It provides a centralized place to put all of your data, which might include things like Firestore, Excel spreadsheets, legacy databases, etcetera. Then you can make queries against this data to use it for combined reporting, and as we'll see later in the video, for machine learning. Just keep in mind it's not a production database, so you wouldn't use it in your front end applications. For that, you would use something like Firestore or a regular SQL database. To get started, you just need to have a Google Cloud Platform or a Firebase project, and you can find BigQuery under the big data panel, and then I recommend pinning it to the top just so it's easy to access on the console. Now BigQuery is designed to scale the huge amounts of data, and by huge, I mean petabytes of data. So when you make a query, it will queue that up as a job in the background. But before we can get to that point, we need to upload some data to BigQuery. There are many different ways to do this, but we're just going to upload a CSV file to cloud storage and then connect it as a table to BigQuery. We're going to grab our horse racing data from a site called Kaggle. It's a platform that allows companies to host their own data science competitions and also provides a large number of free datasets that we can use. Most importantly, they have this horses for courses dataset, which contains about 250,000 rows of horse racing samples. Go ahead and download the horses dot CSV file that you find on this page. Now like a lot of datasets, it's not perfect. And one thing I noticed is that this dataset has mismatched data types, which causes errors when it goes into BigQuery. Just like a regular SQL database, BigQuery has a strict schema, which means we can't have mismatched data types in our columns. We could fix this by writing some Python code, or we could just open it up in cell and format the number columns as numbers. Now that our data is properly formatted, the next step is to upload it to a cloud storage bucket. Go to the storage tab on GCP, and then you can create a new bucket or use an existing bucket if you have one. Then we'll upload the horse's CSV file, which is about a 180 megabytes. If you have a file that's less than 10 megabytes, you can skip this step and just upload it directly to BigQuery. Now getting back to BigQuery, the first thing we'll do is click on the add data button and then create new dataset. Give your dataset a name and then you can just select the default options here. A dataset is just a collection of tables which again you can just think of as SQL tables. Go ahead and click the create table button, and we'll connect the file that we just uploaded to the cloud storage bucket. Now normally with SQL, you would have to define your schema manually, which, of course, we don't have for this dataset. There's at least 50 different features or columns on that horse's spreadsheet, and it would really suck if we had to go through and define that schema manually. Luckily, BigQuery has this thing called schema auto detect, which will randomly sample some of the rows and try to infer their data type. So make sure you have that selected. And we'll wanna make sure that it skips the header row, and we can do that by setting the skip header rows to 1. After that, you can click create table and that will queue up a job in the background to create this table. You can monitor the progress of the job, which should only take a couple of seconds in this case, and it will also report any errors if it fails to upload the table. If everything worked, you should have a table that looks like this where the field names match the header row of the CSV file. You can look at the schema and you can also preview the actual values in the table here as well. The The next thing we'll do is make an actual SQL query against this dataset. We can do this directly in the console, but keep in mind you could also do this from the command line or with one of the client libraries. First, we'll click on create table to set up some of the boilerplate code, and we'll start the query with a select statement, which will select specific fields from the table. In this particular dataset, it is the position value that we're trying to predict. That is the result that a horse took in a given race, so 0 would be a scratch or a non place. First would be the winner, second would be the runner-up, and so on. We'll also select the horse's sex and age from this table so we can see if that has any relationship to the position. We also wanna make sure that sex is not null, of course, and we'll limit it to 10,000 records just to make it easier to work with in Data Studio. We can go ahead and run this query, and it will run a job in the background. That will provide us with a new table with the queried results. Now there's a lot more we round. That will provide us with a new table with the query results. Now there's a lot more we could get into with SQL queries, like joining multiple tables together and things like that. But when it comes to machine learning, the first thing you generally want to do is explore your data. And BigQuery makes this really easy because it's integrated with another service called Data Studio. By clicking on export, we can transfer this data to Data Studio and do a visual analysis on it. Basically, we're taking the query that we just created and visualizing it. Everything in Data Studio is just point and click or drag and drop. And what I'm doing here is creating a bar chart to see if the sex of the horse has any relationship to its position in the races. And what we can see here is that Colts and Phillies have a higher chance of positioning well in a race. That means this field is probably a useful feature to include in our machine learning model. Now we could train our model directly from the BigQuery console, but I wanna show you a really cool tool called Datalab. When I work on machine learning problems, I like to use Python Notebooks. And Datalab basically gives you a Python notebook with unlimited computing resources. To set up a Datalab, the first thing we'll do is activate a shell session, then run Datalab create followed by whatever name you wanna give it. Then it's going to ask you to select a region, then it will take you through the steps to set up an SSH tunnel. And that's just a way for you to access a Python notebook that's connected to your cloud resources in the browser. That'll take a few minutes to set up, But once it's complete, you can go to the Compute Engine tab, and you'll be able to see the actual virtual machine that it created. Keep in mind that virtual machines accrue cost, so you'll wanna shut this down after you're done with this tutorial. Once it's done, go ahead and click on the web preview button and then select change port. It runs on port 8,801, so go ahead and click change in preview and you should be good to go. Now you have this playground for all your data analysis and processing needs, and it's already wired up with your data from BigQuery. And even though we're in a Python environment, we're only going to be writing a tiny bit of Python code. The cool thing about notebooks is that you can run code on a cell by cell basis. If you're not already familiar with them, I recommend checking out some of the examples and running the code just to get a feel for how it works. Now I'm starting from a new notebook, And I first wanna make sure that we have access to our BigQuery table. We use the b q command and call tables list, and that should list out the table that you have in BigQuery. And you can also use the help command just to list out all of the commands available on the b q namespace. Now that we know the name of our table, let's go ahead and make a query against it in this Python notebook. We use the b q query command, and then we can just write a standard SQL statement. We can go ahead and click play, then it should take a second to run and then output the result of that query in the notebook. And we finally reached the point where BigQuery ML comes into play. We're going to write a special SQL statement that makes a query while at the same time creating a machine learning model based on that data. We use the create or replace model statement and then give it a name of ML model. And it can live in the same dataset as our horses table. From here, we can pass in a variety of different options. And first, we'll set the model type to logistic regression. In machine learning, this is a classification problem because we're trying to predict a specific label, which is either 1st place, 2nd place, 3rd, and so one. The other option is Linear Regression, which you would use if you have a continuous value. For example, the measurement of length which could have any infinite number of values depending on how precise you go. And confusingly because we have a classification problem, we use the logistic regression model type. The next thing we need to do is tell it what our input label is or, in other words, the thing we're trying to predict which is the position field. Once we have the options defined, we can just make a query to our table using select and from. And BigQuery will automatically do all of the machine learning stuff under the hood, like separate your data into a training and validation set, it will choose the hyperparameters for the algorithm and it will run multiple training iterations based on what it thinks is fast. That'll take a minute. And then if we go back to the BigQuery console, you can see that we now have our machine learning model in our dataset. The next thing we need to do is determine if our machine learning model is any good. We'll run another query. This time, we'll select all the fields from the model, and then we'll run ML dot evaluate. And we'll point it to the ML model in our dataset. Then from inside here, we can make a query to the actual horse's data and evaluate it based on that model. This is going to print out a variety of different metrics, but the one we're concerned about is log loss. That's the value that we're trying to minimize. You can also keep an eye on the area under the curve. It's currently at 0.5, which means our model is completely random or in other words, completely worthless. How do we make this model better? Well, in our case, we're only using the sex in the age of the horse to determine the outcome. There's a lot more data in this dataset, so let's go ahead and feed that into the machine learning model to see if we get better results. So I'm selecting a bunch of other fields like the handicap weight, penalty, barrier, and stuff like that. If we have high quality data, then these fields should increase the predictive value of the model. When we retrain and reevaluate the model, you can see the log loss now decreases to 0.29, and the area under the curve increases to 0.7. Now we have a model that we should be able to take to the racetrack to make an unlimited amount of money. Now we can make predictions on new data by using the mlpredict statement. You would use this one by creating a new table with some new data that you acquired at the racetrack. Using a similar schema, you would feed it into this machine learning model, and it would give you predictions on that new data. In this example here, I'm just randomly sampling 10 items from our existing dataset. And you can see the result of that query is a prediction for the position of the horse in that race. I'm gonna go ahead and wrap things up there. If this video helped you, please like and subscribe. And if you want access to even more content, consider becoming a pro member at Fireship I l. Thanks for watching, and I will talk to you soon. All you gotta do is bet on the winner and you'll never lose.
        