---
title: Load Data from External Sources in Data Pipelines
slug: SqE5-load-data-from
description: The Data Pipeline functionality allows you to look up and load data from external sources into the Ocient System. Use the LOOKUP keyword and the LOOKUP function in the CREATE PIPELINE SQL statement to look up data in external source tables. 
docTags: 
createdAt: Wed Aug 06 2025 20:52:58 GMT+0000 (Coordinated Universal Time)
updatedAt: Mon Mar 30 2026 18:52:28 GMT+0000 (Coordinated Universal Time)
---

The data pipeline functionality in the Ocient® System enables you to load data from external sources, such as other databases, using the `LOOKUP` function and the `LOOKUP` keyword of the corresponding `CREATE PIPELINE` SQL statement. You must use the function and the `LOOKUP` keyword together to join data from an external source.

You can use the `LOOKUP` function with multiple external sources by specifying each source with its `LOOKUP` function and corresponding `LOOKUP` keyword syntax.

**Syntax**

The `LOOKUP` function returns a value from an external source table based on a join between a specified value and another column in the source table. The function creates and executes this SQL statement from the specified function arguments.&#x20;

```sql
SELECT return_column_name 
FROM lookup_source_name 
WHERE value = join_column_name;
```

The source table should only have unique values for the `join_column_name` column for the join operation.

```sql
LOOKUP(lookup_source_name, value, join_column_name, return_column_name)
```

| **Argument**         | **Data** **Type**                                                                                                | **Description**                                                                                         |
| -------------------- | ---------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------- |
| `lookup_source_name` | VARCHAR                                                                                                          | The name of the external source defined by the `LOOKUP` keyword in the `CREATE PIPELINE` SQL statement. |
| `value`              | VARCHAR, BOOLEAN, BYTE, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, DATE, TIME, TIMESTAMP, HASH, BINARY, DECIMAL, UUID | The value for the lookup.                                                                               |
| `join_column_name`   | VARCHAR                                                                                                          | The name of a column in the external source table for the join operation.                               |
| `return_column_name` | VARCHAR, BOOLEAN, BYTE, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, DATE, TIME, TIMESTAMP, HASH, BINARY, DECIMAL, UUID | The name of the column in the external source table that contains the data to load.                     |

:::hint{type="info"}
If the data type of the returned value of the `LOOKUP` function is not one of the data types specified for the `return_column_name` argument, then the function transforms the value to the nearest compatible type, typically `VARCHAR`.
:::

**Example**

Create a data pipeline `lookup_data_pipeline` that loads data from the CSV file `data.csv`. Use an external source `existing_all_types` with the table `tablename` in the schema `schemaname` using a JDBC connection with the connection string: `jdbc:host://111.1.1.1:4200/databasename;user=username@databasename;password=testpassword`. For your connection string, substitute these variables with the values specific to your database and credentials:

- `host` — Hostname
- `111.1.1.1` — IP address&#x20;
- `4200` — Port number
- `databasename` — Database name
- `username` — Username
- `testpassword` — Password

Look up data in the `col_binary` column of the `tablename` table by joining the second column of the CSV file to the `col_bigint` column in the `tablename` table. Load the data in the `col_binary` column based on the result of the `LOOKUP` function.

```sql
CREATE PIPELINE lookup_data_pipeline
SOURCE FILESYSTEM
    FILTER '/tmp/folder/data.csv'
LOOKUP existing_all_types
    CONNECTION_TYPE 'jdbc'
    CONNECTION_STRING 'jdbc:host://111.1.1.1:4200/databasename;user=username@databasename;password=testpassword'
    LOOKUP_SCHEMA 'schemaname'
    LOOKUP_TABLE 'tablename'
EXTRACT
    FORMAT CSV
INTO public.destination_table
SELECT $1 AS col_pk,
    LOOKUP('existing_all_types', $2, 'col_bigint', 'col_binary') AS col_binary;
```

## Performance Considerations

The performance of the lookup operation depends on the size of the external source. If the external source is large, the lookup operation is slower. The Ocient System recommends keeping the size to less than 5 million rows for the total size of all external source tables used by actively running data pipelines in the system.

## Related Links

[Load Data](<././Load Data.md>)

[CREATE PIPELINE](<././SQL Reference/Data Definition Language _DDL_ Statement Reference/Data Pipelines.md>)&#x20;

[Transform Data in Data Pipelines](<./Transform Data in Data Pipelines.md>)&#x20;

