google-bigquery - BigQuery 无效的字段名称 _PARTITIONDATE 或 _PARTITIONTIME

Question

在日期分区表中，您应该能够运行：

    SELECT
      _PARTITIONDATE,
      COUNT(*)
    FROM table
    GROUP BY 1
    ORDER BY 1

但是，运行此命令将返回错误：

Invalid field name "_PARTITIONDATE". Field names are not allowed to start with the (case-insensitive) prefixes _PARTITION, _TABLE_, _FILE_ and _ROW_TIMESTAMP

为什么这不起作用？

标签： google-bigquery

Answer 1

事实证明，您需要做的就是为查询中的_PARTITIONDATEor_PARTITIONTIME列提供别名：

SELECT 
    -- _PARTITIONDATE alone will not work
    _PARTITIONDATE as partition_date,
    COUNT(*)
FROM table
GROUP BY 1
ORDER BY 1