时间戳

首页 > 解决方案 > Insert values in Hive tables with primitive and complex data type

hive - Insert values in Hive tables with primitive and complex data type

问题描述

If i have only one table such as student and table definition and schema is such as hive> 

create table student1(S_Id int,
    > S_name Varchar(100),
    > Address Struct<a:int, b:String, c:int>,
    > marks Map<String, Int>);

OK
Time taken: 0.439 seconds
hive> 

hive> Describe Student1;
OK
s_id                    int                                         
s_name                  varchar(100)                                
address                 struct<a:int,b:string,c:int>                        
marks                   map<string,int>                             
Time taken: 0.112 seconds, Fetched: 4 row(s)

Now i am trying to insert values into that Student1 table such as 

hive> insert into table student1 values(1, 'Afzal', Struct(42, 'nelson Ave NY', 08309),MAP("MATH", 89));

I am getting that error

FAILED: SemanticException [Error 10293]: Unable to create temp file for insert values Expression of type TOK_FUNCTION not supported in insert/values

How do i insert values for one record in one go, Can anyone please help me?

标签： hivehiveql

解决方案

它在使用insert .. select语句时起作用。创建一个单行的虚拟表，或使用一些现有的表 + 添加limit 1。还使用named_struct功能：

演示：

hive> insert into table student1 
      select 1                                                    s_id, 
             'Afzal'                                              s_name, 
             named_struct('a',42, 'b','nelson Ave NY', 'c',08309) address,
             MAP('MATH', 89)                                      marks 
        from default.dual limit 1; --this is dummy table

Loading data to table dev.student1
Table dev.student1 stats: [numFiles=1, numRows=1, totalSize=48, rawDataSize=37]
OK
Time taken: 27.175 seconds

检查数据：

hive> select * from student1;
OK
1       Afzal   {"a":42,"b":"nelson Ave NY","c":8309}   {"MATH":89}
Time taken: 0.125 seconds, Fetched: 1 row(s)

推荐阅读