Hive 編程專題之 - 使用正則指定輸出列

環境：

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》中居然也沒有提到如何解決。