Membuat objek database eksternal

6 menit

Anda dapat menggunakan fungsi OPENROWSET dalam kueri SQL yang berjalan di database master default dari kumpulan SQL tanpa server bawaan untuk menjelajahi data di data lake. Namun, terkadang Anda mungkin ingin membuat database kustom yang berisi beberapa objek yang memudahkan untuk bekerja dengan data eksternal di data lake yang perlu Sering Anda kueri.

Membuat database

Anda dapat membuat database di kumpulan SQL tanpa server seperti yang Anda lakukan dalam instans SQL Server. Anda dapat menggunakan antarmuka grafis di Synapse Studio, atau pernyataan CREATE DATABASE. Salah satu pertimbangannya adalah mengatur kolase database Anda sehingga mendukung konversi data teks dalam file ke jenis data Transact-SQL yang sesuai.

Contoh kode berikut membuat database bernama salesDB dengan kolase yang memudahkan untuk mengimpor data teks yang dikodekan UTF-8 ke dalam kolom VARCHAR.

CREATE DATABASE SalesDB
    COLLATE Latin1_General_100_BIN2_UTF8

Membuat sumber data eksternal

Anda dapat menggunakan fungsi OPENROWSET dengan jalur MASSAL untuk mengkueri data file dari database Anda sendiri, seperti yang Anda bisa di database master; tetapi jika Anda berencana untuk sering mengkueri data di lokasi yang sama, lebih efisien untuk menentukan sumber data eksternal yang mereferensikan lokasi tersebut. Misalnya, kode berikut membuat sumber data bernama file untuk folder https://mydatalake.blob.core.windows.net/data/files/ hipotetis:

CREATE EXTERNAL DATA SOURCE files
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/files/'
)

Salah satu manfaat sumber data eksternal, adalah Anda dapat menyederhanakan kueri OPENROWSET untuk menggunakan kombinasi sumber data dan jalur relatif ke folder atau file yang ingin Anda kueri:

SELECT *
FROM
    OPENROWSET(
        BULK 'orders/*.csv',
        DATA_SOURCE = 'files',
        FORMAT = 'csv',
        PARSER_VERSION = '2.0'
    ) AS orders

Dalam contoh ini, parameter massal digunakan untuk menentukan jalur relatif untuk semua file .csv dalam folder urutan, yang merupakan subfolder file folder yang dirujuk oleh sumber data.

Manfaat lain menggunakan sumber data adalah Anda dapat menetapkan kredensial untuk digunakan sumber data saat mengakses penyimpanan yang mendasar, memungkinkan Anda memberikan akses ke data melalui SQL tanpa mengizinkan pengguna untuk mengakses data secara langsung di akun penyimpanan. Misalnya, kode berikut membuat kredensial yang menggunakan tanda tangan akses bersama (SAS) untuk mengautentikasi terhadap akun penyimpanan Azure yang mendasar yang menghosting data lake.

CREATE DATABASE SCOPED CREDENTIAL sqlcred
WITH
    IDENTITY='SHARED ACCESS SIGNATURE',  
    SECRET = 'sv=xxx...';
GO

CREATE EXTERNAL DATA SOURCE secureFiles
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/secureFiles/'
    CREDENTIAL = sqlcred
);
GO

Petunjuk / Saran

Selain autentikasi SAS, Anda dapat menentukan kredensial yang menggunakan identitas terkelola (identitas Microsoft Entra yang digunakan oleh ruang kerja Azure Synapse Anda), perwakilan Microsoft Entra tertentu, atau autentikasi passthrough berdasarkan identitas pengguna yang menjalankan kueri (yang merupakan jenis autentikasi default). Untuk mempelajari selengkapnya tentang menggunakan kredensial di kumpulan SQL tanpa server, lihat artikel Mengontrol akses akun penyimpanan untuk kumpulan SQL tanpa server di Azure Synapse Analytics dalam dokumentasi Azure Synapse Analytics.

Membuat format file eksternal

Meskipun sumber data eksternal menyederhanakan kode yang diperlukan untuk mengakses file dengan fungsi OPENROWSET, Anda masih perlu memberikan detail format untuk file yang sedang diakses; yang mungkin mencakup beberapa pengaturan untuk file teks yang dibatasi. Anda dapat merangkum pengaturan ini dalam format file eksternal, seperti ini:

CREATE EXTERNAL FILE FORMAT CsvFormat
    WITH (
        FORMAT_TYPE = DELIMITEDTEXT,
        FORMAT_OPTIONS(
            FIELD_TERMINATOR = ',',
            STRING_DELIMITER = '"'
        )
    );
GO

Setelah membuat format file untuk file data tertentu yang perlu Anda kerjakan, Anda dapat menggunakan format file untuk membuat tabel eksternal, seperti yang dibahas berikutnya.

Membuat tabel eksternal

Ketika Anda perlu melakukan banyak analisis atau pelaporan dari file di data lake, menggunakan fungsi OPENROWSET dapat menghasilkan kode kompleks yang mencakup sumber data dan jalur file. Untuk menyederhanakan akses ke data, Anda dapat merangkum file dalam tabel eksternal; pengguna dan aplikasi pelaporan mana yang dapat mengkueri menggunakan pernyataan SQL SELECT standar sama seperti tabel database lainnya. Untuk membuat tabel eksternal, gunakan pernyataan CREATE EXTERNAL TABLE, menentukan skema kolom sebagai untuk tabel standar, dan termasuk klausa WITH yang menentukan sumber data eksternal, jalur relatif, dan format file eksternal untuk data Anda.

CREATE EXTERNAL TABLE dbo.products
(
    product_id INT,
    product_name VARCHAR(20),
    list_price DECIMAL(5,2)
)
WITH
(
    DATA_SOURCE = files,
    LOCATION = 'products/*.csv',
    FILE_FORMAT = CsvFormat
);
GO

-- query the table
SELECT * FROM dbo.products;

Dengan membuat database yang berisi objek eksternal yang dibahas dalam unit ini, Anda dapat menyediakan lapisan database relasional atas file di data lake, sehingga memudahkan banyak analis data dan alat pelaporan untuk mengakses data dengan menggunakan semantik kueri SQL standar.

Saran dan Komentar

Apakah halaman ini membantu?