البرنامج التعليمي: تحميل مجموعة بيانات نيويورك تاكسيكاب

  • مقالة
  • قراءة خلال 8 دقائق

يستخدم هذا البرنامج التعليمي عبارة COPY لتحميل مجموعة بيانات New York Taxicab من حساب Azure Blob Storage. يستخدم البرنامج التعليمي مدخل AzureSQL Server Management Studio (SSMS) من أجل:

  • إنشاء مستخدم معين لتحميل البيانات
  • إنشاء الجداول لمجموعة البيانات النموذجية
  • استخدم بيان COPY T-SQL لتحميل البيانات في مستودع البيانات الخاص بك
  • عرض تقدم البيانات أثناء تحميلها

إذا لم يكن لديك اشتراك في Azure، فأنشئ free account قبل البدء.

قبل أن تبدأ

قبل البدء في هذا البرنامج التعليمي، قم بتنزيل أحدث إصدار من SQL Server Management Studio (SSMS) وتثبيته.

يفترض هذا البرنامج التعليمي أنك قمت بالفعل بإنشاء تجمع مخصص SQL من البرنامج التعليمي التالي.

إنشاء مستخدم لتحميل البيانات

يهدف حساب مسؤول الخادم إلى تنفيذ عمليات الإدارة، وهو غير مناسب لتشغيل الاستعلامات على بيانات المستخدم. تحميل البيانات هو عملية كثيفة الذاكرة. يتم تعريف الحدود القصوى للذاكرة وفقا لوحدات مستودع البياناتوفئة الموارد التي تم تكوينها.

من الأفضل إنشاء تسجيل دخول ومستخدم مخصص لتحميل البيانات. ثم أضف مستخدم التحميل إلى فئة موارد تتيح تخصيص الحد الأقصى المناسب للذاكرة.

الاتصال كمسؤول الخادم حتى تتمكن من إنشاء عمليات تسجيل الدخول والمستخدمين. استخدم هذه الخطوات لإنشاء تسجيل دخول ومستخدم يسمى LoaderRC20. ثم قم بتعيين المستخدم إلى فئة الموارد staticrc20 .

  1. في SSMS، حدد الرئيسي بزر الماوس الأيمن لإظهار قائمة منسدلة، واختر استعلام جديد. يتم فتح نافذة «استعلام جديد».

    New query in master

  2. في نافذة الاستعلام، أدخل أوامر T-SQL هذه لإنشاء تسجيل دخول ومستخدم باسم LoaderRC20، مع استبدال كلمة المرور الخاصة بك ب "a123STRONGpassword!".

    CREATE LOGIN LoaderRC20 WITH PASSWORD = 'a123STRONGpassword!';
CREATE USER LoaderRC20 FOR LOGIN LoaderRC20;

  3. حدد «Execute».

  4. انقر بزر الماوس الأيمن فوق mySampleDataWarehouse، واختر استعلام جديد. يتم فتح نافذة استعلام جديدة.

    New query on sample data warehouse

  5. أدخل أوامر T-SQL التالية لإنشاء مستخدم قاعدة بيانات باسم LoaderRC20 لتسجيل الدخول إلى LoaderRC20. يمنح السطر الثاني أذونات CONTROL للمستخدم الجديد على مستودع البيانات الجديد. تشبه هذه الأذونات جعل المستخدم مالك قاعدة البيانات. يضيف السطر الثالث المستخدم الجديد كعضو في فئة الموارد staticrc20.

    CREATE USER LoaderRC20 FOR LOGIN LoaderRC20;
GRANT CONTROL ON DATABASE::[mySampleDataWarehouse] to LoaderRC20;
EXEC sp_addrolemember 'staticrc20', 'LoaderRC20';

  6. حدد «Execute».

الاتصال إلى الخادم كمستخدم تحميل

الخطوة الأولى نحو تحميل البيانات هي تسجيل الدخول باسم LoaderRC20.

  1. في مستكشف الكائنات، حدد القائمة المنسدلة الاتصالوحدد محرك قاعدة البيانات. يظهر مربع الحوار ⁧⁩Connect to Server⁧⁩.

    Connect with new login

  2. أدخل اسم الخادم المؤهل بالكامل، وأدخل LoaderRC20 كتسجيل الدخول. أدخل كلمة المرور الخاصة بك ل LoaderRC20.

  3. حدد Connect.

  4. عندما يكون اتصالك جاهزا، سترى اتصالين بالخادم في "مستكشف الكائنات". اتصال واحد ك ServerAdmin واتصال واحد باسم MedRCLogin.

    Connection is successful

إنشاء جداول لنماذج البيانات

أنت مستعد لبدء عملية تحميل البيانات في مستودع البيانات الجديد الخاص بك. يوضح لك هذا الجزء من البرنامج التعليمي كيفية استخدام عبارة COPY لتحميل مجموعة بيانات كابينة سيارات الأجرة في مدينة نيويورك من نقطة تخزين Azure. للرجوع إليها في المستقبل، لمعرفة كيفية نقل بياناتك إلى Azure Blob Storage أو تحميلها مباشرة من مصدرك، راجع نظرة عامة على التحميل.

قم بتشغيل البرامج النصية SQL التالية وحدد معلومات حول البيانات التي ترغب في تحميلها. تتضمن هذه المعلومات مكان وجود البيانات وتنسيق محتويات البيانات وتعريف الجدول للبيانات.

  1. في القسم السابق، قمت بتسجيل الدخول إلى مستودع البيانات الخاص بك ك LoaderRC20. في SSMS، انقر بزر الماوس الأيمن فوق اتصال LoaderRC20 وحدد استعلام جديد. تظهر نافذة استعلام جديدة.

    New loading query window

  2. قارن نافذة الاستعلام بالصورة السابقة. تحقق من تشغيل نافذة الاستعلام الجديدة ك LoaderRC20 وإجراء استعلامات على قاعدة بيانات MySampleDataWarehouse. استخدم نافذة الاستعلام هذه لتنفيذ كافة خطوات التحميل.

  3. قم بتشغيل عبارات 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 Storage Blob.

ملاحظة

يقوم هذا البرنامج التعليمي بتحميل البيانات مباشرة في الجدول النهائي. عادة ما يتم تحميلك في جدول مرحلي لأحمال عمل الإنتاج الخاصة بك. أثناء وجود البيانات في جدول التدريج ، يمكنك إجراء أي تحويلات ضرورية.

  1. قم بتشغيل العبارات التالية لتحميل البيانات:

    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');

  2. عرض بياناتك أثناء تحميلها. أنت تقوم بتحميل عدة غيغابايت من البيانات وضغطها في فهارس columnstore مجمعة عالية الأداء. قم بتشغيل الاستعلام التالي الذي يستخدم طرق عرض إدارة ديناميكية (DMVs) لإظهار حالة الحمل.

    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;

  3. عرض كافة استعلامات النظام.

    SELECT * FROM sys.dm_pdw_exec_requests;

  4. استمتع برؤية بياناتك محملة بشكل جيد في مستودع البيانات الخاص بك.

    View loaded tables

تنظيف الموارد

يتم تحصيل رسوم منك مقابل حساب الموارد والبيانات التي قمت بتحميلها في مستودع البيانات الخاص بك. تتم فوترة هذه الفواتير بشكل منفصل.

  • إذا كنت تريد الاحتفاظ بالبيانات في التخزين، فيمكنك إيقاف الحساب مؤقتاً عند عدم استخدام مستودع البيانات. من خلال إيقاف الحوسبة مؤقتا ، سيتم تحصيل رسوم منك فقط مقابل تخزين البيانات ويمكنك استئناف الحوسبة كلما كنت مستعدا للعمل مع البيانات.
  • إذا كنت ترغب في إزالة الرسوم المستقبلية، يمكنك حذف مستودع البيانات.

اتبع هذه الخطوات لتنظيف الموارد كما تريد.

  1. قم بتسجيل الدخول إلى مدخل Azure، وحدد مستودع البيانات الخاص بك.

    Clean up resources

  2. لإيقاف الحساب مؤقتاً، حدد الزر "Pause" . عند إيقاف مستودع البيانات مؤقتا، سترى زر ابدأ . لاستئناف الحساب، حدد "Start" .

  3. لإزالة مستودع البيانات حتى لا يتم تحصيل رسوم منك مقابل الحوسبة أو التخزين، حدد حذف.

  4. لإزالة الخادم الذي قمت بإنشائه، حدد mynewserver-20180430.database.windows.net في الصورة السابقة، ثم حدد حذف. كن حذرا مع هذا لأن حذف الخادم سيؤدي إلى حذف جميع قواعد البيانات المخصصة للخادم.

  5. لإزالة مجموعة الموارد، حدد myResourceGroup، ثم حدد "Delete resource group" .

الخطوات التالية

في هذا البرنامج التعليمي ، تعلمت كيفية إنشاء مستودع بيانات وإنشاء مستخدم لتحميل البيانات. لقد استخدمت عبارة COPY البسيطة لتحميل البيانات في مستودع البيانات الخاص بك.

لقد قمت بما يلي:

  • إنشاء مستودع بيانات في مدخل Azure
  • إعداد قاعدة جدار حماية على مستوى الخادم في مدخل Azure
  • متصل بمستودع البيانات باستخدام SSMS
  • إنشاء مستخدم معين لتحميل البيانات
  • إنشاء الجداول لنموذج البيانات
  • استخدام بيان COPY T-SQL لتحميل البيانات في مستودع البيانات الخاص بك
  • عرض تقدم البيانات أثناء تحميلها

تقدم إلى نظرة عامة على التطوير لمعرفة كيفية ترحيل قاعدة بيانات موجودة إلى Azure Synapse Analytics:

تصميم القرارات لترحيل قاعدة بيانات موجودة إلى Azure Synapse Analytics

لمزيد من أمثلة التحميل والمراجع، راجع الوثائق التالية: