JSON ドキュメントの SQL Server へのインポート

  • [アーティクル]

適用対象: SQL Server 2016 (13.x) 以降 Azure SQL DatabaseAzure SQL Managed Instance

この記事では、SQL Server に JSON ファイルをインポートする方法について説明します。 JSON ドキュメントには、アプリケーション ログ、センサー データなど、さまざまな種類のデータが格納されます。 ファイルに保存されている JSON データ読み取り、そのデータを SQL Server に読み込んで分析できることが重要です。

アクセス許可

インスタンス レベルでは、この機能には bulkadmin 固定サーバー ロールまたは ADMINISTER BULK OPERATIONS アクセス許可のメンバーシップが必要です。

データベース レベルでは、この機能には ADMINISTER DATABASE BULK OPERATIONS アクセス許可が必要です。

Azure Blob Storage にアクセスするには、読み取り/書き込みアクセスが必要です。

JSON ドキュメントを 1 つの列にインポートする

OPENROWSET(BULK) は、SQL Server が読み取りアクセス権を持っている場所であれば、ローカル ドライブまたはネットワーク上の任意のファイルからデータを読み取ることができるテーブル値関数です。 ファイルの内容を含む 1 列のテーブルを返します。 OPENROWSET(BULK) 関数では、区切り記号など、さまざまなオプションを使用できます。 簡単な場合では、単にファイルの内容全体をテキスト値として読み込むことができます。 (この 1 つの大きな値は、Single Character Large Object (SINGLE_CLOB) と呼ばれます)。

JSON ファイルの内容を読み取り、それを 1 つの値としてユーザーに返す 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 ドキュメントをインポートする

上記で説明した OPENROWSET(BULK) を使用して、SQL Server がアクセスできる他のファイルの場所から JSON ファイルを読み取ることもできます。 たとえば、Azure File Storage は SMB プロトコルをサポートしています。 そのため、次の手順でローカルの仮想ドライブを Azure File Storage 共有にマップできます。

  1. Azure ポータルまたは 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]

    ローカル ドライブ文字 T: を Azure File Storage 共有に割り当てる例を次に示します。

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

    ストレージ アカウント キーと、プライマリまたはセカンダリ ストレージ アカウント アクセス キーは、Azure ポータルの [設定] の [キー] セクションで確認できます。

  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 コマンドまたは OPENROWSET 関数を使用して、Azure Blob Storage のファイルを Azure SQL Database に直接読み込むことができます。

まず外部データ ソースを作成します。次に例を示します。

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 つの値として読み取るのではなく、解析して、ファイル内の書籍とそのプロパティを行と列で返すこともできます。 次の例では、書籍の一覧を格納しているこちらのサイトの JSON ファイルを使います。

例 1

最も簡単な例では、単にファイルから一覧全体を読み込むことができます。

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

上記の OPENROWSET では、ファイルから 1 つのテキスト値が読み取られます。 OPENROWSET では値が BulkColumn として返され、OPENJSON 関数に BulkColumn が渡されます。 OPENJSON では、BulkColumn 配列内の JSON オブジェクトの配列が反復処理され、各行で 1 冊の書籍が返されます。 次に示すように、各行は 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 Author
978-0641723445 The Lightning Thief 12.5 384 Rick Riordan
978-1423103349 The Sea of Monsters 6.49 304 Rick Riordan
978-1857995879 Sophie's World : The Greek Philosophers 3.07 64 Jostein Gaarder
978-1933988177 Lucene in Action, Second Edition 30.5 475 Michael McCandless

このテーブルをユーザーに返したり、データを別のテーブルに読み込んだりすることができます。

関連項目