Query a public dataset with the BigQuery client libraries
Learn how to query a public dataset with the BigQuery client libraries.
To follow step-by-step guidance for this task directly in the Google Cloud console, select your preferred programming language:
Take the C# tour Take the Go tour Take the Java tour Take the Node.js tour
Take the PHP tour Take the Python tour Take the Ruby tour
Before you begin
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
-
Choose whether to use the BigQuery sandbox at no charge, or to enable billing for your Google Cloud project.
If you do not enable billing for a project, you automatically work in the BigQuery sandbox. The BigQuery sandbox lets you learn BigQuery with a limited set of BigQuery features at no charge. If you do not plan to use your project beyond this document, we recommend that you use the BigQuery sandbox.
-
Enable the BigQuery API.
For new projects, the BigQuery API is automatically enabled.
-
In the Google Cloud console, activate Cloud Shell.
Activate your Google Cloud project in Cloud Shell:
gcloud config set project PROJECT_ID
Replace PROJECT_ID with the project that you selected for this walkthrough.
The output is similar to the following:
Updated property [core/project].
Query a public dataset
Select one of the following languages:
C#
In Cloud Shell, create a new C# project and file:
dotnet new console -n BigQueryCsharpDemo
The output is similar to the following. Several lines are omitted to simplify the output.
Welcome to .NET 6.0! --------------------- SDK Version: 6.0.407 ... The template "Console App" was created successfully. ...
This command creates a C# project that's named
BigQueryCsharpDemo
and a file that's namedProgram.cs
.Open the Cloud Shell Editor:
cloudshell workspace BigQueryCsharpDemo
To open a terminal in the Cloud Shell Editor, click Terminal > New Terminal.
Install the BigQuery client library for C#:
dotnet add package Google.Cloud.BigQuery.V2
The output is similar to the following. Several lines are omitted to simplify the output.
Determining projects to restore... Writing /tmp/tmpF7EKSd.tmp ... info : Writing assets file to disk. ...
Set the variable
GOOGLE_PROJECT_ID
to the valueGOOGLE_CLOUD_PROJECT
and export the variable:export GOOGLE_PROJECT_ID=$GOOGLE_CLOUD_PROJECT
In the Explorer pane, locate your
BIGQUERYCSHARPDEMO
project.Click the
Program.cs
file to open it.To create a query against the
bigquery-public-data.stackoverflow
dataset that returns the top 10 most viewed Stack Overflow pages and their view counts, replace the contents of the file with the following code:In the terminal, run the
Program.cs
script. If you are prompted to authorize Cloud Shell and agree to the terms, click Authorize.dotnet run
The result is similar to the following:
Query Results: ------------ https://stackoverflow.com/questions/35159967: 170023 views https://stackoverflow.com/questions/22879669: 142581 views https://stackoverflow.com/questions/10604135: 132406 views https://stackoverflow.com/questions/44564887: 128781 views https://stackoverflow.com/questions/27060396: 127008 views https://stackoverflow.com/questions/12482637: 120766 views https://stackoverflow.com/questions/20673986: 115720 views https://stackoverflow.com/questions/39109817: 108368 views https://stackoverflow.com/questions/11057219: 105175 views https://stackoverflow.com/questions/43195143: 101878 views
You have successfully queried a public dataset with the BigQuery C# client library.
Go
In Cloud Shell, create a new Go project and file:
mkdir bigquery-go-quickstart \ && touch \ bigquery-go-quickstart/app.go
This command creates a Go project that's named
bigquery-go-quickstart
and a file that's namedapp.go
.Open the Cloud Shell Editor:
cloudshell workspace bigquery-go-quickstart
To open a terminal in the Cloud Shell Editor, click Terminal > New Terminal.
Create a
go.mod
file:go mod init quickstart
The output is similar to the following:
go: creating new go.mod: module quickstart go: to add module requirements and sums: go mod tidy
Install the BigQuery client library for Go:
go get cloud.google.com/go/bigquery
The output is similar to the following. Several lines are omitted to simplify the output.
go: downloading cloud.google.com/go/bigquery v1.49.0 go: downloading cloud.google.com/go v0.110.0 ... go: added cloud.google.com/go/bigquery v1.49.0 go: added cloud.google.com/go v0.110.0
In the Explorer pane, locate your
BIGQUERY-GO-QUICKSTART
project.Click the
app.go
file to open it.To create a query against the
bigquery-public-data.stackoverflow
dataset that returns the top 10 most viewed Stack Overflow pages and their view counts, copy the following code into theapp.go
file:In the terminal, run the
app.go
script. If you are prompted to authorize Cloud Shell and agree to the terms, click Authorize.go run app.go
The result is similar to the following:
https://stackoverflow.com/questions/35159967 : 170023 views https://stackoverflow.com/questions/22879669 : 142581 views https://stackoverflow.com/questions/10604135 : 132406 views https://stackoverflow.com/questions/44564887 : 128781 views https://stackoverflow.com/questions/27060396 : 127008 views https://stackoverflow.com/questions/12482637 : 120766 views https://stackoverflow.com/questions/20673986 : 115720 views https://stackoverflow.com/questions/39109817 : 108368 views https://stackoverflow.com/questions/11057219 : 105175 views https://stackoverflow.com/questions/43195143 : 101878 views
You have successfully queried a public dataset with the BigQuery Go client library.
Java
In Cloud Shell, create a new Java project using Apache Maven:
mvn archetype:generate \ -DgroupId=com.google.app \ -DartifactId=bigquery-java-quickstart \ -DinteractiveMode=false
This command creates a Maven project that's named
bigquery-java-quickstart
.The output is similar to the following. Several lines are omitted to simplify the output.
[INFO] Scanning for projects... ... [INFO] Building Maven Stub Project (No POM) 1 ... [INFO] BUILD SUCCESS ...
There are many dependency management systems that you can use other than Maven. For more information, learn how to set up a Java development environment to use with client libraries.
Rename the
App.java
file that Maven creates by default:mv \ bigquery-java-quickstart/src/main/java/com/google/app/App.java \ bigquery-java-quickstart/src/main/java/com/google/app/SimpleApp.java
Open the Cloud Shell Editor:
cloudshell workspace bigquery-java-quickstart
If you are prompted whether to synchronize the Java classpath or configuration, click Always.
If you are not prompted and encounter an error that is related to the classpath during this walkthrough, do the following:
- Click File > Preferences > Open Settings (UI).
- Click Extensions > Java.
- Scroll to Configuration: Update Build Configuration and select automatic.
In the Explorer pane, locate your
BIGQUERY-JAVA-QUICKSTART
project.Click the
pom.xml
file to open it.Inside the
<dependencies>
tag, add the following dependency after any existing ones. Do not replace any existing dependencies.<dependency> <groupId>com.google.cloud</groupId> <artifactId>google-cloud-bigquery</artifactId> </dependency>
On the line following the closing tag (
</dependencies>
), add the following:<dependencyManagement> <dependencies> <dependency> <groupId>com.google.cloud</groupId> <artifactId>libraries-bom</artifactId> <version>26.1.5</version> <type>pom</type> <scope>import</scope> </dependency> </dependencies> </dependencyManagement>
In the Explorer pane, in your
BIGQUERY-JAVA-QUICKSTART
project, click src > main/java/com/google/app > SimpleApp.java. The file opens.To create a query against the
bigquery-public-data.stackoverflow
dataset, leave the first line of the file (package com.google.app;
), and replace the remaining contents of the file with the following code:The query returns the top 10 most viewed Stack Overflow pages and their view counts.
Right-click SimpleApp.java and click Run Java. If you are prompted to authorize Cloud Shell and agree to the terms, click Authorize.
The result is similar to the following:
https://stackoverflow.com/questions/35159967 : 170023 views https://stackoverflow.com/questions/22879669 : 142581 views https://stackoverflow.com/questions/10604135 : 132406 views https://stackoverflow.com/questions/44564887 : 128781 views https://stackoverflow.com/questions/27060396 : 127008 views https://stackoverflow.com/questions/12482637 : 120766 views https://stackoverflow.com/questions/20673986 : 115720 views https://stackoverflow.com/questions/39109817 : 108368 views https://stackoverflow.com/questions/11057219 : 105175 views https://stackoverflow.com/questions/43195143 : 101878 views
You have successfully queried a public dataset with the BigQuery Java client library.
Node.js
In Cloud Shell, create a new Node.js project and file:
mkdir bigquery-node-quickstart \ && touch \ bigquery-node-quickstart/app.js
This command creates a Node.js project that's named
bigquery-node-quickstart
and a file that's namedapp.js
.Open the Cloud Shell Editor:
cloudshell workspace bigquery-node-quickstart
To open a terminal in the Cloud Shell Editor, click Terminal > New Terminal.
Install the BigQuery client library for Node.js:
npm install --save @google-cloud/bigquery
The output is similar to the following:
added 63 packages in 2s
In the Explorer pane, locate your
BIGQUERY-NODE-QUICKSTART
project.Click the
app.js
file to open it.To create a query against the
bigquery-public-data.stackoverflow
dataset that returns the top 10 most viewed Stack Overflow pages and their view counts, copy the following code into theapp.js
file:In the terminal, run the
app.js
script. If you are prompted to authorize Cloud Shell and agree to the terms, click Authorize.node app.js
The result is similar to the following:
Query Results: url: https://stackoverflow.com/questions/35159967, 170023 views url: https://stackoverflow.com/questions/22879669, 142581 views url: https://stackoverflow.com/questions/10604135, 132406 views url: https://stackoverflow.com/questions/44564887, 128781 views url: https://stackoverflow.com/questions/27060396, 127008 views url: https://stackoverflow.com/questions/12482637, 120766 views url: https://stackoverflow.com/questions/20673986, 115720 views url: https://stackoverflow.com/questions/39109817, 108368 views url: https://stackoverflow.com/questions/11057219, 105175 views url: https://stackoverflow.com/questions/43195143, 101878 views
You have successfully queried a public dataset with the BigQuery Node.js client library.
PHP
In Cloud Shell, create a new PHP project and file:
mkdir bigquery-php-quickstart \ && touch \ bigquery-php-quickstart/app.php
This command creates a PHP project that's named
bigquery-php-quickstart
and a file that's namedapp.php
.Open the Cloud Shell Editor:
cloudshell workspace bigquery-php-quickstart
To open a terminal in the Cloud Shell Editor, click Terminal > New Terminal.
Install the BigQuery client library for PHP:
composer require google/cloud-bigquery
The output is similar to the following. Several lines are omitted to simplify the output.
Running composer update google/cloud-bigquery Loading composer repositories with package information Updating dependencies ... No security vulnerability advisories found Using version ^1.24 for google/cloud-bigquery
In the Explorer pane, locate your
BIGQUERY-PHP-QUICKSTART
project.Click the
app.php
file to open it.To create a query against the
bigquery-public-data.stackoverflow
dataset that returns the top 10 most viewed Stack Overflow pages and their view counts, copy the following code into theapp.php
file:In the terminal, run the
app.php
script. If you are prompted to authorize Cloud Shell and agree to the terms, click Authorize.php app.php
The result is similar to the following:
--- Row 1 --- url: https://stackoverflow.com/questions/35159967, 170023 views --- Row 2 --- url: https://stackoverflow.com/questions/22879669, 142581 views --- Row 3 --- url: https://stackoverflow.com/questions/10604135, 132406 views --- Row 4 --- url: https://stackoverflow.com/questions/44564887, 128781 views --- Row 5 --- url: https://stackoverflow.com/questions/27060396, 127008 views --- Row 6 --- url: https://stackoverflow.com/questions/12482637, 120766 views --- Row 7 --- url: https://stackoverflow.com/questions/20673986, 115720 views --- Row 8 --- url: https://stackoverflow.com/questions/39109817, 108368 views --- Row 9 --- url: https://stackoverflow.com/questions/11057219, 105175 views --- Row 10 --- url: https://stackoverflow.com/questions/43195143, 101878 views Found 10 row(s)
You have successfully queried a public dataset with the BigQuery PHP client library.
Python
In Cloud Shell, create a new Python project and file:
mkdir bigquery-python-quickstart \ && touch \ bigquery-python-quickstart/app.py
This command creates a Python project that's named
bigquery-python-quickstart
and a file that's namedapp.py
.Open the Cloud Shell Editor:
cloudshell workspace bigquery-python-quickstart
To open a terminal in the Cloud Shell Editor, click Terminal > New Terminal.
Install the BigQuery client library for Python:
pip install --upgrade google-cloud-bigquery
The output is similar to the following. Several lines are omitted to simplify the output.
Installing collected packages: google-cloud-bigquery ... Successfully installed google-cloud-bigquery-3.9.0 ...
In the Explorer pane, locate your
BIGQUERY-PYTHON-QUICKSTART
project.Click the
app.py
file to open it.To create a query against the
bigquery-public-data.stackoverflow
dataset that returns the top 10 most viewed Stack Overflow pages and their view counts, copy the following code into theapp.py
file:In the terminal, run the
app.py
script. If you are prompted to authorize Cloud Shell and agree to the terms, click Authorize.python app.py
The result is similar to the following:
https://stackoverflow.com/questions/35159967 : 170023 views https://stackoverflow.com/questions/22879669 : 142581 views https://stackoverflow.com/questions/10604135 : 132406 views https://stackoverflow.com/questions/44564887 : 128781 views https://stackoverflow.com/questions/27060396 : 127008 views https://stackoverflow.com/questions/12482637 : 120766 views https://stackoverflow.com/questions/20673986 : 115720 views https://stackoverflow.com/questions/39109817 : 108368 views https://stackoverflow.com/questions/11057219 : 105175 views https://stackoverflow.com/questions/43195143 : 101878 views
You have successfully queried a public dataset with the BigQuery Python client library.
Ruby
In Cloud Shell, create a new Ruby project and file:
mkdir bigquery-ruby-quickstart \ && touch \ bigquery-ruby-quickstart/app.rb
This command creates a Ruby project that's named
bigquery-ruby-quickstart
and a file that's namedapp.rb
.Open the Cloud Shell Editor:
cloudshell workspace bigquery-ruby-quickstart
To open a terminal in the Cloud Shell Editor, click Terminal > New Terminal.
Install the BigQuery client library for Ruby:
gem install google-cloud-bigquery
The output is similar to the following. Several lines are omitted to simplify the output.
23 gems installed
In the Explorer pane, locate your
BIGQUERY-RUBY-QUICKSTART
project.Click the
app.rb
file to open it.To create a query against the
bigquery-public-data.stackoverflow
dataset that returns the top 10 most viewed Stack Overflow pages and their view counts, copy the following code into theapp.rb
file:In the terminal, run the
app.rb
script. If you are prompted to authorize Cloud Shell and agree to the terms, click Authorize.ruby app.rb
The result is similar to the following:
https://stackoverflow.com/questions/35159967: 170023 views https://stackoverflow.com/questions/22879669: 142581 views https://stackoverflow.com/questions/10604135: 132406 views https://stackoverflow.com/questions/44564887: 128781 views https://stackoverflow.com/questions/27060396: 127008 views https://stackoverflow.com/questions/12482637: 120766 views https://stackoverflow.com/questions/20673986: 115720 views https://stackoverflow.com/questions/39109817: 108368 views https://stackoverflow.com/questions/11057219: 105175 views https://stackoverflow.com/questions/43195143: 101878 views
You have successfully queried a public dataset with the BigQuery Ruby client library.
Clean up
To avoid incurring charges to your Google Cloud account, either delete your Google Cloud project, or delete the resources that you created in this walkthrough.
Delete the project
The easiest way to eliminate billing is to delete the project that you created for the tutorial.
To delete the project:
- In the Google Cloud console, go to the Manage resources page.
- In the project list, select the project that you want to delete, and then click Delete.
- In the dialog, type the project ID, and then click Shut down to delete the project.
Delete the resources
If you used an existing project, delete the resources that you created:
C#
In Cloud Shell, move up a directory:
cd ..
Delete the
BigQueryCsharpDemo
folder that you created:rm -R BigQueryCsharpDemo
The
-R
flag deletes all assets in a folder.
Go
In Cloud Shell, move up a directory:
cd ..
Delete the
bigquery-go-quickstart
folder that you created:rm -R bigquery-go-quickstart
The
-R
flag deletes all assets in a folder.
Java
In Cloud Shell, move up a directory:
cd ..
Delete the
bigquery-java-quickstart
folder that you created:rm -R bigquery-java-quickstart
The
-R
flag deletes all assets in a folder.
Node.js
In Cloud Shell, move up a directory:
cd ..
Delete the
bigquery-node-quickstart
folder that you created:rm -R bigquery-node-quickstart
The
-R
flag deletes all assets in a folder.
PHP
In Cloud Shell, move up a directory:
cd ..
Delete the
bigquery-php-quickstart
folder that you created:rm -R bigquery-php-quickstart
The
-R
flag deletes all assets in a folder.
Python
In Cloud Shell, move up a directory:
cd ..
Delete the
bigquery-python-quickstart
folder that you created:rm -R bigquery-python-quickstart
The
-R
flag deletes all assets in a folder.
Ruby
In Cloud Shell, move up a directory:
cd ..
Delete the
bigquery-ruby-quickstart
folder that you created:rm -R bigquery-ruby-quickstart
The
-R
flag deletes all assets in a folder.
What's next
- Learn more about using the BigQuery client libraries.
- Learn more about BigQuery public datasets.
- Learn how to load data into BigQuery.
- Learn more about querying data in BigQuery.
- Get updates about BigQuery.
- Learn about BigQuery pricing.
- Learn about BigQuery quotas and limits.