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

 

 

[참고자료]

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


클러스터 컬럼스토어 인덱스(Clusterd Columnstore Index)에서 대량 인서트 작업시 발생하는 래치 경합 최소화 트릭

 

·         Version : SQL Server

 

Clustered Columnstore Index(CCI)에서 데이터를 로드 하는 일반적인 시나리오는 오버헤드가 적은 시간 (일반적으로 야간 시간) 활용해서 수백만개의 행이 포함된 데이터 파일을 로드하는 것이다. 권장 되는 배치 사이즈는 batchsize >= 102400으로 데이터를 로드 하는것이다. 배치 사이즈에 대한 내용은 아래 링크를 참고 한다.

·         컬럼스토어 인덱스 대용량 데이터 로드 : http://sqlmvp.kr/220135847446

 

CCI 동시 데이터 스트림을 동일한 델타 그룹으로 허용한다. 그러나 동시성을 높이면 많은 페이지 래치 경합이 발생한다. 델타 RG 내부적으로 클러스터된 b-tree 인덱스로 구성되며 데이터로드는 단순하게 증가하는 클러스터된 인덱스 패턴을 따라 마지막 페이지에서 래치 경합이 발생한다.

·         컬럼스토어 인덱스 INSERT 작업과 동시성 : http://sqlmvp.kr/220132145097

·         컬럼스토어 인덱스 동시성 : http://sqlmvp.kr/220130069090

 

아래 스크립트는 CCI 삽입된 숨겨진 값을 테스트하는 간단한 방법이다.

create table foo (c1 int)

create clustered columnstore index foocci on foo

go

 

insert into foo values (1)

insert into foo values (2)

 

-- check the hidden clustering key index

select %%physloc%% , * from foo

 

 

값은 <rowgroup-id>, <tuple-id> 나타내는 클리스터된 컬럼스토어 인덱스 행의 숨겨진 열을 나타낸다. <tuple-id> 내부적으로 단순하게 증가하는 숫자로 생성되어 동시 삽입시 페이지 래치 경합을 초래한다.

 

이러한 경합 문제를 해결하기 위한 트릭으로Memory optimized 테이블을 사용할 있다.  Memory optimized 테이블을 사용하면PAGE 구조 없이 메모리에 데이터를 유지하도록 구현되므로 마지막 페이지 경합이 제거된다.

 

트릭을 사용하여 Memory optimized 테이블은102400 행보다 청크로 마이그레이션 하여  델타행 그룹을 사용하지 않을 있다.

 

 

[참고자료]

https://blogs.msdn.microsoft.com/sql_server_team/clustered-columnstore-index-massively-parallel-trickle-insert/

 

 

2018-06-11 / Sungwook Kang / http://sqlmvp.kr / http://sqlangeles.com

 

SQL Server, MS SQL, Columnstore Index, 컬럼스토어 인덱스, 인덱스 경합, 래치 경합, page latch contention, 대용량 데이터 로드, bulk data insert

컬럼스토어 인덱스 성능 (Columnsotre Index Performance)

 

  • Version : SQL Server 2012, 2014, 2016

 

SQL Server 2012부터 도입된 컬럼스토어 인덱스는 열 기반 데이터 저장소 및 열 기반 쿼리 처리를 사용하여 데이터를 저장하고 관리한다. 인덱스는 주로 대량 로드 및 전용 쿼리를 수행하는 데이터웨어하우징 작업에 효과적이다. 전체 테이블 검색을 사용하는 쿼리에서는 뛰어난 성능을 제공하지만 특정 값을 찾아 데이터를 검색하는 쿼리에는 부적합 하다.

 

자세한 내용은 MSDN을 참고한다.

 

일반 B-tree 인덱스와 컬럼스토어 인덱스의 성능에 대해서 비교해본다. 아래 스크립트는 테스트를 위한 테이블 생성 및 데이터 생성 스크립트이다. (꽤 오랜 시간동안 테스트 데이터가 생성된다.)

-- Create SampleData table

CREATE TABLE [dbo].[SampleData](

    [RowKey] [int] NOT NULL,

    [CreateDate] [int] NOT NULL,

    [OtherDate] [int] NOT NULL,

    [VarcharColumn1] [varchar](20) NULL,

    [VarcharColumn2] [varchar](20) NULL,

    [VarcharColumn3] [varchar](20) NULL,

    [VarcharColumn4] [varchar](20) NULL,

    [VarcharColumn5] [varchar](20) NULL,

    [IntColumn1] int NULL,

    [IntColumn2] int NULL,

    [IntColumn3] int NULL,

    [IntColumn4] int NULL,

    [IntColumn5] int NULL,

    [IntColumn6] int NULL,

    [IntColumn7] int NULL,

    [IntColumn8] int NULL,

    [IntColumn9] int NULL,

    [IntColumn10] int NULL,

    [FloatColumn1] float NULL,

    [FloatColumn2] float NULL,

    [FloatColumn3] float NULL,

    [FloatColumn4] float NULL,

    [FloatColumn5] float NULL    

)

GO

 

-- Load sample data into table

DECLARE @val INT

SELECT @val=1

WHILE @val < 5000000

BEGIN

INSERT INTO SampleData

VALUES (@val,

CAST(CONVERT(varchar,DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 365),

'2015-01-01'),112) as integer),

CAST(CONVERT(varchar,DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 365),

'2015-01-01'),112) as integer),

'TEST' + cast(round(rand()*100,0) AS VARCHAR),

'TEST' + cast(round(rand()*100,0) AS VARCHAR),

'TEST' + cast(round(rand()*100,0) AS VARCHAR),

'TEST' + cast(round(rand()*100,0) AS VARCHAR),

'TEST' + cast(round(rand()*100,0) AS VARCHAR),

round(rand()*100000,0),

round(rand()*100000,0),     

round(rand()*100000,0),     

round(rand()*100000,0),     

round(rand()*100000,0),

round(rand()*100000,0),

round(rand()*100000,0),

round(rand()*100000,0),

round(rand()*100000,0),

round(rand()*100000,0),

     round(rand()*10000,2),

     round(rand()*10000,2),

     round(rand()*10000,2),

     round(rand()*10000,2),

     round(rand()*10000,2))

SELECT @val=@val+1

END

GO

 

 

데이터 생성이 완료 되었으면 성능 비교를 위해 B-tree 인덱스와 컬럼스토어 인덱스를 생성한다.

-- b-tree index

CREATE NONCLUSTERED INDEX IX_SampleData_Reg

ON SampleData (VarcharColumn1,FloatColumn1);

GO

 

-- column store index

CREATE NONCLUSTERED COLUMNSTORE INDEX IX_SampleData_ColStore

ON SampleData (VarcharColumn1,FloatColumn1);

GO

 

 

각 인덱스를 사용한 쿼리를 실행하여 성능을 측정한다. 실행 측정 결과는 프로파일러를 사용하였다.

-- column store query

SELECT VarcharColumn1,avg(FloatColumn1)

FROM SampleData GROUP BY VarcharColumn1

GO

 

-- b-tree query

SELECT VarcharColumn1,avg(FloatColumn1)

FROM SampleData GROUP BY VarcharColumn1

OPTION (TABLE HINT(SampleData, INDEX (IX_SampleData_Reg)))

GO

 

 

결과를 살펴보면 컬럼스토어 인덱스 성능이 훨씬 낫다는 것을 실험에서 확인할 수 있다.

 

[참고자료]

 

 

2015-09-14 / 강성욱 / http://sqlmvp.kr

 

SQL Server, MSSQL, 컬럼스토어 인덱스, Columnstore Index, 컬럼스토어 인덱스 성능, 열 저장소, 데이터베이스, 컬럼 기반 인덱스

+ Recent posts