ProHoster > Блог > Administracija > Rad sa MS SQL-om iz Powershell-a na Linuxu

Rad sa MS SQL-om iz Powershell-a na Linuxu

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

Spremam se za Zero Touch PROD za RDS (MS SQL), o kojem su nam sve uši brujale, napravio sam prezentaciju (POC - Proof Of Concept) automatizacije: skup powershell skripti. Nakon prezentacije, kada je buran, dugotrajan aplauz utihnuo, prešao u neprestani aplauz, rekli su mi - sve je to dobro, ali samo iz ideoloških razloga, svi naši robovi Jenkinsa rade na Linuxu!

Je li to moguće? Uzmite tako toplu, lampu DBA ispod Windowsa i zataknite je u samu vrućinu powershell-a pod Linuxom? Nije li ovo okrutno?

Rad sa MS SQL-om iz Powershell-a na Linuxu
Morao sam se uroniti u ovu čudnu kombinaciju tehnologija. Naravno, svih mojih 30+ skripti su prestale da rade. Na moje iznenađenje, uspjela sam sve popraviti u jednom radnom danu. Pišem u žurbi. Dakle, na koje zamke možete naići kada prenosite powershell skripte sa Windowsa na Linux?

sqlcmd vs Invoke-SqlCmd

Dozvolite mi da vas podsjetim na glavnu razliku između njih. Dobri stari uslužni program sqlcmd Također radi pod Linuxom, sa gotovo identičnom funkcionalnošću. Prosljeđujemo upit za izvršavanje -Q, ulaznu datoteku kao -i, a izlaznu kao -o. Ali imena datoteka, naravno, razlikuju se velika i mala slova. Ako koristite -i, onda u datoteci napišite na kraju:

GO
EXIT

Ako na kraju nema EXIT-a, tada će sqlcmd nastaviti čekati na unos, a ako prije IZLAZ neće GO, tada posljednja komanda 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 ga koristiti sqlcmd, nakon raščlanjivanja njegovog izlaza, gotovo je nemoguće izvesti nešto složeno: jer ovo postoji Invoke-SqlCmd. Ali ova ekipa ima i svoje šale:

  • Ako joj prenesete datoteku putem -InputFile, onda IZLAZ nije potrebno, štoviše, proizvodi sintaksičku grešku
  • -OutputFile ne, komanda vam vraća rezultat kao objekat
  • Postoje dvije sintakse za određivanje servera: -ServerInstance -Korisničko ime -Lozinka -Baza podataka i kroz -ConnectionString. Čudno, u prvom slučaju nije moguće navesti port osim 1433.
  • izlaz teksta, ukucajte PRINT, koji je jednostavno "uhvaćen" sqlcmdza Invoke-SqlCmd je problem
  • I najvažnije: Najvjerovatnije vaš Linux nema ovaj cmdlet!

I to je glavni problem. Samo u martu ovaj cmdlet postao dostupan za ne-Windows platforme, 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)

Evo ga. U *nix zamjene varijabli ne rade. Parametar -v ignorisano. U Invoke-SqlCmd ignorisano -Varijable. Iako je parametar koji specificira same varijable zanemaren, same zamjene funkcioniraju—možete koristiti bilo koje varijable iz Shell-a. Međutim, uvrijedile su me varijable i odlučio sam da uopće ne ovisim o njima, te sam postupio 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.

Učitavanje fajlova

U Windows verziji, svaka operacija je bila praćena revizijom: pokrenuli smo sqlcmd, primili neku vrstu zloupotrebe u izlaznoj datoteci, priložili ovu datoteku na ploču za reviziju. Srećom, SQL server je radio na istom serveru kao i Jenkins, urađeno 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

Dakle, u potpunosti progutamo BCP datoteku i uguramo je u nvarchar(max) polje tabele revizije. Naravno, ceo ovaj sistem se raspao jer sam umesto SQL servera dobio RDS, a BULK INSERT uopste ne radi preko UNC-a zbog pokusaja preuzimanja ekskluzivne brave na fajlu, a sa RDS-om je to generalno osudjeno na propast. samom početku. Zato sam odlučio da promenim dizajn sistema, čuvajući reviziju red po red:

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

I zapišite u ovu tabelu 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() 
  }

Da biste odabrali sadržaj, potrebno je da odaberete po ID-u, birajući redom n (identitet).

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

izvor: www.habr.com

Dodajte komentar