עבודה עם MS SQL מ- Powershell על לינוקס

מאמר זה הוא פרקטי בלבד ומוקדש לסיפור העצוב שלי

מתכונן ל Zero Touch PROD עבור RDS (MS SQL), שלגביו כל האוזניים מזמזמו, הכנתי מצגת (POC - Proof Of Concept) של אוטומציה: סט של סקריפטים של Powershell. לאחר המצגת, כשמחיאות הכפיים הסוערות והממושכות דעכו, והפכו למחיאות כפיים בלתי פוסקות, אמרו לי - כל זה טוב, אבל רק מסיבות אידיאולוגיות, כל עבדי הג'נקינס שלנו עובדים על לינוקס!

האם זה אפשרי? לקחת DBA כל כך חם ומנורה מתחת ל-Windows ולהדביק אותו בחום של Powershell תחת לינוקס? זה לא אכזרי?

הייתי צריך לשקוע בשילוב המוזר הזה של טכנולוגיות. כמובן, כל 30+ הסקריפטים שלי הפסיקו לעבוד. להפתעתי, הצלחתי לתקן הכל ביום עבודה אחד. אני כותב במרדף לוהט. אז באילו מלכודות אתה יכול להיתקל בעת העברת סקריפטים של Powershell מ-Windows ללינוקס?

sqlcmd לעומת Invoke-SqlCmd

הרשו לי להזכיר לכם את ההבדל העיקרי ביניהם. שירות ישן וטוב sqlcmd זה עובד גם תחת לינוקס, עם פונקציונליות כמעט זהה. אנו מעבירים את השאילתה לביצוע -Q, קובץ הקלט כ-i, והפלט כ-o. אבל שמות הקבצים, כמובן, נעשים תלויי-רישיות. אם אתה משתמש ב-i, אז בקובץ כתוב בסוף:

GO
EXIT

אם אין EXIT בסוף, אז sqlcmd ימשיך להמתין לקלט, ואם לפני יְצִיאָה לא GO, אז הפקודה האחרונה לא תעבוד. קובץ הפלט מכיל את כל הפלט, הבחירות, ההודעות, ההדפסה וכו'.

Invoke-SqlCmd מייצר את התוצאה כ-DataSet, DataTables או DataRows. לכן, אם אתה מעבד את התוצאה של בחירה פשוטה, אתה יכול להשתמש sqlcmd, לאחר שניתח את הפלט שלו, זה כמעט בלתי אפשרי לגזור משהו מורכב: בשביל זה יש Invoke-SqlCmd. אבל לצוות הזה יש גם בדיחות משלו:

• אם תעביר לה קובץ באמצעות -InputFile, אז יְצִיאָה אין צורך, יתר על כן, הוא מייצר שגיאת תחביר
• -קובץ פלט לא, הפקודה מחזירה לך את התוצאה כאובייקט
• ישנם שני תחבירים לציון שרת: -ServerInstance - שם משתמש - סיסמה - מסד נתונים ודרך -מחרוזת חיבור. באופן מוזר, במקרה הראשון לא ניתן לציין יציאה אחרת מאשר 1433.
• פלט טקסט, הקלד PRINT, שפשוט "נתפס" sqlcmdעבור Invoke-SqlCmd מהווה בעיה
• והכי חשוב: סביר להניח שאין ללינוקס שלך את ה-cmdlet הזה!

וזו הבעיה העיקרית. רק במרץ ה-cmdlet הזה הפך זמין עבור פלטפורמות שאינן Windows, וסוף סוף נוכל להתקדם!

החלפה משתנה

ל-sqlcmd יש החלפת משתנה באמצעות -v, למשל כך:

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

בסקריפט SQL אנו משתמשים בהחלפות:

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

אז הנה זה. ב-*nix החלפות משתנות לא עובדות... פָּרָמֶטֶר -v התעלמו. U Invoke-SqlCmd התעלמו -משתנים. למרות שמתעלמים מהפרמטר שמציין את המשתנים עצמם, ההחלפות עצמן פועלות - אתה יכול להשתמש בכל משתנים מ-Shell. עם זאת, נעלבתי מהמשתנים והחלטתי לא לסמוך עליהם כלל, ופעלתי בגסות ופרימיטיבית, מכיוון שהסקריפטים של SQL קצרים:

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

זה, כפי שאתה מבין, מבחן כבר מגרסת יוניקס.

העלה קבצים

בגרסת Windows כל פעולה לוותה בביקורת: הרצנו sqlcmd, קיבלנו איזושהי התעללות בקובץ הפלט, צירפנו את הקובץ הזה ללוח הביקורת. למרבה המזל, שרת SQL עבד על אותו שרת כמו ג'נקינס, זה נעשה בערך כך:

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

לפיכך, אנו בולעים את קובץ ה-BCP לחלוטין ודוחפים אותו לשדה nvarchar(max) של טבלת הביקורת. כמובן שכל המערכת הזו התפרקה, כי במקום שרת SQL קיבלתי RDS, ו-BULK INSERT לא עובד בכלל דרך UNC עקב ניסיון לקחת נעילה בלעדית על קובץ, ועם RDS זה בכלל נידון מ ממש ההתחלה. אז החלטתי לשנות את עיצוב המערכת, לאחסן את הביקורת שורה אחר שורה:

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

וכתוב בטבלה הזו כך:

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

כדי לבחור תוכן, עליך לבחור לפי מזהה, בחירה בסדר n (זהות).

במאמר הבא אפרט יותר על האופן שבו כל זה מתקשר עם ג'נקינס.

מקור: www.habr.com