# Database Lookup

The *Database Lookup* object in Astera is used to look up values from a source. It uses a database table that contains the lookup values as well as a set of corresponding output values. When the lookup is performed, the object returns either a single output field or multiple output fields, depending on the nature of the lookup table. Similarly, the lookup can be performed based on one lookup field or multiple lookup fields. In each case, the output field or fields are returned from the records in which the lookup values match the incoming values.

### Video

{% embed url="<https://www.youtube.com/watch?v=qJQLNRUgJLo>" %}

### Use Case

In this use case, we have a sample *Customers* dataset that is stored in a database table. Within this dataset, there is a field that contains the country of residence for each customer. We have another database table that contains all of these countries and their corresponding codes. Our goal is to replace the full country names with codes while writing the customer dataset to an Excel file. To do this, we will use a *Database Lookup* object.

### How to Work with Database Lookup in Astera

1. Drag the relevant source object from the Toolbox and drop it onto the designer. In this case, we will select the *Database Table Source* object from *Toolbox > Sources > Database Table Source* and configure it so that it reads data from the *Customers* dataset.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FAU14x8cb2HZrnsqCJ1kj%2F01-database-table-source.PNG?alt=media\&token=6f49b681-d244-4dcf-bfd9-c9c72f08348c)

To learn how you can configure a *Database Table Source* object, click [here](https://documentation.astera.com/astera-data-stack-v10/dataflows/sources/database-table-source).

To preview the data, right-click on the object header and select *Preview Output* from the context menu. Here, you can see that there is a field that contains each customers’ country of residence.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FugZylCECYsFUgGQyJecW%2F02-customers-data-preview.PNG?alt=media\&token=76cd9e0d-a09f-45ee-a044-4a6036167012)

2. Drag the *Database Lookup* object from *Toolbox > Transformations > Database Lookup* and drop it onto the designer.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FI87tDohuDyOQxxdEWyne%2F03-drag-database-lookup.gif?alt=media\&token=cc648220-1a38-456c-bedf-f2e5bdc5bc5a)

3. Right-click on the header of the *Database Lookup* object and select *Properties* from the context menu.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FIRSJ7kdvZH7az61gKFqo%2F04-database-lookup-properties.PNG?alt=media\&token=abc1d648-e66f-4bd7-8878-d6b37fb3666f)

This will open a new window on your screen.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FgOVfBA5pXR4oXeAj9S0E%2F05-database-connection.PNG?alt=media\&token=816bc336-acbd-4110-8040-6921bf9de8c2)

Here, you are required to configure the properties for the *Database Lookup* object.

4. On the *Database Connection* window, enter the details for the database you wish to connect to.

* Use the *Data Provider* drop-down list to specify which database provider you wish to connect to. The required credentials will vary according to your chosen provider.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2F2df9sFaMvItg7d3vE8ud%2F06-database-connection.PNG?alt=media\&token=9307bf74-e1cd-4479-98fd-b0005f4f7830)

* Provide the required credentials. Alternatively, use the *Recently Used* drop-down list to connect to a recently connected database.
* *Test Connection* to ensure that you have successfully connected to the database. A new window will open, showing whether your test is successful or has ended in an error. When the connection has been successfully established, close it by clicking *OK*, and then click *Next*.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FCnenWGOZJKXZii649wJr%2F07-database-connection.PNG?alt=media\&token=d16648c7-c912-4813-aa28-29923af051e8)

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FpGhozSXLkxPFcp34anCL%2F08-test-connection.PNG?alt=media\&token=9d1486b8-20b7-4043-8ffb-8c5c42dc15fd)

5. The next window is the *Database Lookup Map Properties* window. Here, you can pick a table from the database that you have connected to.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FLfYo6eeHeh2THxGwI1Gt%2F09-database-lookup-map-properties.PNG?alt=media\&token=4fdef890-79a6-4c13-8434-cc81695e7b00)

In this case, we will select the table named *CountryCodeLookup*. This table contains the code for each country and will serve as the lookup table in our use case.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FpTaE6L4J3UVBrqX1tIDk%2F10-database-lookup-map-properties.PNG?alt=media\&token=d5bf9531-bf83-4d26-a72c-f42cfa5b9ba0)

In the text box provided under the *Pick Table* option, you can enter a *where clause* to modify the lookup query. In this case, we will leave it empty.

Once you have chosen a table, click *Next*.

6. On the next window, you can choose a *Lookup Cache Type* from the following options:

*No Caching*: No data will be stored in a cache. This option is selected by default.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FjLOkq3RzPzR7tOQJ3dyc%2F11-lookup-caching-options.PNG?alt=media\&token=34c4bb99-607b-488b-a78b-0f5fe878bedd)

*Static*: The lookup values are stored in a cache. Once the cache is created, the transformation object will always query the cache instead of the lookup table. When you select this option, the following sub-options are enabled:

* *Fill Cache With All Lookup Values at Start*: Fills the cache with all of the lookup values at the start and continues to use this cache for every lookup.
* *Cache After First Use*: Uses the database table for the first lookup and fills the cache right after it is done. This cache is then used for every subsequent lookup. Checking this option enables another sub-option:
  * *Cache Commit Count*: Defines the number of records collected per cache chunk before they are committed to the cache.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FYPmvtxIn3pFvp65EVajf%2F12-lookup-caching-options.PNG?alt=media\&token=34a1e670-0de7-472a-a372-347f321b4a3f)

*Dynamic*: The lookup values are stored in a temporary cache file, which is deleted once the dataflow has been executed. When you select this option, the following sub-options are enabled:

* *Fill Cache With All Lookup Values at Start*: Fills the cache with all of the lookup values at the start and continues to use this cache for every lookup.
* *Cache After First Use*: Uses the database table for the first lookup and fills the cache right after it is done. This cache is then used for every subsequent lookup. Checking this option enables other sub-options:
  * *Cache Commit Count*: Defines the number of records collected per cache chunk before they are committed to the cache.
  * *Cache Key Column*: Defines a matching key field to check whether a record already exists in the cache.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FrE5Prd7fBZQU4mIiT6TO%2F13-lookup-caching-options.PNG?alt=media\&token=8b8320a5-2763-434a-b28c-a7de29ed8851)

*Persistent*: Saves the lookup values in a cache file that can be reused for future lookups. When you choose this option, the following sub-options are enabled:

* *Rebuild Persistent Cache on Next Run*: Checking this option will allow the contents of the cache file to be modified after every run.
* *Cache File Name*: Here, you can enter a name for your cache file.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2F9rrt6hjdzwUmRGqEUGYZ%2F14-lookup-caching-options.PNG?alt=media\&token=1d103b9b-665f-4afb-8908-c35d5341b9a4)

In this case, we will select the *No Caching* option. Once you are done, click *Next*.

7. On the *Lookup Options* window, you can choose between multiple lookup options.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FR6FBjUWC171egDn0UvLu%2F15-lookup-options.PNG?alt=media\&token=73a02b4d-dffb-4bd9-b9dd-44f5ef1326f1)

This page provides a set of options for different scenarios that could be faced during a lookup.

*If Multiple Values Are Found*

*Multiple Matches Found Option*\*\*:\*\* This option provides the flexibility to choose the output value if more than one matches are found for a single value in the lookup table. You can select one out of the three options that appear in the drop-down list:

* *Return First*: Returns the first matched value.
* *Return Last*: Returns the last value among all matched values.
* *Return All*: Returns all matched values.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2Fbk3uLsmZ8OHxDhh4KYGZ%2F16-lookup-options.PNG?alt=media\&token=979bee56-02a2-42ed-a712-548b76fb401a)

*If Value Is Not Found In the Lookup List*

If no lookup values are found for a source value, you can choose from the following options to be appended with the output:

* *No Message*: The output value will be the same as the input value and no message will appear with it.
* *Add Error*: An error message will appear with the output.
* *Add Warning*: A warning message will appear with the output.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2F17I9uQwrrMAprPaWF74N%2F17-lookup-options.PNG?alt=media\&token=5841c329-534d-4c22-95ca-ff5aea17ec3f)

*If Value Is Not Found in the Lookup List, Assign Value*

If no lookup value is found for a source value, you can assign an output value of your choice.

* *Assign Source Value*: Returns the source value in the output.
* *Assign Null*: Returns null in the output.
* *This Value*: Allows you to enter any value that will be returned in the output.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FEDyHHaA091JC1AnKMWnv%2F18-lookup-options.PNG?alt=media\&token=a3bd4646-92e8-4a1c-9b71-604e9a6718c9)

In this case, there is only one code for each country. Therefore, we will choose *Return First* from the drop-down list in the *Multiple Matches Found Option*. Moreover, we will leave the other options at their default selection i.e. *No Message* under *If Value Is Not Found in the Lookup List*, and *Assign Null* under *If Value Is Not Found, Assign Value*.

Once you are done choosing the options, click *Next*.

8. On the *Config Parameters* window, you can define certain parameters for the *Database Lookup* object.

These parameters facilitate an easier deployment of flows by excluding hardcoded values and providing a more convenient method of configuration. If left blank, they will assume the default values that were initially assigned to them.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2Ft4sUnXY9hwWbpzAi8IHD%2F19-database-lookup-config-parameters.PNG?alt=media\&token=69081621-6ad8-4cf6-831f-86bde44a0496)

In this case, we will leave them blank. Click *Next*.

9. On the last window, which is the *General Options* window, you will be provided with a text box to add *Comments*. The *General Options* in this window have been disabled.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2F7t2MnCrDubHGxqLOWPfD%2F20-general-options.PNG?alt=media\&token=be4a99a4-6253-45ec-875c-cb35e0d83492)

You are now done configuring the *Database Lookup* object. Click *OK* to close the configuration window.

10. Expand the *Database Lookup* object to view the layout of the lookup table. In this case, it contains two fields, *Country* and *Code*. The former contains the full name of each country and the latter contains each country’s code.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FdZ3Gb5sYIvS0YHJqCj04%2F21-database-lookup-object.PNG?alt=media\&token=97e51c0b-d904-4cff-8137-0cbd7953a0ca)

11. Map the Country field from the *Database Table Source* object to its counterpart in the *Database Lookup* object.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FqGkunRqk1U0zkQG2jJVA%2F22-database-lookup-mapping.PNG?alt=media\&token=63fe4d70-448b-4bbf-8e1e-0807c9ab4739)

12. Drag an *Excel Workbook Destination* object from *Toolbox > Destinations > Excel Workbook Destination* and drop it onto the designer. Configure the object by providing a name and the path to the directory and folder where you want to save your destination file.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2F75PS0ZLdDY7nVRVx82nl%2F23-database-lookup-mapping.PNG?alt=media\&token=769e94c0-a95d-4f21-b3cd-b74224276921)

To learn how you can configure an *Excel Workbook Destination* object, click [here](https://documentation.astera.com/astera-data-stack-v10/dataflows/destinations/excel-workbook-destination).

13. Auto-map the source dataset to the destination object.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FpuAzG0zcvuWYAfXA4E00%2F24-database-lookup-mapping.PNG?alt=media\&token=d648e70a-c519-4baa-bdf4-dbaacc43dc2f)

14. Delete the mapping link between the *Country* fields in the source and destination. To do this, right-click on the mapping link and select *Delete* from the context menu.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FT1Rqq2kA8dJcXt7NkB34%2F25-database-lookup-mapping.PNG?alt=media\&token=72d3331a-5886-4e60-a4d9-324cc330db2d)

15. Map the *Code* field from the *Database Lookup* object to the *Country* field in the destination object. This is what the final dataflow should look like:

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2Fbi8QR1kRPHiPuQjHi6RE%2F26-database-lookup-dataflow.PNG?alt=media\&token=a20b2293-d0a9-468a-9e67-31bd51c51e78)

16. Right-click on the destination object’s header and select *Preview Output* from the context menu.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2F7yvylUN4oYP1UOSIHD5y%2F27-preview-output.PNG?alt=media\&token=aa4630e1-c7e4-4948-84fe-5001bab3ca61)

In the *Data Preview* window, you will see that each county name has been replaced by its corresponding code.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FKKIfK2QNtXlnSTix43ZU%2F28-data-preview.PNG?alt=media\&token=e19ddfe7-48ad-4008-8d6c-bdf757d1b5d0)

This concludes using the *Database Lookup* Transformation object in Astera.