bcp-sql-server-tools
bcp-sql-server-tools

วันนี้จะเป็นบทความสั้นๆเกี่ยวกับการใช้ tools ตัวนึงของ Microsoft ชื่อ

Bulk Copy Program (BCP)

เจ้า BCP นี้ต้องบอกเลยผมก็ไม่เคยได้ยินจนกระทั่งเมื่อไม่นานมานี้เอง เพราะว่ามีโจทย์ที่ท้าทายสําหรับการทํา Test Enviroment ให้ stable มากขึ้นหน่อย

ปัญหาที่เจอในงานนี้ก็คือ เราเกิด Flaky Test ค่อนข้างบ่อยเลย 🙂 เนื่องจาก QA enviroment เป็นระบบที่แชร์กันทั้งองค์กร และการแก้ไขอย่างนึงก็จะกระทบอีกอย่างนึงเป็นทอดๆไป ซึ่งจะทําให้เราเสียเวลาในการทํางานค่อนข้างมาก ในการแก้กับ Falky Test (นั้นเลยเป็นเหตุผลที่สําหรับ QA แล้ว Docker เองก็สําคัญไม่แพ้กันเลยน่ะ อ่าน Docker 101 ได้ที่นี้ เดี๋ยวไม่นานจะมี Version Docker in action ออกมาน่ะ เพื่อให้รู้ว่าสําหรับ QA แล้ว Docker เองก็สําคัญยิ่งชีพ)

BCP คืออะไร? ต้องใช้แต่ตัวนี้หรอ?

sql-server-logo
sql-server-logo

กลับมาที่ BCP เจ้า BCP เป็น tools ของ Microsoft ที่เค้าออกมาเพื่อการจัดการ copies data จอง Microsoft SQL server เลย เพราะว่าในชีวิตจริง Data ที่อยู่ใน MSSQL มันไม่ได้มีแค่ 100-200 records มันมีมากมายเป็นหมื่น เป็นแสน records เลยล่ะ ซึ่งจริงๆแล้วการ Export มันก็ทําได้หลายวิธี อ่านจากที่นี้ได้

แต่ขอตัดวิธีที่ใช้ UI ออก แล้วมาพูดถึง 2 ตัวน่ะนั้นก็คือ

  1. Programming + MSSQL client ซักตัว แล้วต่อเข้า Database แล้ว Write Files ออกมาจาก Table โดยการ Query
    • ข้อดี ค่อนข้างง่ายต่อการทํา ไม่ติดปัญหาอะไรเยอะ เพราะเขียน client ดึงได้เลย
    • ข้อเสีย ไอ้ตอน Export Files ออกมาในรูปแบบ CSV หรือ XML หรืออะไรก็ตามอ่ะ ถึงมันจะช้าและกิน Performance มาก (นอกจากนั้นยังมีเรื่องของ Bit Values ที่ดึงออกมาจาก SQLreader แล้วมันแสดงผลเป็น True/False แทนที่จะเป็น 0 หรือ 1 ด้วย ซึ่งต้องมาแปลงเองอีกทีน่ะ) มันก็ไม่ลําบากใจเท่าตอน Import กลับไปหรอก….. อย่าลืมเรื่อง Constraints ของแต่ละ Table ในการ Import เชียวล่ะ เช่น Table A ห้าม Update Field นี้ เราก็ต้องเขียนกันไว้ Table B ห้ามทำอย่างนี้ เราก็ต้องเขียนไว้ ทีนี้มี 10-20 Tables code เราก็เละล่ะครับ Maintain กันตาเหลือกเลย
  2. BCP พระเอกของวันนี้
    • ข้อดี มันเป็น tools ที่ออกแบบมาเพื่อการ Import และ Export เลย ซึ่ง Performance ของมันนี้เรียกได้ว่าโคตรน่าประทับใจเลยล่ะ มันสามารถจัดการกับ data file ที่มีจํานวน 2^63 rows ได้สบายๆ (หรือประมาณล้านๆกว่าแถวอ่ะ)
    • ข้อเสีย Export ไม่เท่าไร มันมีการรองรับ Query ไว้ให้ ถ้าเราเขียน Scripts ครอบคลุมมันแล้ว Iterate มันเรื่อยๆก็ชิวดี แต่…..Import นี้สิ! 🙁 มันไม่มีการ UPDATE สิ่งที่มันมีคือ SELECT & INSERT เท่านั้น! แปลว่าเราเอาไปใช้งานกับ Table ที่มี Data อยู่แล้วไม่ได้ เพราะมันจะไม่สนใจว่ามันมี records นี้ๆอยู่มั้ย มันจะ INSERT อย่างเดียวเลย ปวดใจ 🙁

สรุปแล้วทั้งสองวิธีมีความยืดหยุ่นต่างกันน่ะ แบบ 1 มันก็ดีแหละ ไม่มี limit ชีวิตเกินร้อย อยากทําไรทํา แต่ Performance แย่หน่อยน่ะ กับ มี condition เยอะมาก ส่วนแบบ 2 นี้มี limit แต่ Performance สุดยอดเลย เพราะฉะนั้นต้องหาทางดัดแปลงเอา

วิธีการใช้ BCP

ไม่เขียนอธิบายทฤษฐีหรือแต่ละ Parameter น่ะ สามารถหาอ่านได้ที่นี้ แต่อยากให้ดูคําสั่งที่ใช้เลย

วิธีการ export


bcp "select * from mycompany.dbo.employee" queryout "c:\temp\emp.txt" -c -t"%$&\",\"%$&" -r"\"\n\"", -S "127.0.0.1" -d "mycompany" -U "mart" -P "mart123" -q -w -a 65353

ทีนี้ เจ้านี้คือตัวอย่างของการ Export data จาก SQL server โดยดึงข้อมูลของพนักงานทั้งหมดออกมา แล้วไปใส่ไว้ที่ emp.txt ใน folder temp ไดรฟ์ C: ถ้าสังเกตุดีๆจะเห็นว่ามี parameters มากมายเลย ซึ่งพวกนี้คือสิ่งที่จําเป็นสําหรับเรา ได้แก่

  • -c = จะทํางานให้ออกมาเป็น Character Type ทั้งหมด หรือ อีกความหมายก็คือ มันแปลงข้อมูลให้เป็น character data ให้หมดเพื่อเวลาที่เราเอาไป Import database อื่นจะไม่มีปัญหา เพราะมัน convert character data to native ทั้งหมด

    bulk-character-format
    bulk-character-format
  • -t = ตัวเด็ดเลย field terminator เอา -t มาใช้ในการ terminate แต่ละ field เช่น field เป็น “,” หมายถึง แต่ละ field จะแบ่งด้วย “,” comma นั้นเอง
  • -r = row terminator เราจะหยุด row ยังไง โดยปกติก็ใช้ new line เพื่อขึ้นบรรทัดใหม่นั้นแหละ
    ดังนั้นถ้าtab,newline,backslash แต่ที่เด็ดคือเวลา import กลับเข้าไป มันจะแปลงให้หมดเลย เป็นเหมือนเดิมทุกอย่าง ถ้าเราใช้ terminate เดียวกันกับ export
    สำหรับ -r กับ -t อ่านรายละเอียดเพิ่มได้ที่นี้
  • -S = server ที่เราจะต่อเข้าไป
  • -d = database name
  • -U = username
  • -P = password
  • -q = เพื่อให้มันรับรู้ว่าเราจะใช้ Single quotation mark เวลาทํางาน
  • -w = ใช้ Unicode characters ในการจัดการ export & import จะได้จัดการพวกตัวอักษรแปลกๆได้นั้นแหละ

    bcp-unicode-parameter
    bcp-unicode-parameter (https://docs.microsoft.com/en-us/sql/relational-databases/import-export/use-unicode-character-format-to-import-or-export-data-sql-server)
  • -a = packet_size ระบุจํานวนชอง bytes ต่อ Packet ที่จะใช้กับ sql server ถ้าไม่ระบุมันจะให้มาจํานวนแล้วมีปัญหา performance ให้ใช้ 65535 มากสุด

สําหรับ Import

bcp mycompany.dbo.employee in "c:\temp\emp.txt" -c -t"%$&\",\"%$&" -r"\"\n\"", -S "127.0.0.1" -d "mycompany" -U "mart" -P "mart123" -q -w -a 65353

เจ้า Import ไม่แตกต่างไรมาก แค่เปลี่ยนจาก queryout เป็น in เท่านั้นแหละ ก็ทํางานได้เลย จริงๆหลักการมันมีแค่

  • out = export แบบทั้ง table
  • in = import
  • queryout  = export แบบใช้ query

ซึ่งหลักๆก็มีแค่นี้แหละ ไม่ว่าจะใช้งานไหน แค่นี้ก็พอแล้วล่ะหรือจะหาใส่เพิ่มเองก็ได้น่ะ 🙂

สรุปแล้ว…

ใช้ bcp เถอะในการ export และ import แต่อาจจะต้องประยุคหน่อยน่ะ เพราะไม่มี update แปลว่าถ้าเราไปใช้กับ table ที่มีข้อมูลอยู่แล้ว เราอาจจะต้อง backup data แล้ว DELETE table นั้นออกทั้งหมดก่อน แล้วค่อยเอาข้อมูลใส่กลับเข้าไป เพื่อที่จะไม่ได้สร้าง rows ซำ้นั้นเองครับ 🙂

หรือทางที่ดีที่สุด สร้าง DOCKER enviroment แยกออกมาเพื่อใช้งาน แล้วก็เล่นรัวๆๆได้เลย ใช้ DOCKER กันเถอะเหล่า QA มันจะช่วยให้การทํา test enviroment สบายขึ้นอีกเยอะเลย

Leave a Reply

avatar

This site uses Akismet to reduce spam. Learn how your comment data is processed.