Rad sa MS SQL iz Powershell-a na Linux-u

Ovaj članak je potpuno praktičan i posvećen mojoj tužnoj priči

Pripremajući se za Zero Touch PROD za RDS (MS SQL), o kojem su nam u ušima brujali, napravio sam prezentaciju (POC - Proof Of Concept) automatizacije: niza powershell skripti. Nakon prezentacije, kada su se smirili burni, dugotrajni aplauzi, koji su prelazili u neprekidne ovacije, rekli su mi – sve to zvuči lepo, ali iz ideoloških razloga naši Jenkins slave rade samo na Linuxu!

Kako to može biti? Uzeti tako toplog, domaćeg DBA iz Windows-a i strpati ga u žarište powershell-a na Linuxu? Zar to nije surovo?

Rad sa MS SQL iz Powershell-a na Linux-u
Morao sam se uroniti u ovu čudnu kombinaciju tehnologija. Naravno, svih mojih 30+ skripti prestalo je raditi. Na moje iznenađenje, uspeo sam to sve popraviti za jedan radni dan. Pišem dok su utisci sveži. Dakle, koji problemi mogu naići prilikom prenošenja powershell skripti iz Windows-a na Linux?

sqlcmd vs Invoke-SqlCmd

Podsećam na osnovnu razliku između njih. Stara dobra utilita sqlcmd radi i na Linux-u, sa gotovo identičnom funkcionalnošću. Upit za izvršavanje prenosimo -Q, ulaznu datoteku kao -i, a izlaz -o. Samo, imena datoteka, naravno, postaju osetljiva na velika i mala slova. Ako koristite -i, u datoteci se na kraju napiše:

GO
EXIT

Ako na kraju ne bude EXIT, sqlcmd će preći u čekanje unosa, a ako pre EXIT neće GO, tada poslednja komanda neće biti izvršena. U datoteku izlaza dolazi sav izlaz, selects, poruke, print itd.

Invoke-SqlCmd vraća rezultat kao DataSet, DataTables ili DataRows. Dakle, ako možete obraditi rezultat jednostavnog select-a putem sqlcmd, razlažući njegov izlaz, prilično je nemoguće izvesti nešto složeno: za to postoji Invoke-SqlCmd. Ali ovaj komandni alat ima i svoje trikove:

  • Ako mu prosledite datoteku preko -InputFile, onda EXIT nije potreban, naprotiv, izaziva sintaktičku grešku
  • -OutputFile ne, komanda vam vraća rezultat u obliku objekta
  • Za određivanje servera postoje dvije sintakse: -ServerInstance -Username -Password -Database and through -ConnectionString. Kako to čudno zvučalo, u prvom slučaju nije moguće navesti port različit od 1433.
  • tekstualni izlaz, tipa PRINT, koji se jednostavno 'hvata' sqlcmd, za Invoke-SqlCmd predstavlja problem
  • I najvažnije: verovatno u vašem Linux-u ovaj cmdlet ne postoji!

I to je glavni problem. Tek u martu ovaj cmdlet postao je dostupan za ne-windows platforme, i konačno možemo napredovati!

Zamena promenljivih

U sqlcmd postoji zamena promenljivih pomoću -v, na primer, ovako:

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

U SQL skripti koristimo zamene:

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

Dakle, u *nix zamene promenljivih ne rade. Parametar -v se ignorira. U Invoke-SqlCmd se ignorira -Variables. Iako se parametar koji postavlja same promenljive ignoriše, same zamene rade — možete koristiti bilo koje promenljive iz Shell-a. Međutim, uvredio sam se na promenljive i odlučio da ne zavisim od njih, pa sam postupio grubo i primitivno, dobro je što su SQL skripte kratke:

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

To, kao što ste shvatili, je test već sa unix verzije.

Učitavanje fajlova

U verziji za Windows, svaka operacija bila je praćena auditom: izvršili smo sqlcmd, dobili neku grešku u izlaznoj datoteci, priložili tu datoteku tabeli audita. Srećom, SQL server je radio na istom serveru kao i Jenkins, to se radilo otprilike ovako:

KREIRAJ proceduru AuditUpload
  @id int, @filename varchar(256)
as
  postavi nocount on
  deklarisi @sql varchar(max)

  KREIRAJ TABELU #multi (filer NVARCHAR(MAX))
  postavi @sql='BULK INSERT #multi FROM '''+@filename
    +''' WITH (ROWTERMINATOR = '' '',CODEPAGE = ''ACP'')'
  exec (@sql)
  izaberi @sql=filer iz #multi
  update JenkinsAudit set multiliner=@sql where ID=@id
  vrati

Na taj način učitavamo BCP datoteku u celosti i smeštamo je u polje nvarchar(max) tabele audita. Naravno, ceo ovaj sistem se raspao, pošto sam umesto SQL servera dobio RDS, a BULK INSERT ne funkcioniše putem UNC zbog pokušaja preuzimanja ekskluzivnog zaključavanja na datoteci, a sa RDS je to od samog početka bilo prokletstvo. Tako da sam odlučio da promenim dizajn sistema, čuvajući audit po redovima:

KREIRAJ TABELU AuditOut (
  ID int NULL,
  TextLine nvarchar(max) NULL,
  n int IDENTITY(1,1) PRIMARNI KLJUČ
  )

I upisivati u ovu tabelu ovako:

funkcija WriteAudit([string]$Filename, [string]$ConnStr, 
     [string]$Tabname, [string]$Jobname)
{
  # uzmi $lastid poslednjeg izvršenja  -- preskočeno za članak
	
  #kreati mrežu i popuniti je podacima iz datoteke
  $audit =  Get-Content $Filename
  $DT = novi-objekat Data.DataTable   

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

  $COL2 =  novi-objekat 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)   
    } 

  # upisati u tabelu
  $conn=novi-objekat System.Data.SqlClient.SQLConnection 
  $conn.ConnectionString = $ConnStr
  $conn.Open() 
  $bulkCopy = novi-objekat ("Data.SqlClient.SqlBulkCopy") $ConnStr
  $bulkCopy.DestinationTableName = $Tabname 
  $bulkCopy.BatchSize = 50000
  $bulkCopy.BulkCopyTimeout = 0
  $bulkCopy.WriteToServer($DT) 
  $conn.Close() 
  }  

Za odabir sadržaja treba raditi select po ID-ju, birajući po redosledu n (identitet).

U sledećem članku detaljnije ću se osvrnuti na to kako sve ovo funkcioniše sa Jenkinsom.

Izvor: habr.com

Kupite pouzdan hosting za sajtove uz zaštitu od DDoS, VPS VDS servere 🔥 Kupite pouzdan hosting za sajtove uz zaštitu od DDoS, VPS VDS servere | ProHoster