ProHoster > Блог > Isakoso > Nṣiṣẹ pẹlu MS SQL lati Powershell lori Lainos

Nṣiṣẹ pẹlu MS SQL lati Powershell lori Lainos

Nkan yii jẹ iwulo daada ati igbẹhin si itan ibanujẹ mi

Ngbaradi fun Odo Fọwọkan PROD fun RDS (MS SQL), nipa eyiti gbogbo awọn eti wa n pariwo, Mo ṣe igbejade (POC - Proof Of Concept) ti adaṣe: ṣeto awọn iwe afọwọkọ agbara. Lẹhin igbejade, nigbati iji, iyìn gigun ti ku, titan sinu iyìn ailopin, wọn sọ fun mi - gbogbo eyi dara, ṣugbọn fun awọn idi arosọ nikan, gbogbo awọn ẹrú Jenkins wa ṣiṣẹ lori Linux!

Ṣe eyi ṣee ṣe? Mu iru kan gbona, atupa DBA lati labẹ Windows ki o si Stick ninu awọn gan ooru ti powershell labẹ Linux? Ṣe eyi kii ṣe ika?

Nṣiṣẹ pẹlu MS SQL lati Powershell lori Lainos
Mo ni lati fi ara mi bọmi ni akojọpọ ajeji ti awọn imọ-ẹrọ. Nitoribẹẹ, gbogbo awọn iwe afọwọkọ 30+ mi duro ṣiṣẹ. Si iyalenu mi, Mo ti ṣakoso lati ṣatunṣe ohun gbogbo ni ọjọ iṣẹ kan. Mo n kikọ ni gbona ilepa. Nitorinaa, awọn ipalara wo ni o le ba pade nigba gbigbe awọn iwe afọwọkọ agbara lati Windows si Linux?

sqlcmd vs Invoke-SqlCmd

Jẹ ki n ran ọ leti iyatọ akọkọ laarin wọn. Ti o dara atijọ IwUlO sqlcmd O tun ṣiṣẹ labẹ Lainos, pẹlu fere iṣẹ ṣiṣe. A kọja ibeere naa lati ṣiṣẹ -Q, faili titẹ sii bi -i, ati abajade bi -o. Ṣugbọn awọn orukọ faili, dajudaju, ni a ṣe ni ọran-kókó. Ti o ba lo -i, lẹhinna ninu faili kọ ni ipari:

GO
EXIT

Ti ko ba si ijade ni ipari, lẹhinna sqlcmd yoo tẹsiwaju lati duro fun titẹ sii, ati pe ti o ba ṣaaju JADE yoo ko GO, lẹhinna aṣẹ ti o kẹhin kii yoo ṣiṣẹ. Faili ti o wu jade ni gbogbo iṣẹjade, yiyan, awọn ifiranṣẹ, titẹ, ati bẹbẹ lọ.

Invoke-SqlCmd ṣe agbejade abajade bi DataSet, DataTables tabi DataRows. Nitorinaa, ti o ba ṣe ilana abajade ti yiyan ti o rọrun, o le lo sqlcmd, ti o ti ṣe atunwo abajade rẹ, o fẹrẹ jẹ pe ko ṣee ṣe lati ni nkan ti o nipọn: fun eyi o wa Pe-SqlCmd. Ṣugbọn ẹgbẹ yii tun ni awọn awada tirẹ:

  • Ti o ba gbe faili kan si ọdọ rẹ nipasẹ -File Inputlẹhinna JADE ko nilo, Jubẹlọ, o fun wa a sintasi aṣiṣe
  • -O wu faili rara, aṣẹ naa da abajade pada fun ọ bi ohun kan
  • Awọn ọna kika meji wa fun sisọ olupin kan pato: -ServerInstance -Orukọ olumulo -Ọrọigbaniwọle -Database ati nipasẹ -ConnectionString. Ni iyalẹnu, ni ọran akọkọ ko ṣee ṣe lati pato ibudo miiran ju 1433.
  • iṣẹjade ọrọ, tẹ PRINT, eyiti o jẹ “mu” nirọrun sqlcmd, fun Pe-SqlCmd jẹ iṣoro
  • Ati pataki julọ: O ṣeese julọ Lainos rẹ ko ni cmdlet yii!

Ati pe eyi ni iṣoro akọkọ. Nikan ni Oṣù yi cmdlet di wa fun ti kii-Windows iru ẹrọ, ati nikẹhin a le lọ siwaju!

Iyipada Ayipada

sqlcmd ni aropo oniyipada nipa lilo -v, fun apẹẹrẹ bii eyi:

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

Ninu iwe afọwọkọ SQL a lo awọn aropo:

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

Nitorina nibi o wa. Ninu * nix awọn aropo oniyipada ko ṣiṣẹ... Paramita -v bikita. U Pe-SqlCmd bikita -Ayipada. Botilẹjẹpe paramita ti o ṣalaye awọn oniyipada funrara wọn jẹ aibikita, awọn aropo funrararẹ ṣiṣẹ — o le lo eyikeyi awọn oniyipada lati Shell. Bibẹẹkọ, inu mi binu nipasẹ awọn oniyipada ati pinnu lati ma gbarale wọn rara, mo si ṣe aibikita ati ni iṣaaju, nitori awọn iwe afọwọkọ SQL kukuru:

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

Eyi, bi o ṣe loye, jẹ idanwo tẹlẹ lati ẹya Unix.

Ikojọpọ awọn faili

Ninu ẹya Windows, eyikeyi iṣẹ ṣiṣe wa pẹlu iṣayẹwo: a ran sqlcmd, gba iru ilokulo diẹ ninu faili ti o wu jade, so faili yii si awo ayẹwo. O da, olupin SQL ṣiṣẹ lori olupin kanna bi Jenkins, o ti ṣe nkan bi eyi:

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

Nitorinaa, a gbe faili BCP mì patapata ati gbe e sinu aaye nvarchar(max) ti tabili iṣayẹwo. Nitoribẹẹ, gbogbo eto yii ṣubu yato si, nitori dipo olupin SQL Mo ni RDS, ati INSERT BULK ko ṣiṣẹ rara nipasẹ UNC nitori igbiyanju lati mu titiipa iyasoto lori faili kan, ati pẹlu RDS eyi jẹ iparun gbogbogbo lati ọdọ. ibere ibere. Nitorinaa Mo pinnu lati yi apẹrẹ eto pada, titoju laini iṣayẹwo nipasẹ laini:

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

Ki o si kọ sinu tabili yii bi eleyi:

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

Lati yan akoonu, o nilo lati yan nipasẹ ID, yiyan ni ibere n (idanimọ).

Ninu nkan ti nbọ Emi yoo lọ sinu alaye diẹ sii nipa bii eyi ṣe ṣe ajọṣepọ pẹlu Jenkins.

orisun: www.habr.com

Fi ọrọìwòye kun