Is a command-line tool that you can use for running sql statements.
The bq command-line tool is a Python-based command-line tool for BigQuery. This page contains general information about using the bq command-line tool. Show
For a complete reference of all bq commands and flags, see the bq command-line tool reference. Before you beginBefore you can use the bq command-line tool, you must use the Google Cloud console to create or select a project.
Entering bq commands in Cloud ShellYou can enter bq command-line tool commands in Cloud Shell either from the Google Cloud console or from the Google Cloud CLI.
Positioning flags and argumentsThe bq command-line tool supports two kinds of flags:
For a list of available global and command-specific flags, see bq command-line tool reference. Place any global flags before the bq command, and then include command-specific flags. You can include multiple global or command-specific flags. For example: bq --location=us mk --reservation --project_id=project reservation_nameYou can specify command arguments in the following ways:
Replace the following:
Some commands require the use of single or double quotes around arguments. This is often true when the argument contains spaces, commas, or other special characters. For example: bq query --nouse_legacy_sql \ 'SELECT COUNT(*) FROM `bigquery-public-data`.samples.shakespeare'Flags with boolean values can be specified without an argument. If you specify true or false, then you must use the format FLAG=ARGUMENT. For example, this command specifies false for the boolean flag --use_legacy_sql by placing no at the front of the flag: bq query --nouse_legacy_sql \ 'SELECT COUNT(*) FROM `bigquery-public-data`.samples.shakespeare'Alternatively, to specify false as the flag's argument, you can enter the following: bq query --use_legacy_sql=false \ 'SELECT COUNT(*) FROM `bigquery-public-data`.samples.shakespeare'Running queries from the bq command-line toolTo take a query that you've developed in the Google Cloud console and run it from the bq command-line tool, do the following:
For example, transform the following Google Cloud console query: -- count Shakespeare's use of the string "raisin" SELECT word, SUM(word_count) AS count FROM `bigquery-public-data`.samples.shakespeare WHERE word LIKE '%raisin%' GROUP BY wordinto a bq command-line tool query as follows: bq query --use_legacy_sql=false \ 'SELECT word, SUM(word_count) AS count FROM `bigquery-public-data`.samples.shakespeare WHERE word LIKE "%raisin%" GROUP BY word'For more information, see Running interactive and batch query jobs. Getting helpTo get help for the bq command-line tool, you can enter the following commands:
Replace COMMAND with the command that you need help with. Setting default values for command-line flagsYou can set default values for command-line flags by including them in the bq command-line tool's configuration file, .bigqueryrc. Before you configure your default options, you must first create a .bigqueryrc file. You can use your preferred text editor to create the file. After you create the .bigqueryrc file, you can specify the path to the file using the --bigqueryrc global flag. If the --bigqueryrc flag is not specified, then the BIGQUERYRC environment variable is used. If that is not specified, then the path ~/.bigqueryrc is used. The default path is $HOME/.bigqueryrc. Adding flags to .bigqueryrcTo add default values for command-line flags to .bigqueryrc:
For example: --apilog=stdout --format=prettyjson --location=US [query] --use_legacy_sql=false --max_rows=100 --maximum_bytes_billed=10000000 [load] --destination_kms_key=projects/myproject/locations/mylocation/keyRings/myRing/cryptoKeys/myKeyThe preceding example sets default values for the following flags:
Running the bq command-line tool in an interactive shellYou can run the bq command-line tool in an interactive shell where you don't need to prefix the commands with bq. To start interactive mode, enter bq shell. After launching the shell, the prompt changes to the ID of your default project. To exit interactive mode, enter exit. Running the bq command-line tool in a scriptYou can run the bq command-line tool in a script, as you would run a Google Cloud CLI command. The following is an example of gcloud and bq commands in a bash script: #!/bin/bash gcloud config set project myProject bq query --use_legacy_sql=false --destination_table=myDataset.myTable \ 'SELECT word, SUM(word_count) AS count FROM `bigquery-public-data`.samples.shakespeare WHERE word LIKE "%raisin%" GROUP BY word'Running bq commands from a service accountTo run bq commands using a service account, you must authorize access to Google Cloud from the service account. For more information, see gcloud auth activate-service-account. ExamplesYou can find command-line examples throughout the How-to guides section of the BigQuery documentation. This section lists links to common command-line tasks such as creating, getting, listing, deleting, and modifying BigQuery resources. Creating resourcesFor information about using the bq command-line tool to create resources, see the following:
For examples of creating a table using a data file, see Loading data. Getting information about resourcesFor information about using the bq command-line tool to get information about resources, see the following:
Listing resourcesFor information about using the bq command-line tool to list resources, see the following:
Updating resourcesFor information about using the bq command-line tool to update resources, see the following:
Loading dataFor information about using the bq command-line tool to load data, see the following:
Querying dataFor information about using the bq command-line tool to query data, see the following:
Using external data sourcesFor information about using the bq command-line tool to query data in external data sources, see the following:
Exporting dataFor information about using the bq command-line tool to export data, see the following:
Using the BigQuery Data Transfer ServiceFor information about using the bq command-line tool with the BigQuery Data Transfer Service, see the following:
Troubleshooting the bq command-line toolThis section shows you how to resolve issues with bq command-line tool. Keep your gcloud CLI up to dateIf you are using the bq command-line tool from the Google Cloud CLI, then make sure that you have the latest functionality and fixes for the bq command-line tool by keeping your gcloud CLI installation up to date. To see whether you are running the latest version of the gcloud CLI, enter the following command in Cloud Shell: gcloud components listThe first two lines of the output display the version number of your current gcloud CLI installation and the version number of the most recent gcloud CLI. If you discover that your version is out of date, then you can update your gcloud CLI installation to the most recent version by entering the following command in Cloud Shell: gcloud components updateDebuggingYou can enter the following commands to debug the bq command-line tool:
Which tool is used to run SQL queries?Recommended tools. What is run SQL command line?Oracle SQL Developer Command Line (SQLcl) is a free command line interface for Oracle Database. It allows you to interactively or batch execute SQL and PL/SQL.
Which command is used in SQL?There are 3 main types of commands. DDL (Data Definition Language) commands, DML (Data Manipulation Language) commands, and DCL (Data Control Language) commands.
Which command line tool can you use to query Azure SQL?Query editor (preview) is a tool to run SQL queries against Azure SQL Database in the Azure portal.
|