工具包使用

• 服务器安装oracle client，下载地址

# 查看oracle版本为11.2
select * from v$version

• 下载rpm包，上传到服务器/home/software/oracle目录

oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm  
oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm  
oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm

• 执行命令安装

rpm -ivh oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm 
rpm -ivh oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm
rpm -ivh oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm

• 创建文件夹

mkdir -p /usr/lib/oracle/11.2/client64/network/admin

• 配置环境变量

vim /etc/profile
 
export  ORACLE_HOME=/usr/lib/oracle/11.2/client64
export  TNS_ADMIN=$ORACLE_HOME/network/admin
export  LD_LIBRARY_PATH=$ORACLE_HOME/lib
export  LANG=zh_CN.UTF-8
export  NLS_LANG=AMERICAN_AMERICA.utf8
export  PATH=$PATH:$ORACLE_HOME/bin
 
source /etc/profile
 
# 测试，输出sqlplus
[root@node1 oracle]# sqlplus
 
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 19 20:04:23 2024
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
Enter user-name: ciss
Enter password: 

• 项目中新建文件auto_create_hive_table/cn/itcast/utils/ReadOracleCx.py

import cx_Oracle
import datetime
import os
 
# 连接Oracle
connection = cx_Oracle.connect('ciss', '123456', cx_Oracle.makedsn( '192.168.128.100', '1521',None,'helowin')   )
 
# 创建游标
cursor = connection.cursor()
 
current_time = datetime.datetime.now().time()
print("定时清洗任务开始执行 Time:", current_time)
 
# 执行SQL
cursor.execute("select * from CISS4.CAP_USER")
 
# # 获取查询结果
result = cursor.fetchall()
 
# 打印查询结果
for row in result:
    print(row)
 
# 提交事务
connection.commit()
 
# 关闭游标和数据库连接
cursor.close()
connection.close()

• 上传到服务器，Tools -> Deployment -> Upload/Configuration
  

cd /tmp/pycharm_project_178/auto_create_hive_table/cn/itcast/utils
 
python3 ReadOracleCx.py
 
# 查询结果如下
(3487, 'default', 'wangfl', 'k2xvHUmCHWw=', None, 'wangfl', 'local', '1', datetime.datetime(2019, 8, 2, 15, 28, 43, 950000), 'default', datetime.datetime(2019, 8, 2, 15, 28, 43, 950000), None, datetime.datetime(2019, 8, 2, 0, 0), None, None, None, None, None, 'sysadmin', datetime.datetime(2019, 8, 2, 15, 28, 43, 950000))
(3499, 'default', 'wuyf01', 'k2xvHUmCHWw=', None, 'wuyf01', 'local', '1', datetime.datetime(2019, 9, 23, 11, 42, 2, 296000), 'default', datetime.datetime(2019, 9, 23, 11, 42, 2, 296000), None, datetime.datetime(2019, 9, 23, 0, 0), None, None, None, None, None, 'sysadmin', datetime.datetime(2019, 9, 23, 11, 42, 2, 296000))
(3500, 'default', 'chuhr', 'k2xvHUmCHWw=', None, 'chuhr', 'local', '1', datetime.datetime(2019, 9, 23, 11, 42, 40, 889000), 'default', datetime.datetime(2019, 9, 23, 11, 42, 40, 889000), None, datetime.datetime(2019, 9, 23, 0, 0), None, None, None, None, None, 'sysadmin', datetime.datetime(2019, 9, 23, 11, 42, 40, 889000))
//。。。

查看详情

• shell脚本安装方式

#!/bin/bash
 
servers=" 服务器1 服务器2 ....."
env_path="xxxxx"
for server in ${servers}
do
    echo "正在连接服务器 $server"
 
    ssh $server "rpm -ivh /xxxxx/oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm"
    ssh $server "rpm -ivh /xxxxx/oracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64.rpm"
    ssh $server "rpm -ivh /xxxxx/oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm"
 
    # env
    ssh $server "echo 'export ORACLE_HOME=/usr/lib/oracle/12.2/client64' >> ${env_path}"
    ssh $server "echo 'export TNS_ADMIN=\$ORACLE_HOME/network/admin' >> ${env_path}"
    ssh $server "echo 'export LD_LIBRARY_PATH=\$ORACLE_HOME/lib' >> ${env_path}"
    ssh $server "echo 'export LANG=zh_CN.UTF-8' >> ${env_path}"
    ssh $server "echo 'export NLS_LANG=AMERICAN_AMERICA.utf8' >> ${env_path}"
    ssh $server "echo 'export PATH=\$PATH:\$ORACLE_HOME/bin' >> ${env_path}"
 
    # source 
    ssh $server "source ${env_path}"
    # cx_oracle
    ssh $server "pip3 install /xxxxx/cx_Oracle-8.3.0-cp38-cp38-manylinux_2_5_x86_64.manylinux1_x86_64.manylinux_2_12_x86_64.manylinux2010_x86_64.whl"
 
    echo "服务器 $server 安装完成"
done
 
echo "批量安装完成"

• 修改\OneMake30\auto_create_hive_table\resources\config.txt

# oracle connect info
[OracleConn]
oracleHost=192.168.128.100
oraclePort=1521
oracleSID=helowin
oracleUName=ciss
oraclePassWord=123456

• \OneMake30\auto_create_hive_table\cn\itcast\utils目录下编写FileUtil.py

#!/usr/bin/env python
# @desc : todo 实现读取表名文件
__coding__ = "utf-8"
__author__ = "itcast"
 
 
def readFileContent(fileName):
    """
    加载表名所在的文件
    :param fileName:存有表名的文件路径
    :return:存有所有表名的列表集合
    """
    # 定义一个空的列表，用于存放表名，最后返回
    tableNameList = []
    # 打开一个文件
    fr = open(fileName)
    # 遍历每一行
    for line in fr.readlines():
        # 将每一行尾部的换行删掉
        curLine = line.rstrip('\n')
        # 把表名放入列表
        tableNameList.append(curLine)
    # 返回所有表名的列表
    return tableNameList
 
from auto_create_hive_table.cn.itcast.utils import TableNameUtil
 
if __name__ == '__main__':
    tableList =  readFileContent("/tmp/pycharm_project_178/dw/ods/meta_data/tablenames.txt")
    # 调用工具类，将全量表的表名存入一个列表，将增量表的表名存入另外一个列表中，再将这两个列表放入一个列表中：List[2个List元素：List1[44张全量表的表名]，List2[57张增量表的表名]]
    tableNameList = TableNameUtil.getODSTableNameList(tableList)
    # ------------------测试：输出获取到的连接以及所有表名
    #
    for tbnames in tableList:
        print("---------------------")
        # for tbname in tbnames:
        print(tbnames)

• pycharm执行，控制台打印

ssh://root@192.168.128.100:22/home/software/anaconda3/bin/python3 -u /tmp/pycharm_project_178/auto_create_hive_table/cn/itcast/utils/FileUtil.py
---------------------
ciss_base_areas
---------------------
ciss_base_baseinfo
---------------------
ciss_base_csp
---------------------
ciss_base_customer
//...