Computed Fields

Computed fields allow you to extend the source table and stage table with additional settings.

For example, you use the computed fields to set the partial full load field when set partial full load mode in CSV Downloader or SQL Downloader. For more information, see ‘Set partial full load mode for the entities’ in CSV Downloader or SQL Downloader, correspondingly.

Structure and Parameters

The ‘computed_field’ section contains an array of parameters.

"computed_fields": [
  {
    "name": "custom_field_name",
    "type": "field_data_type",
    "table_type": ["src|stg"],
    "function": "function_name",
    "definition": "function_definition",
    "key": "true|false", // optional
    "position": "last|first", // optional
    "encoding": "encoding_type" // optional
  }
]
NameTypeMandatory?DefaultDescription
namestringyesn/a

The name of the custom field in the database

typestringyesn/a

The type of the custom field

Possible values:

  • varchar(X): string-X
  • varchar(255): string
  • integer: integer
  • decimal(X,Y): decimal-X-Y
  • decimal(16,10): decimal-16-10
  • boolean: boolean
  • date (without time): date-false
  • date (with time): date-true
  • time (without date): time
table_typearrayyesn/a

The type of the table (stage or source) where the custom field will be created

Possible values:

  • stg
  • src
functionstringyesn/a

The function that will be used for filling in the custom field

Possible values for the source table ( "table_type": ["src"] ):

  • timestamp: The field is filled in with the timestamp value.
  • now: The field is filled in by the now() function.
  • custom_function: The field is filled in by the SQL command specified in the 'definition' parameter.
  • runtime_metadata|value: The field is filled in by the runtime metadata parameter.  value specifies the name of the runtime metadata that can be used. For example, non-standard fields in the manifest files downloaded by CSV Downloader are copied to 'runtime_metadata' so you can use them here.  

Possible values for the stage table ( "table_type": ["stg"] ):

  • value|value_name: The field is filled in from the source table field that is specified by value_name.  Example: value|_sys_is_deleted copies the value of the '_sys_is_deleted' field from the source table to the stage table.
  • updated_at: The field is filled in by the now() function.
definitionstringsee 'Description'n/a(Only when 'function' is set to 'custom_function') The definition of the function
keyBooleannofalse

(Only when 'function' is set to 'runtime_metadata|value' or 'value|value_name') Specifies whether the custom field will be used as part of the primary key

  • If not set or set to 'false', the custom field will not be used as part of the primary key.
  • If set to 'true', the custom field will be used as part of the primary key.
positionstringnolast

(Only when 'key' is set to 'true') Specifies whether the custom field should be on the first or the last position in the primary key.

Possible values:

  • last
  • first

TIP: If you are using the computed fields for setting partial full load mode (see 'Set partial full load mode for the entities' in CSV Downloader or SQL Downloader), use the 'position' parameter together with the 'encoding' parameter (see below in this table) for better performance. Set the 'position' parameter to 'first', and set the 'encoding' parameter to 'RLE'.

encodingstringnoAUTO

The encoding type of fields

For information about available options, see Encoding Types in Vertica.

TIP: If you are using the computed fields for setting partial full load mode (see 'Set partial full load mode for the entities' in CSV Downloader or SQL Downloader), use the 'encoding' parameter together with the 'position' parameter (see above in this table) for better performance. Set the 'encoding' parameter to 'RLE', and set the 'position' parameter to 'first'.

Examples

"computed_fields": [
  {
    "name": "field_extract",
    "type": "string-255",
    "function": "custom_function",
    "table_type": ["src"],
    "definition": "LEFT(\"field1\",5)"
  }
]

 

Location

You can place the ‘computed_fileds’ section in either of the following locations in the configuration file:

  • In the ‘custom’ section for a specific entity (if you want to apply it only to this entity):

    "entities": {
  "table_1_name": {
    "global": {
      "custom": {
        "hub": ["column_1_name","column_2_name","column_3_name"],
        "partial_full_load_field": "custom_field_name", // optional
        "computed_fields": [
          {
            "name": "custom_field_name",
            "type": "field_data_type",
            "table_type": ["src|stg"],
            "function": "function_name",
            "definition": "function_definition",
            "key": "true|false", // optional
            "position": "last|first", // optional
            "encoding": "encoding_type" // optional
          }
        ]
      }
    }
  }
}

  • In the ‘ads_storage’ section (if you want to apply it to all processed entities):

    "ads_storage": {
  "instance_id": "data_warehouse_instance_id",
  "username": "dw_email@address.com",
  "options": {},
  "computed_fields": [
    {
      "name": "custom_field_name",
      "type": "field_data_type",
      "table_type": ["src|stg"],
      "function": "function_name",
      "definition": "function_definition",
      "key": "true|false", // optional
      "position": "last|first", // optional
      "encoding": "encoding_type" // optional
    }
  ]
}