SQL Server에 JSON 문서 가져오기

  • 아티클

적용 대상: SQL Server 2016(13.x) 이상 Azure SQL DatabaseAzure SQL Managed Instance

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

사용 권한

인스턴스 수준에서 이 기능을 사용하려면 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 File Storage에서 JSON 문서 가져오기

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

  1. Azure Portal 또는 Azure PowerShell을 사용하여 파일 스토리지 계정(예 mystorage: 파일 공유), 파일 공유(예 sharejson: Azure File Storage) 및 폴더를 만듭니다.

  2. 일부 JSON 파일을 파일 스토리지 공유에 업로드합니다.

  3. 컴퓨터의 Windows 방화벽에서 포트 445를 허용하는 아웃바운드 방화벽 규칙을 만듭니다. 인터넷 서비스 공급자가 이 포트를 차단할 수 있습니다. 다음 단계에서 DNS 오류(오류 53)가 발생하면 포트 445를 열지 않았거나 ISP가 이를 차단하고 있습니다.

  4. 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 File Storage 공유에 로컬 드라이브 문자를 T: 할당하는 예제입니다.

    net use t: \\mystorage.file.core.windows.net\sharejson /u:myaccount hb5qy6eXLqIdBj0LvGMHdrTiygkjhHDvWjUZg3Gu7bubKLg==

    Azure Portal의 설정 키 섹션에서 스토리지 계정 키 및 기본 또는 보조 스토리지 계정 액세스 키를 찾을 수 있습니다.

  5. 이제 다음 예제에서처럼 매핑된 드라이브를 사용하여 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 File Storage에 대한 자세한 내용은 File Storage를 참조하세요.

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

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

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

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

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 파일을 단일 값으로 읽는 대신 구문 분석하고 파일의 책과 행 및 열의 해당 속성을 반환할 수 있습니다. 다음 예제에서는 이 사이트에서 책 목록이 포함된 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 Lucene in Action, Second Edition 30.5 475 Michael McCandless

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

참고 항목