SQL-transformaties toevoegen

U kunt SQL-gebaseerde tabellen opnemen in transformatietaken. Met een SQL-transformatie kunt u een query SQL SELECT invoeren in een pijplijn om complexe of eenvoudige transformaties te definiëren. U kunt ook macro's gebruiken om de query aan te passen om uitgevoerd te worden als een initiële lading of een incrementele lading wordt uitgevoerd.

Raadpleeg Gegevens transformeren voor meer informatie over het maken van een transformatietaak.

Een SQL-transformatie toevoegen

Ga als volgt te werk om een SQL-tranformatie toe te voegen in een transformatiegegevenstaak:

Selecteer in Transformeren de gegevensverzamelingen die moeten worden opgenomen in de query en klik op SQL-transformatie toevoegen.

SQL-transformatie toevoegen wordt weergegeven waar u de instellingen voor de transformatie kunt opgeven.
Voer een naam in voor de doelgegevensverzameling in Naam.

U kunt ook een langere beschrijving toevoegen in Beschrijving.
Selecteer in Materialisatie of de getransformeerde uitvoer gematerialiseerd moet worden of niet. U kunt ervoor kiezen de instelling over te nemen vanuit de gegevenstaakinstellingen.
- Met Aan worden tabellen gemaakt en wordt de bijbehorende ELT-verwerking afgehandeld.
- Met Uit worden weergaven gemaakt die transformaties ad-hoc uitvoeren.
Met Incrementele lading kunt u de query aanpassen voor een incrementele gegevenslading door filters of andere voorwaarden toe te passen om de set gegevens die wordt verwerkt te verkleinen met behulp van macro's. Incrementele lading is alleen beschikbaar als de gegevens worden gematerialiseerd als tabellen.
- Wanneer Incrementele lading is ingesteld op Aan
  
  De eerste uitvoering van de taak voert een initiële lading uit, waarbij alle resultaten van de query in uw doeltabel worden ingevoerd. Opvolgende uitvoeringen voeren incrementele ladingen uit waarbij filters of specifieke voorwaarden worden gebruikt die u hebt gedefinieerd voor incrementele verwerking. Tijdens de incrementele lading verwerkt de taak alleen gegevens als een update of een invoeging, verwijderingen worden niet beheerd.
- Wanneer Incrementele lading is ingesteld op Uit
  
  De eerste uitvoering van de taak voert een initiële lading uit, waarbij alle resultaten van de query in uw doeltabel worden ingevoerd. Opvolgende uitvoeringen verwerken alle resultaten van de query door deze te vergelijken met uw doeltabel, en verwerkingsrecords die nieuw, gewijzigd of verwijderd zijn.
InformatieStel Incrementele lading in op Uit als de query alle records selecteert die in het doel moeten voorkomen. Records die niet geselecteerd zijn, worden in het doel verwijderd.
Klik op Toevoegen als u klaar bent om de SQL-transformatie te maken.

SQL-transformatie bewerken wordt weergegeven.
Typ de query om de op SQL gebaseerde tabel te retourneren in SELECT-query.

De query kan alleen lezen van de brongegevensverzamelingen die u hebt geselecteerd, maar u kunt met behulp van Toevoegen meer brongegevensverzamelingen toevoegen.

U kunt selecteren vanuit de Gegevensverzameling en Kolommen.
Als incrementele lading wordt gebruikt, kunt u Macro's gebruiken om de query aan te passen om uitgevoerd te worden als een initiële lading of een incrementele lading wordt uitgevoerd.

Ga voor meer informatie naar Macro's gebruiken om de query aan te passen aan ladingstype.
Klik op Parameters extraheren om de parameters in de SELECT-query te parseren.

De parameters moeten zijn toegewezen aan de bron- of doelobjecten. U kunt toewijzen aan
- Geschiedenis type 2-structuren (_history)
- Huidige structuren
- Doelobjecten
  
  U kunt alleen doelobjecten toewijzen wanneer een parameter in een incrementele macro is gedefinieerd.
TipU kunt een parameter vervangen door een referentie om een query te krijgen die eenvoudiger te lezen is. Wijzig de waarde in Parameter en de referentie in SELECT-query wordt ook gewijzigd.
Klik op Tabel beschrijven om een voorbeeld te bekijken van de metagegevens van de query-uitvoer.

U kunt de resultaten ook controleren met behulp van een sample van de gegevens.

Voorbeeld van gegevens bekijken
Zorg dat de tabel een primaire sleutel bevat.

U kunt ook instellingen voor metagegevens bewerken.
- Stel primaire sleutels in de kolom Sleutel in.
- Stel in de kolom Null-waarde toegestaan in of een null-waarde is toegestaan voor de kolom.
- Selecteer een kolom en klik op Bewerken om een kolomnaam, sleutel, null-waarde toestaan en gegevenstype in te stellen.
Wanneer u tevreden bent met de resultaten van de query, klikt u op OK. De gegevensverzameling wordt nu toegevoegd aan de lijst met doelen en u kunt er een voorbeeld van bekijken.

Voorbeeld van gegevens bekijken

U kunt de resultaten controleren met behulp van een sample van de gegevens.

Voorbeeld van gegevens moet zijn ingeschakeld op tenantniveau in Qlik Beheerconsole. U vindt de optie Gegevens weergeven in Gegevensintegratie in Instellingen onder Kenmerkbeheer.

De volgende rollen moeten aan u zijn toegewezen om gegevens te kunnen controleren:

Kan gegevens bekijken in de ruimte waarin de gegevensverbinding zich bevindt.
Kan bekijken in de ruimte waarin het gegevensproject zich bevindt.

Om een voorbeeld van resultaten te bekijken:

Klik op Gegevens weergeven.

Resultaten worden weergegeven op basis van een sample van de gegevens. U kunt met Aantal rijen instellen hoeveel gegevensrijen in het voorbeeld worden opgenomen.

U kunt de voorbeeldgegevens op twee manieren filteren:

Gebruik om te filteren welke voorbeeldgegevens worden opgehaald.

Als u het filter ${OrderYear}>2023 gebruikt en Aantal rijen is ingesteld op 10, krijgt u een voorbeeld met 10 orders van 2024.
U kunt de voorbeeldgegevens sorteren op een specifieke kolom.

Dit is alleen van invloed op de bestaande voorbeeldgegevens. Als u hebt gebruikt om alleen orders van 2024 op te nemen en u stelt de kolomfilter in om orders van 2022 te tonen, dan krijgt u een leeg resultaat.

U kunt de gegevensample sorteren op een specifieke kolom. Het sorteren is allleen van invloed op de bestaande voorbeeldgegevens. Als u filter hebt gebruikt om alleen orders van 2024 op te nemen en u keert de sorteervolgorde om, dan bevatten de voorbeeldgegevens nog steeds alleen orders van 2024.

Macro's gebruiken om de query aan te passen aan ladingstype

Als incrementele lading wordt gebruikt, kunt u Macro's gebruiken om de query aan te passen om uitgevoerd te worden als een initiële lading of een incrementele lading wordt uitgevoerd.

Selecteer de macro en voeg de query in de respectievelijke clausule toe.

Q_RUN_INCREMENTAL voegt een clausule toe waarin u een query kunt plaatsen die alleen wordt uitgevoerd wanneer een incrementele lading wordt uitgevoerd.
Q_RUN_INITIAL_LOAD voegt een clausule toe waarin u een query kunt plaatsen die alleen wordt uitgevoerd wanneer een initiële lading wordt uitgevoerd.

U hoeft niet de volledige query te specificeren voor elke macro. U kunt code aanpassen of toevoegen op basis van uw gebruiksscenario.

Usecase: incrementeel filter met behulp van datumbereik:

In dit gebruiksscenario moet een set bestellingsgegevens worden getransformeerd met aangepaste SQL. Omdat dit de grootste gegevensset is, moet u incrementeel filteren opnemen om ervoor te zorgen dat de incrementele lading alleen bestellingen van de afgelopen 12 uur in acht neemt.

Dit is de basisquery:

SELECT O.ORDER_ID, O.CUSTOMER_ID, OD.PRODUCT_ID, OD.QUANTITY, O.AMOUNT , OD.LAST_UPDATE_DTM FROM “mydb”.”myStorageTask”.”ORDER” O JOIN “mydb”.”myStorageTask”.”ORDER_DETAIL” OD ON O.ORDER_ID = OD.ORDER_ID

Dit is het filter dat moet worden toegepast tijdens de incrementele uitvoering, waarbij u alleen rekening houdt met bestellingen die de afgelopen 12 uur zijn toegevoegd:

WHERE OD.UPDATE_DTM > DATEADD(hour, -12, CURRENT_TIMESTAMP())

Bij het implementeren van een SQL-gebaseerde tabel, wordt aanbevolen de tabelnamen te vervangen met de notatie ${TABLE}. Hiermee voorkomt u hardgecodeerde database- en schemanamen die kunnen veranderen van omgeving tot omgeving, bijvoorbeeld bij het verplaatsen van ontwikkeling naar productie.

Dit is de volledige query, waarbij de filtermacro wordt gebruikt en met de tabelnamen vervangen met de notatie ${TABLE}:

SELECT O.ORDER_ID, O.CUSTOMER_ID, OD.PRODUCT_ID, OD.QUANTITY, O.AMOUNT , OD.LAST_UPDATE_DTM FROM ${ORDER} O JOIN ${ORDER_DETAIL} OD ON O.ORDER_ID = OD.ORDER_ID {{#QLIK_FUNC_IF Q_RUN_INCREMENTAL}} WHERE OD.UPDATE_DTM > DATEADD(hour, -12, CURRENT_TIMESTAMP() ) {{/QLIK_FUNC_IF}}

Dit resulteert in een initiële ladingsquery:

SELECT O.ORDER_ID, O.CUSTOMER_ID, OD.PRODUCT_ID, OD.QUANTITY, O.AMOUNT , OD.LAST_UPDATE_DTM FROM ${ORDER} O JOIN ${ORDER_DETAIL} OD ON O.ORDER_ID = OD.ORDER_ID

En een incrementele ladingsquery:

SELECT O.ORDER_ID, O.CUSTOMER_ID, OD.PRODUCT_ID, OD.QUANTITY, O.AMOUNT , OD.LAST_UPDATE_DTM FROM ${ORDER} O JOIN ${ORDER_DETAIL} OD ON O.ORDER_ID = OD.ORDER_ID WHERE OD.UPDATE_DTM > DATEADD(hour, -12, CURRENT_TIMESTAMP() )

Usecase: incrementeel filter met gebruik van gegevens in het doel:

In dit gebruiksscenario moet een set bestellingsgegevens worden getransformeerd met aangepaste SQL. Omdat bestellingen een grote gegevensset zijn, moet u incrementeel filteren opnemen om ervoor te zorgen dat de incrementele lading alleen rekening houdt met records voor bestellingsdetails die nieuwer zijn dan de gegevens in de doeltabel. U moet ook markeren of een rij is verwerkt door de initiële lading of een incrementele lading.

Zoals u kunt zien in dit voorbeeld, kunnen de macro's worden gebruikt om elk deel van de query voor initiële lading of incrementele lading aan te passen.

Dit is de basisquery:

Dit is het filter dat moet worden toegepast tijdens de incrementele uitvoering. “ORDERS_DERIVED” is de naam van de doelgegevensset.

WHERE OD.UPDATE_DTM > = (SELECT MAX(LAST_UPDATE_DTM) FROM “mydb”,”myTrasformTask”.”ORDERS_DERIVED” )

Incrementele lading kan ook een notatie ${TABLE} toewijzen aan de doelobjecten die zijn gemaakt door de transformatietaak.

Dit is de volledige query, waarbij de filtermacro wordt gebruikt en met tabelnamen vervangen met de notatie ${TABLE}. LOADED_BY is ingesteld op INIT als de lading initieel is en op INCR als de lading incrementeel is:

SELECT O.ORDER_ID, O.CUSTOMER_ID, OD.PRODUCT_ID, OD.QUANTITY, O.AMOUNT, OD.LAST_UPDATE_DTM, {{#QLIK_FUNC_IF Q_RUN_INITIAL_LOAD}} ‘INIT’ as LOADED_BY {{/QLIK_FUNC_IF}} {{#QLIK_FUNC_IF Q_RUN_INCREMENTAL}} ‘INCR’ as LOADED_BY {{/QLIK_FUNC_IF}} FROM ${ORDER} O JOIN ${ORDER_DETAIL} OD ON O.ORDER_ID = OD.ORDER_ID {{#QLIK_FUNC_IF Q_RUN_INCREMENTAL}} WHERE OD.UPDATE_DTM >= (SELECT MAX(LAST_UPDATE_DTM) FROM ${TARGET_TABLE} ) {{/QLIK_FUNC_IF}}

Dit resulteert in een initiële ladingsquery:

SELECT O.ORDER_ID, O.CUSTOMER_ID, OD.PRODUCT_ID, OD.QUANTITY, O.AMOUNT, OD.LAST_UPDATE_DTM, ‘INIT’ as LOADED_BY FROM ${ORDER} O JOIN ${ORDER_DETAIL} OD ON O.ORDER_ID = OD.ORDER_ID

En een incrementele ladingsquery:

SELECT O.ORDER_ID, O.CUSTOMER_ID, OD.PRODUCT_ID, OD.QUANTITY, O.AMOUNT, OD.LAST_UPDATE_DTM, ‘INCR’ as LOADED_BY FROM ${ORDER} O JOIN ${ORDER_DETAIL} OD ON O.ORDER_ID = OD.ORDER_ID WHERE OD.UPDATE_DTM >= (SELECT MAX(LAST_UPDATE_DTM) FROM ${TARGET_TABLE} )

Aanbevolen procedure

Wanneer u een SQL-transformatie toevoegt, wordt niet automatisch een primaire sleutel gedefinieerd. Voeg een sleutel toe aan de gegevensverzameling in het tabblad Gegevensverzameling.
Maak niet handmatig kolomkoppen aan in de getransformeerde uitvoer.
Vermijd het gebruik van SELECT met *, omdat hierdoor verschillende kolommen worden weergegeven elke keer nadat een query is uitgevoerd. Als het schema is gewijzigd, wijzig de query dan ook en voer Tabel omschrijven nogmaals uit. U kunt alle kolommen bewerken en tabellen wijzigen.

Was deze pagina nuttig?

Als u problemen ervaart op deze pagina of de inhoud onjuist is – een typfout, een ontbrekende stap of een technische fout – laat het ons weten zodat we dit kunnen verbeteren!

Geef hier uw feedback