Versnel uw datawarehouse-migratie naar Amazon Redshift – deel 2

Dit is het tweede bericht in een serie die uit meerdere delen bestaat. We zijn verheugd om tientallen nieuwe functies te kunnen delen om uw schemaconversie te automatiseren; behoud uw investering in bestaande scripts, rapporten en applicaties; de queryprestaties versnellen; en verlaagt mogelijk uw totale kosten om naartoe te migreren Amazon roodverschuiving. Bekijk het eerste bericht Versnel uw datawarehouse-migratie naar Amazon Redshift – deel 1 voor meer informatie over geautomatiseerde macroconversie, hoofdletterongevoelige tekenreeksvergelijking, hoofdlettergevoelige ID's en andere opwindende nieuwe functies.

Amazon Redshift is het toonaangevende clouddatawarehouse. Geen enkel ander datawarehouse maakt het zo eenvoudig om nieuwe inzichten uit uw data te halen. Met Amazon Redshift kunt u exabytes aan gegevens in uw datawarehouse, operationele datastores en data lake opvragen met behulp van standaard SQL. U kunt ook andere services integreren, zoals Amazon EMR, Amazone Athene en Amazon Sage Maker om alle analytische mogelijkheden in de AWS Cloud te gebruiken.

Veel klanten hebben om hulp gevraagd bij het migreren van hun zelfbeheerde datawarehouse-engines naar Amazon Redshift. In deze gevallen beschikt u mogelijk over terabytes (of petabytes) aan historische gegevens, een sterke afhankelijkheid van bedrijfseigen functies en duizenden extractie-, transformatie- en laadprocessen (ETL) en rapporten die gedurende jaren (of decennia) van gebruik zijn opgebouwd.

Tot nu toe was het migreren van een datawarehouse naar AWS complex en vergde het een aanzienlijke hoeveelheid handmatige inspanning.

Vandaag delen we graag aanvullende verbeteringen aan de AWS Schema Conversie Tool (AWS SCT) om uw migraties naar Amazon Redshift te automatiseren. Deze verbeteringen verminderen de hercodering die nodig is voor uw gegevenstabellen, en, nog belangrijker, het handmatige werk dat nodig is voor views, opgeslagen procedures, scripts en andere applicatiecode die deze tabellen gebruiken.

In dit bericht introduceren we automatisering voor INTERVAL- en PERIOD-gegevenstypen, automatische typecasting, ondersteuning voor binaire gegevens en enkele andere verbeteringen waar klanten om hebben gevraagd. We laten u zien hoe u AWS SCT kunt gebruiken om objecten uit een Teradata-datawarehouse te converteren en bieden koppelingen naar relevante documentatie, zodat u deze nieuwe mogelijkheden kunt blijven verkennen.

INTERVAL-gegevenstypen

Een INTERVAL is een niet-verankerde tijdsduur, zoals ‘1 jaar’ of ‘2 uur’, die geen specifieke begin- of eindtijd heeft. In Teradata worden INTERVAL-gegevens geïmplementeerd als 13 verschillende gegevenstypen, afhankelijk van de granulariteit van de tijd die wordt weergegeven. De volgende tabel geeft een overzicht van deze typen.

Amazon Redshift ondersteunt standaard geen INTERVAL-gegevenstypen. Als u voorheen INTERVAL-typen in uw datawarehouse gebruikte, moest u aangepaste code ontwikkelen als onderdeel van het databaseconversieproces.

Nu converteert AWS SCT automatisch INTERVAL-gegevenstypen voor u. AWS SCT converteert een INTERVAL-kolom naar een CHARACTER VARYING-kolom in Amazon Redshift. Vervolgens converteert AWS SCT uw applicatiecode die de kolom gebruikt om de INTERVAL-semantiek te emuleren.

Bekijk bijvoorbeeld de volgende Teradata-tabel, die een MONTH-intervalkolom heeft. In deze tabel worden verschillende soorten verlof en de toegestane duur voor elk verlof opgeslagen.

CREATE TABLE testschema.loa_durations ( loa_type_id INTEGER
, loa_name VARCHAR(100) CHARACTER SET LATIN
, loa_duration INTERVAL MONTH(2))
PRIMARY INDEX (loa_type_id);

AWS SCT converteert de tabel als volgt naar Amazon Redshift. Omdat Amazon Redshift geen eigen INTERVAL-gegevenstype heeft, vervangt AWS SCT dit door een VARCHAR-gegevenstype.

CREATE TABLE testschema.loa_durations( loa_type_id INTEGER
, loa_name VARCHAR(100)
, loa_duration VARCHAR(64)
)
DISTSTYLE KEY
DISTKEY
(
loa_type_id
)
SORTKEY
(
loa_type_id
);

Laten we nu aannemen dat uw applicatiecode de loa_duration kolom, zoals de volgende Teradata-weergave. Hier wordt het veld INTERVALMAAND toegevoegd aan de huidige datum om te berekenen wanneer een verlof eindigt als het vandaag begint.

REPLACE VIEW testschema.loa_projected_end_date AS
SELECT loa_type_id loa_type_id
, loa_name loa_name
, loa_duration
, current_date AS today
, current_date + loa_duration AS end_date
FROM
testschema.loa_durations
;

Omdat de gegevens worden opgeslagen als KARAKTER VARIËREN, injecteert AWS SCT het juiste type CAST in de Amazon Redshift-code om de tekenreekswaarden te interpreteren als een MAAND-interval. Vervolgens converteert het de rekenkunde met behulp van Amazon Redshift-datumfuncties.

CREATE OR REPLACE VIEW testschema.loa_projected_end_date (loa_type_id, loa_name, loa_duration, today, end_date) AS
SELECT loa_type_id AS loa_type_id
, loa_name AS loa_name
, loa_duration
, CURRENT_DATE AS today
, dateadd(MONTH, CAST (loa_duration AS INTEGER),CURRENT_DATE)::DATE AS end_date
FROM testschema.loa_durations
;

Als bonus converteert AWS SCT automatisch alle letterlijke INTERVAL-waarden die u mogelijk in uw code gebruikt.

Bekijk bijvoorbeeld de volgende Teradata-tabel. De tabel bevat een kolom DATUM, waarin de laatste datum wordt vastgelegd waarop een werknemer promotie heeft gemaakt.

CREATE TABLE TESTSCHEMA.employees ( id INTEGER
, name VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC
, manager_id INTEGER
, last_promo_date DATE FORMAT 'YY/MM/DD'
)
UNIQUE PRIMARY INDEX ( id );

Stel nu dat de database een weergave bevat die de volgende datum berekent waarop een werknemer in aanmerking komt voor een promotie. We implementeren een bedrijfsregel dat werknemers die nog nooit promotie hebben gemaakt, over 1.5 jaar in aanmerking komen voor promotie. Alle overige medewerkers komen 2.5 jaar na hun laatste promotie in aanmerking. Zie de volgende code:

REPLACE VIEW testschema.eligible_for_promo AS
SELECT id
, name
, last_promo_date
, CASE WHEN last_promo_date is NULL THEN current_date + INTERVAL '18' MONTH ELSE last_promo_date + INTERVAL '2-06' YEAR TO MONTH END eligible_date
FROM employees
;

AWS SCT converteert de INTERVAL-waarden die in de CASE-instructie worden gebruikt en vertaalt de datumexpressies dienovereenkomstig:

CREATE OR REPLACE VIEW testschema.eligible_for_promo (id, name, last_promo_date, eligible_date) AS
SELECT id
, name
, last_promo_date
, CASE WHEN last_promo_date IS NULL THEN dateadd(MONTH, 18, CURRENT_DATE)::DATE ELSE dateadd(MONTH, 30, last_promo_date)::DATE END AS eligible_date
FROM testschema.employees
;

We zijn enthousiast over INTERVAL-automatisering in AWS SCT en moedigen u aan om het eens te proberen. Voor meer informatie over aan de slag gaan met AWS SCT, zie AWS SCT installeren, verifiëren en updaten.

PERIOD-gegevenstype

Een PERIOD-gegevenswaarde vertegenwoordigt een tijdsduur met een gespecificeerd begin en einde. Bijvoorbeeld de letterlijke Teradata “(2021-01-01 to 2021-01-31)” is een periode met een duur van 31 dagen die begint en eindigt op respectievelijk de eerste en de laatste dag van januari 2021. PERIOD-gegevenstypen kunnen drie verschillende granulariteiten hebben: DATE, TIME of TIMESTAMP. De volgende tabel bevat enkele voorbeelden.

Net als bij INTERVAL wordt het PERIOD-gegevenstype niet standaard ondersteund door Amazon Redshift. Als u voorheen deze gegevenstypen in uw tabellen gebruikte, moest u aangepaste code schrijven als onderdeel van het databaseconversieproces.

Nu converteert AWS SCT PERIOD-gegevenstypen automatisch voor u. AWS SCT converteert een PERIOD-kolom naar twee DATE- (of TIME of TIMESTAMP)-kolommen, afhankelijk van het geval op Amazon Redshift. Vervolgens converteert AWS SCT uw applicatiecode die de kolom gebruikt om de semantiek van de bronengine te emuleren.

Bekijk bijvoorbeeld de volgende Teradata-tabel:

CREATE SET TABLE testschema.period_table ( id INTEGER
, period_col PERIOD(timestamp)) UNIQUE PRIMARY INDEX (id);

AWS SCT converteert de PERIOD(TIMESTAMP)-kolom naar twee TIMESTAMP-kolommen in Amazon Redshift:

CREATE TABLE IF NOT EXISTS testschema.period_table( id INTEGER
, period_col_begin TIMESTAMP
, period_col_end TIMESTAMP
)
DISTSTYLE KEY
DISTKEY
(id)
SORTKEY
(id);

Laten we nu eens kijken naar een eenvoudig voorbeeld van hoe u AWS SCT kunt gebruiken om uw applicatiecode te converteren. Een veel voorkomende bewerking in Teradata is het extraheren van de begin- (of eind-) tijdstempels in een PERIOD-waarde met behulp van de ingebouwde functies BEGIN en END:

REPLACE VIEW testschema.period_view_begin_end AS SELECT BEGIN(period_col) AS period_start
, END(period_col) AS period_end FROM testschema.period_table
;

AWS SCT converteert de weergave zodat deze verwijst naar de getransformeerde tabelkolommen:

CREATE OR REPLACE VIEW testschema.period_view_begin_end (period_start, period_end) AS
SELECT period_col_begin AS period_start
, period_col_end AS period_end
FROM testschema.period_table;

We blijven doorgaan met het bouwen van automatisering voor PERIOD-gegevensconversie, dus houd ons in de gaten voor meer verbeteringen. In de tussentijd kunt u nu de PERIOD-gegevenstypeconversiefuncties in AWS SCT uitproberen. Voor meer informatie, zie AWS SCT installeren, verifiëren en updaten.

Type gieten

Sommige datawarehouse-engines, zoals Teradata, bieden een uitgebreide set regels om gegevenswaarden in expressies te gieten. Deze regels laten het toe impliciete casts, waarbij het doelgegevenstype wordt afgeleid uit de expressie, en expliciete casts, die doorgaans een functie gebruiken om de typeconversie uit te voeren.

Voorheen moest u impliciete cast-bewerkingen handmatig in uw SQL-code converteren. Nu delen we graag mee dat AWS SCT automatisch impliciete casts converteert als dat nodig is. Deze functie is nu beschikbaar voor de volgende reeks krachtige Teradata-gegevenstypen.

Laten we eens kijken hoe u getallen naar DATE kunt casten. Veel Teradata-toepassingen behandelen getallen en DATE als gelijkwaardige waarden. Intern slaat Teradata DATE-waarden op als INTEGER. De regels voor het converteren tussen een INTEGER en een DATE zijn bekend en ontwikkelaars hebben deze informatie vaak misbruikt om datumberekeningen uit te voeren met behulp van de rekenkunde INTEGER.

Bekijk bijvoorbeeld de volgende Teradata-tabel:

CREATE TABLE testschema.employees ( id INTEGER
, name VARCHAR(20) CHARACTER SET LATIN
, manager_id INTEGER
, last_promo_date DATE FORMAT 'YY/MM/DD')
UNIQUE PRIMARY INDEX ( id );

We voegen een enkele rij gegevens in de tabel in:

select * from employees; *** Query completed. One row found. 4 columns returned. *** Total elapsed time was 1 second. id name manager_id last_promo_date
----------- -------------------- ----------- --------------- 112 Britney 201 ?

We gebruiken een macro om het bestand bij te werken last_promo_date veld voor id = 112. De macro accepteert een BIGINT-parameter om het DATE-veld in te vullen.

replace macro testschema.set_last_promo_date(emp_id integer, emp_promo_date bigint) AS (
update testschema.employees
set last_promo_date = :emp_promo_date
where id = :emp_id;
);

Nu voeren we de macro uit en controleren de waarde van de last_promo_date attribuut:

exec testschema.set_last_promo_date(112, 1410330); *** Update completed. One row changed. *** Total elapsed time was 1 second. select * from employees; *** Query completed. One row found. 4 columns returned. *** Total elapsed time was 1 second. id name manager_id last_promo_date
----------- -------------------- ----------- --------------- 112 Britney 201 41/03/30

U kunt het zien last_promo_date attribuut is ingesteld op de datum 30 maart 2041.

Laten we nu AWS SCT gebruiken om de tabel en macro naar Amazon Redshift te converteren. Zoals we binnen zagen Deel 1 van deze serie converteert AWS SCT de Teradata-macro naar een opgeslagen Amazon Redshift-procedure:

CREATE TABLE IF NOT EXISTS testschema.employees( id INTEGER
, name CHARACTER VARYING(20) , manager_id INTEGER
, last_promo_date DATE
)
DISTSTYLE KEY
DISTKEY
(id)
SORTKEY
(id); CREATE OR REPLACE PROCEDURE testschema.set_last_promo_date(par_emp_id INTEGER, par_emp_promo_date BIGINT)
AS $BODY$
BEGIN UPDATE testschema.employees SET last_promo_date = TO_DATE((par_emp_promo_date + 19000000), 'YYYYMMDD') WHERE id = par_emp_id;
END;
$BODY$
LANGUAGE plpgsql;

Merk op dat 20410330 = 1410330 + 19000000; dus het toevoegen van 19,000,000 aan de invoer retourneert de juiste datumwaarde 2041-03-30.

Wanneer we nu de opgeslagen procedure uitvoeren, wordt de last_promo_date zoals verwacht:

myredshift=# select * from testschema.employees; id | name | manager_id | last_promo_date 112 | Britney | 201 |
(1 row) myredshift=# call testschema.set_last_promo_date(112, 1410330);
CALL myredshift=# select * from testschema.employees; id | name | manager_id | last_promo_date 112 | Britney | 201 | 2041-03-30
(1 row)

Automatisch casten van gegevenstypes is nu beschikbaar in AWS SCT. Jij kan download de nieuwste versie en probeer het uit.

BLOB-gegevens

Amazon Redshift heeft geen native ondersteuning voor BLOB-kolommen, die u gebruikt om grote binaire objecten zoals tekst of afbeeldingen op te slaan.

Als u voorheen een tabel met een BLOB-kolom migreerde, moest u de BLOB-waarden handmatig naar de bestandsopslag verplaatsen, zoals Amazon eenvoudige opslagservice (Amazon S3) en voeg vervolgens een verwijzing naar het S3-bestand in de tabel toe. Het gebruik van Amazon S3 als opslagdoel voor binaire objecten is een best practice, omdat deze objecten groot zijn en doorgaans een lage analytische waarde hebben.

We zijn blij om te kunnen delen dat AWS SCT dit proces nu voor u automatiseert. AWS SCT vervangt de BLOB-kolom door een CHARACTER VARYING-kolom in de doeltabel. Wanneer u vervolgens de AWS SCT-gegevensextractors Om uw gegevens te migreren, uploaden de extractors de BLOB-waarde naar Amazon S3 en voegen ze een verwijzing naar de BLOB in de doeltabel in.

Laten we bijvoorbeeld een tabel maken in Teradata en deze vullen met enkele gegevens:

CREATE SET TABLE TESTSCHEMA.blob_table ( id INTEGER
, blob_col BLOB(10485760))
PRIMARY INDEX ( id ); select * from blob_table; *** Query completed. 2 rows found. 2 columns returned. *** Total elapsed time was 1 second. id blob_col
----------- --------------------------------------------------------------- 1 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 2 BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB

Nu converteren we de tabel met AWS SCT en bouwen deze op Amazon Redshift:

myredshift=# d testschema.blob_table; Table "testschema.blob_table"
Column | Type | Collation | Nullable | Default id | integer |. | | blob_col | character varying(1300) | | |

Vervolgens gebruiken we de AWS SCT-data-extractors om de tabelgegevens van Teradata naar Amazon Redshift te migreren.

Als we naar de tabel in Amazon Redshift kijken, zie je de paden naar de S3-bestanden die de BLOB-waarden bevatten:

myredshift=# select * from testschema.blob_table;
(2 rows) id | blob_col 2 | s3://<bucket name>/data/c12f53330dd3427a845a77f143d4a1a1/dbdee8e0485c481dad601fd6170fbfb4_lobs/2/308b6f0a902941e793212058570cdda5.dat 1 | s3://<bucket name>/data/c12f53330dd3427a845a77f143d4a1a1/dbdee8e0485c481dad601fd6170fbfb4_lobs/2/a7686067af5549479b52d81e83c3871e.dat

En op Amazon S3 kunt u de daadwerkelijke gegevensbestanden zien. Er zijn er twee, één voor elke BLOB-waarde:

$ aws s3 ls s3://<bucket name>/data/c12f53330dd3427a845a77f143d4a1a1/dbdee8e0485c481dad601fd6170fbfb4_lobs/2/
2021-05-13 23:59:47 23 522fee54fda5472fbae790f43e36cba1.dat
2021-05-13 23:59:47 24 5de6c53831f741629476e2c2cbc6b226.dat

BLOB-ondersteuning is nu beschikbaar in AWS SCT en de AWS SCT-gegevensextractors. Download de nieuwste versie van de applicatie en probeer het vandaag nog.

Multi-byte CHARACTER-conversie

Teradata ondersteunt multibyte-tekens in CHARACTER-gegevenskolommen, dit zijn velden met een vaste lengte. Amazon Redshift ondersteunt multibyte-tekens in CHARACTER VARYING-velden, maar niet in CHARACTER-kolommen met een vaste lengte.

Als u voorheen CHARACTER-kolommen met een vaste lengte had, moest u bepalen of deze multibyte-tekengegevens bevatten en de doelkolomgrootte indien nodig vergroten.

AWS SCT overbrugt deze kloof nu voor u. Als uw Teradata-tabellen CHARACTER-kolommen met multibyte-tekens bevatten, converteert AWS SCT deze kolommen automatisch naar Amazon Redshift CHARACTER VARYING-velden en stelt de kolomgroottes dienovereenkomstig in. Beschouw het volgende voorbeeld, dat vier kolommen bevat: een LATIN-kolom die alleen tekens van één byte bevat, en de kolommen UNICODE, GRAPHIC en KANJISJIS die tekens van meerdere bytes kunnen bevatten:

create table testschema.char_table ( latin_col char(70) character set latin
, unicode_col char(70) character set unicode
, graphic_col char(70) character set graphic
, kanjisjis_col char(70) character set kanjisjis
);

AWS SCT vertaalt de LATIN-kolom naar een CHARACTER-kolom met een vaste lengte. De kolommen met meerdere bytes worden vergroot en geconverteerd naar KARAKTERVARIANT:

CREATE TABLE IF NOT EXISTS testschema.char_table ( latin_col CHARACTER(70)
, unicode_col CHARACTER VARYING(210)
, graphic_col CHARACTER VARYING(210)
, kanjisjis_col CHARACTER VARYING(210)
)
DISTSTYLE KEY
DISTKEY
(col1)
SORTKEY
(col1);

Automatische conversie voor multibyte CHARACTER-kolommen is nu beschikbaar in AWS SCT.

GEOMETRIE gegevenstypegrootte

Amazon Redshift ondersteunt al lang georuimtelijke gegevens met een GEOMETRY gegevenstype en bijbehorende ruimtelijke functies.

Voorheen beperkte Amazon Redshift de maximale grootte van een GEOMETRY-kolom tot 64 KB, waardoor sommige klanten met grote objecten beperkt waren. Nu delen we graag mee dat de maximale grootte van GEOMETRY-objecten is vergroot tot iets minder dan 1 MB (in het bijzonder 1,048,447 bytes).

Bekijk bijvoorbeeld de volgende Teradata-tabel:

create table geometry_table ( id INTEGER
, geometry_col1 ST_GEOMETRY , geometry_col2 ST_GEOMETRY(1000)
, geometry_col3 ST_GEOMETRY(1048447) , geometry_col4 ST_GEOMETRY(10484470)
, geometry_col5 ST_GEOMETRY INLINE LENGTH 1000
)
;

U kunt AWS SCT gebruiken om het naar Amazon Redshift te converteren. De geconverteerde tabeldefinitie is als volgt. Voor de geconverteerde kolommen is geen groottespecificatie nodig omdat Amazon Redshift impliciet de kolomgrootte instelt.

CREATE TABLE IF NOT EXISTS testschema.geometry_table(
id INTEGER,
geometry_col1 GEOMETRY,
geometry_col2 GEOMETRY,
geometry_col3 GEOMETRY,
geometry_col4 GEOMETRY,
geometry_col5 GEOMETRY
)
DISTSTYLE KEY
DISTKEY
(
id
)
SORTKEY
(
id
);
ALTER TABLE testschema.geometry_table ALTER DISTSTYLE AUTO;
ALTER TABLE testschema.geometry_table ALTER SORTKEY AUTO;

Grote GEOMETRY-kolommen zijn nu beschikbaar in Amazon Redshift. Voor meer informatie, zie Ruimtelijke gegevens opvragen in Amazon Redshift.

Conclusie

We delen deze nieuwe functies graag met u. Als u een migratie naar Amazon Redshift overweegt, kunnen deze mogelijkheden u helpen uw schemaconversie te automatiseren en uw investering in bestaande rapporten, applicaties en ETL te behouden, en uw queryprestaties te versnellen.

In dit bericht worden enkele van de tientallen nieuwe functies beschreven die we onlangs hebben geïntroduceerd om uw datawarehouse-migraties naar Amazon Redshift te automatiseren. We zullen meer delen in komende berichten. Je hoort over aanvullende SQL-automatisering, een speciaal gebouwde scripttaal voor Amazon Redshift met BTEQ-compatibiliteit en geautomatiseerde ondersteuning voor eigen SQL-functies.

Kom snel terug voor meer informatie. Tot die tijd kun je er meer over leren Amazon roodverschuiving en AWS Schema Conversie Tool op de AWS-website. Veel plezier met migreren!

Over de auteur

Michaël Soo is een database-ingenieur bij het AWS DMS- en AWS SCT-team bij Amazon Web Services.

Bron: https://aws.amazon.com/blogs/big-data/part-2-accelerate-your-data-warehouse-migration-to-amazon-redshift/