说明
通过建表，查看 表与表的血缘关系
准备数据文件
在 linux 执行下面命令
dept 表数据
vim /program/dept.txt
内容如下：
10, ACCOUNTING, NEW YORK
20, RESEARCH, DALLAS
30, SALES, CHICAGO
40, OPERATIONS, BOSTON
emp 表数据
vim /program/emp.txt
内容如下：
7369,SMITH,CLERK,7902,1980-12-17,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09,2450,,10
7788,SCOTT,ANALYST,7566,1982-12-09,3000,,20
7839,KING,PRESIDENT,,1981-11-17,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08,1500,0,30
7876,ADAMS,CLERK,7788,1983-01-12,1100,,20
7900,JAMES,CLERK,7698,1981-12-03,950,,30
7902,FORD,ANALYST,7566,1981-12-03,3000,,20
7934,MILLER,CLERK,7782,1982-01-23,1300,,10
创建表方式
hive 客户端
执行下面命令：
hive
dbeaver
略
创建表 dept 表
create external table dept(
deptno int comment '部门编号',
dname string comment '部门名称',
loc string comment '部门所在地'
)
row format delimited fields terminated by ',';
导入数据
load data local inpath '/program/dept.txt' into table dept;
创建表 emp 表
create external table emp(
empno int comment '员工编号',
ename string comment '姓名',
job string comment '岗位',
mgr int comment '领导编号',
hiredate string comment '入职时间',
sal double comment '工资',
comm double comment '奖金',
deptno int comment '部门编号'
)
row format delimited fields terminated by ',';
导入数据
load data local inpath '/program/emp.txt' into table emp;
测试
执行查询语句：
select * from emp
select * from dept
创建 ads层表
create external table ads_emp_count(
deptno int comment '部门编号',
emp_count int comment '部门员工人数',
sal_count int comment '部门员工工资总数',
comm_count int comment '部门员工奖金总数'
)
row format delimited fields terminated by ',';
插入数据
insert into ads_emp_count select deptno ,count(*),sum(sal),nvl(sum(comm),0) from emp group by deptno