Transform Rule File¶
Transform rule file is an additional configuration file written in JSON format that describes several transform rules that a SynchDB connector should follow when receiving a table data from remote heterogeneous database. This file shall be put under $PGDATA
directory and is selected when a connector is created using synchdb_add_conninfo()
SQL function. It is possible not to use a custom transform rule file when creating a new connector, in this case, default transfom rules will be applied.
Sample rule file¶
{
"transform_datatype_rules": [
{
"translate_from": "GEOMETRY",
"translate_from_autoinc": false,
"translate_to": "TEXT",
"translate_to_size": -1
},
{
"translate_from": "POINT",
"translate_from_autoinc": false,
"translate_to": "TEXT",
"translate_to_size": -1
},
{
"translate_from": "inventory.geom.g.GEOMETRY",
"translate_from_autoinc": false,
"translate_to": "GEOMETRY",
"translate_to_size": 0
},
{
"translate_from": "inventory.orders.quantity.INT",
"translate_from_autoinc": false,
"translate_to": "BIGINT",
"translate_to_size": 0
}
],
"transform_objectname_rules": [
{
"object_type": "table",
"source_object": "inventory.orders",
"destination_object": "schema1.orders"
},
{
"object_type": "table",
"source_object": "inventory.products",
"destination_object": "products"
},
{
"object_type": "column",
"source_object": "inventory.orders.order_number",
"destination_object": "ididid"
},
{
"object_type": "column",
"source_object": "inventory.orders.purchaser",
"destination_object": "the_dude"
},
{
"object_type": "column",
"source_object": "inventory.orders.quantity",
"destination_object": "the_numba"
},
{
"object_type": "column",
"source_object": "testDB.dbo.customers.first_name",
"destination_object": "the_awesome_first_name"
}
],
"transform_expression_rules": [
{
"transform_from": "inventory.orders.quantity",
"transform_expression": "case when %d < 500 then 0 else %d end"
},
{
"transform_from": "inventory.geom.g",
"transform_expression": "ST_SetSRID(ST_GeomFromWKB(decode('%w', 'base64')),%s)"
},
{
"transform_from": "inventory.products.name",
"transform_expression": "'>>>>>' || '%d' || '<<<<<'"
},
{
"transform_from": "inventory.products.description",
"transform_expression": "'>>>>>' || '%d' || '<<<<<'"
}
],
"ssl_rules":
{
"ssl_mode": "disabled",
"ssl_keystore": null,
"ssl_keystore_pass": null,
"ssl_truststore": null,
"ssl_truststore_pass": null
}
}
Transform Data Type Rules¶
Transform data type rules influence how SynchDB maps a data type from source heterogeneous database to an equivalent data type in PostgreSQL These rules can be written to apply to all source tables or only selected tables. If a data type rule is not available for a particular data type, SynchDB will use the default data type mapping rules instead.
Custom data type transform rules can be defined with a JSON array with name "transform_datatype_rules"
and each element in the array must contain the following objects:
Description | Example | |
---|---|---|
translate_from | the data type name or Fully Qualified Name (FQN) from heterogeneous database to transform from. The FQN consists of: [database].[schema (if present)].[table name].[column name].[data type]:
|
GEOMETRY POINT inventory.geom.g.GEOMETRY inventory.orders.quantity.INT |
translate_from_autoinc | indicate if the data type specified in "translate_from" is marked as auto increment. |
false true |
translate_to | the PostgreSQL data type to translate to. See below for list of supported PostgreSQL data types. It is possible to specify data types not natively supported by PostgreSQL, for example GEOMETRY, please ensure that the data type has already been installed before starting a connector using it. |
TEXT VARCHAR BIGINT GEOMETRY |
translate_to_size | indicate if we should transform the size of the data type specifed in "translate_to" :
|
0 -1 45 7000 |
Supported PostgreSQL Data Types¶
SynchDB supports the following native PostgreSQL data types that can be transformed to during the table creation. However, it is possible to transform a foreign data type to PostgreSQL data type not listed below. For example, the GEOMETRY
data type added by Postgis. In this case, the table is still created with GEOMETRY
data type, but the data that SynchDB receives will be formatted and inserted as TEXT
. It is up to you to decide if this data needs to be processed by an expression or SQL function (see transform expression rules below) before it can be applied to PostgreSQL.
- BOOLEAN (BOOLOID)
- BIGINT (INT8OID)
- SMALLINT (INT2OID)
- INT (INT4OID)
- INTEGER (INT4OID)
- DOUBLE PRECISION (FLOAT8OID)
- REAL (FLOAT4OID)
- MONEY (MONEYOID)
- NUMERIC (NUMERICOID)
- CHAR (BPCHAROID)
- CHARACTER (BPCHAROID)
- TEXT (TEXTOID)
- VARCHAR (VARCHAROID)
- CHARACTER VARYING (VARCHAROID)
- TIMESTAMPTZ (TIMESTAMPTZOID)
- JSONB (JSONBOID)
- UUID (UUIDOID)
- VARBIT (VARBITOID)
- BIT VARYING (VARBITOID)
- BIT (BITOID)
- DATE (DATEOID)
- TIMESTAMP (TIMESTAMPOID)
- TIME (TIMEOID)
- BYTEA (BYTEAOID)
Transform Object Name Rules¶
Transform object name rules influence how SynchDB maps a source table or column name from heterogeneous database to PostgreSQL table or column names.
Transform data type rules can be defined with a JSON array with name `"transform_objectname_rules" and each element in the array must contain the following objects:
description | example | |
---|---|---|
object type | the object type of this transformation element. It can be:
|
table column |
source_object | The Fully Qualified Name (FQN) of the source object from heterogeneous database.
|
false true |
destination_object | The name to be used in PostgreSQL to represent the source_object from heterogeneous database:
|
myschema1.mytable1 mytable2 the_dude the_numba |
If a table or column name does not have a matching transform object name rules, the default transform will be automatically applied as below:
Remote object FQN | Default object name transform rule on PostgreSQL side |
---|---|
[database].[table] |
|
[database].[schema].[table] |
|
Transform Expression Rules¶
Transform expression rules indicate to SynchDB if it needs to perfrom additional "expressions" on the received data before applying them to PostgreSQL. This feature allows SynchDB to changes the representation of data without additional application logics on PostgreSQL site.
Transform data type rules can be defined with a JSON array with name "transform_expression_rules"
and each element in the array must contain the following objects:
Description | Example | |
---|---|---|
transform_from | the Fully Qualified Name (FQN) of a remote column that can be one of these formats:
|
inventory.orders.quantity testDB.dbo.products.description |
transform_expression | the expression to execute on the received data. You can use these placeholder tokens to construct an expression:
the expression can be written in any standard SQL syntax supported by PostgreSQL. |
1. case when %d < 500 then 0 else %d end sets a value to 0 if it's less than 500, otherwise keeps its original value 2. ST_SetSRID(ST_GeomFromWKB(decode('%w', 'base64')),%s) Converts base64-encoded Well-Known Binary (WKB) geometry data to a PostGIS geometry object with a specified spatial reference system (SRID) 3. '>>>>>' \|\| '%d' \|\| '<<<<<' adds visual markers around a value |
SSL Rules¶
If SSL is required to establish connection to a remote database, this section is required.
Field | Description |
---|---|
ssl_mode | can be one of:
|
ssl_keystore | path to the keystore file |
ssl_keystore_pass | password to access the keystore file |
ssl_truststore | path to the truststore file |
ssl_truststore_pass | password to access the truststore file |