SQL Server에 JSON 문서 가져오기

아티클
07/12/2024

적용 대상: SQL Server 2016(13.x) 이상 Azure SQL 데이터베이스 Azure SQL Managed Instance

이 문서에서는 JSON 파일을 SQL Server로 가져오는 방법을 설명합니다. JSON 문서는 애플리케이션 로그, 센서 데이터 등 다양한 유형의 데이터를 저장합니다. 파일에 저장된 JSON 데이터를 읽고, SQL Server에 데이터를 로드하고, 분석할 수 있어야 합니다.

이 문서의 예제에서는 책 목록이 포함된 GitHub 샘플의 JSON 파일을 사용합니다.

사용 권한

인스턴스 수준에서 이 기능을 사용하려면 bulkadmin 고정 서버 역할 또는 ADMINISTER BULK OPERATIONS 사용 권한의 멤버 자격이 필요합니다.

데이터베이스 수준의 경우 이 기능에는 ADMINISTER DATABASE BULK OPERATIONS 사용 권한이 필요합니다.

Azure Blob Storage에 액세스하려면 읽기-쓰기 액세스가 필요합니다.

JSON 문서를 단일 열로 가져오기

OPENROWSET(BULK)는 SQL Server에서 해당 위치에 대한 읽기 권한이 있는 경우 로컬 드라이브 또는 네트워크의 모든 파일에서 데이터를 읽을 수 있는 테이블 반환 함수입니다. 파일의 콘텐츠를 포함된 단일 열이 있는 테이블을 반환합니다. 구분 기호와 같이 OPENROWSET(BULK) 함수와 함께 사용할 수 있는 다양한 옵션이 있습니다. 그러나 가장 간단한 경우 파일의 전체 콘텐츠를 텍스트 값으로 로드할 수 있습니다. (이 단일 큰 값을 단일 문자 LOB(Large Object) 또는 SINGLE_CLOB이라고 합니다.)

다음은 JSON 파일의 콘텐츠를 읽고 단일 값으로 사용자에게 반환하는 OPENROWSET(BULK) 함수의 예입니다.

SELECT BulkColumn
FROM OPENROWSET(BULK 'C:\JSON\Books\book.json', SINGLE_CLOB) as j;

OPENJSON(BULK)은 파일의 콘텐츠를 읽고 BulkColumn에서 반환합니다.

다음 예제에서처럼 파일의 콘텐츠를 지역 변수 또는 테이블로 로드할 수도 있습니다.

-- Load file contents into a variable
DECLARE @json NVARCHAR(MAX);
SELECT @json = BulkColumn
 FROM OPENROWSET(BULK 'C:\JSON\Books\book.json', SINGLE_CLOB) as j

-- Load file contents into a table
SELECT BulkColumn
INTO #temp
FROM OPENROWSET(BULK 'C:\JSON\Books\book.json', SINGLE_CLOB) as j

JSON 파일의 콘텐츠를 로드한 후 JSON 텍스트를 테이블에 저장할 수 있습니다.

Azure 파일 저장소에서 JSON 문서 가져오기

앞에서 설명한 대로 SQL Server에서 액세스할 수 있는 다른 파일 위치에서 JSON 파일을 읽는 데 OPENROWSET(BULK)을 사용할 수도 있습니다. 예를 들어 Azure 파일 저장소는 SMB 프로토콜을 지원합니다. 따라서 다음 절차에 따라 Azure File Storage 공유에 로컬 가상 드라이브를 매핑할 수 있습니다.

Azure Portal 또는 Azure PowerShell을 사용하여 Azure 파일 스토리지에 파일 소토리지 계정(예: mystorage), 파일 공유(예: sharejson) 및 폴더를 만듭니다.
일부 JSON 파일을 파일 스토리지 공유에 업로드합니다.
컴퓨터의 Windows 방화벽에서 포트 445를 허용하는 아웃바운드 방화벽 규칙을 만듭니다. 인터넷 서비스 공급자가 이 포트를 차단할 수 있습니다. 다음 단계에서 DNS 오류(오류 53)가 발생하면 포트 445가 열리지 않거나 ISP가 이를 차단하고 있습니다.
Azure File Storage 공유를 로컬 드라이브(예: T:)로 탑재합니다.

명령 구문은 다음과 같습니다.
```
net use [drive letter] \\[storage name].file.core.windows.net\[share name] /u:[storage account name] [storage account access key]
```
다음은 Azure 파일 저장소 공유에 로컬 드라이브 문자 T:를 할당하는 예제입니다.
```
net use t: \\mystorage.file.core.windows.net\sharejson /u:myaccount hb5qy6eXLqIdBj0LvGMHdrTiygkjhHDvWjUZg3Gu7bubKLg==
```
Azure Portal 설정의 키 섹션에서 스토리지 계정 키와 기본 또는 보조 스토리지 계정 액세스 키를 찾을 수 있습니다.

이제 다음 예제에서처럼 매핑된 드라이브를 사용하여 Azure File Storage 공유에서 JSON 파일에 액세스할 수 있습니다.

SELECT book.*
FROM OPENROWSET(BULK N't:\books\books.json', SINGLE_CLOB) AS json
CROSS APPLY OPENJSON(BulkColumn) WITH (
    id NVARCHAR(100),
    name NVARCHAR(100),
    price FLOAT,
    pages_i INT,
    author NVARCHAR(100)
) AS book

Azure 파일 저장소에 대한 자세한 내용은 파일 저장소를 참조하세요.

Azure Blob Storage에서 JSON 문서 가져오기

적용 대상: SQL Server 2017(14.x) 이상 버전 및 Azure SQL

T-SQL BULK INSERT 명령 또는 OPENROWSET 함수를 사용하여 Azure Blob Storage에서 Azure SQL 데이터베이스로 직접 파일을 로드할 수 있습니다.

먼저 다음 예제와 같이 외부 데이터 원본을 만듭니다.

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://myazureblobstorage.blob.core.windows.net',
    CREDENTIAL = MyAzureBlobStorageCredential
);

다음으로, DATA_SOURCE 옵션을 사용하여 BULK INSERT 명령을 실행합니다.

BULK INSERT Product
FROM 'data/product.dat'
WITH ( DATA_SOURCE = 'MyAzureBlobStorage');

JSON 문서를 행 및 열로 구문 분석

전체 JSON 파일을 단일 값으로 읽는 대신 구문 분석하고 파일의 책과 행 및 열의 해당 속성을 반환할 수 있습니다.

예 1

가장 간단하게 파일에서 전체 목록을 로드할 수 있습니다.

SELECT value
FROM OPENROWSET(BULK 'C:\JSON\Books\books.json', SINGLE_CLOB) AS j
CROSS APPLY OPENJSON(BulkColumn);

앞서 OPENROWSET는 파일에서 단일 텍스트 값을 읽습니다. OPENROWSET는 값을 BulkColumn으로 반환하고 BulkColumn을 OPENJSON 함수에 전달합니다. OPENJSON는 BulkColumn 배열의 JSON 개체 배열을 반복하고 각 행에 하나의 책을 반환합니다. 각 행은 다음에 표시된 것처럼 JSON으로 서식이 지정됩니다.

{"id":"978-0641723445", "cat":["book","hardcover"], "name":"The Lightning Thief", ... }
{"id":"978-1423103349", "cat":["book","paperback"], "name":"The Sea of Monsters", ... }
{"id":"978-1857995879", "cat":["book","paperback"], "name":"Sophie's World : The Greek", ... }
{"id":"978-1933988177", "cat":["book","paperback"], "name":"Lucene in Action, Second", ... }

예제 2

OPENJSON 함수는 JSON 콘텐츠를 구문 분석하고 테이블 또는 결과 집합으로 변환할 수 있습니다. 다음 예제에서는 콘텐츠를 로드하고, 로드된 JSON을 구문 분석하고, 5개의 필드를 열로 반환합니다.

SELECT book.*
FROM OPENROWSET(BULK 'C:\JSON\Books\books.json', SINGLE_CLOB) AS j
CROSS APPLY OPENJSON(BulkColumn) WITH (
    id NVARCHAR(100),
    name NVARCHAR(100),
    price FLOAT,
    pages_i INT,
    author NVARCHAR(100)
) AS book;

이 예제에서는 OPENROWSET(BULK)이 파일의 콘텐츠를 읽고 출력에 대해 정의된 스키마를 사용하여 해당 콘텐츠를 OPENJSON 함수에 전달합니다. OPENJSON은 열 이름을 사용하여 JSON 개체의 속성을 찾습니다. 예를 들어 price 속성은 price 열로 반환되고 float 데이터 형식으로 변환됩니다. 결과는 다음과 같습니다.

ID	속성	price	pages_i	작성자
978-0641723445	번개 도둑	12.5	384	Rick Riordan
978-1423103349	괴물의 바다	6.49	304	Rick Riordan
978-1857995879	소피의 세계: 그리스 철학자	3.07	64	Jostein Gaarder
978-1933988177	실전 루신, 제2판	30.5	475	Michael McCandless

이제 사용자에게 이 테이블을 반환하거나 다른 테이블로 데이터를 로드할 수 있습니다.

다음을 통해 공유

SQL Server에 JSON 문서 가져오기

사용 권한

JSON 문서를 단일 열로 가져오기

Azure 파일 저장소에서 JSON 문서 가져오기

Azure Blob Storage에서 JSON 문서 가져오기

JSON 문서를 행 및 열로 구문 분석

예 1

예제 2

피드백

추가 리소스

다음을 통해 공유

SQL Server에 JSON 문서 가져오기

사용 권한

JSON 문서를 단일 열로 가져오기

Azure 파일 저장소에서 JSON 문서 가져오기

Azure Blob Storage에서 JSON 문서 가져오기

JSON 문서를 행 및 열로 구문 분석

예 1

예제 2

관련 콘텐츠

피드백

추가 리소스