Query Cloud Storage with a temporary table

  import 
  
 com.google.cloud.bigquery. BigQuery 
 
 ; 
 import 
  
 com.google.cloud.bigquery. BigQueryException 
 
 ; 
 import 
  
 com.google.cloud.bigquery. BigQueryOptions 
 
 ; 
 import 
  
 com.google.cloud.bigquery. CsvOptions 
 
 ; 
 import 
  
 com.google.cloud.bigquery. ExternalTableDefinition 
 
 ; 
 import 
  
 com.google.cloud.bigquery. Field 
 
 ; 
 import 
  
 com.google.cloud.bigquery. QueryJobConfiguration 
 
 ; 
 import 
  
 com.google.cloud.bigquery. Schema 
 
 ; 
 import 
  
 com.google.cloud.bigquery. StandardSQLTypeName 
 
 ; 
 import 
  
 com.google.cloud.bigquery. TableResult 
 
 ; 
 // Sample to queries an external data source using a temporary table 
 public 
  
 class 
 QueryExternalGcsTemp 
  
 { 
  
 public 
  
 static 
  
 void 
  
 main 
 ( 
 String 
 [] 
  
 args 
 ) 
  
 { 
  
 // TODO(developer): Replace these variables before running the sample. 
  
 String 
  
 tableName 
  
 = 
  
 "MY_TABLE_NAME" 
 ; 
  
 String 
  
 sourceUri 
  
 = 
  
 "gs://cloud-samples-data/bigquery/us-states/us-states.csv" 
 ; 
  
  Schema 
 
  
 schema 
  
 = 
  
  Schema 
 
 . 
 of 
 ( 
  
  Field 
 
 . 
 of 
 ( 
 "name" 
 , 
  
  StandardSQLTypeName 
 
 . 
 STRING 
 ), 
  
  Field 
 
 . 
 of 
 ( 
 "post_abbr" 
 , 
  
  StandardSQLTypeName 
 
 . 
 STRING 
 )); 
  
 String 
  
 query 
  
 = 
  
 String 
 . 
 format 
 ( 
 "SELECT * FROM %s WHERE name LIKE 'W%%'" 
 , 
  
 tableName 
 ); 
  
 queryExternalGcsTemp 
 ( 
 tableName 
 , 
  
 sourceUri 
 , 
  
 schema 
 , 
  
 query 
 ); 
  
 } 
  
 public 
  
 static 
  
 void 
  
 queryExternalGcsTemp 
 ( 
  
 String 
  
 tableName 
 , 
  
 String 
  
 sourceUri 
 , 
  
  Schema 
 
  
 schema 
 , 
  
 String 
  
 query 
 ) 
  
 { 
  
 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 
 (); 
  
 // Skip header row in the file. 
  
  CsvOptions 
 
  
 csvOptions 
  
 = 
  
  CsvOptions 
 
 . 
 newBuilder 
 (). 
 setSkipLeadingRows 
 ( 
 1 
 ). 
 build 
 (); 
  
 // Configure the external data source and query job. 
  
  ExternalTableDefinition 
 
  
 externalTable 
  
 = 
  
  ExternalTableDefinition 
 
 . 
 newBuilder 
 ( 
 sourceUri 
 , 
  
 csvOptions 
 ). 
 setSchema 
 ( 
 schema 
 ). 
 build 
 (); 
  
  QueryJobConfiguration 
 
  
 queryConfig 
  
 = 
  
  QueryJobConfiguration 
 
 . 
 newBuilder 
 ( 
 query 
 ) 
  
 . 
  addTableDefinition 
 
 ( 
 tableName 
 , 
  
 externalTable 
 ) 
  
 . 
 build 
 (); 
  
 // Example query to find states starting with 'W' 
  
  TableResult 
 
  
 results 
  
 = 
  
 bigquery 
 . 
  query 
 
 ( 
 queryConfig 
 ); 
  
 results 
  
 . 
  iterateAll 
 
 () 
  
 . 
 forEach 
 ( 
 row 
  
 - 
>  
 row 
 . 
 forEach 
 ( 
 val 
  
 - 
>  
 System 
 . 
 out 
 . 
 printf 
 ( 
 "%s," 
 , 
  
 val 
 . 
 toString 
 ()))); 
  
 System 
 . 
 out 
 . 
 println 
 ( 
 "Query on external temporary table performed successfully." 
 ); 
  
 } 
  
 catch 
  
 ( 
  BigQueryException 
 
  
 | 
  
 InterruptedException 
  
 e 
 ) 
  
 { 
  
 System 
 . 
 out 
 . 
 println 
 ( 
 "Query not performed \n" 
  
 + 
  
 e 
 . 
 toString 
 ()); 
  
 } 
  
 } 
 } 
 

  // Import the Google Cloud client library and create a client 
 const 
  
 { 
 BigQuery 
 } 
  
 = 
  
 require 
 ( 
 ' @google-cloud/bigquery 
' 
 ); 
 const 
  
 bigquery 
  
 = 
  
 new 
  
  BigQuery 
 
 (); 
 async 
  
 function 
  
 queryExternalGCSTemp 
 () 
  
 { 
  
 // Queries an external data source using a temporary table. 
  
 const 
  
 tableId 
  
 = 
  
 'us_states' 
 ; 
  
 // Configure the external data source 
  
 const 
  
 externalDataConfig 
  
 = 
  
 { 
  
 sourceFormat 
 : 
  
 'CSV' 
 , 
  
 sourceUris 
 : 
  
 [ 
 'gs://cloud-samples-data/bigquery/us-states/us-states.csv' 
 ], 
  
 // Optionally skip header row. 
  
 csvOptions 
 : 
  
 { 
 skipLeadingRows 
 : 
  
 1 
 }, 
  
 schema 
 : 
  
 { 
 fields 
 : 
  
 schema 
 }, 
  
 }; 
  
 // Example query to find states starting with 'W' 
  
 const 
  
 query 
  
 = 
  
 `SELECT post_abbr 
 FROM \` 
 ${ 
 tableId 
 } 
 \` 
 WHERE name LIKE 'W%'` 
 ; 
  
 // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource 
  
 const 
  
 options 
  
 = 
  
 { 
  
 query 
 , 
  
 tableDefinitions 
 : 
  
 {[ 
 tableId 
 ] 
 : 
  
 externalDataConfig 
 }, 
  
 }; 
  
 // Run the query as a job 
  
 const 
  
 [ 
 job 
 ] 
  
 = 
  
 await 
  
 bigquery 
 . 
 createQueryJob 
 ( 
 options 
 ); 
  
 console 
 . 
 log 
 ( 
 `Job 
 ${ 
  job 
 
 . 
 id 
 } 
 started.` 
 ); 
  
 // Wait for the query to finish 
  
 const 
  
 [ 
 rows 
 ] 
  
 = 
  
 await 
  
  job 
 
 . 
  getQueryResults 
 
 (); 
  
 // Print the results 
  
 console 
 . 
 log 
 ( 
 'Rows:' 
 ); 
  
 console 
 . 
 log 
 ( 
 rows 
 ); 
 } 
 

  from 
  
 google.cloud 
  
 import 
  bigquery 
 
 # Construct a BigQuery client object. 
 client 
 = 
  bigquery 
 
 . 
  Client 
 
 () 
 # Configure the external data source and query job. 
 external_config 
 = 
  bigquery 
 
 . 
  ExternalConfig 
 
 ( 
 "CSV" 
 ) 
  external_config 
 
 . 
 source_uris 
 = 
 [ 
 "gs://cloud-samples-data/bigquery/us-states/us-states.csv" 
 ] 
  external_config 
 
 . 
 schema 
 = 
 [ 
  bigquery 
 
 . 
  SchemaField 
 
 ( 
 "name" 
 , 
 "STRING" 
 ), 
  bigquery 
 
 . 
  SchemaField 
 
 ( 
 "post_abbr" 
 , 
 "STRING" 
 ), 
 ] 
 assert 
  external_config 
 
 . 
  csv_options 
 
 is 
 not 
 None 
  external_config 
 
 . 
  csv_options 
 
 . 
 skip_leading_rows 
 = 
 1 
 table_id 
 = 
 "us_states" 
 job_config 
 = 
  bigquery 
 
 . 
  QueryJobConfig 
 
 ( 
 table_definitions 
 = 
 { 
 table_id 
 : 
 external_config 
 }) 
 # Example query to find states starting with 'W'. 
 sql 
 = 
 'SELECT * FROM ` 
 {} 
 ` WHERE name LIKE "W%"' 
 . 
 format 
 ( 
 table_id 
 ) 
 results 
 = 
 client 
 . 
  query_and_wait 
 
 ( 
 sql 
 , 
 job_config 
 = 
 job_config 
 ) 
 # Make an API request. 
 w_states 
 = 
 list 
 ( 
 results 
 ) 
 # Wait for the job to complete. 
 print 
 ( 
 "There are 
 {} 
 states with names starting with W." 
 . 
 format 
 ( 
 len 
 ( 
 w_states 
 )))