Azure Blob Storage에 있는 csv 파일을 SSIS, SQL Agent Job을 이용해서 SQL Server에 자동으로 쌓기
[테스트 목적]
- 매월 Azure 사용량을 Power BI 보고서로 만들 예정
- BI의 데이터는 MSSQL에서 가져오는 방식
- Azure 사용량은 Blob Storage에 자동으로 저장되며, 매일 쌓이는 데이터를 수동으로 INSERT 하기 번거롭기 때문에 자동으로 DB에 저장될 수 있도록 구성
[테스트 구성도]
[테스트 방법]
- 모든 테스트는 Azure Virtual Machine에서 진행
- Azure Blob Storage에는 매일 1개의 csv 파일이 쌓이고 있음
- Blob Storage에 자동으로 저장되는 파일명은 ‘폴더명_랜덤값.csv’ 으로 저장됨
- Azure Blob Storage에 있는 데이터를 로컬로 가져오기 위해 SSIS 사용
- SSIS를 통해 가져온 csv 파일을 BULK INSERT문으로 미리 생성해둔 테이블에 적재
- SQL Server job을 사용하여 자동으로 데이터가 저장될 수 있도록 함
[테스트 상세 내용]
1. Azure Blob Storage -> SSIS
Blob Storage 컨테이너에 csv 파일이 있고, 파일명은 **_**_Billing_랜덤값.csv 로 되어있다.
자동으로 가져오기 위해서 랜덤값을 어떻게 가져와야하나 고민을 많이 했었는데 SSIS 에서 Azure Blob Download Task로 데이터를 가져올 수 있었다
SSIS에서 Azure 관련된 기능을 사용하려면 Azure 서비스 기능팩을 별도로 설치해야 한다. (아래 링크)
https://learn.microsoft.com/en-us/sql/integration-services/azure-feature-pack-for-integration-services-ssis?view=sql-server-ver16
이렇게 설정하고 실행하면 로컬 폴더로 blob storage에 있는 파일들이 저장된다.
2. SSIS -> VM 폴더
현재 테스트는 7월만 진행했는데, 자동으로 매월 가져오는 것이 테스트의 목적이기 때문에
위 사진의 BlobDirecotry명을 1~12월까지로 설정하고, 데이터를 가져올 날짜를 매월 5일로 설정하고자 변수 설정을 해줬다
SSIS에서 변수 설정하기
1. 변수 생성
2. SQL 실행 태스크에서 DB와 연결 후 SQLStatement에서 결과 얻을 쿼리 생성
여기서는 '***/**_**Billing/20230701-20230731/' 이 값에서 날짜 변수 처리와 매달 5일에 가져오기 위해 쿼리 생성
SQLStatement에 아래 변수를 각각 입력해준다
스토리지 폴더명 날짜 변수
DECLARE
@folderdate nvarchar(100),
@folderstart nvarchar(10),
@folderend nvarchar(10),
@foldername nvarchar(20) = '***/**_**_Billing/'
SET @folderstart = (SELECT CONVERT(VARCHAR(6) ,DATEADD(mm,-1,GETDATE()) ,112) + '01')
SET @folderend = (SELECT CONVERT(VARCHAR(6) ,DATEADD(mm,-1,GETDATE()) ,112) + '31')
SET @folderdate = (SELECT @foldername + @folderstart + '-' + @folderend + '/')
SELECT ?= @folderdate
실행할 날짜 변수 (매월 5일)
SELECT ? = CONVERT(VARCHAR(8) ,GETDATE() ,121) + '05' ,? = CONVERT(VARCHAR(8) ,GETDATE() ,121) + '05 23:59:59'
파라미터 매핑에서 파라미터 이름은 0,1로 해준다
그다음 Azure Blob Download Task Editor로 가서 변수처리할 항목들을 보고 Expressions에서 생성했던 변수 넣어주면 됨
변수 설정 완료 후 실행
그럼 vm에서 지정한 폴더에 파일이 저장된다
3. VM 폴더 -> DB 적재
로컬 폴더에 저장된 데이터들을 DB에서 BULK INSERT로 가져올 때 xp_cmdshell을 사용했다
xp_cmdshell
→ MSSQL에서 운영체제 명령을 실행할 수 있도록 하는 함수
xp_cmdshell 사용이유
→ BULK INSERT로 csv 파일을 가져올경우 파일명을 지정해줘야함.
파일명을 매번 지정해서 실행할 수 없기 때문에 자동으로 가지고 오기 위해 사용
BULK INSERT
→ CSV파일을 이용해 대용량 데이터를 테이블에 INSERT 할 때 사용
1) csv구조에 맞는 테이블 생성
2) xp_cmdshell 사용을 위해 아래와 같이 설정 (이때 가져오려는 폴더에 접근 권한이 설정되어 있어야 함)
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO
-----------------------------------------------------------------------------
EXEC sp_xp_cmdshell_proxy_account 'VM\je', '비밀번호'
GRANT EXECUTE ON xp_cmdshell TO [VM\je]
execute as login = 'VM\je'
exec xp_cmdshell 'whoami'
3) 파일명 가져오고 Bulkinsert로 테이블에 데이터 insert 될 수 있도록 프로시저 생성
(참고) Import Multiple CSV Files into SQL Server using stored procedure. - YouTube
CREATE OR ALTER PROC INSERTDATA
AS
BEGIN
DECLARE @Query VARCHAR(1000), @Filepath VARCHAR(1000), @pattern varchar(1000)
SET @Filepath = 'C:\Users\je\Desktop\Report\blob\'
SET @pattern = '*.csv'
CREATE TABLE #X (name varchar(200))
SET @Query = 'master.dbo.xp_cmdshell "dir '+@Filepath+@pattern+' /b" '
EXEC (@Query)
INSERT X EXEC (@Query)
DELETE FROM X WHERE name IS NULL
SELECT IDENTITY(INT, 1, 1) AS ID, NAME INTO Y FROM X
DROP TABLE X
DECLARE @max int, @COUNT INT, @filename varchar(200)
SET @max = (select max(ID) FROM Y)
SET @COUNT= 0
WHILE @COUNT < @max
BEGIN
SET @COUNT = @COUNT + 1
SET @filename = (select name from #Y where [ID] = @COUNT)
SET @Query = 'BULK INSERT Billing_en FROM "'+@Filepath+@filename+'" WITH(FORMAT = ''CSV'', CODEPAGE=''65001'', FIELDTERMINATOR ='','', ROWTERMINATOR = ''\n'', FIELDQUOTE =''"'', KEEPNULLS, FIRSTROW = 2)'
EXEC (@Query)
END
DROP TABLE y
END- 원래 X, Y 테이블은 임시 테이블로 생성했으나, JOB 생성시 FAIL 되서 일반 테이블로 생성
4) 프로시저 실행하면 아래와 같이 테이블에 데이터가 저장됨
<프로시저 실행 전>
<프로시저 실행 후>
데이터가 잘 들어왔는데 한글로 된 데이터가 깨져서 들어왔다..
프로시저 실행 쿼리에서 BULK INSERT 할 때 CODEPAGE = 65001 추가했더니 한글 깨짐 현상이 사라졌다
4. SQL Server Agent Job 설정
SSIS 에서 자동으로 실행할 JOB을 설정해준다
로컬에서 자동으로 파일 가져올 수 있도록 위에서 생성했던 프로시저를 JOB에 입력해준다
끝