hive 把一个表中的两列导入到另一张建好的表中

1. 建表wkz

hive> create table wkz (id int,
    > name string,
    > age int,
    > tel string)
    > ROW FORMAT DELIMITED
    > FIELDS TERMINATED BY '\t'
    > ;

 

2. 本地当前路径数据文件wkz内容：

1       wkz
2       bug
3       cdd
 

3.建表b

hive> create table b (
    > we string,ig int,id int,omg int,name string) 
    > ROW FORMAT DELIMITED
    > FIELDS TERMINATED BY '\t'
    > ;

 

4.导入wkz文件数据到wkz表：

hive> load data local inpath  "wkz" into table wkz;
Loading data to table default.wkz
Table default.wkz stats: [numFiles=1, totalSize=18]
OK
Time taken: 1.932 seconds

 

5.把wkz前两列插入b：

（1）  > insert into table b select id,name from wkz;
FAILED: SemanticException [Error 10044]: Line 1:18 Cannot insert into target table because column number/types are different 'b': Table insclause-0 has 5 columns, but query has 2 columns.

直接报错说b表有5列，但是只给了两列

（2）其他列用id字段代替插入成功：

hive> insert into table b select id,name,id ,id ,id  from wkz;
Query ID = hadoop_20181229112929_df47ff39-c711-4a50-8ee9-a8d08a86e4ca
Total jobs = 3
Launching Job 1 out of 3
Number of reduce tasks is set to 0 since there's no reduce operator

...............
Stage-Stage-1: Map: 1   Cumulative CPU: 3.19 sec   HDFS Read: 3822 HDFS Write: 98 SUCCESS
Total MapReduce CPU Time Spent: 3 seconds 190 msec
OK
Time taken: 13.973 seconds

6.总结：

（1）hive能够从一个表取数据插入到另一个表，不过字段不足要自己填充

（2）hive不会自动对应字段名去插入另一个表，顺序自己调整