Memuat data dengan aman menggunakan Synapse SQL

Artikel ini menyoroti dan memberikan contoh mekanisme autentikasi yang aman untuk pernyataan COPY. Pernyataan COPY adalah cara paling fleksibel dan aman untuk memuat data secara massal di Synapse SQL.

Mekanisme autentikasi yang didukung

Matriks berikut menjelaskan metode autentikasi yang didukung untuk setiap jenis file dan akun penyimpanan. Ini berlaku untuk lokasi penyimpanan sumber dan lokasi file kesalahan.

	CSV	Parquet	ORC
Penyimpanan blob Azure	SAS/MSI/SERVICE PRINCIPAL/KEY/AAD	SAS/KEY	SAS/KEY
Azure Data Lake Gen2	SAS/MSI/SERVICE PRINCIPAL/KEY/AAD	SAS (blob1)/MSI (dfs2)/SERVICE PRINCIPAL/KEY/AAD	SAS (blob1)/MSI (dfs2)/SERVICE PRINCIPAL/KEY/AAD

1: Titik akhir .blob (.blob.core.windows.net) di jalur lokasi eksternal Anda diperlukan untuk metode autentikasi ini.

2: Titik akhir .dfs (.dfs.core.windows.net) di jalur lokasi eksternal Anda diperlukan untuk metode autentikasi ini.

J. Kunci akun penyimpanan dengan LF sebagai pemisah baris (baris baru gaya Unix)

--Note when specifying the column list, input field numbers start from 1
COPY INTO target_table (Col_one default 'myStringDefault' 1, Col_two default 1 3)
FROM 'https://adlsgen2account.dfs.core.windows.net/myblobcontainer/folder1/'
WITH (
    FILE_TYPE = 'CSV'
    ,CREDENTIAL=(IDENTITY= 'Storage Account Key', SECRET='<Your_Account_Key>')
    --CREDENTIAL should look something like this:
    --CREDENTIAL=(IDENTITY= 'Storage Account Key', SECRET='<secret>'),
    ,ROWTERMINATOR='0x0A' --0x0A specifies to use the Line Feed character (Unix based systems)
)

Penting

• Gunakan nilai heksadesimal (0x0A) untuk menentukan karakter Umpan Baris/Baris Baru. Perhatikan bahwa pernyataan COPY akan menafsirkan string \n sebagai \r\n (baris baru kembali ke awal).

B. Tanda Tangan Akses Bersama (SAS) dengan CRLF sebagai pemisah baris (baris baru gaya Windows)

COPY INTO target_table
FROM 'https://adlsgen2account.dfs.core.windows.net/myblobcontainer/folder1/'
WITH (
    FILE_TYPE = 'CSV'
    ,CREDENTIAL=(IDENTITY= 'Shared Access Signature', SECRET='<Your_SAS_Token>')
    --CREDENTIAL should look something like this:
    --CREDENTIAL=(IDENTITY= 'Shared Access Signature', SECRET='?sv=2018-03-28&ss=bfqt&srt=sco&sp=rl&st=2016-10-17T20%3A14%3A55Z&se=2021-10-18T20%3A19%3A00Z&sig=IEoOdmeYnE9%2FKiJDSFSYsz4AkN'),
    ,ROWTERMINATOR='\n'-- COPY command automatically prefixes the \r character when \n (newline) is specified. This results in carriage return newline (\r\n) for Windows based systems.
)

Penting

Jangan tentukan ROWTERMINATOR sebagai '\r\n' yang akan ditafsirkan sebagai '\r\r\n' dan dapat mengakibatkan masalah penguraian. Perintah COPY secara otomatis mengawali karakter \r saat \n (baris baru) ditentukan. Hal ini mengakibatkan baris baru kembali ke awal (\r\n) untuk sistem berbasis Windows.

C. Identitas Terkelola

Autentikasi Identitas Terkelola diperlukan saat akun penyimpanan Anda dilampirkan ke VNet.

Prasyarat

1. Pasang Azure PowerShell. Lihat Menginstal PowerShell.
2. Jika Anda memiliki akun penyimpanan v1 atau penyimpanan blob tujuan umum, Anda harus terlebih dahulu meningkatkan ke v2 tujuan umum. Lihat Meningkatkan versi ke akun penyimpanan v2 tujuan umum.
3. Anda harus mengaktifkan izin Mengizinkan layanan Microsoft terpercaya untuk mengakses akun penyimpanan ini di bagian menu pengaturan Firewall dan jaringan virtual akun Azure Storage. Lihat Mengonfigurasi firewall dan jaringan virtual Azure Storage.

Langkah-langkah

1. Jika Anda memiliki kumpulan SQL khusus mandiri, daftarkan server SQL Anda dengan MICROSOFT Entra ID menggunakan PowerShell:

   Connect-AzAccount
   Select-AzSubscription -SubscriptionId <subscriptionId>
   Set-AzSqlServer -ResourceGroupName your-database-server-resourceGroup -ServerName your-SQL-servername -AssignIdentity
   

   Langkah ini tidak diperlukan untuk kumpulan SQL khusus dalam ruang kerja Synapse. Identitas terkelola yang ditetapkan sistem (SA-MI) dari ruang kerja adalah anggota dari peran Administrator Synapse dan dengan demikian memiliki hak yang lebih tinggi pada kumpulan SQL khusus dari ruang kerja.

2. Buat Akun Penyimpanan v2 tujuan umum. Untuk informasi selengkapnya, lihat Membuat akun penyimpanan.

   Catatan

   • Jika Anda memiliki akun penyimpanan v1 atau blob tujuan umum, Anda harus meningkatkan versi ke v2 terlebih dahulu. Untuk informasi selengkapnya, lihat Meningkatkan ke akun penyimpanan v2 tujuan umum.
   • Untuk mengetahui masalah umum tentang Azure Data Lake Storage Gen2, lihat Masalah umum dengan Azure Data Lake Storage Gen2.

3. Di bawah akun penyimpanan Anda, pilih Kontrol akses (IAM).

4. Pilih Tambahkan>Tambahkan penetapan peran untuk membuka halaman Tambahkan penetapan peran.

5. Tetapkan peran berikut. Untuk langkah-langkah mendetail, lihat Menetapkan peran Azure menggunakan portal Azure.

   Pengaturan	Nilai
   Peran	Data blob penyimpanan kontributor
   Tetapkan akses ke	SERVICEPRINCIPAL
   Anggota	server atau ruang kerja yang menghosting kumpulan SQL khusus yang telah Anda daftarkan dengan ID Microsoft Entra

   

   Catatan

   Hanya anggota yang memiliki hak istimewa Pemilik yang dapat melakukan langkah ini. Untuk mengetahui berbagai peran bawaan Azure, lihat Peran bawaan Azure.

   Penting

   Tentukan peran Azure Pemilik, Kontributor, atau Pembaca Data BlobPenyimpanan. Peran ini berbeda dari peran bawaan Azure Pemilik, Kontributor, dan Pembaca.

   

6. Sekarang Anda dapat menjalankan pernyataan COPY yang menentukan "Identitas Terkelola":

   COPY INTO dbo.target_table
   FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/*.txt'
   WITH (
       FILE_TYPE = 'CSV',
       CREDENTIAL = (IDENTITY = 'Managed Identity'),
   )
   

D. Autentikasi Microsoft Entra

Langkah-langkah

1. Di bawah akun penyimpanan Anda, pilih Kontrol akses (IAM).

2. Pilih Tambahkan>Tambahkan penetapan peran untuk membuka halaman Tambahkan penetapan peran.

3. Tetapkan peran berikut. Untuk langkah-langkah mendetail, lihat Menetapkan peran Azure menggunakan portal Azure.

   Pengaturan	Nilai
   Peran	Pemilik Data Storage Blob, Kontributor, atau Pembaca
   Tetapkan akses ke	USER
   Anggota	Pengguna Microsoft Entra

   

   Penting

   Tentukan peran Azure Pemilik, Kontributor, atau Pembaca Data BlobPenyimpanan. Peran ini berbeda dari peran bawaan Azure Pemilik, Kontributor, dan Pembaca.

   

4. Mengonfigurasi autentikasi Microsoft Entra. Lihat Mengonfigurasi dan mengelola autentikasi Microsoft Entra dengan Azure SQL.

5. Sambungkan ke kumpulan SQL Anda menggunakan Direktori Aktif tempat Anda sekarang dapat menjalankan pernyataan COPY tanpa menentukan info masuk apa pun:

   COPY INTO dbo.target_table
   FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/*.txt'
   WITH (
       FILE_TYPE = 'CSV'
   )
   

E. Autentikasi Perwakilan Layanan

Langkah-langkah

1. Buat aplikasi Microsoft Entra.

2. Dapatkan ID aplikasi.

3. Dapatkan kunci autentikasi.

4. Dapatkan titik akhir token V1 OAuth 2.0.

5. Tetapkan izin baca, tulis, dan eksekusi ke aplikasi Microsoft Entra Anda di akun penyimpanan Anda.

6. Sekarang Anda dapat menjalankan pernyataan COPY:

   COPY INTO dbo.target_table
   FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder0/*.txt'
   WITH (
       FILE_TYPE = 'CSV'
       ,CREDENTIAL=(IDENTITY= '<application_ID>@<OAuth_2.0_Token_EndPoint>' , SECRET= '<authentication_key>')
       --CREDENTIAL should look something like this:
       --,CREDENTIAL=(IDENTITY= '92761aac-12a9-4ec3-89b8-7149aef4c35b@https://login.microsoftonline.com/72f714bf-86f1-41af-91ab-2d7cd011db47/oauth2/token', SECRET='juXi12sZ6gse]woKQNgqwSywYv]7A.M')
   )
   

Penting

Gunakan versi V1 dari titik akhir token OAuth 2.0

Langkah berikutnya

• Periksa artikel pernyataan COPY untuk sintaks terperinci
• Periksa artikel gambaran umum pemuatan data untuk praktik terbaik pemuatan