建立 Google 雲端硬碟外部資料表

bq mk \ --external_table_definition=DEFINITION_FILE \ DATASET.TABLE

bq mk --external_table_definition=/tmp/mytable_def mydataset.mytable 

bq mk \ --external_table_definition=SCHEMA@SOURCE_FORMAT=DRIVE_URI \ DATASET.TABLE

bq mk \ --external_table_definition=Region:STRING,Quarter:STRING,Total_sales:INTEGER@GOOGLE_SHEETS=https://drive.google.com/open?id=1234_AbCD12abCd \ mydataset.sales 

bq mk \ --external_table_definition=SCHEMA_FILE@SOURCE_FORMAT=DRIVE_URI \ DATASET.TABLE

bq mk \ --external_table_definition=/tmp/sales_schema.json@CSV=https://drive.google.com/open?id=1234_AbCD12abCd \ mydataset.sales 

from google.cloud import bigquery import google.auth  credentials, project = google.auth.default()  # 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_options assert options is not None options.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)     ) )

import com.google.auth.oauth2.GoogleCredentials; import com.google.auth.oauth2.ServiceAccountCredentials; import com.google.cloud.bigquery.BigQuery; import com.google.cloud.bigquery.BigQueryException; import com.google.cloud.bigquery.BigQueryOptions; import com.google.cloud.bigquery.ExternalTableDefinition; import com.google.cloud.bigquery.Field; import com.google.cloud.bigquery.GoogleSheetsOptions; import com.google.cloud.bigquery.QueryJobConfiguration; import com.google.cloud.bigquery.Schema; import com.google.cloud.bigquery.StandardSQLTypeName; import com.google.cloud.bigquery.TableId; import com.google.cloud.bigquery.TableInfo; import com.google.cloud.bigquery.TableResult; import com.google.common.collect.ImmutableSet; import java.io.IOException;  // Sample to queries an external data source using a permanent table public class QueryExternalSheetsPerm {    public static void main(String[] args) {     // TODO(developer): Replace these variables before running the sample.     String datasetName = "MY_DATASET_NAME";     String tableName = "MY_TABLE_NAME";     String sourceUri =         "https://docs.google.com/spreadsheets/d/1i_QCL-7HcSyUZmIbP9E6lO_T5u3HnpLe7dnpHaijg_E/edit?usp=sharing";     Schema schema =         Schema.of(             Field.of("name", StandardSQLTypeName.STRING),             Field.of("post_abbr", StandardSQLTypeName.STRING));     String query =         String.format("SELECT * FROM %s.%s WHERE name LIKE 'W%%'", datasetName, tableName);     queryExternalSheetsPerm(datasetName, tableName, sourceUri, schema, query);   }    public static void queryExternalSheetsPerm(       String datasetName, String tableName, String sourceUri, Schema schema, String query) {     try {        GoogleCredentials credentials =           ServiceAccountCredentials.getApplicationDefault();        // 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.newBuilder().setCredentials(credentials).build().getService();        // Skip header row in the file.       GoogleSheetsOptions sheetsOptions =           GoogleSheetsOptions.newBuilder()               .setSkipLeadingRows(1) // Optionally skip header row.               .setRange("us-states!A20:B49") // Optionally set range of the sheet to query from.               .build();        TableId tableId = TableId.of(datasetName, tableName);       // Create a permanent table linked to the Sheets file.       ExternalTableDefinition externalTable =           ExternalTableDefinition.newBuilder(sourceUri, sheetsOptions).setSchema(schema).build();       bigquery.create(TableInfo.of(tableId, externalTable));        // Example query to find states starting with 'W'       TableResult results = 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 | IOException e) {       System.out.println("Query not performed \n" + e.toString());     }   } }