Created
November 11, 2013 17:55
-
-
Save mxrss/7417361 to your computer and use it in GitHub Desktop.
Use Powershell to hydrate objects and map if inital to elasticsearch using the MSSQL snappins.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Param( | |
[bool]$firstRun, | |
[Parameter(Mandatory=$true)] | |
[string]$dbServer, | |
[Parameter(Mandatory=$true)] | |
[string]$userName, | |
[Parameter(Mandatory=$true)] | |
[string]$password, | |
[Parameter(Mandatory=$true)] | |
[string]$esServerInstance, | |
[Parameter(Mandatory=$true)] | |
[string]$Database | |
) | |
<# | |
.Synopsis | |
creates a index maping using the default constrained, could be upgraded later to support a index mapping ES shell. | |
#> | |
function createIndexMappings() { | |
$mappings = '{ "Customer" : { | |
"properties" : { | |
"fullName" : { | |
"type" : "string", | |
"index_analyzer" : "autocomplete", | |
"index" : "analyzed", | |
"search_analyzer" : "standard" | |
}, | |
"userName" : { | |
"type" : "string", | |
"index_analyzer" : "autocomplete", | |
"index" : "analyzed", | |
"search_analyzer" : "standard" | |
} | |
, | |
"groupName" : { | |
"type" : "string", | |
"index_analyzer" : "autocomplete", | |
"index" : "analyzed", | |
"search_analyzer" : "standard" | |
} | |
} | |
} | |
}' | |
<# | |
.Synopsis | |
Creates the analyzer that the index will use. | |
#> | |
Invoke-RestMethod http://$esServerInstance/customers/Customer/_mapping -Method Put -Body $mappings | |
} | |
function createAnalyzer { | |
$analyzer = ' {"analysis" : { | |
"analyzer" : { | |
"autocomplete" : { | |
"tokenizer" : "whitespace", | |
"filter" : ["lowercase", "engram"] | |
} | |
}, | |
"filter" : { | |
"engram" : { | |
"type" : "edgeNGram", | |
"min_gram" : 3, | |
"max_gram" : 10 | |
} | |
} | |
} | |
}' | |
Invoke-RestMethod http://$esServerInstance/customers -Method Post -Body $analyzer | |
} | |
<# | |
.Synopsis | |
Recreates the index from scratch | |
#> | |
function indexRecreate{ | |
try { | |
# reset the index since this is a first time. | |
write-host 'reinstalling index' | |
Invoke-RestMethod "http://$esServerInstance/customers" -Method Delete -ErrorAction Continue | |
} Catch { Write-Host "Something went wrong!" } | |
createAnalyzer | |
createIndexMappings | |
} | |
<# | |
Description | |
Program steps are as followed | |
1. Goto database and get the view | |
2. Convert the data into a JSON Object | |
3. Send the data to a ElasticSearch server | |
#> | |
# | |
# Add the SQL Server provider. | |
# | |
$ErrorActionPreference = "Stop" | |
$sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps" | |
if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue") | |
{ | |
throw "SQL Server Provider is not installed." | |
} | |
else | |
{ | |
$item = Get-ItemProperty $sqlpsreg | |
$sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path) | |
} | |
# | |
# Set mandatory variables for the SQL Server rovider | |
# | |
Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0 | |
Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30 | |
Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false | |
Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000 | |
# | |
# Load the snapins, type data, format data | |
# | |
Push-Location | |
cd $sqlpsPath | |
Add-PSSnapin SqlServerCmdletSnapin100 | |
Add-PSSnapin SqlServerProviderSnapin100 | |
Update-TypeData -PrependPath SQLProvider.Types.ps1xml | |
update-FormatData -prependpath SQLProvider.Format.ps1xml | |
Pop-Location | |
# ok lets define delete the index first and foremost | |
if ($firstRun -eq $true) { | |
indexRecreate | |
Write-Host "reseting last updated time stamp!" | |
Invoke-sqlCmd "UPDATE UserData SET LastUpdatedOn = GETDATE()" -ServerInstance "$dbServer" -Username "$userName" -Password "$password" -Database "$Database" | |
} | |
$LastUpdatedOn=((get-date).AddMinutes(-1)) | |
$query = "SELECT TOP 50 userId, UserName userName, FirstName firstName, LastName lastName, Cell cell, school schoolName, GroupName groupName from CustomerData where lastUpdatedOn=`$(LastUpdateOn)`;" | |
$users = Invoke-sqlCmd "SELECT firstName + ' ' + lastName fullName, userId, UserName userName, FirstName firstName, LastName lastName, Cell cell, school schoolName, GroupName groupName from CustomerData;" -ServerInstance "$dbServer" -Username "$userName" -Password "$password" -Database "$Database" |select-object fullName, userId, userName, firstName, lastName, cell, schoolName, groupName | |
#iterate and process items | |
$jsonUsers = ConvertTo-Json $users | |
$counter = 0 | |
$usersCount = $users.Count | |
$users | Foreach-Object { | |
$objectToSend = ConvertTo-Json $_ | |
$userId = $_.userId | |
$result = Invoke-RestMethod "http://$esServerInstance/customers/Customer/$userId" -Method Put -ContentType "application/json" -Body $objectToSend | |
$counter++ | |
Write-Progress -Activity "Updating $($_.fullName) " -PercentComplete (($counter/$usersCount)*100) -Status "$(($counter/$usersCount)*100)%" | |
} | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment