MSSQL및 PDW(parallel data warehouse)에는 하둡과 연결고리가 잘 되어있다.
이들이 지원하는 PolyBase라는 기능은 RDBMS의 데이터와 하둡에 저장된 데이터를 조인할 수 있도록 하는데 예를 들어 하둡에 저장되어 있는 데이터를 RDBMS로 가져오고 둘을 조인해서 원하는 측정값을 뽑아내기에 매우 유용하다.
위 처럼 HDFS -> MSSQL(PDW) 로의 데이터 이동이 아닌 MSSQL(PDW) -> HDFS로 데이터를 내리는 것도 가능하며 예제는 다음과 같다. (PDW 기준으로 예제를 작성하였다.)
다음 예제는 ORC 타입으로 데이터를 내리는 방법이고 파케이(parquet)나 텍스트 등 다양한 타입 및 압축(Gzip, Snappy 등)으로 데이터 포맷을 생성할 수 있다.
먼저 데이터 소스랑 파일 포맷을 생성한다. (각 DB별로 생성할 수 있다.)
USE TEST_DB;
-- 데이터 소스 생성
IF EXISTS (SELECT * FROM sys.external_data_sources WHERE name = 'HDFS_DS_SNAPSHOT')
DROP EXTERNAL DATA SOURCE HDFS_DS_SNAPSHOT
CREATE EXTERNAL DATA SOURCE HDFS_DS_SNAPSHOT
WITH (TYPE = HADOOP
, LOCATION = N'hdfs://XXX.XXX.XXX.XXX:8020'
, RESOURCE_MANAGER_LOCATION = N'XXX.XXX.XXX.XXX:8050')
GO
-- 파일 포맷 생성
IF EXISTS (SELECT * FROM sys.external_file_formats WHERE name = 'HDFS_FF_ORC')
DROP EXTERNAL FILE FORMAT HDFS_FF_ORC
CREATE EXTERNAL FILE FORMAT HDFS_FF_ORC
WITH (
FORMAT_TYPE = ORC,
DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
-- 생성된 데이터 소스 및 파일 포맷 확인
SELECT * FROM sys.external_data_sources
SELECT * FROM sys.external_file_formats
-- 하둡에 월 파티션으로 데이터 내리기
DECLARE @DT VARCHAR(8) = '20170101'
DECLARE @SQL NVARCHAR(4000);
WHILE(1=1)
BEGIN
SET @SQL = N' IF OBJECT_ID(''TEST_DB..HDFS_TEST_TABLE_'+@DT+''') IS NOT NULL
DROP EXTERNAL TABLE HDFS_TEST_TABLE_' + @DT + '
CREATE EXTERNAL TABLE HDFS_TEST_TABLE_' + @DT + '
WITH (LOCATION=' + '''/apps/hive/xxxxxxxx/yyyyyyyy/zzzzzzzzz/TEST_TABLE/yyyymmdd=' + @DT + ''',
DATA_SOURCE = HDFS_DS_SNAPSHOT,
FILE_FORMAT = HDFS_FF_ORC,
REJECT_TYPE = PERCENTAGE,
REJECT_SAMPLE_VALUE = 10000,
REJECT_VALUE = 0.1
) AS
SELECT *
FROM TEST_TABLE
WHERE ' + N'DT' + ' >= ''' + @DT + ''' AND ' + N'DT' + ' < CONVERT(VARCHAR(8),DATEADD(MM,1,''' + @DT + '''),112) '
--PRINT @SQL
SET @DT = CONVERT(VARCHAR(8),DATEADD(MM,1,@DT),112)
IF(@DT > '20180101')
BREAK;
END
다음부터는 하이브에서 진행하며 위에서 생성한 HDFS를 사용한다.
-- 하이브에서 테이블 만들기
CREATE EXTERNAL TABLE HDFS_TEST_TABLE
(
`COL1` bigint ,
`COL2` bigint ,
`COL3` bigint ,
)
PARTITIONED BY (YYYYMMDD STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS ORC
LOCATION '/apps/hive/xxxxxxxx/yyyyyyyy/zzzzzzzzz/TEST_TABLE';
-- 파티션 적용하기
ALTER TABLE database_name.HDFS_TEST_TABLE ADD PARTITION (YYYYMMDD='20170101');
ALTER TABLE database_name.HDFS_TEST_TABLE ADD PARTITION (YYYYMMDD='20170201');
ALTER TABLE database_name.HDFS_TEST_TABLE ADD PARTITION (YYYYMMDD='20170301');
ALTER TABLE database_name.HDFS_TEST_TABLE ADD PARTITION (YYYYMMDD='20170401');
ALTER TABLE database_name.HDFS_TEST_TABLE ADD PARTITION (YYYYMMDD='20170501');
ALTER TABLE database_name.HDFS_TEST_TABLE ADD PARTITION (YYYYMMDD='20170601');
ALTER TABLE database_name.HDFS_TEST_TABLE ADD PARTITION (YYYYMMDD='20170701');
ALTER TABLE database_name.HDFS_TEST_TABLE ADD PARTITION (YYYYMMDD='20170801');
ALTER TABLE database_name.HDFS_TEST_TABLE ADD PARTITION (YYYYMMDD='20170901');
ALTER TABLE database_name.HDFS_TEST_TABLE ADD PARTITION (YYYYMMDD='20171001');
ALTER TABLE database_name.HDFS_TEST_TABLE ADD PARTITION (YYYYMMDD='20171101');
ALTER TABLE database_name.HDFS_TEST_TABLE ADD PARTITION (YYYYMMDD='20171201');
정리하면 MSSQL(PDW)에서 데이터 소스와 파일 포맷을 만들어주고 이를 활용해서 HDFS로 데이터를 내린다. 그리고 하이브에서 내린 데이터를 가지고 외부 테이블을 생성하면 된다.
