Hive 编程专题之 - 表值函数

Hive: 2.7.7
Oracle SQL Developer
Cloudera JDBC Driver

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

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('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> ) ;

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')).

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

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("zhejiang","hangzhou","hubin","201210")

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

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

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

select  explode(subordinates) as subs
from default.employee