////
Licensed to the Apache Software Foundation (ASF) under one
or more contributor license agreements.  See the NOTICE file
distributed with this work for additional information
regarding copyright ownership.  The ASF licenses this file
to you under the Apache License, Version 2.0 (the
"License"); you may not use this file except in compliance
with the License.  You may obtain a copy of the License at
  http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing,
software distributed under the License is distributed on an
"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
KIND, either express or implied.  See the License for the
specific language governing permissions and limitations
under the License.
////
:documentationPath: /pipeline/transforms/
:language: en_US
:description: The Database lookup transform allows you to look up values in a database table.

= image:transforms/icons/dblookup.svg[Database Lookup transform Icon, role="image-doc-icon"] Database Lookup

[%noheader,cols="3a,1a", role="table-no-borders" ]
|===
|
== Description

The Database lookup transform allows you to look up values in a database table.
Lookup values are added as new fields onto the stream.
|
== Supported Engines
[%noheader,cols="2,1a",frame=none, role="table-supported-engines"]
!===
!Hop Engine! image:check_mark.svg[Supported, 24]
!Spark! image:check_mark.svg[Supported, 24]
!Flink! image:check_mark.svg[Supported, 24]
!Dataflow! image:check_mark.svg[Supported, 24]
!===
|===

== Options

[options="header]
|===
|Option|Description
|Transform name|Name of the transform.
This name has to be unique in a single pipeline.
|Connection|Database connection to the lookup table.
|Lookup schema|Database schema containing the lookup table.
|Lookup Table|Name of the database table used for the lookup.
|Enable cache?|Enables caching of database lookups.
This means that once a key (or group of key) has been looked up, the looked up values are stored, and returned again the next time this key (or group of key) is being looked up (without incurring the cost of a database call).

*Important*: If other processes are changing values in the table where you perform a lookup, do not cache values.
In all other instances, caching values increases the performance substantially because database lookups are relatively slow.
If you can't use the cache, consider launching several copies of the simultaneously.
A simultaneous launch keeps the database busy through different connections.
See Launching several copies of a transform.
|Cache size in rows|The size of the cache (number of rows), 0 means cache everything.
|Load all data from table|Pre-loads the cache with all the data present in the lookup table.
This may improve performance by avoiding database calls.
However, if you have a large table, you risk running out of memory.

*IMPORTANT*: In memory look-ups can lead to different results because of the differences in the way your database compares data.
For example, if your database table allows case-insensitive comparisons, you may get different results with this option.
The same goes for data with trailing spaces.
Those are ignored in certain databases when using certain character data types.
See more details on the below note about this option.
|Keys to look up table|The keys and conditions to perform the database lookup.

*Tip*: When using the LIKE operator on the key lookup it's not automatically adding wildcards to the stream value.
So by default the LIKE operator is behaving as an "=" (see your database for SQL specific topics on the LIKE operator).
It is possible to add the wildcards to add a 'Replace in String' transform before the database lookup.
In the replace transform select the lookup field then use Regex to look for (^.*) and replace with %$1%.
This will add the wildcards to the field for the subsequent lookup task.
|Values to return table a|The fields from the lookup table to add to the output stream.
"New name" allows you to use a different name if the database column name is inappropriate.
"Default" is the value returned (instead of null) if the lookup fails.

Notes:

* Enabling error handling on the Database Lookup transform will redirect all failed lookup rows to the error handling transform, instead of adding them to the main output steam with null/default values.
* _Type_ is the type of the output field.
* When we use _Trim type_ option, the current transform behavior is to trim all the spaces but leave any newline character eventually contained in the string extracted by the transform upon a successful lookup.

|Do not pass the row if the lookup fails|Enable to avoid passing a row when lookup fails.
Within the SQL syntax, enabling this would be an INNER JOIN, otherwise it would be an OUTER JOIN.
|Fail on multiple results?|Enable to force the transform to fail if the lookup returns multiple results.
|Order by|If the lookup query returns multiple results, the ORDER BY clause helps you to select the record to take.
For example, ORDER BY would allow you to pick the customer with the highest sales volume in a specified state.
|Get Fields|Click to return a list of available fields from the input stream(s) of the transform.
|Get lookup fields|Click to return a list of available fields from the lookup table that can be added to the transform's output stream.
|===

*Note on the Load all data from table option*: As an example: when storing a value of type "CHAR(3)", many databases will store "ab" using 3 characters: "ab " (notice the blank space).
When you do a "SELECT * FROM my_lookup_table WHERE key_column = 'ab'", the database is smart and adds a blank space before applying the where clause (it looks for 'ab ' instead of 'ab').

The problem occurs when you use "load all data from table" to pre-load the cache: at startup, the cache is built using all the values present in the database, so we store 'ab ' in the cache.
Later, looking up 'ab' fails, because the trailing space matters in java equality.

Note that this problem does not occur when using the cache, if "load all data from table" is disabled.
Indeed, the first lookup for 'ab' would find no entry in the cache, call the database (which is smart enough to handle the trailing whitespace problem), get the correct result, and store it in the java cache under the 'ab' key.
Thus, the next lookup for 'ab' will find the appropriate result in the cache :)

