hive根据已有表创建新表_2.4 hive创建表实例讲解

一、create table

##

员工表

create table IF NOT EXISTS default.emp(

empno int,

ename string,

job string,

mgr int,

hiredate string,

sal double,

comm double,

deptno int

)

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

##

部门表

create table IF NOT EXISTS default.dept(

deptno int,

dname string,

loc string

)

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

#查看

hive (default)> show tables;

OK

tab_name

...

dept

emp

2、加载数据

#emp

hive (default)> load data local inpath '/opt/datas/emp.txt' overwrite into table emp ;

#dept

hive (default)> load data local inpath '/opt/datas/dept.txt' overwrite into table dept ;

#select

hive (default)> select * from emp;

OK

emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno

7369 SMITH CLERK 7902 1980-12-17 800.0 NULL 20

7499 ALLEN SALESMAN 7698 1981-2-20 1600.0 300.0 30

7521 WARD SALESMAN 7698 1981-2-22 1250.0 500.0 30

7566 JONES MANAGER 7839 1981-4-2 2975.0 NULL 20

7654 MARTIN SALESMAN 7698 1981-9-28 1250.0 1400.0 30

7698 BLAKE MANAGER 7839 1981-5-1 2850.0 NULL 30

7782 CLARK MANAGER 7839 1981-6-9 2450.0 NULL 10

7788 SCOTT ANALYST 7566 1987-4-19 3000.0 NULL 20

7839 KING PRESIDENT NULL 1981-11-17 5000.0 NULL 10

7844 TURNER SALESMAN 7698 1981-9-8 1500.0 0.0 30

7876 ADAMS CLERK 7788 1987-5-23 1100.0 NULL 20

7900 JAMES CLERK 7698 1981-12-3 950.0 NULL 30

7902 FORD ANALYST 7566 1981-12-3 3000.0 NULL 20

7934 MILLER CLERK 7782 1982-1-23 1300.0 NULL 10

Time taken: 0.029 seconds, Fetched: 14 row(s)

hive (default)> select * from dept;

OK

dept.deptno dept.dname dept.loc

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

Time taken: 0.028 seconds, Fetched: 4 row(s)

二、创建一个表通过子查询

#创建

hive (default)> create table if not exists default.dept_cats as select * from dept;

#查

hive (default)> select * from dept_cats;

OK

dept_cats.deptno dept_cats.dname dept_cats.loc

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

Time taken: 0.026 seconds, Fetched: 4 row(s)

三、清除一个表

##

hive (default)> truncate table dept_cats;

OK

Time taken: 0.037 seconds

hive (default)> select * from dept_cats;

OK

dept_cats.deptno dept_cats.dname dept_cats.loc

Time taken: 0.026 seconds

四、alter、删除

#先创建一个表

hive (default)> create table if not exists default.dept_like like default.dept ;

OK

Time taken: 0.04 seconds

#alter

hive (default)> alter table dept_like rename to dept_like_rename;

OK

Time taken: 0.088 seconds

#删除

hive (default)> drop table if exists dept_like_rename ;

Moved: 'hdfs://hadoop-senior.ibeifeng.com:8020/user/hive/warehouse/dept_like_rename' to trash at: hdfs://hadoop-senior.ibeifeng.com:8020/user/root/.Trash/Current

OK

Time taken: 0.346 seconds