ProHoster > Blog > uprava > Rad s MS SQL iz Powershell-a na Linuxu

Rad s MS SQL iz Powershell-a na Linuxu

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

Priprema za Zero Touch PROD za RDS (MS SQL), o kojem su nam sve uši zujale, napravio sam prezentaciju (POC - Proof Of Concept) automatizacije: set powershell skripti. Nakon prezentacije, kad je buran, dugotrajni pljesak utihnuo, prerastajući u neprestani pljesak, rekli su mi – sve je to dobro, ali samo iz ideoloških razloga, svi naši Jenkinsovi robovi rade na Linuxu!

Je li to moguće? Uzeti tako toplog, lampaškog DBA ispod Windowsa i zabiti ga u samu vrućinu powershell-a pod Linuxom? Nije li ovo okrutno?

Rad s MS SQL iz Powershell-a na Linuxu
Morao sam uroniti u ovu čudnu kombinaciju tehnologija. Naravno, svih mojih 30+ skripti su prestale raditi. Na moje iznenađenje, uspio sam sve popraviti u jednom radnom danu. Pišem u hitnoj potrazi. Dakle, na koje se zamke možete susresti prilikom prijenosa powershell skripti iz Windowsa u Linux?

sqlcmd u odnosu na Invoke-SqlCmd

Dopustite mi da vas podsjetim na glavnu razliku između njih. Dobri stari alat sqlcmd Također radi pod Linuxom, s gotovo identičnom funkcionalnošću. Prosljeđujemo upit za izvršenje -Q, ulaznu datoteku kao -i, a izlaz kao -o. Ali nazivi datoteka, naravno, razlikuju velika i mala slova. Ako koristite -i, tada u datoteku na kraju napišite:

GO
EXIT

Ako nema EXIT na kraju, tada će sqlcmd nastaviti čekati unos, a ako prije EXIT neće GO, tada zadnja naredba neće raditi. Izlazna datoteka sadrži sav izlaz, odabire, poruke, ispis itd.

Invoke-SqlCmd proizvodi rezultat kao DataSet, DataTables ili DataRows. Stoga, ako obradite rezultat jednostavnog odabira, možete koristiti sqlcmd, analizirajući njegov izlaz, gotovo je nemoguće izvesti nešto složeno: za ovo postoji Invoke-SqlCmd. Ali ova ekipa ima i svoje šale:

  • Ako joj prenesete datoteku putem -Ulazna datotekatada EXIT nije potreban, štoviše, proizvodi sintaktičku pogrešku
  • -Izlazna datoteka ne, naredba vam vraća rezultat kao objekt
  • Postoje dvije sintakse za određivanje poslužitelja: -Instanca poslužitelja -Korisničko ime -Lozinka -Baza podataka i kroz -ConnectionString. Čudno je da u prvom slučaju nije moguće navesti port osim 1433.
  • izlaz teksta, tip PRINT, koji se jednostavno “hvata” sqlcmdza Invoke-SqlCmd je problem
  • I glavna stvar: Najvjerojatnije vaš Linux nema ovaj cmdlet!

I to je glavni problem. Samo u ožujku ovaj cmdlet postala dostupna za platforme koje nisu Windows, i konačno možemo krenuti naprijed!

Zamjena varijable

sqlcmd ima zamjenu varijable koristeći -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 zamjene:

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

Pa evo ga. U *nix zamjene varijabli ne rade. Parametar -v ignorirani. U Invoke-SqlCmd ignorirani -Varijable. Iako se parametar koji specificira same varijable zanemaruje, same zamjene rade - možete koristiti bilo koje varijable iz Shella. Međutim, uvrijedile su me varijable i odlučio sam uopće ne ovisiti o njima, te sam se ponašao grubo i primitivno, jer 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"

Ovo je, kao što razumijete, test već iz Unix verzije.

Prijenos datoteka

U verziji sustava Windows svaka je operacija bila popraćena revizijom: pokrenuli smo sqlcmd, primili neku vrstu zlouporabe u izlaznoj datoteci, priložili ovu datoteku revizijskoj ploči. Srećom, SQL poslužitelj je radio na istom poslužitelju kao Jenkins, učinjeno je 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

Stoga u cijelosti gutamo BCP datoteku i guramo je u polje nvarchar(max) revizijske tablice. Naravno, cijeli ovaj sustav se raspao jer sam umjesto SQL servera dobio RDS, a preko UNC-a BULK INSERT nikako ne radi zbog pokušaja ekskluzivnog zaključavanja datoteke, a kod RDS-a je to općenito osuđeno na propast samog početka. Stoga sam odlučio promijeniti dizajn sustava, pohranjujući reviziju red po red:

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

I upišite u ovu tablicu ovako:

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

Za odabir sadržaja potrebno je odabrati prema ID-u, birajući redom n (identitet).

U sljedećem ću članku detaljnije govoriti o tome kako sve to djeluje s Jenkinsom.

Izvor: www.habr.com

Dodajte komentar