Now it's time to create your first table and insert some data into your CrateDB cluster. Let's get started.
Here I'm using the Cloud Console, so with my cloud database, I've selected 'console' over here on the left. And this is an interface that allows us to enter SQL commands similar to the Crash CLI. We could also do these commands in the Crash CLI, but I wanted to show you the console in this video.
If you're working with Docker, you'll see a slightly different interface, so you'll go to localhost port 4200, and you'll click on this symbol over here, which is the console. From here, the process is identical.
You just enter the SQL commands that I'm going to show in the cloud. So first thing we need to do is create a table. I've got some SQL in the clipboard, so I'm going to go ahead and paste that and we'll see.
We're creating a table here called taxis. It has some fields that describe vehicles that are taxis in the city of Chicago. We're going to give them an id, a status field, make model and model years, some information about the vehicle itself, what colour it is, what fuel it takes, whether it's wheelchair accessible, etc. If I press 'execute' here, that will go ahead and create the table. We can see what's happened, and here in the doc schema we now have a table called taxis and we can look at it. So what we need to do next is go ahead and load up some data into our table because there's currently nothing there.
One of the ways that CrateDB allows us to load data is by copying it in from URLs. So we've got a URL that represents a CSV file that contains data for our Chicago Taxi data set, and we can see that here. The link for this will be in your workbook. What I need to do is copy this link and I'm going to paste that into a SQL command. What I'm going to say is copy and then the table name, tap these. That's the table we're going to put the data into from then where it's coming from. So this URL here and then I'm going to say return summary. So I'm going to ask CrateDB to return me a summary of what happened when it attempted to load all of these CSV records into the database. Let's click 'execute' and you'll see here we have a success count and an error count.
I have 2655 records loaded with no errors. That's what we want to see, no errors. We can now go ahead and query the data. Having created that table, it's now time to write our first query again. I have this in the clipboard, and we're going to ask CrateDB how many of each type of make and model of taxi are there in Chicago. Which ones are the popular ones?
So we're going to ask it to order them by the number of occurrences. So what we see here is that the Toyota Camry is clearly the most popular taxi. There's 1098 of those, followed by the Prius and the Sienna. And most of the popular vehicles here are all Toyotas. So what we've got here is a very basic query that we've run. We've created a table and we've copied data from a source on the Internet, which in this case was a GitHub repository. You'll now need to work through all of the commands in your workbook to install the data set for this course.