Làm việc với dữ liệu bằng công cụ BCP có trên SQL Server

Amazakuten tháng 11 21, 2021 SQL server

Lâu rồi mới có dịp làm việc với SQL Server lại, đang làm một một dự án liên quan đến vận chuyển và có liên quan đến một lượng khủng data từ SQL Server. Hiện tại, mỗi lần có thành viên mới vào dự án thì việc setup môi trường SQL để có đầy đủ data và đồng nhất hoàn toàn với môi trường product khá mất thời gian.

Bài toán import và export nhiều dữ liệu

Vì data được sử dụng qua rất nhiều năm và được input từ nhiều hệ thống khác từ bên ngoài vào nên mỗi table chứ khá nhiều dữ liệu ( có table đến vài chục GB dữ liệu). Nghiên cứu một hồi thì cũng có giải phát đó là sử dụng BCP của SQL Server để xử lý.

Tìm hiểu một chút về BCP

Đây là một bộ công cụ commandline được cung cấp bởi SQL Server nên có tính tương thích cao khi tương tác với SQL Server. Các chức năng hỗ trợ của BCP:

Export data từ table của SQL Server sang data dưới dạng file. (csv, tsv, text đều được..)
Export data từ câu query của SQL Server sang data dưới dạng file (csv, tsv, text đều được..)
Import hàng loạt data từ file đã export trước đó vào lại table của SQL Server.
Ngoài ra, bạn có thể linh động trong việc định dạng file ( xuất định dạng file text, csv... hoặc một định dạng nào mà bạn thấy phù hợp nhất...)

Tham khảo: https://docs.microsoft.com/ja-jp/sql/tools/bcp-utility?view=sql-server-ver15

Chi tiết về cách sử dụng BCP

Khi sử dụng BCP bạn cần hiểu rõ các thông tin trong command line bên dưới nhé. Mình sẽ list chi tiết ra từng loại.

in data_file: in ý nghĩa là bạn muốn import file, data_file là file mà bạn muốn import vào SQL Server.
out data_file: out ý nghĩa là bạn muốn output file dưới dạng một table chỉ định, data_file là file mà bạn muốn export vào SQL Server.
queryout data_file: queryout ý nghĩa là bạn muốn output file dưới dạng một câu query, data_file là file mà bạn muốn export vào SQL Server.
-c: kiểu character khi thao tác. Cái này mình cũng không rõ lắm vì cũng ít khi sử dụng.
-t: truy cập theo phương thức Trusted connection (Windows Authentication).
-u xxx -p xxxx: nếu có sử dụng password và user khi truy cập vào SQL Server.
-S: servername của bạn (FREEPROGRAMMINGCOUPON\SQLEXPRESS)
-t, – Định nghĩa dấu ngăn cách trong file output, kiểu CSV mà bạn muốn ngăn mỗi field là một dấu .
-b1000 – Export the data in batches of 1000 rows, có thể điều chỉnh được 5000 hoặc 10000 row nhé, nhưng chỉ cho import, còn output thì mình thử chỉ có 1000 row theo từng block.

Bên trên là các câu lệnh thường hay dùng nhất, ngoài ra nếu muốn xem thêm thông tin các câu lệnh khác thì bạn có thể type trên commandline bcp là sẽ có một danh sách cho các bạn tìm hiểu thêm. Nếu ra được nội dung như hình bên dưới thì tool bcp bạn có thể sử dụng được rồi.

Nếu kỹ hơn nữa, bạn có thể kiểm tra theo đường dẫn bên dưới xem có tồn tại file bcp không nhé. ( Tùy theo máy và phiên bản SQL Server sẽ có đường dẫn khác nhau nhé.)

C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\bcp.exe

Ví dụ thực tế cho dễ hiểu về sử dụng BCP

Chắc chắn nói như trên thì các bạn sẽ rất khó hiểu, bản thân mình khi tìm hiểu cũng khó hiểu nếu không làm thực tế.

Case 1: Export table xxx ra file yyy.txt từ SQL Server với Server Name và phương thức truy cập là Windows Authentication

bcp master.[dbo].[category] out E:\output.txt -c -T -S FREEPROGRAMMINGCOUPON\SQLEXPRESS

Do khá mất thời gian để giải thích chi tiết nên mình chỉ giải thích cụ thể case số 1 thôi nhé. Case 2 và case 3 các bạn áp dụng tương tự là được.

Case 2: Export câu lệnh select * from table xxx ra file yyy.txt SQL Server với Server Name và phương thức truy cập là Windows Authentication

bcp "select * from category" out E:\output.txt -c -T -S FREEPROGRAMMINGCOUPON\SQLEXPRESS

Case 3: Import câu lệnh select * from table xxx ra file yyy.txt SQL Server với Server Name và phương thức truy cập là Windows Authentication

bcp master.[dbo].[category] in E:\input.txt -c -T -S FREEPROGRAMMINGCOUPON\SQLEXPRESS

input.txt file chính là output file mà mình export ra từ case số 1 và case số 2 cho các bạn dễ hiểu.

Với các làm trên, 1 table với khoảng 150k record vẫn khá ổn, mình chưa test hết thử xem mất bao lâu. Ngoài ra, cơ chế của bcp là nó sẽ chia ra từng block để export/ import, mỗi lần là 1000 record.

Giải quyết tiếp bài toán nhiều table

Giờ DB có tận 50 table thì làm 50 lần tính ra cũng ok mà với góc nhìn của dân kỹ thuật thì chạy cơm như vậy khá là chuối. Thôi làm tiếp các Powershell hoặc BatchCommand để chạy hàng loạt đỡ tốn "cơm" hơn. Giải pháp hiện tại mình của mình có một đoạn hơi thủ công một chút đó là đoạn copy và tạo file batch để chạy.

1. Lấy tất cả table của tất cả database ra, rồi sau đó tạo một danh sách các câu lệch bcp như ở trên mình ví dụ.