INSERT
标准语法
Standard Syntax:INSERT INTO TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)]
VALUES values_row [, values_row ...] Where values_row is:( value [, value ...] )where a value is either null or any valid SQL literal
无分区
CREATE TABLE students (name VARCHAR(64), age INT, gpa DECIMAL(3, 2)) CLUSTERED BY (age) INTO 2 BUCKETS STORED AS ORC;
INSERT INTO TABLE students VALUES ('fred flintstone', 35, 1.28), ('barney rubble', 32, 2.32);
有分区
CREATE TABLE pageviews (userid VARCHAR(64), link STRING, came_from STRING) PARTITIONED BY (datestamp STRING) CLUSTERED BY (userid) INTO 256 BUCKETS STORED AS ORC;
INSERT INTO TABLE pageviews PARTITION (datestamp = '2014-09-23') VALUES ('jsmith', 'mail.com', 'sports.com'), ('jdoe', 'mail.com', null);
INSERT INTO TABLE pageviews PARTITION (datestamp) VALUES ('tjohnson', 'sports.com', 'finance.com', '2014-09-23'), ('tlee', 'finance.com', null, '2014-09-21');
INSERT INTO TABLE pageviews VALUES ('tjohnson', 'sports.com', 'finance.com', '2014-09-23'), ('tlee', 'finance.com', null, '2014-09-21');
全列插入
CREATE TABLE test(id STRING,name STRING)PARTITIONED BY (dt STRING ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;
CREATE TABLE tmp_test(id STRING, name STRING) PARTITIONED BY (dt STRING ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;
CREATE TABLE tmp_test_2( id STRING) PARTITIONED BY (dt STRING ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;
列数不一致会报错
insert into test partition(dt='20180101') select * from tmp_test
在hive里面执行没问题，但是在spark-sql执行的时候会报错，spark-sql 会先对列数进行校验
insert into test partition(dt='20180101') select * from tmp_test2
部分列插入
insert into test partition(dt='20180101') (id) select (id) from tmp_test
insert into test partition(dt='20180101') (id,name) select (id,name) from tmp_test
问题
1.列数不匹配（查询列多于插入列）
hive> insert into test partition(dt='20180101') select * from tmp_test;
FAILED: SemanticException [Error 10044]: Line 1:12 Cannot insert into target table because column number/types are different ''20180101'': Table insclause-0 has 2 columns, but query has 3 columns.
解决方案
指定列插入
