Hive 编程专题 之 – 使用正则指定输出列

  • 2019 年 12 月 25 日
  • 筆記

环境：

Hive: 2.7.7  Oracle SQL Developer  Cloudera JDBC Driver

案例：

select type,nameobject,`*date`  from tblobj2  limit 10 ;

错误:

1 – 使用 Oracle SQL Developer 执行上述 HQL 语句报错：

[Cloudera][HiveJDBCDriver](500051) ERROR processing query/statement. Error Code: 40000, SQL state: TStatus(statusCode:ERROR_STATUS, infoMessages:[*org.apache.hive.service.cli.HiveSQLException:Error while compiling statement: FAILED: SemanticException Line 1:23 Invalid column reference '`*date`': Dangling meta character '*' near index 0  *date  ....(省却其他错误输出）  java.lang.Thread:run:Thread.java:745, *org.apache.hadoop.hive.ql.parse.SemanticException:Line 1:23 Invalid column reference '`*date`': Dangling meta character '*' near index 0  *date  ....(省却其他错误输出）  sqlState:42000, errorCode:40000, errorMessage:Error while compiling statement: FAILED: SemanticException Line 1:23 Invalid column reference '`*date`': Dangling meta character '*' near index 0  *date  ^), Query: select type,nameobject,`*date`  from tblobj2  limit 10.

2 – 使用 Hive 同样 也报错：

hive> select nameobject,type,`*date` from tblobj2 limit 10 ;  FAILED: SemanticException [Error 10004]: Line 1:23 Invalid table alias or column reference '*date': (possible column names are: nameobject, object_id, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published, is_schema_published)

解决方案：

set hive.support.quoted.identifiers=none  select type,nameobject, `.+date`  from tblobj2  where create_date is not null  limit 10  
set hive.support.quoted.identifiers=none

quoted.identifier 这才是解决问题的关键。《Hive Programming》中居然也没有提到如何解决。

image

VirMach 便宜 VPS

QNews

QNews