演習 - データを Azure SQL Database に読み込む

完了 100 XP

データの一括読み込みを行うときは、どこかから取得する必要があります。 Azure では、Azure Blob Storage にデータを格納またはダンプするのが一般的です。 Blob Storage は、比較的低コストで大量の非構造化データを格納できるよう最適化されています。

このシナリオでは、Adventure Works Cycles は店舗の ID 番号に基づいて店舗返品データを受け取ります。 このデータは .dat ファイルに格納された後、Azure Blob Storage にプッシュされます。 データが Blob Storage に格納されたら、Azure SQL でそれにアクセスする方法が必要です。 ストレージ アカウントにアクセスできる外部データ ソースを作成することで、それを行うことができます。 そのストレージ アカウントへのアクセスは、Microsoft Entra ID、共有キーの認可、または Shared Access Signature (SAS) を使用して制御できます。

この演習では、Azure Blob Storage から Azure SQL Database にデータを一括で読み込む 1 つのシナリオについて調べます。 このアプローチでは、T-SQL と Shared Access Signature を使います。

この演習を行うには、2 つのオプションがあります。

  • Azure Cloud Shell での sqlcmd
  • Azure Data Studio での SQL ノートブック

どちらの演習にも同じコマンドとコンテンツが含まれているため、お好きなオプションを選択できます。

オプション 1: Azure Cloud Shell での sqlcmd

sqlcmd は、コマンド ラインを使用して SQL Server および Azure SQL を操作できるコマンド ライン ツールです。 この演習では、Azure Cloud Shell の PowerShell インスタンスで sqlcmd を使います。 sqlcmd は既定でインストールされるので、Azure Cloud Shell から簡単に使用できます。

  1. Bash 用に Azure Cloud Shell を構成する方法のため、最初に Azure Cloud Shell で次のコマンドを実行し、ターミナル モードを変更する必要があります。

    TERM=dumb
    
  2. サーバー名とパスワードを自分のものに変更した後、統合ターミナルで次のコマンドを実行します。

    sqlcmd -S <server name>.database.windows.net -P <password> -U cloudadmin -d AdventureWorks
    
  3. 読み込むデータのためのテーブルとスキーマを作成します。 このプロ説は、単純な T-SQL です。 データベースに接続したので、ターミナルで次のスクリプトを実行します。

    IF SCHEMA_ID('DataLoad') IS NULL
    EXEC ('CREATE SCHEMA DataLoad')
    CREATE TABLE DataLoad.store_returns
    (
        sr_returned_date_sk             bigint,
        sr_return_time_sk               bigint,
        sr_item_sk                      bigint,
        sr_customer_sk                  bigint,
        sr_cdemo_sk                     bigint,
        sr_hdemo_sk                     bigint,
        sr_addr_sk                      bigint,
        sr_store_sk                     bigint,
        sr_reason_sk                    bigint,
        sr_ticket_number                bigint,
        sr_return_quantity              integer,
        sr_return_amt                   float,
        sr_return_tax                   float,
        sr_return_amt_inc_tax           float,
        sr_fee                          float,
        sr_return_ship_cost             float,
        sr_refunded_cash                float,
        sr_reversed_charge              float,
        sr_store_credit                 float,
        sr_net_loss                     float
    );
    GO
    

    ヒント

    T-SQL ステートメントの後に数値エントリが表示されます。 これは、T-SQL エントリの各行を表します。 たとえば、上のコマンドは 26 で終了します。 これらの行の後で必ず Enter キーを押してください。

    1> が再び表示されると、コマンドが終了したことがわかります。これは、sqlcmd に次の T-SQL エントリの最初の行を入力する準備ができたことを示します。

  4. 次に、マスター キーを作成します。

    CREATE MASTER KEY 
    ENCRYPTION BY PASSWORD='MyComplexPassword00!';
    GO
    
  5. Blob Storage はパブリック (匿名) アクセスを許可するように構成されていないため、DATABASE SCOPED CREDENTIAL 値を作成するにはマスター キーが必要です。 資格情報では Blob Storage アカウントを参照します。 データ部分では、店舗返品データのコンテナーを指定します。

    Azure SQL が解釈する方法を認識している ID として Shared Access Signature を使います。 シークレットは、Blob Storage アカウントから生成できる SAS トークンです。 この例では、アクセス権のないストレージ アカウントの SAS トークンが提供されるため、店舗返品データのみにアクセスできます。

    CREATE DATABASE SCOPED CREDENTIAL [https://azuresqlworkshopsa.blob.core.windows.net/data/]
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
    SECRET = 'st=2020-09-28T22%3A05%3A27Z&se=2030-09-29T22%3A05%3A00Z&sp=rl&sv=2018-03-28&sr=c&sig=52WbuSIJCWyjS6IW6W0ILfIpqh4wLMXmOlifPyOetZI%3D';
    GO
    
  6. コンテナーに対する外部データ ソースを作成します。

    CREATE EXTERNAL DATA SOURCE dataset
    WITH
    (
        TYPE = BLOB_STORAGE,
        LOCATION = 'https://azuresqlworkshopsa.blob.core.windows.net/data',
        CREDENTIAL = [https://azuresqlworkshopsa.blob.core.windows.net/data/]
    );
    GO
    
  7. 店舗返品ファイルの 1 つを一括挿入します。 次のスクリプトを実行し、完了するまでに、コメントを確認します。

    SET NOCOUNT ON -- Reduce network traffic by stopping the message that shows the number of rows affected
    BULK INSERT DataLoad.store_returns -- Table you created in step 3
    FROM 'dataset/store_returns/store_returns_1.dat' -- Within the container, the location of the file
    WITH (
    DATA_SOURCE = 'dataset' -- Using the external data source from step 6
    ,DATAFILETYPE = 'char'
    ,FIELDTERMINATOR = '\|'
    ,ROWTERMINATOR = '\|\n'
    ,BATCHSIZE=100000 -- Reduce network traffic by inserting in batches
    , TABLOCK -- Minimize number of log records for the insert operation
    );
    GO
    
  8. テーブルに挿入された行の数を確認します。

    SELECT COUNT(*) FROM DataLoad.store_returns;
    GO
    

    すべてが正常に実行された場合、2807797 が返されるはずです。

このコードは、Blob Storage から Azure SQL Database にデータを挿入する方法の簡単な例です。 演習をもう一度行いたい場合は、次のコードを実行して、行った内容をリセットします。

DROP EXTERNAL DATA SOURCE dataset;
DROP DATABASE SCOPED CREDENTIAL [https://azuresqlworkshopsa.blob.core.windows.net/data/];
DROP TABLE DataLoad.store_returns;
DROP MASTER KEY;
GO

オプション 2: Azure Data Studio での SQL ノートブック

このアクティビティでは、LoadData.ipynb という名前のノートブックを使います。 これは、デバイスの \mslearn-azure-sql-fundamentals\02-DeployAndConfigure\loaddata にあります。 Azure Data Studio でこのファイルを開いてこの演習を完了した後で、ここに戻ります。

何らかの理由で演習を完了できない場合は、GitHub の対応するノートブック ファイルで結果を確認できます。


次のユニット: まとめ

前へ 次へ