无需插件就能实现异构数据库的互联互通？（powershell妙用）

news/2024/4/28 4:56:10

  前两天在DBA群里有大佬分享了利用Oracle Database Gateway（透明网关）实现sqlserver和oracle 的数据交互，这里让我想到前些年写的一些powershell脚本用来做sqlserver和oracle的数据交互，powershell是windows自带的一个脚本工具，常见的是处理一些系统管理等工作，其实powershell也是可以作为DBA的好帮手，实现异构数据库的交互，数据库监控，发送邮件等等工作，本文将引用几个范例来介绍powershell在数据库上的用法。

以下是 PowerShell 的一些主要特点和功能：

  1. 命令行 Shell：PowerShell 提供了一个交互式的命令行 Shell，用户可以在其中执行各种命令和操作系统任务。它包含了丰富的命令集，可以进行文件操作、进程管理、网络配置等各种操作。

  2. 脚本语言：PowerShell 是一种完整的脚本语言，它具有变量、循环、条件语句等常见编程语言的特性。用户可以编写 PowerShell 脚本来自动化重复性的任务，简化管理工作。

  3. 对象导向：PowerShell 是基于对象的，它处理的不是简单的文本流，而是.NET 对象。这使得在 PowerShell 中进行数据处理更为直观和灵活。

  4. 模块化：PowerShell 支持模块化，可以通过安装和导入模块来扩展功能。Microsoft 和第三方开发者提供了大量的模块，用于管理 Azure、Active Directory、Exchange 等 Microsoft 产品，以及其他常用任务的自动化。

  5. 管道（Pipeline）：PowerShell 的管道功能允许将多个命令连接起来，其中一个命令的输出可以作为另一个命令的输入。这种管道功能使得命令的组合和操作变得更加灵活。

  6. 远程管理：PowerShell 支持远程管理，可以通过 PowerShell Remoting 在远程计算机上执行命令和脚本，管理远程计算机。

  7. 自动完成：PowerShell 提供了自动完成功能，可以通过按下 Tab 键来补全命令、参数、变量等，提高了命令行的使用效率。

  8. 跨平台：除了 Windows，PowerShell 也可以在 Linux 和 macOS 上运行。Microsoft 提供了 PowerShell Core 版本，可以在非 Windows 平台上使用。

PowerShell 是一种功能强大且灵活的工具，适用于系统管理员、开发人员和 DevOps 工程师等各种角色，用于管理、自动化和配置 Windows 和其他操作系统。

1.Powershell操作sqlserver数据库

$SQLServer			    = '10.x.x.xx'  ##sqlsever ip
$SQLDatabase			    = 'master'      ##database name
$SQLUserID			    = 'sa'          ##user
$SQLPassword			    = 'xxxxxx'      ##password
$SQLPort                            = '1433'        ##port number  if default port can ignore

function sqlConn() ##connect to db{[void][System.Reflection.Assembly]::LoadWithPartialName("System.Data.SqlClient")$ConnectionString				= "Server=$SQLServer;Database=$SQLDatabase;User Id=$SQLUserID;Password=$SQLPassword;Integrated Security=no"$SqlConnection					= New-Object System.Data.SqlClient.SqlConnection$SqlConnection.ConnectionString	= $ConnectionStringreturn $SqlConnection
}function SqlQuery ($SqlLine) ##select 
{$SqlConnection					= sqlConn$SqlCommand						= New-Object System.Data.SqlClient.SqlCommand$SqlCommand.CommandText			= $SqlLine$SqlCommand.Connection			= $SqlConnection$SqlDataAdapter					= New-Object system.Data.SqlClient.SqlDataAdapter$SqlDataAdapter.SelectCommand	= $SqlCommand$DataSet						= New-Object system.Data.DataSet$SqlConnection.Open()$SqlDataAdapter.Fill($DataSet) | Out-Null$SqlDataAdapter.Dispose()$SqlConnection.Close()return $DataSet.Tables[0]
}function SqlCommand ($SqlLine)  ##dml sql
{$SqlConnection					= sqlConn$SqlCommand						= New-Object System.Data.SqlClient.SqlCommand$SqlCommand.CommandText			= $SqlLine$SqlCommand.Connection			= $SqlConnection$SqlConnection.Open()return $SqlCommand.ExecuteNonQuery()$SqlConnection.Close()
}
################上面部分为sqlserver连接通用模板############################### SqlQuery('SELECT * FROM [master].[INFORMATION_SCHEMA].[TABLES]')   ##查询系统表 #######查询[INFORMATION_SCHEMA].[TABLES]记录循环插入到tempdb.dbo.test表中foreach ($invalue in SqlQuery('SELECT table_name,table_type FROM [master].[INFORMATION_SCHEMA].[TABLES]'))  ###将查询的结果暂存为$invalue{$tablename = $invalue.table_name   ##分别定义需要插入的值$tabletype = $invalue.table_type  $SqlLine = "INSERT INTO tempdb.dbo.test (table_name, table_type) VALUES ('$tablename', '$tabletype')"   ###insert 语句SqlCommand $SqlLine    ###执行  
}

执行结果如下

2.Powershell操作oracle数据库

    场景利用powershell 查询出远端oracle数据库 并将查询的结果写入到本地文件，如果有数据写入则发用邮件 并将文件作为附件

$OracleDatabase                        = 'mtrpdb1'   ##tns name  当前环境安装了oracle client
$OracleUserID			       = 'mtr'       ##username
$OraclePassword			       = 'xxxxx'     ##password
$OraclePort1                           = '1521'      ##port number  if use default port can ignorefunction OracleConn()
{[void][System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")$ConnectionString				= "Data Source=$OracleDatabase1;User Id=$OracleUserID1;Password=$OraclePassword1;Integrated Security=no"$SqlConnection					= New-Object System.Data.OracleClient.OracleConnection$SqlConnection.ConnectionString	= $ConnectionStringreturn $SqlConnection
}function OracleQuery ($SqlLine)
{$SqlConnection					= OracleConn$SqlDataAdapter					= New-Object System.Data.OracleClient.OracleDataAdapter$SqlCommand						= New-Object System.Data.OracleClient.OracleCommand$DataSet						= New-Object system.Data.DataSet$SqlCommand.CommandText			= $SqlLine$SqlCommand.Connection			= $SqlConnection$SqlDataAdapter.SelectCommand	= $SqlCommand$SqlConnection.Open()$SqlDataAdapter.Fill($DataSet) | Out-Null$SqlDataAdapter.Dispose()$SqlConnection.Close()return $DataSet.Tables[0]
}function OracleCommand ($SqlLine)
{$SqlConnection					= OracleConn$SqlCommand						= New-Object System.Data.OracleClient.OracleCommand$SqlCommand.CommandText			= $SqlLine$SqlCommand.Connection			= $SqlConnection$SqlConnection.Open()return $SqlCommand.ExecuteNonQuery()$SqlConnection.Close()
}
################上面部分为oracle连接通用模板############################### function sendmail ($body,$toname,$file)  #######带附件邮件函数模板
{$today = Get-Date$SmtpClient = new-object system.net.mail.smtpClient $mailmessage = New-Object system.net.mail.mailmessage $attachment = new-Object System.Net.Mail.Attachment("$file")$SmtpClient.Host = "10.0.x.xx"   ##smtp server ip$mailmessage.from = ("norton.fan@mail.com") ##发件人地址$mailmessage.To.add($toname)#$mailmessage.cc.add(" ") $mailmessage.Subject = "DB Alert Test!" $mailmessage.IsBodyHtml = $true$mailmessage.Attachments.add($attachment)$mailmessage.Body = "<h4>$today `n</h4>" + $body$smtpclient.Send($mailmessage) 
}
##############################上面为邮件通用模板#########################################	$mailname = "xiaofan23z@163.com"   ##收件人mail#$mailname1 = "xiaofan23z@163.com"$LOGFILE = "C:\dbeaver\test\logfile.txt"  ##create a file store informationOracleQuery "SELECT * FROM DB_TOTAL dt WHERE CREATION_DATE >sysdate -10"  ##查询数据量统计信息
#############将查询结果写入到logfile 变发送邮件###############################	foreach ($Info in (OracleQuery "SELECT total_gb,creation_date,site FROM DB_TOTAL dt WHERE CREATION_DATE >sysdate -10")){$total_gb = $Info.total_gb.ToString()$creation_date = $Info.creation_date.ToString()$site = $Info.site.ToString()$content = "$total_gb $creation_date $site"##if have new information insert into this logfileout-file -FilePath $LOGFILE -InputObject $content -Append  ##写入到logfile}$mass = type $LOGFILE##$l = $mass.Length
if($mass.Length -ge 0 ){sendmail "$mass" "$mailname" "$LOGFILE" ##发送邮件}

邮件结果

附件如图

3.Powershell操作mysql数据库

需要下载MySQL Connector/NET，下载地址如下

MySQL :: Download MySQL Connector/NET (Archived Versions)

​$MySQLServer            = '10.0.x.x'
$MySQLDatabase		= 'dbname'
$MySQLUserID		= 'user'
$MySQLPassword		= 'password'
$MySQLPort          = '3306'  ##port number if use default port can ignorefunction mysqlConn()
{Add-Type -Path "C:\Program Files (x86)\MySQL\MySQL Connector Net 8.0.28\Assemblies\v4.5.2\MySql.Data.dll"  ###这个默认的安装目录，根据个人环境变更$ConnectionString = "server=$MySQLServer;port=$MySQLPort;database=$MySQLDatabase;uid=$MySQLUserID;password=$MySQLPassword"$SqlConnection = New-Object MySql.Data.MySqlClient.MySqlConnection$SqlConnection.ConnectionString = $ConnectionStringreturn $SqlConnection
}function mysqlQuery($SqlLine)
{$SqlConnection = mysqlConn$SqlCommand = New-Object MySql.Data.MySqlClient.MySqlCommand$SqlCommand.CommandText = $SqlLine$SqlCommand.Connection = $SqlConnection$SqlDataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter$SqlDataAdapter.SelectCommand = $SqlCommand$DataSet = New-Object system.Data.DataSet$SqlConnection.Open()$SqlDataAdapter.Fill($DataSet) | Out-Null$SqlDataAdapter.Dispose()$SqlConnection.Close()return $DataSet.Tables[0]
}function mysqlCommand($SqlLine)
{$SqlConnection = mysqlConn$SqlCommand = New-Object MySql.Data.MySqlClient.MySqlCommand$SqlCommand.CommandText = $SqlLine$SqlCommand.Connection = $SqlConnection$SqlConnection.Open()$result = $SqlCommand.ExecuteNonQuery()$SqlConnection.Close()return $result
}
##################上面为mysql通用连接模板#######################################mysqlquery('select * from `table1`  where `id` =14')

上面为最简单的范例 查询一个表的信息并返回

4.Powershell操作postgresql数据库

pg也是类似，因为手头没有可用的pg测试库就没有加范例，但是这个标准模板是可以用的

$PostgreSQLServer    = 'hostip'
$PostgreSQLDatabase  = 'mydatabase'
$PostgreSQLUserID    = 'username'
$PostgreSQLPassword  = 'password'
$PostgreSQLPort      = '5432'  # 默认 PostgreSQL 端口为 5432function pgConn()
{Add-Type -Path "C:\Program Files\PostgreSQL\13\bin\Npgsql.dll"  # 这里的路径需要根据你的实际安装路径进行修改$ConnectionString = "Host=$PostgreSQLServer;Port=$PostgreSQLPort;Database=$PostgreSQLDatabase;Username=$PostgreSQLUserID;Password=$PostgreSQLPassword"$SqlConnection = New-Object Npgsql.NpgsqlConnection$SqlConnection.ConnectionString = $ConnectionStringreturn $SqlConnection
}function pgsqlQuery($SqlLine)
{$SqlConnection = pgConn$SqlCommand = New-Object Npgsql.NpgsqlCommand$SqlCommand.CommandText = $SqlLine$SqlCommand.Connection = $SqlConnection$SqlDataAdapter = New-Object Npgsql.NpgsqlDataAdapter$SqlDataAdapter.SelectCommand = $SqlCommand$DataSet = New-Object system.Data.DataSet$SqlConnection.Open()$SqlDataAdapter.Fill($DataSet) | Out-Null$SqlDataAdapter.Dispose()$SqlConnection.Close()return $DataSet.Tables[0]
}function pgsqlCommand($SqlLine)
{$SqlConnection = pgConn$SqlCommand = New-Object Npgsql.NpgsqlCommand$SqlCommand.CommandText = $SqlLine$SqlCommand.Connection = $SqlConnection$SqlConnection.Open()$result = $SqlCommand.ExecuteNonQuery()$SqlConnection.Close()return $result
}

5.Powershell 实现异构数据交互

  这里示范一个例子，查询oracle数据库中一个表的数据，并将查询结果插入到mysql和sqlserver数据库中

####################mysql###########################################
$MySQLServer		= '10.x.x.x'
$MySQLDatabase		= 'dbname'
$MySQLUserID		= 'user'
$MySQLPassword		= 'password'
$MySQLPort          = '3306'function mysqlConn()
{Add-Type -Path "C:\Program Files (x86)\MySQL\MySQL Connector Net 8.0.28\Assemblies\v4.5.2\MySql.Data.dll"$ConnectionString = "server=$MySQLServer;port=$MySQLPort;database=$MySQLDatabase;uid=$MySQLUserID;password=$MySQLPassword"$SqlConnection = New-Object MySql.Data.MySqlClient.MySqlConnection$SqlConnection.ConnectionString = $ConnectionStringreturn $SqlConnection
}function mysqlQuery($SqlLine)
{$SqlConnection = mysqlConn$SqlCommand = New-Object MySql.Data.MySqlClient.MySqlCommand$SqlCommand.CommandText = $SqlLine$SqlCommand.Connection = $SqlConnection$SqlDataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter$SqlDataAdapter.SelectCommand = $SqlCommand$DataSet = New-Object system.Data.DataSet$SqlConnection.Open()$SqlDataAdapter.Fill($DataSet) | Out-Null$SqlDataAdapter.Dispose()$SqlConnection.Close()return $DataSet.Tables[0]
}function mysqlCommand($SqlLine)
{$SqlConnection = mysqlConn$SqlCommand = New-Object MySql.Data.MySqlClient.MySqlCommand$SqlCommand.CommandText = $SqlLine$SqlCommand.Connection = $SqlConnection$SqlConnection.Open()$result = $SqlCommand.ExecuteNonQuery()$SqlConnection.Close()return $result
}##########################sqlserver############################################
$SQLServer						= '10.x.x.x'
$SQLDatabase						= 'dbname'
$SQLUserID						= 'user'
$SQLPassword						= 'password'
$SQLPort                                                = '1433'function sqlConn()
{[void][System.Reflection.Assembly]::LoadWithPartialName("System.Data.SqlClient")$ConnectionString				= "Server=$SQLServer;Database=$SQLDatabase;User Id=$SQLUserID;Password=$SQLPassword;Integrated Security=no"$SqlConnection					= New-Object System.Data.SqlClient.SqlConnection$SqlConnection.ConnectionString	= $ConnectionStringreturn $SqlConnection
}function SqlQuery ($SqlLine)
{$SqlConnection					= sqlConn$SqlCommand						= New-Object System.Data.SqlClient.SqlCommand$SqlCommand.CommandText			= $SqlLine$SqlCommand.Connection			= $SqlConnection$SqlDataAdapter					= New-Object system.Data.SqlClient.SqlDataAdapter$SqlDataAdapter.SelectCommand	= $SqlCommand$DataSet						= New-Object system.Data.DataSet$SqlConnection.Open()$SqlDataAdapter.Fill($DataSet) | Out-Null$SqlDataAdapter.Dispose()$SqlConnection.Close()return $DataSet.Tables[0]
}function SqlCommand ($SqlLine)
{$SqlConnection					= sqlConn$SqlCommand						= New-Object System.Data.SqlClient.SqlCommand$SqlCommand.CommandText			= $SqlLine$SqlCommand.Connection			= $SqlConnection$SqlConnection.Open()return $SqlCommand.ExecuteNonQuery()$SqlConnection.Close()
}#############################oracle#############################################
$OracleDatabase						= 'tnsname'
$OracleUserID						= 'user'
$OraclePassword						= 'password'
$OraclePort                                             = '1521'function OracleConn()
{[void][System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")$ConnectionString				= "Data Source=$OracleDatabase;User Id=$OracleUserID;Password=$OraclePassword;Integrated Security=no"$SqlConnection					= New-Object System.Data.OracleClient.OracleConnection$SqlConnection.ConnectionString	= $ConnectionStringreturn $SqlConnection
}function OracleQuery ($SqlLine)
{$SqlConnection					= OracleConn$SqlDataAdapter					= New-Object System.Data.OracleClient.OracleDataAdapter$SqlCommand						= New-Object System.Data.OracleClient.OracleCommand$DataSet						= New-Object system.Data.DataSet$SqlCommand.CommandText			= $SqlLine$SqlCommand.Connection			= $SqlConnection$SqlDataAdapter.SelectCommand	= $SqlCommand$SqlConnection.Open()$SqlDataAdapter.Fill($DataSet) | Out-Null$SqlDataAdapter.Dispose()$SqlConnection.Close()return $DataSet.Tables[0]
}function OracleCommand ($SqlLine)
{$SqlConnection					= OracleConn$SqlCommand						= New-Object System.Data.OracleClient.OracleCommand$SqlCommand.CommandText			= $SqlLine$SqlCommand.Connection			= $SqlConnection$SqlConnection.Open()return $SqlCommand.ExecuteNonQuery()$SqlConnection.Close()
}######################################################################foreach ($Info in (OracleQuery "SELECT total_gb,site FROM DB_TOTAL WHERE CREATION_DATE >sysdate -10")){$total_gb = $Info.total_gb$site = $Info.site# 在 SQL Server 中插入数据$SqlLine = "INSERT INTO tempdb.dbo.db_total (total_gb, site) VALUES ('$total_gb', '$site')"SqlCommand $SqlLine# 在 MySQL 中插入数据$MysqlLine = "INSERT INTO db_total (total_gb, site) VALUES ('$total_gb', '$site')"mysqlCommand $MysqlLine}

返回结果1 即表示插入成功

oracle 原始数据

插入到mysql 数据

插入到sqlserver 的数据

6.总结

 看了一下自己的powershell脚本记录，十几年前就曾经使用powershell来做oracle的一些例如tablespace的监控，partition table的监控，做的更多的是一些本地文件和数据库直接的交互，在这个方面powershell拥有得天独厚的优势，比如抓取某个目录下的所有文件名，插入到数据库中 等等；虽然这种方式并不太适合大数据量的交互和迁移，但是在一些无需大数据量的应用场景下，还是很有用的，比如设置个定时任务就可以将oracle数据库中的数据，定时抛送到其他异构数据库中（mysql，sqlserver，postgresql等），而这一切都只需要window自带的工具既可！ 希望给大家在日后的数据交互和迁移上提供一点点思路！

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.cpky.cn/p/11194.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈，一经查实，立即删除！

相关文章

oracle 19c 打补丁教程

oracle 19c 打补丁教程

感谢大家的关注&#xff0c;针对上篇文章分析的19c的补丁包部分这里分享一下之前记录的打补丁手册 希望对大家有帮助&#xff01; oracle patch 大全&#xff08;更新至2024-03&#xff09;-CSDN博客这里是补丁下载地址 oracle 19CRAC打补丁到19.20 本文是简洁版只有步骤和…
阅读更多...
道路与航线

道路与航线

一道类似缩点的好题&#xff0c;先按道路缩点 然后将缩点以后的图按照航线做DAG 在DAG上先跑topsort 在每一个团内部跑dijkstra&#xff0c;同时更新top点 很有意思的一道题目 #include<bits/stdc.h> using namespace std; using ll long long; const int N 3e510; co…
阅读更多...
搭建第一个python项目(虚拟环境)

搭建第一个python项目(虚拟环境)

前言 python虚拟环境类似一个虚拟机&#xff0c;主要就是在多个项目中进行一个隔离&#xff0c;防止包的版本冲突或者其他情况&#xff0c;并且在虚拟环境中pip下载包的存放路径也会只在当前项目中&#xff0c;不会放在全局安装目录中。这就是虚拟环境的优势。 python环境安装…
阅读更多...
DC-6靶机

DC-6靶机

一.环境搭建 1.下载地址 靶机下载地址:https://download.vulnhub.com/dc/DC-6.zip 2.虚拟机配置 设置网络为nat模式&#xff0c;启动打开时&#xff0c;发现错误直接重试和点是 打开靶机虚拟机&#xff0c;如下图所示即为正常 二.开始渗透 1.信息收集 查找与攻击机&#…
阅读更多...
HCIP —— 生成树 （下）

HCIP —— 生成树 （下）

目录 STP&#xff08;生成树&#xff09;的角色选举 根网桥 根端口 选举规则&#xff1a; 指定端口 生成树的端口状态 STP的接口状态&#xff1a;禁用、阻塞、侦听、学习、转发 五种状态 禁用状态 阻塞状态 侦听状态 学习状态 转发状态 当生成树拓扑结构发生变化 …
阅读更多...
鸿蒙HarmonyOS应用开发之Native与ArkTS对象绑定

鸿蒙HarmonyOS应用开发之Native与ArkTS对象绑定

场景介绍 通过napi_wrap将ArkTS对象与Native的C对象绑定&#xff0c;后续操作时再通过napi_unwrap将ArkTS对象绑定的C对象取出&#xff0c;并对其进行操作。 使用示例 接口声明、编译配置以及模块注册 接口声明 // index.d.ts export class MyObject {constructor(arg: num…
阅读更多...
推荐文章
最新文章

Copyright @ 2022~2023

友情链接： 我的编程经验分享 一条长河网 尧图网站开发 尧图建网站 广利置业