下载和处理要在 Oracle 中加载的非常大的压缩 MS 访问文件的最佳方式
本文关键字:MS 压缩 访问 文件 方式 最佳 非常 Oracle 下载和 加载 处理 | 更新日期: 2023-09-27 18:35:25
我必须下载一个通过FTP加密的250mb~ZIP文件密码。下载后,我必须使用通用密码解压缩它,Zip 文件将包含一个 1.5GB MS Access 数据库,我必须读取并与我的数据库 Oracle 中的一些表进行一些连接,并将该数据转换并加载到该 Oracle 数据库中。
我正在寻找完成此过程的最佳方法。我是一名 c# 开发人员,所以我的第一个想法是使用 c#,通过 FtpClient 或 FtpWebRequest 下载文件,然后使用 DotNetZip 等 zip 库并通过 ODBC 打开 MS Access 数据库并使用 ODP.NEt 将记录加载到 Oracle 中,我认为这是我的"简单方法",因为我知道该怎么做。
但是由于这是一个大文件,我知道这可能需要很长时间,我担心时间和效率以及如何减少此过程的时间。
所以我认为将所有请求直接处理到 oracle(从那里下载 FTP,在那里解压缩,然后直接在那里处理信息会减少从 c# 到oracle逐条记录传递的时间)应该减少这个过程的时间,但我不确定这是否是正确的方法这样做。
所以我开始研究 oracle 的库,它可以做我试图归档的事情,我找到了 PLSQL-utils,似乎它们可以做我需要的一切,除了阅读 MS Access 数据库,我开始寻找它并找到了异构服务但我从未使用过它们,所以我对此有点迷茫。
我也听说我可以直接从Oracle使用Java,我知道Java可以通过JDBC连接到MS Access。所以我搜索了一下,发现了一些关于在Oracle数据库中调用Java方法的信息。
这就是我目前所拥有的,但我不知道我应该使用哪种方法,我的意思是,据我所知,RDBMS 用于处理数据,但不用于编程诸如下载文件或类似的东西,这就是为什么我们有 OOP 的语言。
作为附加信息,此过程将在一个月内执行一次或两次,因此我必须安排它,如果它在 oracle 中,可以使用计划作业轻松完成,或者在 c# 中使用计划任务或 Windows 服务(这些是我知道的工具)
我有一些限制
- 我的客户端没有MS SQL Server,也无法为其购买许可证(所以我不能使用DTSX进行此过程)
- 在 Oracle 生产服务器中,也许我没有足够的权限来做所有的事情,但如果它们是最适合流程的,我可以遵守这些权限
- 如果需要后端服务器(Java,托管在IIS或WebLogic或JBoss或任何种类上的c#),则该服务器和Oracle服务器将是不同的。
- 托管在Unix服务器上的Oracle数据库
怎样才能有效地完成所有这些过程,我应该使用 .net 并在我的 Oracle 数据库中逐条记录加载吗?我应该在甲骨文中做所有事情吗?还是没有这些?有没有更好的方法可以做到这一点?
我认为您使用 C# 控制台应用程序使其成为可重复的过程是正确的。这是我在许多项目中用于zip的很棒的免费库。
using (var client = new WebClient())
using (var stream = client.OpenRead(@"ftp://mysite.com/mydb.zip"))
using (var file = File.Create(@"c:'temp'mydb.zip"))
{
stream.CopyTo(@"c:'temp'mydb.zip", 32000);
}
using (ZipFile zip = ZipFile.Read(@"c:'temp'mydb.zip"))
{
ZipEntry e = zip["bigdb.mdb"];
e.Password = "yourpassword";
e.Extract("c:'temp'bigdb.mdb");
}
解压缩后,您可以创建与访问数据库和数据读取器对象的数据连接。然后使用 dbreader 读取行并写入平面文件(避免大型数据集的内存不足异常)。
private constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=yourdbfile.mdb;Jet OLEDB:Database Password=yourpassword;";
OleDbConnection conn = new OleDbConnection(constr);
string query = "SELECT * FROM [YourTable]";
OleDbCommand cmd = new OleDbCommand(query, conn);
OleDbDataReader reader = cmd.ExecuteReader();
int rowNum = 0;
StringBuilder sb = new StringBuilder();
while (reader.Read())
{
// write rows to flat file in chunks of 10K rows.
sb.Append(reader["FieldA"].ToString() + "|");
sb.Append(reader["FieldB"].ToString() + "|");
sb.Append(reader["FieldC"].ToString() + System.Environment.NewLine);
if (rowNum % 10000 == 0)
{
File.AppendText(@"c:'temp'data.psv", sb.ToString());
sb = new StringBuilder();
}
rowNum++;
}
File.AppendText(@"c:'temp'data.psv", sb.ToString());
reader.Close();
填写数据表后,可以将其导出到平面文件。我不建议逐行插入数据,这将非常慢,并且会使您的 Oracle 数据库事务日志膨胀。我不相信 Oracle 10g 有支持批量加载的 .Net 驱动程序,因此您可能需要通过平面文件进行批量加载。
接下来,通过命令行导入到 Oracle 中,可以从 C# 控制台应用调用它。在执行此操作之前,您需要先创建一个控制文件 ctl.ldr,Oracle 首先使用该文件进行批量加载操作。
options (skip=1)
load data
INFILE 'c:'temp'data.psv'
INTO table tblTest
APPEND
FIELDS TERMINATED BY "|" optionally enclosed by '"'
( fielda,fieldb,etc...)
and then
run it in as follows via command line
sqlldr username/pswd@oracle_sid control=ctl.ldr
希望这有帮助,祝你好运!
[编辑]
您还可以查看 .Net Oracle 批量复制类。这是随 Oracle 11g 客户端驱动程序一起提供的。也许它仍然适用于您的 10g 服务器。那里的一个潜在问题是,同一应用程序服务器上的所有其他应用程序也需要使用这些较新的 11g 客户端驱动程序。另一种选择是构建一个使用支持批量加载的耶拿框架的 Java 应用程序。
查看Pentaho。这基本上是一个类似Java开源SSIS的产品。下载并提取 Access 数据库后,此工具应完成其余工作,将数据加载到 Oracle 中。我以前在MySQL中使用过它,它运行得很好,它现在有一个批量的Oracle db加载组件。Pentaho也不需要在您的数据库服务器上运行,因此这可能是您寻找的银弹。权衡是会有一些爬坡时间。
另一个可能的选择是假设您的 Oracle 服务器正在运行 Windows Server,并且您可以将 Access 文件放在该服务器上的本地位置或服务器可以读取它的位置,您可以执行以下操作。
- 创建一个 C# 应用,以下载、提取 Access 数据库并将其复制到 Oracle 可访问位置。
- 在数据库服务器上为访问数据库创建本地系统 DSN(ODBC 条目)。
- 配置并启动 Oracle 侦听器、HS 和连接设置。
- 添加数据库链接。
- 使用链接的数据库和查询进行联接。
本文概述了配置 Oracle 数据库链接以进行访问的整个过程。
我将创建一个可以调用的应用程序、网页或 WCF 服务,该服务将下载文件、解压缩文件,然后将记录直接逐行插入 Oracle 中。我会尝试将所有数据尽可能保存在 Oracle 中。获取并保存。下载后,我不会经常弄乱zip文件。
基本上:
数据 ->处理器应用 ->Oracle
这样事情就可以自动化。我会用 C# 做处理器。
正如我在评论中询问的那样,可以从oracle创建DBLink到另一个数据库。因此,我将创建一个应用程序来下载文件并将其解压缩到特定区域(您的oracle服务器可以读取的区域)。然后,我将按照以下说明为该文件设置DBLink。https://forums.oracle.com/thread/407779 或如 asktomhttp://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4406709207206
然后,直接从读取 msAccess 数据库的预言机导入数据。
我有两个Powershell脚本,第一个帮助我从中国下载ftp文件:
下载 FTP 脚本
# Parametros
param([string]$url, [string]$user, [string]$pass, [string]$folder, [string]$fecpro)
if (!$fecpro)
{
$date = Get-Date
$date = $date.AddDays(-1)
$fecpro = '{0:yyyyMMdd}' -f $date
}
$FtpServer = "ftp://$url/$fecpro"
$Username = $user
$Password = $pass
$RemoteFolder = $FtpServer
if (!$folder)
{
$LocalFolder = $pwd.path + "'"
}
else
{
$LocalFolder = $folder + "'"
if (-not(Test-Path -Path $LocalFolder))
{
New-Item -ItemType Directory -Force -Path $LocalFolder
}
}
Write-Host "Searching *.zip files from $RemoteFolder..."
$existfiles = gci $LocalFolder | Where-Object { $_.name -like "*.zip" }
$cntFile = 0
$totFiles = 1
$listFiles = @(0)*$totfiles
$listFiles[1] = "cs2-" + $fecpro + "-6004-0000-1.zip"
$existfiles = gci $LocalFolder | Where-Object { $_.name -like "*.zip" }
do
{
try
{
$f_exist = $false
$cntFile = $cntFile + 1
if ($existfiles) {
foreach($file in $existfiles) {
$tmpname = $file.name.ToString()
$bool = $listFiles[$cntFile].CompareTo($file.name)
if ($bool -eq 0) {
$f_exist = ($f_exist -or $true)
} else {
$f_exist = ($f_exist -or $false)
}
}
}
if (!$f_exist -and $rescnt['CNT'] -eq 0) {
$ftpfile = $RemoteFolder + "/" + $listFiles[$cntFile]
$FTPRequest1 = [System.Net.FtpWebRequest]::Create($ftpfile)
$FTPRequest1.Credentials = New-Object System.Net.NetworkCredential($Username,$Password)
$FTPRequest1.Method = [System.Net.WebRequestMethods+Ftp]::DownloadFile
$FTPRequest1.UsePassive = $true
$FTPRequest1.UseBinary = $true
$FTPRequest1.KeepAlive = $false
$destfile = $LocalFolder + $listFiles[$cntFile]
$file = New-Object IO.FileStream ($destfile, [IO.FileMode]::Create)
$FTPResponse = $FTPRequest1.GetResponse()
$ResponseStream = $FTPResponse.GetResponseStream()
[byte[]] $buffer = New-Object byte[] 64
$FTPReader = $ResponseStream.Read($buffer, 0, 64)
while($FTPReader -ne 0){
$FTPReader = $ResponseStream.Read($buffer,0, 64)
$file.Write($buffer,0,$FTPReader)
}
Write-Host "File $ftpfile is downloaded in local folder."
$file.Close()
$FTPReader.Close()
$FTPResponse.Close()
$ResponseStream.Close()
}
else {
Write-Host "The file $name already exists in local folder."
}
}
catch
{
if ($file) {
$file.Close()
}
}
}
while ($cntFile -lt $totFiles+1)
我的第二个脚本使用 Oracle 客户端附带的解压缩解压缩文件.exe解压缩文件。(%ORACLE_HOME%''产品''10.2.0''client_1''BIN)
解压缩文件脚本
param([string]$folder_origen, [string]$folder_destino, [string]$fecpro)
$P_FOLDER = $folder_origen + "'"
$P_DESTINO = $folder_destino + "'"
if (!$fecpro)
{
$date = Get-Date
$date = $date.AddDays(-1)
$fecpro = '{0:yyyyMMdd}' -f $date
}
Write-Host "Searching *.zip files in $P_FOLDER"
# Inicio del programa
$LocalFolder = $P_FOLDER + "*$fecpro*.zip"
$filelocation = gci $LocalFolder
foreach ($file in $filelocation){
if (($file) -and ($file.length -gt 0kb))
{
$shell = new-object -com shell.application
$filename = $file.name.ToString()
$split = $filename.Split(".")
$dest = $P_DESTINO + $split[0]
If (!(Test-Path $dest))
{
New-Item $dest -Type Directory
}
$orifile = $P_FOLDER + $filename
$time = "{0:hh_mm_ss.ffff}" -f (get-date)
$prefile = "d." + (get-date).ToShortDateString() + ".t." + $time
$prefile = $prefile -replace "/", "_"
$filename = $prefile + ".f." + $filename
$P_DESTZIP = $P_FOLDER + "proc" + "'"
If (!(Test-Path $P_DESTZIP))
{
New-Item $P_DESTZIP -Type Directory
}
#Copy-Item $orifile "$P_DESTZIP$prefile_$filename"
$zipname = $orifile
#"$P_DESTZIP$prefile_$filename"
$zip = $shell.NameSpace($zipname)
$destn = $shell.Namespace($dest)
unzip -o -qq $zipname "*$fecpro*afsbjdtl.*" "*$fecpro*afgnled.bin" -d $dest
Write-Host "$orifile was renamed and unzipped."
} else
{
Write-Host "Zip files not found in the folder."
}
}
当我可以使用文件大小、名称、扩展名等过滤器时,我会使用模式搜索要提取的文件,而且速度非常快。
最后关于加载,我也建议命令加载数据。它具有最好的性能。
我想说你应该最好使用你最了解的工具。可以在java中执行相同的操作,然后将其加载到数据库中或作为单独的应用程序运行,但是为什么要麻烦呢?我相信你知道在 C# 中下载/解压缩/开放访问数据库的有效方法。
所以只缺少一件事,如何有效地将数据从访问数据库上传到oracle数据库。
简短的回答是 - bulk inserts
,可以结合临时表和一些存储过程的帮助进行进一步的批量处理。通过数据库/表自定义调优可以实现更高的加速,以更好地适应您的数据。
你也可以在java中做同样的事情,但我怀疑它会更快。您正确处理缓冲数据和批量加载比任何其他因素重要得多。之后,获取一些DBA和bug em来帮助您进行数据库调优。细节超出了这个问题的范围。
说的是显而易见的,请原谅,但如果您的主要性能问题是 msAccess 的 ftp/sftp 传输速度.db那么让访问数据库定期导出您实际需要访问的表/记录的子集可能是有意义的,以便您对 Oracle 进行所需的更新。
几年前,我发现使用"ArrayDML"向 Oracle 添加记录的速度大约是一次添加一条记录的 10 倍。 (不确定 odp.net 是否支持...