Query script

  import 
  
 com.google.api.gax.paging. Page 
 
 ; 
 import 
  
 com.google.cloud.bigquery. BigQuery 
 
 ; 
 import 
  
 com.google.cloud.bigquery. BigQueryException 
 
 ; 
 import 
  
 com.google.cloud.bigquery. BigQueryOptions 
 
 ; 
 import 
  
 com.google.cloud.bigquery. Job 
 
 ; 
 import 
  
 com.google.cloud.bigquery. JobInfo 
 
 ; 
 import 
  
 com.google.cloud.bigquery. QueryJobConfiguration 
 
 ; 
 // Sample to run query script. 
 public 
  
 class 
 QueryScript 
  
 { 
  
 public 
  
 static 
  
 void 
  
 main 
 ( 
 String 
 [] 
  
 args 
 ) 
  
 { 
  
 String 
  
 script 
  
 = 
  
 "-- Declare a variable to hold names as an array.\n" 
  
 + 
  
 "DECLARE top_names ARRAY<STRING>;\n" 
  
 + 
  
 "-- Build an array of the top 100 names from the year 2017.\n" 
  
 + 
  
 "SET top_names = (\n" 
  
 + 
  
 "  SELECT ARRAY_AGG(name ORDER BY number DESC LIMIT 100)\n" 
  
 + 
  
 "  FROM `bigquery-public-data`.usa_names.usa_1910_current\n" 
  
 + 
  
 "  WHERE year = 2017\n" 
  
 + 
  
 ");\n" 
  
 + 
  
 "-- Which names appear as words in Shakespeare's plays?\n" 
  
 + 
  
 "SELECT\n" 
  
 + 
  
 "  name AS shakespeare_name\n" 
  
 + 
  
 "FROM UNNEST(top_names) AS name\n" 
  
 + 
  
 "WHERE name IN (\n" 
  
 + 
  
 "  SELECT word\n" 
  
 + 
  
 "  FROM `bigquery-public-data`.samples.shakespeare\n" 
  
 + 
  
 ");" 
 ; 
  
 queryScript 
 ( 
 script 
 ); 
  
 } 
  
 public 
  
 static 
  
 void 
  
 queryScript 
 ( 
 String 
  
 script 
 ) 
  
 { 
  
 try 
  
 { 
  
 // Initialize client that will be used to send requests. This client only needs to be created 
  
 // once, and can be reused for multiple requests. 
  
  BigQuery 
 
  
 bigquery 
  
 = 
  
  BigQueryOptions 
 
 . 
 getDefaultInstance 
 (). 
 getService 
 (); 
  
  QueryJobConfiguration 
 
  
 queryConfig 
  
 = 
  
  QueryJobConfiguration 
 
 . 
 newBuilder 
 ( 
 script 
 ). 
 build 
 (); 
  
  Job 
 
  
 createJob 
  
 = 
  
 bigquery 
 . 
  create 
 
 ( 
 JobInfo 
 . 
 of 
 ( 
 queryConfig 
 )); 
  
 // Wait for the whole script to finish. 
  
  JobInfo 
 
  
 jobInfo 
  
 = 
  
 createJob 
 . 
  waitFor 
 
 (); 
  
 String 
  
 parentJobId 
  
 = 
  
 jobInfo 
 . 
  getJobId 
 
 (). 
 getJob 
 (); 
  
 // Fetch jobs created by the SQL script. 
  
 Page<Job> 
  
 childJobs 
  
 = 
  
 bigquery 
 . 
  listJobs 
 
 ( 
 BigQuery 
 . 
 JobListOption 
 . 
 parentJobId 
 ( 
 parentJobId 
 )); 
  
 childJobs 
  
 . 
 iterateAll 
 () 
  
 . 
 forEach 
 ( 
 job 
  
 - 
>  
 System 
 . 
 out 
 . 
 printf 
 ( 
 "Child Job Id: " 
 , 
  
 job 
 . 
 getJobId 
 (). 
 getJob 
 ())); 
  
 System 
 . 
 out 
 . 
 println 
 ( 
 "Query script performed successfully." 
 ); 
  
 } 
  
 catch 
  
 ( 
  BigQueryException 
 
  
 | 
  
 InterruptedException 
  
 e 
 ) 
  
 { 
  
 System 
 . 
 out 
 . 
 println 
 ( 
 "Query not performed \n" 
  
 + 
  
 e 
 . 
 toString 
 ()); 
  
 } 
  
 } 
 } 
 

  from 
  
 google.cloud 
  
 import 
  bigquery 
 
 # Construct a BigQuery client object. 
 client 
 = 
  bigquery 
 
 . 
  Client 
 
 () 
 # Run a SQL script. 
 sql_script 
 = 
 """ 
 -- Declare a variable to hold names as an array. 
 DECLARE top_names ARRAY<STRING>; 
 -- Build an array of the top 100 names from the year 2017. 
 SET top_names = ( 
 SELECT ARRAY_AGG(name ORDER BY number DESC LIMIT 100) 
 FROM `bigquery-public-data.usa_names.usa_1910_2013` 
 WHERE year = 2000 
 ); 
 -- Which names appear as words in Shakespeare's plays? 
 SELECT 
 name AS shakespeare_name 
 FROM UNNEST(top_names) AS name 
 WHERE name IN ( 
 SELECT word 
 FROM `bigquery-public-data.samples.shakespeare` 
 ); 
 """ 
 parent_job 
 = 
 client 
 . 
  query 
 
 ( 
 sql_script 
 ) 
 # Wait for the whole script to finish. 
 rows_iterable 
 = 
 parent_job 
 . 
 result 
 () 
 print 
 ( 
 "Script created 
 {} 
 child jobs." 
 . 
 format 
 ( 
 parent_job 
 . 
 num_child_jobs 
 )) 
 # Fetch result rows for the final sub-job in the script. 
 rows 
 = 
 list 
 ( 
 rows_iterable 
 ) 
 print 
 ( 
 " 
 {} 
 of the top 100 names from year 2000 also appear in Shakespeare's works." 
 . 
 format 
 ( 
 len 
 ( 
 rows 
 ) 
 ) 
 ) 
 # Fetch jobs created by the SQL script. 
 child_jobs_iterable 
 = 
 client 
 . 
  list_jobs 
 
 ( 
 parent_job 
 = 
 parent_job 
 ) 
 for 
 child_job 
 in 
 child_jobs_iterable 
 : 
 child_rows 
 = 
 list 
 ( 
 child_job 
 . 
 result 
 ()) 
 print 
 ( 
 "Child job with ID 
 {} 
 produced 
 {} 
 row(s)." 
 . 
 format 
 ( 
 child_job 
 . 
 job_id 
 , 
 len 
 ( 
 child_rows 
 ) 
 ) 
 )