Membuat tabel, mengimpor secara massal, dan data kueri

  • 10 menit

Universitas membutuhkan lokasi untuk menyimpan data yang saat ini ada dalam file teks. Mereka ingin membuat relasional data untuk meningkatkan kemampuan untuk mengaksesnya. Mereka telah memilih satu database di Azure SQL Database sebagai layanan penyimpanan untuk data ini. Mari kita lihat Database SQL dan lihat cara mengunggah dan mengkueri data.

Membuat database tunggal dengan menggunakan portal Azure

SQL Database adalah layanan database relasional yang didasarkan pada versi stabil terbaru Microsoft SQL Server Database Engine. SQL Database adalah database berkinerja tinggi yang mudah digunakan yang andal dan sangat aman. Anda dapat menggunakan SQL Database untuk membangun aplikasi, situs web, dan layanan mikro baru dalam bahasa pemrograman pilihan Anda, dan Anda tidak perlu mengelola infrastruktur.

Anda dapat membuat satu database melalui portal Azure atau dengan menggunakan Azure PowerShell atau CLI.

  1. Pada menu portal Microsoft Azure, pilih Buat sumber daya.

    Screenshot of Azure portal menu and Create a resource option.

  2. Pilih Database, lalu pilih Database SQL.

    Screenshot of the Databases and SQL Database options.

  3. Untuk menggunakan CLI, jalankan az sql server create perintah dan az sql db create .

  4. Untuk menggunakan PowerShell, jalankan New-AzSqlServer perintah dan New-AzSqlDatabase .

Saat Anda membuat database tunggal, Anda akan diminta untuk menentukan server untuk mengelolanya. Anda dapat membuat server baru atau menggunakan server yang ada.

Saat membuat server baru dan memilih untuk menggunakan autentikasi SQL, Anda akan diminta untuk menentukan nama pengguna dan kata sandi admin server. Gunakan kredensial ini untuk menyambungkan ke server untuk melakukan tugas administratif dan mengakses database yang dikontrol server. SQL Database juga mendukung autentikasi Microsoft Entra. Anda juga dapat memilih untuk menggunakan autentikasi SQL dan Microsoft Entra. Tapi, Anda harus selalu mengatur admin atau membuat akun admin saat membuat server baru. Kemudian, berikan akses ke akun yang disimpan di ID Microsoft Entra.

Setiap server database dilindungi oleh firewall untuk memblokir proses yang memiliki potensi bahaya. Anda dapat membuka firewall ke layanan Azure lainnya. Dan Anda dapat secara selektif mengaktifkan akses ke komputer lain berdasarkan alamat IP atau rentang alamat mereka. SQL Database juga menyediakan keamanan data lanjutan yang memungkinkan Anda untuk:

  • Menentukan sensitivitas data dalam masing-masing kolom dalam tabel.
  • Menilai kerentanan database Anda dan mengambil langkah-langkah perbaikan yang diperlukan.
  • Kirim peringatan saat ancaman terdeteksi.

Anda mengonfigurasi sumber daya dengan menggunakan model inti virtual (vCore), yang menentukan memori, I/O, dan sumber daya CPU untuk dialokasikan. Anda dapat menskalakan sumber daya komputasi dan penyimpanan secara mandiri. Atau, Anda dapat menetapkan sumber daya dalam hal unit transaksi database (DTUs). DTU adalah ukuran biaya yang dikalibrasi dari sumber daya yang Anda butuhkan untuk melakukan transaksi patokan.

Jika Anda memiliki beberapa database dan kebutuhan sumber daya database yang berfluktuasi, Anda dapat menggunakan kumpulan elastis SQL. Fitur ini memungkinkan berbagi kumpulan sumber daya di antara database yang terkumpulan sesuai permintaan.

Saat membuat database, Anda juga menentukan bagaimana data disatukan. Kolase menentukan aturan yang digunakan database untuk mengurutkan dan membandingkan data. Ini juga menentukan set karakter yang akan digunakan untuk data teks. Anda dapat mengubah kolase setelah Anda membuat database, tetapi sebaiknya tidak mengubahnya setelah database berisi data.

Membuat tabel

Anda dapat menggunakan salah satu alat ini untuk membuat tabel:

  • Editor kueri di portal Azure
  • Utilitas sqlcmd dan Cloud Shell
  • SQL Server Management Studio

Alat mana pun yang Anda pilih, Anda menentukan tabel dengan menggunakan CREATE TABLE perintah Transact-SQL (T-SQL). SQL Database mendukung kunci utama, kunci asing, indeks, dan pemicu pada tabel. Kode sampel berikut membuat sepasang tabel terkait dan indeks nonclustered. Anda dapat menjalankan perintah ini sebagai batch di editor kueri atau di utilitas sqlcmd.

CREATE TABLE MyTable
(
    MyColumn1 INT NOT NULL PRIMARY KEY,
    MyColumn2 VARCHAR(50) NOT NULL
);

CREATE TABLE MyTable2
(
    AnotherColumn1 INT NOT NULL,
    AnotherColumn2 INT NOT NULL REFERENCES MyTable,
    AnotherColumn3 VARCHAR(50) NULL,
    PRIMARY KEY (AnotherColumn1, AnotherColumn2)
);

CREATE INDEX cci ON MyTable2(AnotherColumn3);

Untuk mengakses editor kueri di portal Azure, buka halaman untuk database Anda dan pilih Editor Kueri. Anda dimintai kredensial. Anda dapat mengatur Jenis Otorisasi ke autentikasi SQL Server dan memasukkan nama pengguna dan kata sandi yang Anda siapkan saat membuat database. Atau Anda dapat memilih autentikasi kata sandi Direktori Aktif dan memberikan kredensial pengguna yang berwenang di ID Microsoft Entra. Jika akses menyeluruh Active Directory diaktifkan, Anda dapat tersambung dengan menggunakan identitas Azure Anda.

The SQL Database sign-in page in the Azure portal.

Anda memasukkan kode T-SQL Anda di panel kueri lalu pilih Jalankan untuk menjalankannya. Jika pernyataan T-SQL adalah kueri, baris apa pun yang dikembalikan muncul di panel Hasil . Panel Pesan menampilkan informasi seperti jumlah baris yang dikembalikan atau kesalahan apa pun yang terjadi:

The query editor in the Azure portal with the various panes highlighted.

Untuk menggunakan utilitas sqlcmd, buka Cloud Shell dan jalankan perintah berikut. Ganti <server> dengan nama server database yang Anda buat, <database> dengan nama database Anda, dan <user name> dan <password> dengan kredensial Anda.

sqlcmd -S <server>.database.windows.net -d <database> -U <username> -P <password>

Jika perintah masuk berhasil, Anda akan melihat 1> perintah. Anda dapat memasukkan perintah T-SQL pada beberapa baris lalu mengetik GO untuk menjalankannya.

Mengimpor data secara massal dengan bcp

Microsoft menyediakan beberapa alat yang dapat Anda gunakan untuk mengunggah data ke database SQL Anda:

  • SQL Server Integration Services (SSIS)
  • Pernyataan SQL BULK INSERT
  • Utilitas Program Penyalinan Massal (bcp)

Utilitas bcp ini sering digunakan karena nyaman dan dapat dengan mudah ditulis untuk mengimpor data ke beberapa tabel. Utilitas bcp adalah alat baris perintah yang dapat Anda gunakan untuk mengimpor dan mengekspor data dari database. Untuk mengimpor data, bcp memerlukan tiga hal berikut:

  • Data sumber yang akan diunggah.
  • Tabel yang sudah ada di database tujuan.
  • File format yang menentukan format data dan cara memetakan data ke kolom dalam tabel tujuan.

Utilitasnya bcp fleksibel. Data sumber dapat berada dalam hampir semua format terstruktur. File format menunjukkan tata letak data dan apakah data tersebut biner atau berbasis karakter. Ini juga menentukan jenis dan panjang setiap item, dan bagaimana data dipisahkan. File format juga menentukan cara memetakan setiap item dalam file ke kolom dalam tabel. Penting untuk menentukan isi file ini dengan benar. Jika tidak, data Anda mungkin tidak diimpor, atau data mungkin dibaca ke dalam kolom yang salah.

Misalkan Anda memiliki data berikut dalam file mydata.csv, dan Anda ingin mengimpor data ini ke dalam tabel MyTable yang kami buat sebelumnya.

Column1,Column2
99,some text
101,some more text
97,another bit of text
87,yet more text
33,a final bit of text

Baris pertama berisi nama bidang yang tidak sama dengan kolom dalam tabel. Data dipisahkan koma, dan setiap baris dihentikan dengan karakter baris baru. Perlu diingat bahwa urutan kolom dalam file mungkin berbeda dari tabel. Dalam contoh ini, kolom pertama dalam tabel adalah numerik, dan kolom kedua adalah string, sebagai berikut:

CREATE TABLE MyTable
(
    MyColumn1 INT NOT NULL PRIMARY KEY,
    MyColumn2 VARCHAR(50) NOT NULL
);

Anda dapat menggunakan perintah bcp untuk membuat file format untuk diimpor. Perintah bcp dapat membuat file format berdasarkan skema tabel target dalam database. Anda kemudian dapat mengedit file agar sesuai dengan data dalam file sumber.

Jalankan perintah berikut untuk membuat file format. Ganti item dalam kurung sudut dengan nilai untuk database, server, nama pengguna, dan kata sandi Anda:

bcp <database>.dbo.mytable format nul -c -f mytable.fmt -t, -S <server>.database.windows.net -U <username> -P <password>

Utilitas bcp ini memiliki beberapa parameter yang mengontrol fungsionalitas utilitas. Anda dapat menentukan:

  • Tabel target (<database>.<schema>.<table>)
  • Data yang akan diimpor dan detail tentang data (format nul -c -f mytable.fmt -t,)
  • Detail koneksi untuk database Anda (-S <server>.database.windows.net -U <username> -P <password>)

Untuk parameter sintaks penuh dan baris perintah untuk utilitas, lihat dokumentasi bantuan.

Perintah menghasilkan file format mytable.fmt dengan konten yang terlihat seperti ini:

14.0
2
1       SQLCHAR             0       12      ","    1     MyColumn1                                ""
2       SQLCHAR             0       50      "\n"   2     MyColumn2                                SQL_Latin1_General_CP1_CI_AS

Baris pertama memperlihatkan nomor versi internal SQL Database. Baris kedua memperlihatkan jumlah kolom dalam tabel sumber. Dua baris terakhir menunjukkan cara memetakan data dalam file sumber ke kolom tersebut.

Kedua baris dimulai dengan angka yang merupakan nomor kolom dalam tabel. Bidang kedua (SQLCHAR) menentukan bahwa ketika kita menggunakan file format ini untuk mengimpor data, setiap bidang dalam file sumber berisi data karakter. bcp Utilitas mencoba mengonversi data ini ke jenis yang sesuai untuk kolom terkait dalam tabel. Bidang berikutnya (12 dan 50) adalah panjang data di setiap kolom dalam database. Jangan ubah bidang ini! Item berikut ("," dan "\n") masing-masing adalah terminator bidang dalam file sumber dan karakter baru. Kolom berikutnya adalah nomor bidang dalam file sumber. Bidang kedua hingga terakhir (MyColumn1 dan MyColumn2) adalah nama kolom dalam database. Bidang terakhir adalah kolase yang digunakan, yang hanya berlaku untuk data karakter dalam database.

Ingat bahwa bidang dalam file sumber berada dalam urutan yang berbeda dari kolom dalam database. Jadi Anda harus mengedit file format dan mengubah nomor bidang, seperti yang diperlihatkan di sini:

14.0
2
1       SQLCHAR             0       12      ","    2     MyColumn1                                ""
2       SQLCHAR             0       50      "\n"   1     MyColumn2                                SQL_Latin1_General_CP1_CI_AS

Data di bidang 2 dalam file sumber dipetakan ke kolom pertama dalam database. Bidang 1 dipetakan ke kolom kedua.

Sekarang Anda dapat menggunakan perintah bcp untuk mengimpor data, sebagai berikut:

bcp <database>.dbo.mytable in mydata.csv -f mytable.fmt -S <server>.database.windows.net -U <username> -P <password> -F 2

Bendera in menentukan bahwa kita menggunakan bcp untuk mengimpor data. Anda dapat menggunakan out untuk mentransfer data dari database ke sebuah file. Bendera -F 2 menunjukkan bahwa operasi impor harus dimulai dari baris 2 dari file sumber. Ingatlah bahwa baris pertama berisi header dan bukan data.

Perintah harus berjalan dengan sukses dan mengembalikan pesan yang mirip dengan contoh-contoh ini:

Starting copy...

5 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 46     Average : (108.7 rows per sec.)

Baris penting dalam output adalah "5 baris disalin." Jumlah baris dalam file sumber yang berisi data yang diimpor. Jika angka ini berbeda (atau 0), file format Anda mungkin salah.

Mengkueri data

Verifikasi bahwa impor berhasil dengan mengkueri data. Anda dapat menggunakan editor kueri dari portal Azure. Atau Anda dapat menggunakan utilitas sqlcmd untuk menyambungkan ke database dari baris perintah. Dalam kedua kasus, Anda menjalankan pernyataan SELECT seperti ini:

SELECT *
FROM MyTable;

Anda akan melihat hasil berikut.

The query editor in the Azure portal shows the results of a query.