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
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 13606
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 8407
2134 Azure SQL에서 네트워크를 구성하는 방법 jevida(강성욱) 2019.03.25 274
2133 SQL Server 네이티브 컴파일된 저장 프로시저 성능 모니터링 jevida(강성욱) 2019.03.25 555
2132 SQL Server 2017 소규모 시스템에서 향상된 리소스 사용 jevida(강성욱) 2019.03.25 304
2131 클러스터 컬럼스토어 인덱스(Clusterd Columnstore Index)에서 대량 인서트 작업시 발생하는 래치 경합 최소화 트릭 jevida(강성욱) 2019.03.25 455
2130 클러스터 컬럼스토어 인덱스(Clusterd Columnstore Index)에서 대량 인서트 작업시 발생하는 래치 경합 최소화 트릭 jevida(강성욱) 2019.03.25 444
2129 SQL Server 2016 향상된 복제 기능 – 배포 데이터베이스 클린업 향상 jevida(강성욱) 2019.03.25 354
2128 SQL Server 2017향상된 복제 기능 – 배포 데이터베이스의 AG 지원 jevida(강성욱) 2019.03.25 500
2127 SQL Server 2017 향상된 복제 기능 - 복제에이전트 프로필 매개변수의 동적 새로 고침 jevida(강성욱) 2019.03.25 541
2126 SQL Server update on Docker jevida(강성욱) 2019.03.25 438
2125 SQL Server 2017에서 향상된 UDF 실행 계획 jevida(강성욱) 2019.03.25 409
2124 ETL data error with MariaDB ODBC 3.0 (from Aurora to MS SQL) jevida(강성욱) 2019.03.25 582
2123 Run the SQL Server 2017 with Docker jevida(강성욱) 2019.03.25 495
2122 SQL Server Configuration Manager 실행 오류 (WMI 공급자 연결 오류) jevida(강성욱) 2019.03.25 609
2121 BULK INSERT 동시에 여러개 실행 향지 2019.01.30 1049
2120 대용량 데이터 조인에 대한 고민 ( Hash Join VS Nested Loop Join ) 향지 2018.04.04 4507
2119 How to check if Azure SQL is using In-Memory jevida(강성욱) 2018.03.31 3305
2118 MSSQL-CLI를 활용한 크로스플랫폼에서 SQL Server 관리하기 jevida(강성욱) 2018.03.31 3674
2117 SSMS – Search for execution plan jevida(강성욱) 2018.03.31 3798
2116 SQL Server Parallelism and Wait change (CXAPCKET, CXCONSUMER) jevida(강성욱) 2018.03.31 4090
2115 SQL Server Statistics Update row sampling rate and histogram step jevida(강성욱) 2018.03.31 3727





XE Login