개발자

Ch03.

 01. 데이터 준비 - 2 : 조인, 관계, 블랜딩, 관계

 02. 필터 : Order of Operations

출력

  SELECT
  CAST(42 AS STRING) => '42'
  , CAST('42' AS INT64) => 42
  , FORMAT('%03d', 42) => 042
  , FORMAT('%5.3f', 32.457842) => 32.458
  , FORMAT('%5.3f', 32.4) => 32.400
  , FORMAT('**%s**', 'H') => **H**
  , FORMAT('%s-%03d', 'Agent', 7) => Agent-007

문자열 조작 함수

SELECT
  ENDS_WITH('Hello', 'o') -- true
  , ENDS_WITH('Hello', 'h') -- false
  , STARTS_WITH('Hello', 'h') -- false
  , STARTS_WITH('Hello', 'H') -- true
  , STARTS_WITH('Hello', 'Hel') -- true
  , STRPOS('Hello', 'e') -- 2
  , STRPOS('Hello', 'l') -- 3
  , STRPOS('Hello', 'll') -- 3
  , STRPOS('Hello', 'f') -- 0 (for not-found)
  , SUBSTR('Hello', 2, 4) -- ello
  , CONCAT('Hello', ' World') -- Hello World

변환 함수

SELECT
  LPAD('Hello', 10, '*') -- 왼쪽에 *가 추가된다
  , RPAD('Hello', 10, '*') -- 오른쪽에 *가 추가된다
  , LPAD('Hello', 10) -- 왼쪽에 공백이 추가된다
  , LTRIM('   Hello   ') -- 왼쪽의 공백이 제거된다
  , RTRIM('   Hello   ') -- 오른쪽의 공백이 제거된다
  , TRIM ('   Hello   ') -- 양쪽의 공백이 제거된다
  , TRIM ('***Hello***', '*') -- 양쪽의 *이 제거된다
  , REVERSE('Hello') -- olleH

정규 표현식

SELECT
  column
  , REGEXP_CONTAINS(column, r'\d{5}(?:[-\s]\d{4})?') has_zipcode
  , REGEXP_CONTAINS(column, r'^\d{5}(?:[-\s]\d{4})?$') is_zipcode
  , REGEXP_EXTRACT(column, r'\d{5}(?:[-\s]\d{4})?') the_zipcode
  , REGEXP_EXTRACT_ALL(column, r'\d{5}(?:[-\s]\d{4})?') all_zipcodes
  , REGEXP_REPLACE(column, r'\d{5}(?:[-\s]\d{4})?', '*****') masked
FROM (
  SELECT * FROM UNNEST([
     '12345', '1234', '12345-9876', 
     'abc 12345 def', 'abcde-fghi',
     '12345 ab 34567', '12345 9876'
  ]) AS column
)

행     column                   has_zipcode  is_zipcode   the_zipcode        all_zipcodes           masked

정규식 관련 문서 : https://github.com/google/re2/wiki/Syntax

타임 스탬프 처리

SELECT t1, t2, TIMESTAMP_DIFF(t1, t2, MICROSECOND)
FROM (SELECT
  TIMESTAMP "2017-09-27 12:30:00.45" AS t1,
  TIMESTAMP "2017-09-27 13:30:00.45+1" AS t2
)

결과 동일

SELECT
  fmt, input, zone
  , PARSE_TIMESTAMP(fmt, input, zone) AS ts
FROM (
  SELECT '%Y%m%d-%H%M%S' AS fmt, '20181118-220800' AS input, '+0' AS zone
  UNION ALL SELECT '%c', 'Sat Nov 24 21:26:00 2018', 'America/Los_Angeles'
  UNION ALL SELECT '%x %X', '11/18/18 22:08:00', 'UTC'
)

SELECT
  ts, fmt
  , FORMAT_TIMESTAMP(fmt, ts, '+6') AS ts_output
FROM (
  SELECT CURRENT_TIMESTAMP() AS ts, '%Y%m%d-%H%M%S' AS fmt
  UNION ALL SELECT CURRENT_TIMESTAMP() AS ts, '%c' AS fmt
  UNION ALL SELECT CURRENT_TIMESTAMP() AS ts, '%x %X' AS fmt
)

항공권 검색 서비스 앞에 [실시간]이라는 단어가 붙는 이유는 무엇일까요? 너무 당연한 이야기이지만 항공 스케줄과 요금에 대한 ‘실시간 데이터’ 를 제공하기 때문입니다. (ㅎㅎ)

​

실시간 데이터가 중요한 이유는 항공권 가격과 스케줄에 대한 예약 가능 여부가 실시간으로 변경되기 때문이에요. 예약이 가능한 좌석 수를 Avail이라고 하는데요, 성인 4명으로 항공권 검색을 하였을 때 Avail이 4 미만이라면 예약을 할 수 없겠죠? 그런데 누군가가 예약을 취소하여 Avail이 4 이상이 되었다면 어떻게 될까요? 항공 스케줄에 대한 Avail이 실시간으로 반영되어야 더 정확한 검색 결과를 제공 할 수 있어요. 항공권 가격 측면에서 보면 항공사와 여행사는 자신들의 정책이나 수요에 따라 항공권 요금을 변경하는데, 실시간 데이터가 아니라면 변경된 요금에 대해 정확한 검색 결과를 제공하지 못하겠죠.

​

이런 실시간 항공권 검색을 서비스하기 위해서는 대용량 데이터 처리에 대한 고민이 필요해요. 한번의 항공권 검색을 했을 때 실시간으로 처리해야 하는 데이터가 1GB를 넘어가는 경우도 아주 많거든요. 그럼 지금부터 실시간 항공권 검색 서비스를 제공하기 위해 필요한 항공 스케줄 데이터의 종합과 대용량 데이터 처리 방법에 대해 알아보고, 티몬에서는 실시간 항공권 검색 서비스를 어떻게 만들었는지 한번 살펴볼께요.

항공 스케줄 데이터를 제공하는 GDS

우선, 항공권 검색 결과를 제공하기 위해서는 항공 스케줄에 대한 데이터를 가지고 있어야겠죠? 전 세계에 산재되어 있는 항공사의 스케줄 데이터를 요청하고 종합하기는 쉽지 않은 일이에요. 그래서 이런 항공 스케줄 데이터는 GDS(Global Distribution Systems)라는 글로벌 업체를 통해 제공받고 있어요.

GDS가 생긴 이유는 전 세계에 산재되어 있는 항공사의 항공권이나 호텔 등의 예약을 고객에게 더 쉽게 제공하기 위해서에요. 항공사는 여러 나라의 고객을 대상으로 항공권을 판매해야 하는데, 각국의 고객들에게 직접 항공권을 판매하려면 언어, 지역 특색 등 고려해야할 사항이 많겠죠? 그래서 항공사가 직접 판매하기도 하지만, 각 나라에 있는 여행사와 연계하여 항공권을 판매하고 있어요. 여행사 입장에서는 항공권을 판매하면서 수수료나 패키지 구성 등의 이익을 얻을 수 있고요. 이러한 항공사와 여행사의 이해 관계에 의해 둘 사이의 중개를 맡고 있는 곳이 바로 GDS랍니다. GDS는 항공권 스케줄과 요금에 대한 데이터를 보유하고 있고 이 데이터를 실시간 API 형태로 제공하고 있어요.

티몬에서는 국내 실시간 항공권 검색 서비스 중 가장 많은 GDS와 연계하여 실시간 항공권 검색 서비스를 제공하고 있어요. 현재는 하나투어·자유투어 등과 연계된 Sabre, 모두투어·노랑풍선 등과 연계된 Amadeus라는 두 군데의 GDS와 연동이 완료되어 서비스 중이고, 와이페이모어·웹투어 등과 연계된 Galileo GDS와도 연동 작업을 진행하고 있답니다.

GDS별 시장 점유율

GDS에 대해서 어느정도 이해가 되셨나요? 그럼 지금부터는 GDS의 API를 이용하여 제공받은 실시간 항공권 데이터 처리에 대한 이야기를 해볼께요.

GDS에서 제공받은 데이터 처리

티몬에서는 여러 GDS와 연계하여 항공권 검색 결과를 제공하다 보니, 검색 조건에 따라 다르긴 하지만 GDS와 실시간 API 통신 과정에서 1GB 이상의 데이터를 처리해야 하는 경우가 아주 많아요. 물론 각 GDS별로 호출하는 서버와 인스턴스를 분리하여 한 서버에서 처리하는 GDS의 XML 데이터는 1GB까지 되지는 않지만, 각 GDS 데이터에 대한 파싱과 가공 후 데이터 통합 작업을 해야 하기 때문에 원활한 서비스를 제공하기 위해서는 데이터 처리에 대해 고려해야 할 부분들이 많답니다.

GDS들은 대용량 항공 데이터에 대해 XML로 제공해 주는 곳들이 많아요. 티몬에서는 GDS에서 제공하는 XML 데이터에 대한 파싱과 가공을 위해 Jaxb를 이용하고 있어요. XML 데이터에 대한 가공은 1차 가공과 2차 가공으로 나눌 수 있어요.

1차 가공

1차 가공은 기본적인 데이터 trim이나 특수문자에 대한 변환, 특정 필드 앞뒤로 고정된 데이터 추가 등 GDS를 통해 얻은 데이터 자체에 대한 변환·수정 작업이고, 이 부분은 Jaxb의 Adapter를 주로 이용합니다. @XmlJavaTypeAdapter를 이용하는 방법인데요, 특정 필드에 대한 가공은 XmlAdapter<ValueType, BoundType>을 상속받고 unmarshal, marshal method를 override하는 Adapter클래스를 생성하여 가공이 필요한 필드에 @XmlJavaTypeAdapter를 추가하는 방법을 이용하고, 전체 필드에 대한 가공은 package-info.java를 이용하여 가공합니다.

​

2차 가공

2차 가공은 여행사 정보, 스케줄, 요금 정보 등에 대한 필터링 및 티몬 항공권 서비스를 위해 티몬 내부적으로 정해놓은 규격에 맞게 데이터를 가공한답니다.

​

먼저, 특정 필드 가공에 대한 예제를 보여드릴께요.

​

티몬에서는 항공권 요금 관련 필드의 가공을 위해 Integer형으로 파싱을 하는데, GDS에서 제공하는 XML 데이터에는 소수점이 포함된 데이터가 있는 경우가 있습니다. 물론, 소수점 아래 값은 5000.00원처럼 항상 00으로 끝나는 무의미한 데이터여서 소수점 아래 부분을 버리고 있습니다.

public class FareAdapter extends XmlAdapter<String, Integer> { @Override public Integer unmarshal(String v) throws Exception { if (v == null) { return 0; } int pos = v.indexOf(“.”); if (pos > 0) { v = v.substring(0, pos); } return Integer.parseInt(v); } @Override public String marshal(Integer v) throws Exception { if (v == null) { return null; } return String.valueOf(v); } } Fare.java public class Fare { @XmlJavaTypeAdapter(value = FareAdapter.class, type = Integer.class) private Integer adultFare; }

Fare 클래스에 adultFare라는 Integer값이 보이시나요? 특정 필드에 저렇게 @XmlJavaTypeAdapter 어노테이션을 이용하여 변환 작업을 하고 있어요.

전체필드 가공에 대한 예제도 한번 볼께요.

​

위 FareAdapter 클래스 처럼 XmlAdapter<ValueType, BoundType>를 상속받는 Adapter 클래스를 만드는 부분은 동일하고 해당 Adapter 클래스가 적용되어야 할 package 단위로 Adapter를 적용하는 방법입니다.

package-info.java @XmlJavaTypeAdapter(value = TrimAdapter.class, type = String.class) package com.tmon; import com.tmon.adapter.TrimAdapter; import javax.xml.bind.annotation.adapters.XmlJavaTypeAdapter;

패키지 단위에 대한 Java Doc 생성 시 주로 이용하는 package-info.java에 @XmlJavaTypeAdapter어노테이션을 추가하면, 해당 패키지에 있는 Jaxb marshalling, unmarshalling 대상 필드들에 해당 Adapter가 적용됩니다. 정확히 서비스 중인 소스는 아니지만, 이런 형태로 특정 필드 및 전체 필드에 대한 가공을 하고 있답니다.

필터링 작업

이렇게 1, 2차 가공이 끝난 데이터는 필터링 작업에 들어가게 됩니다. 2차 가공에서도 데이터 정합성이나 기타 조건에 의해 일부 데이터는 제외된 상태에요. 항공권을 검색할 때는 출발지, 도착지, 출발 날짜, 도착 날짜, 왕복, 편도, 직항, 경유, 성인, 유아, 소아, 인원수, 좌석 클래스 등 많은 검색 조건이 있어요. 이런 검색 조건들에 의해 최대 10만개 이상의 스케줄과 100만개 이상의 요금 데이터가 검색 결과로 도출 됩니다. 이런 많은 결과 데이터는 검색을 요청한 사용자가 다 볼 수도 없을 뿐 아니라 무의미한 데이터가 다수 포함되어 있을 가능성이 있어요.

​

그래서 티몬에서는 동일한 조건에서 요금이 다른 요금에 비해 많이 비싸거나, 하나의 스케줄에 동일한 여행사와 요금 조건이 중복 될 경우, 최저가를 제외한 나머지 요금 데이터는 무의미한 데이터라고 판단하여 검색 결과에서 제외하는 필터링을 하고 있어요.

​

각 GDS 검색 결과별로 필터링 및 가공을 거친 스케줄과 요금 정보는 검색 결과 제공을 위한 데이터 통합 및 검색 조건에 따른 정렬과 필터링 작업을 통해 최종 검색 결과 데이터로 만들어져요. 이렇게 최종적으로 정제된 데이터는 검색조건을 기준으로 생성한 Key에 검색결과 리스트를 value로 가지는 Map 형태의 데이터로 카우치베이스에 적재됩니다. 이렇게 카우치베이스에 적재된 데이터를 기준으로 항공권 검색 결과를 제공하고 있어요.

요금 조건과 검색 조건에 의해 필터링이 된 데이터라고 하더라도 하나의 Key에 20MB의 제한이 있는 카우치베이스에 용량이 큰 스케줄과 요금 정보를 한번에 적재하기는 불가능한 경우가 많아요. 그래서 티몬에서는 스케줄 요금 데이터를 GZIP으로 압축 후 20MB가 넘으면 별도로 Paging 처리까지 하여 카우치베이스에 적재하고 있어요. 이런 압축 및 Paging 작업은 티몬 항공권 검색 서비스에 여러 여행사들이 입점하면서 데이터 용량이 너무 커져 서비스 오픈 이후 추가로 작업한 부분이에요. 현재는 스케줄과 요금 데이터를 분리하는 등 데이터 구조를 변경하고 서비스 로직을 수정하여 GZIP압축을 하지 않고 카우치베이스를 이용하는 방향으로 개선 작업 중에 있답니다.

lamanus.kr/80

sparkbyexamples.com/spark/spark-sql-window-functions/

알고리즘 시간 초과가 발생한다면 bufferedReader를 사용해보자.

m.blog.naver.com/PostView.nhn?blogId=occidere&logNo=220811824303&proxyReferer=https:%2F%2Fwww.google.com%2F