The UserScore and HourlyTeamScore programs wrote their output to text files, but LeaderBoard does something different. It writes to BigQuery tables.

BigQuery is Google’s data warehouse service. It’s a good place to put the output data from LeaderBoard because it can hold massive amounts of data at low cost and it lets you analyze that data by running SQL queries on it.

To write user scores to BigQuery, LeaderBoard uses the WriteToBigQuery class from utils. This is a transform, so to see a high-level view of what it does, let’s look at the expand method. First, it applies a transform that converts each data record into a row.

Then it applies the BigQueryIO.writeTableRows transform to write those rows to a table in BigQuery. Here’s what the transform needs to know. First, you have to tell it which table to write to. You need to include the project ID, the dataset ID, and the table name. The dataset must already exist, but the table doesn’t necessarily have to. If you set CreateDisposition to CREATE_IF_NEEDED, then it will automatically create the table if it doesn’t exist.

Then you need to tell it what the schema of the table should look like. The method that does that (getSchema) is right here. It goes through a for loop and adds all of the fields that are needed. Here’s the line that adds a field. It specifies the name of the field’s key and the data type of the field’s value.

Finally, you need to specify the WriteDisposition, which says if the table must be empty or if it should be overwritten or if new data should be appended to it. Since LeaderBoard is constantly streaming new data to the table, this needs to be set to append.

That’s it for writing to BigQuery. Now, although LeaderBoard doesn’t read from BigQuery, I’ll show you how that works too.

If you want to read a whole table, then it’s very easy. Here’s what the code looks like. You just need to specify your project ID, then a colon, then the name of the dataset, which is “game” for LeaderBoard, and the name of the table, which is leaderboard_user for the table that holds the user scores.

However, you probably wouldn’t want to read the entire user table because it would be huge if the game had been running for a while. It would be better to run a query. Instead of using the from method, you use the fromQuery method. This example selects the user and total_score fields. It’s written in standard SQL rather than BigQuery’s legacy SQL, so you have to add the usingStandardSql method.

To be honest, this query wouldn’t be much better than reading in the whole table, because it only leaves out one field in the table, and it’s a pretty important field because it contains the processing time for each event. What you’d really want to do is get LeaderBoard to create a partitioned table and then write a query that reads from a subset of partitions. But that’s way outside the the scope of this course. If you want to learn more about partitioned tables, then you can take my Optimizing Google BigQuery course.

OK, there were a lot of new concepts in the LeaderBoard program, weren’t there? In the next lesson, I’ll show you how to run it.