##

员工表

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

#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

#先创建一个表

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