Menyerap data ke Gudang Anda menggunakan Transact-SQL

Diterapkan pada:✅ Gudang di Microsoft Fabric

Bahasa Transact-SQL menawarkan opsi yang dapat Anda gunakan untuk memuat data dalam skala besar dari tabel yang ada di lakehouse dan gudang Anda ke tabel baru di gudang Anda. Opsi ini nyaman jika Anda perlu membuat versi baru tabel dengan data agregat, versi tabel dengan subset baris, atau untuk membuat tabel sebagai hasil dari kueri kompleks. Mari kita jelajahi beberapa contoh.

Membuat tabel baru dengan hasil kueri

Gudang di Microsoft Fabric memungkinkan Anda membuat tabel baru dengan mudah berdasarkan hasil kueri T-SQL, menggunakan pernyataan T-SQL berikut:

• CREATE TABLE AS SELECT Pernyataan (CTAS) yang memungkinkan Anda membuat tabel baru di gudang Anda dari output SELECT pernyataan.
• SELECT INTO klausa kueri yang memungkinkan Anda memilih hasil dari sumber tabel mana pun, dan mengalihkan hasilnya ke tabel baru. Ini adalah fitur standar dalam bahasa T-SQL.

Kedua pernyataan ini serupa, sehingga contoh berikut difokuskan pada pernyataan CTAS.

Pernyataan CTAS menjalankan operasi penyerapan ke dalam tabel baru secara paralel, membuatnya sangat efisien untuk transformasi data dan pembuatan tabel baru di ruang kerja Anda.

Anda dapat menggunakan opsi berikut untuk bagian SELECT pernyataan CTAS:

• Membaca tabel gudang, seperti tabel penahapan.
• Membaca folder Lakehouse Delta Lake menggunakan tabel yang dibuat secara otomatis di titik akhir analitik SQL untuk Lakehouse.
• Membaca file CSV, Parquet, atau JSONL langsung dari Azure Data Lake atau penyimpanan Azure Blob menggunakan fungsi OPENROWSET.

Note

Contoh dalam artikel ini menggunakan himpunan data sampel Bing COVID-19. Untuk memuat himpunan data sampel, ikuti langkah-langkah dalam Menyerap data ke gudang Anda menggunakan pernyataan COPY untuk membuat data sampel ke gudang Anda.

Membuat tabel dari tabel Gudang

Contoh pertama menggambarkan cara membuat tabel baru yang merupakan salinan tabel yang sudah ada dbo.bing_covid19_data_2023 , tetapi difilter ke data dari tahun 2023 saja:

CREATE TABLE dbo.bing_covid19_data_2023
AS
SELECT * 
FROM dbo.bing_covid19_data 
WHERE DATEPART(YEAR, updated) = '2023';

Anda juga dapat membuat tabel baru dengan kolom year, month, dayofmonth baru, dengan nilai yang diperoleh dari kolom updated dalam tabel sumber. Ini dapat berguna jika Anda mencoba memvisualisasikan data infeksi menurut tahun, atau untuk melihat bulan ketika kasus COVID-19 terbanyak diamati:

CREATE TABLE dbo.bing_covid19_data_with_year_month_day
AS
SELECT DATEPART(YEAR, updated) AS [year],
       DATEPART(MONTH, updated) AS [month],
       DATEPART(DAY, updated) AS [dayofmonth],
       * 
FROM dbo.bing_covid19_data;

Sebagai contoh lain, Anda dapat membuat tabel baru yang meringkas jumlah kasus yang diamati dalam setiap bulan, terlepas dari tahun tersebut, untuk mengevaluasi bagaimana musiman memengaruhi penyebaran di negara/wilayah tertentu. Ini menggunakan tabel yang dibuat dalam contoh sebelumnya dengan kolom baru month sebagai sumber:

CREATE TABLE dbo.infections_by_month
AS
SELECT country_region, [month],
       SUM(CAST(confirmed as bigint)) AS confirmed_sum
FROM dbo.bing_covid19_data_with_year_month_day
GROUP BY country_region, [month];

Berdasarkan tabel baru ini, kita dapat melihat bahwa Amerika Serikat mengamati kasus yang lebih terkonfirmasi sepanjang tahun di bulan January, diikuti oleh December dan October. April adalah bulan dengan jumlah kasus terendah secara keseluruhan:

SELECT * FROM dbo.infections_by_month
WHERE country_region = 'United States'
ORDER BY confirmed_sum DESC;

Membuat tabel dari folder Delta Lake

Folder Delta Lake yang bertahan di OneLake secara otomatis diwakili sebagai tabel jika disimpan di folder /Tables di lakehouse. Kode berikut membuat tabel bing_covid19_data_2023 baru dari folder Delta Lake /Tables/bing_covid19_delta_lake di lakehouse MyLakehouse :

CREATE TABLE dbo.bing_covid19_data_2023
AS
SELECT * 
FROM MyLakehouse.dbo.bing_covid19_delta_lake 
WHERE DATEPART(YEAR, updated) = '2023';

Anda dapat mereferensikan folder Delta Lake menggunakan notasi tiga nama bagian yang mereferensikan lakehouse tempat file disimpan. Semua contoh yang ditampilkan di bagian sebelumnya berlaku untuk folder Delta Lake.

Membuat tabel dari file CSV/Parquet/JSONL

Alih-alih membaca data dari tabel Gudang bing_covid19_data , Anda juga dapat membuat tabel baru langsung dari file eksternal menggunakan OPENROWSET fungsi :

CREATE TABLE dbo.bing_covid19_data_2022
AS
SELECT *
FROM OPENROWSET(BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet') AS data
WHERE DATEPART(YEAR, updated) = '2022';

Anda juga dapat membuat tabel baru dengan mengubah data dari file CSV eksternal:

CREATE TABLE dbo.bing_covid19_data_with_year_month_day
AS
SELECT DATEPART(YEAR, updated) AS [year], 
       DATEPART(MONTH, updated) AS [month],
       DATEPART(DAY, updated) AS [dayofmonth],
       *
FROM OPENROWSET(BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv') AS data;

Sebagai contoh lain, Anda dapat membuat tabel baru yang meringkas jumlah kasus yang diamati dalam setiap bulan, terlepas dari tahun tersebut, untuk mengevaluasi bagaimana musiman memengaruhi penyebaran di negara/wilayah tertentu. Ini menggunakan tabel yang dibuat dalam contoh sebelumnya dengan kolom baru month sebagai sumber:

CREATE TABLE dbo.infections_by_month_2022
AS
SELECT country_region,
       DATEPART(MONTH, updated) AS [month],
       SUM(CAST(confirmed as bigint)) AS [confirmed_sum]
FROM OPENROWSET(BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.jsonl') AS data
WHERE DATEPART(YEAR, updated) = '2022'
GROUP BY country_region, DATEPART(MONTH, updated);

Berdasarkan tabel baru ini, kita dapat melihat bahwa Amerika Serikat mengamati kasus yang lebih terkonfirmasi sepanjang tahun di bulan January, diikuti oleh December dan October. April adalah bulan dengan jumlah kasus terendah secara keseluruhan:

SELECT * FROM dbo.infections_by_month_2022
WHERE country_region = 'United States'
ORDER BY confirmed_sum DESC;

• Untuk informasi dan contoh selengkapnya tentang mengkueri data eksternal, lihat Mengkueri file data lake eksternal dengan menggunakan Fabric Data Warehouse atau titik akhir analitik SQL.
• Untuk contoh dan referensi sintaks lainnya, lihat CREATE TABLE AS SELECT (Transact-SQL).

Menyerap data ke dalam tabel yang ada dengan kueri T-SQL

Contoh sebelumnya membuat tabel baru berdasarkan hasil kueri. Untuk mereplikasi contoh tetapi pada tabel yang ada, pola INSERT ... SELECT dapat digunakan.

Mengambil data dari tabel Gudang

Kode berikut menyerap data baru dari tabel gudang ke dalam tabel yang sudah ada:

INSERT INTO dbo.bing_covid19_data_2023
SELECT *
FROM dbo.bing_covid19_data
WHERE DATEPART(YEAR, updated) = '2023';

Kriteria kueri untuk SELECT pernyataan bisa berupa kueri yang valid, selama tipe kolom kueri yang dihasilkan selaras dengan kolom pada tabel tujuan. Jika nama kolom ditentukan dan hanya menyertakan subset kolom dari tabel tujuan, semua kolom lain dimuat sebagai NULL. Untuk informasi selengkapnya, lihat Menggunakan INSERT INTO... SELECT untuk Mengimpor data secara massal dengan pengelogan dan paralelisme minimal.

Menyerap data dari folder Delta Lake

Folder Delta Lake yang bertahan di OneLake secara otomatis direpresentasikan sebagai tabel jika disimpan dalam /Tables folder di lakehouse.

Kode berikut menyerap data baru dari bagian folder /Tables/bing_covid19_delta_lake Delta Lake di MyLakehouse* lakehouse

INSERT INTO dbo.bing_covid19_data_2023
SELECT *
FROM MyLakehouse.dbo.bing_covid19_delta_lake 
WHERE DATEPART(YEAR, updated) = '2023';

Menyerap data dari file CSV/Parquet/JSONL

Anda dapat menggunakan OPENROWSET fungsi sebagai sumber untuk menyerap file Parquet, CSV, atau JSON dari penyimpanan:

INSERT INTO dbo.bing_covid19_data_2023
SELECT *
FROM OPENROWSET(BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet') AS data
WHERE DATEPART(YEAR, updated) = '2023';

Anda dapat membaca beberapa file dengan menggunakan kartubebas seperti *.parquet, atau dengan menargetkan direktori yang dipartisi seperti /year=*/month=*. Untuk mengoptimalkan performa, terapkan filter dalam klausul WHERE untuk menghilangkan baris dan partisi yang tidak perlu selama eksekusi kueri.

Contoh ini mirip dengan yang digunakan dalam penyerapan dengan COPY INTO. Perintah COPY INTO lebih mudah digunakan, terutama untuk pemuatan data sumber-ke-tujuan yang mudah. Namun, jika Anda perlu mengubah data sumber (seperti mengonversi nilai atau bergabung dengan tabel lain), menggunakan INSERT ... SELECT memberi Anda fleksibilitas untuk melakukan transformasi selama penyerapan.

Menyerap data dari OneLake

Anda dapat menggunakan OPENROWSET fungsi sebagai sumber untuk menyerap data dari penyimpanan Fabric OneLake. Ganti {workspaceId} dan {lakehouseId} dengan GUID ruang kerja dan lakehouse yang sesuai pada contoh berikut:

INSERT INTO dbo.bing_covid19_data_2023
SELECT *
FROM OPENROWSET(BULK 'https://onelake.dfs.fabric.microsoft.com/{workspaceId}/{lakehouseId}/Files/year=*/month=*/*.parquet') AS data
WHERE data.filepath(1) = '2023'

Contoh ini mengembangkan dari contoh sebelumnya yang membaca data dari Azure Data Lake Storage. Gunakan pendekatan ini saat Anda perlu mengubah data sumber, misalnya, mengonversi nilai, bergabung dengan tabel lain, atau membaca partisi tertentu. Dalam kasus seperti itu, menggunakan INSERT ... SELECT memberikan fleksibilitas untuk menerapkan transformasi selama penyerapan data.

Menyerap data dari tabel di berbagai gudang dan lakehouse

Untuk CREATE TABLE AS SELECT dan INSERT ... SELECT, pernyataan SELECT juga dapat mereferensikan tabel pada gudang yang berbeda dari gudang tempat tabel tujuan Anda disimpan, dengan menggunakan kueri antar gudang. Ini dapat dicapai dengan menggunakan konvensi [warehouse_or_lakehouse_name.][schema_name.]table_name penamaan tiga bagian. Misalnya, Anda memiliki aset ruang kerja berikut:

• Sebuah rumah danau bernama cases_lakehouse dengan data kasus terbaru.
• Gudang bernama reference_warehouse dengan tabel yang digunakan untuk data referensi.
• Gudang bernama research_warehouse tempat tabel tujuan dibuat.

Tabel baru dapat dibuat yang menggunakan penamaan tiga bagian untuk menggabungkan data dari tabel pada aset ruang kerja ini:

CREATE TABLE research_warehouse.dbo.cases_by_continent
AS
SELECT *
FROM cases_lakehouse.dbo.bing_covid19_data AS cases
INNER JOIN reference_warehouse.dbo.bing_covid19_data AS reference
ON cases.iso3 = reference.countrycode;

Untuk mempelajari selengkapnya tentang kueri lintas gudang, lihat Menulis Kueri SQL lintas database.

Mengaudit dan memantau penyerapan T-SQL

Operasi CTAS dan INSERT ... SELECT yang dijalankan melalui T-SQL muncul di riwayat/aktivitas kueri gudang, dan dapat dipantau bersama operasi gudang lainnya.

Opsi penyerapan data

Cara lain untuk menyerap data ke gudang Anda meliputi:

• Memasukkan data menggunakan pernyataan COPY
• Menyerap data menggunakan alur
• Menyerap data menggunakan aliran data

Konten terkait

• Menyerap data ke gudang
• Memecahkan masalah kesalahan penyerapan
• Menulis Kueri SQL lintas database