暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 无需插件就能实现异构数据库的互联互通？（powershell妙用）

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

原创 潇湘秦 2024-03-28
297

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

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

  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	= $ConnectionString
	return $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 ignore

function 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	= $ConnectionString
	return $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 information

 OracleQuery "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 logfile
   out-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，下载地址如下

https://downloads.mysql.com/archives/c-net/



$MySQLServer            = '10.0.x.x'
$MySQLDatabase		= 'dbname'
$MySQLUserID		= 'user'
$MySQLPassword		= 'password'
$MySQLPort          = '3306'  ##port number if use default port can ignore

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 = $ConnectionString
    return $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 端口为 5432

function 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 = $ConnectionString
    return $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 = $ConnectionString
    return $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	= $ConnectionString
	return $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	= $ConnectionString
	return $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自带的工具既可！ 希望给大家在日后的数据交互和迁移上提供一点点思路！


----------------------------------------------------------

欢迎关注作者

墨天伦：潇湘秦的个人主页 - 墨天轮
CSDN：https://blog.csdn.net/xiaofan23z/?type=blog

公众号：潇湘秦的DBA之路

----------------------------------------------------------

墨力计划数据库
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论