SQL Server 또는 SQL Database에 JSON 문서 저장

아티클
08/23/2023

SQL Server 및 Azure SQL Database에는 표준 SQL 언어를 사용하여 JSON 문서를 구문 분석할 수 있는 네이티브 JSON 함수가 있습니다. JSON 문서를 SQL Server 또는 SQL Database에 저장하고 JSON 데이터를 NoSQL 데이터베이스에서처럼 쿼리할 수 있습니다. 이 문서에서는 JSON 문서를 SQL Server 또는 SQL Database에 저장하는 옵션에 대해 설명합니다.

JSON 스토리지 형식

첫 번째 스토리지 디자인 결정은 테이블에 JSON 문서를 저장하는 방법입니다. 다음과 같은 두 가지 옵션을 사용할 수 있습니다.

LOB 스토리지 - JSON 문서는 열에 NVARCHAR 있는 그대로 저장할 수 있습니다. 이는 로드 속도가 문자열 열의 로드 속도와 일치하기 때문에 빠른 데이터 로드 및 수집에 가장 적합한 방법입니다. 이 방법은 쿼리가 실행되는 동안 원시 JSON 문서를 구문 분석해야 하므로 JSON 값에 대한 인덱싱이 수행되지 않는 경우 쿼리/분석 시간에 추가 성능 저하가 발생할 수 있습니다.
관계형 스토리지 - JSON 문서는 OPENJSON, JSON_VALUE 또는 JSON_QUERY 함수를 사용하여 테이블에 삽입하는 동안 구문 분석할 수 있습니다. 입력 JSON 문서의 조각은 SQL 데이터 형식 열 또는 JSON 하위 요소가 포함된 NVARCHAR 열에 저장할 수 있습니다. 이 방법은 로드 중에 JSON 구문 분석이 수행되므로 로드 시간이 증가합니다. 그러나 쿼리는 관계형 데이터에 대한 클래식 쿼리의 성능과 일치합니다.

클래식 테이블

JSON 문서를 SQL Server 또는 SQL Database에 저장하는 가장 간단한 방법은 문서의 ID와 문서 내용이 포함된 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 문서가 열에 log 저장되어 있다고 가정합니다. 유효한 JSON이 log 열에 저장되었는지 확인하려는 경우 열에 CHECK 제약 조건을 추가할 수 있습니다. 예시:

ALTER TABLE WebSite.Logs
    ADD CONSTRAINT [Log record should be formatted as JSON]
                   CHECK (ISJSON(log)=1)

누군가가 테이블에 문서를 삽입하거나 업데이트할 때마다 이 제약 조건은 JSON 문서의 형식이 올바른지 확인합니다. JSON 문서가 처리 없이 열에 직접 추가되므로 제약 조건이 없으면 테이블이 삽입에 최적화됩니다.

테이블에 JSON 문서를 저장할 때 표준 Transact-SQL 언어를 사용하여 문서를 쿼리할 수 있습니다. 예시:

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 AVG( CAST( JSON_VALUE(log,'$.duration') as float) ) DESC

모든 T-SQL 함수 및 쿼리 절을 사용하여 JSON 문서를 쿼리할 수 있다는 강력한 장점이 있습니다. SQL Server 및 SQL Database는 JSON 문서를 분석하는 데 사용할 수 있는 쿼리에 제약 조건을 도입하지 않습니다. 함수를 사용하여 JSON 문서에서 JSON_VALUE 값을 추출하고 다른 값과 마찬가지로 쿼리에서 사용할 수 있습니다.

다양한 T-SQL 쿼리 구문을 사용하는 이 기능은 SQL Server, SQL Database, 클래식 NoSQL 데이터베이스 간의 주요 차이점입니다. Transact-SQL에서는 JSON 데이터를 처리하는 데 필요한 기능이 있을 것입니다.

인덱스

쿼리가 일부 속성(예 severity : JSON 문서의 속성)으로 문서를 자주 검색하는 경우 속성에 클래식 NONCLUSTERED 인덱스를 추가하여 쿼리 속도를 높일 수 있습니다.

지정된 경로(경로)의 JSON 열에서 JSON 값을 노출하는 계산 열을 만들고 이 계산 열에 $.severity표준 인덱스를 만들 수 있습니다. 예시:

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'

이 인덱스의 한 가지 중요한 특징은 데이터 정렬을 인식한다는 것입니다. 원래 NVARCHAR 열에 COLLATION 속성(예: 대/소문자 구분 또는 일본어)이 있는 경우 인덱스는 NVARCHAR 열과 연결된 언어 규칙 또는 대/소문자 구분 규칙에 따라 구성됩니다. JSON 문서를 처리할 때 사용자 지정 언어 규칙을 사용해야 하는 글로벌 시장용 애플리케이션을 개발하는 경우 이 데이터 정렬 인식은 중요한 기능일 수 있습니다.

큰 테이블 및 columnstore 형식

컬렉션에 많은 수의 JSON 문서가 있을 것으로 예상되는 경우 다음 예제와 같이 컬렉션에 CLUSTERED COLUMNSTORE 인덱스 추가를 권장합니다.

create sequence WebSite.LogID as bigint;
go
create table WebSite.Logs (
    _id bigint default(next value for WebSite.LogID),
    log nvarchar(max),

    INDEX cci CLUSTERED COLUMNSTORE
);

CLUSTERED COLUMNSTORE 인덱스는 스토리지 공간 요구 사항을 크게 줄이고 스토리지 비용을 낮추며 워크로드의 I/O 성능을 높일 수 있는 높은 데이터 압축(최대 25배)을 제공합니다. 또한 CLUSTERED COLUMNSTORE 인덱스는 JSON 문서의 테이블 검색 및 분석에 최적화되므로 이러한 유형의 인덱스가 로그 분석에 가장 적합한 옵션일 수 있습니다.

앞의 예에서는 시퀀스 개체를 사용하여 _id 열에 값을 할당합니다. 시퀀스 및 ID 모두 이 ID 열에 대해 유효한 옵션입니다.

자주 변경되는 문서 및 메모리 최적화 테이블

컬렉션에서 많은 업데이트, 삽입 및 삭제 작업이 실행되도록 하려면 JSON 문서를 메모리 최적화 테이블에 저장할 수 있습니다. 메모리 최적화 JSON 컬렉션은 항상 데이터를 메모리에 유지하므로 스토리지 I/O 오버헤드가 없습니다. 또한 메모리 최적화 JSON 컬렉션은 완전히 잠금 해제됩니다. 즉, 문서에 대한 작업이 다른 작업을 차단하지 않습니다.

클래식 컬렉션을 메모리 최적화 컬렉션으로 변환해야 하는 유일한 작업은 다음 예제와 같이 테이블 정의 다음에 with(memory_optimized=on) 옵션을 지정하는 것입니다. 그런 다음 메모리 최적화 버전의 JSON 컬렉션이 있습니다.

create table WebSite.Logs (
  _id bigint identity primary key nonclustered,
  log nvarchar(4000)
) with (memory_optimized=on)

메모리 최적화 테이블은 자주 변경되는 문서에 가장 적합한 옵션입니다. 메모리 최적화 테이블을 고려할 때 성능도 고려합니다. 가능한 경우 성능이 크게 향상될 수 있으므로 메모리 최적화 컬렉션의 JSON 문서에 NVARCHAR(max) 대신 NVARCHAR(4000)를 사용합니다.

클래식 테이블과 마찬가지로 계산 열을 사용하여 메모리 최적화 테이블에서 노출하는 필드에 인덱스를 추가할 수 있습니다. 예시:

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 값을 속성 값을 보유하는 데 사용할 수 있는 가장 작은 형식으로 캐스팅합니다. 앞의 예제 에서는 tinyint 가 사용됩니다.

JSON 문서를 저장 프로시저에 업데이트하는 SQL 쿼리를 배치하여 네이티브 컴파일의 이점을 얻을 수도 있습니다. 예시:

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

고유하게 컴파일된 이 프로시저는 쿼리를 사용하고 쿼리를 실행하는 .DLL 코드를 만듭니다. 고유하게 컴파일된 프로시저는 데이터를 쿼리하고 업데이트하기 위한 보다 빠른 방법입니다.

결론

SQL Server 및 SQL Database의 네이티브 JSON 함수를 사용하면 NoSQL 데이터베이스와 마찬가지로 JSON 문서를 처리할 수 있습니다. 관계형 또는 NoSQL인 모든 데이터베이스에는 JSON 데이터 처리에 대한 몇 가지 장단점이 있습니다. JSON 문서를 SQL Server 또는 SQL Database에 저장하는 주요 이점은 완전한 SQL 언어 지원입니다. 풍부한 Transact-SQL 언어를 사용하여 데이터를 처리하고 다양한 스토리지 옵션을 구성할 수 있습니다(높은 압축 및 빠른 분석을 위한 columnstore 인덱스에서 잠금 없는 처리를 위한 메모리 최적화 테이블에 이르기까지). 동시에 NoSQL 시나리오에서 쉽게 재사용할 수 있는 성숙한 보안 및 국제화 기능의 이점을 누릴 수 있습니다. 이 문서에 설명된 이유는 SQL Server 또는 SQL Database에 JSON 문서를 저장하는 것을 고려하는 훌륭한 이유입니다.

SQL Server 및 Azure SQL Database의 JSON에 대해 자세히 알아보기

Microsoft 비디오

참고 항목

이 섹션의 일부 비디오 링크는 현재 작동하지 않을 수 있습니다. Microsoft는 이전에 Channel 9에 있던 콘텐츠를 새 플랫폼으로 마이그레이션하고 있습니다. 비디오가 새 플랫폼으로 마이그레이션되면 링크를 업데이트할 예정입니다.

SQL Server 및 Azure SQL Database의 기본 제공 JSON 지원에 대한 시각적 소개는 다음 비디오를 참조하세요.

NoSQL과 관계형 세계 간의 브리지로서의 JSON