How to Execute a Query Using bq query Command in BigQuery?

If you're working with Google BigQuery from the command line, one of the most common tasks you'll perform is querying data using the bq query command. This blog post walks you through how to execute a SQL query using the bq CLI tool with a focus on using standard SQL.

Why Specify --use_legacy_sql=false?

By default, the bq query command expects legacy SQL, which is an older dialect specific to BigQuery. However, most users today prefer standard SQL, which follows the ANSI SQL standard and is more powerful and readable.

 

To use standard SQL in your query, you need to explicitly set the --use_legacy_sql flag to false.

 

Basic Syntax

Here’s the basic syntax for running a query using standard SQL:

bq query --use_legacy_sql=false "SELECT * FROM my_project.my_dataset.my_table"

If you are working in the default project, you can omit the project ID:

bq query --use_legacy_sql=false "SELECT * FROM my_dataset.my_table"

Example

Let's look at an example query to fetch all records from the employees table in the test_dataset.

 

Command:

bq query --use_legacy_sql=false "SELECT * FROM test_dataset.employees"

$bq query --use_legacy_sql=false "SELECT * FROM test_dataset.employees"
+------------+-----------+----------+-------------+--------+
| EmployeeID | FirstName | LastName | Department  | Salary |
+------------+-----------+----------+-------------+--------+
|        101 | Raj       | Sharma   | Engineering |  75000 |
|        106 | Deepika   | Rao      | Engineering |  72000 |
|        105 | Anil      | Patel    | Finance     |  80000 |
|        110 | Suresh    | Menon    | Finance     |  82000 |
|        104 | Neha      | Iyer     | HR          |  60000 |
|        109 | Sunita    | Joshi    | HR          |  61000 |
|        102 | Priya     | Agarwal  | Marketing   |  65000 |
|        107 | Vikram    | Mehta    | Marketing   |  67000 |
|        103 | Amit      | Verma    | Sales       |  70000 |
|        108 | Kiran     | Chopra   | Sales       |  69000 |
+------------+-----------+----------+-------------+--------+

In Summary, using bq query with --use_legacy_sql=false ensures you’re writing your queries using standard SQL, the preferred and recommended approach for most BigQuery users. Whether you’re just exploring data or building complex analytics pipelines, mastering this command will make your life much easier. 

 

Previous                                                    Next                                                    Home