json - 数据逐行而不是成批插入

Question

我试图从一个大的json文档批处理行，并将其插入SQL Server。

以下代码有效，但是一次将1行插入到SQL Server表中。我认为这实际上是每第1000行。

add-type -path "C:\Program Files\WindowsPowerShell\Modules\newtonsoft.json\1.0.2.201\libs\newtonsoft.json.dll"
install-module sqlserver -AllowClobber

class EliteCoords {
    [double] $x
    [double] $y
    [double] $z
}

class EliteSystem {
    [int]         $id
    [long]        $id64
    [string]      $name
    [EliteCoords] $coords
    [string]      $date 
}

$dt = New-Object system.data.datatable

$dt.Columns.Add("ID",[int])
$dt.Columns.Add("ID64",[long])
$dt.Columns.Add("Name",[string])
$dt.Columns.Add("Coordsx",[decimal])
$dt.Columns.Add("Coordsy",[decimal])
$dt.Columns.Add("Coordsz",[decimal])
$dt.Columns.Add("DiscoveryDate",[string])

$stream = (Get-Item c:\code\systemsWithCoordinates.json).OpenText()
$reader = [Newtonsoft.Json.JsonTextReader]::new($stream)
while ($reader.Read()) {
    $cnt = 0
    $dt.Clear()
    while ($cnt -le 1000) {
        $cnt = $cnt + 1

        if ($reader.TokenType -eq 'StartObject') {

            $row = [Newtonsoft.Json.JsonSerializer]::CreateDefault().Deserialize($reader, [EliteSystem])

            $dr = $dt.NewRow()

            $dr["ID"]            = $row.id
            $dr["ID64"]          = $row.id64
            $dr["Name"]          = $row.name
            $dr["Coordsx"]       = $row.coords.x
            $dr["Coordsy"]       = $row.coords.y
            $dr["Coordsz"]       = $row.coords.z
            $dr["DiscoveryDate"] = $row.date

            $dt.Rows.Add($dr)       
        }
    }

    write-sqltabledata -ServerInstance ELITEDANGEROUS -Database EDSM -Schema Staging -Table SystemsWithCoordinates -InputData $dt
}

$stream.Close()

我知道问题是因为除了内部while循环的第一次迭代之外，对于所有其他块，都将跳过if块，因为 token 类型从StartObject更改为StartArray。

我尝试在其中放置一个附加的阅读器循环，但是，当然，它可以读取整个文件。

我也尝试仅读取数组而不是对象，但是当然，由于嵌套的json失败。

我应该如何构造循环，以便可以批量处理1000行？

Answer 1

您的问题是您要为$reader.Read()的每次调用(即每一行)刷新并清除表。

相反，您需要累积行直到达到1000，然后刷新:

$stream = (Get-Item c:\code\systemsWithCoordinates.json).OpenText()
$reader = [Newtonsoft.Json.JsonTextReader]::new($stream)
try {
    $serializer = [Newtonsoft.Json.JsonSerializer]::CreateDefault()
    while ($reader.Read()) {
        # If the reader is positioned at the start of an object then accumulate a row.
        if ($reader.TokenType -eq 'StartObject') {                
            $row = serializer.Deserialize($reader, [EliteSystem])

            $dr = $dt.NewRow()

            $dr["ID"]            = $row.id
            $dr["ID64"]          = $row.id64
            $dr["Name"]          = $row.name
            $dr["Coordsx"]       = $row.coords.x
            $dr["Coordsy"]       = $row.coords.y
            $dr["Coordsz"]       = $row.coords.z
            $dr["DiscoveryDate"] = $row.date

            $dt.Rows.Add($dr)       
        }

        # If we have accumulated 1000 rows, flush them.
        if ($dt.Rows.Count -ge 1000) {
            write-sqltabledata -ServerInstance ELITEDANGEROUS -Database EDSM -Schema Staging -Table SystemsWithCoordinates -InputData $dt
            $dt.Clear()
        }
    }

    # Flush any remaining rows.
    if ($dt.Rows.Count -ge 0) {
        write-sqltabledata -ServerInstance ELITEDANGEROUS -Database EDSM -Schema Staging -Table SystemsWithCoordinates -InputData $dt
        $dt.Clear()
    }
}
finally {
    $reader.Close()
    $stream.Close()
}

笔记:

如果发生异常，您可能应该在StreamReader块中处置JsonTextReader和finally。有关执行此操作的漂亮方法，请参见How to implement using statement in powershell?。

只分配一次序列化程序应该可以稍微提高性能，而不会产生任何费用。

没有JSON文件的示例，我们无法说出用于每行的EliteSystem数据模型是否存在其他问题。例如，如果JSON文件实际上是一个锯齿状的2d数组，则可能不起作用。