Denodo: Converting Existing Queries to Denodo
Denodo is a data virtualization platform that enables users to access, integrate, and deliver data from multiple, disparate sources. One important benefit is the ability to do cross-database joins (for example, between InfoAccess and Badger Data) through a single data connection. However, like any other database there are always minor differences in syntax. This document provides easy steps for converting your existing queries from InfoAccess or Snowflake to the Denodo platform.
Converting InfoAccess to Denodo
1. Use Aliases
The conversion process is quickest if your queries use aliases for the database views.
In the following example, the "sm" and "ser" codes are aliases to their respective data views.
This example assumes that you have a connection to the DWHP database for InfoAccess and using the schema "uw".
SELECT sm.TERM, sm.CAMPUS_ID, sm.NAME, sm.WITHDREW_IND as WITHDREW,
sm.WITHDREW_REASON, sm.GENDER, sm.AGE, sm.CITIZEN_IND as CITIZEN,
sm.CUM_TRANSFER_CREDITS, sm.PREVIOUS_TERM_GPA,
ser.ETHNIC_REPORTING_DESCR
FROM uw.STDNT_MULTITERM sm
INNER JOIN uw.STDNT_ETHNIC_REPORTING ser ON (sm.ID = ser.ID)
WHERE sm.TERM = '1256';
2. Change Data Sources
When you are connected to Denodo, to update your query, all you need to do is update the data sources.
Since the original source was InfoAccess, you will want to change from the uw schema to use the schema for ext_ia.
SELECT sm.TERM, sm.CAMPUS_ID, sm.NAME, sm.WITHDREW_IND as WITHDREW,
sm.WITHDREW_REASON, sm.GENDER, sm.AGE, sm.CITIZEN_IND as CITIZEN,
sm.CUM_TRANSFER_CREDITS, sm.PREVIOUS_TERM_GPA,
ser.ETHNIC_REPORTING_DESCR
FROM ext_ia.STDNT_MULTITERM sm
INNER JOIN ext_ia.STDNT_ETHNIC_REPORTING ser ON (sm.ID = ser.ID)
WHERE sm.TERM = '1256';
Converting Badger Data to Denodo
1. Use Aliases
The conversion process is quickest if your queries use aliases for the database views.
In the following example, the facil, link, and spc are aliases to their respective data views.
This example assumes that you have a connection to the Badger Data database for BAGP and using the schema DM.
SELECT DISTINCT facil.FACILITY_NAME, facil.FACILITY_TYPE, spc.room, link.sa_facility_key, spc.room_name
FROM bagp.dm.SA_FACILITY_DIM facil
INNER JOIN bagp.dm.SA_SPACE_ALLOC_FACT link ON (facil.SA_FACILITY_KEY = link.SA_FACILITY_KEY)
INNER JOIN bagp.dm.SA_SPACE_DIM spc ON (spc.SA_SPACE_KEY = link.SA_SPACE_KEY)
WHERE spc.CURR_REC_FLAG = 'Y';
2. Change Data Sources
When you are connected to Denodo, to update your query, all you need to do is update the data sources.
Since the original source was Badger Data's BAGP.dm, you will want to change it to use the Denodo schema for ext_bdw.
To update your query, all you need to do is update the data source for your query. Since your original source was InfoAccess, you will want to change from the bagp.dm to ext_bdw.
SELECT DISTINCT facil.FACILITY_NAME, facil.FACILITY_TYPE, spc.room, link.sa_facility_key, spc.room_name
FROM ext_bdw.SA_FACILITY_DIM facil
INNER JOIN ext_bdw.SA_SPACE_ALLOC_FACT link ON (facil.SA_FACILITY_KEY = link.SA_FACILITY_KEY)
INNER JOIN ext_bdw.SA_SPACE_DIM spc ON (spc.SA_SPACE_KEY = link.SA_SPACE_KEY)
WHERE spc.CURR_REC_FLAG = 'Y';
Converting SQL Functions
Denodo uses industry-standard SQL syntax for its Virtual Query Language (VQL), but like all databases, you may encounter specific functions that vary between systems, like moving between Oracle/Snowflake/SQL Server/Postgres. These are usually low-impact and have a parallel function, but to simplify these function conversions, Denodo has provided some helpful quick-reference guides.
In order to access them, you will need an account on the Denodo Community.