Hive 編程專題之 - 表值函數

• 2019 年 12 月 25 日
• 筆記

環境：

Hive: 2.7.7  Oracle SQL Developer  Cloudera JDBC Driver  

案例 - 1 ： 生成多列

select  explode(deductions) as (type,vol)  from default.employee  

結果：

image

錯誤：

1 - 環境部署

create table account(accountid int, account array)  

在行: 3 上開始執行命令時出錯 -  create table account(accountid int, account array)  錯誤報告 -  [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: ParseException line 1:49 mismatched input ')' expecting < near 'array' in list type: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.ParseException:line 1:49 mismatched input ')'  expecting < near 'array' in list type:22:6, org.apache.hadoop.hive.ql.parse.ParseDriver:parse:ParseDriver.java:211, org.apache.hadoop.hive.ql.parse.ParseUtils:parse:ParseUtils.java:77, org.apache.hadoop.hive.ql.parse.ParseUtils:parse:ParseUtils.java:70, org.apache.hadoop.hive.ql.Driver:compile:Driver.java:468, 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: ParseException line 1:49 mismatched input ')' expecting < near 'array' in list type), Query: create table account(accountid int, account array).  

解決方法：

create table account(accountid int, account array<string>)  

array, map, struct 都有自己的定義方法，而他們的調用方法基本都相同：

調用方法：

array('Ali','Tencent')  map('company','ali','city','hangzhou')  struct('ZheJiang','Hangzhou','Ali')  

定義數據類型：

create table employee(      [name] string,      salary float,      subordinates array<string>,      deductions map<string,float>,      address struct<street:string,city:string,state:string,zip:int> ) ;  

異常：需要輸入綁定

image

最終發現，執行腳本與執行語句，還是有些不一樣的地方。

執行語句的時候 :var 是代表一個參數，所以需要輸入參數值

2 - 生成測試數據

insert into account(accountid,account) values(2,array('Ali.com','Tencent'))      在行: 6 上開始執行命令時出錯 -  insert into account(accountid,account) values(2,array('Ali.com','Tencent'))  錯誤位於命令行: 6 列: 1  錯誤報告 -  SQL 錯誤: [Cloudera][HiveJDBCDriver](500051) ERROR processing query/statement. Error Code: [Cloudera][JSQLEngine](12000) The column "account" could not be found., SQL state: HY000, Query: insert into account(accountid,account) values(2,array('Ali.com','Tencent')).  

其實是 array() 不能用在 insert … values… 這種語句中，換成如下便可：

create table employee(          name string      ,   salary float      ,   subordinates array<string>  )    insert into employee(name,salary,subordinates)  select 'ali',320,array('ali','acai','ayun')  from account  limit 1  

完整的 Array, Map, Struct 調用方法如下：

insert into default.employee (name,salary,subordinates,deductions,address)  select 'ali'      ,320      ,array('ali','acai','ayun') as sub      , map('ali',1,'acai',2,'ayun',7) as ded      ,named_struct('street',"zhejiang",  'city',"hangzhou",  'state',"hubin",  'zip',"201210")  from student  limit 1  

值得注意的地方，struct() 與 named_struct() 的不同：

struct("zhejiang","hangzhou","hubin","201210")  

會被編譯成這樣的數據結構：

struct("col0":"zhejiang","col1":"hangzhou","col2":"hubin","col3":"201210")  

但 named_struct 的節點就被定死了：

named_struct('street',"zhejiang",  'city',"hangzhou",  'state',"hubin",  'zip',"201210")  

編譯過後就是：

struct('street',"zhejiang",  'city',"hangzhou",  'state',"hubin",  'zip',"201210")  

而這種結構正符合表中定義的數據類型。

案例 - 2 ： 生成多行

select  explode(subordinates) as subs  from default.employee  

image