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

## Explore further


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

- [Query Bigtable external tables](https://docs.cloud.google.com/bigquery/docs/external-data-bigtable)

## Code sample

### Java


Before trying this sample, follow the Java setup instructions in the
[BigQuery quickstart using
client libraries](https://docs.cloud.google.com/bigquery/docs/quickstarts/quickstart-client-libraries).


For more information, see the
[BigQuery Java API
reference documentation](https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/overview).


To authenticate to BigQuery, set up Application Default Credentials.
For more information, see

[Set up authentication for client libraries](https://docs.cloud.google.com/bigquery/docs/authentication#client-libs).


    import com.google.api.client.util.https://docs.cloud.google.com/java/docs/reference/google-http-client/latest/com.google.api.client.util.Base64.html;
    import com.google.cloud.bigquery.https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigQuery.html;
    import com.google.cloud.bigquery.https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigQueryException.html;
    import com.google.cloud.bigquery.https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigQueryOptions.html;
    import com.google.cloud.bigquery.https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigtableColumn.html;
    import com.google.cloud.bigquery.https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigtableColumnFamily.html;
    import com.google.cloud.bigquery.https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigtableOptions.html;
    import com.google.cloud.bigquery.https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.ExternalTableDefinition.html;
    import com.google.cloud.bigquery.https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.QueryJobConfiguration.html;
    import com.google.cloud.bigquery.https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.TableResult.html;
    import com.google.common.collect.ImmutableList;

    // Sample to queries an external bigtable data source using a temporary table
    public class QueryExternalBigtableTemp {

      public static void main(String[] args) {
        // TODO(developer): Replace these variables before running the sample.
        String projectId = "MY_PROJECT_ID";
        String bigtableInstanceId = "MY_INSTANCE_ID";
        String bigtableTableName = "MY_BIGTABLE_NAME";
        String bigqueryTableName = "MY_TABLE_NAME";
        String sourceUri =
            String.format(
                "https://googleapis.com/bigtable/projects/%s/instances/%s/tables/%s",
                projectId, bigtableInstanceId, bigtableTableName);
        String query = String.format("SELECT * FROM %s ", bigqueryTableName);
        queryExternalBigtableTemp(bigqueryTableName, sourceUri, query);
      }

      public static void queryExternalBigtableTemp(String tableName, String sourceUri, 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.
          https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigQuery.html bigquery = https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigQueryOptions.html.getDefaultInstance().getService();

          https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigtableColumnFamily.html.Builder statsSummary = https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigtableColumnFamily.html.newBuilder();

          // Configuring Columns
          https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigtableColumn.html connectedCell =
              https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigtableColumn.html.newBuilder()
                  .https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigtableColumn.Builder.html#com_google_cloud_bigquery_BigtableColumn_Builder_setQualifierEncoded_java_lang_String_(https://docs.cloud.google.com/java/docs/reference/google-http-client/latest/com.google.api.client.util.Base64.html.https://docs.cloud.google.com/java/docs/reference/google-http-client/latest/com.google.api.client.util.Base64.html#com_google_api_client_util_Base64_encodeBase64String_byte___("connected_cell".https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery-jdbc/latest/com.google.cloud.bigquery.jdbc.BigQueryBaseResultSet.html#com_google_cloud_bigquery_jdbc_BigQueryBaseResultSet_getBytes_int_()))
                  .https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigtableColumn.Builder.html#com_google_cloud_bigquery_BigtableColumn_Builder_setFieldName_java_lang_String_("connected_cell")
                  .setType("STRING")
                  .setEncoding("TEXT")
                  .build();
          https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigtableColumn.html connectedWifi =
              https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigtableColumn.html.newBuilder()
                  .https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigtableColumn.Builder.html#com_google_cloud_bigquery_BigtableColumn_Builder_setQualifierEncoded_java_lang_String_(https://docs.cloud.google.com/java/docs/reference/google-http-client/latest/com.google.api.client.util.Base64.html.https://docs.cloud.google.com/java/docs/reference/google-http-client/latest/com.google.api.client.util.Base64.html#com_google_api_client_util_Base64_encodeBase64String_byte___("connected_wifi".https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery-jdbc/latest/com.google.cloud.bigquery.jdbc.BigQueryBaseResultSet.html#com_google_cloud_bigquery_jdbc_BigQueryBaseResultSet_getBytes_int_()))
                  .https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigtableColumn.Builder.html#com_google_cloud_bigquery_BigtableColumn_Builder_setFieldName_java_lang_String_("connected_wifi")
                  .setType("STRING")
                  .setEncoding("TEXT")
                  .build();
          https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigtableColumn.html osBuild =
              https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigtableColumn.html.newBuilder()
                  .https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigtableColumn.Builder.html#com_google_cloud_bigquery_BigtableColumn_Builder_setQualifierEncoded_java_lang_String_(https://docs.cloud.google.com/java/docs/reference/google-http-client/latest/com.google.api.client.util.Base64.html.https://docs.cloud.google.com/java/docs/reference/google-http-client/latest/com.google.api.client.util.Base64.html#com_google_api_client_util_Base64_encodeBase64String_byte___("os_build".https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery-jdbc/latest/com.google.cloud.bigquery.jdbc.BigQueryBaseResultSet.html#com_google_cloud_bigquery_jdbc_BigQueryBaseResultSet_getBytes_int_()))
                  .https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigtableColumn.Builder.html#com_google_cloud_bigquery_BigtableColumn_Builder_setFieldName_java_lang_String_("os_build")
                  .setType("STRING")
                  .setEncoding("TEXT")
                  .build();

          // Configuring column family and columns
          statsSummary
              .setColumns(ImmutableList.of(connectedCell, connectedWifi, osBuild))
              .https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigtableColumnFamily.Builder.html#com_google_cloud_bigquery_BigtableColumnFamily_Builder_setFamilyID_java_lang_String_("stats_summary")
              .setOnlyReadLatest(true)
              .setEncoding("TEXT")
              .setType("STRING")
              .build();

          // Configuring BigtableOptions is optional.
          https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigtableOptions.html options =
              https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigtableOptions.html.newBuilder()
                  .https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigtableOptions.Builder.html#com_google_cloud_bigquery_BigtableOptions_Builder_setIgnoreUnspecifiedColumnFamilies_java_lang_Boolean_(true)
                  .https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigtableOptions.Builder.html#com_google_cloud_bigquery_BigtableOptions_Builder_setReadRowkeyAsString_java_lang_Boolean_(true)
                  .https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigtableOptions.Builder.html#com_google_cloud_bigquery_BigtableOptions_Builder_setColumnFamilies_java_util_List_com_google_cloud_bigquery_BigtableColumnFamily__(ImmutableList.of(statsSummary.build()))
                  .build();

          // Configure the external data source and query job.
          https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.ExternalTableDefinition.html externalTable =
              https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.ExternalTableDefinition.html.newBuilder(sourceUri, options).build();
          https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.QueryJobConfiguration.html queryConfig =
              https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.QueryJobConfiguration.html.newBuilder(query)
                  .https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.QueryJobConfiguration.Builder.html#com_google_cloud_bigquery_QueryJobConfiguration_Builder_addTableDefinition_java_lang_String_com_google_cloud_bigquery_ExternalTableDefinition_(tableName, externalTable)
                  .build();

          // Example query
          https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.TableResult.html results = bigquery.https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigQuery.html#com_google_cloud_bigquery_BigQuery_query_com_google_cloud_bigquery_QueryJobConfiguration_com_google_cloud_bigquery_BigQuery_JobOption____(queryConfig);

          results
              .https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.TableResult.html#com_google_cloud_bigquery_TableResult_iterateAll__()
              .forEach(row -> row.forEach(val -> System.out.printf("%s,", val.toString())));

          System.out.println("Query on external temporary table performed successfully.");
        } catch (https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigQueryException.html | InterruptedException e) {
          System.out.println("Query not performed \n" + e.toString());
        }
      }
    }

## What's next


To search and filter code samples for other Google Cloud products, see the
[Google Cloud sample browser](https://docs.cloud.google.com/docs/samples?product=bigquery).