spoon工具的常用基础操作

一些常用转换工具

1、emp表输入->excel表输出

emp表输入，可以进行预览查看数据有没有过来excel表输出


成功执行后，可以到保存的excel位置进行查看。

2、excel输入->表输出

运行转换后可以在oracle进行查看是否有成功创建这个表

3、对部门最高工资进行分组==表输入->排序记录->分组->文本文档输出

4、对emp表和dept表进行表连接

1. 新建两个表输入，一个是对emp表的查询，一个是对dept查询
2. 分别对两个表进行以deptno进行排序记录
3. 拖拽一个记录集连接的图标
4. 运行转换成功后可进行表预览查看

5、查找每个职位排名第一的信息==表输入->排序记录->根据字段值来改变序列->过滤记录->输出

1. 表输入查找emp表数据
2. 对job下的工资进行排序，也就是两个字段都要加进去，job在前，sal降序在后，不然数据会出问题。
3. 根据字段值来改变序列
4. 过滤记录里面，根据排名结果进行过滤
5. 运行转换查看结果

6、列转行

1. 先到oracle对demo和demo1进行建表

CREATE TABLE "DEMO" 
   (  "ID" NUMBER(*,0), 
  "NAME" VARCHAR2(20), 
  "NUMS" NUMBER(*,0)
   ) ;
insert into DEMO (id, name, nums)
values (1, '苹果', 1000);
insert into DEMO (id, name, nums)
values (2, '苹果', 2000);
insert into DEMO (id, name, nums)
values (3, '苹果', 4000);
insert into DEMO (id, name, nums)
values (1, '橘子', 5000);
insert into DEMO (id, name, nums)
values (2, '橘子', 3000);
insert into DEMO (id, name, nums)
values (3, '葡萄', 3500);
insert into DEMO (id, name, nums)
values (4, '芒果', 4200);
insert into DEMO (id, name, nums)
values (4, '芒果', 5500);
commit;

select * from DEMO;

create table demo1 as 
select  id,
      sum(case when name='苹果' then nums end) 苹果,
      sum(case when name='橘子' then nums end) 橘子, 
      sum(case when name='葡萄' then nums end) 葡萄,
      sum(case when name='芒果' then nums end) 芒果 
from  demo
group by id
order  by id;


select * from demo1;
--列转行
select * 
from demo1
unpivot include nulls (nums for name in (苹果,橘子,葡萄,芒果));

2. 对demo1进行表输入sql查询
3. 列转行
   
4. 运行转换
   转换前

转换后

7、行转列（对demo的name字段行转列）

sql语句的行转列

--unpivot方法
select * 
from demo1
unpivot include nulls (nums for name in (苹果,橘子,葡萄,芒果));

--聚合函数+case when
select  id,
        sum(case when name='苹果' then nums end) 苹果,
        sum(case when name='橘子' then nums end) 橘子,
        sum(case when name='葡萄' then nums end) 葡萄,
        sum(case when name='芒果' then nums end) 芒果 
from demo
group by id
order by id;

1. 对表输入demo1的数据
2. 因为要用到聚合函数所以需要用到排序记录，对不动的列id进行排序
3. 行转列
4. 进行表输出demo2

8、case when 的使用

10部门 员工姓名转小写并向右填充* 至九位 ,job转首字母大写
20部门 员工姓名S替换成* ,M-Z 替换成#
30部门 职位SALESMAN替换成销售

1. 表输入emp数据

2. case when

3. 对不同的条件进行不同字符操作
   
   

4. 输出到一直excel表，运行结果输出

9、全量更新。会复制源表内容到目标表。根据ETL_DATE字段，目标表的数据=原来旧的的表数据+本次复制的所有源表数据

1. 准备测试数据

--建立源表-------------------
DROP TABLE EMP_YUAN;
CREATE TABLE EMP_YUAN AS
SELECT T.EMPNO,
       T.JOB,
       T.SAL,
       SYSDATE-2 HIRE_DATE,
       SYSDATE-2 UP_DATE
FROM EMP T WHERE DEPTNO = 10;

--建立目标表-----------------
DROP TABLE EMP_MUBIAO;
CREATE TABLE EMP_MUBIAO AS
SELECT T.EMPNO,
       T.JOB, 
       T.SAL,
       T.HIRE_DATE,
       T.UP_DATE,
       SYSDATE-1 ETL_DATE
FROM EMP_YUAN T;

-------------------------------------------------------
SELECT * FROM EMP_YUAN; ----业务数据发生的表
SELECT * FROM EMP_MUBIAO;----备份或数仓中的历史表
-------------------------------------------------------

INSERT INTO EMP_YUAN
SELECT T.EMPNO,
       T.JOB, 
       T.SAL,
       SYSDATE-1 HIRE_DATE,
       SYSDATE-1 UP_DATE
FROM EMP T WHERE DEPTNO = 20;
-----新入职了五个员工

UPDATE EMP_YUAN SET SAL=2000,UP_DATE = SYSDATE-1 WHERE EMPNO = 7934;
----员工7934的工资发生了变化
COMMIT;

2. 表输入查找源表emp_yuan数据
3. 获取系统时间，建立一个新字段ETL_DATE和目标表的字段对应
4. 表输出，用源表数据覆盖目标表内容

10、增量更新。对比原表，更新和插入新的内容。

1. 查询源表数据
2. 获取系统时间etl_date,用来匹配目标表字段
3. 插入/更新

利用‘作业’定时发送邮件

1. 新建一个作业
2. 拖入 start、转换、成功、发送邮件
3. 转换随便放入一个有效的转换文件
4. 发送邮件的前提是要当QQ后台，账户->设置->第三发服务->开启服务后保留授权码等会要用
5. 地址填写
6. 服务器
7. 邮件消息
8. 都填写好后，运行，信息会发送到对应的收件人QQ邮箱里。

利用作业循环打印不同角色下数据库表数据

三个用户 u1 u2 u3 密码 分别也是 u1 u2 u3
这三个用户下 都有一张emp表 分别是 三个部门的用户信息
查询用户下emp表中各有多少条数据 输出列三列 用户名 uname 表名 tname 条数 tcount

1. 首先先创建不同角色的对应的数据库数据

create user u1 identified by s1;
create user u2 identified by s2;
create user u3 identified by s3;

grant dba to u1;
grant dba to u2;
grant dba to u3;

create table u1.emp as  select * from hu.emp where deptno=10; 
create table u2.emp as  select * from hu.emp where deptno=20; 
create table u3.emp as  select * from hu.emp where deptno=30;

select * from u1.emp;
select * from u2.emp;
select * from u3.emp;

create table tt(username varchar2(20),
                tablename varchar2(20),
                tcount   number);
select * from tt;

如果无法create用户，权限不够的话就进行授权。cmd进入命令框，执行下面三句命令。

sqlplus /nolog
SQL>conn /as sysdba;
SQL>grant all privileges to hu;

2. 准备一张excel表包含用户名和密码
3. 一共有五个步骤，下面详细解释。

• 1_获取记录，新建一个转换，excel表输入然后复制记录到结果，保存转换文件- 2_设置变量，新建一个转换，拖拽从结果获取记录，设置变量
  

• 3_查询结果输出，新建一个数据库连接，变量命名用户名和密码。注意查询语句为select '${u}' username,'emp' tablename,count(1) tcount from emp
  

• 4_循环获取数据，先创建一个作业，这个作业流程，包括进行设置变量转换，查询结果输出转换，方便后续循环。
  
  

• 5_执行整个流程，读取记录->循环获取数据输出结果

• 整个流程写完后，执行作业5_执行整个流程，成功后，可以在数据库查看结果