importcom.google.auth.oauth2.GoogleCredentials;importcom.google.auth.oauth2.ServiceAccountCredentials;importcom.google.cloud.bigquery.BigQuery;importcom.google.cloud.bigquery.BigQueryException;importcom.google.cloud.bigquery.BigQueryOptions;importcom.google.cloud.bigquery.ExternalTableDefinition;importcom.google.cloud.bigquery.Field;importcom.google.cloud.bigquery.GoogleSheetsOptions;importcom.google.cloud.bigquery.QueryJobConfiguration;importcom.google.cloud.bigquery.Schema;importcom.google.cloud.bigquery.StandardSQLTypeName;importcom.google.cloud.bigquery.TableId;importcom.google.cloud.bigquery.TableInfo;importcom.google.cloud.bigquery.TableResult;importcom.google.common.collect.ImmutableSet;importjava.io.IOException;// Sample to queries an external data source using a permanent tablepublicclassQueryExternalSheetsPerm{publicstaticvoidmain(String[]args){// TODO(developer): Replace these variables before running the sample.StringdatasetName="MY_DATASET_NAME";StringtableName="MY_TABLE_NAME";StringsourceUri="https://docs.google.com/spreadsheets/d/1i_QCL-7HcSyUZmIbP9E6lO_T5u3HnpLe7dnpHaijg_E/edit?usp=sharing";Schemaschema=Schema.of(Field.of("name",StandardSQLTypeName.STRING),Field.of("post_abbr",StandardSQLTypeName.STRING));Stringquery=String.format("SELECT * FROM %s.%s WHERE name LIKE 'W%%'",datasetName,tableName);queryExternalSheetsPerm(datasetName,tableName,sourceUri,schema,query);}publicstaticvoidqueryExternalSheetsPerm(StringdatasetName,StringtableName,StringsourceUri,Schemaschema,Stringquery){try{// Create credentials with Drive & BigQuery API scopes.// Both APIs must be enabled for your project before running this code.GoogleCredentialscredentials=ServiceAccountCredentials.getApplicationDefault().createScoped(ImmutableSet.of("https://www.googleapis.com/auth/bigquery","https://www.googleapis.com/auth/drive"));// Initialize client that will be used to send requests. This client only needs to be created// once, and can be reused for multiple requests.BigQuerybigquery=BigQueryOptions.newBuilder().setCredentials(credentials).build().getService();// Skip header row in the file.GoogleSheetsOptionssheetsOptions=GoogleSheetsOptions.newBuilder().setSkipLeadingRows(1)// Optionally skip header row..setRange("us-states!A20:B49")// Optionally set range of the sheet to query from..build();TableIdtableId=TableId.of(datasetName,tableName);// Create a permanent table linked to the Sheets file.ExternalTableDefinitionexternalTable=ExternalTableDefinition.newBuilder(sourceUri,sheetsOptions).setSchema(schema).build();bigquery.create(TableInfo.of(tableId,externalTable));// Example query to find states starting with 'W'TableResultresults=bigquery.query(QueryJobConfiguration.of(query));results.iterateAll().forEach(row->row.forEach(val->System.out.printf("%s,",val.toString())));System.out.println("Query on external permanent table performed successfully.");}catch(BigQueryException|InterruptedException|IOExceptione){System.out.println("Query not performed \n"+e.toString());}}}

fromgoogle.cloudimportbigqueryimportgoogle.auth# Create credentials with Drive & BigQuery API scopes.# Both APIs must be enabled for your project before running this code.## If you are using credentials from gcloud, you must authorize the# application first with the following command:## gcloud auth application-default login \#   --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platformcredentials,project=google.auth.default(scopes=["https://www.googleapis.com/auth/drive","https://www.googleapis.com/auth/bigquery",])# Construct a BigQuery client object.client=bigquery.Client(credentials=credentials,project=project)# TODO(developer): Set dataset_id to the ID of the dataset to fetch.# dataset_id = "your-project.your_dataset"# Configure the external data source.dataset=client.get_dataset(dataset_id)table_id="us_states"schema=[bigquery.SchemaField("name","STRING"),bigquery.SchemaField("post_abbr","STRING"),]table=bigquery.Table(dataset.table(table_id),schema=schema)external_config=bigquery.ExternalConfig("GOOGLE_SHEETS")# Use a shareable link or grant viewing access to the email address you# used to authenticate with BigQuery (this example Sheet is public).sheet_url=("https://docs.google.com/spreadsheets""/d/1i_QCL-7HcSyUZmIbP9E6lO_T5u3HnpLe7dnpHaijg_E/edit?usp=sharing")external_config.source_uris=[sheet_url]options=external_config.google_sheets_optionsassertoptionsisnotNoneoptions.skip_leading_rows=1# Optionally skip header row.options.range=("us-states!A20:B49"# Optionally set range of the sheet to query from.)table.external_data_configuration=external_config# Create a permanent table linked to the Sheets file.table=client.create_table(table)# Make an API request.# Example query to find states starting with "W".sql='SELECT * FROM `{}.{}` WHERE name LIKE "W%"'.format(dataset_id,table_id)results=client.query_and_wait(sql)# Make an API request.# Wait for the query to complete.w_states=list(results)print("There are{} states with names starting with W in the selected range.".format(len(w_states)))