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_categoryfrom test_dataset.cosme_raw_table,  UNNEST(split( product_category,".")) as prd_catwhere product_link = "XXX"group by prd_cat,product_category;我不清楚您希望通过查询获得什么结果，但至少它应该运行。