Query a public dataset with the BigQuery client libraries

C#

1. In Cloud Shell, create a new C# project and file:

   dotnetnewconsole-nBigQueryCsharpDemo

   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 named Program.cs.

2. Open the Cloud Shell Editor:

   cloudshellworkspaceBigQueryCsharpDemo

3. To open a terminal in the Cloud Shell Editor, click Open Terminal.

4. Open your project directory:

   cdBigQueryCsharpDemo

5. Install the BigQuery client library for C#:

   dotnetaddpackageGoogle.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. ... 

6. Set the variable GOOGLE_PROJECT_ID to the value GOOGLE_CLOUD_PROJECT and export the variable:

   exportGOOGLE_PROJECT_ID=$GOOGLE_CLOUD_PROJECT

7. Click Open Editor.

8. In the Explorer pane, locate your BIGQUERYCSHARPDEMO project.

9. Click the Program.cs file to open it.

10. 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:

    usingSystem;usingGoogle.Cloud.BigQuery.V2;namespaceGoogleCloudSamples{publicclassProgram{publicstaticvoidMain(string[]args){stringprojectId=Environment.GetEnvironmentVariable("GOOGLE_PROJECT_ID");varclient=BigQueryClient.Create(projectId);stringquery=@"SELECT CONCAT( 'https://stackoverflow.com/questions/', CAST(id as STRING)) as url, view_count FROM `bigquery-public-data.stackoverflow.posts_questions` WHERE tags like '%google-bigquery%' ORDER BY view_count DESC LIMIT 10";varresult=client.ExecuteQuery(query,parameters:null);Console.Write("\nQuery Results:\n------------\n");foreach(varrowinresult){Console.WriteLine($"{row["url"]}: {row["view_count"]} views");}}}}

11. Click Open Terminal.

12. In the terminal, run the Program.cs script. If you are prompted to authorize Cloud Shell and agree to the terms, click Authorize.

    dotnetrun

    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

1. In Cloud Shell, create a new Go project and file:

   mkdirbigquery-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 named app.go.

2. Open the Cloud Shell Editor:

   cloudshellworkspacebigquery-go-quickstart

3. To open a terminal in the Cloud Shell Editor, click Open Terminal.

4. Open your project directory:

   cdbigquery-go-quickstart

5. Create a go.mod file:

   gomodinitquickstart

   The output is similar to the following:

    go: creating new go.mod: module quickstart go: to add module requirements and sums: go mod tidy 

6. Install the BigQuery client library for Go:

   gogetcloud.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 

7. Click Open Editor.

8. In the Explorer pane, locate your BIGQUERY-GO-QUICKSTART project.

9. Click the app.go file to open it.

10. 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 the app.go file:

    // Command simpleapp queries the Stack Overflow public dataset in Google BigQuery.packagemainimport("context""fmt""io""log""os""cloud.google.com/go/bigquery""google.golang.org/api/iterator")funcmain(){projectID:=os.Getenv("GOOGLE_CLOUD_PROJECT")ifprojectID==""{fmt.Println("GOOGLE_CLOUD_PROJECT environment variable must be set.")os.Exit(1)}ctx:=context.Background()client,err:=bigquery.NewClient(ctx,projectID)iferr!=nil{log.Fatalf("bigquery.NewClient: %v",err)}deferclient.Close()rows,err:=query(ctx,client)iferr!=nil{log.Fatal(err)}iferr:=printResults(os.Stdout,rows);err!=nil{log.Fatal(err)}}// query returns a row iterator suitable for reading query results.funcquery(ctxcontext.Context,client*bigquery.Client)(*bigquery.RowIterator,error){query:=client.Query(`SELECT CONCAT( 'https://stackoverflow.com/questions/', CAST(id as STRING)) as url, view_count FROM `+"`bigquery-public-data.stackoverflow.posts_questions`"+` WHERE tags like '%google-bigquery%' ORDER BY view_count DESC LIMIT 10;`)returnquery.Read(ctx)}typeStackOverflowRowstruct{URLstring`bigquery:"url"`ViewCountint64`bigquery:"view_count"`}// printResults prints results from a query to the Stack Overflow public dataset.funcprintResults(wio.Writer,iter*bigquery.RowIterator)error{for{varrowStackOverflowRowerr:=iter.Next(&row)iferr==iterator.Done{returnnil}iferr!=nil{returnfmt.Errorf("error iterating through results: %w",err)}fmt.Fprintf(w,"url: %s views: %d\n",row.URL,row.ViewCount)}}

11. Click Open Terminal.

12. In the terminal, run the app.go script. If you are prompted to authorize Cloud Shell and agree to the terms, click Authorize.

    gorunapp.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

1. In Cloud Shell, create a new Java project using Apache Maven:

   mvnarchetype: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.

2. 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

3. Open the Cloud Shell Editor:

   cloudshellworkspacebigquery-java-quickstart

4. 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:

   1. Click File > Preferences > Open Settings (UI).
   2. Click Extensions > Java.
   3. Scroll to Configuration: Update Build Configuration and select automatic.

5. In the Explorer pane, locate your BIGQUERY-JAVA-QUICKSTART project.

6. Click the pom.xml file to open it.

7. 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> 

8. 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> 

9. In the Explorer pane, in your BIGQUERY-JAVA-QUICKSTART project, click src > main/java/com/google/app > SimpleApp.java. The file opens.

10. 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:

    importcom.google.cloud.bigquery.BigQuery;importcom.google.cloud.bigquery.BigQueryException;importcom.google.cloud.bigquery.BigQueryOptions;importcom.google.cloud.bigquery.FieldValueList;importcom.google.cloud.bigquery.Job;importcom.google.cloud.bigquery.JobId;importcom.google.cloud.bigquery.JobInfo;importcom.google.cloud.bigquery.QueryJobConfiguration;importcom.google.cloud.bigquery.TableResult;publicclassSimpleApp{publicstaticvoidmain(String...args)throwsException{// TODO(developer): Replace these variables before running the app.StringprojectId="MY_PROJECT_ID";simpleApp(projectId);}publicstaticvoidsimpleApp(StringprojectId){try{BigQuerybigquery=BigQueryOptions.getDefaultInstance().getService();QueryJobConfigurationqueryConfig=QueryJobConfiguration.newBuilder("SELECT CONCAT('https://stackoverflow.com/questions/', "+"CAST(id as STRING)) as url, view_count "+"FROM `bigquery-public-data.stackoverflow.posts_questions` "+"WHERE tags like '%google-bigquery%' "+"ORDER BY view_count DESC "+"LIMIT 10")// Use standard SQL syntax for queries.// See: https://cloud.google.com/bigquery/sql-reference/.setUseLegacySql(false).build();JobIdjobId=JobId.newBuilder().setProject(projectId).build();JobqueryJob=bigquery.create(JobInfo.newBuilder(queryConfig).setJobId(jobId).build());// Wait for the query to complete.queryJob=queryJob.waitFor();// Check for errorsif(queryJob==null){thrownewRuntimeException("Job no longer exists");}elseif(queryJob.getStatus().getExecutionErrors()!=null && queryJob.getStatus().getExecutionErrors().size() > 0){// TODO(developer): Handle errors here. An error here do not necessarily mean that the job// has completed or was unsuccessful.// For more details: https://cloud.google.com/bigquery/troubleshooting-errorsthrownewRuntimeException("An unhandled error has occurred");}// Get the results.TableResultresult=queryJob.getQueryResults();// Print all pages of the results.for(FieldValueListrow:result.iterateAll()){// String typeStringurl=row.get("url").getStringValue();StringviewCount=row.get("view_count").getStringValue();System.out.printf("%s : %s views\n",url,viewCount);}}catch(BigQueryException|InterruptedExceptione){System.out.println("Simple App failed due to error: \n"+e.toString());}}}

    The query returns the top 10 most viewed Stack Overflow pages and their view counts.

11. 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

1. In Cloud Shell, create a new Node.js project and file:

   mkdirbigquery-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 named app.js.

2. Open the Cloud Shell Editor:

   cloudshellworkspacebigquery-node-quickstart

3. To open a terminal in the Cloud Shell Editor, click Open Terminal.

4. Open your project directory:

   cdbigquery-node-quickstart

5. Install the BigQuery client library for Node.js:

   npminstall--save@google-cloud/bigquery

   The output is similar to the following:

    added 63 packages in 2s 

6. Click Open Editor.

7. In the Explorer pane, locate your BIGQUERY-NODE-QUICKSTART project.

8. Click the app.js file to open it.

9. 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 the app.js file:

   // Import the Google Cloud client libraryconst{BigQuery}=require('@google-cloud/bigquery');asyncfunctionqueryStackOverflow(){// Queries a public Stack Overflow dataset.// Create a clientconstbigqueryClient=newBigQuery();// The SQL query to runconstsqlQuery=`SELECT CONCAT( 'https://stackoverflow.com/questions/', CAST(id as STRING)) as url, view_count FROM \`bigquery-public-data.stackoverflow.posts_questions\` WHERE tags like '%google-bigquery%' ORDER BY view_count DESC LIMIT 10`;constoptions={query:sqlQuery,// Location must match that of the dataset(s) referenced in the query.location:'US',};// Run the queryconst[rows]=awaitbigqueryClient.query(options);console.log('Query Results:');rows.forEach(row=>{consturl=row['url'];constviewCount=row['view_count'];console.log(`url: ${url}, ${viewCount} views`);});}queryStackOverflow();

10. Click Open Terminal.

11. In the terminal, run the app.js script. If you are prompted to authorize Cloud Shell and agree to the terms, click Authorize.

    nodeapp.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

1. In Cloud Shell, create a new PHP project and file:

   mkdirbigquery-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 named app.php.

2. Open the Cloud Shell Editor:

   cloudshellworkspacebigquery-php-quickstart

3. To open a terminal in the Cloud Shell Editor, click Open Terminal.

4. Open your project directory:

   cdbigquery-php-quickstart

5. Install the BigQuery client library for PHP:

   composerrequiregoogle/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 

6. Click Open Editor.

7. In the Explorer pane, locate your BIGQUERY-PHP-QUICKSTART project.

8. Click the app.php file to open it.

9. 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 the app.php file:

   <?php# ...require __DIR__ . '/vendor/autoload.php';use Google\Cloud\BigQuery\BigQueryClient;$bigQuery = new BigQueryClient();$query = <<<ENDSQLSELECT CONCAT( 'https://stackoverflow.com/questions/', CAST(id as STRING)) as url, view_countFROM `bigquery-public-data.stackoverflow.posts_questions`WHERE tags like '%google-bigquery%'ORDER BY view_count DESCLIMIT 10;ENDSQL;$queryJobConfig = $bigQuery->query($query);$queryResults = $bigQuery->runQuery($queryJobConfig);if ($queryResults->isComplete()) { $i = 0; $rows = $queryResults->rows(); foreach ($rows as $row) { printf('--- Row %s ---' . PHP_EOL, ++$i); printf('url: %s, %s views' . PHP_EOL, $row['url'], $row['view_count']); } printf('Found %s row(s)' . PHP_EOL, $i);} else { throw new Exception('The query failed to complete');}

10. Click Open Terminal.

11. In the terminal, run the app.php script. If you are prompted to authorize Cloud Shell and agree to the terms, click Authorize.

    phpapp.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

1. In Cloud Shell, create a new Python project and file:

   mkdirbigquery-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 named app.py.

2. Open the Cloud Shell Editor:

   cloudshellworkspacebigquery-python-quickstart

3. To open a terminal in the Cloud Shell Editor, click Open Terminal.

4. Open your project directory:

   cdbigquery-python-quickstart

5. Install the BigQuery client library for Python:

   pipinstall--upgradegoogle-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 ... 

6. Click Open Editor.

7. In the Explorer pane, locate your BIGQUERY-PYTHON-QUICKSTART project.

8. Click the app.py file to open it.

9. 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 the app.py file:

   fromgoogle.cloudimportbigquerydefquery_stackoverflow():client=bigquery.Client()query_job=client.query(""" SELECT CONCAT( 'https://stackoverflow.com/questions/', CAST(id as STRING)) as url, view_count FROM `bigquery-public-data.stackoverflow.posts_questions` WHERE tags like '%google-bigquery%' ORDER BY view_count DESC LIMIT 10""")results=query_job.result()# Waits for job to complete.forrowinresults:print("{} : {} views".format(row.url,row.view_count))if__name__=="__main__":query_stackoverflow()

10. Click Open Terminal.

11. In the terminal, run the app.py script. If you are prompted to authorize Cloud Shell and agree to the terms, click Authorize.

    pythonapp.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

1. In Cloud Shell, create a new Ruby project and file:

   mkdirbigquery-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 named app.rb.

2. Open the Cloud Shell Editor:

   cloudshellworkspacebigquery-ruby-quickstart

3. To open a terminal in the Cloud Shell Editor, click Open Terminal.

4. Open your project directory:

   cdbigquery-ruby-quickstart

5. Install the BigQuery client library for Ruby:

   geminstallgoogle-cloud-bigquery

   The output is similar to the following. Several lines are omitted to simplify the output.

    23 gems installed 

6. Click Open Editor.

7. In the Explorer pane, locate your BIGQUERY-RUBY-QUICKSTART project.

8. Click the app.rb file to open it.

9. 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 the app.rb file:

   require"google/cloud/bigquery"# This uses Application Default Credentials to authenticate.# @see https://cloud.google.com/bigquery/docs/authentication/getting-startedbigquery=Google::Cloud::Bigquery.newsql="SELECT "\"CONCAT('https://stackoverflow.com/questions/', CAST(id as STRING)) as url, view_count "\"FROM `bigquery-public-data.stackoverflow.posts_questions` "\"WHERE tags like '%google-bigquery%' "\"ORDER BY view_count DESC LIMIT 10"results=bigquery.querysqlresults.eachdo|row|puts"#{row[:url]}: #{row[:view_count]} views"end

10. Click Open Terminal.

11. In the terminal, run the app.rb script. If you are prompted to authorize Cloud Shell and agree to the terms, click Authorize.

    rubyapp.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.

C#

1. In Cloud Shell, move up a directory:

   cd..

2. Delete the BigQueryCsharpDemo folder that you created:

   rm-RBigQueryCsharpDemo

   The -R flag deletes all assets in a folder.

Go

1. In Cloud Shell, move up a directory:

   cd..

2. Delete the bigquery-go-quickstart folder that you created:

   rm-Rbigquery-go-quickstart

   The -R flag deletes all assets in a folder.

Java

1. In Cloud Shell, move up a directory:

   cd..

2. Delete the bigquery-java-quickstart folder that you created:

   rm-Rbigquery-java-quickstart

   The -R flag deletes all assets in a folder.

Node.js

1. In Cloud Shell, move up a directory:

   cd..

2. Delete the bigquery-node-quickstart folder that you created:

   rm-Rbigquery-node-quickstart

   The -R flag deletes all assets in a folder.

PHP

1. In Cloud Shell, move up a directory:

   cd..

2. Delete the bigquery-php-quickstart folder that you created:

   rm-Rbigquery-php-quickstart

   The -R flag deletes all assets in a folder.

Python

1. In Cloud Shell, move up a directory:

   cd..

2. Delete the bigquery-python-quickstart folder that you created:

   rm-Rbigquery-python-quickstart

   The -R flag deletes all assets in a folder.

Ruby

1. In Cloud Shell, move up a directory:

   cd..

2. Delete the bigquery-ruby-quickstart folder that you created:

   rm-Rbigquery-ruby-quickstart

   The -R flag deletes all assets in a folder.