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

[アーティクル]
02/21/2024

適用対象: SQL Server 2016 (13.x) 以降 Azure SQL データベース Azure SQL Managed Instance

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

この記事の例では、書籍の一覧含む GitHub サンプルの JSON ファイルを使用します。

アクセス許可

インスタンスレベルでは、この機能には 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 共有にマップできます。

Azure ポータルまたは Azure PowerShell を使用して、ファイルストレージアカウント (mystorage など)、ファイル共有 (sharejson など)、および Azure File Storage のフォルダーを作成します。
いくつかの 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]
```
ローカルドライブ文字 T: を Azure File Storage 共有に割り当てる例を次に示します。
```
net use t: \\mystorage.file.core.windows.net\sharejson /u:myaccount hb5qy6eXLqIdBj0LvGMHdrTiygkjhHDvWjUZg3Gu7bubKLg==
```
ストレージアカウントキーと、プライマリまたはセカンダリストレージアカウントアクセスキーは、Azure ポータルの [設定] の [キー] セクションで確認できます。

マップ済みドライブを使って 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 つの値として読み取るのではなく、解析して、ファイル内の書籍とそのプロパティを行と列で返すこともできます。

例 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	Name	Price	pages_i	作成者
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

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

次の方法で共有

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

アクセス許可

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

Azure File Storage から JSON ドキュメントをインポートする

Azure Blob Storage から JSON ドキュメントをインポートする

JSON ドキュメントを行と列に解析する

例 1

例 2

フィードバック

その他のリソース

次の方法で共有

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

アクセス許可

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

Azure File Storage から JSON ドキュメントをインポートする

Azure Blob Storage から JSON ドキュメントをインポートする

JSON ドキュメントを行と列に解析する

例 1

例 2

関連するコンテンツ

フィードバック

その他のリソース