sqlcmd და bcp: SQL Server ბრძანების ხაზიდან

sqlcmd T-SQL-ს ტერმინალიდან უშვებს, bcp კი ცხრილის მონაცემებს ფაილებში და ფაილებიდან სწრაფად გადააქვს. ერთად ისინი ფარავს ყველაფერს, რასაც სხვა შემთხვევაში SSMS-ში ღილაკებით გადიოდი და ვერ ავტომატიზირებდი: მიგრაციის სკრიპტების გაშვება CI-დან, backup-ის აღება cron-იდან, query-ის CSV-ში გადმოტანა, ფაილიდან მილიონი row-ის ჩატვირთვა წამებში. ორი ბრძანება, რომელსაც ყველაზე ხშირად გამოიყენებ, არის sqlcmd -S host,port -U user -d db -C -b -i script.sql და bcp dbo.Table out table.dat -n -S host,port -U user -d db. ამ გზამკვლევის დანარჩენი ნაწილი მათ გარშემო არსებული flag-ებია და ნაგულისხმევები, რომლებიც version 18-ში შეიცვალა და უამრავი სკრიპტი გატეხა.

რომელი sqlcmd გაქვს#

არსებობს ორი პროგრამა სახელით sqlcmd, და ისინი ძირითადად ერთსა და იმავე არგუმენტებს იღებენ:

• ODBC sqlcmd - ორიგინალი, რომელიც SQL Server-თან ერთად მოდის და Microsoft-ის mssql-tools18 პაკეტშია Linux-ისა და macOS-ისთვის (ყენდება /opt/mssql-tools18/bin/-ში). მას გვერდით დაყენებული ODBC Driver for SQL Server სჭირდება.
• go-sqlcmd - უფრო ახალი, ერთ ბინარად გადაწერილი ვერსია Go-ზე, რომელიც Windows-ზე winget install sqlcmd-ით ან macOS-ზე brew install sqlcmd-ით ყენდება. მას ODBC დრაივერი არ სჭირდება, ამატებს რამდენიმე მოხერხებულობას, მაგალითად ლოკალური SQL Server კონტეინერის შექმნას, და მიზნად ისახავს, სკრიპტებისთვის პირდაპირი შემცვლელი იყოს.

სკრიპტინგისთვის ორივე მუშაობს. სადაც მათი ქცევა განსხვავდება, ეს გზამკვლევი ამას აღნიშნავს. bcp მხოლოდ ODBC ვარიანტში არსებობს, იმავე mssql-tools18 პაკეტიდან ან Windows-ზე SQL Server-ის ინსტალაციიდან. შეამოწმე, რა გაქვს:

$ sqlcmd -?$ bcp -v

version 18-ის ინსტრუმენტებმა ერთი ცვლილება შეიტანეს, რომელიც ყველა დანარჩენზე მნიშვნელოვანია: კავშირები ნაგულისხმევად დაშიფრულია და სერვერის სერტიფიკატი მოწმდება. სერვერი self-signed სერტიფიკატით - ასეთია სადეველოპერო ინსტანციების უმეტესობა და ბევრი ჰოსტინგის ინსტანციაც - მაშინ certificate chain შეცდომით ჩავარდება. გადაეცი -C sqlcmd-ს (სერვერის სერტიფიკატის ნდობა) და -u bcp 18-ს, ან დააყენე სერტიფიკატი, რომელსაც კლიენტი ენდობა. ძველი ინსტრუმენტებისთვის დაწერილი სკრიპტები ამ flag-ების გარეშე ჩვეული მიზეზია, რის გამოც CI job-ი runner-ის image-ის განახლების შემდეგ გატყდა.

დაკავშირება#

$ export SQLCMDPASSWORD='the-generated-password'$ sqlcmd -S db.example.net,14330 -U sa -d appdb -C1> SELECT @@VERSION;2> GO

flag-ები:

პაროლის -P-ით გადაცემა მას შენი shell-ის ისტორიაში და პროცესების სიაში ათავსებს, სადაც მანქანის ნებისმიერ სხვა მომხმარებელს შეუძლია წაიკითხოს. SQLCMDPASSWORD გარემოში, CI-ში secret store-იდან დაყენებული, ორივეს აგარიდებს. არსებობს SQLCMDSERVER, SQLCMDUSER და SQLCMDDBNAME-იც, ამიტომ სკრიპტს შეიძლება საერთოდ არ ჰქონდეს კავშირის დეტალები.

-d-ის გარეშე login-ის ნაგულისხმევ ბაზაში ხვდები. RE:NODE-ზე შენთვის შექმნილი ბაზა უკვე sa-ს ნაგულისხმევია, ამიტომ sqlcmd -S host,port -U sa -C პირდაპირ მასში შეგიყვანს.

Query-ებისა და სკრიპტების გაშვება#

ინტერაქტიულ რეჟიმში არაფერი ეშვება, სანამ ცალკე ხაზზე GO-ს არ აკრეფ. GO T-SQL არ არის: ეს batch-ის გამყოფია, რომელიც sqlcmd-ს და SSMS-ს ესმით, და ისინი სკრიპტს ყოველ GO-ზე ყოფენ და ნაწილებს სათითაოდ აგზავნიან. სწორედ ამიტომ უნდა იყოს CREATE PROCEDURE თავის batch-ში პირველი ინსტრუქცია და ამიტომ ქრება GO-მდე გამოცხადებული ცვლადი მის შემდეგ. GO 100 წინა batch-ს ასჯერ უშვებს, რაც სატესტო მონაცემების გენერირებისთვის მოსახერხებელია.

ერთჯერადი ბრძანებებისთვის -Q query-ს უშვებს და გადის:

$ sqlcmd -S db.example.net,14330 -U sa -C -Q "SELECT name, state_desc FROM sys.databases"

-q (პატარა ასოთი) მას უშვებს და ინტერაქტიულ რეჟიმში რჩება. ფაილებისთვის - -i:

$ sqlcmd -S db.example.net,14330 -U sa -d appdb -C -b -i 001_schema.sql -i 002_seed.sql

რამდენიმე -i ფაილი რიგით ეშვება ერთ სესიაში. სკრიპტის შიგნით ორწერტილით დაწყებული ბრძანებები sqlcmd-ის დირექტივებია და არა T-SQL:

SSMS-ს იგივე დირექტივები ესმის, თუ Query მენიუდან SQLCMD Mode-ს ჩართავ, რაც ერთ სკრიპტს საშუალებას აძლევს, იმუშაოს როგორც ინტერაქტიულად, ისე pipeline-ში.

თუ შენი სკრიპტი N'...' ლიტერალებში არა-ASCII ტექსტს შეიცავს, ODBC sqlcmd-ს ფაილის კოდირება უთხარი -f 65001-ით UTF-8-ისთვის, ან სკრიპტი შეინახე UTF-8-ად byte-order mark-ით. არცერთის გარეშე აქცენტიანი სიმბოლოები შეიძლება დამახინჯებული მივიდეს. SQL Server-ის collation-ები და Unicode ხსნის, რატომ არის N პრეფიქსი საერთოდ მნიშვნელოვანი.

ცვლადები და exit კოდები ავტომატიზაციისთვის#

სკრიპტის ცვლადები ერთ სკრიპტს რამდენიმე გარემოზე მუშაობის საშუალებას აძლევს. მიმართე მათ როგორც $(Name) და მიაწოდე -v-ით:

:on error exitUSE [$(DbName)];IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'$(ReaderUser)')    CREATE USER [$(ReaderUser)] FOR LOGIN [$(ReaderUser)];ALTER ROLE db_datareader ADD MEMBER [$(ReaderUser)];GO

$ sqlcmd -S db.example.net,14330 -U sa -C -b \    -v DbName="appdb" ReaderUser="reporting" \    -i create-reader.sql

ცვლადები ტექსტად ჩაისმება batch-ის გაგზავნამდე, ამიტომ ისინი ყველგან მუშაობს - ობიექტების სახელებში, სტრიქონის ლიტერალებში, USE-ში. ეს მათი საფრთხეცაა: არასოდეს გაატარო არასანდო შეყვანა სკრიპტის ცვლადით, რადგან ეს სტრიქონების შეერთებაა ყოველგვარი escaping-ის გარეშე. იმავე სახელის გარემოს ცვლადებიც სკრიპტის ცვლადებად აიღება, რაც CI-დან მნიშვნელობების გადაცემის მოწესრიგებული გზაა.

exit კოდები არის ის, რაც sqlcmd-ს pipeline-ში უსაფრთხოს ხდის. ნაგულისხმევად sqlcmd შეცდომას იუწყება, აგრძელებს და 0-ით გადის. დაამატე -b და ის არანულოვანი კოდით გავა, როცა 11 ან მეტი სიმძიმის შეცდომა მოხდება, ამიტომ CI-ის ნაბიჯი ჩავარდება და გატეხილი მიგრაციის შემდეგ წარმატებას არ გამოაცხადებს. შეუთავსე ის :on error exit-ს თავად სკრიპტში და SET XACT_ABORT ON-ს ნებისმიერი ტრანზაქციის შიგნით, რომ შუა გზაზე ჩავარდნამ არც გააგრძელოს და არც ღია ტრანზაქცია დატოვოს.

$ sqlcmd -S "$DB_HOST,$DB_PORT" -U deployer -d appdb -C -b -i migrate.sql \    || { echo "migration failed"; exit 1; }

იგივე შაბლონი sqlcmd-ს განრიგის მკლავად აქცევს SQL Server Express-ზე, რომელსაც SQL Server Agent არ აქვს. ღამის BACKUP DATABASE ინსტრუქცია ფაილში, რომელსაც სხვა მანქანაზე cron-ი უშვებს -b-ით, რომ ჩავარდნები ჩანდეს, სრული backup job-ია; SQL Server backup და აღდგენა შეიცავს სკრიპტს და ნაწილებს, რომლებიც ყურადღებას საჭიროებს, მაგალითად სად ხვდება .bak. იგივე ეხება სტატისტიკის განახლებასა და ინდექსების მოვლას: ყველაფერი, რასაც T-SQL ტიპის Agent job-ის ნაბიჯში ჩადებდი, შეიძლება იყოს sqlcmd-ის გამოძახება ტაიმერზე.

თუ მიგრაციებს აპლიკაციის framework-იდან უშვებ, Entity Framework Core-ის მიგრაციები production-ში აჩვენებს, როგორ შექმნა idempotent სკრიპტი, რომლის გამოყენებაც ამავე ბრძანებას შეუძლია.

Query-ის შედეგების ფაილში ექსპორტი#

sqlcmd-ს შეუძლია შედეგების ნაკრების გამყოფიანი ტექსტით ჩაწერა, რაც სწრაფი ექსპორტისა და რეპორტებისთვის ნორმალურია:

$ sqlcmd -S db.example.net,14330 -U sa -d appdb -C -W -h -1 -s "," \    -Q "SET NOCOUNT ON; SELECT OrderId, CustomerId, Total FROM dbo.Orders" \    -o orders.csv

თითოეული ეს flag მიზეზით არის აქ, და რომელიმეს გამოტოვება ქმნის ფაილს, რომელიც თითქმის სწორად გამოიყურება:

• -W სვეტებიდან ბოლო ჰარეებს აჭრის, რის გარეშეც ყოველი მნიშვნელობა სვეტის სიგანემდე ივსება.
• -h -1 შლის სათაურის row-ს და მის ქვემოთ ტირეებიან ხაზს. გამოტოვე, თუ სათაურები გინდა - მაშინ ტირეებიანი ხაზი შენი ფაილის მეორე row იქნება.
• -s "," სვეტების გამყოფს აყენებს.
• SET NOCOUNT ON ბოლოში (1234 rows affected) ხაზს თრგუნავს.

რასაც sqlcmd არ აკეთებს, ეს მნიშვნელობების ბრჭყალებში ჩასმაა. სახელის შიგნით მძიმე იმ row-ის სვეტების განლაგებას არღვევს. თავისუფალი ტექსტის მქონე მონაცემებისთვის ან აირჩიე გამყოფი, რომელიც ვერ გაჩნდება (tab ან pipe სიმბოლო), ან query-დან JSON მიიღე FOR JSON PATH-ით, ან გამოიყენე bcp, რომელიც დიდ მოცულობაზე ბევრად სწრაფიცაა.

bcp: მასობრივი ექსპორტი და იმპორტი#

bcp ცხრილის მონაცემებს მასობრივად კითხულობს და წერს იმავე სწრაფი გზით, რომელსაც SQL Server bulk load-ებისთვის იყენებს. მას ოთხი რეჟიმი აქვს, მეორე არგუმენტად გადაცემული: out (მთელი ცხრილი), queryout (query-ის შედეგი), in (ფაილის ცხრილში ჩატვირთვა) და format (format ფაილის ჩაწერა).

# Export a table in native format: fastest, exact, SQL Server only$ bcp dbo.Orders out orders.dat -S db.example.net,14330 -U sa -d appdb -n -u# Export a query as comma-separated text$ bcp "SELECT OrderId, CustomerId, Total FROM appdb.dbo.Orders WHERE Status = 1" \    queryout paid.csv -S db.example.net,14330 -U sa -c -t, -u# Load the native file into another server's empty table$ bcp dbo.Orders in orders.dat -S other.example.net,14330 -U sa -d appdb \    -n -E -b 10000 -h "TABLOCK" -u

bcp პაროლს მოთხოვნით კითხულობს, თუ -P-ს გამოტოვებ, რაც ინტერაქტიულად უფრო უსაფრთხო არჩევანია. ფორმატის flag-ები წყვეტს, რას შეიცავს ფაილი:

და flag-ები, რომლებიც წყვეტს, როგორ იქცევა ჩატვირთვა:

ტექსტური ფაილების code page-ის დამუშავება განსხვავდება Windows-სა და bcp-ის Linux და macOS build-ებს შორის, ამიტომ თუ შენი მონაცემები -c რეჟიმში არა-ASCII ტექსტს შეიცავს, შეამოწმე -C ოფცია შენი პლატფორმის დოკუმენტაციაში, ან გამოიყენე -w და ეს კითხვა თავიდან აიცილე.

როცა ფაილის სვეტები ცხრილს ერთი-ერთზე არ ემთხვევა, format ფაილი მათ ერთმანეთს უთავსებს. დააგენერირე ის ცხრილიდან და დაარედაქტირე:

$ bcp dbo.Orders format nul -c -t, -f orders.fmt -S db.example.net,14330 -U sa -d appdb -u

-x-ის დამატება XML ვარიანტს წერს, რომელიც უფრო ადვილად იკითხება. ფაილი გადაეცი -f orders.fmt-ით in ან out ბრძანებას.

სწრაფი ჩატვირთვა და ალტერნატივები#

bulk load ყველაზე სწრაფია, როცა მინიმალურად ილოგება: ცხრილი დაბლოკილია (-h "TABLOCK"), ბაზა simple ან bulk-logged recovery model-შია, სამიზნე კი heap-ია ან ცარიელი ცხრილი. ამ პირობებში SQL Server ლოგავს გვერდების გამოყოფას და არა ყოველ row-ს, რაც რამდენჯერმე სწრაფია და transaction log-ს პატარას ტოვებს. SQL Server-ის recovery model-ები და log-ის ზრდა ხსნის, რატომ შეუძლია full recovery-ში დიდ ჩატვირთვას log-ის მონაცემების ზომით გაზრდა.

რამდენიმე nonclustered ინდექსიან ცხრილში დიდი ერთჯერადი ჩატვირთვისთვის ხშირად უფრო სწრაფია მათი წაშლა ან გამორთვა, ჩატვირთვა და ხელახლა აგება, ვიდრე row-row მათი შენარჩუნება. Check constraint-ები და foreign key-ები bcp-ის ჩატვირთვისას ნაგულისხმევად არ მოწმდება და შემდეგ untrusted-ად აღინიშნება; დაამატე -h "CHECK_CONSTRAINTS", თუ მათი დაცვა გჭირდება, ან შემდეგ ხელახლა შეამოწმე ALTER TABLE ... WITH CHECK CHECK CONSTRAINT ALL-ით.

სერვერის მხარის ალტერნატივებია BULK INSERT და OPENROWSET(BULK ...), რომლებიც ფაილს ბაზის სერვერის საკუთარი დისკიდან კითხულობენ და SQL Server 2017-დან CSV-ს სწორად იგებენ FORMAT = 'CSV'-ით და FIELDQUOTE-ით. მათ ფაილი სერვერზე სჭირდებათ, Linux-ზე კი sysadmin, რადგან bulkadmin როლი იქ მხარდაჭერილი არ არის. აპლიკაციის კოდისთვის ყველა დრაივერს აქვს bulk-copy API - SqlBulkCopy .NET-ში, fast_executemany pyodbc-ში - რომელიც იმავე პროტოკოლს იყენებს, რასაც bcp, დროებითი ფაილის გარეშე. მთელი ბაზის გადატანა რამდენიმე ცხრილის ნაცვლად ჯობს backup-ით ან BACPAC-ით გააკეთო; ბაზის მიგრაცია SQL Server ჰოსტინგზე ვარიანტებს ადარებს.

პრობლემების მოგვარება#

SSL Provider: certificate chain was issued by an authority that is not trusted. version 18-ის დაშიფვრის ნაგულისხმევი. დაამატე -C sqlcmd-ს ან -u bcp-ს, ან სერვერზე სანდო სერტიფიკატი დააყენე.

Named Pipes Provider: Could not open a connection. კლიენტი სერვერამდე ვერ მივიდა. შეამოწმე ჰოსტი, პორტი, მათ შორის მძიმე და ნებისმიერი firewall გზაზე.

Login failed for user. არასწორი პაროლი, login-ი, რომელიც არ არსებობს, ან ნაგულისხმევი ბაზა, რომელსაც login-ი ვერ ხსნის. დაამატე -d master, რომ login-ი ცალკე შეამოწმო.

Invalid object name bcp-ში. ცხრილს ბაზის სახელი დაუმატე (appdb.dbo.Orders) ან გადაეცი -d. queryout რეჟიმში ყოველთვის სამნაწილიანი სახელები გამოიყენე.

Unexpected EOF encountered in BCP data-file. ბრძანების დამასრულებლები ფაილს არ ემთხვევა, ხშირად Windows-ის \r\n ხაზის დაბოლოებები, ჩატვირთული -r \n-ით. გამოიყენე -r 0x0a ან ფაილი გადააკონვერტირე.

String or binary data would be truncated. ფაილში მნიშვნელობა სვეტზე გრძელია. SQL Server 2019 და შემდეგი ვერსიები შეტყობინებაში სვეტსაც და მნიშვნელობასაც ასახელებენ; სვეტი გააფართოვე ან მონაცემები გაასუფთავე.

FAQ#

ხელმისაწვდომია sqlcmd Linux-სა და macOS-ზე?

დიახ. Microsoft აქვეყნებს mssql-tools18-ს, რომელიც sqlcmd-ს და bcp-ს შეიცავს, ძირითადი Linux დისტრიბუციებისა და macOS-ისთვის, go-sqlcmd კი Homebrew-ით ყენდება. ორივე უკავშირდება ნებისმიერ SQL Server-ს ნებისმიერ პლატფორმაზე.

როგორ შევაჩერო sqlcmd, რომ შეცდომის შემდეგ არ გააგრძელოს?

ბრძანების ხაზზე გამოიყენე -b, რომ არანულოვანი კოდით გავიდეს, და სკრიპტის თავში ჩასვი :on error exit, რომ პირველივე ჩავარდნილ batch-ზე გაჩერდეს. ტრანზაქციების შიგნით დაამატე SET XACT_ABORT ON, რომ ტრანზაქცია უკან დაბრუნდეს და ღია არ დარჩეს.

რა არის ორ SQL Server-ს შორის ერთი ცხრილის კოპირების ყველაზე სწრაფი გზა?

bcp out native ფორმატში (-n) წყაროდან, შემდეგ bcp in -h "TABLOCK"-ით და batch-ის ზომით სამიზნეზე ცარიელ ცხრილში. Native ფორმატი ტექსტის ყოველგვარ parsing-ს ტოვებს, ცხრილის lock კი მინიმალურ ლოგირებას უშვებს.

შეუძლია bcp-ს სვეტების სათაურების ექსპორტი?

პირდაპირ არა. ჩვეული გამოსავალია queryout სათაურის row-ის UNION ALL-ით, ტექსტად გარდაქმნილი, ან ექსპორტი sqlcmd-ით, რომელიც სათაურებს ნაგულისხმევად წერს. ცხრილების პროგრამაში გადასატანი მონაცემებისთვის ორივე ნორმალურია.

რატომ ვარდება sqlcmd-ში ჩემი პაროლი სპეციალური სიმბოლოებით?

shell-ი $, ! ან &-ის მსგავს სიმბოლოებს ინტერპრეტაციას უკეთებს, სანამ sqlcmd მათ დაინახავს. SQLCMDPASSWORD-ის export-ისას პაროლი ერთმაგ ბრჭყალებში ჩასვი, ან ფაილიდან წაიკითხე, და -P-ით ნუ გადასცემ.