FlinkCDC 抽取oracle数据

大数据刚入门的小菜鸟

已于 2022-05-26 15:37:30 修改

阅读量3.3k

点赞数

分类专栏：大数据框架文章标签： oracle 数据库 database

于 2022-05-26 15:36:28 首次发布

本文链接：https://blog.csdn.net/weixin_46580067/article/details/124985447

版权

大数据框架专栏收录该内容

11 篇文章 2 订阅

订阅专栏

1、环境准备

1、flink 1.13.0
2、oracle 11g docker 安装
3、flink-connector-oracle-cdc 2.1.0

oracle 安装

docker run -d -p 1521:1521 --name oracle11g registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g
docker exec -it oracle11 bash
su - root # helowin 密码

oracle 配置

sqlplus / as sysdba
-- 启用日志归档
alter system set db_recovery_file_dest_size = 10G;
alter system set db_recovery_file_dest = '/home/oracle/oracle-data-test' scope=spfile;
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;

-- 检查日志归档是否开启
archive log list;

-- 为捕获的数据库启用补充日志记录,以便数据更改捕获更改的数据库行之前的状态,下面说明了如何在数据库级别进行配置。
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- 创建表空间
CREATE TABLESPACE logminer_tbs DATAFILE '/home/oracle/logminer_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

-- 创建用户family绑定表空间LOGMINER_TBS
CREATE USER family IDENTIFIED BY zyhcdc DEFAULT TABLESPACE LOGMINER_TBS QUOTA UNLIMITED ON LOGMINER_TBS;

-- 授予family用户dba的权限
 grant connect,resource,dba to family;

-- 并授予权限
  GRANT CREATE SESSION TO family;
  GRANT SELECT ON V_$DATABASE to family;
  GRANT FLASHBACK ANY TABLE TO family;
  GRANT SELECT ANY TABLE TO family;
  GRANT SELECT_CATALOG_ROLE TO family;
  GRANT EXECUTE_CATALOG_ROLE TO family;
  GRANT SELECT ANY TRANSACTION TO family;
  GRANT EXECUTE ON SYS.DBMS_LOGMNR TO family;
  GRANT SELECT ON V_$LOGMNR_CONTENTS TO family;
  GRANT CREATE TABLE TO family;
  GRANT LOCK ANY TABLE TO family;
  GRANT ALTER ANY TABLE TO family;
  GRANT CREATE SEQUENCE TO family;

  GRANT EXECUTE ON DBMS_LOGMNR TO family;
  GRANT EXECUTE ON DBMS_LOGMNR_D TO family;

  GRANT SELECT ON V_$LOG TO family;
  GRANT SELECT ON V_$LOG_HISTORY TO family;
  GRANT SELECT ON V_$LOGMNR_LOGS TO family;
  GRANT SELECT ON V_$LOGMNR_CONTENTS TO family;
  GRANT SELECT ON V_$LOGMNR_PARAMETERS TO family;
  GRANT SELECT ON V_$LOGFILE TO family;
  GRANT SELECT ON V_$ARCHIVED_LOG TO family;
  GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO family;
  
-- 创建 STUDENT_INFO 表
create table student_info (
  sid         number(10) constraint pk_sid primary key,
  sname       varchar2(10),
  sex         varchar2(2)
);

-- 修改STUDENT_INFO表让其支持增量日志，这句先在Oracle里创建user表再执行
ALTER TABLE FAMILY.STUDENT_INFO ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

insert into student_info (sid, sname, sex) values (1, 'lisi', 'm');
insert into student_info (sid, sname, sex) values (2, 'zhangsan', 'm');
insert into student_info (sid, sname, sex) values (3, 'wangwu', 'm');

2、maven依赖:

	<properties>
        <java.version>1.8</java.version>
        <maven.compiler.source>${java.version}</maven.compiler.source>
        <maven.compiler.target>${java.version}</maven.compiler.target>
        <flink.version>1.13.0</flink.version>
        <scala.version>2.12</scala.version>
    </properties>
    <dependencies>

        <dependency>
            <groupId>org.apache.flink</groupId>
            <artifactId>flink-table-planner-blink_${scala.version}</artifactId>
            <version>${flink.version}</version>
        </dependency>

        <dependency>
            <groupId>com.ververica</groupId>
            <artifactId>flink-connector-oracle-cdc</artifactId>
            <version>2.1.0</version>
        </dependency>

        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc10</artifactId>
            <version>19.10.0.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.flink</groupId>
            <artifactId>flink-streaming-java_${scala.version}</artifactId>
            <version>${flink.version}</version>
        </dependency>

        <dependency>
            <groupId>org.apache.flink</groupId>
            <artifactId>flink-clients_${scala.version}</artifactId>
            <version>${flink.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.flink</groupId>
            <artifactId>flink-cep_${scala.version}</artifactId>
            <version>${flink.version}</version>
        </dependency>

        <dependency>
            <groupId>org.apache.flink</groupId>
            <artifactId>flink-java</artifactId>
            <version>${flink.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.flink</groupId>
            <artifactId>flink-json</artifactId>
            <version>${flink.version}</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.68</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.20</version>
        </dependency>
        <!--Flink 默认使用的是 slf4j 记录日志，相当于一个日志的接口,我们这里使用 log4j 作为
       具体的日志实现-->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.7.25</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>1.7.25</version>
        </dependency>
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-to-slf4j</artifactId>
            <version>2.14.0</version>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-assembly-plugin</artifactId>
                <version>3.0.0</version>
                <configuration>
                    <descriptorRefs>
                        <descriptorRef>jar-with-dependencies</descriptorRef>
                    </descriptorRefs>
                </configuration>
                <executions>
                    <execution>
                        <id>make-assembly</id>
                        <phase>package</phase>
                        <goals>
                            <goal>single</goal>

                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

3、功能代码:

import org.apache.flink.streaming.api.environment.StreamExecutionEnvironment;
import org.apache.flink.table.api.TableResult;
import org.apache.flink.table.api.bridge.java.StreamTableEnvironment;

public class OracleSourceExample {
    public static void main(String[] args) throws Exception {

        StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
        env.setParallelism(1);
        env.disableOperatorChaining();
        StreamTableEnvironment tableEnv = StreamTableEnvironment.create(env);

        tableEnv.executeSql("CREATE TABLE student_info (\n" +
                "     SID INT NOT NULL,\n" +                   // 注意字段名要大写
                "     SNAME STRING,\n" +
                "     SEX STRING,\n" +
                "     PRIMARY KEY(SID) NOT ENFORCED\n" +
                "     ) WITH (\n" +
                "     'connector' = 'oracle-cdc',\n" +
                "     'hostname' = 'localhost',\n" +
                "     'port' = '1521',\n" +
                "     'username' = 'familys',\n" +
                "     'password' = 'familys',\n" +
                "     'database-name' = 'helowin',\n" +
                "     'schema-name' = 'FAMILY',\n" +           // 注意这里要大写
                "     'table-name' = 'STUDENT_INFO',\n" +
                "     'debezium.log.mining.continuous.mine'='true',\n"+
                "     'debezium.log.mining.strategy'='online_catalog',\n" +
                "     'debezium.database.tablename.case.insensitive'='false',\n"+
                "     'scan.startup.mode' = 'initial')");

        //insert into student_info (sid, sname, sex) values (28, 'zyh-test', 'm');
        //insert into student_info (sid, sname, sex) values (1, 'zyh-test', 'm');
        //insert into student_info (sid, sname, sex) values (26, 'zyhtt', 'm');
        //update student_info t set t.sname='zyh666', t.sex='m' where t.sno=26;
        TableResult tableResult = tableEnv.executeSql("select * from student_info");
        tableResult.print();
        env.execute();
    }
}

4、测试

在这里插入图片描述

参考：

flink oracle 官方文档

https://blog.csdn.net/qq_36039236/article/details/124224500?spm=1001.2014.3001.5502

大数据刚入门的小菜鸟

关注

0
点赞
踩
11

收藏

觉得还不错? 一键收藏
0
评论
FlinkCDC 抽取oracle数据

1、环境准备1、flink 1.13.02、oracle 11g docker 安装3、flink-connector-oracle-cdc 2.1.0oracle 安装docker run -d -p 1521:1521 --name oracle11g registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11gdocker exec -it oracle11 bashsu - root # helowin 密码oracle 配置sqlp
复制链接

扫一扫

专栏目录