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') as float)) 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), @Value nvarchar(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
'SQL Server > SQL Server Tip' 카테고리의 다른 글
| XEvent를 사용하여 Auto tuning 작업 모니터링 (0) | 2019.03.25 |
|---|---|
| SQL Server In-Memory OLTP에 ASP.NET 세션 상태 저장하기 (0) | 2019.03.25 |
| Azure SQL에서 네트워크를 구성하는 방법 (0) | 2019.03.25 |
| SQL Server 네이티브 컴파일된 저장 프로시저 성능 모니터링 (0) | 2019.03.25 |
| SQL Server 2017 소규모 시스템에서 향상된 리소스 사용 (0) | 2019.03.25 |
