SQL Server에 JSON 문서 가져오기Import JSON documents into SQL Server

이 항목은 다음에 적용됩니다. 예SQL Server(2016부터)예Azure SQL Database아니요Azure SQL Data Warehouse아니요병렬 데이터 웨어하우스 THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

이 항목에서는 SQL Server에 JSON 파일을 가져오는 방법을 설명합니다.This topic describes how to import JSON files into SQL Server. 현재 많은 JSON 문서가 파일로 저장됩니다.Currently there are lots of JSON documents stored in files. JSON 파일의 응용 프로그램 로그 정보, JSON 파일에 저장된 센서 생성 정보 등을 예로 들 수 있습니다.Applications log information in JSON files, sensors generate information that's stored in JSON files, and so forth. 파일에 저장된 JSON 데이터를 읽고, 데이터를 SQL Server에 로드하여 분석할 수 있어야 합니다.It's important to be able to read the JSON data stored in files, load the data into SQL Server, and analyze it.

단일 열에 JSON 문서 가져오기Import a JSON document into a single column

OPENROWSET(BULK)는 SQL Server에 해당 위치에 대한 읽기 권한이 있는 경우 로컬 드라이브 또는 네트워크에 있는 모든 파일에서 데이터를 읽을 수 있는 테이블 반환 함수입니다.OPENROWSET(BULK) is a table-valued function that can read data from any file on the local drive or network, if SQL Server has read access to that location. 파일의 콘텐츠를 포함하는 단일 열로 된 테이블을 반환합니다.It returns a table with a single column that contains the contents of the file. 구분 기호 등 OPENROWSET(BULK) 함수에서 사용할 수 있는 다양한 옵션이 있습니다.There are various options that you can use with the OPENROWSET(BULK) function, such as separators. 하지만 가장 간단하게 파일의 전체 콘텐츠를 텍스트 값으로 로드할 수 있습니다.But in the simplest case, you can just load the entire contents of a file as a text value. (이 단일 큰 값을 단일 문자 LOB(Large Object) 또는 SINGLE_CLOB이라고 합니다.)(This single large value is known as a single character large object, or SINGLE_CLOB.)

다음은 JSON 파일의 콘텐츠를 읽고 단일 값으로 사용자에게 반환하는 OPENROWSET(BULK) 함수의 예입니다.Here's an example of the OPENROWSET(BULK) function that reads the contents of a JSON file and returns it to the user as a single value:

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

OPENJSON(BULK)이 파일의 콘텐츠를 읽고 BulkColumn에 반환합니다.OPENJSON(BULK) reads the content of the file and returns it in BulkColumn.

다음 예제에서처럼 파일의 콘텐츠를 지역 변수 또는 테이블로 로드할 수도 있습니다.You can also load the contents of the file into a local variable or into a table, as shown in the following example:

-- Load file contents into a variable
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 텍스트를 테이블에 저장할 수 있습니다.After loading the contents of the JSON file, you can save the JSON text in a table.

여러 개의 JSON 문서 가져오기Import multiple JSON documents

같은 방법을 사용하여 파일 시스템에서 JSON 파일 집합을 지역 변수로 한 번에 하나씩 로드할 수 있습니다.You can use the same approach to load a set of JSON files from the file system into a local variable one at a time. 파일 이름을 book<index>.json이라고 가정합니다.Assume that the files are named book<index>.json.

DECLARE @i INT = 1
DECLARE @json AS NVARCHAR(MAX)

WHILE(@i < 10)
BEGIN
    SET @file = 'C:\JSON\Books\book' + cast(@i AS VARCHAR(5)) + '.json';
    SELECT @json = BulkColumn FROM OPENROWSET (BULK (@file), SINGLE_CLOB) AS j
    SELECT * FROM OPENJSON(@json) AS json
    -- Optionally, save the JSON text in a table.
    SET @i = @i + 1 ;
END

Azure File Storage에서 JSON 문서 가져오기Import JSON documents from Azure File Storage

위에서 설명한 대로 OPENROWSET(BULK)을 사용하여 SQL Server가 액세스할 수 있는 파일 위치에서 JSON 파일을 읽을 수도 있습니다.You can also use OPENROWSET(BULK) as described above to read JSON files from other file locations that SQL Server can access. 예를 들어 Azure File Storage는 SMB 프로토콜을 지원합니다.For example, Azure File Storage supports the SMB protocol. 따라서 다음 절차에 따라 Azure File Storage 공유에 로컬 가상 드라이브를 매핑할 수 있습니다.As a result you can map a local virtual drive to the Azure File storage share using the following procedure:

  1. Azure Portal 또는 Azure PowerShell을 사용하여 Azure File Storage에 파일 저장소 계정(예: mystorage), 파일 공유(예: sharejson) 및 폴더를 만듭니다.Create a file storage account (for example, mystorage), a file share (for example, sharejson), and a folder in Azure File Storage by using the Azure portal or Azure PowerShell.
  2. 파일 저장소 공유에 일부 JSON 파일을 업로드합니다.Upload some JSON files to the file storage share.
  3. 포트 445를 허용하는 컴퓨터에서 Windows 방화벽에 아웃바운드 방화벽 규칙을 만듭니다.Create an outbound firewall rule in Windows Firewall on your computer that allows port 445. 인터넷 서비스 공급자가 이 포트를 차단할 수 있습니다.Note that your Internet service provider may block this port. 다음 단계에서 DNS 오류(오류 53)가 발생하면 포트 445를 열지 않았거나 ISP가 이 포트를 차단하고 있는 것입니다.If you get a DNS error (error 53) in the following step, then you have not opened port 445, or your ISP is blocking it.
  4. Azure File Storage 공유를 로컬 드라이브(예: T:)로 탑재합니다.Mount the Azure File Storage share as a local drive (for example T:).

    명령 구문은 다음과 같습니다.Here is the command syntax:

    net use [drive letter] \\[storage name].file.core.windows.net\[share name] /u:[storage account name] [storage account access key]
    

    다음은 Azure File Storage 공유에 로컬 드라이브 문자 T:를 할당하는 예제입니다.Here's an example that assigns local drive letter T: to the Azure File Storage share:

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

    Azure Portal에서 설정의 키 섹션에서 저장소 계정 키 및 기본 또는 보조 저장소 계정 액세스 키를 찾을 수 있습니다.You can find the storage account key and the primary or secondary storage account access key in the Keys section of Settings in the Azure portal.

  5. 이제 다음 예제에서처럼 매핑된 드라이브를 사용하여 Azure File Storage 공유에서 JSON 파일에 액세스할 수 있습니다.Now you can access your JSON files from the Azure File Storage share by using the mapped drive, as shown in the following example:

    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에 대한 자세한 내용은 파일 저장소를 참조하세요.For more info about Azure File Storage, see File storage.

Azure Blob Storage에서 JSON 문서 가져오기Import JSON documents from Azure Blob Storage

T-SQL BULK INSERT 명령 또는 OPENROWSET 함수를 사용하여 Azure Blob Storage에서 Azure SQL Database로 직접 파일을 로드할 수 있습니다.You can load files directly into Azure SQL Database from Azure Blob Storage with the T-SQL BULK INSERT command or the OPENROWSET function.

먼저 다음 예제와 같이 외부 데이터 원본을 만듭니다.First, create an external data source, as shown in the following example.

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

그런 다음 DATA_SOURCE 옵션을 사용하여 BULK INSERT 명령을 실행합니다.Next, run a BULK INSERT command with the DATA_SOURCE option.

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

자세한 내용 및 OPENROWSET 사용 예제는 Loading files from Azure Blob Storage into Azure SQL Database(Azure Blob Storage에서 Azure SQL Database로 파일 로드)를 참조하세요.For more info and an example that uses OPENROWSET, see Loading files from Azure Blob Storage into Azure SQL Database.

행과 열로 JSON 문서 구문 분석Parse JSON documents into rows and columns

단일 값으로 전체 JSON 파일을 읽지 않고 파일을 구문 분석하여 파일의 책과 해당 속성을 행과 열에 반환할 수 있습니다.Instead of reading an entire JSON file as a single value, you may want to parse it and return the books in the file and their properties in rows and columns. 다음 예제에서는 이 사이트에서 책 목록이 포함된 JSON 파일을 사용합니다.The following example uses a JSON file from this site containing a list of books.

예제 1Example 1

가장 간단하게 파일에서 전체 목록을 로드할 수 있습니다.In the simplest example, you can just load the entire list from the file.

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

예제 2Example 2

OPENROWSET는 파일에서 단일 텍스트 값을 읽어 BulkColumn으로 반환하고 OPENJSON 함수에 전달합니다.OPENROWSET reads a single text value from the file, returns it as a BulkColumn, and passes it to the OPENJSON function. OPENJSON은 BulkColumn 배열에서 JSON 개체의 배열을 반복하고 각 행에 JSON으로 형식이 지정된 한 권의 책을 반환합니다.OPENJSON iterates through the array of JSON objects in the BulkColumn array and returns one book in each row, formatted as 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 … 

예 3Example 3

OPENJSON 함수는 JSON 콘텐츠를 구문 분석하여 테이블 또는 결과 집합으로 변환할 수 있습니다.The OPENJSON function can parse the JSON content and transform it into a table or a result set. 다음 예제에서는 콘텐츠를 로드하고 로드된 JSON을 구문 분석하여 5개의 필드를 열로 반환합니다.The following example loads the content, parses the loaded JSON, and returns the five fields as columns:

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 함수에 전달합니다.In this example, OPENROWSET(BULK) reads the content of the file and passes that content to the OPENJSON function with a defined schema for the output. OPENJSON은 열 이름을 사용하여 JSON 개체의 속성을 찾습니다.OPENJSON matches properties in the JSON objects by using column names. 예를 들어 price 속성은 price 열로 반환되고 float 데이터 형식으로 변환됩니다.For example, the price property is returned as a price column and converted to the float data type. 결과는 다음과 같습니다.Here are the results:

IdId 이름Name priceprice pages_ipages_i 작성자Author
978-0641723445978-0641723445 번개 도둑The Lightning Thief 12.512.5 384384 Rick RiordanRick Riordan
978-1423103349978-1423103349 몬스터 바다The Sea of Monsters 6.496.49 304304 Rick RiordanRick Riordan
978-1857995879978-1857995879 소피의 세계: 그리스 철학자Sophie’s World : The Greek Philosophers 3.073.07 6464 Jostein GaarderJostein Gaarder
978-1933988177978-1933988177 루씬 인 액션(개정판)Lucene in Action, Second Edition 30.530.5 475475 Michael McCandlessMichael McCandless

이제 사용자에게 이 테이블을 반환하거나 다른 테이블로 데이터를 로드할 수 있습니다.Now you can return this table to the user, or load the data into another table.

SQL Server의 기본 제공 JSON 지원에 대한 자세한 정보Learn more about the built-in JSON support in SQL Server

많은 특정 솔루션, 사용 사례 및 권장 사항은 Microsoft 프로그램 관리자인 Jovan Popovic이 제공하는 SQL Server 및 Azure SQL Database의 기본 제공 JSON 지원에 대한 블로그 게시물을 참조하세요.For lots of specific solutions, use cases, and recommendations, see the blog posts about the built-in JSON support in SQL Server and in Azure SQL Database by Microsoft Program Manager Jovan Popovic.

관련 항목:See Also

OPENJSON을 사용하여 JSON 데이터를 행 및 열로 변환Convert JSON Data to Rows and Columns with OPENJSON