functions/Import-StackArchive.ps1
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 |
function Import-StackArchive { <# .SYNOPSIS Pulls XML dump file and bulk loads into database .DESCRIPTION Imports the XML dump file and then uses bulk load method to import into specified database .PARAMETER Folder The path to where the uncompressed XML files are located for the StackExchange site .PARAMETER SqlServer SQL Server instance were database is located .PARAMETER SqlCredential Credential object to connect as another user to SQL Server. .PARAMETER Database Database where you want to import the data .PARAMETER Schema Defaults to "dbo", specify schema if using specific one .PARAMETER TableList If you want to granularly load tables for demos or refresh a table. .PARAMETER BatchSize Set the batch size for the bulk copy process, default to 2000. May want to adjust when loading SO database. .EXAMPLE Import-StackArchive -Folder 'C:\quant.stackexchange.com' -SqlServer SQL12 -Database StackExchange -Schema quant -TableList Badges Import the Badges table from quant archive into the database StackExchange on SQL12 .EXAMPLE Import-StackArchive -Folder 'C:\quant.stackexchange.com' -SqlServer SQL12 -Database StackExchange -Schema quant -TableList 'Badges','Votes' Import the Badges and Votes data from quant archive into the database StackExchange on SQL12 .EXAMPLE Import-StackArchive -Folder 'C:\quant.stackexchange.com' -SqlServer SQL12 -Database StackExchange Import all data from quant archive into the database StackExchange on SQL12. #> [CmdletBinding()] param ( [string]$Folder, [string]$SqlServer, [PSCredential]$SqlCredential, [string]$Database, [string]$Schema = 'dbo', [ValidateSet('Badges', 'Comments', 'PostHistory', 'PostLinks', 'Posts', 'Tags', 'Users', 'Votes')] [string[]]$TableList, [int]$BatchSize ) begin { if ( !(Test-Path $Folder) ) { Stop-PSFFunction -Message "$Folder does not exist!" return } if (Test-PSFParameterBinding 'BatchSize' -Not) { $BatchSize = 2000 } if (Test-PSFParameterBinding 'TableList' -Not) { $TableList = 'Badges', 'Comments', 'PostHistory', 'PostLinks', 'Posts', 'Tags', 'Users', 'Votes' } $fileList = 'Badges.xml', 'Comments.xml', 'PostHistory.xml', 'PostLinks.xml', 'Posts.xml', 'Tags.xml', 'Users.xml', 'Votes.xml' $files = Get-ChildItem -Path $Folder | Where-Object Name -in $FileList if ($files.Count -le 0) { Write-PSFMessage -Level Warning -Message "No files found in $Folder" return } Write-PSFMessage -Level Verbose -Message "Connecting to $SqlServer" try { $instance = Connect-DbaInstance -SqlInstance $SqlServer -SqlCredential $SqlCredential -ClientName "StackDb PowerShell Module - StackExchange Archive" } catch { Stop-PSFFunction -Message "Failure" -Category ConnectionError -Target $SqlServer -ErrorRecord $_ return } if ( -not (Get-DbaDatabase -SqlInstance $instance -Database $Database) ) { Stop-PSFFunction -Message "$Database not found on $instance" -Target $instance return } } process { if (Test-PSFFunctionInterrupt) {return} $i = $x = 1 foreach ($f in $files) { Write-Progress -Id 1 -Activity "Working on files" -Status "Processing $($f.Name)" -PercentComplete $i foreach ($t in $TableList) { Write-Progress -Id 2 -Activity "Working on Tables" -Status "Processing $t" -PercentComplete $x if (Test-Path $f.FullName ) { if ($t -eq $f.BaseName) { switch ($t) { "Badges" { Write-PSFMessage -Level Verbose -Message "Found $f file..." [xml]$badges = Get-Content $f.FullName $dataTable = $badges.badges.Row | Select-Object UserId, Name, Date | ConvertTo-DbaDataTable Write-PSFMessage -Level Verbose -Message "Bulk loading Badges file..." Write-DbaDataTable -SqlInstance $instance -Database $Database -Schema $Schema -Table $t -InputObject $dataTable -BatchSize $BatchSize } "Comments" { Write-PSFMessage -Level Verbose -Message "Found $f file..." [xml]$comments = Get-Content $f.FullName $dataTable = $comments.comments.row | Select-Object Id, PostId, Score, Text, CreationDate, UserId | ConvertTo-DbaDataTable Write-PSFMessage -Level Verbose -Message "Bulk loading $f file..." Write-DbaDataTable -SqlInstance $instance -Database $Database -Schema $Schema -Table $t -InputObject $dataTable -BatchSize $BatchSize } "PostHistory" { Write-PSFMessage -Level Verbose -Message "Found $f file..." [xml]$postHistory = Get-Content $f.FullName $dataTable = $postHistory.posthistory.row | Select-Object Id, PostHistoryTypeId, PostId, RevisionGUID, CreationDate, UserId, UserDisplayName, Comment, Text, CloseReasonId | ConvertTo-DbaDataTable Write-PSFMessage -Level Verbose -Message "Bulk loading $f file..." Write-DbaDataTable -SqlInstance $instance -Database $Database -Schema $Schema -Table $t -InputObject $dataTable -BatchSize $BatchSize } "PostLinks" { Write-PSFMessage -Level Verbose -Message "Found $f file..." [xml]$postLink = Get-Content $f.FullName $dataTable = $postLink.postlinks.row | Select-Object Id, CreationDate, PostId, RelatedPostId, LinkTypeId | ConvertTo-DbaDataTable Write-PSFMessage -Level Verbose -Message "Bulk loading $f file..." Write-DbaDataTable -SqlInstance $instance -Database $Database -Schema $Schema -Table $t -InputObject $dataTable -BatchSize $BatchSize } "Posts" { Write-PSFMessage -Level Verbose -Message "Found $f file..." [xml]$posts = Get-Content $f.FullName $dataTable = $posts.posts.row | Select-Object Id, PostTypeId, ParentId, AcceptedAnswerId, CreationDate, Score, ViewCount, Body, OwnerUserId, LastEditorUserId, LastEditorDisplayName, LastEditDate, LastActivityDate, CommunityOwnedDate, ClosedDate, Title, Tags, AnswerCount, CommentCount, FavoriteCount | ConvertTo-DbaDataTable Write-PSFMessage -Level Verbose -Message "Bulk loading $f file..." Write-DbaDataTable -SqlInstance $instance -Database $Database -Schema $Schema -Table $t -InputObject $dataTable -BatchSize $BatchSize } "Tags" { Write-PSFMessage -Level Verbose -Message "Found $f file..." [xml]$tags = Get-Content $f.FullName $dataTable = $tags.tags.row | Select-Object Id, TagName, Count, ExcerptPostId, WikiPostId | ConvertTo-DbaDataTable Write-PSFMessage -Level Verbose -Message "Bulk loading $f file..." Write-DbaDataTable -SqlInstance $instance -Database $Database -Schema $Schema -Table $t -InputObject $dataTable -BatchSize $BatchSize } "Users" { Write-PSFMessage -Level Verbose -Message "Found $f file..." [xml]$users = Get-Content $f.FullName $dataTable = $users.users.row | Select-Object Id, Reputation, CreationDate, DisplayName, EmailHash, LastAccessDate, WebsiteUrl, Location, Age, AboutMe, Views, UpVotes, DownVotes | ConvertTo-DbaDataTable Write-PSFMessage -Level Verbose -Message "Bulk loading $f file..." Write-DbaDataTable -SqlInstance $instance -Database $Database -Schema $Schema -Table $t -InputObject $dataTable -BatchSize $BatchSize } "Votes" { Write-PSFMessage -Level Verbose -Message "Found $f file..." [xml]$votes = Get-Content $f.FullName $dataTable = $votes.votes.row | Select-Object Id, PostId, VoteTypeId, CreationDate, UserId, BountyAmount | ConvertTo-DbaDataTable Write-PSFMessage -Level Verbose -Message "Bulk loading $f file..." Write-DbaDataTable -SqlInstance $instance -Database $Database -Schema $Schema -Table $t -InputObject $dataTable -BatchSize $BatchSize } } #end switch $t } #end if test basename } #end if test name else { Write-PSFMessage -Level Warning -Message "No valid files found in provided directory" } $x++ } #end foreach tablelist $i++ } #end foreach file } } |