BigQuery - Write First Query

It is possible to open a blank page within the query editor, however it is better to compose your first query directly from the table selection step to avoid syntax errors.

To write your first query in this way, begin by navigating to the dataset that contains the table you'd like to query. Click on "view table." In the upper panel, select "query." Following this process will open a new window in which the table name is already populated, along with any limitations the creator added.

For instance, a table may require a WHERE clause or a suggested query may limit a user to say 1000 rows. In order to follow best practices, replace the " * " with the name of the columns you'd like to query.

• Be mindful of including a GROUP BY clause if adding any aggregations to your SELECT
• If you'd like to be extra vigilant about syntax errors, you can also select column names from the provided schema by clicking on them.

If you follow these steps, you shouldn't have a need to write the table name. However, to get in the habit of formulating correct table references, remember the formula is: project.dataset.table. These elements are all enclosed in backticks (not quotations).

One unique element of BigQuery Studio is that the IDE will tell you if a query will run or not. This will be indicated by a green checkmark.

Once you've confirmed everything looks correct, hit run. As the query runs you'll see execution metrics like data processed, time it takes the query to run and number of steps required. If you glance at the bottom panel you'll also see the amount of slots it takes to run.

Write Your First Query on Cloud Shell Terminal

Like querying in the UI, querying in the cloud shell terminal follows a similar structure and allows users to use SQL syntax to access and manipulate data.

The "bq" Query and its Common Flags

Writing and executing a query within cloud shell is simple enough with the command bq query. In the same line, users can supply flags that dictate certain aspects of execution.

Some of the more common flags for the bq query command include −

• allow-large-results (does not cancel jobs due to large results)
• batch = {true | false}
• clustering-fields = [ ]
• destination-table = table_name

You may notice that all of these parameters correspond to the drop-down menu that appears when creating a table or running a query within the UI.

To run a query within cloud shell −

• Log into GCP
• Enter the cloud shell terminal
• Authenticate (done automatically)
• Write and execute query

Which looks like −

(ornate-reaf-332816)$ bq query --use_legacy_sql=false \ 'SELECT * FROM 
ornate-reef-332816.sample_dataset.bikeshare_2022_stsore_date';

Results appear as a terminal output. While results are not rendered like BigQuery UI results, the output is still neat and understandable.