ProHoster > blog > administrasi > Bekerja dengan MS SQL dari Powershell di Linux

Bekerja dengan MS SQL dari Powershell di Linux

Artikel ini murni praktis dan didedikasikan untuk kisah sedih saya

Bersiap-siap untuk PROD Nol Sentuh untuk RDS (MS SQL), yang membuat semua telinga kita berdengung, saya membuat presentasi (POC - Proof Of Concept) otomatisasi: satu set skrip PowerShell. Setelah presentasi, ketika tepuk tangan meriah dan berkepanjangan mereda, berubah menjadi tepuk tangan yang tiada henti, mereka mengatakan kepada saya - semua ini bagus, tetapi hanya karena alasan ideologis, semua budak Jenkins kami bekerja di Linux!

Apakah ini mungkin? Ambil DBA lampu yang hangat dari Windows dan tempelkan di PowerShell yang sangat panas di Linux? Bukankah ini kejam?

Bekerja dengan MS SQL dari Powershell di Linux
Saya harus membenamkan diri dalam kombinasi teknologi yang aneh ini. Tentu saja, 30+ skrip saya berhenti berfungsi. Yang mengejutkan saya, saya berhasil memperbaiki semuanya dalam satu hari kerja. Saya menulis dalam pengejaran. Jadi, kendala apa yang bisa Anda temui saat mentransfer skrip PowerShell dari Windows ke Linux?

sqlcmd vs Panggil-SqlCmd

Izinkan saya mengingatkan Anda tentang perbedaan utama di antara keduanya. Utilitas lama yang bagus sqlcmd Ia juga bekerja di Linux, dengan fungsi yang hampir sama. Kami meneruskan kueri untuk mengeksekusi -Q, file masukan sebagai -i, dan keluaran sebagai -o. Namun nama file, tentu saja, dibuat peka huruf besar-kecil. Jika Anda menggunakan -i, maka di file tulis di akhir:

GO
EXIT

Jika tidak ada EXIT di akhir, maka sqlcmd akan melanjutkan menunggu input, dan jika sebelumnya EXIT tidak akan GO, maka perintah terakhir tidak akan berfungsi. File keluaran berisi semua keluaran, pilihan, pesan, pencetakan, dll.

Invoke-SqlCmd menghasilkan hasil sebagai DataSet, DataTables atau DataRows. Oleh karena itu, jika Anda mengolah hasil seleksi sederhana, Anda dapat menggunakannya sqlcmd, setelah menguraikan keluarannya, hampir tidak mungkin untuk mendapatkan sesuatu yang kompleks: untuk ini ada Panggil-SqlCmd. Namun tim ini juga punya leluconnya sendiri:

  • Jika Anda mentransfer file kepadanya melalui -File Masukan, Kemudian EXIT tidak diperlukan, apalagi menghasilkan kesalahan sintaksis
  • -Berkas keluaran tidak, perintah mengembalikan hasilnya kepada Anda sebagai objek
  • Ada dua sintaks untuk menentukan server: -ServerInstance -Nama Pengguna -Kata Sandi -Database dan melalui -String Koneksi. Anehnya, dalam kasus pertama tidak mungkin untuk menentukan port selain 1433.
  • keluaran teks, ketik PRINT, yang cukup β€œtertangkap” sqlcmduntuk Panggil-SqlCmd adalah sebuah masalah
  • Dan yang paling penting: Kemungkinan besar Linux Anda tidak memiliki cmdlet ini!

Dan inilah masalah utamanya. Hanya di bulan Maret cmdlet ini menjadi tersedia untuk platform non-Windows, dan akhirnya kita bisa maju!

Substitusi Variabel

sqlcmd memiliki substitusi variabel 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 substitusi:

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

Jadi begini. Di *nix substitusi variabel tidak berfungsi. Parameter -v diabaikan. kamu Panggil-SqlCmd diabaikan -Variabel. Meskipun parameter yang menentukan variabel itu sendiri diabaikan, substitusinya sendiri berfungsiβ€”Anda dapat menggunakan variabel apa pun dari Shell. Namun, saya tersinggung dengan variabel tersebut dan memutuskan untuk tidak bergantung pada variabel tersebut sama sekali, dan bertindak kasar dan primitif, karena skrip SQL-nya 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 pahami, adalah tes dari versi Unix.

Mengunggah file

Dalam versi Windows, operasi apa pun disertai dengan audit: kami menjalankan sqlcmd, menerima semacam penyalahgunaan dalam file keluaran, melampirkan file ini ke pelat audit. Untungnya, server SQL bekerja di server yang sama dengan Jenkins, 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

Jadi, kami menelan seluruh file BCP dan memasukkannya ke dalam bidang nvarchar(max) pada tabel audit. Tentu saja, seluruh sistem ini berantakan, karena alih-alih server SQL saya mendapatkan RDS, dan BULK INSERT tidak berfungsi sama sekali melalui UNC karena upaya untuk mengambil kunci eksklusif pada file, dan dengan RDS hal ini umumnya gagal. awal mulanya. Jadi saya memutuskan untuk mengubah desain sistem dengan menyimpan audit baris demi baris:

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

Dan tulis di tabel 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 konten, Anda perlu memilih berdasarkan ID, memilih secara berurutan n (identitas).

Pada artikel selanjutnya saya akan membahas lebih detail tentang bagaimana semua ini berinteraksi dengan Jenkins.

Sumber: www.habr.com

Tambah komentar