Load Data from External Sources in Data Pipelines

The data pipeline functionality in the 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.

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.

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
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. Load Data CREATE PIPELINE Transform Data in Data Pipelines

​Performance Considerations

​Related Links

Performance Considerations

Related Links