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
^:17:16, org.apache.hive.service.cli.operation.Operation:toSQLException:Operation.java:380, org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:206, org.apache.hive.service.cli.operation.SQLOperation:runInternal:SQLOperation.java:290, org.apache.hive.service.cli.operation.Operation:run:Operation.java:320, org.apache.hive.service.cli.session.HiveSessionImpl:executeStatementInternal:HiveSessionImpl.java:530, org.apache.hive.service.cli.session.HiveSessionImpl:executeStatementAsync:HiveSessionImpl.java:517, org.apache.hive.service.cli.CLIService:executeStatementAsync:CLIService.java:310, org.apache.hive.service.cli.thrift.ThriftCLIService:ExecuteStatement:ThriftCLIService.java:530, org.apache.hive.service.rpc.thrift.TCLIService$Processor$ExecuteStatement:getResult:TCLIService.java:1437, org.apache.hive.service.rpc.thrift.TCLIService$Processor$ExecuteStatement:getResult:TCLIService.java:1422, org.apache.thrift.ProcessFunction:process:ProcessFunction.java:39, org.apache.thrift.TBaseProcessor:process:TBaseProcessor.java:39, org.apache.hive.service.auth.TSetIpAddressProcessor:process:TSetIpAddressProcessor.java:56, org.apache.thrift.server.TThreadPoolServer$WorkerProcess:run:TThreadPoolServer.java:286, java.util.concurrent.ThreadPoolExecutor:runWorker:ThreadPoolExecutor.java:1142, java.util.concurrent.ThreadPoolExecutor$Worker:run:ThreadPoolExecutor.java:617, 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
^:31:15, org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genColListRegex:SemanticAnalyzer.java:3419, org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genSelectPlan:SemanticAnalyzer.java:4368, org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genSelectPlan:SemanticAnalyzer.java:4167, org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genPostGroupByBodyPlan:SemanticAnalyzer.java:9705, org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genBodyPlan:SemanticAnalyzer.java:9644, org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genPlan:SemanticAnalyzer.java:10549, org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genPlan:SemanticAnalyzer.java:10427, org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genOPTree:SemanticAnalyzer.java:11125, org.apache.hadoop.hive.ql.parse.CalcitePlanner:genOPTree:CalcitePlanner.java:481, org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:analyzeInternal:SemanticAnalyzer.java:11138, org.apache.hadoop.hive.ql.parse.CalcitePlanner:analyzeInternal:CalcitePlanner.java:286, org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer:analyze:BaseSemanticAnalyzer.java:258, org.apache.hadoop.hive.ql.Driver:compile:Driver.java:512, org.apache.hadoop.hive.ql.Driver:compileInternal:Driver.java:1317, org.apache.hadoop.hive.ql.Driver:compileAndRespond:Driver.java:1295, org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:204], 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》中居然也没有提到如何解决。