ProHoster > Blog > Pentadbiran > Bekerja dengan MS SQL dari Powershell di Linux

Bekerja dengan MS SQL dari Powershell di Linux

Artikel ini praktikal semata-mata dan didedikasikan untuk kisah sedih saya

Bersedia untuk PROD Sentuhan Sifar untuk RDS (MS SQL), yang semua telinga kami berdengung, saya membuat pembentangan (POC - Proof Of Concept) automasi: satu set skrip powershell. Selepas pembentangan, apabila tepukan ribut dan berpanjangan reda, bertukar menjadi tepukan tanpa henti, mereka memberitahu saya - semua ini bagus, tetapi hanya atas sebab ideologi, semua hamba Jenkins kami bekerja di Linux!

Adakah ini mungkin? Ambil DBA lampu yang hangat dari bawah Windows dan lekatkannya dalam kepanasan powershell di bawah Linux? Bukankah ini kejam?

Bekerja dengan MS SQL dari Powershell di Linux
Saya terpaksa melibatkan diri dalam gabungan teknologi yang pelik ini. Sudah tentu, semua 30+ skrip saya berhenti berfungsi. Terkejut saya, saya berjaya membetulkan semuanya dalam satu hari bekerja. Saya menulis dalam usaha mengejar. Jadi, apakah masalah yang boleh anda hadapi semasa memindahkan skrip powershell dari Windows ke Linux?

sqlcmd vs Invoke-SqlCmd

Biar saya ingatkan anda tentang perbezaan utama antara mereka. Utiliti lama yang baik sqlcmd Ia juga berfungsi di bawah Linux, dengan fungsi yang hampir sama. Kami lulus pertanyaan untuk melaksanakan -Q, fail input sebagai -i, dan output sebagai -o. Tetapi nama fail, sudah tentu, dibuat sensitif huruf besar. Jika anda menggunakan -i, maka dalam fail tulis pada akhir:

GO
EXIT

Jika tiada EXIT pada penghujungnya, maka sqlcmd akan terus menunggu input, dan jika sebelum ini EXIT tidak akan GO, maka arahan terakhir tidak akan berfungsi. Fail output mengandungi semua output, pilihan, mesej, cetakan, dsb.

Invoke-SqlCmd menghasilkan hasilnya sebagai DataSet, DataTables atau DataRows. Oleh itu, jika anda memproses hasil pilihan mudah, anda boleh gunakan sqlcmd, setelah menghuraikan outputnya, hampir mustahil untuk memperoleh sesuatu yang kompleks: untuk ini ada Invoke-SqlCmd. Tetapi pasukan ini juga mempunyai jenakanya sendiri:

  • Jika anda memindahkan fail kepadanya melalui -InputFail, Kemudian EXIT tidak diperlukan, lebih-lebih lagi, ia menghasilkan ralat sintaks
  • -Fail keluaran tidak, arahan itu mengembalikan hasilnya kepada anda sebagai objek
  • Terdapat dua sintaks untuk menentukan pelayan: -ServerInstance -Nama Pengguna -Kata Laluan -Pangkalan Data dan melalui -ConnectionString. Anehnya, dalam kes pertama adalah tidak mungkin untuk menentukan port selain 1433.
  • output teks, taip CETAK, yang hanya "ditangkap" sqlcmduntuk Invoke-SqlCmd adalah masalah
  • Dan yang paling penting: Kemungkinan besar Linux anda tidak mempunyai cmdlet ini!

Dan ini adalah masalah utama. Hanya pada bulan Mac cmdlet ini tersedia untuk platform bukan Windows, dan akhirnya kita boleh maju!

Penggantian Pembolehubah

sqlcmd mempunyai penggantian berubah menggunakan -v, contohnya seperti ini:

# $conn содСрТит Π½Π°Ρ‡Π°Π»ΠΎ ΠΊΠΎΠΌΠ°Π½Π΄Ρ‹ sqlcmd
$cmd = $conn + " -i D:appsSlaveJobsKillSpid.sql -o killspid.res 
  -v spid =`"" + $spid + "`" -v age =`"" + $age + "`""
Invoke-Expression $cmd

Dalam skrip SQL kami menggunakan penggantian:

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

Jadi begini. Dalam *nix penggantian berubah tidak berfungsi. Parameter -v diabaikan. U Invoke-SqlCmd diabaikan -Pembolehubah. Walaupun parameter yang menentukan pembolehubah itu sendiri diabaikan, penggantian itu sendiri berfungsiβ€”anda boleh menggunakan sebarang pembolehubah daripada Shell. Walau bagaimanapun, saya tersinggung dengan pembolehubah dan memutuskan untuk tidak bergantung pada mereka sama sekali, dan bertindak kasar dan primitif, kerana skrip SQL adalah pendek:

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

Ini, seperti yang anda faham, adalah ujian daripada versi Unix.

Fail dimuatnaik

Dalam versi Windows, sebarang operasi disertakan dengan audit: kami menjalankan sqlcmd, menerima beberapa jenis penyalahgunaan dalam fail output, melampirkan fail ini pada plat audit. Nasib baik, pelayan SQL berfungsi pada pelayan yang sama seperti Jenkins, ia dilakukan seperti ini:

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

Oleh itu, kami menelan fail BCP sepenuhnya dan memasukkannya ke dalam medan nvarchar(maks) jadual audit. Sudah tentu, keseluruhan sistem ini runtuh, kerana bukannya pelayan SQL saya mendapat RDS, dan BULK INSERT tidak berfungsi sama sekali melalui UNC kerana percubaan untuk mengambil kunci eksklusif pada fail, dan dengan RDS ini biasanya ditakdirkan daripada awal-awal lagi. Jadi saya memutuskan untuk menukar reka bentuk sistem, menyimpan audit baris demi baris:

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

Dan tulis dalam jadual ini seperti ini:

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

Untuk memilih kandungan, anda perlu memilih mengikut ID, memilih mengikut urutan n (identiti).

Dalam artikel seterusnya saya akan pergi ke lebih terperinci tentang bagaimana ini semua berinteraksi dengan Jenkins.

Sumber: www.habr.com

Tambah komen