查询外部表

BigQuery Omni 可让您像查询任何 BigQuery 表一样查询外部表。交互式查询的最大结果大小为 2 MB。如果查询结果较大,请考虑将其导出到 Amazon S3。查询结果存储在 BigQuery 匿名表 (prod spanner) 中。

如需运行查询,请使用 Google Cloud Console 或 bq 命令行工具。

控制台

  1. 在 Cloud Console 中打开 BigQuery 页面。

    转到 BigQuery 页面

  2. 查询编辑器框中输入标准 SQL 查询。标准 SQL 是 Cloud Console 中的默认语法。

    SELECT * FROM DATASET_NAME.TABLE_NAME
    

    替换以下内容:

    • DATASET_NAME:您创建的数据集名称。
    • TABLE_NAME:您创建的外部表名称。
  3. 点击运行

bq

使用 query 命令并通过 --nouse_legacy_sql--use_legacy_sql=false 标志指定标准 SQL 语法。

如需运行查询,请输入以下内容:

bq query --nouse_legacy_sql \
SELECT * FROM DATASET_NAME.TABLE_NAME

替换以下内容:

  • DATASET_NAME:您创建的数据集名称。
  • TABLE_NAME:您创建的外部表名称。

Java

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.CsvOptions;
import com.google.cloud.bigquery.DatasetId;
import com.google.cloud.bigquery.ExternalTableDefinition;
import com.google.cloud.bigquery.Field;
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;

// Sample to queries an external data source aws s3 using a permanent table
public class QueryExternalTableAws {

  public static void main(String[] args) throws InterruptedException {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String datasetName = "MY_DATASET_NAME";
    String externalTableName = "MY_EXTERNAL_TABLE_NAME";
    // Query to find states starting with 'W'
    String query =
        String.format(
            "SELECT * FROM s%.%s.%s WHERE name LIKE 'W%%'",
            projectId, datasetName, externalTableName);
    queryExternalTableAws(query);
  }

  public static void queryExternalTableAws(String query) throws InterruptedException {
    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.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      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 aws external permanent table performed successfully.");
    } catch (BigQueryException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

后续步骤