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 |
[참고자료]
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]
