2. About the dataset

You'll be looking at a dataset about New York City buses. There are more than 300 bus routes and 5,800 vehicles following those routes.Our dataset is a log that includes destination name, vehicle id, latitude, longitude, expected arrival time, and scheduled arrival time. The dataset is made up of snapshots taken around every 10 minutes for June 2017.

3. Schema design

To get the best performance from Cloud Bigtable, you have to be thoughtful when youdesign your schema. Data in Cloud Bigtable isautomatically sorted lexicographically, so if you design your schema well, querying for related data is very efficient. Cloud Bigtable allows for queries usingpoint lookups by row key orrow-range scans that return a contiguous set of rows. However, if your schema isn't well thought out, you might find yourself piecing together multiple row lookups, or worse, doing full table scans, which are extremely slow operations.

Common mistake: You might think that makingtime the first value in the row key would be ideal, because you probably care about more recent data, and would want to run queries mainly around certain times.Doing this causes hotspots in the data, however, so you compromise by puttingtime third. This makes some of your queries more difficult, but you need to do this in order to get the full performance Cloud Bigtable has to offer. Also, you probably don't need to get all buses for a certain time at once. Check out thistalk by Twitter for information about how they optimized their schema.

4. Create instance, table, and family

Next, you'll create a Cloud Bigtable table.

First, create a new project. Use the built-in Cloud Shell, which you can open by clicking the "Activate Cloud Shell" button in the upper-righthand corner.

Set the following environment variables to make copying and pasting the codelab commands easier:

INSTANCE_ID="bus-instance"CLUSTER_ID="bus-cluster"TABLE_ID="bus-data"CLUSTER_NUM_NODES=3CLUSTER_ZONE="us-central1-c"

Cloud Shell comes with the tools that you'll use in this codelab, thegcloud command-line tool, thecbt command-line interface, andMaven, already installed.

Enable the Cloud Bigtable APIs by running this command.

gcloud services enable bigtable.googleapis.com bigtableadmin.googleapis.com

Create an instance by running the following command:

gcloud bigtable instances create $INSTANCE_ID \    --cluster=$CLUSTER_ID \    --cluster-zone=$CLUSTER_ZONE \    --cluster-num-nodes=$CLUSTER_NUM_NODES \    --display-name=$INSTANCE_ID

echo project = $GOOGLE_CLOUD_PROJECT > ~/.cbtrcecho instance = $INSTANCE_ID >> ~/.cbtrccbt createtable $TABLE_IDcbt createfamily $TABLE_ID cf

6. Get the code

git clone https://github.com/googlecodelabs/cbt-intro-java.gitcd cbt-intro-java

Change to Java 11 by running the following commands:

sudo update-java-alternatives -s java-1.11.0-openjdk-amd64 && export JAVA_HOME=/usr/lib/jvm/java-11-openjdk-amd64/

7. Perform a lookup

The first query you'll perform is a simple row lookup. You'll get the data for a bus on the M86-SBS line on June 1, 2017 from 12:00 am to 1:00 am. A vehicle with idNYCT_5824 is on the bus line then.

With that information, and knowing the schema design (Bus company/Bus line/Timestamp rounded down to the hour/Vehicle ID,) you can deduce that the row key is:

MTA/M86-SBS/1496275200000/NYCT_5824

BusQueries.java

privatestaticfinalbyte[]COLUMN_FAMILY_NAME=Bytes.toBytes("cf");privatestaticfinalbyte[]LAT_COLUMN_NAME=Bytes.toBytes("VehicleLocation.Latitude");privatestaticfinalbyte[]LONG_COLUMN_NAME=Bytes.toBytes("VehicleLocation.Longitude");StringrowKey="MTA/M86-SBS/1496275200000/NYCT_5824";ResultgetResult=table.get(newGet(Bytes.toBytes(rowKey)).addColumn(COLUMN_FAMILY_NAME,LAT_COLUMN_NAME).addColumn(COLUMN_FAMILY_NAME,LONG_COLUMN_NAME));

The result should contain the most recent location of the bus within that hour. But you want to see all the locations, so set the maximum number of versions on the get request.

BusQueries.java

ResultgetResult=table.get(newGet(Bytes.toBytes(rowKey)).setMaxVersions(Integer.MAX_VALUE).addColumn(COLUMN_FAMILY_NAME,LAT_COLUMN_NAME).addColumn(COLUMN_FAMILY_NAME,LONG_COLUMN_NAME));

In the Cloud Shell, run the following command to get a list of latitudes and longitudes for that bus over the hour:

mvn package exec:java -Dbigtable.projectID=$GOOGLE_CLOUD_PROJECT \-Dbigtable.instanceID=$INSTANCE_ID -Dbigtable.table=$TABLE_ID \-Dquery=lookupVehicleInGivenHour

You can copy and paste the latitudes and longitudes intoMapMaker App to visualize the results. After a few layers, it will tell you to create a free account. You can create an account or just delete the existing layers you have. This codelab includes a visualization for each step, if you just want to follow along. Here is the result for this first query:

8. Perform a scan

Now, let's view all the data for the bus line for that hour. The scan code looks pretty similar to the get code. You give the scanner a starting position and then indicate you only want rows for the M86-SBS bus line within the hour denoted by the timestamp 1496275200000.

BusQueries.java

Scanscan;scan=newScan();scan.setMaxVersions(Integer.MAX_VALUE).addColumn(COLUMN_FAMILY_NAME,LAT_COLUMN_NAME).addColumn(COLUMN_FAMILY_NAME,LONG_COLUMN_NAME).withStartRow(Bytes.toBytes("MTA/M86-SBS/1496275200000")).setRowPrefixFilter(Bytes.toBytes("MTA/M86-SBS/1496275200000"));ResultScannerscanner=table.getScanner(scan);

Run the following command to get the results.

mvn package exec:java -Dbigtable.projectID=$GOOGLE_CLOUD_PROJECT \-Dbigtable.instanceID=$INSTANCE_ID -Dbigtable.table=$TABLE_ID \-Dquery=scanBusLineInGivenHour

The Map Maker app can display multiple lists at once, so you can see which of the buses are the vehicle from the first query you ran.

An interesting modification to this query is to view the entire month of data for the M86-SBS bus line, and this is very easy to do. Remove the timestamp from the start row and prefix filter to get the result.

BusQueries.java

scan.withStartRow(Bytes.toBytes("MTA/M86-SBS/")).setRowPrefixFilter(Bytes.toBytes("MTA/M86-SBS/"));// Optionally, reduce the results to receive one version per column// since there are so many data points.scan.setMaxVersions(1);

Run the following command to get the results. (There will be a long list of results.)

mvn package exec:java -Dbigtable.projectID=$GOOGLE_CLOUD_PROJECT \-Dbigtable.instanceID=$INSTANCE_ID -Dbigtable.table=$TABLE_ID \-Dquery=scanEntireBusLine

If you copy the results into MapMaker, you can view a heatmap of the bus route. The orange blobs indicate the stops, and the bright red blobs are the start and end of the route.

9. Introduce filters

Next, you will filter on buses heading east and buses heading west, and create a separate heatmap for each.

Note: This filter will only check the latest version, so you will set the max versions to one to ensure the results match the filter. Read more aboutconfiguring your filter.

BusQueries.java

Scanscan;ResultScannerscanner;scan=newScan();SingleColumnValueFiltervalueFilter=newSingleColumnValueFilter(COLUMN_FAMILY_NAME,Bytes.toBytes("DestinationName"),CompareOp.EQUAL,Bytes.toBytes("Select Bus Service Yorkville East End AV"));scan.setMaxVersions(1).addColumn(COLUMN_FAMILY_NAME,LAT_COLUMN_NAME).addColumn(COLUMN_FAMILY_NAME,LONG_COLUMN_NAME);scan.withStartRow(Bytes.toBytes("MTA/M86-SBS/")).setRowPrefixFilter(Bytes.toBytes("MTA/M86-SBS/"));scan.setFilter(valueFilter);scanner=table.getScanner(scan);

Run the following command to get the results for buses going east.

mvn package exec:java -Dbigtable.projectID=$GOOGLE_CLOUD_PROJECT \-Dbigtable.instanceID=$INSTANCE_ID -Dbigtable.table=$TABLE_ID \-Dquery=filterBusesGoingEast

To get the buses going west, change the string in the valueFilter:

BusQueries.java

SingleColumnValueFiltervalueFilter=newSingleColumnValueFilter(COLUMN_FAMILY_NAME,Bytes.toBytes("DestinationName"),CompareOp.EQUAL,Bytes.toBytes("Select Bus Service Westside West End AV"));

Run the following command to get the results for buses going west.

mvn package exec:java -Dbigtable.projectID=$GOOGLE_CLOUD_PROJECT \-Dbigtable.instanceID=$INSTANCE_ID -Dbigtable.table=$TABLE_ID \-Dquery=filterBusesGoingWest

Buses heading east

Buses heading west

By comparing the two heatmaps, you can see the differences in the routes as well as notice differences in the pacing. One interpretation of the data is that on the route heading west, the buses are getting stopped more, especially when entering Central Park. And on the buses heading east, you don't really see many choke points.

10. Perform a multi-range scan

For the final query, you'll address the case when you care about many bus lines in an area:

BusQueries.java

privatestaticfinalString[]MANHATTAN_BUS_LINES={"M1","M2","M3",...Scanscan;ResultScannerscanner;List<RowRange>ranges=newArrayList<>();for(StringbusLine:MANHATTAN_BUS_LINES){ranges.add(newRowRange(Bytes.toBytes("MTA/"+busLine+"/1496275200000"),true,Bytes.toBytes("MTA/"+busLine+"/1496275200001"),false));}Filterfilter=newMultiRowRangeFilter(ranges);scan=newScan();scan.setFilter(filter);scan.setMaxVersions(Integer.MAX_VALUE).addColumn(COLUMN_FAMILY_NAME,LAT_COLUMN_NAME).addColumn(COLUMN_FAMILY_NAME,LONG_COLUMN_NAME);scan.withStartRow(Bytes.toBytes("MTA/M")).setRowPrefixFilter(Bytes.toBytes("MTA/M"));scanner=table.getScanner(scan);

Run the following command to get the results.

mvn package exec:java -Dbigtable.projectID=$GOOGLE_CLOUD_PROJECT \-Dbigtable.instanceID=$INSTANCE_ID -Dbigtable.table=$TABLE_ID \-Dquery=scanManhattanBusesInGivenHour

11. Finish up

Clean up to avoid charges

To avoid incurring charges to your Google Cloud Platform account for the resources used in this codelab you should delete your instance.

gcloud bigtable instances delete $INSTANCE_ID

What we've covered

• Schema design
• Setting up an instance, table, and family
• Importing sequence files with dataflow
• Querying with a lookup, a scan, a scan with a filter, and a multi-range scan

Next steps

• Learn more about Cloud Bigtable in thedocumentation.
• Try out other Google Cloud Platform features for yourself. Have a look at ourtutorials.
• Learn how tomonitor time-series data with the OpenTSDB integration