🥇Darbs ar MS SQL no Powershell uz Linux

Šis raksts ir pilnīgi praktisks un veltīts manai skumjai stāstij.

Sagatavojoties Zero Touch PROD RDS (MS SQL), par kuru mums visu laiku stāstīja, es izveidoju prezentāciju (POC — Proof Of Concept) automatizācijai: PowerShell skriptu komplektam. Pēc prezentācijas, kad izskanēja ilgstošas, skaļas aplaudēšanas un ovācijas, man pateica — viss tas ir labi, bet ideoloģisku iemeslu dēļ mūsu visi Jenkins slaves darbojas Linux!

Vai tā var? Ņemt tādu siltu, mājīgu DBA no Windows un iemest viņu pašu smagajā PowerShell Linux? Vai tas nav nežēlīgi?

Bija jāiedziļinās šajā dīvainajā tehnoloģiju kombinācijā. Protams, visi mani 30+ skripti pārstāja darboties. Manā pārsteigumam, vienas darba dienas laikā man izdevās visu salabot. Rakstu pēc karstām pēdām. Tātad, kādi zemūdens akmeņi var sagaidīt jūs, pārsūtot PowerShell skriptus no Windows uz Linux?

sqlcmd vs Invoke-SqlCmd

Atgādināšu galveno atšķirību starp tiem. Vecā labā utilīta sqlcmd darbojas arī Linux, ar gandrīz identisku funkcionalitāti. Vaicājumu izpildei mēs pārsūtām -Q, ieejas failu kā -i, bet izeju -o. Tikai failu nosaukumi, protams, ir case-sensitive. Ja jūs izmantojat -i, tad failā beigās ierakstiet:

GO
EXIT

Ja beigās nebūs EXIT, tad sqlcmd pāries uz ievades gaidīšanu, bet ja pirms EXIT neizmainīs GO, tad pēdējā komanda netiks izpildīta. izejas failā nonāks visa izeja, selects, ziņojumi, print utt.

Invoke-SqlCmd atgriež rezultātu DataSet, DataTables vai DataRows formā. Tāpēc, ja jūs varat apstrādāt vienkārša select rezultātu caur sqlcmd, aplūkojot tā izeju, tad izvadīt kaut ko sarežģītu praktiski nav iespējams: tam ir Invoke-SqlCmd. Bet šai komandai ir savi dīvainības:

Ja jūs piedāvājat tai failu caur -InputFile, tad EXIT tas nav vajadzīgs, turklāt, tas atgriež sintakse kļūdu
-OutputFile nē, komanda atgriež jums rezultātu objekta veidā.
Lai norādītu serveri, ir divi sintakses varianti: -ServerInstance -Username -Password -Database un caur -ConnectionString. Dīvaini, bet pirmajā gadījumā norādīt portu, kas atšķiras no 1433, nav iespējams.
teksta izeja, piemēram, PRINT, kas elementāri "noķer" sqlcmd, lai Invoke-SqlCmd ir problēma
Un galvenais: visdrīzāk jūsu Linux šī cmdlet nav!

Un tas ir galvenais jautājums. Tikai martā šis cmdlet kļuva pieejams ne-Windows platformām, un beidzot mēs varam virzīties uz priekšu!

Mainīgo nomaiņa

sqlcmd atbalsta mainīgo ievietošanu, izmantojot -v, piemēram, šādi:

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

SQL skriptā mēs izmantojam ievietojumus:

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

Tātad. Unix līdzīgajās sistēmās mainīgo ievietošanas funkcionalitāte nefunkcionē. Parametrs -v tiek ignorēta. Pie Invoke-SqlCmd tiek ignorēta -Mainīgie. Lai gan parametrs, kas nosaka pašus mainīgos, tiek ignorēts, paši ievietojumi darbojas — jūs varat izmantot jebkurus Shell mainīgos. Tomēr es izlēmu nepaļauties uz mainīgajiem un rīkojos nežēlīgi un primitīvi, jo skripti SQL ir īsi:

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

Kā jūs jau sapratāt, šis ir tests, kas jau ir no UNIX versijas.

Failu augšupielāde

Windows versijā man katra darbība bija saistīta ar auditu: izpildījām sqlcmd, saņēmām kādu kļūdu output failā, pievienojām šo failu audita tabulai. Par laimi, SQL serveris strādāja tajā pašā serverī, kur Jenkins, tas tika veikts apmēram šādi:

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

Tādējādi mēs ieņemam BCP failu pilnībā un ieliekam to auditācijas tabulas nvarchar(max) laukā. Protams, visa šī sistēma sabruka, jo vietā SQL servera es saņēmu RDS, un BULK INSERT vispār nefunkcionē uz UNC dēļ mēģinājuma iegūt ekskluzīvu bloķēšanu uz failu; ar RDS tas bija iepriekš nolemts. Tātad es izlēmu mainīt sistēmas dizainu, uzkrājot auditāciju pa rindām:

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

Un rakstīt šajā tabulā tā:

function WriteAudit([string]$Filename, [string]$ConnStr, 
     [string]$Tabname, [string]$Jobname)
{
  # get $lastid of the last execution  -- izlaists rakstam
	
  #izveidot datu pamatu un piepildīt to ar datiem no faila
  $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)   
    } 

  # rakstīt to tabulā
  $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() 
  }

Lai izvēlētos saturu, jums jāveic select pa ID, izvēloties pēc n (identitātes).

Nākamajā rakstā es sīkāk pastāstīšu par to, kā tas viss mijiedarbojas ar Jenkins.

Avots: habr.com