无法将字段创建强制转换为bigint

rqcrx0a6 于 2021-05-29 发布在 Hadoop

关注(0)|答案(1)|浏览(569)

我试图合并一堆表，其中一些字段的类型是bigint数组。当我试着在Hive中投射这些时，它不断地给我一个“表达式not in group by key‘field\u name’”。
我的代码如下：

CREATE TABLE sample_table stored AS avro AS 
    SELECT cast(cast(rand()* 1000000000000000000 AS BIGINT) AS string)        id, 
           'PERSON'AS person, 
           name AS name, 
           age AS age, 
           gender AS gender
FROM   table_a
UNION ALL 
SELECT cast(cast(rand()* 1000000000000000000 AS BIGINT) AS string)    id,  
          'PERSON'AS person, 
           name AS name, 
           collect_list(
    CAST(NULL AS BIGINT)
  ) AS age, 
           null AS gender
FROM   table_b

生成的错误如下：
sql错误[500051][hy000]：[cloudera]hivejdbcdriver处理查询/语句时出错。错误代码：10025，sql状态：tstatustatuscode:error_status，infomessages:[*org.apache.hive.service.cli.hivesqlexception:error while 编译语句：失败：semanticexception[error 10025]：行4:7表达式不在group by key“age”中：28:27，org.apache.hive.service.cli.operation。operation:tosqlexception：操作。java:400,org.apache.hive.service.cli.operation.sqloperation:prepare：sqloperation。java:187

sql hadoop Hive List Arrays

来源：https://stackoverflow.com/questions/56740284/unable-to-cast-field-creation-to-bigint

1条答案

按热度按时间

m3eecexj1#

collect_list 是聚合函数。你需要 group by 每个非恒定场。

SELECT cast(cast(rand()* 1000000000000000000 AS BIGINT) AS string)    id,  
          'PERSON'AS person, 
           name, 
           collect_list(CAST(NULL AS BIGINT)) AS age, 
           null AS gender
FROM   table_b
group by cast(cast(rand()* 1000000000000000000 AS BIGINT) AS string), name

赞(0）回复(0）举报 2021-05-29

我来回答

无法将字段创建强制转换为bigint

1条答案

相关问题

热门标签

最新问答