Build queries in the console

You can query and view your Bigtable data by using theBigtable Studioquery builder. The query builder is an interactive form inthe Google Cloud console that lets you build a query, run it against a table,and then view the results in the console.

The Bigtable Studio query builder lets you use drop-down selectorsto specify a combination of query clauses without needing to write anapplication or use a CLI. When you run the query, the console makes a call tothe Bigtable Data API, which returns data that matches your query.

Note: The query builder does not create SQL statements. For information oncreating and running SQL statements that you can run in theBigtable query editor, seeGoogleSQL forBigtable overview.

This document describes how to use the query builder and offers tips onoptimizing your queries. Before you read this page, you should be familiar withtheBigtableoverview .

Queries run in the query builder are subject to the same pricing and quotas asany other query sent to a table.

When to use the query builder

The ability to query your Bigtable data in theGoogle Cloud console is helpful when you need to do the following:

• Quickly get a visual representation of your table's schema.
• Verify that certain data was successfully written.
• Validate data integrity during a migration.
• Debug possible data issues.
• Preview the results that a specific combination of clauses returns beforeyou use them in your code. This can be especially helpful for newBigtable users as well as experienced users who don't want to usethecbt CLI.

Before you begin

To get the permissions that you need to use the query builder, ask your administrator to grant you theBigtable Reader (roles/bigtable.reader) IAM role on the project. For more information about granting roles, seeManage access to projects, folders, and organizations.

This predefined role contains the permissions required to use the query builder. To see the exact permissions that are required, expand theRequired permissions section:

Required permissions

The following permissions are required to use the query builder:

• bigtable.tables.get
• bigtable.instances.get
• bigtable.appProfiles.list
• bigtable.tables.list
• bigtable.tables.readRows


You might also be able to get these permissions withcustom roles or otherpredefined roles.

If you don't have a table to query, you cancreate a small test table and run your queries against it.

Open the query builder

When you open the query builder, a default query is run and displayed for thefirst table alphabetically in the instance. The default query is run using thedefault app profile, and up to 100 rows are returned.

1. Open the list of Bigtable instances in the Google Cloud console.

   Open the instance list

2. Click the name of the instance that contains the table that you want toquery.

3. In the navigation pane, clickBigtable Studio. TheBigtable Studiopage opens in query builder mode. TheExplorer pane provides a list oftables in the instance, and theQuery results pane shows the results ofthe default query.

4. Optional: Collapse theExplorer pane to make the query builder panelarger.

Data display format

Query builder results are displayed in a table. The first heading and column isRow key. The remaining headings each represent a column in theBigtable table, expressed as the column family and columnqualifier separated by a colon. For example, the headingcell_plan: data_plan_01gb indicates that values in that column are from thedata_plan_01gb column in thecell_plan column family.

Only the most recent value for a column is returned. Timestamps are hidden bydefault, but you can click the toggle to display them.

Select a table

Use theTable drop-down to select the ID of the table that you want toquery.

Optional: Specify an app profile

You can use an app profile that you create specifically for use with the querybuilder. This can be helpful, for example, when you want to isolate the querytraffic by using a different cluster than the oneused by your primary application.

To use a different app profile to run your next query, follow these steps.

1. ClickAdd to query.
2. ClickChange app profile (default). A new drop-down is displayed.
3. Use theApp profile drop-down and choose from the list of the instance'sapp profiles.

To read more about the advantages of using different app profiles for differentworkloads, seeAbout appprofiles . To learn how to create an app profile, seeCreate and configure appprofiles .

If your table is in an instance that uses replication, configure your appprofile to read from the cluster that is geographically closest to you.

Note: You can't use aData Boost appprofile with the query builder.

Add clauses to the query and then run it

1. After you've selected the table and app profile, add clauses to the query.See the next section for details on the available clauses.
2. After you've added all your clauses, clickRun.
3. View the results.

Query clauses

You use the query builder to add clauses to your query. When you run the query,the query builder creates and sends aread request to your table. To learnmore about read requests, seeReads.

The row key, row key range, and row key prefix clauses determine the rows thatare fetched from storage. The row key regex, columns, and time range clauses addafilter to the generated read request. Filters that you use in the querybuilder work the same as when you use them in the client libraries. For detailsabout Bigtable filters and how using them can affect performance,seeFilters andUsefilters .

Specify row key

To retrieve a single row from the table, provide a row key value. You can addmultiple row key clauses.

1. ClickAdd to query.
2. In the drop-down list, selectRow key.
3. Enter the exact value of the row key for which you want to view values.

Specify a row key range

To retrieve a range of rows from the table, specify a start and end row key.Bigtable stores data in lexicographic order by row key.

1. ClickAdd to query.
2. In the drop-down list, selectRow key range.
3. Enter the complete start row key.
4. Enter the complete end row key.

Specify a row key prefix

To retrieve only rows whose row keys begin with a specific set of characters,filter by row key prefix. A row key prefix is the first N characters of a rowkey. By convention, there is often a hashtag, pipe, or other symbol between theprefix and the rest of the row key.

1. ClickAdd to query.
2. In the drop-down list, selectRow key prefix.
3. Enter a row key prefix. You don't need to enter any wildcard symbols.

Filter by row key regex

To get only rows whose row key values match a specified regular expression, usea row key regex filter. For better performance, add a row key range or row keyprefix clause to the query before you add the row key regex filter. For guidanceon composing your regex, seeRow keyregex .

1. ClickAdd to query.
2. In the drop-down list, selectRow key regex.
3. Enter a regular expression that usesre2syntax

Filter by column

To specify one or more columns to include in the query, add acolumnsclause, choosing the column family and column qualifier for each column that youwant to filter by. The read request that is generated when you add this clauseincludes thecolumn family regexfilter and thecolumn rangefilter .

1. ClickAdd to query.
2. In the drop-down list, selectColumns.
3. Click in the Columns field. A list of column families is displayed.
4. Click a column family ID. A list of column qualifiers is displayed.
5. Click a column qualifier.

Filter by time range

To retrieve only cells with a timestamp within a certain range, add a time rangeclause, which uses thetimestamp rangefilter .

1. ClickAdd to query.
2. In the drop-down list, selectTime range.
3. Enter a start timestamp.
4. Enter an end timestamp.

Provide a timestamp in one of the following formats:

• A Unix timestamp in microseconds, such as3023483279876000
• YYYY-MM-DDThh:mm:ss:ss[z]
• YYYY/MM/DD-hh:mm:ss.sss[z]
• MM/DD/YYYY
• YYYY/MM/DD

The optionalT is a literal that indicates time, and the optionalzindicates UTC time rather than local time. For more information about timestampformats, seeISO8601 .

Specify a limit

To specify a maximum number of rows returned, add a limit clause.

1. ClickAdd to query.
2. In the drop-down list, selectLimit.
3. Enter a number from 1 to 1,00.

Limitations

The following limitations apply to the query builder.

• 1,000 characters per cell — Bigtable returns amaximum of 1,000 characters per cell. If a cell contains more than 1,000characters, a truncated result is displayed, annotated with the number ofcharacters not displayed.
• 100 column maximum — Bigtable returns the first 100columns in each row that match the query. Column families are not stored inany particular order, but columns are sorted lexicographically within theircolumn family.
• 1,000 row maximum — The query builder returns a maximum ofthe first 1,000 rows of data that match your query.
• Base64 — Bigtable makes a best effort to accuratelydisplay Base64-encoded data, but results are not guaranteed. IfBigtable is unable to display the data in a cell, it displaysthe cell value's size in bytes instead.

• Older data — You can only retrieve the most recent cell (orversion) in the columns that you query. For details on this concept, seeBigtable storagemodel andGeneral concepts

• Serialized data — Data stored as protocol buffers is not displayedcorrectly in the query results. JSON data is better supported but notguaranteed to look right.

• Artificial timestamps — If your application assigns non-timestampnumbers to the timestamp property of cells when it writes data to yourtable, you can use time range filters in your query to get a range of thevalues that you use instead of timestamps, as long as you specify them asmicroseconds. To understand why your table data might use artificialtimestamps, seeTimestamps

What's next

• Learn about write requests.
• Create and manage tables.