java - 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));

以下是日志 -

        com.google.cloud.bigquery.BigQueryException: Grouping by expressions of type ARRAY is not allowed at [6:10]
at com.google.cloud.bigquery.spi.v2.HttpBigQueryRpc.translate(HttpBigQueryRpc.java:99)
at com.google.cloud.bigquery.spi.v2.HttpBigQueryRpc.getQueryResults(HttpBigQueryRpc.java:401)
at com.google.cloud.bigquery.BigQueryImpl$23.call(BigQueryImpl.java:688)
at com.google.cloud.bigquery.BigQueryImpl$23.call(BigQueryImpl.java:683)
at com.google.api.gax.retrying.DirectRetryingExecutor.submit(DirectRetryingExecutor.java:89)
at com.google.cloud.RetryHelper.run(RetryHelper.java:74)
at com.google.cloud.RetryHelper.runWithRetries(RetryHelper.java:51)
at com.google.cloud.bigquery.BigQueryImpl.getQueryResults(BigQueryImpl.java:682)
at com.google.cloud.bigquery.BigQueryImpl.getQueryResults(BigQueryImpl.java:674)
at com.google.cloud.bigquery.Job$1.call(Job.java:329)
at com.google.cloud.bigquery.Job$1.call(Job.java:326)
at com.google.api.gax.retrying.DirectRetryingExecutor.submit(DirectRetryingExecutor.java:89)
at com.google.cloud.RetryHelper.run(RetryHelper.java:74)
at com.google.cloud.RetryHelper.poll(RetryHelper.java:63)
at com.google.cloud.bigquery.Job.waitForQueryResults(Job.java:325)
at com.google.cloud.bigquery.Job.waitFor(Job.java:240)
at TestBigQuery.explicit(TestBigQuery.java:190)
at TestBigQuery.main(TestBigQuery.java:32)
        Caused by: com.google.api.client.googleapis.json.GoogleJsonResponseException: 400 Bad Request
        {
          "code" : 400,
          "errors" : [ {
"domain" : "global",
"location" : "parameters.q",
"message" : "Grouping by expressions of type ARRAY is not allowed at [6:10]",
"reason" : "invalidQuery"
          } ],
          "message" : "Grouping by expressions of type ARRAY is not allowed at [6:10]",
          "status" : "INVALID_ARGUMENT"
        }

有人可以帮忙吗？谢谢!!

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与 Standard 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

希望有帮助。