oracle - 使用 Powershell 将大输出从 Oracle 导出到 CSV

Question

我需要每周从 Oracle 导出一个相当大的 CSV 文件。

我尝试了两种方法。

1. Adapter.fill（数据集）
2. 循环遍历列和行以一次一行保存到 CSV 文件中。

第一个在运行时内存不足（服务器机器只有 4 GB 的 RAM），第二个大约需要一个小时，因为要导出超过 400 万行。

这是代码＃1：

#Your query. It cannot contain any double quotes otherwise it will break.
$query = "SELECT manycolumns FROM somequery"

#Oracle login credentials and other variables
$username = "username"
$password = "password"
$datasource = "database address"
$output = "\\NetworkLocation\Sales.csv"

#creates a blank CSV file and make sure it's in ASCI
Out-File $output -Force ascii

#This here will look for "Oracle.ManagedDataAccess.dll" file inside "C:\Oracle" folder. We usually have two versions of Oracle installed so the adaptor can be in different locations. Needs changing if the Oracle is installed elsewhere.
$location = Get-ChildItem -Path C:\Oracle -Filter Oracle.ManagedDataAccess.dll -Recurse -ErrorAction SilentlyContinue -Force

#Establishes connection to Oracle using the DLL file
Add-Type -Path $location.FullName
$connectionString = 'User Id=' + $username + ';Password=' + $password + ';Data Source=' + $datasource
$connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionString)
$connection.open()
$command=$connection.CreateCommand()
$command.CommandText=$query

#Creates a table in memory and fills it with results from the query. Then, export the virtual table into CSV.
$DataSet = New-Object System.Data.DataSet
$Adapter = New-Object Oracle.ManagedDataAccess.Client.OracleDataAdapter($command)
$Adapter.Fill($DataSet)
$DataSet.Tables[0] | Export-Csv $output -NoTypeInformation

$connection.Close()

这是＃2

#Your query. It cannot contain any double quotes otherwise it will break.
$query = "SELECT manycolumns FROM somequery"

#Oracle login credentials and other variables
$username = "username"
$password = "password"
$datasource = "database address"
$output = "\\NetworkLocation\Sales.csv"
$tempfile = $env:TEMP + "\Temp.csv"

#creates a blank CSV file and make sure it's in ASCI
Out-File $tempfile -Force ascii

#This here will look for "Oracle.ManagedDataAccess.dll" file inside "C:\Oracle" folder. Needs changing if the Oracle is installed elsewhere.
$location = Get-ChildItem -Path C:\Oracle -Filter Oracle.ManagedDataAccess.dll -Recurse -ErrorAction SilentlyContinue -Force

#Establishes connection to Oracle using the DLL file
Add-Type -Path $location.FullName
$connectionString = 'User Id=' + $username + ';Password=' + $password + ';Data Source=' + $datasource
$connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionString)
$connection.open()
$command=$connection.CreateCommand()
$command.CommandText=$query

#Reads results column by column. This way you don't have to specify how many columns it has.
$reader = $command.ExecuteReader()
  while($reader.Read()) {
       $props = @{}
       for($i = 0; $i -lt $reader.FieldCount; $i+=1) {
           $name = $reader.GetName($i)
           $value = $reader.item($i)
           $props.Add($name, $value)   
       }
       #Exports each line to CSV file. Works best when the file is on local drive as it saves it after each line.
       new-object PSObject -Property $props | Export-Csv $tempfile -NoTypeInformation -Append
  }

Move-Item $tempfile $output -Force

$connection.Close()

理想情况下，我想使用第一个代码，因为它比第二个代码快得多，但以某种方式避免内存不足。

你们知道是否有某种方法可以“填充”前 100 万条记录，将它们附加到 CSV，清理“DataSet”表，接下来的 100 万条记录等？在代码完成运行 CSV 后，它的权重约为 1.3 GB，但当它运行时，即使 8 GB 的内存也不够用（我的笔记本电脑有 8 GB，但服务器只有 4 GB，而且真的很难）。

任何提示将不胜感激。

标签： oraclepowershellcsvexport-to-csv

Answer 1

在 *nix 社区中，我们喜欢单线！

您可以在 sqlplus (>= 12) 中将标记设置为“csv on”

创建查询文件

cat > query.sql <<EOF
set head off
set feed off
set timing off
set trimspool on
set term off
spool output.csv
select 
  object_id, 
  owner, 
  object_name, 
  object_type, 
  status, 
  created, 
  last_ddl_time 
from dba_objects;
spool off
exit;
EOF

像这样假脱机output.csv：

sqlplus -s -m "CSV ON DELIM ',' QUOTE ON" user/password@\"localhost:1521/<my_service>\" @query.sql

另一种选择是 SQLcl（SQL Developer CLI 工具。二进制名称：我将'sql'重命名为'sqlcl'）

创建查询文件（注意！术语 on|off）

cat > query.sql <<EOF
set head off
set feed off
set timing off
set term off
set trimspool on
set sqlformat csv
spool output.csv
select 
  object_id, 
  owner, 
  object_name, 
  object_type, 
  status, 
  created, 
  last_ddl_time 
from dba_objects 
where rownum < 5;
spool off
exit;
EOF

像这样假脱机output.csv：

sqlcl -s system/oracle@\"localhost:1521/XEPDB1\" @query.sql

中提琴！

cat output.csv 
9,"SYS","I_FILE#_BLOCK#","INDEX","VALID",18.10.2018 07:49:04,18.10.2018 07:49:04
38,"SYS","I_OBJ3","INDEX","VALID",18.10.2018 07:49:04,18.10.2018 07:49:04
45,"SYS","I_TS1","INDEX","VALID",18.10.2018 07:49:04,18.10.2018 07:49:04
51,"SYS","I_CON1","INDEX","VALID",18.10.2018 07:49:04,18.10.2018 07:49:04

获胜者是 77k 行的 sqlplus！（删除过滤器 rownum < 5）

time sqlcl -s system/oracle@\"localhost:1521/XEPDB1\" @query.sql

real    0m23.776s
user    0m39.542s
sys     0m1.293s

time sqlplus -s -m "CSV ON DELIM ',' QUOTE ON" system/oracle@localhost/XEPDB1 @query.sql

real    0m3.066s
user    0m0.700s
sys     0m0.265s

wc -l output.csv
77480 output.csv

您可以在 SQL Developer 中试验格式。

select /*CSV|HTML|JSON|TEXT|<TONSOFOTHERFORMATS>*/ from dba_objects;

如果您正在将 CSV 加载到数据库中，这个工具就可以了！

https://github.com/csv2db/csv2db

祝你好运！