Extract multiline regex from extra large files in Powershell

Question

I have extra large log file in CSV format which includes JSON formatted data inside. What I'm trying to do is extract JSON parts from the data and store it in a separate file.

The real problem is that the file size is almost 70Gb which causes some interesting problems to tackle. The file size makes it impossible to read the whole file in one chunk. With Powershell's Get-Content combined with -ReadCount and Foreach-Object I can take smaller chunks and run regex pattern over them, chunk by chunk.

$Path = 
$outPath = 
Out-File -Encoding utf8 -FilePath $outPath
$JsonRegex = "(?smi)\{.*?\}"
Get-Content -Path $Path -ReadCount 100000 | Foreach-Object {
    ( "$_" | Select-String -Pattern $JsonRegex -AllMatches | Foreach-Object { $_.Matches } | Foreach-Object { $_.Value } ) | Add-Content $outPath
}

But here what happens is, every 100k lines the ReadCount is in the middle of a JSON object thus skipping said object and continuing from next object.

Here is an example how this log data looks like. It includes some columns on first row and then JSON formatted data which is not consistent so I cannot use any fixed ReadCount value to avoid being in the middle of a JSON object.

"5","5","9/10/2019 12:00:46 AM","2","some","data","removed","comment","{
  "message": "comment",
  "level": "Information",
  "logType": "User",
  "timeStamp": "2019-09-10T03:00:46.5573047+03:00",
  "fingerprint": "some",
}","11"
"5","5","9/10/2019 12:00:46 AM","2","some","data","removed","comment","{
  "message": "comment",
  "level": "Information",
  "logType": "User",
  "timeStamp": "2019-09-10T03:00:46.5672713+03:00",
  "fingerprint": "some",
  "windowsIdentity": "LOCAL\WinID",
  "machineName": "TK-141",
  "processVersion": "1.0.71",
  "jobId": "24a8",
  "machineId": 11
}","11"

Is there any way to accomplish this without missing any data rows from the gigantous logfile?

user_45375 · Answer

Use a switch statement with the -Regex and -File parameters to efficiently (by PowerShell standards) read the file line by line and keep state across multiple lines.

For efficient writing to a file, use a .NET API, namely a System.IO.StreamWriter instance.

The following code assumes:

Each JSON string spans multiple lines and is non-nested.
On a given line, an opening { / closing } unambiguously marks the start / end of a (multi-line) JSON string.

# Input file path 
$path = '...'

# Output file path
# Important: specify a *full* path
$outFileStream = [System.IO.StreamWriter] "$PWD/out.txt"

$json = ''
switch -Regex -File $path {
 '\{.*'  { $json = $Matches[0]; continue }
 '.*\}'  { 
     $json += "`n" + $Matches[0]
     $outFileStream.WriteLine($json)
     $json = ''
     continue 
   }
 default { if ($json) { $json += "`n" + $_ } }
}
$outFileStream.Close()

If you can further assume that no part of the JSON string follows the opening { / precedes the closing } on the same line, as your sample data suggest, you can simplify (and speed up) the switch statement:

$json = ''
switch -Regex -File $path {
 '\{$'  { $json ='{'; continue }
 '^\}'  { $outFileStream.WriteLine(($json + "`n}")); $json = ''; continue }
 default { if ($json) { $json += "`n" + $_ } }
}
$outFileStream.Close()

Doug Maurer had a solution attempt involving a System.Text.StringBuilder instance so as to optimize the iterative concatenation of the parts making up each JSON string:

However, at least with an input file crafted from many repetitions of the sample data, I saw only a small performance gain in my informal tests.
For the sake of completeness, here's the System.Text.StringBuilder solution:

$json = [System.Text.StringBuilder]::new(512) # tweak the buffer size as needed
switch -Regex -File $path {
  '\{$'  { $null = $json.Append('{'); continue }
  '^\}'  { $outFileStream.WriteLine($json.Append("`n}").ToString()); $null = $json.Clear(); continue }
  default { if ($json.Length) { $null = $json.Append("`n").Append($_) } }
}
$outFileStream.Close()

Extract multiline regex from extra large files in Powershell

1 Answers