Lookup Query
The Integration Service queries the lookup based on the ports and properties you configure in the Lookup transformation. The Integration Service runs a default lookup query when the first row enters the Lookup transformation.
If you use a lookup against a relational table, you can override the lookup query. You can use the override to add a WHERE clause or transform the lookup data before it is cached.
If you configure a SQL override and a filter on the lookup query, the Integration Service ignores the filter.
Default Lookup Query
The default lookup query contains the following statements:
- SELECT
- The SELECT statement includes all the lookup ports in the mapping. To view the SELECT statement for the lookup query, select the Use Custom Query property.
- ORDER BY
- The ORDER BY clause orders the columns in the same order they appear in the Lookup transformation. The Integration Service generates the ORDER BY clause. You cannot view this when you generate the default SQL.
SQL Override for a Lookup Query
You can override the lookup query for a relational lookup. You can add a WHERE clause and transform the lookup data before it is cached.
You can use reserved words and forward slashes in table names and column names.
You can enter a query to override the default lookup query completely. Or, you view and edit the default lookup query. The default lookup query includes the lookup ports, output ports, and the return port.
When you use an SQL override, the query appends the clause ORDER BY 1. The clause orders the data to reliably provide first and last values for other clauses.
Note: You can manually validate the SQL by running the following query in a Hive command line utility:
CREATE VIEW <table name> (<port list>) AS <SQL>
where:
- •<table name> is a name of your choice
- •<port list> is the comma-delimited list of ports in the source
- •<SQL> is the query to validate
Parameters in an SQL Override Query
You can use system parameters or user-defined parameters in the lookup query of a Lookup transformation. The SQL Editor provides a list of the system parameters and user-defined parameters that you can select from.
You can browse for or you can create user-defined parameters in the Query tab of a Lookup transformation. Define a default value for each parameter. You can override a default value by binding a mapplet or mapping parameter to the transformation parameter after you add the Lookup transformation to a mapping.
The following image shows the Lookup transformation Query tab:
Reserved Words
If any lookup name or column name contains a database reserved word, such as MONTH or YEAR, the mapping fails with database errors when the Integration Service executes SQL against the database.
You can create and maintain a reserved words file, reswords.txt, in the Integration Service installation directory. When the Integration Service initializes a mapping, it searches the reswords.txt file and places quotes around reserved words, and then executes the SQL against source, target, and lookup databases.
You might need to enable some databases, such as Microsoft SQL Server and Sybase, to use SQL-92 standards regarding quoted identifiers. Use environment SQL to issue the command. For example, with Microsoft SQL Server, use the following command:
SET QUOTED_IDENTIFIER ON
Guidelines for Overriding the Lookup Query
Certain rules and guidelines apply when you override a lookup query.
Consider the following guidelines when you override the lookup SQL query:
- •You can override the lookup SQL query for relational lookups.
- •Add a source lookup filter to filter the rows that are added to the lookup cache. This ensures that the Integration Service inserts rows in the dynamic cache and target table that match the WHERE clause.
- •If multiple Lookup transformations share a lookup cache, use the same lookup SQL override for each Lookup transformation.
- •If a table name or column name in the lookup query contains a reserved word, enclose the reserved word in quotes.
- •To override the lookup query for an uncached lookup, choose to return any value when the Integration Service finds multiple matches.
- •You cannot add or delete any columns from the default SQL statement.
- •The Developer tool does not validate the syntax of the SQL query. If the SQL override in an unconnected lookup query is not valid, the mapping fails.
Overriding the Lookup Query
You can override the default lookup SQL query to create a customized query on the lookup source.
1. On the Properties view, select the Query tab.
2. Select Advanced.
3. Select Use Custom Query.
4. Edit the lookup query in the SQL Query area.
You can double-click a table name, a column name, or a parameter to add it to the query.
5. Click Validate Query to validate the lookup query.
6. Select Push Custom Query to Database to run the lookup query in the database.
