Tutorial: Memuat himpunan data New York Taxicab

  • Artikel

Tutorial ini menggunakan Pernyataan COPY untuk memuat set data New York Taxicab dari akun Azure Blob Storage. Tutorial menggunakan portal Microsoft Azure dan SQL Server Management Studio (SSMS) untuk:

  • Buat pengguna yang ditunjuk untuk memuat data
  • Buat tabel untuk himpunan data sampel
  • Gunakan pernyataan COPY T-SQL untuk memuat data ke gudang data Anda
  • Menampilkan progres data saat sedang dimuat

Jika Anda tidak memiliki langganan Azure, buat akun Azure gratissebelum Anda memulai.

Sebelum Anda mulai

Sebelum Anda memulai tutorial ini, unduh dan pasang versi terbaru dari SQL Server Management Studio (SSMS).

Tutorial ini mengasumsikan Anda telah membuat kumpulan khusus SQL dari tutorial berikut.

Buat pengguna untuk memuat data

Akun administrator server digunakan untuk melakukan operasi manajemen, dan tidak cocok untuk menjalankan kueri pada data pengguna. Memuat data adalah operasi intensif memori. Maksimum memori didefinisikan sesuai dengan unit gudang data dan kelas sumber daya yang telah dikonfigurasi.

Langkah terbaik adalah dengan membuat login dan pengguna yang didedikasikan untuk memuat data. Kemudian tambahkan pengguna pemuatan ke kelas sumber daya yang memungkinkan alokasi memori maksimum yang sesuai.

Sambungkan sebagai admin server sehingga Anda dapat membuat data masuk dan pengguna. Gunakan langkah-langkah ini untuk membuat login dan pengguna dengan nama LoaderRC20. Kemudian tetapkan pengguna ke kelas sumber daya staticrc20.

  1. Di SSMS, pilih kanan master untuk memperlihatkan menu turun bawah, dan pilih Kueri Baru. Jendela kueri baru terbuka.

    Kueri baru dalam master

  2. Di jendela kueri, masukkan perintah T-SQL ini untuk membuat login dan pengguna bernama LoaderRC20, menggantikani kata sandi Anda sendiri dengan 'a123STRONGpassword!'.

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

  3. Pilih Jalankan.

  4. Klik kanan mySampleDataWarehouse, dan pilih Kueri Baru. Jendela kueri baru terbuka.

    Kueri baru tentang sampel gudang data

  5. Masukkan perintah T-SQL berikut untuk membuat pengguna database bernama LoaderRC20 untuk login LoaderRC20. Baris kedua memberi pengguna baru izin CONTROL pada gudang data baru. Izin ini mirip dengan menjadikan pengguna sebagai pemilik database. Baris ketiga menambahkan pengguna baru sebagai anggota kelas sumber daya staticrc20.

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

  6. Pilih Jalankan.

Sambungkan ke server sebagai pengguna pemuatan

Langkah pertama menuju memuat data adalah dengan login sebagai LoaderRC20.

  1. Di Object Explorer, klik menu turun bawah Sambungkan dan pilih Mesin Database. Kotak dialog Sambungkan ke Server akan muncul.

    Sambungkan dengan login baru

  2. Masukkan nama server yang sepenuhnya memenuhi syarat, dan masukkan LoaderRC20 sebagai Login. Masukkan kata sandi Anda untuk LoaderRC20.

  3. Pilih Sambungkan.

  4. Saat koneksi siap, Anda akan melihat dua koneksi server di Object Explorer. Satu koneksi sebagai ServerAdmin dan satu koneksi sebagai LoaderRC20.

    Koneksi berhasil

Buat tabel untuk data sampel

Anda siap untuk memulai proses pemuatan data ke gudang data baru Anda. Bagian tutorial ini menunjukkan kepada Anda cara menggunakan pernyataan COPY untuk memuat himpunan data taksi Kota New York dari Azure Storage blob. Untuk referensi di masa mendatang, untuk mempelajari cara memasukkan data Anda ke Azure Blob Storage atau cara untuk memuatnya secara langsung dari sumber, lihat gambaran umum pemuatan.

Jalankan skrip SQL berikut dan tentukan informasi tentang data yang ingin Anda muat. Informasi ini mencakup tempat data berada, format konten data, dan definisi tabel untuk data.

  1. Di bagian sebelumnya, Anda masuk ke gudang data sebagai LoaderRC20. Di SSMS, klik kanan koneksi LoaderRC20 Anda dan pilih Kueri Baru. Jendela kueri baru muncul.

    Jendela kueri pemuatan baru

  2. Bandingkan jendela kueri Anda dengan gambar sebelumnya. Verifikasi bahwa jendela kueri baru Anda berjalan sebagai LoaderRC20 dan melakukan kueri pada database MySampleDataWarehouse Anda. Gunakan jendela kueri ini untuk melakukan semua langkah pemuatan.

  3. Jalankan pernyataan T-SQL berikut untuk membuat tabel:

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

Muat data ke gudang data Anda

Bagian ini menggunakan Pernyataan COPY untuk memuat data sampel dari Azure Storage Blob.

Catatan

Tutorial ini memuat data langsung ke tabel akhir. Anda biasanya akan memuat ke dalam meja penahapan untuk beban kerja produksi Anda. Saat data berada dalam tabel penahapan, Anda dapat melakukan transformasi yang diperlukan.

  1. Jalankan pernyataan berikut untuk memuat data:

    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. Lihat data Anda saat dimuat. Anda memuat beberapa GB data dan memadatkannya ke dalam indeks penyimpan kolom berkluster dengan performa tinggi. Jalankan kueri berikut yang menggunakan tampilan manajemen dinamis (DMV) untuk memperlihatkan status beban.

    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. Lihat semua kueri sistem.

    SELECT * FROM sys.dm_pdw_exec_requests;

  4. Nikmati melihat data Anda dimuat dengan baik ke dalam gudang data Anda.

    Lihat tabel yang dimuat

Membersihkan sumber daya

Anda ditagih untuk sumber daya komputasi dan data yang dimuat ke dalam gudang data. Tagihan ditagihkan secara terpisah.

  • Jika Anda ingin menyimpan data di penyimpanan, Anda dapat menjeda komputasi saat Anda tidak menggunakan gudang data. Dengan menjeda komputasi, Anda hanya akan dikenakan biaya untuk penyimpanan data dan Anda dapat melanjutkan komputasi setiap kali Anda siap untuk bekerja dengan data.
  • Jika Anda ingin menghapus biaya di masa mendatang, Anda dapat menghapus gudang data.

Ikuti langkah-langkah berikut untuk membersihkan sumber daya sesuai keinginan Anda.

  1. Masuk ke portal Microsoft Azure, pilih gudang data Anda.

    Membersihkan sumber daya

  2. Untuk menjeda komputasi, pilih tombol Jeda. Saat gudang data dijeda, Anda akan melihat tombol Mulai. Untuk melanjutkan komputasi, pilih Mulai.

  3. Untuk menghapus gudang data sehingga Anda tidak dikenakan biaya untuk komputasi atau penyimpanan, pilih Hapus.

  4. Untuk menghapus server yang Anda buat, mynewserver-20180430.database.windows.net di gambar sebelumnya, lalu pilih Hapus. Berhati-hatilah dengan langkah ini, karena menghapus server akan menghapus semua database yang ditetapkan ke server.

  5. Untuk menghapus grup sumber daya, pilih myResourceGroup, lalu pilih Hapus grup sumber daya.

Langkah berikutnya

Dalam tutorial ini, Anda belajar cara membuat gudang data dan membuat pengguna untuk memuat data. Anda telah menggunakan Pernyataan COPY sederhana untuk memuat data ke gudang data Anda.

Anda telah melakukan hal-hal ini:

  • Buat gudang data di portal Microsoft Azure
  • Siapkan aturan firewall tingkat server di portal Azure
  • Tersambung ke gudang data dengan SSMS
  • Membuat pengguna yang ditunjuk untuk memuat data
  • Membuat tabel untuk data sampel
  • Menggunakan pernyataan COPY T-SQL untuk memuat data ke gudang data Anda
  • Menampilkan progres data saat sedang dimuat

Lanjutkan ke ringkasan pengembangan untuk mempelajari cara memigrasikan database yang sudah ada ke Azure Synapse Analytics:

Keputusan desain untuk memigrasikan database yang sudah ada ke Azure Synapse Analytics

Untuk contoh dan referensi pemuatan lainnya, lihat dokumentasi berikut ini: