Create and update counters in Bigtable

Learn how to create and update counters in Bigtable using aggregates, table cells that aggregate values at write time. This quickstart uses the Google Cloud CLI and the cbt CLI to create three counters:

  • A counter that keeps a running sum
  • A counter that keeps track of the minimum of all added values
  • A counter that keeps track of the maximum of all added values

Before you begin

  1. Sign in to your Google Account.

    If you don't already have one, sign up for a new account.

  2. Install the Google Cloud CLI.
  3. To initialize the gcloud CLI, run the following command:

    gcloud init
  4. Update and install gcloud components:

    gcloud components update
    gcloud components install cbt
  5. Create or select a Google Cloud project.

    • Create a Google Cloud project:

      gcloud projects create PROJECT_ID

      Replace PROJECT_ID with a name for the Google Cloud project you are creating.

    • Select the Google Cloud project that you created:

      gcloud config set project PROJECT_ID

      Replace PROJECT_ID with your Google Cloud project name.

  6. Make sure that billing is enabled for your Google Cloud project.

  7. Enable the Cloud Bigtable API and Cloud Bigtable Admin API APIs:

    gcloud services enable bigtable.googleapis.com bigtableadmin.googleapis.com
  8. Install the Google Cloud CLI.
  9. To initialize the gcloud CLI, run the following command:

    gcloud init
  10. Update and install gcloud components:

    gcloud components update
    gcloud components install cbt
  11. Create or select a Google Cloud project.

    • Create a Google Cloud project:

      gcloud projects create PROJECT_ID

      Replace PROJECT_ID with a name for the Google Cloud project you are creating.

    • Select the Google Cloud project that you created:

      gcloud config set project PROJECT_ID

      Replace PROJECT_ID with your Google Cloud project name.

  12. Make sure that billing is enabled for your Google Cloud project.

  13. Enable the Cloud Bigtable API and Cloud Bigtable Admin API APIs:

    gcloud services enable bigtable.googleapis.com bigtableadmin.googleapis.com
  14. Run the following commands to ensure that the gcloud CLI is up to date and includes the cbt CLI :
    gcloud components update
    gcloud components install cbt

Create a Bigtable instance

  1. Use the bigtable instances create command to create an instance.

    gcloud bigtable instances create counters-quickstart-instance \
        --display-name="Counters quickstart instance" \
        --cluster-config=id="counters-quickstart-cluster",zone="us-east1-c"
    

Connect to your instance

  1. Configure the cbt CLI to use your project and instance by creating a .cbtrc file.

    echo project = PROJECT_ID >> ~/.cbtrc && echo instance = counters-quickstart-instance >> ~/.cbtrc
    

    Replace PROJECT_ID with the ID of the project that you are using.

  2. Verify that you set up the .cbtrc file correctly.

    cat ~/.cbtrc
    

    The terminal displays the contents of the .cbtrc file, which looks similar to the following:

    project = PROJECT_ID
    instance = counters-quickstart-instance

    Now you can use the cbt CLI with your instance.

Create a table with aggregate column families

  1. Use the cbt createtable command to create a table named counters_quickstart_table that has three aggregate column families. Configure each column family with a different aggregation type:

    • Column family max_family is type Max with an input type of Integer.
    • Column family min_family is type Min with an input type of Integer.
    • Column family sum_family is type Sum with an input type of Integer.
    cbt createtable counters_quickstart_table families=sum_family:never:intsum,min_family:never:intmin,max_family:never:intmax
    
  2. List your column families by running the cbt ls command.

    cbt ls counters_quickstart_table
    

    The shell displays output similar to the following:

    Family Name     GC Policy
    -----------     ---------
    max_family      <never>
    min_family      <never>
    sum_family      <never>
    

Create counters in the table

  1. Use the cbt addtocell command to write an initial value of 5 to a new column in each of the three column families, using a row key of row-key1 and a timestamp of 0. This operation creates aggregate cells, which you use as counters.

    cbt addtocell counters_quickstart_table row-key1 sum_family:sum_column=5@0
    cbt addtocell counters_quickstart_table row-key1 min_family:min_column=5@0
    cbt addtocell counters_quickstart_table row-key1 max_family:max_column=5@0
    

Read the data

  1. To view the counter values as integers rather than bytes, define a yaml file that cbt CLI can use to format the output. Run the following:

    echo "families:" > cbtformat.yaml
    echo "  max_family:" >> cbtformat.yaml
    echo "    default_encoding: BigEndian" >> cbtformat.yaml
    echo "    default_type: INT64" >> cbtformat.yaml
    echo "  min_family:" >> cbtformat.yaml
    echo "    default_encoding: BigEndian" >> cbtformat.yaml
    echo "    default_type: INT64" >> cbtformat.yaml
    echo "  sum_family:" >> cbtformat.yaml
    echo "    default_encoding: BigEndian" >> cbtformat.yaml
    echo "    default_type: INT64" >> cbtformat.yaml
    
  2. Verify that you set up the cbtformat.yaml file correctly.

    cat ~/cbtformat.yaml
    

    The terminal displays the contents of the cbtformat.yaml file, which looks similar to the following:

    families:
      max_family:
        default_encoding: BigEndian
        default_type: INT64
      min_family:
        default_encoding: BigEndian
        default_type: INT64
      sum_family:
        default_encoding: BigEndian
        default_type: INT64
    
  3. Use the cbt read command to pass the yaml file and read the data that you added to the table. The table now has three columns, each with a different aggregation type.

    cbt read counters_quickstart_table format-file=$HOME/cbtformat.yaml
    

    The shell displays output similar to the following. The values are formatted as integers, and the timestamps are in UTC format.

    row-key1
      max_family:max_column                    @ 1970/01/01-00:00:00.000000
        5
      min_family:min_column                    @ 1970/01/01-00:00:00.000000
        5
      sum_family:sum_column                    @ 1970/01/01-00:00:00.000000
        5
    

Update the counters

  1. Add a value of 3 to each column in the table, using the same timestamps that you used when you created the cells. In each column, the cell value is merged with the existing value based on the cell's aggregation type.

    cbt addtocell counters_quickstart_table row-key1 sum_family:sum_column=3@0
    cbt addtocell counters_quickstart_table row-key1 min_family:min_column=3@0
    cbt addtocell counters_quickstart_table row-key1 max_family:max_column=3@0
    
  2. Use the cbt read command again to read the data in the table. Each cell now contains an aggregated value.

    cbt read counters_quickstart_table format-file=$HOME/cbtformat.yaml
    

    The sum_column contains the sum of 5 and 3 (8), min_column contains the minimum of the two values that were written to it (3), and max_column contains the maximum of the two values that were written to it (5).

    row-key1
        max_family:max_column                    @ 1970/01/01-00:00:00.000000
            5
        min_family:min_column                    @ 1970/01/01-00:00:00.000000
            3
        sum_family:sum_column                    @ 1970/01/01-00:00:00.000000
            8
    
  3. Optional: Query the table in the Google Cloud console with SQL.

    1. In the Google Cloud console, open the Bigtable instances page.

      Go to instances list

    2. Select counters-quickstart-instance from the list.

    3. In the navigation menu, click Bigtable Studio.

    4. Click the Editor tab.

    5. Paste this query into the editor:

      SELECT * FROM `counters_quickstart_table`
      
    6. Click Run. The results of the query are displayed in the Results table and look similar to the following:

    _key max_family min_family sum_family
    row-key1 { "max_column": 5 } { "min_column": 5 } { "sum_column": 8 }

Clean up

To avoid incurring charges to your Google Cloud account for the resources used on this page, delete the Google Cloud project with the resources.

  1. In the terminal, delete the table counters_quickstart_table:

    cbt deletetable counters_quickstart_table
    
  2. Delete the instance:

    cbt deleteinstance counters-quickstart-instance
    
  3. Delete the .cbtrc file:

    rm ~/.cbtrc
    
  4. Delete the formatting file:

    rm ~/cbtformat.yaml
    
  5. Optional: Revoke credentials from the gcloud CLI:

    gcloud auth revoke
    

What's next