Transact-SQL(T-SQL) #3 - FILE TABLE

FILE TABLE은 SQL SERVER 2012 이상 버전에서 지원하는 기능입니다.

FILE TABLE은 FILESTREAM을 기반으로 동작하는 기능이므로 이전에 블로깅 했던 "Transact-SQL(T-SQL) #2 - FILESTREAM"을 참고하셔서 FILESTREAM을 활성화해주시면 감사하겠습니다.

 

1. 많은 파일을 데이터베이스에 INSERT 하려면?

SQL SERVER에서 제공하는 VARBINARY 데이터 형식을 사용하면 파일을 직접 데이터베이스에 저장할 수 있습니다.

하지만 서버 측에서 파일을 INSERT 하는 일이 빈번하게 일어나거나 한번에 많은 파일을 INSERT 한다면 매번 데이터베이스에 질의를 날리는 것이 번거로울 것입니다.

 

SQL SERVER는 WIN API를 통하여 있는 파일을 데이터베이스와 자동으로 동기화하는 FILE TABLE 기능을 제공합니다.

 

 

2. FILE TABLE을 사용하기 위한 DBMS 설정

1. FILE TABLE을 만들 데이터베이스의 속성 창으로 들어갑니다.

 

데이터베이스 속성 > 옵션 > 기타옵션 > FILESTREAM 에서 FILESTREAM 디렉터리 이름을 추가하고 FILESTREAM 트랜잭션되지 않은 엑세스를 FULL로 변경합니다.

 

 

 

'FILESTREAM 디렉터리 이름' 은 NULL 값을 제외한 어떤 값을 넣어도 상관없습니다.

'FILESTREAM 트랜잭션 되지 않은 엑세스' 를 FULL로 변경하면 T-SQL뿐만 아니라 WIN API를 통하여 FILESTREAM에 데이터를 쓸 수 있게 됩니다.

 

 

3. FILE TABLE 만들기

1. 테이블 폴더에서 우클릭을 하여 "새 FileTable(T)..."을 클릭합니다.

 

 

2. 아래 그림과 같이 FileTable을 만들 수 있는 Script가 자동 생성됩니다.

 

 

3. Script를 다음과 같이 수정합니다.

 

 

SSM_FILETABLE 는 생성할 FILE TABLE의 이름입니다. 원하시는 이름으로 작성하시면 됩니다.

 

 

실행을 누르면 위 그림과 같이 FILE TABLE이 생성됩니다.

file_stream 필드의 데이터 형식은 varbinary(max) 로 여기에 파일이 저장됩니다. 또한, FILESTREAM으로 구현되어 있기 때문에 데이터베이스에 직접 저장되지 않고 따로 파일 형태로 관리됩니다.

또한, file_stream 필드 말고도 파일의 정보를 나타내는 다양한 필드가 생성되어 있음을 알 수 있습니다.

 

4. FILE 쓰기

생성한 FILE TABLE에 파일을 쓰는 방법은 크게 2가지가 있습니다.

첫 번째는 이전 장인 "Transact-SQL(T-SQL) #2 - FILESTREAM"과 동일하게 T-SQL을 사용하여 테이블에 파일을 INSERT 하는 방법입니다.

 

두 번째 방법은 FILE TABLE에서 지원하는 WIN API를 사용하여 File을 INSERT 할 수 있습니다.

 

5. WIN API를 사용하여 FILE INSERT 하기

 

 

위 그림처럼 생성한 FILE TABLE에서 우클릭을 한 후 "FileTable 디렉터리 탐색"을 클릭합니다. 만약 해당 메뉴가 활성화 되어 있지 않다면 데이터베이스 속성의 옵션부분에서 "FILESTREAM 트랜잭션 되지 않은 엑세스"를 Full로 설정되었는지 확인하시기 바랍니다.

 

 

위 그림과 비어있는 폴더가 출력되면 성공입니다. 이 폴더에 파일을 복사하면 FILE TABLE(FILESTREAM)과 동기화됩니다.

 

 

 

위 그림과 같이 FILE TABLE에 INSERT 할 파일을 폴더에 복사합니다.

 

6. FILE  TABLE 데이터 사용

 

 

쿼리로 생성한 FILE TABLE의 데이터를 확인해 보면 위 그림과 같이 폴더로 복사했던 파일들이 TABLE에 자동으로 INSERT 되어 있음을 확인할 수 있습니다.

 

파일은 file_stream 필드에 VARBINARY(MAX) 데이터 형식으로 저장되어 있기 떄문에 기존에 VARBINARY 형식의 데이터를 사용하는 방법으로 동일하게 사용하면 됩니다.

file_stream 필드 말고도 file_type, cached_file_size, create_time 등 파일에 관한 정보가 저장되어있는 필드를 활용할 수 있습니다.

 

7. FILE TABLE의 데이터 수정

WIN API를 통해 접근할 수 있는 FILE TABLE 폴더에 있는 파일을 수정하거나 삭제하면 FILE TABLE에 바로 반영이 됩니다.

 

위 그림과 같이 3개의 파일을 제외하고 나머지 파일은 삭제하였고 2번째 Desert.jpg를 GoodDesert.jpg로 이름 변경하였습니다.

 

 

 

쿼리를 통해 FILE TABLE의 내용을 확인해보면 변경된 부분이 바로 반영되어있음을 확인할 수 있습니다.

 

8. 마치며

SQL SERVER 2012부터 제공되는 FILE TABLE을 알아보았습니다. 많은 기능을 제공하는 FILE TABLE에도 단점이 있습니다.

T-SQL을 통한 FILE TABLE의 질의 수행은 트랜잭션을 보장받지만, WIN API를 통한 파일 쓰기 및 수정은 비동기 트랜잭션이 매번 수행되기 때문에 폴더에 있는 파일이 FILE TABLE과 동기화되는 정확한 시간을 보장받을 수 없습니다.

 

하지만 WIN API를 통해 FILE TABLE에 접근할 수 있는 점은 활용방법이 무궁무진할 것으로 생각됩니다.