MS SQL-i kasutamine Powershelliga Linuxis

See artikkel on täiesti praktiline ja pühendatud minu kurvale loole

Valmistudes Zero Touch PROD RDS (MS SQL) jaoks, millest meile on pidevalt räägitud, tegin esitluse (POC — Proof Of Concept) automatiseerimise kohta: powershelli skriptide kogumik. Pärast esitlust, kui vali ja pikaajaline aplaus vaibus ning muutus lakkamatuks ovatsiooniks, öeldi mulle – kõik see on hea, kuid ideoloogilistel põhjustel töötavad kõik meie Jenkins slave'id Linuxis!

Kas see on tõesti võimalik? Võtta nii soe, kodune DBA Windowsilt ja visata ta Linuxi powershelli tule alla? Kas see pole mitte julm?

Pidin süvenema sellesse ebaharilikku tehnoloogiate kombinatsiooni. Loomulikult lõpetasid kõik mu 30+ skripti töötamise. Olin üllatunud, et suutsin kõike ühe tööpäeva jooksul parandada. Kirjutan kuumade jälgede pealt. Nii et, millised lõksud võivad teid oodata powershelli skriptide üleviimisel Windowsilt Linuxile?

sqlcmd vs Invoke-SqlCmd

Tuletan meelde nende põhilist erinevust. Vanakoolne utiliit sqlcmd töötavad ka Linuxis, peaaegu identse funktsionaalsusega. Küsitlus, mida me täitmiseks edastame, on -Q, sisendfail -i ja väljund -o. Ainult failide nimed, loomulikult, on tundlikud suurtähe osas. Kui kasutate -i, siis failis kirjutage lõpus:

GO
EXIT

Kui lõpus ei ole EXIT, siis sqlcmd läheb sisendi ootamise režiimi, ja kui enne EXIT asenda GO, siis viimane käsk ei toimi. Väljundfaili satub kogu väljund, selects, teated, print jne.

Invoke-SqlCmd tagastab tulemuse DataSeti, DataTable'i või DataRow'de kujul. Seega, kui lihtsa selecti tulemust saate töötleda ka selle kaudu sqlcmd, lahendades selle väljundi, siis keerulise väljastamine on praktiliselt võimatu: selleks on Invoke-SqlCmd. Kuid sellel käsul on ka oma naljad:

• Kui edastate sellele faili kaudu -InputFile, siis EXIT ei ole vajalik, rohkemgi veel, see annab süntaksi vea
• -OutputFile ei, käsk tagastab teile tulemuse objekti kujul
• Serveri määramiseks on kaks süntaksit: -ServerInstance -Username -Password -Database ja läbi -ConnectionString. Kuidas imelik, esimeses variandis ei ole võimalik määrata porti, mis erineb 1433-st.
• teksti väljund, nagu PRINT, mida on elementaarselt "püütud" sqlcmd, jaoks Invoke-SqlCmd on probleem
• Ja peamine: tõenäoliselt pole teie Linuxis seda cmdleti olemas!

Ja see on peamine probleem. Alles märtsis sai see cmdlet kaasaegsetele mitte-Windowsi platvormidele kätte saadavaks, ja nüüd saame edasi liikuda!

Muutujate asendamine

Sqlcmd-is on muutuva asendamine -v kaudu, näiteks nii:

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

SQL skriptis kasutame asendusi:

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

Noh, *nix'is muutujate asendamine ei tööta. Parameeter -v ignoreeritakse. Meil Invoke-SqlCmd ignoreeritakse -Muudatused. Kuigi parameeter, mis määrab ise muutujad, ignoreeritakse, siis ise asendused töötavad — saate kasutada kõiki muutujaid Shell'is. Kuid ma viha muutujate vastu ja otsustasin neist üldse mitte sõltuda, ning läksin karmilt ja primitiivselt, õnneks on SQL skriptid lühikesed:

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

See on, nagu te juba aru saite, test juba unixiversioonist.

Failide laadimine

Windowsi versioonis saatis iga toiming auditi: käivitasime sqlcmd, saime mõnda nurinat väljundfailis, lisasime selle faili auditi tabelisse. Õnneks töötas SQL server samal serveril koos Jenkinsiga, see tehti umbes nii:

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

Nii saame me BCP faili tervikuna ja pistame selle nvarchar(max) tüüpi auditi tabelisse. Loomulikult läks kogu see süsteem räbalaks, kuna SQL serveri asemel sain RDS'i, ja BULK INSERT ei tööta UNC kaudu, kuna see üritab faili eksklusiivselt lukustada, ja RDS-iga on see üldse algusest peale ette määratud. Nii et otsustasin süsteemi disaini muuta, hoides auditi reavahetusena:

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

Ja kirjutage seda tabelisse nii:

function WriteAudit([string]$Filename, [string]$ConnStr, 
     [string]$Tabname, [string]$Jobname)
{
  # get $lastid of the last execution  -- vahele jäetud artikli jaoks
	
  #loo tabel ja täida see faili andmetega
  $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)   
    } 

  # kirjutage see tabelisse
  $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() 
  }  

Sisu valimiseks tuleb teha select ID järgi, valides järjekorras n (identiteet).

Järgmises artiklis käsitlen ma põhjalikumalt, kuidas see kõik Jenkinsiga suhtleb.

Allikas: habr.com