チュートリアル:ニューヨークのタクシーデータセットを読み込む

[アーティクル]
05/23/2023

このチュートリアルでは、COPY ステートメントを使用して、Azure Blob Storage アカウントからニューヨークのタクシーデータセットを読み込みます。このチュートリアルでは、Azure Portal と SQL Server Management Studio (SSMS) を使って、次のことを行います。

データを読み込むように指定されたユーザーを作成する
サンプルデータセット用のテーブルを作成する
COPY T-SQL ステートメントを使ってデータをデータウェアハウスに読み込む
データ読み込みの進行状況を表示する

Azure サブスクリプションをお持ちでない場合は、開始する前に無料の Azure アカウントを作成してください。

開始する前に

このチュートリアルを始める前に、最新バージョンの SQL Server Management Studio (SSMS) をダウンロードしてインストールします。

このチュートリアルでは、次のチュートリアルから SQL 専用プールを既に作成しているものと想定しています。

データを読み込むためのユーザーを作成する

サーバー管理者アカウントは管理操作を実行するためのものであり、ユーザーデータに対するクエリの実行には適していません。データの読み込みは、メモリを大量に消費する操作です。メモリの最大値は、データウェアハウスユニットとリソースクラスの構成に従って定義されます。

データの読み込みに専用のログインとユーザーを作成することをお勧めします。その後、適切な最大メモリ割り当てを有効にするリソースクラスに読み込みユーザーを追加します。

ログインとユーザーを作成できるようにサーバー管理者として接続します。以下の手順を使って、LoaderRC20 という名前のログインとユーザーを作成します。その後、そのユーザーを staticrc20 リソースクラスに割り当てます。

SSMS で [master] を右クリックしてドロップダウンメニューを表示し、 [新しいクエリ] を選びます。新しいクエリウィンドウが開きます。
クエリウィンドウで、次の T-SQL コマンドを入力して、LoaderRC20 という名前のログインとユーザーを作成します。"a123STRONGpassword!" は独自のパスワードに置き換えます。
```
CREATE LOGIN LoaderRC20 WITH PASSWORD = 'a123STRONGpassword!';
CREATE USER LoaderRC20 FOR LOGIN LoaderRC20;
```
[実行] を選択します。
mySampleDataWarehouse を右クリックして、 [新しいクエリ] を選びます。新しいクエリウィンドウが開きます。
次の T-SQL コマンドを入力して、LoaderRC20 ログインに対する LoaderRC20 という名前のデータベースユーザーを作成します。 2 行目では、新しいユーザーに新しいデータウェアハウスの CONTROL アクセス許可を付与しています。これらのアクセス許可は、ユーザーをデータベースの所有者にする場合と似ています。 3 行目では、新しいユーザーを staticrc20 リソースクラスのメンバーとして追加します。
```
CREATE USER LoaderRC20 FOR LOGIN LoaderRC20;
GRANT CONTROL ON DATABASE::[mySampleDataWarehouse] to LoaderRC20;
EXEC sp_addrolemember 'staticrc20', 'LoaderRC20';
```
[実行] を選択します。

読み込みユーザーとしてサーバーに接続する

データを読み込むための最初のステップは、LoaderRC20 としてログインすることです。

オブジェクトエクスプローラーで [接続] ドロップダウンメニューを選択し、 [データベースエンジン] を選択します。 [サーバーに接続] ダイアログボックスが表示されます。
完全修飾サーバー名を入力し、ログインとして「LoaderRC20」と入力します。 LoaderRC20 のパスワードを入力します。
[接続] を選択します。
接続する準備ができると、オブジェクトエクスプローラーに 2 つのサーバー接続が表示されます。 1 つは ServerAdmin としての接続、もう 1 つは LoaderRC20 としての接続です。

サンプルデータ用のテーブルを作成する

新しいデータウェアハウスにデータを読み込むプロセスを始める準備ができました。チュートリアルのこのパートでは、COPY ステートメントを使用して、Azure Storage Blob からニューヨーク市のタクシーのデータセットを読み込む方法を示します。今後の参考として、データを Azure Blob Storage に取得する方法やソースから直接読み込む方法については、読み込みの概要に関するページを参照してください。

次の SQL スクリプトを実行して、読み込むデータに関する情報を指定します。この情報には、データが置かれている場所、データの内容の形式、およびデータのテーブル定義が含まれます。

前のセクションで、LoaderRC20 としてデータウェアハウスにログインしました。 SSMS で LoaderRC20 接続を右クリックして、 [新しいクエリ] を選びます。新しいクエリウィンドウが表示されます。
実際のクエリウィンドウと上の画像を比べてください。新しいクエリウィンドウが LoaderRC20 として実行されていること、および MySampleDataWarehouse データベースに対するクエリを実行していることを確認します。このクエリウィンドウを使って、すべての読み込み手順を実行します。

次の T-SQL ステートメントを実行して、テーブルを作成します。

CREATE TABLE [dbo].[Date]
(
    [DateID] int NOT NULL,
    [Date] datetime NULL,
    [DateBKey] char(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DayOfMonth] varchar(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DaySuffix] varchar(4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DayName] varchar(9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DayOfWeek] char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DayOfWeekInMonth] varchar(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DayOfWeekInYear] varchar(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DayOfQuarter] varchar(3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DayOfYear] varchar(3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [WeekOfMonth] varchar(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [WeekOfQuarter] varchar(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [WeekOfYear] varchar(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Month] varchar(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [MonthName] varchar(9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [MonthOfQuarter] varchar(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Quarter] char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [QuarterName] varchar(9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Year] char(4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [YearName] char(7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [MonthYear] char(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [MMYYYY] char(6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [FirstDayOfMonth] date NULL,
    [LastDayOfMonth] date NULL,
    [FirstDayOfQuarter] date NULL,
    [LastDayOfQuarter] date NULL,
    [FirstDayOfYear] date NULL,
    [LastDayOfYear] date NULL,
    [IsHolidayUSA] bit NULL,
    [IsWeekday] bit NULL,
    [HolidayUSA] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
WITH
(
    DISTRIBUTION = ROUND_ROBIN,
    CLUSTERED COLUMNSTORE INDEX
);

CREATE TABLE [dbo].[Geography]
(
    [GeographyID] int NOT NULL,
    [ZipCodeBKey] varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [County] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [City] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [State] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Country] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ZipCode] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
WITH
(
    DISTRIBUTION = ROUND_ROBIN,
    CLUSTERED COLUMNSTORE INDEX
);

CREATE TABLE [dbo].[HackneyLicense]
(
    [HackneyLicenseID] int NOT NULL,
    [HackneyLicenseBKey] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [HackneyLicenseCode] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
WITH
(
    DISTRIBUTION = ROUND_ROBIN,
    CLUSTERED COLUMNSTORE INDEX
);

CREATE TABLE [dbo].[Medallion]
(
    [MedallionID] int NOT NULL,
    [MedallionBKey] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [MedallionCode] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
WITH
(
    DISTRIBUTION = ROUND_ROBIN,
    CLUSTERED COLUMNSTORE INDEX
);

CREATE TABLE [dbo].[Time]
(
    [TimeID] int NOT NULL,
    [TimeBKey] varchar(8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [HourNumber] tinyint NOT NULL,
    [MinuteNumber] tinyint NOT NULL,
    [SecondNumber] tinyint NOT NULL,
    [TimeInSecond] int NOT NULL,
    [HourlyBucket] varchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [DayTimeBucketGroupKey] int NOT NULL,
    [DayTimeBucket] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
WITH
(
    DISTRIBUTION = ROUND_ROBIN,
    CLUSTERED COLUMNSTORE INDEX
);

CREATE TABLE [dbo].[Trip]
(
    [DateID] int NOT NULL,
    [MedallionID] int NOT NULL,
    [HackneyLicenseID] int NOT NULL,
    [PickupTimeID] int NOT NULL,
    [DropoffTimeID] int NOT NULL,
    [PickupGeographyID] int NULL,
    [DropoffGeographyID] int NULL,
    [PickupLatitude] float NULL,
    [PickupLongitude] float NULL,
    [PickupLatLong] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DropoffLatitude] float NULL,
    [DropoffLongitude] float NULL,
    [DropoffLatLong] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [PassengerCount] int NULL,
    [TripDurationSeconds] int NULL,
    [TripDistanceMiles] float NULL,
    [PaymentType] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [FareAmount] money NULL,
    [SurchargeAmount] money NULL,
    [TaxAmount] money NULL,
    [TipAmount] money NULL,
    [TollsAmount] money NULL,
    [TotalAmount] money NULL
)
WITH
(
    DISTRIBUTION = ROUND_ROBIN,
    CLUSTERED COLUMNSTORE INDEX
);

CREATE TABLE [dbo].[Weather]
(
    [DateID] int NOT NULL,
    [GeographyID] int NOT NULL,
    [PrecipitationInches] float NOT NULL,
    [AvgTemperatureFahrenheit] float NOT NULL
)
WITH
(
    DISTRIBUTION = ROUND_ROBIN,
    CLUSTERED COLUMNSTORE INDEX
);

データウェアハウスにデータを読み込む

このセクションでは、Azure Storage Blob からサンプルデータを読み込むための COPY ステートメントを使用します。

Note

このチュートリアルでは、最終テーブルにデータを直接読み込みます。通常は、運用ワークロード用のステージングテーブルに読み込みます。データがステージングテーブルにある間に、必要な変換を実行できます。

次のステートメントを実行して、データを読み込みます。

COPY INTO [dbo].[Date]
FROM 'https://nytaxiblob.blob.core.windows.net/2013/Date'
WITH
(
    FILE_TYPE = 'CSV',
    FIELDTERMINATOR = ',',
    FIELDQUOTE = ''
)
OPTION (LABEL = 'COPY : Load [dbo].[Date] - Taxi dataset');


COPY INTO [dbo].[Geography]
FROM 'https://nytaxiblob.blob.core.windows.net/2013/Geography'
WITH
(
    FILE_TYPE = 'CSV',
    FIELDTERMINATOR = ',',
    FIELDQUOTE = ''
)
OPTION (LABEL = 'COPY : Load [dbo].[Geography] - Taxi dataset');

COPY INTO [dbo].[HackneyLicense]
FROM 'https://nytaxiblob.blob.core.windows.net/2013/HackneyLicense'
WITH
(
    FILE_TYPE = 'CSV',
    FIELDTERMINATOR = ',',
    FIELDQUOTE = ''
)
OPTION (LABEL = 'COPY : Load [dbo].[HackneyLicense] - Taxi dataset');

COPY INTO [dbo].[Medallion]
FROM 'https://nytaxiblob.blob.core.windows.net/2013/Medallion'
WITH
(
    FILE_TYPE = 'CSV',
    FIELDTERMINATOR = ',',
    FIELDQUOTE = ''
)
OPTION (LABEL = 'COPY : Load [dbo].[Medallion] - Taxi dataset');

COPY INTO [dbo].[Time]
FROM 'https://nytaxiblob.blob.core.windows.net/2013/Time'
WITH
(
    FILE_TYPE = 'CSV',
    FIELDTERMINATOR = ',',
    FIELDQUOTE = ''
)
OPTION (LABEL = 'COPY : Load [dbo].[Time] - Taxi dataset');

COPY INTO [dbo].[Weather]
FROM 'https://nytaxiblob.blob.core.windows.net/2013/Weather'
WITH
(
    FILE_TYPE = 'CSV',
    FIELDTERMINATOR = ',',
    FIELDQUOTE = '',
    ROWTERMINATOR='0X0A'
)
OPTION (LABEL = 'COPY : Load [dbo].[Weather] - Taxi dataset');

COPY INTO [dbo].[Trip]
FROM 'https://nytaxiblob.blob.core.windows.net/2013/Trip2013'
WITH
(
    FILE_TYPE = 'CSV',
    FIELDTERMINATOR = '|',
    FIELDQUOTE = '',
    ROWTERMINATOR='0X0A',
    COMPRESSION = 'GZIP'
)
OPTION (LABEL = 'COPY : Load [dbo].[Trip] - Taxi dataset');

読み込んだデータを表示します。数 GB のデータを読み込み、高パフォーマンスのクラスター化列ストアインデックスに圧縮しています。動的管理ビュー (DMV) を使用する次のクエリを実行して、読み込みの状態を表示します。

SELECT  r.[request_id]
,       r.[status]
,       r.resource_class
,       r.command
,       sum(bytes_processed) AS bytes_processed
,       sum(rows_processed) AS rows_processed
FROM    sys.dm_pdw_exec_requests r
              JOIN sys.dm_pdw_dms_workers w
                     ON r.[request_id] = w.request_id
WHERE [label] = 'COPY : Load [dbo].[Date] - Taxi dataset' OR
    [label] = 'COPY : Load [dbo].[Geography] - Taxi dataset' OR
    [label] = 'COPY : Load [dbo].[HackneyLicense] - Taxi dataset' OR
    [label] = 'COPY : Load [dbo].[Medallion] - Taxi dataset' OR
    [label] = 'COPY : Load [dbo].[Time] - Taxi dataset' OR
    [label] = 'COPY : Load [dbo].[Weather] - Taxi dataset' OR
    [label] = 'COPY : Load [dbo].[Trip] - Taxi dataset'
and session_id <> session_id() and type = 'WRITER'
GROUP BY r.[request_id]
,       r.[status]
,       r.resource_class
,       r.command;

すべてのシステムクエリを表示します。
```
SELECT * FROM sys.dm_pdw_exec_requests;
```
データウェアハウスにデータが適切に読み込まれました。

リソースをクリーンアップする

コンピューティングリソースとデータウェアハウスに読み込んだデータには課金されています。これらは別々に請求されます。

データをストレージに保持しておく場合は、データウェアハウスを使わない間、コンピューティングリソースを一時停止できます。コンピューティングリソースを一時停止すると課金はデータストレージだけになり、データを使う準備ができたらいつでもコンピューティングリソースを再開できます。
それ以上課金されないようにする場合は、データウェアハウスを削除できます。

必要に応じて、以下の手順でリソースをクリーンアップします。

Azure portal にログインし、データウェアハウスを選択します。
コンピューティングリソースを一時停止するには、 [一時停止] ボタンを選択します。データウェアハウスが一時停止すると、ボタンの表示が [開始] になります。コンピューティングリソースを再開するには、 [開始] を選択します。
コンピューティングリソースやストレージに課金されないようにデータウェアハウスを削除するには、 [削除] を選択します。
作成したサーバーを削除するには、前の図の mynewserver-20180430.database.windows.net を選び、 [削除] を選択します。サーバーを削除すると、サーバーに割り当てられているすべてのデータベースが削除されるので、注意してください。
リソースグループを削除するには、myResourceGroup を選択して、 [リソースグループの削除] を選択します。

次のステップ

このチュートリアルでは、データウェアハウスを作成し、データを読み込むためのユーザーを作成する方法について学習しました。シンプルな COPY ステートメントを使って、データをデータウェアハウスに読み込みました。

以下のことを行いました。

Azure Portal でデータウェアハウスを作成しました
Azure Portal でサーバーレベルのファイアウォール規則を設定する
SSMS でデータウェアハウスに接続しました
データを読み込むように指定されたユーザーを作成しました
サンプルデータ用のテーブルを作成しました
COPY T-SQL ステートメントを使ってデータをデータウェアハウスに読み込みました
データ読み込みの進行状況を表示しました

開発の概要に進んで、既存のデータベースを Azure Synapse Analytics に移行する方法を確認してください。

既存のデータベースを Azure Synapse Analytics に移行するための設計上の決定

読み込みの例とリファレンスに関する詳細については、次のドキュメントを確認してください。

次の方法で共有

チュートリアル:ニューヨークのタクシーデータセットを読み込む

開始する前に

データを読み込むためのユーザーを作成する

読み込みユーザーとしてサーバーに接続する

サンプルデータ用のテーブルを作成する

データウェアハウスにデータを読み込む

リソースをクリーンアップする

次のステップ

フィードバック

その他のリソース

次の方法で共有

チュートリアル:ニューヨークのタクシー データセットを読み込む

開始する前に

データを読み込むためのユーザーを作成する

読み込みユーザーとしてサーバーに接続する

サンプル データ用のテーブルを作成する

データ ウェアハウスにデータを読み込む

リソースをクリーンアップする

次のステップ

フィードバック

その他のリソース

チュートリアル:ニューヨークのタクシーデータセットを読み込む

サンプルデータ用のテーブルを作成する

データウェアハウスにデータを読み込む