데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

SQL Server에서 JSON 데이터 저장하기

 

·         Version : SQL Server, Azure SQL

 

SQL Server  Azure SQL에는 표준 SQL 언어를 사용하여 JSON 데이터에 대한 구문을 분석할  있는 네이티브 JSON 함수가 있다그래서 SQL Server JSON 데이터를 저장하고 NoSQL 데이터베이스와 동일하게 JSON 데이터를 쿼리할  있다이번 포스트에서는 SQL Server JSON 데이터를 저장하는 옵션에 대해서 알아본다.

 

SQL Server JSON 데이터를 저장하는 가장 간단한 방법은 고유한 키값과 데이터를 저장하는 2개의 컬럼을 가진 간단한 테이블을 생성하여 사용하는 것이다.

create table WebSite.Logs (

    _id bigint primary key identity,

    log nvarchar(max)

);

 

 구조는 기존의 데이터베이스 모델에서 사용하던것과 동일하다기본키 _id 고유한 식별자로 사용된다  구조는 _id 데이터를 조회하거나 _id 사용하여 업데이트를 해야하는 전형적인  NoSQL 시나리오에 적합하다. NVARCHAR(MAX)유형을 사용하면 최대 2GB 크기의 JSON 데이터를 저장할  있다. JSON 크기가 8KB 초과하지 않는다면NVARCHAR(MAX)대신 NVARCHAR(4000) 사용하는 것이 성능상 좋다.

 

위의 저장방법은 JSON 형식의 데이터가 유효하다는 가정하에 사용할  있다. JSON데이터 형식이  유효한지 확인하려면 테이블에 CHECK 제약조건을 추가  수있다제약조건을 추가하면 데이터가 입력/수정 될때 마다 형식이 올바른지 확인한다.

ALTER TABLE WebSite.Logs

    ADD CONSTRAINT [Log record should be formatted as JSON]

                   CHECK (ISJSON(log)=1)

 

JSON 형식으로 저장된 데이터는 표준 T-SQL 사용하여 아래 예제처럼 JSON 데이터를 쿼리할  있다가장  장점은 T-SQL 함수와 쿼리절을 사용하여 JSON 데이터를 쿼리할  있다는 것이다. SQL Server JSON 데이터를 분석하는데 사용할  있는 쿼리에 제약조건이 없으며 JSON_VALUE 함수를 사용하여 JSON 데이터에서 값을 추출할  있다.

 

SELECT TOP 100 JSON_VALUE(log, '$.severity'), AVG( CAST( JSON_VALUE(log,'$.duration') asfloat))

 FROM WebSite.Logs

 WHERE CAST( JSON_VALUE(log,'$.date') as datetime) > @datetime

 GROUP BY JSON_VALUE(log, '$.severity')

 HAVING AVG( CAST( JSON_VALUE(log,'$.duration') as float) ) > 100

 ORDER BY CAST( JSON_VALUE(log,'$.duration') as float) ) DESC

 

특정 속성값으로 조회가 빈번할 경우 JSON 데이터 컬럼에 NONCLUSTERED 인덱스를 생성하여 검색 속도를 높일수도 있다자주 사용되는 속성값이 있다면 해당 속성에 인덱스를 생성한다 인덱스의  가지 중요한 특징은 데이터 정렬(collation) 인식한다는 것이다.

create table WebSite.Logs (

_id bigint primary key identity,

log nvarchar(max),

 

severity AS JSON_VALUE(log, '$.severity'),

index ix_severity (severity)

);

 

 예제에서 사용된 계산열은 테이블에 추가 공간을 추가하지 않는 가상 열이다인덱스 ix_severity 아래와 같은 쿼리의 성능을 향상시키는데 사용된다.

SELECT log

FROM Website.Logs

WHERE JSON_VALUE(log, '$.severity') = 'P4'

 

대량의 JSON 데이터가 저장되는 테이블 경우  CLUSTERED COLUMNSTORE 인덱스를 추가하는  좋다. CLUSTERED COLUMNSTORE 인덱스는 스토리지 공간 요구사항을 줄여 스토리지 비용을 낮추며 높은 데이터 압축을 사용하여 검색에 대한 I/O 부하를 낮춘다또한 CLUSTERED COLUMNSTORE 인덱스는 JSON 데이터의 테이블 스캔  분석에 최적화 되어 있어 로그 분석에 적합하다.

Create table WebSite.Logs (

_id bigint identity primary key nonclustered,

log nvarchar(4000),

severity AS cast(JSON_VALUE(log, '$.severity') as tinyint) persisted,

index ix_severity (severity)

) with (memory_optimized=on)

 

빈번한 업데이트삽입  삭제 작업이 있다면 JSON 데이터를 In-Memory Optimized 테이블로 사용할  있다메모리 최적회된 JSON 데이터는 항상 메모리에 보관하므로 스토리지에 대한 I/O 오버헤드가 없다.

create table WebSite.Logs (

_id bigint identity primary key nonclustered,

log nvarchar(4000),

 

severity AS cast(JSON_VALUE(log, '$.severity') as tinyint) persisted,

index ix_severity (severity)

 

) with (memory_optimized=on)

 

데이터를 저장할때 네이티브 컴파일 저장프로시저를 사용할수도 있다네이티브 컴파일된 프로시저는 .dll 코드를 생성하여 사용한다.

CREATE PROCEDURE WebSite.UpdateData(@Id int, @Property nvarchar(100), @Valuenvarchar(100))

WITH SCHEMABINDING, NATIVE_COMPILATION

 

AS BEGIN

    ATOMIC WITH (transaction isolation level = snapshot,  language = N'English')

 

    UPDATE WebSite.Logs

    SET log = JSON_MODIFY(log, @Property, @Value)

    WHERE _id = @Id;

 

END

 

 

[참고자료]

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/12/19/storing-json-documents-in-sql-database/

 

 

2018-06-15 / Sungwook Kang / http://sqlmvp.kr

 

SQL Server, Azure SQL, JSON, columnstore index, in-meory optimized 테이블, JSON_VALUE



출처: https://sqlmvp.tistory.com/1255?category=618825 [Database Lab]
No. Subject Author Date Views
2150 SQL Server 2019 에서 업그레이드된sp_estimate_data_compression_savings 프로시저 (컬럼스토어 압축율 예상) jevida(강성욱) 2019.03.26 528
2149 SQL Server 2019 에서 추가된sys.dm_db_page_info, sys.fn_PageResCracker 기능으로 대기 관련 정보 확인 jevida(강성욱) 2019.03.26 766
2148 SQL Server 2019에서 향상된 Rowstore batch mode jevida(강성욱) 2019.03.26 420
2147 SQL Server 2016부터 도입된 USE HINT를 사용한 추적 플래그 활성화 jevida(강성욱) 2019.03.26 439
2146 In-memory optimized table에 사용되는 Hash Index jevida(강성욱) 2019.03.26 390
2145 VM환경에서 AG를 구성하였을때VSS 백업 동작 변경 jevida(강성욱) 2019.03.25 394
2144 SQL Server 2016 향상된 가용성 그룹 – 데이터베이스 수준의 상태 탐지 장애조치 jevida(강성욱) 2019.03.25 419
2143 SQL Server 2016 대용량 데이터 로드시 최소 로깅(minimal logging) 과Batch Size jevida(강성욱) 2019.03.25 309
2142 SQL Server 설치시 발생하는 1638 오류 jevida(강성욱) 2019.03.25 410
2141 SQL Server 666코드의 고유 식별자 오류 jevida(강성욱) 2019.03.25 293
2140 SQL Server AlwaysOn synchronous-commit 환경에서 동기화 레이턴시 트러블슈팅 jevida(강성욱) 2019.03.25 349
2139 SQL Server Scheduling and Yielding 트러블슈팅 jevida(강성욱) 2019.03.25 351
2138 SQL Server 2016 Tempdb 경합(contention) 최적화 jevida(강성욱) 2019.03.25 437
2137 XEvent를 사용하여 Auto tuning 작업 모니터링 jevida(강성욱) 2019.03.25 450
2136 SQL Server In-Memory OLTP에 ASP.NET 세션 상태 저장하기 jevida(강성욱) 2019.03.25 351
» SQL Server에서 JSON 데이터 저장하기 jevida(강성욱) 2019.03.25 424
2134 Azure SQL에서 네트워크를 구성하는 방법 jevida(강성욱) 2019.03.25 231
2133 SQL Server 네이티브 컴파일된 저장 프로시저 성능 모니터링 jevida(강성욱) 2019.03.25 441
2132 SQL Server 2017 소규모 시스템에서 향상된 리소스 사용 jevida(강성욱) 2019.03.25 258
2131 클러스터 컬럼스토어 인덱스(Clusterd Columnstore Index)에서 대량 인서트 작업시 발생하는 래치 경합 최소화 트릭 jevida(강성욱) 2019.03.25 398





XE Login