Amazon Redshift'e Veri Ambarı Geçişinizi Hızlandırın

Plato tarafından yeniden yayınlandı

İzleyiciler: 0

Bu çok bölümlü bir serinin üçüncü yazısı. Şema dönüşümünüzü otomatikleştirecek düzinelerce yeni özelliği paylaşmanın heyecanını yaşıyoruz; mevcut komut dosyalarına, raporlara ve uygulamalara yaptığınız yatırımı koruyun; sorgu performansını hızlandırın; ve genel geçiş maliyetinizi azaltın Amazon Kırmızıya Kaydırma.

Amazon Redshift, lider bulut veri ambarıdır. Başka hiçbir veri ambarı, verilerinizden yeni içgörüler elde etmeyi bu kadar kolay hale getiremez. Amazon Redshift ile standart SQL kullanarak veri ambarınız, operasyonel veri depolarınız ve veri gölünüz genelinde eksabaytlarca veriyi sorgulayabilirsiniz. Ayrıca aşağıdaki gibi diğer hizmetleri de entegre edebilirsiniz. Amazon EMR'si, Amazon Atina, ve Amazon Adaçayı Yapıcı AWS Cloud'daki tüm analitik yetenekleri kullanmak için.

Birçok müşteri, Teradata gibi kendi kendini yöneten veri ambarı motorlarından Amazon Redshift'e geçiş konusunda yardım istedi. Bu durumlarda, terabaytlarca (veya petabaytlarca) geçmiş veriye, özel özelliklere yoğun bir bağımlılığa ve yıllar (veya on yıllar) kullanım sonucunda oluşturulan binlerce ayıklama, dönüştürme ve yükleme (ETL) işlemine ve rapora sahip olabilirsiniz.

Şimdiye kadar Teradata veri ambarını AWS'ye taşımak karmaşıktı ve önemli miktarda manuel çaba gerektiriyordu.

Bugün Amazon Redshift'te yapılan son geliştirmeleri ve AWS Şema Dönüştürme Aracı (AWS SCT), Teradata'dan Amazon Redshift'e geçişlerinizi otomatikleştirmenizi kolaylaştırır.

Bu yazıda, birleştirme ifadeleri için yeni otomasyon, ASCII karakter dönüştürmeyi destekleyen yerel bir işlev, dizeden tarihe dönüştürme için gelişmiş hata denetimi, Teradata imleçleri ve kimlik sütunları için gelişmiş destek, HERHANGİ BİR ve BAZI yüklemler için otomasyon, RESET WHEN otomasyonu tanıtıyoruz. cümlecikleri, iki özel Teradata işlevi için otomasyon (TD_NORMALIZE_OVERLAP ve TD_UNPIVOT) ve analitik işlevleri desteklemek için otomasyon (QUANTILE ve QUALIFY).

Bildirimi birleştir

Adından da anlaşılacağı gibi, merge ifadesi bir girdi kümesi alır ve onu bir hedef tabloyla birleştirir. Hedef tabloda bir giriş satırı zaten mevcutsa (hedef tablodaki bir satır aynı birincil anahtar değerine sahipse), hedef satır güncellenir. Eşleşen hedef satır yoksa giriş satırı tabloya eklenir.

Şimdiye kadar iş yükünizde birleştirme ifadeleri kullandıysanız Amazon Redshift'te çalışacak şekilde birleştirme ifadesini manuel olarak yeniden yazmak zorunda kalıyordunuz. Artık AWS SCT'nin bu dönüşümü sizin için otomatikleştirdiğini paylaşmaktan mutluluk duyuyoruz. AWS SCT, birleştirme ifadesini mevcut kayıtlardaki bir güncellemeye ve ardından yeni kayıtlar için bir eklemeye dönüştürür.

Bir örneğe bakalım. Teradata'da iki tablo oluşturuyoruz: hedef tablo, employeeve bir delta tablosu, employee_delta, giriş satırlarını yerleştirdiğimiz yer:

CREATE TABLE testschema.employee( id INTEGER
, name VARCHAR(20)
, manager INTEGER)
UNIQUE PRIMARY INDEX (id)
; CREATE TABLE testschema.employee_delta ( id INTEGER
, name VARCHAR(20)
, manager INTEGER)
UNIQUE PRIMARY INDEX(id)
;

Şimdi, hedefte bir satır varsa güncelleyen, aksi takdirde yeni satırı ekleyen bir Teradata birleştirme ifadesi oluşturuyoruz. Bu birleştirme ifadesini bir makroya yerleştirdik, böylece size daha sonra dönüşüm sürecini gösterebiliriz.

REPLACE MACRO testschema.merge_employees AS ( MERGE INTO testschema.employee tgt USING testschema.employee_delta delta ON delta.id = tgt.id WHEN MATCHED THEN UPDATE SET name = delta.name, manager = delta.manager WHEN NOT MATCHED THEN INSERT (delta.id, delta.name, delta.manager);
);

Artık makroyu dönüştürmek için AWS SCT'yi kullanıyoruz. (Görmek Amazon Redshift'e veri ambarı geçişinizi hızlandırın - Bölüm 1 AWS SCT, bir güncelleme (WHEN MATCHED koşulunu uygulamak için) ve bir ekleme (WHEN NOT MATCHED koşulunu uygulamak için) içeren bir saklı prosedür oluşturur.

CREATE OR REPLACE PROCEDURE testschema.merge_employees()
AS $BODY$
BEGIN UPDATE testschema.employee SET name = "delta".name, manager = "delta".manager FROM testschema.employee_delta AS delta JOIN testschema.employee AS tgt ON "delta".id = tgt.id; INSERT INTO testschema.employee SELECT "delta".id , "delta".name , "delta".manager FROM testschema.employee_delta AS delta WHERE NOT EXISTS ( SELECT 1 FROM testschema.employee AS tgt WHERE "delta".id = tgt.id );
END;
$BODY$
LANGUAGE plpgsql;

Bu örnek, makrolar için birleştirme otomasyonunun nasıl kullanılacağını gösterdi ancak birleştirme ifadelerini herhangi bir uygulama bağlamında dönüştürebilirsiniz: saklı yordamlar, BTEQ komut dosyaları, Java kodu ve daha fazlası. İndir en son versiyon AWS SCT'yi deneyin ve deneyin.

ASCII() işlevi

ASCII işlevi girdi olarak bir dize alır ve dizedeki ilk karakterin ASCII kodunu veya daha doğrusu UNICODE kod noktasını döndürür. Daha önce Amazon Redshift, ASCII'yi yalnızca lider düğüm işlevi olarak destekliyordu ve bu durum, kullanıcı tanımlı tablolarla kullanımını engelliyordu.

ASCII işlevinin artık Amazon Redshift işlem düğümlerinde mevcut olduğunu ve kullanıcı tanımlı tablolarla kullanılabileceğini paylaşmaktan mutluluk duyuyoruz. Aşağıdaki kodda bazı dize verilerini içeren bir tablo oluşturuyoruz:

CREATE TABLE testschema.char_table ( id INTEGER
, char_col CHAR(10)
, varchar_col VARCHAR(10)
); INSERT INTO testschema.char_table VALUES (1, 'Hello', 'world');

Artık dize sütunlarında ASCII işlevini kullanabilirsiniz:

# SELECT id, char_col, ascii(char_col), varchar_col, ascii(varchar_col) FROM testschema.char_table; id | char_col | ascii | varchar_col | ascii 1 | Hello | 72 | world | 119

Son olarak, uygulama kodunuz ASCII işlevini kullanıyorsa AWS SCT, bu tür işlev çağrılarını otomatik olarak Amazon Redshift'e dönüştürür.

The ASCII özelliği şu anda mevcut; kendi kümenizde deneyin.

TO_DATE() işlevi

TO_DATE işlevi bir karakter dizesini DATE değerine dönüştürür. Bu işlevin tuhaflığı, geçerli bir tarih olmayan bir dize değerini kabul edebilmesi ve bunu geçerli bir tarihe çevirebilmesidir.

Örneğin, dizeyi düşünün 2021-06-31. Bu geçerli bir tarih değil çünkü Haziran ayında yalnızca 30 gün var. Ancak TO_DATE işlevi bu dizeyi kabul eder ve Haziran ayının “31.” gününü (1 Temmuz) döndürür:

# SELECT to_date('2021-06-31', 'YYYY-MM-DD'); to_date 2021-07-01
(1 row)

Müşteriler TO_DATE için giriş denetiminin sıkı bir şekilde yapılmasını talep etti ve biz de bu yeni özelliği paylaşmaktan mutluluk duyuyoruz. Artık, sıkı denetimi etkinleştiren işlev çağrısına bir Boolean değeri ekleyebilirsiniz:

# SELECT to_date('2021-06-31', 'YYYY-MM-DD', TRUE);
ERROR: date/time field date value out of range: 2021-6-31

Kesin kontrolü açık bir şekilde de kapatabilirsiniz:

# SELECT to_date('2021-06-31', 'YYYY-MM-DD', FALSE); to_date 2021-07-01
(1 row)

Ayrıca Boole değeri isteğe bağlıdır. Eklemezseniz sıkı denetim kapatılır ve özellik başlatılmadan önceki davranışın aynısını görürsünüz.

Sen hakkında daha fazla bilgi edinebilirsiniz BUGÜNE KADAR işlevini kullanın ve şimdi Amazon Redshift'te sıkı tarih kontrolünü deneyin.

CURSOR sonuç kümeleri

İmleç, uygulamaların bir sonuç kümesini her seferinde bir satır olarak işlemek için kullandığı bir programlama dili yapısıdır. İmleçler OLTP uygulamalarıyla daha alakalıdır ancak veri ambarları üzerine kurulu bazı eski uygulamalar da bunları kullanır.

Teradata çok çeşitli imleç konfigürasyonları sağlar. Amazon Redshift daha akıcı bir imleç özellikleri kümesini destekler.

Müşteri geri bildirimlerine dayanarak, Teradata İLE DÖNÜŞ imleçlerini desteklemek için otomasyon ekledik. Bu tür imleçler saklı yordamlar içinde açılır ve sonuç kümesinin işlenmesi için çağırana geri gönderilir. AWS SCT, YOUR RETURN imlecini Amazon Redshift'e dönüştürecek REFÜRÖR.

Örneğin, bir İLE DÖNÜŞ imleci içeren aşağıdaki yordamı göz önünde bulundurun. Prosedür imleci açar ve sonucu arayan kişiye DİNAMİK SONUÇ AYARI olarak döndürür:

REPLACE PROCEDURE testschema.employee_cursor (IN p_mgrid INTEGER) DYNAMIC RESULT SETS 1
BEGIN DECLARE result_set CURSOR WITH RETURN ONLY FOR SELECT id, name, manager FROM testschema.employee WHERE manager = to_char(p_mgrid); OPEN result_set;
END;

AWS SCT, prosedürü aşağıdaki gibi dönüştürür. REFCURSOR'u iletmek için prosedür imzasına ek bir parametre eklenir:

CREATE OR REPLACE PROCEDURE testschema.employee_cursor(par_p_mgrid IN INTEGER, dynamic_return_cursor INOUT refcursor)
AS $BODY$
DECLARE
BEGIN OPEN dynamic_return_cursor FOR SELECT id, name, manager FROM testschema.employee WHERE manager = to_char(par_p_mgrid, '99999');
END;
$BODY$
LANGUAGE plpgsql;

KİMLİK sütunları

Teradata, IDENTITY sütunları için ANSI uyumlu olmayan birçok özelliği destekler. AWS SCT'yi mümkün olduğunda bu özellikleri otomatik olarak Amazon Redshift'e dönüştürecek şekilde geliştirdik.

Özellikle, AWS SCT artık Teradata START With ve INCREMENT BY cümlelerini şuna dönüştürüyor: Amazon Redshift SEED ve STEP maddeleri, sırasıyla. Örneğin aşağıdaki Teradata tablosunu göz önünde bulundurun:

CREATE TABLE testschema.identity_table ( a2 BIGINT GENERATED ALWAYS AS IDENTITY ( START WITH 1 INCREMENT BY 20 )
);

GENERATED ALWAYS yan tümcesi, sütunun her zaman otomatik olarak doldurulduğunu belirtir; bir değer sütuna açıkça eklenemez veya güncellenemez. START With yan tümcesi, sütuna eklenecek ilk değeri tanımlar ve INCREMENT BY yan tümcesi, sütuna eklenecek bir sonraki değeri tanımlar.

Bu tabloyu AWS SCT kullanarak dönüştürdüğünüzde aşağıdaki Amazon Redshift DDL oluşturulur. START With ve INCREMENT BY değerlerinin hedef sözdiziminde korunduğuna dikkat edin:

CREATE TABLE IF NOT EXISTS testschema.identity_table ( a2 BIGINT IDENTITY(1, 20)
)
DISTSTYLE KEY
DISTKEY
(a2)
SORTKEY
(a2);

Ayrıca Amazon Redshift'teki bir IDENTITY sütunu varsayılan olarak yalnızca otomatik olarak oluşturulan değerleri içerir, böylece Teradata'daki GENERATED ALWAYS özelliği korunur:

# INSERT INTO testschema.identity_table VALUES (100);
ERROR: cannot set an identity column to a value

Teradata'daki IDENTITY sütunları, VARSAYILAN TARAFINDAN OLUŞTURULDU olarak da belirtilebilir. Bu durumda bir değer INSERT deyiminde açıkça tanımlanabilir. Hiçbir değer belirtilmezse sütun normal gibi otomatik olarak oluşturulan bir değerle doldurulur. Daha önce AWS SCT, GENERATED BY DEFAULT sütunları için dönüştürmeyi desteklemiyordu. Artık AWS SCT'nin bu tür sütunları sizin için otomatik olarak dönüştürdüğünü paylaşmaktan mutluluk duyuyoruz.

Örneğin, aşağıdaki tabloda VARSAYILAN TARAFINDAN OLUŞTURULAN bir KİMLİK sütunu bulunmaktadır:

CREATE TABLE testschema.identity_by_default ( a1 BIGINT GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 INCREMENT BY 20 )
PRIMARY INDEX (a1);

IDENTITY sütunu AWS SCT tarafından aşağıdaki şekilde dönüştürülür. Dönüştürülen sütun, Amazon Redshift GENERATED BY DEFAULT yan tümcesini kullanır:

CREATE TABLE testschema.identity_by_default ( a1 BIGINT GENERATED BY DEFAULT AS IDENTITY(1,20) DISTKEY
) DISTSTYLE KEY SORTKEY (a1);

Dikkat edilmesi gereken ek bir sözdizimi sorunu daha var. Teradata'da, sütun değeri için NULL belirtildiğinde otomatik olarak oluşturulan bir değer eklenir:

INSERT INTO identity_by_default VALUES (null);

Amazon Redshift aynı amaç için farklı bir sözdizimi kullanır. Burada, sütunun otomatik olarak oluşturulması gerektiğini belirtmek için DEFAULT anahtar kelimesini değerler listesine eklersiniz:

INSERT INTO testschema.identity_by_default VALUES (default);

AWS SCT'nin önceki örnekte olduğu gibi INSERT ifadeleri için Teradata sözdizimini otomatik olarak dönüştürdüğünü paylaşmaktan mutluluk duyuyoruz. Örneğin aşağıdaki Teradata makrosunu göz önünde bulundurun:

REPLACE MACRO testschema.insert_identity_by_default AS ( INSERT INTO testschema.identity_by_default VALUES (NULL);
);

AWS SCT, NULL'u kaldırır ve onu DEFAULT ile değiştirir:

CREATE OR REPLACE PROCEDURE testschema.insert_identity_by_default() LANGUAGE plpgsql
AS $$ BEGIN INSERT INTO testschema.identity_by_default VALUES (DEFAULT);
END; $$

IDENTITY sütunu otomasyonu artık AWS SCT'de mevcut. İndirebilirsin en son versiyon ve dene.

Eşitsizlik tahminlerini içeren HERHANGİ BİR ve BAZI filtreler

ANY ve SOME filtreleri, bir yüklemin listedeki bir veya daha fazla değere uygulanıp uygulanamayacağını belirler. Örneğin Teradata'da, belirli bir yönetici için çalışmayan tüm çalışanları bulmak için <> HERHANGİ BİRİNİ kullanabilirsiniz:

REPLACE MACRO testschema.not_in_103 AS ( SELECT * FROM testschema.employee WHERE manager <> ANY (103)
;
);

Elbette bu sorguyu basit, eşit olmayan bir filtre kullanarak yeniden yazabilirsiniz, ancak sıklıkla üçüncü taraf SQL oluşturucularından gelen ve bu modeli izleyen sorguları görürsünüz.

Amazon Redshift bu sözdizimini yerel olarak desteklemez. Daha önce bu sözdizimini kullanan tüm sorguların manuel olarak dönüştürülmesi gerekiyordu. Artık AWS SCT'nin HERHANGİ BİR ve BAZI cümleleri eşitsizlik yüklemleriyle otomatik olarak dönüştürdüğünü paylaşmaktan mutluluk duyuyoruz. Yukarıdaki makro aşağıdaki gibi saklı prosedüre dönüştürülür.

CREATE OR REPLACE PROCEDURE testschema.not_in_103(macro_out INOUT refcursor)
AS $BODY$
BEGIN OPEN macro_out FOR SELECT * FROM testschema.employee WHERE ((manager <> 103));
END;
$BODY$
LANGUAGE plpgsql;

ANY'den sonraki değerler listesinde iki değer daha varsa AWS SCT, bunu listedeki her öğe için bir tane olmak üzere bir dizi VEYA koşuluna dönüştürür.

HERHANGİ BİR/BAZI filtre dönüşümü artık AWS SCT'de mevcut. Bunu şurada deneyebilirsiniz en son versiyon uygulama.

RESET WHEN ile analitik işlevler

RESET WHEN, SQL analitik pencere işlevlerinde kullanılan bir Teradata özelliğidir. ANSI SQL standardının bir uzantısıdır. RESET WHEN, belirtilen bir koşula göre bir SQL pencere fonksiyonunun üzerinde çalışacağı bölümü belirler. Koşul doğru olarak değerlendirilirse mevcut pencere bölümünün içinde yeni bir dinamik alt bölüm oluşturulur.

Örneğin, aşağıdaki görünüm mağazaya göre değişen toplamı hesaplamak için RESET WHEN'i kullanır. Satışlar aydan aya arttığı sürece cari toplam birikir. Satışlar bir aydan diğerine düşerse cari toplam sıfırlanır.

CREATE TABLE testschema.sales ( store_id INTEGER
, month_no INTEGER
, sales_amount DECIMAL(9,2)
)
; REPLACE VIEW testschema.running_total ( store_id
, month_no
, sales_amount
, cume_sales_amount
)
AS
SELECT store_id
, month_no
, sales_amount
, SUM(sales_amount) OVER ( PARTITION BY store_id ORDER BY month_no RESET WHEN sales_amount < SUM(sales_amount) OVER ( PARTITION BY store_id ORDER BY month_no ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ) ROWS UNBOUNDED PRECEDING )
FROM testschema.sales;

Göstermek için tabloya bazı test verileri ekliyoruz:

INSERT INTO testschema.sales VALUES (1001, 1, 35000.00);
INSERT INTO testschema.sales VALUES (1001, 2, 40000.00);
INSERT INTO testschema.sales VALUES (1001, 3, 45000.00);
INSERT INTO testschema.sales VALUES (1001, 4, 25000.00);
INSERT INTO testschema.sales VALUES (1001, 5, 30000.00);
INSERT INTO testschema.sales VALUES (1001, 6, 30000.00);
INSERT INTO testschema.sales VALUES (1001, 7, 50000.00);
INSERT INTO testschema.sales VALUES (1001, 8, 35000.00);
INSERT INTO testschema.sales VALUES (1001, 9, 60000.00);
INSERT INTO testschema.sales VALUES (1001, 10, 80000.00);
INSERT INTO testschema.sales VALUES (1001, 11, 90000.00);
INSERT INTO testschema.sales VALUES (1001, 12, 100000.00);

Satış tutarları 3. ve 7. aylardan sonra düşer. Geçerli toplam 4. ve 8. aylarda buna göre sıfırlanır.

SELECT * FROM testschema.running_total; store_id month_no sales_amount cume_sales_amount
----------- ----------- ------------ ----------------- 1001 1 35000.00 35000.00 1001 2 40000.00 75000.00 1001 3 45000.00 120000.00 1001 4 25000.00 25000.00 1001 5 30000.00 55000.00 1001 6 30000.00 85000.00 1001 7 50000.00 135000.00 1001 8 35000.00 35000.00 1001 9 60000.00 95000.00 1001 10 80000.00 175000.00 1001 11 90000.00 265000.00 1001 12 100000.00 365000.00

AWS SCT, görünümü aşağıdaki gibi dönüştürür. Dönüştürülen kod, RESET WHEN'i taklit etmek için bir alt sorgu kullanır. Temel olarak sonuca, aydan aya satış düşüşünü işaretleyen bir işaretleyici özelliği eklenir. Bayrak daha sonra toplam artan satışların en uzun önceki dönemini belirlemek için kullanılır.

CREATE OR REPLACE VIEW testschema.running_total ( store_id
, month_no
, sales_amount
, cume_sales_amount) AS
SELECT store_id
, month_no
, sales_amount
, sum(sales_amount) OVER (PARTITION BY k1, store_id ORDER BY month_no NULLS FIRST ROWS UNBOUNDED PRECEDING)
FROM ( SELECT store_id , month_no , sales_amount , SUM(CASE WHEN k = 1 THEN 0 ELSE 1 END) OVER (PARTITION BY store_id ORDER BY month_no NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS k1 FROM ( SELECT store_id , month_no , sales_amount , CASE WHEN sales_amount < SUM(sales_amount) OVER (PARTITION BY store_id ORDER BY month_no ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) OR sales_amount IS NULL THEN 0 ELSE 1 END AS k FROM testschema.sales )
);

RESET WHEN dönüşümünün müşteriler arasında büyük bir etki yaratmasını bekliyoruz. Şimdi deneyebilirsiniz AWS ÖTV.

TD_NORMALIZE_OVERLAP() işlevi

TD_NORMALIZE_OVERLAP işlevi, çakışan PERIOD değerlerine sahip satırları birleştirir. Ortaya çıkan normalleştirilmiş satır, ilgili tüm satırların PERIOD değerlerinden en erken başlangıç sınırını ve en son bitiş sınırını içerir.

Örnek olarak aşağıdaki kod ile çalışanların maaşlarını kaydeden Teradata tablosu oluşturuyoruz. Tablodaki her satır, çalışana verilen maaşın ödendiği dönemi gösteren zaman damgasına sahiptir.

CREATE TABLE testschema.salaries ( emp_id INTEGER
, salary DECIMAL(8,2)
, from_to PERIOD(DATE)
);

Şimdi iki çalışanın verilerini ekliyoruz. İçin emp_id = 1 ve salary = 2000, örtüşen iki satır var. Benzer şekilde, iki satır emp_id = 2 ve salary = 3000 örtüşmektedir.

SELECT * FROM testschema.salaries ORDER BY emp_id, from_to; emp_id salary from_to
----------- ---------- ------------------------ 1 1000.00 ('20/01/01', '20/05/31') 1 2000.00 ('20/06/01', '21/02/28') 1 2000.00 ('21/01/01', '21/06/30') 2 3000.00 ('20/01/01', '20/03/31') 2 3000.00 ('20/02/01', '20/04/30')

Şimdi çakışan verileri normalleştirmek için TD_NORMALIZE_OVERLAP işlevini kullanan bir görünüm oluşturuyoruz:

REPLACE VIEW testschema.normalize_salaries AS WITH sub_table(emp_id, salary, from_to) AS ( SELECT emp_id , salary , from_to FROM testschema.salaries
)
SELECT *
FROM TABLE(TD_SYSFNLIB.TD_NORMALIZE_OVERLAP (NEW VARIANT_TYPE(sub_table.emp_id, sub_table.salary), sub_table.from_to) RETURNS (emp_id INTEGER, salary DECIMAL(8,2), from_to PERIOD(DATE)) HASH BY emp_id LOCAL ORDER BY emp_id, salary, from_to ) AS DT(emp_id, salary, duration)
;

Görünüm verilerinin gerçekten normalleştirilip normalleştirilmediğini kontrol edebiliriz:

select * from testschema.normalize_salaries order by emp_id, duration; emp_id salary duration
----------- ---------- ------------------------ 1 1000.00 ('20/01/01', '20/05/31') 1 2000.00 ('20/06/01', '21/06/30') 2 3000.00 ('20/01/01', '20/04/30')

Artık herhangi bir TD_NORMALIZE_OVERLAP ifadesini dönüştürmek için AWS SCT'yi kullanabilirsiniz. Öncelikle maaş tablosunu Amazon Redshift'e dönüştürüyoruz (bkz. Amazon Redshift'e veri ambarı geçişinizi hızlandırın - Bölüm 2 dönem veri türü otomasyonuyla ilgili ayrıntılar için):

CREATE TABLE testschema.salaries ( emp_id integer distkey
, salary numeric(8,2) ENCODE az64
, from_to_begin date ENCODE az64
, from_to_end date ENCODE az64 ) DISTSTYLE KEY SORTKEY (emp_id); # SELECT * FROM testschema.salaries ORDER BY emp_id, from_to_begin; emp_id | salary | from_to_begin | from_to_end 1 | 1000.00 | 2020-01-01 | 2020-05-31 1 | 2000.00 | 2020-06-01 | 2021-02-28 1 | 2000.00 | 2021-01-01 | 2021-06-30 2 | 3000.00 | 2020-01-01 | 2020-03-31 2 | 3000.00 | 2020-02-01 | 2020-04-30

Artık normalize_salaries görünümünü dönüştürmek için AWS SCT'yi kullanıyoruz. AWS SCT, yeni bir satır grubunun başlangıcını işaretleyen bir sütun ekler. Daha sonra her grup için normalleştirilmiş zaman damgasına sahip tek bir satır üretir.

CREATE VIEW testschema.normalize_salaries (emp_id, salary, from_to_begin, from_to_end) AS
WITH sub_table AS ( SELECT emp_id , salary , from_to_begin AS start_date , from_to_end AS end_date , CASE WHEN start_date <= lag(end_date) OVER (PARTITION BY emp_id, salary ORDER BY start_date, end_date) THEN 0 ELSE 1 END AS GroupStartFlag FROM testschema.salaries )
SELECT t2.emp_id
, t2.salary
, min(t2.start_date) AS from_to_begin
, max(t2.end_date) AS from_to_end
FROM ( SELECT emp_id , salary , start_date , end_date , sum(GroupStartFlag) OVER (PARTITION BY emp_id, salary ORDER BY start_date ROWS UNBOUNDED PRECEDING) AS GroupID FROM sub_table
) AS t2
GROUP BY t2.emp_id
, t2.salary
, t2.GroupID;

Dönüştürülen görünümün doğru şekilde normalleştirilmiş verileri döndürdüğünü kontrol edebiliriz:

# SELECT * FROM testschema.normalize_salaries ORDER BY emp_id; emp_id | salary | from_to_begin | from_to_end 1 | 1000.00 | 2020-01-01 | 2020-05-31 1 | 2000.00 | 2020-06-01 | 2021-06-30 2 | 3000.00 | 2020-01-01 | 2020-04-30

AWS SCT'nin en son sürümünde TD_NORMALIZE_OVERLAP dönüşümünü deneyebilirsiniz. İndir şimdi.

TD_UNPIVOT() işlevi

TD_UNPIVOT işlevi sütunları satırlara dönüştürür. Temel olarak, bunu farklı zaman dilimlerindeki benzer metriklerin bir satırını almak ve her metrik için ayrı bir satır oluşturmak için kullanırız.

Örneğin aşağıdaki Teradata tablosunu göz önünde bulundurun. Tablo, küçük kiosk mağazalarına yönelik müşteri ziyaretlerini yıl ve aya göre kaydeder:

CREATE TABLE TESTSCHEMA.kiosk_monthly_visits ( kiosk_id INTEGER
, year_no INTEGER
, jan_visits INTEGER
, feb_visits INTEGER
, mar_visits INTEGER
, apr_visits INTEGER
, may_visits INTEGER
, jun_visits INTEGER
, jul_visits INTEGER
, aug_visits INTEGER
, sep_visits INTEGER
, oct_visits INTEGER
, nov_visits INTEGER
, dec_visits INTEGER)
PRIMARY INDEX (kiosk_id);

Tabloya bazı örnek veriler ekliyoruz:

INSERT INTO testschema.kiosk_monthly_visits VALUES (100, 2020, 1100, 1200, 1300, 1400, 1500, 1600, 1700, 1800, 1900, 2000, 2100, 2200);

Daha sonra, aylık ziyaretlerin ayrı satırlarda görünmesi için tablonun özetini kaldıran bir görünüm oluşturuyoruz. Pivotlu tablodaki tek satır, pivotsuz tabloda ayda bir satır olmak üzere 12 satır oluşturur.

REPLACE VIEW testschema.unpivot_kiosk_monthly_visits ( kiosk_id
, year_no
, month_name
, month_visits
)
AS
SELECT kiosk_id
, year_no
, month_name (FORMAT 'X(10)')
, month_visits
FROM TD_UNPIVOT ( ON (SELECT * FROM testschema.kiosk_monthly_visits) USING VALUE_COLUMNS ('month_visits') UNPIVOT_COLUMN('month_name') COLUMN_LIST( 'jan_visits' , 'feb_visits' , 'mar_visits' , 'apr_visits' , 'may_visits' , 'jun_visits' , 'jul_visits' , 'aug_visits' , 'sep_visits' , 'oct_visits' , 'nov_visits' , 'dec_visits' ) COLUMN_ALIAS_LIST ( 'jan' , 'feb' , 'mar' , 'apr' , 'may' , 'jun' , 'jul' , 'aug' , 'sep' , 'oct' , 'nov' , 'dec' )
) a;

Görünümden seçim yaptığınızda aylık satışlar 12 ayrı satıra ayrılır:

SELECT * FROM testschema.unpivot_monthly_sales; id yr mon mon_sales
----------- ----------- ---------- ----------
100 2021 jan 1100.00
100 2021 feb 1200.00
100 2021 mar 1300.00
100 2021 apr 1400.00
100 2021 may 1500.00
100 2021 jun 1600.00
100 2021 jul 1700.00
100 2021 aug 1800.00
100 2021 sep 1900.00
100 2021 oct 2000.00
100 2021 nov 2100.00
100 2021 dec 2200.00

Artık görünümü Amazon Redshift'te çalıştırılabilecek ANSI SQL'e dönüştürmek için AWS SCT'yi kullanıyoruz. Dönüşüm, her ayı ayrı bir satıra yerleştirmek için ortak bir tablo ifadesi (CTE) oluşturur. Daha sonra CTE'yi ve orijinal pivot tablonun geri kalan niteliklerini birleştirir.

REPLACE VIEW testschema.unpivot_kiosk_monthly_visits (kiosk_id, year_no, month_name, month_visits) AS
WITH cols
AS (SELECT 'jan' AS col
UNION ALL
SELECT 'feb' AS col
UNION ALL
SELECT 'mar' AS col
UNION ALL
SELECT 'apr' AS col
UNION ALL
SELECT 'may' AS col
UNION ALL
SELECT 'jun' AS col
UNION ALL
SELECT 'jul' AS col
UNION ALL
SELECT 'aug' AS col
UNION ALL
SELECT 'sep' AS col
UNION ALL
SELECT 'oct' AS col
UNION ALL
SELECT 'nov' AS col
UNION ALL
SELECT 'dec' AS col)
SELECT t1.kiosk_id, t1.year_no, col AS "month_name", CASE col WHEN 'jan' THEN "jan_visits" WHEN 'feb' THEN "feb_visits" WHEN 'mar' THEN "mar_visits" WHEN 'apr' THEN "apr_visits" WHEN 'may' THEN "may_visits" WHEN 'jun' THEN "jun_visits" WHEN 'jul' THEN "jul_visits" WHEN 'aug' THEN "aug_visits" WHEN 'sep' THEN "sep_visits" WHEN 'oct' THEN "oct_visits" WHEN 'nov' THEN "nov_visits" WHEN 'dec' THEN "dec_visits" ELSE NULL END AS "month_visits" FROM testschema.kiosk_monthly_visits AS t1 CROSS JOIN cols WHERE month_visits IS NOT NULL;

Dönüştürülen görünümün Teradata sürümüyle aynı sonucu üretip üretmediğini kontrol edebilirsiniz:

# SELECT * FROM testschema.unpivot_kiosk_monthly_visits; kiosk_id | year_no | month_name | month_visits 100 | 2020 | oct | 2000 100 | 2020 | nov | 2100 100 | 2020 | jul | 1700 100 | 2020 | feb | 1200 100 | 2020 | apr | 1400 100 | 2020 | aug | 1800 100 | 2020 | sep | 1900 100 | 2020 | jan | 1100 100 | 2020 | mar | 1300 100 | 2020 | may | 1500 100 | 2020 | jun | 1600 100 | 2020 | dec | 2200

TD_UNPIVOT için dönüştürme desteğini şurada deneyebilirsiniz: en son versiyon AWS SCT'den.

BİRLİKTE işlevi

QUANTILE bir sıralama fonksiyonudur. Girdi kümesini, her biri toplam popülasyonun eşit bir kısmını içeren belirli sayıda gruba böler. QUANTILE, ANSI SQL'de bulunan NTILE işlevinin tescilli bir Teradata uzantısıdır.

Örneğin, aşağıdaki Teradata görünümünü kullanarak aylık ziyaret verilerinin çeyreklerini hesaplayabiliriz:

REPLACE VIEW testschema.monthly_visit_rank AS
SELECT kiosk_id
, year_no
, month_name
, month_visits
, QUANTILE(4, month_visits) qtile
FROM testschema.unpivot_kiosk_monthly_visits
;

Görünümden seçim yaptığınızda, QUANTILE işlevi çeyreği hesaplar ve bunu çıktıya bir nitelik olarak uygular:

SELECT * FROM monthly_visit_rank; kiosk_id year_no month_name month_visits qtile
----------- ----------- ---------- ------------ ----------- 100 2020 jan 1100 0 100 2020 feb 1200 0 100 2020 mar 1300 0 100 2020 apr 1400 1 100 2020 may 1500 1 100 2020 jun 1600 1 100 2020 jul 1700 2 100 2020 aug 1800 2 100 2020 sep 1900 2 100 2020 oct 2000 3 100 2020 nov 2100 3 100 2020 dec 2200 3

Amazon Redshift, QUANTILE'ı uygulayabilen ve ANSI uyumlu olan genelleştirilmiş bir NTILE işlevini destekler. QUANTILE işlev çağrılarını otomatik olarak eşdeğer NTILE işlev çağrılarına dönüştürmek için AWS SCT'yi geliştirdik.

Örneğin, önceki Teradata görünümünü dönüştürdüğünüzde AWS SCT aşağıdaki Amazon Redshift kodunu üretir:

SELECT unpivot_kiosk_monthly_visits.kiosk_id
, unpivot_kiosk_monthly_visits.year_no
, unpivot_kiosk_monthly_visits.month_name
, unpivot_kiosk_monthly_visits.month_visits
, ntile(4) OVER (ORDER BY unpivot_kiosk_monthly_visits.month_visits ASC NULLS FIRST) - 1) AS qtile FROM testschema.unpivot_kiosk_monthly_visits
;

QUANTILE dönüşüm desteği şu anda mevcut AWS ÖTV.

UYGUNLUK filtresi

Teradata'daki QUALIFY yan tümcesi, analitik bir işlev tarafından üretilen satırları filtreler. Bir örneğe bakalım. Aylara göre mağaza gelirini içeren aşağıdaki tabloyu kullanıyoruz. Amacımız gelir bazında ilk beş ayı bulmaktır:

CREATE TABLE testschema.sales ( store_id INTEGER
, month_no INTEGER
, sales_amount DECIMAL(9,2))
PRIMARY INDEX (store_id); SELECT * FROM sales; store_id month_no sales_amount
----------- ----------- ------------ 1001 1 35000.00 1001 2 40000.00 1001 3 45000.00 1001 4 25000.00 1001 5 30000.00 1001 6 30000.00 1001 7 50000.00 1001 8 35000.00 1001 9 60000.00 1001 10 80000.00 1001 11 90000.00 1001 12 100000.00

Veriler Temmuz, Eylül, Ekim, Kasım ve Aralık aylarının en çok satış yapılan beş ay olduğunu gösteriyor.

Her ayı satışlara göre sıralamak için RANK işlevini kullanan bir görünüm oluşturuyoruz, ardından ilk beş ayı seçmek için QUALIFY işlevini kullanıyoruz:

REPLACE VIEW testschema.top_five_months( store_id
, month_no
, sales_amount
, month_rank
) as
SELECT store_id
, month_no
, sales_amount
, RANK() OVER (PARTITION BY store_id ORDER BY sales_amount DESC) month_rank
FROM testschema.sales
QUALIFY RANK() OVER (PARTITION by store_id ORDER BY sales_amount DESC) <= 5
;

Daha önce QUALIFY yan tümcesini kullandıysanız SQL ifadelerinizi manuel olarak yeniden kodlamanız gerekiyordu. Artık AWS SCT, QUALIFY'yi otomatik olarak Amazon Redshift uyumlu, ANSI uyumlu SQL'e dönüştürüyor. Örneğin, AWS SCT önceki görünümü şu şekilde yeniden yazar:

CREATE OR REPLACE VIEW testschema.top_five_months ( store_id
, month_no
, sales_amount
, month_rank) AS
SELECT qualify_subquery.store_id
, qualify_subquery.month_no
, qualify_subquery.sales_amount
, month_rank
FROM ( SELECT store_id , month_no , sales_amount , rank() OVER (PARTITION BY store_id ORDER BY sales_amount DESC NULLS FIRST) AS month_rank , rank() OVER (PARTITION BY store_id ORDER BY sales_amount DESC NULLS FIRST) AS qualify_expression_1 FROM testschema.sales) AS qualify_subquery WHERE qualify_expression_1 <= 5;

AWS SCT, orijinal sorguyu bir alt sorguya dönüştürür ve QUALIFY ifadesini alt sorguya filtre olarak uygular. AWS SCT, filtreleme amacıyla alt sorguya ek bir sütun ekler. Bu kesinlikle gerekli değildir ancak sütun takma adları kullanılmadığında kodu basitleştirir.

QUALIFY dönüşümünü şurada deneyebilirsiniz: en son versiyon AWS SCT'den.

Özet

Bu yeni özellikleri sizinle paylaşmaktan mutluluk duyuyoruz. Amazon Redshift'e geçiş yapmayı düşünüyorsanız bu özellikler şema dönüşümünüzü otomatikleştirmenize ve mevcut rapor ve uygulamalara yaptığınız yatırımı korumanıza yardımcı olabilir. Veri ambarı geçişine başlamak istiyorsanız genel belgelerimizden Amazon Redshift ve AWS SCT hakkında daha fazla bilgi edinebilirsiniz.

Bu gönderide, Amazon Redshift'e Teradata geçişlerinizi otomatikleştirmek için sunduğumuz düzinelerce yeni özellikten birkaçı anlatılıyor. Tescilli Teradata özelliklerine yönelik otomasyon ve diğer heyecan verici yeni yetenekler hakkında gelecek gönderilerde daha fazlasını paylaşacağız.

Daha fazla bilgi için yakında tekrar kontrol edin. O zamana kadar, hakkında daha fazla bilgi edinebilirsiniz Amazon Kırmızıya Kaydırma ve AWS Şema Dönüştürme Aracı. Mutlu göç!

Yazarlar Hakkında

Michael Soo AWS Database Migration Service ekibinde Kıdemli Veritabanı Mühendisidir. Müşterilerin veritabanı iş yüklerini AWS bulutuna geçirmesine yardımcı olan ürünler ve hizmetler geliştiriyor.

Raza Hafız AWS Profesyonel Hizmetlerinin Lake House Küresel Uzmanlık Uygulaması bünyesinde Veri Mimarıdır. Kurumsal veri ambarları oluşturma ve optimize etme konusunda 10 yıldan fazla profesyonel deneyime sahiptir ve müşterilerin verilerinin gücünü fark etmelerini sağlama konusunda tutkuludur. Kurumsal veri ambarlarını AWS Lake House Architecture'a taşıma konusunda uzmandır.

Po Hong, Doktora, Lake House Küresel Uzmanlık Uygulaması, AWS Profesyonel Hizmetler'in Baş Veri Mimarıdır. Müşterilerin içgörü elde etme süresini kısaltmak için yenilikçi çözümleri benimsemelerini destekleme konusunda tutkuludur. Po, büyük ölçekli MPP şirket içi veri ambarlarını AWS Lake House mimarisine taşıma konusunda uzmanlaşmıştır.

Entong Shen Amazon Redshift'in Yazılım Geliştirme Müdürüdür. 9 yılı aşkın bir süredir MPP veritabanları üzerinde çalışıyor ve sorgu optimizasyonu, istatistikler ve saklı prosedürler ve veri türleri gibi geçişle ilgili SQL dili özelliklerine odaklandı.

Sumit Singh Amazon Web Services'te Veritabanı Geçiş Hizmeti ekibinde veritabanı mühendisidir. Müşterilerle yakın işbirliği içinde çalışıyor ve şirket içi iş yüklerini AWS buluta taşımak için teknik destek sağlıyor. Ayrıca AWS Veri taşıma ürünlerinin kalitesinin ve işlevselliğinin sürekli olarak iyileştirilmesine de yardımcı olmaktadır.

Nelly Susanto AWS Database Migration Accelerator'ın Kıdemli Veritabanı Geçiş Uzmanıdır. Veri ambarı iş yüklerinin yanı sıra veritabanlarının taşınması ve çoğaltılmasına odaklanan 10 yılı aşkın teknik geçmişi vardır. Müşterilere bulut yolculuklarında yardımcı olma konusunda tutkulu.