Query Bigtable using a temporary table

Query data from a Bigtable instance by creating a temporary table.

Explore further

For detailed documentation that includes this code sample, see the following:

Code sample

Java

Before trying this sample, follow theJava setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQueryJava API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

importcom.google.api.client.util.Base64;importcom.google.cloud.bigquery.BigQuery;importcom.google.cloud.bigquery.BigQueryException;importcom.google.cloud.bigquery.BigQueryOptions;importcom.google.cloud.bigquery.BigtableColumn;importcom.google.cloud.bigquery.BigtableColumnFamily;importcom.google.cloud.bigquery.BigtableOptions;importcom.google.cloud.bigquery.ExternalTableDefinition;importcom.google.cloud.bigquery.QueryJobConfiguration;importcom.google.cloud.bigquery.TableResult;importcom.google.common.collect.ImmutableList;// Sample to queries an external bigtable data source using a temporary tablepublicclassQueryExternalBigtableTemp{publicstaticvoidmain(String[]args){// TODO(developer): Replace these variables before running the sample.StringprojectId="MY_PROJECT_ID";StringbigtableInstanceId="MY_INSTANCE_ID";StringbigtableTableName="MY_BIGTABLE_NAME";StringbigqueryTableName="MY_TABLE_NAME";StringsourceUri=String.format("https://googleapis.com/bigtable/projects/%s/instances/%s/tables/%s",projectId,bigtableInstanceId,bigtableTableName);Stringquery=String.format("SELECT * FROM %s ",bigqueryTableName);queryExternalBigtableTemp(bigqueryTableName,sourceUri,query);}publicstaticvoidqueryExternalBigtableTemp(StringtableName,StringsourceUri,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();BigtableColumnFamily.BuilderstatsSummary=BigtableColumnFamily.newBuilder();// Configuring ColumnsBigtableColumnconnectedCell=BigtableColumn.newBuilder().setQualifierEncoded(Base64.encodeBase64String("connected_cell".getBytes())).setFieldName("connected_cell").setType("STRING").setEncoding("TEXT").build();BigtableColumnconnectedWifi=BigtableColumn.newBuilder().setQualifierEncoded(Base64.encodeBase64String("connected_wifi".getBytes())).setFieldName("connected_wifi").setType("STRING").setEncoding("TEXT").build();BigtableColumnosBuild=BigtableColumn.newBuilder().setQualifierEncoded(Base64.encodeBase64String("os_build".getBytes())).setFieldName("os_build").setType("STRING").setEncoding("TEXT").build();// Configuring column family and columnsstatsSummary.setColumns(ImmutableList.of(connectedCell,connectedWifi,osBuild)).setFamilyID("stats_summary").setOnlyReadLatest(true).setEncoding("TEXT").setType("STRING").build();// Configuring BigtableOptions is optional.BigtableOptionsoptions=BigtableOptions.newBuilder().setIgnoreUnspecifiedColumnFamilies(true).setReadRowkeyAsString(true).setColumnFamilies(ImmutableList.of(statsSummary.build())).build();// Configure the external data source and query job.ExternalTableDefinitionexternalTable=ExternalTableDefinition.newBuilder(sourceUri,options).build();QueryJobConfigurationqueryConfig=QueryJobConfiguration.newBuilder(query).addTableDefinition(tableName,externalTable).build();// Example queryTableResultresults=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|InterruptedExceptione){System.out.println("Query not performed \n"+e.toString());}}}

What's next

To search and filter code samples for other Google Cloud products, see theGoogle Cloud sample browser.

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.