카테고리 없음

[MySQL vs Hive] 문자열 처리 SQL 함수 총정리

glasslego 2025. 3. 6. 20:00

소개

데이터 분석이나 ETL 과정을 진행하다 보면 문자열 처리가 매우 잦습니다. 예를 들어, 불필요한 공백을 제거하거나, 특정 패턴을 추출하거나, 문자열을 대소문자로 변환하는 등의 작업이 필요하죠.

이번 포스팅에서는 흔히 쓰이는 문자열 처리 SQL 함수를 MySQL과 Apache Hive에서 어떻게 사용하는지 간단하게 정리하겠습니다.

1. 문자열 길이 함수

1.1 MySQL: LENGTH()

  • 문법: 
    SELECT LENGTH('Hello World');
  • 설명:
    • 문자열의 바이트(byte) 길이를 반환합니다.
    • 멀티바이트 문자가 포함된 문자열은 주의가 필요합니다. 예를 들어 UTF-8 인코딩에서 한글은 3바이트로 계산되므로, 한글 한 글자의 length는 3이 됩니다.
  • 예시: 
    SELECT LENGTH('가나다');  -- 9 (UTF-8에서는 한 글자당 3바이트)

1.2 MySQL: CHAR_LENGTH()

  • 문법: 
    SELECT CHAR_LENGTH('Hello World');
  • 설명:
    • 문자열의 문자(character) 길이를 반환합니다. 즉, 한글이 3바이트여도 “가나다”는 3을 반환합니다.
    • 멀티바이트 문자를 정확히 세고 싶을 때 사용합니다.
  • 예시: 
    SELECT CHAR_LENGTH('가나다');  -- 3

1.3 Hive: length(string)

  • 문법: 
    SELECT length('Hello World') FROM some_table LIMIT 1;
  • 설명:
    • Hive에서 length() 함수는 문자열의 문자 수를 반환합니다.
    • 다만 Hive 버전에 따라 멀티바이트 문자를 어떻게 처리하는지 다를 수 있습니다. 보통 UTF-8을 가정할 때, 한글 한 글자를 3으로 세는 경우도 있으니, 테스트가 필요합니다.
  • 예시: 
    SELECT length('Hello') AS len FROM dual_table;
-- 결과: 5

Tip: Hive에서는 멀티바이트 처리를 주의해야 합니다. 만약 “가나다”를 넣었을 때 3이 아닌 9가 나오면, Hive 버전 혹은 설정 문제일 수 있습니다.

2. 대소문자 변환

2.1 MySQL

  • UPPER() / LOWER() 
    SELECT UPPER('hello world');  -- HELLO WORLD
SELECT LOWER('HELLO WORLD');  -- hello world

2.2 Hive

  • upper(string) / lower(string) 
    SELECT upper('hive world'), lower('HIVE WORLD') FROM some_table;

두 엔진 모두 대소문자 변환 함수 사용법이 거의 동일합니다.

3. 문자열 잘라내기(Substring)

3.1 MySQL: SUBSTR() 혹은 SUBSTRING()

  • 문법: 
    SELECT SUBSTR('Hello World', 1, 5);
SELECT SUBSTRING('Hello World', 1, 5);
  • 설명:
    • (문자열, 시작 위치, 길이) 순서로 자릅니다.
    • 시작 위치가 1부터 시작함에 유의 (0부터 시작 아님).
  • 예시: 
    SELECT SUBSTRING('Hello World', 2, 3); -- ell

3.2 Hive: substr(string, pos, len)

  • 문법: 
    SELECT substr('Hello World', 1, 5) FROM some_table;
  • 설명:
    • MySQL과 거의 동일.
    • Hive도 시작 인덱스를 1로 합니다.
  • 예시: 
    SELECT substr('Hello World', 7, 5); -- World

4. 문자열 결합

4.1 MySQL: CONCAT()

  • 문법: 
    SELECT CONCAT('Hello', ' ', 'World');
  • 설명:
    • 여러 문자열을 순서대로 이어 붙여서 하나의 문자열로 만듭니다.
    • NULL이 하나라도 있으면 결과가 NULL이 됩니다(주의).

4.2 Hive: concat()

  • 문법: 
    SELECT concat('Hello', ' ', 'Hive') FROM some_table;
  • 설명:
    • MySQL CONCAT()와 동일하게 작동합니다.
    • NULL 처리도 동일.

5. 공백 제거 (Trim)

5.1 MySQL

  • LTRIM(): 왼쪽 공백 제거
  • RTRIM(): 오른쪽 공백 제거
  • TRIM(): 양쪽 공백 제거 
    SELECT TRIM('    Hello   ');
-- 결과: 'Hello'

5.2 Hive

  • ltrim(string), rtrim(string), trim(string) 
    SELECT trim('   Hive   ') FROM some_table;
-- 결과: 'Hive'
  • Hive도 사용법은 동일합니다.

6. 문자열 패턴 찾기/대체

6.1 MySQL: REPLACE()

  • 문법: 
    REPLACE(str, from_str, to_str)
  • 설명:
    • str에서 from_str 모든 occurrences를 to_str로 변경합니다.
  • 예시: 
    SELECT REPLACE('Hello World', 'World', 'MySQL'); 
-- Hello MySQL

6.2 Hive: regexp_replace(string, pattern, replacement)

  • 문법: 
    regexp_replace(column, 'regex_pattern', 'replacement')
  • 설명:
    • 정규표현식을 사용해 일치하는 모든 부분을 치환합니다.
  • 예시: 
    SELECT regexp_replace('Hello 123 World', '\\d+', '###') 
-- 결과: Hello ### World

7. 정규표현식 처리 (Regex Functions)

7.1 MySQL

  • REGEXP 사용
    예: WHERE column REGEXP '^[0-9]+$'
    • SELECT나 WHERE 조건 등에서 간단히 정규표현식을 쓸 수 있습니다.
  • RLIKE: REGEXP와 동일 동작 (Synonym)

7.2 Hive

  • regexp_extract(string subject, string pattern, int index)
    • 정규표현식으로 매칭된 그룹을 추출
    SELECT regexp_extract('abc-1234', '(\\w+)-(\\d+)', 1) AS first_part
-- 결과: 'abc'

SELECT regexp_extract('abc-1234', '(\\w+)-(\\d+)', 2) AS second_part
-- 결과: '1234'
  • regexp_extract_all(string subject, string pattern)
    • Hive 2.x 이상에서 지원, 모든 매칭 결과를 배열 형태로 반환

8. 문자열을 숫자로 변환 또는 형 변환

8.1 MySQL

  • CAST(expr AS type): 
    SELECT CAST('123' AS UNSIGNED);
SELECT CAST('123.45' AS DECIMAL(5,2));
  • CONVERT(expr, type): CAST와 유사

8.2 Hive

  • CAST(expr AS INT / DOUBLE / ...): 
    SELECT CAST('123' AS INT);
SELECT CAST('123.45' AS FLOAT);
  • Hive에서는 int, bigint, float, double, decimal 등으로 변환할 수 있음

9. 그 외 참고 사항

  1. NULL 처리: MySQL과 Hive 모두 문자열 함수에 NULL이 들어가면 결과 NULL인 경우가 많습니다. COALESCE() 등을 이용해 NULL을 미리 처리하거나, IFNULL()(MySQL)로 대체값을 지정하는 것이 좋습니다.
  2. 인덱스 / 성능: 문자열 함수를 WHERE 절이나 JOIN 조건에서 자주 쓰면 인덱스를 타지 않아 성능에 영향이 있을 수 있습니다. 대규모 테이블에서 문자열 함수를 호출하기 전에 컬럼 가공을 해두는 전략이 필요합니다.
  3. Hive 버전 호환성: Hive는 버전에 따라 지원 함수가 달라지거나, 멀티바이트 문자열 처리 방식이 달라질 수 있습니다. 반드시 사용할 Hive 버전의 문서나 테스트 결과를 확인하세요.

결론

  • MySQL과 Hive 모두 기본적인 문자열 처리 함수는 유사합니다. length, substr, concat, trim, upper/lower 등.
  • MySQL은 CHAR_LENGTH() / LENGTH()로 문자 수와 바이트 수를 구분하며, REPLACE()가 간단한 문자열 치환을 담당합니다. 정규표현식은 REGEXP를 조건식에서 사용.
  • Hive는 정규표현식 관련해서 regexp_replace나 regexp_extract 등 강력한 함수를 제공하지만, 멀티바이트 문자열 처리에 주의가 필요합니다.
  • 프로젝트 환경에서 어떤 DBMS/Hive 버전을 쓰는지 미리 파악하고, 일부 함수의 동작 차이를 테스트하는 것을 추천합니다.

마무리

이번 포스팅에서는 MySQL과 Hive에서 자주 쓰이는 문자열 처리 함수들을 간단히 살펴봤습니다. 텍스트 데이터를 다루는 환경이 늘면서 SQL 레벨에서의 문자열 전처리가 점차 중요해지고 있습니다.

  • 핵심 요약:
    • DB 엔진마다 기본적인 함수 이름은 유사 (substr, length, trim, etc.)
    • 멀티바이트, NULL, 정규표현식 지원 여부, 함수별 인덱스 활용 가능 여부 등을 확인해야 함.
    • Hive는 버전에 따라 함수 지원 범위가 다르니 레퍼런스를 반드시 참고하기.

궁금한 점이나 추가로 다뤄줬으면 하는 내용 있으면 댓글 남겨주세요! 감사합니다.

참고 자료

저작자표시 비영리 변경금지 (새창열림)

'카테고리 없음'의 다른글

  • 현재글[MySQL vs Hive] 문자열 처리 SQL 함수 총정리

티스토리툴바