SQL Server 또는 SQL Database에서 JSON 문서 저장
적용 대상:
SQL Server 2016(13.x) 이상 Azure SQL DatabaseAzure SQL Managed Instance
SQL 데이터베이스 엔진은 표준 SQL 언어를 사용하여 JSON 문서를 구문 분석할 수 있는 네이티브 JSON 함수를 제공합니다. JSON 문서를 SQL Server 또는 SQL Database에 저장하고 JSON 데이터를 NoSQL 데이터베이스에서처럼 쿼리할 수 있습니다. 이 문서에서는 JSON 문서를 저장하는 옵션에 대해 설명합니다.
JSON 스토리지 형식
첫 번째 스토리지 디자인 결정은 테이블에 JSON 문서를 저장하는 방법입니다. 다음과 같은 두 가지 옵션을 사용할 수 있습니다.
LOB 스토리지 - JSON 문서는 데이터 형식이 JSON 또는 NVARCHAR인 열에 있는 그대로 저장할 수 있습니다. 로드 속도가 문자열 열의 로드 속도와 일치하기 때문에 빠른 데이터 로드 및 수집에 이 방법이 가장 적합합니다. 쿼리가 실행되는 동안 원본 JSON 문서를 구문 분석해야 하므로 JSON 값에 대한 인덱싱이 수행되지 않는 경우 이 접근법은 쿼리/분석 시간에 추가적인 성능 저하를 발생시킬 수 있습니다.
관계형 스토리지 - JSON 문서는
OPENJSON,JSON_VALUE또는JSON_QUERY함수를 사용하여 테이블에 삽입하는 동안 구문 분석할 수 있습니다. 입력 JSON 문서의 조각은 JSON 또는 NVARCHAR 데이터 형식의 JSON 하위 요소가 포함된 열에 저장할 수 있습니다. 이 방법을 사용하면 로드 중에 JSON 구문 분석이 수행되기 때문에 로드 시간이 늘어나지만 쿼리 성능은 관계형 데이터에 대한 클래식 쿼리의 성능과 같습니다.현재 SQL Server에서 JSON은 기본 제공 데이터 형식이 아닙니다.
현재 JSON 데이터 형식은 Azure SQL 데이터베이스에서 사용할 수 있습니다.
클래식 테이블
JSON 문서를 SQL Server 또는 Azure SQL 데이터베이스에 저장하는 가장 간단한 방법은 문서의 ID와 문서 내용이 포함된 2열 테이블을 만드는 것입니다. 예시:
create table WebSite.Logs (
[_id] bigint primary key identity,
[log] nvarchar(max)
);
또는 지원되는 위치:
create table WebSite.Logs (
[_id] bigint primary key identity,
[log] json
);
이 구조는 클래식 문서 데이터베이스에서 찾을 수 있는 컬렉션과 같습니다. _id 기본 키는 모든 문서에 대해 고유 식별자를 제공하고 빠른 조회를 가능하게 하는 자동으로 증가하는 값입니다. 이 구조는 ID로 문서를 검색하거나 저장된 문서를 ID로 업데이트하려는 고전적인 NoSQL 시나리오에 적합한 선택입니다.
- JSON 문서를 저장할 수 있는 원시 JSON 데이터 형식을 사용합니다.
- nvarchar(max) 데이터 형식을 사용하면 최대 2GB 크기의 JSON 문서를 저장할 수 있습니다. 그러나 JSON 문서가 8KB보다 크지 않다고 확신하는 경우 성능상의 이유로 NVARCHAR(4000)를 NVARCHAR(max) 대신 사용하는 것이 좋습니다.
앞의 예제에서 만든 샘플 테이블은 유효한 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_VALUE 함수로 JSON 문서에서 값을 추출하고 다른 값과 마찬가지로 쿼리에서 사용할 수 있습니다.
다양한 T-SQL 쿼리 구문을 사용하는 이 기능은 SQL Server, SQL Database, 클래식 NoSQL 데이터베이스 간의 주요 차이점입니다. Transact-SQL에서는 JSON 데이터를 처리하는 데 필요한 기능이 있을 것입니다.
인덱스
쿼리가 일부 속성(예: JSON 문서에서의 severity 속성)으로 문서를 자주 검색하는 경우 속성에 rowstore 비클러스터형 인덱스를 추가하여 쿼리 속도를 높일 수 있습니다.
지정된 경로(즉, 경로 $.severity)의 JSON 열에서 JSON 값을 공개하는 계산 열을 만들고 이 계산 열에 표준 인덱스를 만들 수 있습니다. 예시:
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 문서가 있을 것으로 예상되는 경우 다음 예제처럼 컬렉션에 클러스터형 columnstore 인덱스 추가를 권장합니다.
create sequence WebSite.LogID as bigint;
go
create table WebSite.Logs (
[_id] bigint default(next value for WebSite.LogID),
[log] json,
INDEX cci CLUSTERED COLUMNSTORE
);
클러스터형 columnstore 인덱스는 스토리지 공간 요구 사항을 크게 줄이고 스토리지 비용을 낮추며 워크로드의 I/O 성능을 높일 수 있는 높은 데이터 압축(최대 25배)을 제공합니다. 또한 클러스터형 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] json
) WITH (MEMORY_OPTIMIZED=ON)
메모리 최적화 테이블은 자주 변경되는 문서에 가장 적합한 옵션입니다. 메모리 최적화 테이블을 고려할 때 성능도 고려합니다. 가능한 경우 성능이 크게 향상될 수 있으므로 메모리 최적화 컬렉션의 JSON 문서에 NVARCHAR(4000) 데이터 형식을 NVARCHAR(max) 대신 사용합니다. JSON 데이터 형식은 메모리 최적화 테이블에서 지원되지 않습니다.
클래식 테이블의 경우처럼 계산 열을 사용하여 메모리 최적화 테이블에서 공개된 필드에 인덱스를 추가할 수 있습니다. 예시:
CREATE TABLE WebSite.Logs (
[_id] bigint IDENTITY PRIMARY KEY NONCLUSTERED,
[log] json,
[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 데이터베이스에서의 JSON에 대해 알아보기
SQL Server 및 Azure SQL 데이터베이스에서 기본 제공 JSON 지원에 대한 시각적 소개는 다음 비디오를 참조하세요.
관련 콘텐츠
피드백
출시 예정: 2024년 내내 콘텐츠에 대한 피드백 메커니즘으로 GitHub 문제를 단계적으로 폐지하고 이를 새로운 피드백 시스템으로 바꿀 예정입니다. 자세한 내용은 다음을 참조하세요. https://aka.ms/ContentUserFeedback
다음에 대한 사용자 의견 제출 및 보기