importcom.google.cloud.bigquery.BigQuery;importcom.google.cloud.bigquery.BigQueryException;importcom.google.cloud.bigquery.BigQueryOptions;importcom.google.cloud.bigquery.CsvOptions;importcom.google.cloud.bigquery.ExternalTableDefinition;importcom.google.cloud.bigquery.Field;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;// Sample to queries an external data source using a permanent tablepublicclassQueryExternalGcsPerm{publicstaticvoidmain(String[]args){// TODO(developer): Replace these variables before running the sample.StringdatasetName="MY_DATASET_NAME";StringtableName="MY_TABLE_NAME";StringsourceUri="gs://cloud-samples-data/bigquery/us-states/us-states.csv";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);queryExternalGcsPerm(datasetName,tableName,sourceUri,schema,query);}publicstaticvoidqueryExternalGcsPerm(StringdatasetName,StringtableName,StringsourceUri,Schemaschema,Stringquery){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.BigQuerybigquery=BigQueryOptions.getDefaultInstance().getService();// Skip header row in the file.CsvOptionscsvOptions=CsvOptions.newBuilder().setSkipLeadingRows(1).build();TableIdtableId=TableId.of(datasetName,tableName);// Create a permanent table linked to the GCS fileExternalTableDefinitionexternalTable=ExternalTableDefinition.newBuilder(sourceUri,csvOptions).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|InterruptedExceptione){System.out.println("Query not performed \n"+e.toString());}}}

// Import the Google Cloud client library and create a clientconst{BigQuery}=require('@google-cloud/bigquery');constbigquery=newBigQuery();asyncfunctionqueryExternalGCSPerm(){// Queries an external data source using a permanent table/**   * TODO(developer): Uncomment the following lines before running the sample.   */// const datasetId = "my_dataset";// const tableId = "my_table";// Configure the external data sourceconstdataConfig={sourceFormat:'CSV',sourceUris:['gs://cloud-samples-data/bigquery/us-states/us-states.csv'],// Optionally skip header rowcsvOptions:{skipLeadingRows:1},};// For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resourceconstoptions={schema:schema,externalDataConfiguration:dataConfig,};// Create an external table linked to the GCS fileconst[table]=awaitbigquery.dataset(datasetId).createTable(tableId,options);console.log(`Table${table.id} created.`);// Example query to find states starting with 'W'constquery=`SELECT post_abbr  FROM \`${datasetId}.${tableId}\`  WHERE name LIKE 'W%'`;// Run the query as a jobconst[job]=awaitbigquery.createQueryJob(query);console.log(`Job${job.id} started.`);// Wait for the query to finishconst[rows]=awaitjob.getQueryResults();// Print the resultsconsole.log('Rows:');console.log(rows);}

fromgoogle.cloudimportbigquery# Construct a BigQuery client object.client=bigquery.Client()# TODO(developer): Set table_id to the ID of the table to create.table_id="your-project.your_dataset.your_table_name"# TODO(developer): Set the external source format of your table.# Note that the set of allowed values for external data sources is# different than the set used for loading data (see :class:`~google.cloud.bigquery.job.SourceFormat`).external_source_format="AVRO"# TODO(developer): Set the source_uris to point to your data in Google Cloudsource_uris=["gs://cloud-samples-data/bigquery/federated-formats-reference-file-schema/a-twitter.avro","gs://cloud-samples-data/bigquery/federated-formats-reference-file-schema/b-twitter.avro","gs://cloud-samples-data/bigquery/federated-formats-reference-file-schema/c-twitter.avro",]# Create ExternalConfig object with external source formatexternal_config=bigquery.ExternalConfig(external_source_format)# Set source_uris that point to your data in Google Cloudexternal_config.source_uris=source_uris# TODO(developer) You have the option to set a reference_file_schema_uri, which points to# a reference file for the table schemareference_file_schema_uri="gs://cloud-samples-data/bigquery/federated-formats-reference-file-schema/b-twitter.avro"external_config.reference_file_schema_uri=reference_file_schema_uritable=bigquery.Table(table_id)# Set the external data configuration of the tabletable.external_data_configuration=external_configtable=client.create_table(table)# Make an API request.print(f"Created table with external source format{table.external_data_configuration.source_format}")