教學課程:載入紐約計程車資料集
本教學課程使用 COPY 陳述式,從 Azure Blob 儲存體帳戶載入紐約市計程車資料集。 本教學課程是使用 Azure 入口網站和 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 的資料庫使用者。 第二行會在新的資料倉儲上授與新的使用者控制權限。 這些權限類似於讓使用者成為資料庫的擁有者。 第三行會將新的使用者新增為 staticrc20 資源類別的成員。
CREATE USER LoaderRC20 FOR LOGIN LoaderRC20; GRANT CONTROL ON DATABASE::[mySampleDataWarehouse] to LoaderRC20; EXEC sp_addrolemember 'staticrc20', 'LoaderRC20';
選取 [執行]。
以載入使用者身分連線到伺服器
載入資料的首要步驟是以 LoaderRC20 身分登入。
在 [物件總管] 中,選取 [連線] 下拉式功能表並選取 [資料庫引擎]。 [連線到伺服器] 對話方塊隨即出現。
輸入完整伺服器名稱,以及輸入 LoaderRC20 作為登入。 輸入您 LoaderRC20 的密碼。
選取 [連線]。
您的連線就緒時,會在 [物件總管] 中看到兩個伺服器連線。 一個連線做為 ServerAdmin,另一個連線做為 LoaderRC20。
建立範例資料的資料表
您已準備好開始將資料載入新資料倉儲的程序。 本教學課程的這個部分會說明如何使用 COPY 陳述式,從 Azure 儲存體 Blob 載入紐約市計程車資料集。 如需日後參考,以了解如何將您的資料置於 Azure Blob 儲存體,或直接從您的來源將資料載入,請參閱載入概觀。
執行下列 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 );
將資料載入資料倉儲
本節使用 COPY 陳述式從 Azure 儲存體 Blob 載入範例資料。
注意
本教學課程會將資料直接載入最終資料表。 您通常會為生產工作負載載入暫存表格。 當資料位於暫存資料表時,您可以執行任何必要的轉換。
執行下列陳述式以載入資料:
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 入口網站,選取您的資料倉儲。
若要暫停計算,請選取 [暫停] 按鈕。 資料倉儲暫停時,您會看到 [啟動] 按鈕。 若要繼續計算,請選取 [啟動] 。
若要移除資料倉儲而不再支付運算或儲存體的費用,請選取 [刪除]。
若要移除您所建立的伺服器,請選取先前影像中的 [mynewserver-20180430.database.windows.net],然後選取 [刪除]。 請謹慎使用這個,因為刪除伺服器會將所有指派給伺服器的資料庫刪除。
若要移除此資源群組,請選取 [myResourceGroup],然後選取 [刪除資源群組]。
後續步驟
在本教學課程中,您已了解如何建立資料倉儲,以及建立載入資料的使用者。 您使用了簡單的 COPY 陳述式將資料載入資料倉儲。
您進行了下列事項:
- 在 Azure 入口網站中建立資料倉儲
- 在 Azure 入口網站中設定伺服器層級的防火牆規則
- 使用 SSMS 連線到資料倉儲
- 建立針對載入資料指定的使用者
- 建立範例資料的資料表
- 使用 COPY T-SQL 陳述式將資料載入資料倉儲
- 在載入時,已檢閱資料的進度
請繼續閱讀開發概觀,了解如何將現有資料庫移轉至 Azure Synapse Analytics:
如需更多載入範例和參考,請檢視下列文件: