Zephyrnet Logosu

Üretken Veri Zekası

büyük Veri

Amazon Redshift | Amazon Web Hizmetleri

Plato tarafından yeniden yayınlandı
Plato tarafından yeniden yayınlandı

Tarih:

Görüntüleme: 135

Amazon Kırmızıya Kaydırma bulutta tam olarak yönetilen, petabayt ölçeğinde bir veri ambarı hizmetidir. On binlerce müşteri, analitik iş yüklerini güçlendirmek için her gün exabaytlarca veriyi işlemek için Amazon Redshift'i kullanıyor.

Amazon Redshift, analitik işlemeyi geliştirmek için aşağıdakiler gibi birçok özellik ekledi: ROLLUP, KÜP ve GRUPLAMA SETLERİ, gönderide gösterilen ROLLUP, CUBE ve GROUPING SETS gibi yeni SQL yapılarını kullanarak Amazon Redshift'te Çevrimiçi Analitik İşleme (OLAP) sorgularını basitleştirin. Amazon Redshift yakın zamanda birçok SQL komutu ve ifadesi ekledi. Bu yazıda iki yeni SQL özelliğinden bahsedeceğiz: BİRLEŞTİRMEK komuta ve NİTELEMEK veri alımını ve veri filtrelemeyi basitleştiren madde.

Çoğu aşağı yönlü uygulamadaki tanıdık görevlerden biri, değişiklik verilerinin yakalanması (CDC) ve bunun hedef tablolara uygulanmasıdır. Bu görev, kaynak verinin bir güncelleme mi yoksa mevcut hedef verilere bir ekleme mi olduğunu belirlemek için incelenmesini gerektirir. MERGE komutu olmadan, bir iş anahtarı kullanarak yeni veri kümesini mevcut veri kümesine göre test etmeniz gerekiyordu. Bu eşleşmediğinde mevcut veri kümesine yeni satırlar eklediniz; aksi takdirde mevcut veri kümesi satırlarını yeni veri kümesi değerleriyle güncellersiniz.

The BİRLEŞTİRMEK komut, kaynak tablodaki satırları koşullu olarak hedef tabloyla birleştirir. Geleneksel olarak bu yalnızca birden fazla ekleme, güncelleme veya silme ifadesinin ayrı ayrı kullanılmasıyla gerçekleştirilebilirdi. Verileri güncellemek veya eklemek için birden fazla ifade kullanıldığında, farklı işlemler arasında tutarsızlık riski vardır. Birleştirme işlemi tüm işlemlerin tek bir işlemde birlikte yapılmasını sağlayarak bu riski azaltır.

The NİTELEMEK yan tümcesi, önceden hesaplanan bir pencere fonksiyonunun sonuçlarını kullanıcı tarafından belirlenen arama koşullarına göre filtreler. Bir alt sorgu kullanmadan bir pencere fonksiyonunun sonucuna filtreleme koşullarını uygulamak için yan tümceyi kullanabilirsiniz. Bu şuna benzer HAVING WHERE yan tümcesindeki satırları daha fazla filtrelemek için bir koşulu uygulayan yan tümce. QUALIFY ve HAVING arasındaki fark, QUALIFY yan tümcesinden filtrelenen sonuçların, veriler üzerinde pencere işlevlerini çalıştırmanın sonucuna dayanabilmesidir. Tek bir sorguda hem QUALIFY hem de HAVING yan tümcelerini kullanabilirsiniz.

Bu yazıda, CDC'yi uygulamak için MERGE komutunun nasıl kullanılacağını ve bu değişikliklerin doğrulanmasını basitleştirmek için QUALIFY'ın nasıl kullanılacağını göstereceğiz.

Çözüme genel bakış

Bu kullanım durumunda, kaynak sistemden her zaman en son verileri alması gereken bir müşteri boyut tablosunun bulunduğu bir veri ambarımız var. Bu veriler aynı zamanda denetim ve izleme amacıyla ilk oluşturma zamanını ve son güncelleme zamanını da yansıtmalıdır.

Bunu çözmenin basit bir yolu, müşteri boyutunu her gün tamamen geçersiz kılmaktır; ancak bu, bir denetim görevi olan güncelleme izlemeyi başaramaz ve daha büyük tablolar için bunu yapmak mümkün olmayabilir.

Talimatı takip ederek Amazon S3'ten örnek verileri yükleyebilirsiniz. okuyun. Aşağıdaki mevcut müşteri tablosunu kullanma sample_data_dev.tpcds, hem mevcut müşteriler için güncellemeleri hem de yeni müşteriler için eklemeleri içerecek bir müşteri boyutu tablosu ve bir kaynak tablosu oluşturuyoruz. Kaynak tablo verilerini hedef tablo (müşteri boyutu) ile birleştirmek için MERGE komutunu kullanırız. Ayrıca hedef tablodaki değişikliklerin doğrulanmasını basitleştirmek için QUALIFY yan tümcesinin nasıl kullanılacağını da gösteriyoruz.

Bu yazıdaki adımları takip etmek için beraberindekileri indirmenizi öneririz. defter, bu yazı için çalıştırılacak tüm komut dosyalarını içerir. Not defterlerini yazma ve çalıştırma hakkında bilgi edinmek için bkz. Not defterlerini yazma ve çalıştırma.

Önkoşullar

Aşağıdaki ön koşullara sahip olmalısınız:

Boyut tablosunu oluşturun ve doldurun

Aşağıdaki mevcut müşteri tablosunu kullanıyoruz sample_data_dev.tpcds Oluşturmak için customer_dimension masa. Aşağıdaki adımları tamamlayın:

  1. İş anahtarı da dahil olmak üzere seçilen birkaç alanı kullanarak bir tablo oluşturun ve zaman damgalarını eklemek ve güncellemek için birkaç bakım alanı ekleyin: 
     -- create the customer dimension table DROP TABLE IF EXISTS customer_dim CASCADE;
CREATE TABLE customer_dim ( customer_dim_id bigint GENERATED BY DEFAULT AS IDENTITY(1, 1), c_customer_sk integer NOT NULL ENCODE az64 distkey,
c_first_name character(20) ENCODE lzo,
c_last_name character(30) ENCODE lzo,
c_current_addr_sk integer ENCODE az64,
c_birth_country character varying(20) ENCODE lzo,
c_email_address character(50) ENCODE lzo,
record_insert_ts timestamp WITHOUT time ZONE DEFAULT current_timestamp ,
record_upd_ts timestamp WITHOUT time ZONE DEFAULT NULL
)
SORTKEY (c_customer_sk);

  2. Boyut tablosunu doldurun: 
    -- populate dimension insert into customer_dim (c_customer_sk, c_first_name,c_last_name, c_current_addr_sk, c_birth_country, c_email_address) select c_customer_sk, c_first_name,c_last_name, c_current_addr_sk, c_birth_country, c_email_address
from “sample_data_dev”.”tpcds”.”customer”;

  3. Satır sayısını ve tablonun içeriğini doğrulayın: 
    -- check customers count and look at sample data
select count(1) from customer_dim; select * from customer_dim limit 10;

Müşteri tablosu değişikliklerini simüle edin

Tabloda yapılan değişiklikleri simüle etmek için aşağıdaki kodu kullanın:

-- create a source table with some updates and some inserts
-- Update- Email has changed for 100 customers drop table if exists src_customer;
create table src_customer distkey(c_customer_sk) as select c_customer_sk , c_first_name , c_last_name, c_current_addr_sk, c_birth_country, ‘x’+c_email_address as c_email_address, getdate() as effective_dt
from customer_dim where c_email_address is not null
limit 100; -- also let’s add three completely new customers
insert into src_customer values (15000001, ‘Customer#15’,’000001’, 10001 ,’USA’ , ‘Customer#15000001@gmail.com’, getdate() ),
(15000002, ‘Customer#15’,’000002’, 10002 ,’MEXICO’ , ‘Customer#15000002@gmail.com’, getdate() ),
(15000003, ‘Customer#15’,’000003’, 10003 ,’CANADA’ , ‘Customer#15000003@gmail.com’, getdate() ); -- check source count
select count(1) from src_customer;

Kaynak tabloyu hedef tabloyla birleştirin

Artık müşteri boyutu tablosuyla birleştirmeniz gereken bazı değişiklikleri içeren bir kaynak tablonuz var.

MERGE komutundan önce, bu tür bir görevin uygulanması için iki ayrı UPDATE ve INSERT komutu gerekiyordu:

-- merge changes to dim customer
BEGIN TRANSACTION;
-- update current records
UPDATE customer_dim
SET c_first_name = src.c_first_name , c_last_name = src.c_last_name , c_current_addr_sk = src.c_current_addr_sk , c_birth_country = src.c_birth_country , c_email_address = src.c_email_address , record_upd_ts = current_timestamp
from src_customer AS src
where customer_dim.c_customer_sk = src.c_customer_sk ;
-- Insert new records
INSERT INTO customer_dim (c_customer_sk, c_first_name,c_last_name, c_current_addr_sk, c_birth_country, c_email_address) select src.c_customer_sk, src.c_first_name,src.c_last_name, src.c_current_addr_sk, src.c_birth_country, src.c_email_address from src_customer AS src
where src.c_customer_sk NOT IN (select c_customer_sk from customer_dim);
-- end merge operation
COMMIT TRANSACTION;

MERGE komutu, DML güncelleme işlemini mi (eşleştiğinde) yoksa DML ekleme işlemini mi (eşleşmediğinde) gerçekleştireceğimize karar vermek için anahtar karşılaştırma sonucunu kullandığımız daha basit bir sözdizimi kullanır:

MERGE INTO customer_dim using src_customer AS src ON customer_dim.c_customer_sk = src.c_customer_sk
WHEN MATCHED THEN UPDATE SET c_first_name = src.c_first_name , c_last_name = src.c_last_name , c_current_addr_sk = src.c_current_addr_sk , c_birth_country = src.c_birth_country , c_email_address = src.c_email_address , record_upd_ts = current_timestamp
WHEN NOT MATCHED THEN INSERT (c_customer_sk, c_first_name,c_last_name, c_current_addr_sk, c_birth_country, c_email_address) VALUES (src.c_customer_sk, src.c_first_name,src.c_last_name, src.c_current_addr_sk, src.c_birth_country, src.c_email_address );

Hedef tablodaki veri değişikliklerini doğrulama

Şimdi verilerin hedef tabloya doğru şekilde ulaştığını doğrulamamız gerekiyor. Öncelikle güncelleme zaman damgasını kullanarak güncellenen verileri kontrol edebiliriz. Bu bizim ilk güncellememiz olduğundan, güncelleme zaman damgasının boş olmadığı tüm satırları inceleyebiliriz:

-- Check the changes
-- to get updates
select * from customer_dim
where record_upd_ts is not null

Veri değişikliklerinin doğrulanmasını basitleştirmek için QUALIFY'ı kullanın

Bu tabloya en son eklenen verileri incelememiz gerekiyor. Bunu yapmanın bir yolu, verileri ekleme zaman damgasına göre sıralamak ve ilk sıraya sahip olanları almaktır. Bu, pencere işlevinin kullanılmasını gerektirir rank() ve ayrıca sonuçları almak için bir alt sorgu gerektirir.

QUALIFY kullanıma sunulmadan önce bunu aşağıdaki gibi bir alt sorgu kullanarak oluşturmamız gerekiyordu:

select customer_dim_id,c_customer_sk ,c_first_name ,c_last_name ,c_current_addr_sk,c_birth_country ,c_email_address ,record_insert_ts ,record_upd_ts from ( select rank() OVER (ORDER BY DATE_TRUNC(‘second’,record_insert_ts) desc) AS rnk, customer_dim_id,c_customer_sk ,c_first_name ,c_last_name ,c_current_addr_sk,c_birth_country ,c_email_address ,record_insert_ts ,record_upd_ts from customer_dim where record_upd_ts is null)
where rnk = 1;

QUALIFY işlevi, aşağıdaki kod parçacığında olduğu gibi alt sorguya olan ihtiyacı ortadan kaldırır:

-- to get the newly inserted rows we can make use of Qualify feature
select * from customer_dim
where record_upd_ts is null
qualify rank() OVER (ORDER BY DATE_TRUNC(‘second’,record_insert_ts) desc) = 1

Tüm veri değişikliklerini doğrula

Tüm eklemeleri ve güncelleme değişikliklerini almak için her iki sorgunun sonuçlarını birleştirebiliriz:

-- To get all changes
select *
from (
select 'Updates' as operations, cd.* from customer_dim as cd
where cd.record_upd_ts is not null
union select 'Inserts' as operations, cd.* from customer_dim cd
where cd.record_upd_ts is null
qualify rank() OVER (ORDER BY DATE_TRUNC('second',cd.record_insert_ts) desc) = 1 ) order by 1

Temizlemek

Gönderide kullanılan kaynakları temizlemek için Redshift tarafından sağlanan kümeyi veya Redshift Sunucusuz çalışma grubunu ve bu gönderi için oluşturduğunuz ad alanını silin (bu aynı zamanda oluşturulan tüm nesneleri de bırakır).

Mevcut bir Redshift tarafından sağlanan kümeyi veya Redshift Sunucusuz çalışma grubunu ve ad alanını kullandıysanız, bu nesneleri bırakmak için aşağıdaki kodu kullanın:

DROP TABLE IF EXISTS customer_dim CASCADE;
DROP TABLE IF EXISTS src_customer CASCADE;

Sonuç

Verileri güncellemek veya eklemek için birden fazla ifade kullanıldığında, farklı işlemler arasında tutarsızlık riski vardır. BİRLEŞTİRME operasyonu tüm işlemlerin tek bir işlemde birlikte yapılmasını sağlayarak bu riski azaltır. Diğer veri ambarı sistemlerinden geçiş yapan veya Redshift ambarlarına düzenli olarak hızla değişen verileri alması gereken Amazon Redshift müşterileri için MERGE komutu, mevcut ve mevcut tablolara dayalı olarak hedef tablolara koşullu olarak veri eklemenin, güncellemenin ve silmenin basit bir yoludur. yeni kaynak verileri.

Pencere işlevlerini kullanan analitik sorguların çoğunda, bu pencere işlevlerini WHERE yan tümcenizde de kullanmanız gerekebilir. Ancak buna izin verilmez ve bunu yapmak için gerekli pencere işlevini içeren bir alt sorgu oluşturmanız ve ardından sonuçları WHERE yan tümcesindeki üst sorguda kullanmanız gerekir. QUALIFY deyiminin kullanılması alt sorgu ihtiyacını ortadan kaldırır ve dolayısıyla SQL ifadesini basitleştirir ve yazmayı ve okumayı daha az zorlaştırır.

Bu yeni özellikleri kullanmaya başlamanızı ve bize geri bildirimde bulunmanızı öneririz. Daha fazla ayrıntı için bkz. BİRLEŞTİRMEK ve QUALIFY cümlesi.

yazarlar hakkında

Yanzhu Ji Amazon Redshift ekibinde Ürün Yöneticisidir. Sektör lideri veri ürünleri ve platformlarında ürün vizyonu ve stratejisi konusunda deneyime sahiptir. Web geliştirme, sistem tasarımı, veri tabanı ve dağıtılmış programlama teknikleri kullanarak önemli yazılım ürünleri oluşturma konusunda olağanüstü bir beceriye sahiptir. Yanzhu, kişisel hayatında resim yapmayı, fotoğraf çekmeyi ve tenis oynamayı seviyor.

Ahmet Şehat Toronto merkezli AWS'de Kıdemli Analitik Uzmanı Çözüm Mimarıdır. Müşterilerin veri platformlarını modernize etmelerine yardımcı olma konusunda yirmi yılı aşkın bir deneyime sahiptir. Ahmed, müşterilerin verimli, performanslı ve ölçeklenebilir analitik çözümler oluşturmasına yardımcı olma konusunda tutkulu.

Ranjan Burman AWS'de Analitik Uzmanı Çözüm Mimarıdır. Amazon Redshift'te uzmandır ve müşterilerin ölçeklenebilir analitik çözümler oluşturmasına yardımcı olur. Farklı veritabanı ve veri ambarı teknolojilerinde 16 yıldan fazla deneyime sahiptir. Müşteri sorunlarını bulut çözümleriyle otomatikleştirme ve çözme konusunda tutkulu.

spot_img

En Son İstihbarat

spot_img

Telif Hakkı @ 2024 Plato Technologies Inc.