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