🥇Rad s MS SQL iz Powershella na Linuxu

Ovaj članak je isključivo praktične prirode i posvećen je mojoj tužnoj priči

Pripremajući se za Zero Touch PROD za RDS (MS SQL), o kojem su nam svi pričali, napravio sam prezentaciju (POC — Proof Of Concept) automatizacije: skupa powershell skripti. Nakon prezentacije, kada su se smirile burne, dugotrajne aplauze koji su prelazili u neprekidne ovacije, rekli su mi — sve je to dobro, ali ideološki zauzeti smo da svi naši Jenkins slaves rade pod Linuxom!

Može li se to tako? Uzeti tako dragog, prijatnog DBA iz Windows-a i gurnuti ga usred pakla powershell-a pod Linuxom? Zar to nije okrutno?

Morao sam se uroniti u ovu čudnu kombinaciju tehnologija. Naravno, svih mojih 30+ skripti prestalo je raditi. Na moje iznenađenje, za jedan radni dan uspeo sam sve da popravim. Pišem dok su utisci još sveži. Dakle, koje prepreke mogu naići prilikom prebacivanja powershell skripti iz Windows-a u Linux?

sqlcmd vs Invoke-SqlCmd

Podsjećam na osnovnu razliku između njih. Stara dobra alatka sqlcmd radi i pod Linux-om, sa gotovo identičnom funkcionalnošću. Upit za izvršavanje šaljemo sa -Q, ulazni fajl sa -i, a izlaz sa -o. Samo što, naravno, imena fajlova postaju case-sensitive. Ako koristite -i, onda u fajl napišite na kraju:

GO
EXIT

Ako na kraju ne bude EXIT, sqlcmd će preći na čekanje unosa, a ako pre EXIT neće GO, poslednja komanda se neće izvršiti. U izlazni fajl dolazi sav izlaz, select, poruke, print itd.

Invoke-SqlCmd daje rezultat u obliku DataSet-a, DataTable-a ili DataRow-a. Dakle, ako možete obraditi rezultat jednostavnog select-a i kroz sqlcmd, analizirajući njegov izlaz, teško je prikazati nešto složeno: za to postoji Invoke-SqlCmd. Ali ova komanda ima i svoje sitnice:

Ako joj prosledite fajl preko -InputFile, tada EXIT nije potreban, štaviše, daje pravnu grešku
-OutputFile ne, komanda vam vraća rezultat u obliku objekta
Za navođenje servera postoje dva sintaktička oblika: -ServerInstance -Username -Password -Database i preko -ConnectionString. Kako je neobično, u prvom slučaju nije moguće navesti port različit od 1433.
Tekstualni izlaz, poput PRINT-a, koji se jednostavno "hvata" sqlcmd, za Invoke-SqlCmd jesu problem
I najvažnije: verovatno u vašem Linux-u nema ovog cmdlet-a!

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

Zamjena varijabli

U sqlcmd postoji umetanje varijabli pomoću -v, na primjer, 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 umetanja:

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

Dakle, u *nix umetanja varijabli ne rade. Parametar -v zanemaruje. U Invoke-SqlCmd ignora se -Variables. Iako se parametar koji postavlja varijable zanemaruje, sama umetanja rade — možete koristiti bilo koje varijable iz Shell-a. Međutim, zamjerio sam varijablama i odlučio se potpuno osloboditi od njih, pa sam postupio grubo i primitivno, srećom, SQL skripte su 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 je, kao što ste shvatili, test već iz unix verzije.

Učitavanje datoteka

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

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

Tako, učitavamo datoteku BCP u celini i ubacujemo u polje nvarchar(max) u tabeli za reviziju. Naravno, cijeli ovaj sistem se raspao, budući da sam umjesto SQL servera dobio RDS, a BULK INSERT uopće ne radi preko UNC-a zbog pokušaja uzimanja ekskluzivnog zaključavanja nad datotekom, a s RDS-om je to od samog početka bilo osuđeno na propast. Stoga sam odlučio promijeniti dizajn sistema, čuvajući reviziju po redovima:

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

I zapisivanje u ovu tabelu ovako:

function WriteAudit([string]$Filename, [string]$ConnStr, 
     [string]$Tabname, [string]$Jobname)
{
  # get $lastid of the last execution  -- preskočeno za članak
	
  #kreiraj grid i popuni ga podacima iz datoteke
  $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)   
    } 

  # upiši ga u tabelu
  $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() 
  }

Za odabir sadržaja treba raditi select po ID-u, birajući po n (identity).

U sljedećem članku detaljnije ću se osvrnuti na to kako sve ovo funkcionira s Jenkinsom.

Izvor: habr.com