مشاركة عبر

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

  • مقالة

يستخدم هذا البرنامج التعليمي عبارة COPY لتحميل مجموعة بيانات سيارات الأجرة في مدينة نيويورك من حساب Azure Blob Storage. يستخدم البرنامج التعليمي مدخل Azure وManagement Studio (SSMS) إلى:

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

إذا لم يكن لديك اشتراك Azure، ⁦⁩يتعين إنشاء حساب Azure مجاني⁦⁩ قبل أن تبدأ.

قبل أن تبدأ

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

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

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

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

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

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

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

    استعلام جديد في الشكل الرئيس

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

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

  3. حدد تنفيذ.

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

    استعلام جديد في مستودع بيانات العينة

  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. حدد تنفيذ.

الاتصال بالخادم كمستخدم التحميل

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

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

    الاتصال بتسجيل الدخول الجديد

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

  3. حدد ⁧⁩اتصال⁧⁩.

  4. عندما يكون الاتصال جاهزًا، سترى اتصالين بالخادم في مستكشف عناصر SQL Server. اتصال واحد ك ServerAdmin واتصال واحد ك LoaderRC20.

    الاتصال ناجح

إنشاء جداول لبيانات العينة

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

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

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

    نافذة استعلام التحميل الجديد

  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. عرض بياناتك أثناء تحميلها. تقوم بتحميل بيانات يبلغ حجمها عدة غيغابايت وضغطها في مؤشرات تخزين الأعمدة ذات الأداء العالي. قم بتشغيل الاستعلام التالي الذي يستخدم طرق عرض إدارة ديناميكية (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. استمتع برؤية بياناتك محملة بشكل جيد في مستودع البيانات الخاص بك.

    عرض الجداول المحملة

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

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

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

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

  1. سجل الدخول في ⁧⁩مدخل Azure⁧⁩، ثم حدد مستودع البيانات الخاص بك.

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

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

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

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

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

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

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

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

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

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

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

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