[BigQuery & MySQL] Json 컬럼 출력하기

 

오늘은 원티드 프리온보딩 데이터챌린지에서 배웠던 구글 BigQuery의 일부 기능들을 다뤄보려고 합니다.

 

BigQuery 환경 셋팅 방법은 아래 링크에서 더 자세히 다루고 있기 때문에 이 부분은 생략하도록 하겠습니다-!

Google BigQuery (1) 빅쿼리에 데이터를 연결하는 3가지 방법

 

전체 내용은 아래와 같으니 참고 부탁드려요.

1. Json 컬럼 출력하기(BigQuery vs MySQL)
1) BigQuery에서 Json 컬럼 출력하기
2) MySQL에서 Json 컬럼 출력하기
3) 더미데이터 기반 MySQL Json 컬럼 재출력 시도

---

1. Json 컬럼 출력하기(BigQuery vs MySQL)

1) BigQuery에서 Json 컬럼 출력하기

- 우선 BigQuery의 데이터 출력 방법은 MySQL과 달리 from절에 '데이터세트명.테이블명'과 같이 작성해야 함

하지만 프로젝트명을 이미 선택한 상태에서 테이블 조회를 진행한다면, 프로젝트명 제외해도 무방

# 빅쿼리 테이블 조회
SELECT [컬럼명] 
FROM 프로젝트명.데이터세트명.테이블명; -- OR 데이터세트명.테이블명으로 조회 가능

 

- Json 컬럼 출력이 목적이기 때문에, DISTINCT 기능을 이용하여 아래와 같이 출력 진행

BigQuery 출력화면

 

➡️ 출력 조건(예시) : event_property에서 use_skill_filter이 used인 조건만 출력하기

- 해당 컬럼의 경우, json 추출 함수를 통해 아래와 같이 원하는 값 출력 가능

# BigQuery - 조건에 맞는 Json 컬럼 출력하기
SELECT 
	[컬럼명]
FROM 데이터세트명.테이블명
WHERE JSON_EXTRACT_SCALAR(컬럼명, '$.Json경로');

# JSON_EXTRACT_SCALAR(컬럼명)은 JSON 데이터가 포함된 컬럼을 지정해줘야 함
# $ 는JSON 객체의 루트(최상위 레벨)를 나타내며, 특정 필드나 값을 지정하기 위해 사용

 

✅ 출력 결과

JSON_EXTRACT_SCALAR 기반 컬럼 출력

- 즉, WHERE 절에서 JSON 데이터가 포함된 event_property 컬럼 내 use_skill_filter 필드의 값이 'used'와 일치하는 행을 필터링하겠다는 의미로 작성됨

🔸주의사항

- JSON의 데이터 형태에 따라 사용해야할 추출 함수가 달라질 수 있음

> JSON_EXTRACT, JSON_EXTRACT_ARRAY, JSON_EXTRACT_STRING_ARRAY 등

 

- 아래 BigQuery 가이드 문서 또는 'JSON 컬럼 파싱하기' 글 참고 

JSON functions  |  BigQuery  |  Google Cloud

BigQuery - JSON 컬럼 파싱하기

---

2) MySQL에서 Json 컬럼 출력하기

- BigQuery 내 테이블과 동일한 데이터를 사용하여 아래와 같이 진행하고자 함

- USE [테이블명]을 통해 사용할 데이블을 사전 설정하면, FROM절에 테이블명.데이터명으로 명명할 필요 없음

Dbeaver 기반 데이터 조회

 

➡️ 출력 조건(예시) : event_property에서 use_skill_filter이 used인 조건만 출력하기(BigQuery와 동일)

- JSON_EXTRACT 함수 기반 아래와 같이 출력 진행하고자 함

# MySQL - 조건에 맞는 Json 컬럼 출력하기
SELECT 
	[컬럼명]
FROM 테이블명
WHERE JSON_EXTRACT(컬럼명, '$.Json경로');

# JSON_EXTRACT(컬럼명)은 JSON 데이터가 포함된 컬럼을 지정해줘야 함
# $ 는JSON 객체의 루트(최상위 레벨)를 나타내며, 특정 필드나 값을 지정하기 위해 사용

 

*️⃣ BigQuery에서 문제 없이 실행된 코드가 아래와 같은 에러 발생함

코드 자체는 문제 없지만, SQL 에러 발생함

SQL Error [3141] [22001]: Data truncation: Invalid JSON text in argument 1 to function json_extract: "Missing a name for object member." at position 1.
> 유효한 JSON 형식이 아니라고 함. BigQuery에서는 문제 없이 동작되었기 때문에 어떤 부분이 문제인지 gpt를 통해 재확인

 

➡️아래 답변 참고, event_property의 JSON 형태가 쌍따옴표(")가 아닌 따옴표(')로 둘러싸여 있는 것을 확인

- 실제 JSON 데이터라면, {"key": "value"} 형태로 저장되었겠지만, 강의 내 실습을 위한 데이터였기 때문에 발생한 문제임

 

Json 문자열이 따옴표(')로만 둘러싸여 있음

 

➡️JSON_EXTRACT 함수 기반 BigQuery와 동일한 조건으로 출력 가능 유무 확인이 우선이기에 아래와 같이 진행

- REPLACE 함수 기반 따옴표(')를 쌍따옴표(")로 변경 후 데이터 조회 재진행 

 

- REPLACE 함수는 문자열 내에 있는 지정된 문자열을 다른 문자열로 대체하는 데 사용하며 기본 구조는 아래와 같음

# REPLACE 함수 구조
REPLACE(컬럼명, 대체할원본문자열, 대체할문자열)

 

✅ 출력 결과

- REPLACE 함수 기반 event_property에 있는 따옴표를 쌍따옴표로 변경한 후, 조건에 맞는 결과 필터링 함. 정상 출력 확인

 

🔸주의사항

- 문자열 함수를 사용해 데이터를 수정한 방법은 데이터 형식이 일관되어 있을 때 효과적이며, 복잡하거나 예외적인 경우에는 예상치 못한 결과를 초래할 수 있어서 주의 필요

> 프리온보딩 데이터 챌린지에 활용된 데이터는 실제 데이터는 아니어서 REPLACE 함수 사용함

- JSON 데이터의 구조가 복잡하거나 중첩된 경우, 시도한 방법이 실패할 수도 있음
- 데이터의 형식을 변경하는 작업은 항상 주의가 필요하므로, 실제 데이터를 사용한다면 DB 적재 전 확인 과정 필요

---

그렇다면 JSON 데이터 형태가 올바를 경우, MySQL 환경에서 데이터 출력이 정상적으로 이뤄지는지 확인하기 위해 temp 데이블을 생성하여 추가 테스트 진행해보았음

 

3) 더미데이터 기반 MySQL Json 컬럼 재출력 시도

 ➡️ temp 테이블 및 더미 데이터 생성 

# JSON_EXTRACT 함수 동작 확인용 temp 테이블 생성
CREATE TABLE temp (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    address JSON
);

# 더미 데이터 생성
INSERT INTO temp (name, age, address)
VALUES
    ('John Doe', 25, JSON_OBJECT('street', 'Main St 1', 'city', 'New York', 'zip-code', '10001')),
    ('Jane Smith', 30, JSON_OBJECT('street', 'Second St 2', 'city', 'Los Angeles', 'zip-code', '90001')),
    ('Alice Johnson', 28, JSON_OBJECT('street', 'Third St 3', 'city', 'Chicago', 'zip-code', '60001')),
    ('Chris Brown', 32, JSON_OBJECT('street', 'Fourth St 4', 'city', 'Houston', 'zip-code', '77001')),
    ('Emily White', 22, JSON_OBJECT('street', 'Fifth St 5', 'city', 'Phoenix', 'zip-code', '85001'));

 

➡️temp 데이터 확인 결과, 쌍따옴표(")로 둘러싸인 것을 확인함

address 컬럼이 JSON 형태로 들어온 것을 확인

 

✅ JSON_EXTRACT 기반 데이터 조회 결과, 정상 출력확인😀

---

JSON 형태의 데이터를 BigQuery 및 MySQL 환경에서 각각 출력하는 방법을 진행해봤는데요.

의도치 않게 JSON 형태가 쌍따옴표("), 따옴표(')로 둘러싸여 있는지에 따라 데이터 조회 가능 유무를 알게 되었습니다. 

 

동일한 데이터셋을 사용했음에도 BigQuery에서는 조회가 되고, MySQL에서는 안되는 이유에 대해서 추가 조사해봤는데요. 

 

요약하자면 아래와 같은 이유로 데이터를 처리한다고 합니다.

# BigQuery의 JSON 처리 방식
- BigQuery는 JSON 데이터 처리에 있어서 상대적으로 더 유연하며, 비표준 JSON 형식(예: 작은따옴표를 사용한 경우)도 일부 지원할 수 있음

# MySQL의 JSON 처리 방식
- MySQL은 JSON 데이터 형식에 대해 더 엄격하며, JSON 데이터는 반드시 표준 형식을 따라야 함. 이는 모든 문자열이 쌍따옴표로 둘러싸여 있어야 함을 의미하고, 작은따옴표로 둘러싸인 JSON 데이터는 유효한 JSON으로 인식하지 않음

➡️ 즉, BigQuery는 데이터 처리에 있어 유연성을 제공하는 반면, MySQL은 표준 JSON 형식을 엄격히 준수함으로써 데이터의 일관성과 정확성을 보장한다는 특징 있음

 

배웠던 것을 정리하는 차원에서 작성하다가 새로운 지식을 알게되었네요.

역시 공부하면 할수록 새롭게 배우는 것이 많아지는 요즈음입니다 ㅎㅎ 

 

오늘은 간단하게 JSON 추출방식에 대해 정리해봤는데 다음 글에 이어서 배웠던 내용들을 추가적으로 정리할 예정입니다. 

 

긴 글 읽어주셔서 감사합니다-!