You are viewing our older product's guide. Click here for the documentation of GoodData Cloud, our latest and most advanced product.

Feed File

A feed file describes the structure of source data files and data types in their columns. Once you created and uploaded the feed file, keep it in the folder and do not change it until the structure of the source files changes (for example, a column is added to a source file).

You can have only one feed file.

File Structure

The feed file is a text file delimited with vertical bars ( | ). The whole content of the feed file is case-sensitive.

The feed file has the following columns:

Name	Mandatory?	Description
file	yes	The name of the entity which the row describes
version	yes	The version of the entity Use 'version' to version entities in the source files if you want to process the files in different structures. You can put multiple versions of the same entity, and you can keep the older version of the entity in the feed file to allow smooth transition between versions. The version can have one decimal place. Example: `1.0` `3.23`
field	yes	The name of the column It can differ from the column name in the header of the CSV file because the header of the CSV file is ignored.
type	yes	The data type of the field For the supported types, see Data Types.
order	yes	The order in which the fields are sorted in the source files (from the lowest to the highest number; the first column has 0)
date_format	no	The format of dates in data Example: `'DD.MM.YYYY'` For more information, see Template Patterns for Date/Time Formatting in Vertica.
encoding	no	The encoding type of fields For information about available options, see Encoding Types in Vertica.
xpath	see "Description"	(Only for JSON source files; see "Use JSON files as source files" in CSV Downloader) The XPath notation that defines the rules for converting the JSON data that was processed and integrated into the flex tables (see "Flex Table" in ADS Integrator) to the format that allows ADS Integrator to copy this data to the source tables for further processing For the supported notations, XPath Notations.

Do not change field names after the first run. If you change a field name, CSV Downloader treats this as if the field has been removed, and adds a new field. This may result into data inconsistency in the database (data being not in the same columns).
Do not change the order of the fields. Changing the order of the fields in metadata may result in CSV Downloader not working correctly.

Data Types

The following field data types are supported:

SQL type representation	Feed file type name
`varchar(X)`	`varchar(X)`
`varchar(255)`	`varchar`
`decimal(16,10)`	`decimal`
`decimal(X,Y)`	`decimal(X,Y)`
`boolean`	`boolean`
`integer`	`integer`
`date (with time)`	`datetime`
`date (without time)`	`date`
`time (without date)`	`time`
`timestamp (without timezone)`	`timestamp without timezone`
`array[int]`	`array[int]` *
`array[varchar]`	`array[varchar]` *
`array[varchar(X)]`	`array[varchar(X)]` *

* The database parser must be the Vertica fcsvparser parser with type set to either traditional or rfc4180 (see “Describe the structure of the source CSV files” in CSV Downloader):

"db_parser": "fcsvparser-traditional"

"db_parser": "fcsvparser"

XPath Notations

Use the XPath notations only when your source files are JSON files (uncompressed or GZIPed). For more information, see “Use JSON files as source files” in CSV Downloader.

Some XPath notations are supported only for the Vertica fjsonparser parser without flattening the nested data (fjsonparser(flatten_maps=false)), some are supported only for the parser with flattening the nested data (fjsonparser or fjsonparser(flatten_maps=true)), and some are supported for both.

The following XPath notations are supported for both fjsonparser and fjsonparser(flatten_maps=false):

XPath notation Description JSON example XPath example

XPath notation	Description	JSON example	XPath example
`.[]`	A JSON array with values of the primitive JSON data types Only one notation is supported for one JSON entity.	`"devices": [ "Projector", "Table" ]`	`devices.[]`
`.[](index)`	An index field with the index of a JSON array with values of the primitive JSON data types Only one notation is supported for one JSON entity. The start index is 0.	`"devices": [ "Projector", "Table" ]`	`devices.[](index)`

.[]

A JSON array with values of the primitive JSON data types

Only one notation is supported for one JSON entity.

"devices":
  [
    "Projector",
    "Table"
  ]

devices.[]

.[](index)

An index field with the index of a JSON array with values of the primitive JSON data types

Only one notation is supported for one JSON entity. The start index is 0.

"devices":
  [
    "Projector",
    "Table"
  ]

devices.[](index)

The following XPath notations are supported only for fjsonparser:

XPath notation Description JSON example XPath example

XPath notation	Description	JSON example	XPath example
`.[].x`	A JSON array with values of the compound JSON data types where `x` is the key of the array Multiple notations are supported for one JSON entity.	`"members": [ { "level": "engineering", "quantity": "10" }, { "level": "manager", "quantity": "4" } ]`	`members.[].level`
`..key` `..value`	Nested JSON fields that you want to split into pairs of keys and values Only one key/value pair is supported for one JSON entity.	`"buildInfo": { "company": "Acme" }`	`buildInfo.*.key`
`.x`	A JSON object with multiple fields where `x` is the key of the JSON object Multiple notations are supported for one JSON entity.	`"visits": { "visitors": 40050, "international": false, "company": "Acme" }`	`visits.visitors`

.[].x

A JSON array with values of the compound JSON data types where x is the key of the array

Multiple notations are supported for one JSON entity.

"members":
  [
    {
      "level": "engineering",
      "quantity": "10"
    },
    {
      "level": "manager",
      "quantity": "4"
    }
  ]

members.[].level

.*.key .*.value

Nested JSON fields that you want to split into pairs of keys and values

Only one key/value pair is supported for one JSON entity.

"buildInfo":
  {
    "company": "Acme"
  }

buildInfo.*.key

.x

A JSON object with multiple fields where x is the key of the JSON object

Multiple notations are supported for one JSON entity.

"visits":
  {
    "visitors": 40050,
    "international": false,
    "company": "Acme"
  }

visits.visitors

The following XPath notations are supported only for fjsonparser(flatten_maps=false):

XPath notation Description JSON example XPath

XPath notation	Description	JSON example	XPath
`.['x']::type`	A JSON object with multiple fields where `x` is the key of the JSON object and `type` is the same data type that is used in the `type` column for this row (see Data Types) Multiple notations are supported for one JSON entity.	`"visits": { "visitors": 40050, "international": false, "company": "Acme" }`	`visits.['visitors']::varchar(255)`

.['x']::type

A JSON object with multiple fields where x is the key of the JSON object and type is the same data type that is used in the type column for this row (see Data Types)

Multiple notations are supported for one JSON entity.

"visits":
  {
    "visitors": 40050,
    "international": false,
    "company": "Acme"
  }

visits.['visitors']::varchar(255)

Notice that for some XPath notations, only one notation of this type can be used in a JSON entity (data extracted from a source JSON file and stored in a flex table). That means that if you have a JSON entity with two JSON arrays with “primitive” values and you want to use the .[] notation, you must choose only one JSON array to process and copy to the source table. The JSON array that you chose should be defined in the feed file.

If the feed file contains the XPath notations .[], .[](index) and .[].x, make sure to place the lines with the .[] and .[](index) notations before the lines with the .[].x notations. For example:

file|version|field|type|order|xpath
...
Departments|1.1|device|string-255|1|devices.[]
...
Departments|1.1|device_index|string-255|5|devices.[](index)
...
Departments|1.1|member_level|string-255|8|members.[].level
Departments|1.1|member_quantity|string-255|9|members.[].quantity

This ensures that ADS Integrator generates a correct SQL query to copy the processed data from the flex tables to the source tables.

The order of the lines with the .[] and .[](index) XPath notations within the feed file is not important. The order of the lines with the .[].x notation within the feed file is not important either.

Here are some examples of how the feed file may look like depending on whether the nested data is flattened and how the JSON data is structured.

Example 1: An entity containing a JSON object with multiple fields, individual key/value JSON fields, and two JSON arrays with “primitive” values; the nested data is flattened (fjsonparser(flatten_maps=true))

{
  "id": 1,
  "devices": [
    "Projector",
    "Table"
  ],
  "managers": [
    "Jane",
    "John"
  ],
  "date": "2017-05-01",
  "visits": {
    "visitors": 40050,
    "international": false,
    "company": "Acme"
  }
}

The feed file would look like the following (notice that only one JSON array, devices, is defined):

file|version|field|type|order|xpath
Departments|1.1|id|string-255|0|
Departments|1.1|devices|string-255|1|devices.[]
Departments|1.1|date|date-false|2|
Departments|1.1|visitors|string-255|3|visits.visitors
Departments|1.1|international|string-255|4|visits.international
Departments|1.1|company|string-255|5|visits.company

Example 2: An entity containing a JSON object with multiple fields, an individual key/value JSON field, JSON arrays (one with “primitive” values, one with “compound” values), and nested JSON fields; the nested data is flattened (fjsonparser(flatten_maps=true))

{
  "id": "Departament01",
  "room": {
    "code": "EU",
    "map": [
      {
        "location": "Europe"
      }
    ]
  },
  "devices": [
    "Projector",
    "Table"
  ],
  "members": [
    {
      "level": "engineering",
      "quantity": "10"
    },
    {
      "level": "manager",
      "quantity": "4"
    }
  ],
  "buildInfo": {
    "company": "Acme"
  },
  "goalSuccess": {
    "yearly":"100%"
  }
}

The feed file would look like the following:

file|version|field|type|order|xpath
Departments|1.1|id|string-255|0|id
Departments|1.1|device|string-255|1|devices.[]
Departments|1.1|device_index|string-255|2|devices.[](index)
Departments|1.1|room_code|string-255|3|room.code
Departments|1.1|room_location|string-255|4|room.map.[].location
Departments|1.1|member_level|string-255|5|members.[].level
Departments|1.1|member_quantity|string-255|6|members.[].quantity
Departments|1.1|build_info_key|string-255|7|buildInfo.*.key
Departments|1.1|build_info_value|string-255|8|buildInfo.*.value
Departments|1.1|goal_yearly|string-255|9|goalSuccess.yearly

Example 3: An entity containing a JSON object with multiple fields, individual key/value JSON fields, and two JSON arrays with “primitive” values; the nested data is not flattened (fjsonparser(flatten_maps=false))

{
  "id": 1,
  "devices": [
    "Projector",
    "Table"
  ],
  "managers": [
    "Jane",
    "John"
  ],
  "date": "2017-05-01",
  "visits": {
    "visitors": 40050,
    "international": false,
    "company": "Acme"
  }
}

The feed file would look like the following (notice that only one JSON array, devices, is defined):

file|version|field|type|order|xpath
Departments|1.1|id|string-255|0|
Departments|1.1|devices|string-255|1|device.[]
Departments|1.1|date|date-false|2|
Departments|1.1|visitors|varchar(128)|3|visits['visitors']::varchar(128)
Departments|1.1|international|varchar(128)|4|visits['international']::varchar(128)
Departments|1.1|company|varchar(128)|5|visits['company']::varchar(128)

File Name

The recommended name of the feed file is feed.txt.

File Examples

Example 1: A feed file with the mandatory columns only

file|version|field|type|order
Account|1.0|ID|integer|0
Account|1.0|Name|string-255|1
Account|1.0|Attribute1|string-255|2
Account|1.0|Attrubute2|string-255|3
Account|1.1|Name|string-255|1
Account|1.1|Attribute1|string-255|2
Account|1.1|Attrubute2|string-255|3
Account|1.1|Attribute3|string-255|4
Account|1.1|date1|datetime|5
Account|1.1|date2|timestamp without time zone|6
User|1.0|ID|integer|1
User|1.0|Name|string-255|2
User|1.0|Attribute1|string-255|3
Facts|1.0|ID|integer|1
Facts|1.0|account_id|integer|2
Facts|1.0|user_id|integer|3
Facts|1.0|fact1|decimal-20-5|4
Facts|1.2|ID|integer|1
Facts|1.2|account_id|integer|2
Facts|1.2|user_id|integer|3
Facts|1.2|fact1|decimal-20-5|4
Facts|1.2|fact2|decimal-20-5|5
Department|1.0|Name|string-255|1
Department|1.0|Number|array[int]|2
Department|1.0|Address|array[varchar]|3

Example 2: A feed file with the mandatory columns and two optional columns, encoding and date_format

file|version|field|type|order|encoding|date_format
Account|1.0|ID|integer|0||
Account|1.0|Name|string-255|1||
Account|1.0|Attribute1|string-255|2|RLE|
Account|1.0|date1|datetime|5||DD/MM/YYYY
Account|1.0|date2|timestamp without time zone|6||DD/MM/YYYY

Example 3: A feed file with the mandatory columns and one optional column, date_format

file|version|field|type|order|date_format
Account|1.0|ID|integer|0|
Account|1.0|Name|varchar(255)|1|
Account|1.0|City|varchar(255)|2|
Account|1.0|Note|varchar(255)|3|
Account|1.0|timeCreated|timestamp|4|YYYY-MM-DD HH24:MI:SS.MS
Account|1.0|dateOpened|date|5|YYYY-MM-DD
Account|1.0|dateClosed|timestamp|6|YYYY-MM-DD HH24:MI:SS.MS
User|1.0|ID|integer|0|
User|1.0|Name|varchar(255)|1|
User|1.0|Attribute1|varchar(255)|2|
Product|1.0|ID|integer|0|
Product|1.0|product_name|varchar(255)|1|
Product|1.0|type|varchar(255)|2|
Facts|1.0|ID|integer|0|
Facts|1.0|fact1|decimal(20,5)|1|
Facts|1.2|ID|integer|0|
Facts|1.2|fact1|decimal(20,5)|1|
Facts|1.2|product_id|integer|2|
Facts|1.2|fact2|decimal(20,5)|3|

Example 4: A feed file with the mandatory columns and the xpath column for processing a JSON file with fjsonparser(flatten_maps=false)

file|version|field|type|order|xpath
Departments|1.1|id|string-255|0|
Departments|1.1|devices|string-255|1|device.[]
Departments|1.1|date|date-false|2|
Departments|1.1|visitors|varchar(128)|3|visits['visitors']::varchar(128)
Departments|1.1|international|varchar(128)|4|visits['international']::varchar(128)
Departments|1.1|company|varchar(128)|5|visits['company']::varchar(128)

Manifest File