0: jdbc:hive2://CentOS:10000> add jar /usr/apache-hive-1.2.2-bin/hcatalog/share/hcatalog/hive-hcatalog-core-1.2.2.jar
CREATE EXTERNAL TABLE t_emp_json(
empno INT,
ename STRING,
job STRING,
mgr INT,
hiredate TIMESTAMP,
sal DECIMAL(7,2),
comm DECIMAL(7,2),
deptno INT)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe';0: jdbc:hive2://CentOS:10000> select empno,ename,job,mgr,hiredate,sal,comm,deptno from t_emp_json;
+--------+---------+------------+-------+------------------------+-------+-------+---------+--+| empno | ename | job | mgr | hiredate | sal | comm | deptno |+--------+---------+------------+-------+------------------------+-------+-------+---------+--+|7369| SMITH | CLERK |7902|1980-12-1700:00:00.0|800| NULL |20||7499| ALLEN | SALESMAN |7698|1981-02-2000:00:00.0|1600|300|30||7521| WARD | SALESMAN |7698|1981-02-2200:00:00.0|1250|500|30||7566| JONES | MANAGER |7839|1981-04-0200:00:00.0|2975| NULL |20||7654| MARTIN | SALESMAN |7698|1981-09-2800:00:00.0|1250|1400|30||7698| BLAKE | MANAGER |7839|1981-05-0100:00:00.0|2850| NULL |30||7782| CLARK | MANAGER |7839|1981-06-0900:00:00.0|2450| NULL |10||7788| SCOTT | ANALYST |7566|1987-04-1900:00:00.0|1500| NULL |20||7839| KING | PRESIDENT | NULL |1981-11-1700:00:00.0|5000| NULL |10||7844| TURNER | SALESMAN |7698|1981-09-0800:00:00.0|1500|0|30||7876| ADAMS | CLERK |7788|1987-05-2300:00:00.0|1100| NULL |20||7900| JAMES | CLERK |7698|1981-12-0300:00:00.0|950| NULL |30||7902| FORD | ANALYST |7566|1981-12-0300:00:00.0|3000| NULL |20||7934| MILLER | CLERK |7782|1982-01-2300:00:00.0|1300| NULL |10|+--------+---------+------------+-------+------------------------+-------+-------+---------+--+
CREATE TABLE t_employee(
empno INT,
ename STRING,
job STRING,
mgr INT,
hiredate TIMESTAMP,
sal DECIMAL(7,2),
comm DECIMAL(7,2))
PARTITIONED BY (deptno INT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";
0: jdbc:hive2://CentOS:10000> insert overwrite table t_employee partition(deptno=10) select empno,ename,job,mgr,hiredate,sal,comm from t_emp_json where deptno=10;--覆盖
0: jdbc:hive2://CentOS:10000> insert into table t_employee partition(deptno=20) select empno,ename,job,mgr,hiredate,sal,comm from t_emp_json where deptno=20;--追加
将查询结果插入多个表中
FROM from_statement
INSERT OVERWRITE TABLE tablename1
[PARTITION (partcol1=val1, partcol2=val2 ...)[IF NOT EXISTS]] select_statement1
[INSERT OVERWRITE TABLE tablename2 [PARTITION ...[IF NOT EXISTS]] select_statement2][INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2]...;
from t_emp_json
INSERT OVERWRITE TABLE t_employee partition(deptno=10) select empno,ename,job,mgr,hiredate,sal,comm where deptno=10
INSERT OVERWRITE TABLE t_employee partition(deptno=20) select empno,ename,job,mgr,hiredate,sal,comm where deptno=20
INSERT OVERWRITE TABLE t_employee partition(deptno=30) select empno,ename,job,mgr,hiredate,sal,comm where deptno=30
动态插入
0: jdbc:hive2://CentOS:10000> set hive.exec.dynamic.partition.mode=nonstrict;#开启动态分区
No rows affected (0.004 seconds)0: jdbc:hive2://CentOS:10000> INSERT OVERWRITE TABLE t_employee PARTITION (deptno) SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno FROM t_emp_json;0: jdbc:hive2://CentOS:10000> show partitions t_employee;+------------+--+| partition |+------------+--+| deptno=10|| deptno=20|| deptno=30|+------------+--+3 rows selected (0.064 seconds)
结果写出到文件系统
INSERT OVERWRITE [LOCAL] DIRECTORY directory1
[ROW FORMAT row_format][STORED AS file_format]
SELECT ... FROM ...[ROW FORMAT row_format]:不指定采用默认分隔符
[STORED AS file_format]:输出类型
0: jdbc:hive2://CentOS:10000> INSERT OVERWRITE DIRECTORY '/employee' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE select * from t_employee;
加载文件数据到表中LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]load data local inpath "/root/baizhi/t_employee" overwrite into table t_e...