MSSQL(PDW)에서 ORC 타입으로 하둡에 데이터를 내리고 하이브 테이블 생성하기

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로 데이터를 내린다. 그리고 하이브에서 내린 데이터를 가지고 외부 테이블을 생성하면 된다.