Serde - JsonSerde

September 2, 2021

서데(SerDe, Serializer/Deserialaizer)는 하이브가 데이터를 해석하는 방법을 제공한다. 예를 들어 하둡에 Json 파일을 따로 전처리하지 않고 서데를 이용하여 바로 집어 넣으면 하이브에서 Row 포맷으로 변환하여 읽을 수 있게 된다.

하이브는 서데와 파일 포맷을 이용하여 데이터를 읽고, 쓸 수 있다.

아래는 하이브가 서데를 이용하여 파일을 읽을 때와 쓸 때의 처리방법이다.

파일 읽을 때 HDFS files -> InputFileFormat -> [key, value] -> Deserializer -> Row object
파일 쓸 때 Row object -> Serializer -> [key, value] -> OutputFileFormat -> HDFS files

하이브 기본 서데

하이브에서 제공하는 기본 서데는 7가지(Avro, ORC, RegEx, Thrift, Parquet, CSV, JsonSerDe)가 있다. 각 서데는 STORED AS 에 지정하는 파일의 포맷에 따라 자동으로 선택된다.

가장 많이 쓰는 반정형 데이터 형식인 Json을 하둡에 넣고 서데를 이용하여 하이브로 조회해보자.

넣을 데이터는 아래와 같다.

# localdata.json

{
    "result": {
        "header": {
            "columns": [
                {
                    "rowNum": "번호",
                    "opnSfTeamCode": "개방자치단체코드",
                    "mgtNo": "관리번호",
                    "opnSvcId": "개방서비스ID",
                    "updateGbn": "데이터갱신구분",
                    "updateDt": "데이터갱신일자",
                    "opnSvcNm": "개방서비스명",
                    "bplcNm": "사업장명",
                    "sitePostNo": "지번우편번호",
                    "siteWhlAddr": "지번주소",
                    "rdnPostNo": "도로명우편번호",
                    "rdnWhlAddr": "도로명주소",
                    "siteArea": "소재지면적",
                    "apvPermYmd": "인허가일자",
                    "apvCancelYmd": "인허가취소일자",
                    "dcbYmd": "폐업일자",
                    "clgStdt": "휴업시작일자",
                    "clgEnddt": "휴업종료일자",
                    "ropnYmd": "재개업일자",
                    "trdStateGbn": "영업상태코드",
                    "trdStateNm": "영업상태명",
                    "dtlStateGbn": "상세영업상태코드",
                    "dtlStateNm": "상세영업상태명",
                    "x": "좌표정보(X)",
                    "y": "좌표정보(Y)",
                    "lastModTs": "최종수정일자",
                    "uptaeNm": "업태구분명",
                    "siteTel": "전화번호"
                }
            ],
            "paging": {
                "pageIndex": 1,
                "totalCount": 7360,
                "pageSize": 50
            },
            "process": {
                "code": "00",
                "message": "정상 처리되었습니다."
            }
        },
        "body": {
            "rows": [
                {
                    "row": [
                        {
                            "rowNum": 1,
                            "opnSfTeamCode": 3740000,
                            "mgtNo": "PHMD120213740032084000004",
                            "opnSvcId": "01_01_06_P",
                            "updateGbn": "I",
                            "updateDt": "2021-07-04 00:22:53.0",
                            "opnSvcNm": "약국",
                            "bplcNm": "하나온누리약국",
                            "sitePostNo": "",
                            "siteWhlAddr": "경기도 수원시 권선구 세류동 1155-9 세류비즈타워",
                            "rdnPostNo": 16660,
                            "rdnWhlAddr": "경기도 수원시 권선구 경수대로 193, 세류비즈타워 1층 105호 (세류동)",
                            "siteArea": "",
                            "apvPermYmd": 20210712,
                            "apvCancelYmd": "",
                            "dcbYmd": "",
                            "clgStdt": "",
                            "clgEnddt": "",
                            "ropnYmd": "",
                            "trdStateGbn": "01",
                            "trdStateNm": "영업/정상",
                            "dtlStateGbn": 13,
                            "dtlStateNm": "영업중",
                            "x": "201454.508790129    ",
                            "y": "415838.18218489     ",
                            "lastModTs": 20210702162433,
                            "uptaeNm": "",
                            "siteTel": ""
                        },
                        {
                            "rowNum": 2,
                            "opnSfTeamCode": 4180000,
                            "mgtNo": 4180000202189,
                            "opnSvcId": "11_50_03_P",
                            "updateGbn": "I",
                            "updateDt": "2021-07-04 00:22:53.0",
                            "opnSvcNm": "행정사업",
                            "bplcNm": "유도현 행정사 사무소",
                            "sitePostNo": "",
                            "siteWhlAddr": "강원도 춘천시 퇴계동 ****",
                            "rdnPostNo": 24380,
                            "rdnWhlAddr": "강원도 춘천시 남춘천새길 *(퇴계동)",
                            "siteArea": "",
                            "apvPermYmd": 20210708,
                            "apvCancelYmd": "",
                            "dcbYmd": "",
                            "clgStdt": "",
                            "clgEnddt": "",
                            "ropnYmd": "",
                            "trdStateGbn": "01",
                            "trdStateNm": "영업/정상",
                            "dtlStateGbn": "01",
                            "dtlStateNm": "영업중",
                            "x": "264546.296706142    ",
                            "y": "484486.499826113    ",
                            "lastModTs": 20210702151631,
                            "uptaeNm": "",
                            "siteTel": "033-264-1238"
                        },
                        ...
                    ]
                }
            ]
        }
    }
}

위는 json 데이터를 정렬시켜 놓았지만, hadoop에 실제 들어가는 json은 정렬하지 말고 한줄로 들어가야 한다.

json serde를 이용한 create문은 아래와 같다.

create table localdata (
	result struct<
		header: struct<
			columns: array<
				struct<
					rowNum: string,
					opnSfTeamCode: string,
					mgtNo: string,
					opnSvcId: string,
					updateGbn: string,
					updateDt: string,
					opnSvcNm: string,
					bplcNm: string,
					sitePostNo: string,
					siteWhlAddr: string,
					rdnPostNo: string,
					rdnWhlAddr: string,
					siteArea: string,
					apvPermYmd: string,
					apvCancelYmd: string,
					dcbYmd: string,
					clgStdt: string,
					clgEnddt: string,
					ropnYmd: string,
					trdStateGbn: string,
					trdStateNm: string,
					dtlStateGbn: string,
					dtlStateNm: string,
					x: string,
					y: string,
					lastModTs: string,
					uptaeNm: string,
					siteTel: string
				>
			>,
			paging: struct<
				pageIndex: string,
				totalCount: string,
				pageSize: string
			>,
			process: struct<
				code: string,
				message: string
			>
		>,
		body: struct<
		 	`rows`: array<
		 		struct<
		 			`row`: array<
		 				struct<
							rowNum: string,
							opnSfTeamCode: string,
							mgtNo: string,
							opnSvcId: string,
							updateGbn: string,
							updateDt: string,
							opnSvcNm: string,
							bplcNm: string,
							sitePostNo: string,
							siteWhlAddr: string,
							rdnPostNo: string,
							rdnWhlAddr: string,
							siteArea: string,
							apvPermYmd: string,
							apvCancelYmd: string,
							dcbYmd: string,
							clgStdt: string,
							clgEnddt: string,
							ropnYmd: string,
							trdStateGbn: string,
							trdStateNm: string,
							dtlStateGbn: string,
							dtlStateNm: string,
							x: string,
							y: string,
							lastModTs: string,
							uptaeNm: string,
							siteTel: string
						>
					>
		 		>
		 	>
		>
	>
)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS TEXTFILE
LOCATION
  '/user/hive/st/localdata'
;

테이블을 생성했으면 hadoop에 데이터를 넣자.

$ hadoop fs -put localdata.json /user/hive/st/localdata

데이터에서 body만 조회를 해보자.

select
	msgarr.rowNum,
	msgarr.opnSfTeamCode,
	msgarr.mgtNo,
	msgarr.opnSvcId,
	msgarr.updateGbn,
	msgarr.updateDt,
	msgarr.opnSvcNm,
	msgarr.bplcNm,
	msgarr.sitePostNo,
	msgarr.siteWhlAddr,
	msgarr.rdnPostNo,
	msgarr.rdnWhlAddr,
	msgarr.siteArea,
	msgarr.apvPermYmd,
	msgarr.apvCancelYmd,
	msgarr.dcbYmd,
	msgarr.clgStdt,
	msgarr.clgEnddt,
	msgarr.ropnYmd,
	msgarr.trdStateGbn,
	msgarr.trdStateNm,
	msgarr.dtlStateGbn,
	msgarr.dtlStateNm,
	msgarr.x,
	msgarr.y,
	msgarr.lastModTs,
	msgarr.uptaeNm,
	msgarr.siteTel
from localdata ld lateral view explode(result.body.`rows`.`row`) msgtable as msgarr
;

이는 아래와 같이 결과가 나올 것이다.

50개의 데이터가 각 컬럼의 배열에 담겨 한 row로 나온다.

각각의 row로 데이터를 보고싶을땐 아래와 같이 하면 된다.

select
	msgarr2.rowNum,
	msgarr2.opnSfTeamCode,
	msgarr2.mgtNo,
	msgarr2.opnSvcId,
	msgarr2.updateGbn,
	msgarr2.updateDt,
	msgarr2.opnSvcNm,
	msgarr2.bplcNm,
	msgarr2.sitePostNo,
	msgarr2.siteWhlAddr,
	msgarr2.rdnPostNo,
	msgarr2.rdnWhlAddr,
	msgarr2.siteArea,
	msgarr2.apvPermYmd,
	msgarr2.apvCancelYmd,
	msgarr2.dcbYmd,
	msgarr2.clgStdt,
	msgarr2.clgEnddt,
	msgarr2.ropnYmd,
	msgarr2.trdStateGbn,
	msgarr2.trdStateNm,
	msgarr2.dtlStateGbn,
	msgarr2.dtlStateNm,
	msgarr2.x,
	msgarr2.y,
	msgarr2.lastModTs,
	msgarr2.uptaeNm,
	msgarr2.siteTel
from localdata ld lateral view explode(result.body.`rows`.`row`) msgtable as msgarr
                    LATERAL VIEW explode(msgarr) msgtable2 AS msgarr2
;

결과는 아래와 같다.

데이터 총 개수를 구하는 쿼리문은 아래와 같다.

select
	size(msgarr)
from localdata ld lateral view explode(result.body.`rows`.`row`) msgtable as msgarr
;

Serde - JsonSerde

하이브 기본 서데

Leave a comment

You may also enjoy

Spark와 MariaDB Connection Error

[MinIO] MinIO Key Encryption Service(KES)

[MinIO] MinIO 핵심 운영 개념

[Snowflake Partner Technical Bootcamp] 6. Data Sharing