Bigquery 查询 API - 数组问题

Question

我正在尝试在查询下运行 -        select prd_cat, product_category from        (            select split( product_category,".") as prd_cat,product_category  from test_dataset.cosme_raw_table  where product_link = "XXX"        ) as a        group by prd_cat,product_category;当我使用 BigQuery Web 界面运行它时，它运行成功，但是当我尝试使用 BigQuery Query API 运行它时，它失败并显示错误消息“[6:10] 不允许按 ARRAY 类型的表达式分组”下面是我的代码 -        String query = "select prd_cat, product_category" +                " from\n" +                "(\n" +                "select split( product_category,\".\") as prd_cat," +                "product_category  " +                "from test_dataset.cosme_raw_table  \n" +                "where product_link = \"XXX\"\n" +                ") as a\n" +                "group by prd_cat,product_category";        QueryJobConfiguration queryJobConfiguration =                QueryJobConfiguration.newBuilder(query)                        .setDestinationTable(tableId1)                      .setWriteDisposition(JobInfo.WriteDisposition.WRITE_TRUNCATE)                        .build();        Job loadJob1 = bigquery.create(JobInfo.of(queryJobConfiguration));有人可以帮忙吗。谢谢！！

Answer 1

发生这种情况是因为您使用的是Legacy SQL。您需要在 QueryJobConfiguration 中设置它。例如：

import com.google.cloud.bigquery.BigQuery;

import com.google.cloud.bigquery.BigQueryOptions;

import com.google.cloud.bigquery.Dataset;

import com.google.cloud.bigquery.DatasetInfo;

import com.google.cloud.bigquery.FieldValue;

import com.google.cloud.bigquery.FieldValueList;

import com.google.cloud.bigquery.QueryJobConfiguration;

public class QuickstartSample {

  public static void main(String... args) throws Exception {

    BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

    String query = "Your-Query";

    //setUseLegacySql(true) below

    QueryJobConfiguration queryConfig = QueryJobConfiguration.newBuilder(query).setUseLegacySql(true).build();

    for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {

        for (FieldValue val : row) {

             System.out.printf("%s,", val.toString());

        }

        System.out.printf("\n");

    }

  }

}

否则，您可以将TO_JSON_STRING与标准 SQL 一起使用。例如：

String query =  "WITH sample AS (SELECT 1 id, ['a,b', 'c'] a UNION ALL SELECT 1, ['a','b,c']) SELECT TO_JSON_STRING(a) arr,COUNT(DISTINCT id) cnt FROM sample GROUP BY arr";

QueryJobConfiguration queryConfig = QueryJobConfiguration.newBuilder(query).build();

在你的情况下，你可以尝试：

WITH a AS (select split(product_category,".") as prd_cat,product_category from test_dataset.cosme_raw_table where product_link = "XXX") select TO_JSON_STRING(prd_cat) arr, product_category from a GROUP BY arr,product_category

希望能帮助到你。

Answer 2

如果您使用旧版 SQL，GROUP BY 运算符将隐式展平您正在分组的任何数组。如果使用标准 SQL，则需要显式展平数组。注意：

• BigQuery 的经典 UI 默认使用旧版 SQL，但您可以在查询选项中更改方言。

• BigQuery 的新 UI（属于 Cloud 控制台的一部分）默认使用标准 SQL。

• BigQuery 的客户端库默认使用标准 SQL。

您可以通过展平数组来修复您的查询以使用标准 SQL 工作，例如：

select prd_cat, product_category

from test_dataset.cosme_raw_table,

  UNNEST(split( product_category,".")) as prd_cat

where product_link = "XXX"

group by prd_cat,product_category;

我不清楚您希望通过查询获得什么结果，但至少它应该运行。