Amazon Redshift Connectors

Back Next

Rules and guidelines for configuring SQL query

Consider the following rules and guidelines when you configure an SQL query:

When you select the source type as Query, you can override a SQL query over a custom query by setting the flag

precedenceToSQLOverrideOverCustomQuery=True

in the JVM option. Ensure that the number of columns in the overridden SQL query is same as the custom query.

When you run a mapping in advanced mode and define a SQL query with alldatatypes, the target columns with the boolean data type appear as NULL.

When you select individual columns but not all the columns in an SQL query in a mapping in advanced mode, the values are written as NULL to the Redshift target.

When you use a parameterized query source type in a mapping to read from multiple tables, and you configure a join for one or more tables that have the same column names, the mapping fails.

For example, see the following SQL query that involves a full outer join between two tables EMPLOYEE and DEPARTMENT that are part of the SALES.PUBLIC schema, where two columns have the same name, CITY:

SELECT EMP_ID, NAME, CITY, DEPT_ID, DEPT_NAME, CITY FROM SALES.PUBLIC.EMPLOYEE
FULL OUTER JOIN SALES.PUBLIC.DEPARTMENT ON EMP_ID = DEPT_ID

To distinguish the conflicting column names, add aliases that the database can refer to while joining the tables:

SELECT e.EMP_ID, e.NAME, e.CITY as ecity,d.DEPT_ID, d.DEPT_NAME, d.CITY as dcity FROM SALES.PUBLIC.EMPLOYEE e 
FULL OUTER JOIN SALES.PUBLIC.DEPARTMENT d ON e.EMP_ID = d.DEPT_ID

Rename Saved Search

Table of Contents

Amazon Redshift Connectors

Amazon Redshift Connectors

Rules and guidelines for configuring SQL query

Rules and guidelines for configuring SQL query