Agent: export

Pulls data using SQL and exports it into target table or temporary file.

Syntax and samples

 - do: export
   file: input.sql
   cols:
    - id
    - some_field
   pkcols:
    - id
   changedetection: ts
   ts_col: modified_ts
   to: direct

Structure

Element	Required	Purpose
do	yes	Agent name: export or eksport
file	yes	SQL file containing SELECT
cols	for to: direct	Target column names in the same order as columns in SELECT
pkcols	for to: direct	Columns used by target table alternative key
changedetection	no	Change detection method. Supported value: ts
ts_col	if changedetection is ts	Column containing change timestamp
to	no	Destination: direct or file. Default is file

file can be string or array of strings, but only first file is used.

cols must be ordered according to SELECT.

Direct export

to: direct inserts rows straight into target table.

If row with the same alternative key already exists, it will be updated.

 - do: export
   file: input.sql
   cols:
    - id
    - name
    - active
   pkcols:
    - id
   to: direct

-- input.sql
SELECT id, name, active
FROM source_schema.source_table

Export through temporary file

to: file saves data into PostgreSQL-importable temporary .dat file. File can be consumed by next action during the same job.

 - do: export
   file: input.sql
   to: file

file is default destination and can be omitted:

 - do: export
   file: input.sql

If SELECT returns no rows, empty temporary file is created.

Change detection by timestamp

Use changedetection: ts together with ts_col.

 - do: export
   file: input.sql
   cols:
    - id
    - name
    - modified_ts
   pkcols:
    - id
   changedetection: ts
   ts_col: modified_ts
   to: direct

-- input.sql
SELECT id, name, modified_ts
FROM source_schema.source_table
WHERE modified_ts > '{{last_value_ts}}'::timestamp
ORDER BY modified_ts

Rows must be ordered by change timestamp, older first.

Timestamp column can be:

• included in cols
• extra unnamed column in SELECT after all columns listed in cols

In second case timestamp is used for change detection but is not inserted into target table.

Last saved timestamp is stored after every row. Export stops when data is near current time.

Available placeholders

SQL file can use:

Placeholder	Value
{{target_schema}}	Target schema from task id
{{target_table}}	Target table from task id
{{source_column_ts}}	Value of ts_col
{{last_value_ts}}	Last saved timestamp

Timestamp placeholders are intended for changedetection: ts.

Remarks

• use to: direct for immediate insert/update into target table
• use to: file only when next action needs temporary data file
• pkcols must match columns of constraint ak_<target_table>_4_uniq
• cols order must match SELECT column order
• only first SQL file is used if multiple files are defined