Bài giảng Kiến trúc cài đặt cơ sở dữ liệu - Chương 2: Nhập xuất dữ liệu (Exporting and importing data)

GV Phi Loan - Khoa CNTT – HUI

Nội dung

• Lệnh BCP • Lệnh BULK INSERT • Import And Export Wizard • Project SSIS

GV Phi Loan - Khoa CNTT – HUI 2

Nhập xuất dữ liệu

• SQL Server cung cấp nhiều tools thực

hiện việc copy hàng loạt hay nhập/xuất dữ liệu. – Lệnh BCP – Lệnh BULK INSERT – Dùng lệnh SELECT INTO – Dùng chức năng Import And Export Wizard – Project SSIS

GV Phi Loan - Khoa CNTT – HUI 3

BCP (Bulk Copy Program)

• Là 1 tiện ích (utility) mà người dùng có thể

thực hiện từ dấu nhắc lệnh.

• Được dùng để copy dữ liệu từ SQL server

thành 1 file của hệ điều hành hay ngược lại. • Rất thường hay dùng để chuyển 1 lượng lớn dữ liệu vào các bảng của SQL server từ các chương trình khác như từ các DBMS khác

GV Phi Loan - Khoa CNTT – HUI 4

BCP (Bulk Copy Program)

GV Phi Loan - Khoa CNTT – HUI 5

Tiện ích BCP

• Dữ liệu chuyển từ nguồn bên ngoài vào

database của SQL server được gọi là BCP IN (còn được gọi là importing)

• Nếu dữ liệu được chuyển từ SQL Server ra nguồn bên ngoài được gọi là BCP OUT (còn được gọi là exporting). Nguồn ngoài phải là file văn bản (flat file).

GV Phi Loan - Khoa CNTT – HUI 6

Tiện ích BCP

• Để nhập dữ liệu từ ngoài vào 1 bảng:

– Dùng file định dạng sẵn cho bảng đó – Hiểu rõ cấu trúc của bảng và kiểu dữ liệu

cho các cột của bảng.

GV Phi Loan - Khoa CNTT – HUI 7

Các yêu cầu khi thực hiện BCP

• File dữ liệu chứa dữ liệu được nhập vào

cần phải có dạng row/column.

• File dữ liệu nên có ký hiệu kết thúc hàng (row terminator) và kết thúc cột (column terminator) sao cho SQL Server có thể nhận biết được khi truyền dữ liệu.

• Cấu trúc của file dữ liệu và bảng của SQL

Server cần phải đồng nhất.

GV Phi Loan - Khoa CNTT – HUI 8

Cú pháp lệnh BCP

BCP [[database_name.][owner].]table_name {in|out|queryout|format} data_file [-m max_errors] [-f format_file] [-x] [-e err_file] [-F first_row] [-L last_row][-b batch_size] [-n] [-c] [-w] [-N] [-t field_term] [-r row_term] [-i input_file] [-o output_file] [-a packet_size] [-S server_name] [-U login_id] [-P password] [-T]

GV Phi Loan - Khoa CNTT – HUI 9

Cú pháp BCP

• in|out|queryout|format là hướng của lệnh.

– In: copy dữ liệu từ 1 file dữ liệu vào bảng của

CSDL.

– Out: copy dữ liệu từ bảng CSDL vào 1 file dữ liệu. – Queryout: copy dữ liệu từ 1 truy vấn – Format: tạo 1 file định dạng phụ thuộc vào các tùy

chọn (-n, -c, -w, or -N) và k{ hiệu phân cách (delimiters) của bảng. Khi lệnh bcp được thực hiện, dữ liệu sẽ lưu vào file theo định dạng này. Tùy chọn này phải đi kèm cùng với tùy chọn –f

GV Phi Loan - Khoa CNTT – HUI 10

Cú pháp BCP

 data_file là đường dẫn đầy đủ của file dữ

liệu, có thể chứa từ 1 đến 255 ký tự.

 -m max_errors: là số lỗi cực đại có thể xảy ra trong khi thực thi lệnh. Mỗi hàng không thể copy bởi lệnh được tính là 1 lỗi. Mặc định là 10 lỗi.

GV Phi Loan - Khoa CNTT – HUI 11

Cú pháp BCP

• -f format_file : dùng để xác định đường dẫn

đầy đủ của file định dạng. – Nếu -f được dùng cùng với tùy chọn

format, thì format_file được tạo riêng cho bảng hay view. Để tạo file định dạng XML, thì phải xác định thêm tùy chọn –x .

– Nếu –f được dùng với tùy chọn in hay out, -

f yêu cầu file định dạng phải có sẵn.

GV Phi Loan - Khoa CNTT – HUI 12

Cú pháp BCP

 -e err_file là đường dẫn đầy đủ của file lỗi (error

file). File này được dùng để lưu trữ các hàng mà lệnh không thể copy được từ file vào CSDL. Nếu không có tuz chọn này, file lỗi không thể tạo được.

 -b batch_size là số hàng trên 1 batch dữ liệu sẽ được copy. Trong trường hợp bị lỗi, SQL Server sẽ commit hay rolls back giao dịch (transaction) theo từng batch dữ liệu. Mặc định tất cả dữ liệu trong file dữ liệu xác định sẽ được copy thành 1 batch.

GV Phi Loan - Khoa CNTT – HUI 13

Cú pháp BCP

• -F first_row : xác định vị trí hàng đầu tiên

được xuất từ bảng hay nhập vào bảng từ file bên ngoài. Mặc định là hàng đầu tiên của file hay bảng là 1.

• -L last_row : xác định vị trí hàng cuối cùng cần

sao chép.

• first_row, last_row có thể là 1 số nguyên

dương có thể lên đến 2^63-1.

GV Phi Loan - Khoa CNTT – HUI 14

Cú pháp BCP

• -n (native) copy các kiểu dữ liệu gốc của chính

database.

• -c dùng kiểu dữ liệu character. Mỗi trường được lưu dưới dạng char không dùng prefixes , với \t như k{ hiệu phân cách trường, và \r\n (newline character) như k{ hiệu kết thúc hàng.

• -N dùng kiểu dữ liệu gốc cho các dữ liệu không phải là character và dùng kiểu Unicode cho dữ liệu là character.

GV Phi Loan - Khoa CNTT – HUI 15

Cú pháp BCP • -w dùng kiểu k{ tự Unicode (nchar), không prefixes, với \t (tab character) như k{ hiệu phân cách trường (field separator), và \n (newline character) như k{ hiệu kết thúc dòng.

 -t field_term : xác định ký tự kết thúc cột (field

terminator). Mặc định là \t.

 -r row_term: xác định ký tự kết thúc hàng (row

terminator). Mặc định là \n.

 -i input_file: xác định tên của file ngõ vào.  -o output_file: xác định tên của file ngõ ra.

GV Phi Loan - Khoa CNTT – HUI 16

Cú pháp BCP

 -a packet_size: xác định số byte cho 1 gói tin

trên mạng (network packet), có thể có từ 4096 đến 65535 bytes; Mặc định là 4096.

 Kích cỡ gói tin càng lớn thì càng tăng khả năng thực thi của lệnh. Nếu gói tin quá lớn không thể tạo được thì kích cỡ mặc định sẽ được dùng.

GV Phi Loan - Khoa CNTT – HUI 17

Cú pháp BCP

 -S server_name[\instance_name]: xác định điển hình của SQL Server được nối đến. Nếu không dùng tùy chọn này, lệnh bcp kết nối đến điển hình mặc định của SQL Server trên máy tính mặc định. Tùy chọn này được yêu cầu khi thực thi lệnh bcp từ 1 máy tính ở xa trên mạng.

 -U login_id : xác định login ID được dùng để kết

nối tới SQL Server.

GV Phi Loan - Khoa CNTT – HUI 18

Cú pháp BCP

 -P password: xác định passowrd cho login ID.

Nếu không dùng tùy chọn này, bcp sẽ hỏi password khi chạy. Nếu dùng tùy chọn này mà không xác định password thì bcp sẽ dùng password mặc định.

 -T: kết nối SQL server bằng kết nối tin cậy

(trusted connection) của người dùng mạng, không cần dùng –U và -P

GV Phi Loan - Khoa CNTT – HUI 19

Ví dụ

• bcp toyworld.dbo.Toys out “Toy type.txt" -T –

Chép dữ liệu từ bảng Toys vào tập tin dạng

text (kết nối tự động –T)

• bcp toyworld.dbo.Toys out “Toy.txt" –Usa –

SPHILOAN-PC -P –c

Chép dữ liệu từ bảng Toys vào tập tin dạng text thông qua kết nối vào SQL server thông qua user sa

GV Phi Loan - Khoa CNTT – HUI 20

Ví dụ

• USE toyworld SELECT * INTO toy2 from Toys where 1=2 bcp toyworld.dbo.toy2 in toy.txt –T –c Chép dữ liệu từ file toy.txt bên ngoài vào bảng

toy2

• Bcp “SELECT vToyName FROM toyworld.dbo.

toys” queryout “ToyName.txt” –T –c

 Chép cột vToyName ra thành file text bên ngoài

GV Phi Loan - Khoa CNTT – HUI 21

Lệnh BULK INSERT

• Được dùng để chuyển hàng loạt dữ liệu từ 1

file bên ngoài vào bảng SQL server

• Lệnh BULK INSERT có thể được thực thi từ

dấu nhắc lệnh hay từ query analyzer.

• Có thể dùng lệnh BULK INSERT để chèn dữ liệu từ 1 hay nhiều nguồn vào bảng SQL Server.

GV Phi Loan - Khoa CNTT – HUI 22

Cú pháp lệnh BULK INSERT

BULK INSERT [ [ 'database_name'.] [ 'owner' ].] {

'table_name' FROM 'data_file' }

[ WITH ( [ BATCHSIZE [ = batch_size ] ] [ [ , ] CHECK_CONSTRAINTS ] [ [ , ] DATAFILETYPE [ = { 'char' | 'native'| 'widechar' | 'widenative' } ] ] [ [ , ] FIELDTERMINATOR [ = 'field_terminator' ] ] [ [ , ] FIRSTROW [ = first_row ] ] [ [ , ] LASTROW [ = last_row ] ] [ [ , ] MAXERRORS [ = max_errors ] ] [ [ , ] ROWTERMINATOR [ = 'row_terminator' ] ]

)

]

GV Phi Loan - Khoa CNTT – HUI 23

Cú pháp lệnh BULK INSERT

• BATCHSIZE [ = batch_size ] : xác định số hàng trong 1 batch. Mỗi batch được copy vào server như 1 transaction.

• CHECK_CONSTRAINTS: yêu cầu bất kz

constraint trong bảng phải được kiểm tra trong lúc thực thi lệnh. Mặc định lệnh bỏ qua các constraint cua bảng.

GV Phi Loan - Khoa CNTT – HUI 24

Cú pháp lệnh BULK INSERT

• DATAFILETYPE [ = {'char' | 'native' | 'widechar'

| 'widenative' } ]: – Char: file dữ liệu kiểu ký tự – Native: sử dụng kiểu dữ liệu hiện có của

database

– Widechar: file dữ liệu kiểu ký tự Unicode – Widenative: tương tự như native, ngoại trừ các cột char, varchar và text sẽ được lưu trữ theo kiểu Unicode

GV Phi Loan - Khoa CNTT – HUI 25

Cú pháp lệnh BULK INSERT

 FIELDTERMINATOR [ = 'field_terminator' ] : xác định ký tự kết thúc trường (field terminator) cho các file dữ liệu kiểu char và widechar. Mặc định là \t.

 FIRSTROW [ = first_row ]: xác định số của hàng

đầu tiên được copy. Mặc định là 1.

 LASTROW [ = last_row ]: xác định số của hàng cuối cùng được copy. Mặc định là 0 để chỉ hàng cuối cùng của file dữ liệu.

GV Phi Loan - Khoa CNTT – HUI 26

Cú pháp lệnh BULK INSERT

 MAXERRORS [ = max_errors ] : xác định số lỗi cực đại có thể xảy ra trước khi lệnh bulk copy bị hủy bỏ. Mặc định là 10.

 ROWTERMINATOR [ = 'row_terminator' ]: xác định ký tự kết thúc hàng dùng cho file dữ liệu kiểu char và widechar . Mặc định là \n

GV Phi Loan - Khoa CNTT – HUI 27

Ví dụ

BULK INSERT toyworld.dbo.toy2 FROM ‘C:\Users\Phi Loan\toy.txt' WITH ( FIELDTERMINATOR = ‘\t', ROWTERMINATOR =

'\n' )

GV Phi Loan - Khoa CNTT – HUI 28

SSIS package

• Package là đối tượng cốt lõi trong SQL Server Integration Services (SSIS) chứa business logic để quản l{ trình tự xử l{ dữ liệu.

• SSIS dùng để chuyển dữ liệu từ nguồn đến đích

và quản l{ trình tự xử l{ dữ liệu. • Có thể tạo package SSIS theo 2 cách:

– Dùng lệnh SQL Server Import And Export Wizard trong SQL Server Management Studio (SSMS) – Dùng SSIS Designer trong Business Intelligence

Development Studio (BIDS).

GV Phi Loan - Khoa CNTT – HUI 29

SSIS package

• Cho phép chuyển đổi dữ liệu đi và đến từ các

nguồn sau: – NET Framework Data Provider for SQLServer – Flat File Source – Microsoft Access – Microsoft Excel – Microsoft OLE DB Provider for Analysis Services – Microsoft OLE DB Provider for Oracle – Microsoft OLE DB Provider for SQL Server – SQL Server Native Client

GV Phi Loan - Khoa CNTT – HUI 30

Trình tự thực hiện Import and Export Wizard

• Chọn Data Source • Chọn Destination • Chọn dữ liệu cần Import hay Export • Chọn cách thực hiện: trực tiếp hay lưu

lại như SSIS package

GV Phi Loan - Khoa CNTT – HUI 31

Project SSIS trong BIDS

• Package SSIS chứa:

– Business logic: bao gồm các nhiệm vụ như trích dữ liệu, thao tác và biến đổi dữ liệu trước khi chuyển đổi dữ liệu.

– Workflow: chạy 1 thủ tục (stored procedure), chuyển 1 file từ FPT server đến thư mục xác định trên server, hay gửi email khi có lỗi…

– Các kết nối với nguồn dữ liệu (data source) và

đích dữ liệu (data destination)

GV Phi Loan - Khoa CNTT – HUI 32

Khi nào nên dùng SSIS trong BIDS

• Khi package SSIS có nhiều yêu cầu phức

tạp

• Cần kết hợp nhiều package SSIS lại.

GV Phi Loan - Khoa CNTT – HUI 33

GV Phi Loan - Khoa CNTT – HUI

Trình tự tạo 1 SSIS

• Tạo Data Source ( tùy chọn) • Tạo kết nối • Tạo các đối tượng Control Flow

GV Phi Loan - Khoa CNTT – HUI 35

Tạo Data Source

• Chứa các chuỗi kết nối trỏ đến file hay

CSDL.

• Là 1 đồi tượng tùy chọn của dự án SSIS,

được dùng khi: – Một số package cần dùng chung 1 CSDL hay

1 kết nối file.

– Cần thay đổi kết nối được dùng trong nhiều

package.

GV Phi Loan - Khoa CNTT – HUI 36

Package Connection Managers

• Connection manager cho phép tạo các loại kết nối khác nhau cho package. – Các thành phần khác nhau trong SSIS có thể kêt nối với đối tượng bên ngoài package (như database, file, hay server)

GV Phi Loan - Khoa CNTT – HUI 37

Tạo kết nối

• Chọn loại kết nối:

– New Connection ( kết nối độc lập) – New Connection From Data Source – New File connection – New Analysis Services Connection – New ADO.NET Connection – New Flat File Connection

GV Phi Loan - Khoa CNTT – HUI 38

Thiết kế package trong SSIS

• Màn hình thiết kế package chứa 4 tab cho phép thực hiện các phần việc khác nhau.

GV Phi Loan - Khoa CNTT – HUI 39

Tab Control flow và Data flow

• Control flow: là cơ chế quản lý thứ tự thực hiện bao gồm control flow tasks, containers, và các ràng buộc

• Data flow trực tiếp liên quan đến việc xử lý và biến đổi dữ liệu từ nguồn đến đích.

GV Phi Loan - Khoa CNTT – HUI 40

Tab Control Flow

• Có ba loại đối tượng control flow :

– Control flow tasks – Control flow containers – Constraints

GV Phi Loan - Khoa CNTT – HUI 41

Control flow task

• Là nơi mà ta xác định package sẽ phải

làm gì: – gửi email, thực hiện lệnh SQL hay copy file

từ FTP server.

• Khi 1 nhiệm vụ control flow hoàn tất thì hoặc thành công (succeeded) hoặc thất bại (failed)

GV Phi Loan - Khoa CNTT – HUI 42

Control flow task

• Dùng control flow để điều khiển việc

thực thi các nhiệm vụ song song hay đặt trước các ràng buộc dựa vào tình trạng hoàn thành của nhiệm vụ.

• Để tạo nhiệm vụ mới: drag biểu tượng từ toolbox vào màn hình của Control Flow

GV Phi Loan - Khoa CNTT – HUI 43

Bốn nhóm biểu tượng của Control Flow

Tasks : những việc mà SSIS có thể làm như thi hành lệnh SQL, chuyển đối tượng từ SQL Server sang Server khác.

2. Maintenance Plan tasks: nhóm nhiệm vụ đặc biệt quản l{ các công việc như kiểm tra tính bảo toàn dữ liệu, xây dựng lại chỉ mục,… Data Flow Task: là nhiệm vụ liên quan đến các thao tác dữ liệu ETL (extract, transform, and load) Containers: là các đối tượng dùng để nhóm các task.

GV Phi Loan - Khoa CNTT – HUI 44

Cấu hình task mới

• Mỗi task đều có 1 editor cho phép thay

đổi cấu hình của task. – Nhấp đôi tại task – hay nhấp phải và chọn lệnh Edit.

GV Phi Loan - Khoa CNTT – HUI 45

GV Phi Loan - Khoa CNTT – HUI

Control Flow Container

• Cho phép nhóm các task và sắp xếp sao cho các nhiệm vụ có thể chạy song song, hoặc sắp xếp thứ tự đăng nhập và thực thi các giao dịch (transaction) – Để sử dụng container, drag container từ toolbox vào màn hình của control flow.

GV Phi Loan - Khoa CNTT – HUI 47

Phân loại Container

1. Sequence Container 2. For Loop Container 3. Foreach Loop Container ( thực hiện vòng lặp trên tập các đối tượng như các file trong 1 folder)

GV Phi Loan - Khoa CNTT – HUI 48

Làm việc với biến (variable)

• Các biến được dùng để kết hợp các đối

tượng bằng cách: – Truyền giá trị giữa các task và container – Nhận giá trị từ bên ngoài – Trong các đoạn mã tự tạo

• Biến còn được dùng để audit và đăng

nhập vào package.

GV Phi Loan - Khoa CNTT – HUI 49

Làm việc với biến (variable)

• Để mở cửa số Variable:

SSIS\variable

• Mỗi biến được xác định bởi:

– Name – Scope, – Data type – Và giá trị của biến

GV Phi Loan - Khoa CNTT – HUI 50

Phân loại biến

• System variables • User variables.

GV Phi Loan - Khoa CNTT – HUI 51

Biến hệ thống (System variable)

• Không thể chỉnh sửa được nhưng có thể

tham chiếu đến bên trong task hay container.

• Để xem các biến hệ thống: nhấp vào nút Show System Variables. Trên thanh công cụ Variables.

GV Phi Loan - Khoa CNTT – HUI 52

Biến người dùng (user variable)

• Do người dùng tạo và xác định tùy theo

mục đích sử dụng.

• Ví dụ: trong vòng lặp Foreach, biến người dùng để đếm số lần lặp.

GV Phi Loan - Khoa CNTT – HUI 53

Nhiệm vụ đánh giá dữ liệu Data Profiling Task • Cho phép đánh giá (review) các thực thể của dữ liệu nguồn (source), kiểm tra tính rõ ràng, toàn vẹn của dữ liệu và tìm hiểu cấu trúc dữ liệu, mối quan hệ giữa các cột, cột nào có thể là khóa…. • Bao gồm 2 phần:

– Data Profiling Task : thực hiện việc phân tích

dữ liệu.

– Data Profile Viewer: xem kết quả.

GV Phi Loan - Khoa CNTT – HUI 54

Trình tự thực hiện Data Profiling Task

• B1: tạo kết nối ADO.NET mà nguồn là các

bảng/view cần đánh giá.

• B2: drag biểu tượng Data Profiling task

từ toolbox vào control flow, mở và chỉnh sửa thuộc tính của task. – Cách đơn giản: nhấp nút Quick Profile ngay

trong cửa sổ Data Profiling Task Editor.

GV Phi Loan - Khoa CNTT – HUI 55

Trình tự thực hiện Data Profiling Task

• B3: xác định file chứa kết quả của task (file XML) trong thuộc tính Destination.

• B4: xem kết quả bằng cách mở Data

Profile Viewer (Start\ All Programs\Microsoft SQL Server 2008\ Integration Services\ Data Profile Viewer)

GV Phi Loan - Khoa CNTT – HUI 56

GV Phi Loan - Khoa CNTT – HUI 57

Data Flow Task

• Là 1 trong những task quan trọng nhất, cho phép kiểm soát và biến đổi các dataset. – Hoặc drag biểu tượng Data Flow Task từ toolbox

vào vùng làm việc của tab Control Flow

– Hoặc nhấp vào tab Data Flow

• Có 3 loại đối tượng trong toolbox:

– Data flow source adapters – Data flow transformations – Data flow destination adapters

GV Phi Loan - Khoa CNTT – HUI 58

Phân loại Data flow

• Data Flow Source adapter: chỉ ra cách dữ

liệu được đưa vào hệ thồng.

• Data Flow Transformations : chỉ ra các

cách thay đổi và thao tác dữ liệu.

• Data Flow Destination adapter: chỉ ra nơi

để gửi đến dữ liệu đã biến đổi.

GV Phi Loan - Khoa CNTT – HUI 59

Trình tự thực hiện

• Chọn 1 trong các data Flow Source và xác

định cấu hình cho nguồn đã chọn

• Chọn 1 trong các data Flow Destination và xác định cầu hình cho đích đã chọn • Xác định các phép biến đổi dữ liệu từ

nguồn đến đích.

GV Phi Loan - Khoa CNTT – HUI 60

Data Flow Source

GV Phi Loan - Khoa CNTT – HUI 61

Data Flow Destination

GV Phi Loan - Khoa CNTT – HUI 62

Data Flow Destination

GV Phi Loan - Khoa CNTT – HUI 63

Ví dụ minh họa

GV Phi Loan - Khoa CNTT – HUI 64

Chọn các phép biến đổi (transformation)

• Logical row-level transformations • Multi-input or multi-output transformations • Multi-row transformations • Advanced data -preparation transformations

GV Phi Loan - Khoa CNTT – HUI 65

GV Phi Loan - Khoa CNTT – HUI