Að vinna með MS SQL frá Powershell á Linux

Þessi grein er eingöngu hagnýt og er tileinkuð sorgarsögu minni

Undirbúningur fyrir Zero Touch PROD fyrir RDS (MS SQL), sem öll eyru okkar suðuðu um, gerði ég kynningu (POC - Proof Of Concept) um sjálfvirkni: sett af powershell skriftum. Eftir kynninguna, þegar stormasamt, langvarandi lófaklappið dró úr, breyttist í stanslaust lófaklapp, sögðu þeir mér - allt þetta er gott, en aðeins af hugmyndafræðilegum ástæðum, allir Jenkins-þrælarnir okkar vinna á Linux!

Er þetta hægt? Taktu svona heitt, lampa DBA undir Windows og haltu því í mjög hita powershell undir Linux? Er þetta ekki grimmt?

Ég þurfti að sökkva mér inn í þessa undarlegu samsetningu tækni. Auðvitað hættu öll 30+ forskriftirnar mínar að virka. Mér til undrunar tókst mér að laga allt á einum virkum degi. Ég er að skrifa í brennidepli. Svo, hvaða gildrur getur þú lent í þegar þú flytur powershell forskriftir frá Windows til Linux?

sqlcmd vs Invoke-SqlCmd

Leyfðu mér að minna þig á aðalmuninn á þeim. Gamla góða tólið sqlcmd Það virkar líka undir Linux, með næstum sömu virkni. Við sendum fyrirspurnina til að keyra -Q, inntaksskrána sem -i og úttakið sem -o. En skráarnöfnin eru auðvitað gerð hástöfum. Ef þú notar -i, þá skrifaðu í skrána í lokin:

GO
EXIT

Ef það er engin EXIT í lokin, þá mun sqlcmd halda áfram að bíða eftir inntak, og ef áður HÆTTA mun ekki GO, þá virkar síðasta skipunin ekki. Úttaksskráin inniheldur allt úttak, val, skilaboð, prentun osfrv.

Invoke-SqlCmd framleiðir niðurstöðuna sem DataSet, DataTables eða DataRows. Þess vegna, ef þú vinnur niðurstöðuna af einföldum vali, geturðu notað sqlcmd, eftir að hafa flokkað úttak þess, er næstum ómögulegt að leiða eitthvað flókið: fyrir þetta er til Invoke-SqlCmd. En þetta lið hefur líka sína eigin brandara:

• Ef þú flytur skrá til hennar í gegnum -Inntaksskráþá HÆTTA ekki þörf, þar að auki framleiðir það setningafræðivillu
• -OutputFile nei, skipunin skilar þér niðurstöðunni sem hlut
• Það eru tvær setningafræði til að tilgreina netþjón: -ServerInstance -Notendanafn -Lykilorð -Database og í gegnum -ConnectionString. Merkilegt nokk, í fyrra tilvikinu er ekki hægt að tilgreina aðra höfn en 1433.
• textaúttak, sláðu inn PRINT, sem er einfaldlega „fangað“ sqlcmdfyrir Invoke-SqlCmd er vandamál
• Og síðast en ekki síst: Líklegast er Linux ekki með þennan cmdlet!

Og þetta er aðalvandamálið. Aðeins í mars þetta cmdlet varð fáanlegt fyrir kerfi sem ekki eru Windows, og loksins getum við haldið áfram!

Breytileg skipti

sqlcmd hefur breytuskipti með því að nota -v, til dæmis eins og þetta:

# $conn содержит начало команды sqlcmd
$cmd = $conn + " -i D:appsSlaveJobsKillSpid.sql -o killspid.res 
  -v spid =`"" + $spid + "`" -v age =`"" + $age + "`""
Invoke-Expression $cmd

Í SQL handritinu notum við staðgöngur:

set @spid=$(spid)
set @age=$(age)

Svo hér er það. Í *nix breytilegar staðgöngur virka ekki... Parameter -v hunsuð. U Invoke-SqlCmd hunsuð -Breytur. Þótt færibreytan sem tilgreinir breyturnar sjálfar sé hunsuð, þá virka staðskiptin sjálf - þú getur notað hvaða breytur sem er frá Shell. Hins vegar var ég móðgaður yfir breytunum og ákvað að vera alls ekki háður þeim og hegðaði mér dónalega og frumstætt, þar sem SQL forskriftirnar eru stuttar:

# prepend the parameters  
"declare @age int, @spid int" | Add-Content "q.sql"
"set @spid=" + $spid | Add-Content "q.sql"
"set @age=" + $age | Add-Content "q.sql"

foreach ($line in Get-Content "Sqlserver/Automation/KillSpid.sql") { 
  $line | Add-Content "q.sql" 
  }
$cmd = "/opt/mssql-tools/bin/" + $conn + " -i q.sql -o res.log"

Þetta, eins og þú skilur, er próf þegar frá Unix útgáfunni.

Að hlaða upp skrám

Í Windows útgáfunni fylgdi hvaða aðgerð sem er endurskoðun: við keyrðum sqlcmd, fengum einhvers konar misnotkun í úttaksskránni, festum þessa skrá við endurskoðunarplötuna. Sem betur fer virkaði SQL server á sama netþjóni og Jenkins, það var gert eitthvað á þessa leið:

CREATE procedure AuditUpload
  @id int, @filename varchar(256)
as
  set nocount on
  declare @sql varchar(max)

  CREATE TABLE #multi (filer NVARCHAR(MAX))
  set @sql='BULK INSERT #multi FROM '''+@filename
    +''' WITH (ROWTERMINATOR = '' '',CODEPAGE = ''ACP'')'
  exec (@sql)
  select @sql=filer from #multi
  update JenkinsAudit set multiliner=@sql where ID=@id
  return

Þannig gleypum við BCP skrána alveg og ýtum henni inn í nvarchar(max) reitinn í endurskoðunartöflunni. Auðvitað datt allt þetta kerfi í sundur, því í staðinn fyrir SQL server fékk ég RDS, og BULK INSERT virkar alls ekki í gegnum UNC vegna tilraunar til að taka einkalás á skrá, og með RDS er þetta almennt dæmt frá alveg byrjunin. Svo ég ákvað að breyta kerfishönnuninni og geymdi endurskoðunina línu fyrir línu:

CREATE TABLE AuditOut (
  ID int NULL,
  TextLine nvarchar(max) NULL,
  n int IDENTITY(1,1) PRIMARY KEY
  )

Og skrifaðu í þessa töflu svona:

function WriteAudit([string]$Filename, [string]$ConnStr, 
     [string]$Tabname, [string]$Jobname)
{
  # get $lastid of the last execution  -- проскипано для статьи
	
  #create grid and populate it with data from file
  $audit =  Get-Content $Filename
  $DT = new-object Data.DataTable   

  $COL1 =  new-object Data.DataColumn; 
  $COL1.ColumnName = "ID"; 
  $COL1.DataType =  [System.Type]::GetType("System.Int32") 

  $COL2 =  new-object Data.DataColumn; 
  $COL2.ColumnName = "TextLine"; 
  $COL2.DataType =  [System.Type]::GetType("System.String") 
  
  $DT.Columns.Add($COL1) 
  $DT.Columns.Add($COL2) 
  foreach ($line in $audit) 
    { 
    $DR = $dt.NewRow()   
    $DR.Item("ID") = $lastid
    $DR.Item("TextLine") = $line
    $DT.Rows.Add($DR)   
    } 

  # write it to table
  $conn=new-object System.Data.SqlClient.SQLConnection 
  $conn.ConnectionString = $ConnStr
  $conn.Open() 
  $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $ConnStr
  $bulkCopy.DestinationTableName = $Tabname 
  $bulkCopy.BatchSize = 50000
  $bulkCopy.BulkCopyTimeout = 0
  $bulkCopy.WriteToServer($DT) 
  $conn.Close() 
  }  

Til að velja efni þarftu að velja eftir auðkenni, velja í röð n (auðkenni).

Í næstu grein mun ég fara nánar út í hvernig þetta hefur samskipti við Jenkins.

Heimild: www.habr.com