[SQL - Bigquery/빅쿼리] 문자열을 배열로 분해하기(split함수)

빅데이터 분석에서 가장 많이 사용되는 자료형은 문자열이지만 문자열 자료형은 더 세부적으로 분해해서 사용해야 하는 경우가 많다. 이번에는 문자열을 배열로 분해하는 sql 쿼리를 소개하고자 한다

 

 

1. split() 함수

split( '문자열', '구분문자')

 

문자열을 분해하여 배열 형태로 반환해 주는 함수이다. 기본 구분자는 쉼표(,)로 사용된다.

 

 

 

2. split() 함수 예제

2-1) split 함수 예제 1

 

SELECT split('a, b, c, d, e', ',');

 

'a, b, c, d, e'라는 문자열을 분해하고자 한다면 위와 같이 함수를 사용하면 된다.

쉼표(,)를 기준으로 문자를 구분하고, 쿼리 결과를 통해 배열 형태로 반환된 것을 확인할 수 있다.

 

 

 

2-2) split 함수 예제 2

 

사용할 샘플 데이터 테이블이다.

url 경로를 슬래시 단위로 분할하여 계층을 추출해 보는 쿼리문을 작성해 보았다.

 

SELECT stamp, url, regexp_extract(url, '//[^/]+([^?#]+)') AS path,
       split(regexp_extract(url, '//[^/]+([^?#]+)'), '/')[SAFE_ORDINAL(2)] AS path2,
       split(regexp_extract(url, '//[^/]+([^?#]+)'), '/')[SAFE_ORDINAL(3)] AS path3
FROM `data.access_log`;

 

'//[^/]+([^?#]+)' 이 부분은 url 주소에서 호스트 뒷부분을 나타내는 정규표현식이다. 정규표현식은 따로 다루도록 하고, 여기선 저 복잡한 식이 url의 계층 부분을 가리키는 것이라는 것만 알고 넘어가도록 하자.

 

split함수를 이용하여 슬래쉬(/)를 기준으로 문자열을 자르고 인덱싱으로 배열 요소를 추출해 주었다.

 

배열 인덱싱

빅쿼리의 경우, 배열의 인덱스를 0부터 시작하려면 OFFSET 1부터 시작하려면 ORDINAL을 지정해줘야 한다.

배열 길이 이상의 인덱스에 접근하면 오류를 리턴하기 때문에 NULL을 리턴하게 하려면 SAFE_OFFSET 또는 SAFE_ORDINAL을 지정해주어야 한다.

 

 

 

2-3) split 함수 예제 3

 

SELECT ip, cast(split(ip,'.')[safe_ordinal(1)] as int64) as ip_part_1,
           cast(split(ip,'.')[safe_ordinal(2)] as int64) as ip_part_2,
           cast(split(ip,'.')[safe_ordinal(3)] as int64) as ip_part_3,
           cast(split(ip,'.')[safe_ordinal(4)] as int64) as ip_part_4
FROM (select '192.168.0.1' as ip) as t;

 

IP 주소를 정수 자료형으로 변환하는 쿼리문을 작성해 보았다.

점(.) 단위로 문자열을 구분하고 배열 형태로 반환된 것을 확인할 수 있다.

인덱싱을 이용하여 각 배열 요소를 추출해 주었다.

 

* cast(expression AS typename) : cast() 함수는 입력받은 값을 지정한 데이터 유형으로 바꾸어준다.

 

 

지금까지 BigQuery(빅쿼리)의 split 함수에 대해 알아보았다.

감사합니다.

 

 

---

<데이터 분석을 위한 SQL 레시피> 5강, 6강 부분을 참고하여 작성하였습니다.