Lucrul cu MS SQL de la Powershell pe Linux

Acest articol este pur practic și este dedicat poveștii mele triste

Pregătirea pentru Zero Touch PROD pentru RDS (MS SQL), despre care ne bâzâiau toate urechile, am făcut o prezentare (POC - Proof Of Concept) de automatizare: un set de scripturi powershell. După prezentare, când aplauzele furtunoase și prelungite s-au stins, transformându-se în aplauze neîncetate, mi-au spus - toate acestea sunt bune, dar numai din motive ideologice, toți sclavii noștri Jenkins lucrează pe Linux!

Este posibil? Luați un DBA atât de cald, lampă de sub Windows și puneți-l în căldura Powershell-ului sub Linux? Nu este asta crud?

A trebuit să mă cufund în această combinație ciudată de tehnologii. Desigur, toate cele peste 30 de scripturi ale mele au încetat să funcționeze. Spre surprinderea mea, am reușit să repar totul într-o zi lucrătoare. Scriu în căutarea fierbinte. Deci, ce capcane puteți întâmpina atunci când transferați scripturi Powershell de la Windows la Linux?

sqlcmd vs Invoke-SqlCmd

Permiteți-mi să vă reamintesc principala diferență dintre ele. Utilitate veche bună sqlcmd Funcționează și sub Linux, cu funcționalități aproape identice. Trecem interogarea pentru a executa -Q, fișierul de intrare ca -i și ieșirea ca -o. Dar numele fișierelor, desigur, sunt sensibile la majuscule. Dacă utilizați -i, atunci în fișier scrieți la sfârșit:

GO
EXIT

Dacă nu există EXIT la sfârșit, atunci sqlcmd va continua să aștepte intrarea, și dacă înainte EXIT nu va GO, atunci ultima comandă nu va funcționa. Fișierul de ieșire conține toate rezultatele, selecțiile, mesajele, imprimarea etc.

Invoke-SqlCmd produce rezultatul ca DataSet, DataTables sau DataRows. Prin urmare, dacă procesați rezultatul unei simple selectări, puteți utiliza sqlcmd, după ce i-a analizat rezultatul, este aproape imposibil să derivăm ceva complex: pentru aceasta există Invoke-SqlCmd. Dar această echipă are și propriile ei glume:

• Dacă îi transferi un fișier prin -Fișier de intrare, Apoi, EXIT nu este necesar, în plus, produce o eroare de sintaxă
• -Fisier de iesire nu, comanda vă returnează rezultatul ca obiect
• Există două sintaxe pentru specificarea unui server: -ServerInstance -Nume utilizator -Parolă -Bază de date și prin -ConectionString. Destul de ciudat, în primul caz nu este posibil să specificați un alt port decât 1433.
• ieșire text, tastați PRINT, care este pur și simplu „prins” sqlcmdpentru Invoke-SqlCmd este o problema
• Si cel mai important: Cel mai probabil, Linux-ul tău nu are acest cmdlet!

Și aceasta este principala problemă. Numai în martie acest cmdlet a devenit disponibil pentru platformele non-Windows, și în sfârșit putem merge mai departe!

Înlocuirea variabilei

sqlcmd are substituție variabilă folosind -v, de exemplu astfel:

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

În scriptul SQL folosim substituții:

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

Deci aici este. În *nix substituțiile de variabile nu funcționează. Parametru -v ignorat. U Invoke-SqlCmd ignorat -Variabile. Deși parametrul care specifică variabilele în sine este ignorat, substituțiile în sine funcționează - puteți utiliza orice variabilă din Shell. Cu toate acestea, am fost jignit de variabile și am decis să nu depind deloc de ele și am acționat grosolan și primitiv, deoarece scripturile SQL sunt scurte:

# 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"

După cum înțelegeți, acesta este un test deja din versiunea Unix.

Încărcarea fișierelor

În versiunea Windows, orice operațiune a fost însoțită de un audit: am rulat sqlcmd, am primit un fel de abuz în fișierul de ieșire, am atașat acest fișier pe placa de audit. Din fericire, serverul SQL a funcționat pe același server ca și Jenkins, s-a făcut ceva de genul:

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

Astfel, înghițim fișierul BCP în întregime și îl introducem în câmpul nvarchar(max) al tabelului de audit. Desigur, întregul sistem s-a prăbușit, deoarece în loc de un server SQL am primit RDS, iar BULK INSERT nu funcționează deloc prin UNC din cauza unei încercări de a bloca un fișier exclusiv, iar cu RDS acest lucru este în general condamnat. chiar începutul. Așa că am decis să schimb designul sistemului, stocând auditul linie cu linie:

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

Și scrieți în acest tabel așa:

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() 
  }  

Pentru a selecta conținut, trebuie să selectați după ID, alegând în ordinea n (identitate).

În articolul următor voi intra în mai multe detalii despre modul în care toate acestea interacționează cu Jenkins.

Sursa: www.habr.com