1.一直以来想写下基于kettle的系列文章,作为较火的数据ETL工具,也是日常项目开发中常用的一款工具,最近刚好挤时间梳理、总结下这块儿的知识体系。
2.熟悉、梳理、总结下Oracle数据库相关知识体系
3.欢迎批评指正,跪谢一键三连!
- kettle访问Oracle数据库并处理数据至execl文件文章目录索引如下所示:
- 【实操视频,辅助实操学习,跪谢一键三连!】【kettle005】kettle访问Oracle数据库并处理数据至execl文件
1.环境搭建、配置
- 1.0 实验环境信息
Windows 10
CentOS 7.1(虚拟机)
Docker version 26.0.2
Dbeaver
Kettle 8.2
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
- 1.1 镜像操作命令
mkdir -p ./data/oracle && chmod 777 ./data/oracle
docker pull wnameless/oracle-xe-11g-r2
docker run --name oracle -d -p 1521:1521 -e ORACLE_ALLOW_REMOTE=true wnameless/oracle-xe-11g-r2
docker ps -a
docker logs 1baa
docker exec -it 1baa /bin/bash
-- 登录修改密码等
sqlplus /nolog
connect sys/oracle as sysdba;
alter user sys identified by newpassword;
alter user system identified by newpassword;
-- 创建用户和授权
create user xxx identified by password;
grant create session to xxx;
grant create table,unlimited tablespace to xxx;
grant select any table to xxx;
grant update any table,drop any table,insert any table to xxx;
-- 查看表空间
select tablespace_name, file_name from dba_data_files;
grant connect,resource,dba to youli;
- 1.2 问题解决记录过程
- 1.2.1 【问题记录1】oracle比较大,无剩余空间导致拉取镜像失败;删除部分镜像及容器;空间还不够的话,扩容Linux虚拟机或进行虚拟机碎片整理。
- 1.2.2 【问题记录2】oracle-11g镜像格式不兼容问题,寻找其他有效oracle镜像
- 如
docker pull registry.aliyuncs.com/zhuyijun/oracle:19c
,版本太大、没空间启动失败 -
docker pull wnameless/oracle-xe-11g-r2
,有效命令
- 1.2.3 【问题记录3】docker镜像版本不兼容问题,尝试了很多次仍旧失败。如改镜像源头,设置环境变量等。
- 1.2.4 【问题记录4】镜像拉取成功,大小区别明显
- 1.2.5 【问题记录5】
docker: invalid reference format: repository name (library/ORACLE_PDB=ORCLPDB1) must be lowercase.
- 手敲一遍,或调整下格式,如
\
,包含空格
- 1.3 oracle容器最终运行效果
2.oracle-xe-11g-r2数据库容器链接测试
hostname: localhost
port: 1521
sid: xe
username: system/sys
password: oracle
- 2.1 链接测试配置详情
- sqlplus 权限授权
- 2.2 建表测试语句
create table youli_testtable0 (
id number primary key,
name varchar2(50) not null,
description clob,
created_at timestamp default current_timestamp
);
comment on table test_table is '这是一个测试表';
comment on column test_table.id is '测试表的唯一标识';
comment on column test_table.name is '测试表的名称';
comment on column test_table.description is '测试表的描述信息';
comment on column test_table.created_at is '记录的创建时间';
create table "youli"."youli_testtable"
( "id" number(22,0) not null enable,
"name" varchar2(100) not null enable,
"email" varchar2(100) not null enable,
"age" number(*,0) not null enable,
"sex" varchar2(100) not null enable,
"id_card" varchar2(100) not null enable,
"mobile_phone" varchar2(100) not null enable,
"visa_card" varchar2(100) not null enable,
"officer_card" varchar2(255),
"address" varchar2(255),
primary key ("id")
)
tablespace users;
INSERT INTO YOULI."youli_testtable"
("id", "name", "email", "age", "sex", "id_card", "mobile_phone", "visa_card", "officer_card", "address")
VALUES
('1', 'youli', 'youli@yaho.net', '45', 'M', '50011919870824110X', '18152276065', 'SYP618183458', '', '北京市朝阳区国贸1');
INSERT INTO YOULI."youli_testtable"
("id", "name", "email", "age", "sex", "id_card", "mobile_phone", "visa_card", "officer_card", "address")
VALUES
('2', 'youli', 'youli@yaho.net', '45', 'M', '50011919870824110X', '18152276065', 'SYP618183458', '', '北京市朝阳区国贸2');
INSERT INTO YOULI."youli_testtable"
("id", "name", "email", "age", "sex", "id_card", "mobile_phone", "visa_card", "officer_card", "address")
VALUES
('3', 'youli', 'youli@yaho.net', '45', 'M', '50011919870824110X', '18152276065', 'SYP618183458', '', '北京市朝阳区国贸3');
INSERT INTO YOULI."youli_testtable"
("id", "name", "email", "age", "sex", "id_card", "mobile_phone", "visa_card", "officer_card", "address")
VALUES
('4', 'youli', 'youli@yaho.net', '45', 'M', '50011919870824110X', '18152276065', 'SYP618183458', '', '北京市朝阳区国贸4');
INSERT INTO YOULI."youli_testtable"
("id", "name", "email", "age", "sex", "id_card", "mobile_phone", "visa_card", "officer_card", "address")
VALUES
('5', 'youli', 'youli@yaho.net', '45', 'M', '50011919870824110X', '18152276065', 'SYP618183458', '', '北京市朝阳区国贸5');
INSERT INTO YOULI."youli_testtable"
("id", "name", "email", "age", "sex", "id_card", "mobile_phone", "visa_card", "officer_card", "address")
VALUES
('6', 'youli', 'youli@yaho.net', '45', 'M', '50011919870824110X', '18152276065', 'SYP618183458', '', '北京市朝阳区国贸6');
INSERT INTO YOULI."youli_testtable"
("id", "name", "email", "age", "sex", "id_card", "mobile_phone", "visa_card", "officer_card", "address")
VALUES
('7', 'youli', 'youli@yaho.net', '45', 'M', '50011919870824110X', '18152276065', 'SYP618183458', '', '北京市朝阳区国贸7');
SELECT * FROM YOULI."youli_testtable"
- 2.3 DBeaver SQL效果测试
3.kettle流程任务配置、效果验证
- 3.1 将Oracle驱动包放到 kettlle lib 目录下,重启 kettle,进行链接测试
- 3.2 kettle链接oracle-xe-11g-r2数据库容器环境测试结果
- 3.3 遗留问题: 新建表 kettle中加载失败(
待进一步排查、解决
)