qub40232046/40232046-graphical-processing-be repository overview

⁠Graphical CSV Processing API

Processes and queries supplied CSV files using a comprehensive graph model.
This repo has been implemented with conscious effort to enable further extension easily through forking,should a need arise for an additional node or changing to an existing node.
This API is capable of returning multiple csvs from the same or separate data streams and as it works on a copy of the contents at each stage will never impact the input data.

⁠Docker Quick Start

⁠Registry

Docker Hub⁠: qub40232046/40232046-graphical-processing-be

⁠Starting

docker run -d -p 8080:8080 -e APP_FRONTEND="{space-separated-list-cors-urls-allowed}" qub40232046/40232046-graphical-processing-be:{tag}

Note the APP_FRONTEND variable defaults to http://localhost:3000⁠ so to enable quick running of the paired frontend⁠ application locally.

⁠Integration Guide

⁠Example Request

curl --location --request POST 'http://localhost:8080/process' \
--form 'csvFiles=@"/redacted/Scores.csv"' \
--form 'graph="{
	\"defaultValues\": {},
	\"nodes\": [
		{
			\"id\": \"0\",
      			\"group\": \"file\", 
     			\"operation\":\"open_file\",
      			\"name\": \"Scores.csv\"
		},
		{
			\"id\": \"1\",
      			\"group\": \"processing\", 
      			\"operation\": \"filter\",
      			\"filterType\": \"less_than\",
      			\"column\": \"Score\",
     			\"condition\": \"40\",
			\"equal\": \"true\"
		},
		{
			\"id\": \"2\",
			\"group\": \"file\", 
     			\"operation\": \"write_file\",
      			\"name\": \"Output.csv\"
		}
	],
	\"edges\": [
		{
			\"from\": \"0\",
			\"to\": \"1\"
		},
		{
			\"from\": \"1\",
			\"to\": \"2\"
		}
	]
}";type=application/json'

Files: 1 or more to be supplied - matching number of open file nodes.
Graph: contains the following:

• Default Values: any node attribute default
• Nodes: 1 or more nodes (must contain an open file node).
• Edges: 0 or more edges representing flow of data.

⁠General Integration Rules

• Must ensure minimum 1 file is returned.
• Must ensure minimum 1 file is supplied.
• Must ensure each file supplied has a corresponding Open File Node.
• Files must be .csv.
• Nodes supplied must be listed or be added.
• Nodes supplied must have the required attributes specified.
• Edges must contain both 'from' and 'to' attributes referring to node ids.
• No cycles may be in the supplied graph, G = (N, E).

⁠Valid Columns Names

• A column name may be any alphanumeric string with spaces, underscores and hyphens possible.
• An alias is optional
• If an alias is present it must start alphanumeric then may also include spaces, underscores and hyphens.
• A single '.' character will separate the alias from the column name.

⁠Identifying Columns

• If a column name alone is unique across the list of csvs supplied to a node, this is sufficient to deduce the column referenced.
• Otherwise, the alias is required to identify the column being referenced.
• In the event a missing or an ambiguous column name is supplied to the API, it will return exception indicating the issue.

⁠Clashing Data

On a merge of any type, checks are taken to ensure cells don't lose genuine data. Genuine data is considered cells with non-empty string content.

• Matching genuine data in cells => merge
• Genuine data in one cell and empty or null in other => merge
• Non-matching genuine data in cells => exception

⁠How the data passes from node to node

Each node will output a single csv result. This is then able to be used as input into any number of non-dependent nodes.
That is to emphasise you cannot have loops in the supplied graph

⁠Node Types

• Alias Node⁠
• Columns Math Node⁠
• Concat Columns Node⁠
• Concat Tables Node⁠
• Drop Alias Node⁠
• Drop Columns Node⁠
• Filter Node⁠
• Join Node⁠
• Limit Node⁠
• Merge Columns Node⁠
• Merge Rows Node⁠
• Open File Node⁠
• Or Node⁠
• Order Column Node⁠
• Rename Column Node⁠
• Row Basic Math Node⁠
• Row Statistical Math Node⁠
• Set Complement Node⁠
• Take Columns Node⁠
• Unique Columns Node⁠
• Write File Node⁠

⁠Alias Node

Description: Prefixes supplied alias onto the columns of the supplied csv.
Inputs: 1 csv file (will remove existing alias on any of the columns if present).
Outputs: Copy of csv with the new identifier prefixed to column headers.
Attributes: 1

• String: Alias

Exceptions if:

• Resultant csv does not have unique column headers
• Alias supplied is not valid⁠

⁠Column Math Node

Description: Calculate the count, min, max, average or sum of a specified column.
Inputs: 1 csv file (will remove existing alias on any of the columns if present).
Outputs: Csv with just the calculated value.
Attributes: 2

• String: column
• StatisticalType: math operation
  • Average
  • Count
  • Max
  • Min
  • Sum

Exceptions if:

• Column cannot be identified⁠
• Any non-empty cell in the column cannot be parsed to double

⁠Concat Columns Node

Description: Creates a suffixed column on csv in the order of first column value + second column value.
Inputs: 1 csv file.
Outputs: Copy of csv with the new column suffixed csv.
Attributes: 3

• String: column1
• String: column2
• String: concat header

Exceptions if:

• Column1 or column2 cannot be identified⁠
• Concat header is not valid⁠

⁠Concat Tables Node

Description: Takes 2 input csvs and produces single csv with number of rows matching sum of two inputs rows. Headers are merged to avoid duplicate headers.
Inputs: 2 csv files.
Outputs: A table of the two inputs.
Attributes: 0

⁠Drop Alias Node

Description: Removes any identifier alias on the columns of the input csv.
Inputs: 1 csv file.
Outputs: Copy of csv with de-aliased column headers.
Attributes: 0
Exceptions if:

• Resultant csv does not have unique⁠ column headers

⁠Drop Columns Node

Description: Removes the blacklist of columns from the resultant csv.
Inputs: 1 csv file.
Outputs: Copy of csv with only rows unspecified.
Attributes: 1

• Comma-separated-list: columns

Exceptions if:

• Any column in list cannot be identified⁠

⁠Filter Node

Description: Filter for equality or an inequality.
Inputs: 1 csv file.
Outputs: Copy of csv with only rows matching or not matching filter depending on equal boolean.
Attributes: 4

• String: column
• String/Double: condition
• FilterType: filter type
  • String Equality (String condition)
  • Numeric Equality (Double condition)
  • Greater Than (Double condition)
  • Greater Than or Equal (Double condition)
  • Less Than (Double condition)
  • Less Than or Equal (Double condition)
• Boolean: equal

Exceptions if:

• Specified column cannot be identified⁠
• Condition requires double and cannot be parsed to double

⁠Join Node

Description: Join two csv files on commonality of two columns.
Inputs: 2 csv files.
Outputs: Joined csv of the two input csvs as per the join type chosen (duplicate headers are possible and user must mitigate this if problem).
Attributes: 3

• String: left column
• String: right column
• JoinType: join type
  • Left Join
  • Right Join
  • Inner Join
  • Outer Join

Exceptions if:

• Either column specified cannot be identified⁠

⁠Limit Node

Description: Limit number of rows in the supplied csv.
Inputs: 1 csv file.
Outputs: Copy of input csv with specified number of rows.
Attributes: 1

• Integer: limit

Exceptions if:

• Limit supplied is negative

⁠Merge Columns Node

Description: Merge two columns into one in the position of the first column.
Inputs: 1 csv file.
Outputs: Copy of input csv with the specified columns merged.
Attributes: 4

• String: column1
• String: column2
• MergeType: merge type
  • String Equality
  • Numeric Equality
• String: merged column name

Exceptions if:

• Either column specified cannot be identified⁠
• New column name is invalid⁠
• Data in two columns of any row clash⁠

⁠Merge Rows Node

Description: Merge rows which hold value in the column specified into one.
Inputs: 1 csv file.
Outputs: Copy of input csv with the rows merged.
Attributes: 2

• String: column
• String: value

Exceptions if:

• Column specified cannot be identified⁠
• Data in rows clash⁠

⁠Open File Node

Description: Node to represent supplied file by setting name to filename.
Inputs: 0 csv file.
Outputs: Copy of input csv supplied in request.
Attributes: 1

• String: name

⁠Or Node

Description: Logical or node. To be applied on two filter nodes enabling logical or.
Inputs: 2 csv files.
Outputs: Concatenation of the two input csvs and unique column applied to avoid duplicate entries matching both filter expressions.
Attributes: 1

• String: column

Exceptions if:

• Column specified cannot be identified⁠ post concat
• Data in rows clash⁠ on unique

⁠Order Column Node

Description: Orders specified column as per order type supplied.
Inputs: 1 csv file.
Outputs: Copy of input csv with rows ordered as specified.
Attributes: 2

• String: column
• OrderType: order type
  • Alphabetic Order Asc
  • Alphabetic Order Desc
  • Numeric Order Asc
  • Numeric Order Desc

Exceptions if:

• Column specified cannot be identified⁠

⁠Rename Column Node

Description: Renames the specified column in the supplied csv.
Inputs: 1 csv file.
Outputs: Copy of input csv with the specified columns merged.
Attributes: 2

• String: column
• String: new name

Exceptions if:

• Column specified cannot be identified⁠
• New column name is invalid⁠

⁠Row Basic Math Node

Description: Add, subtract, multiply, divide or modulo of either two columns or one column and a literal.
Inputs: 1 csv file.
Outputs: Copy of csv with an additional column suffixed which takes the value in column1 and applies the column or literal in value.
Attributes: 5

• String: column1
• String: value
• String: new name
• MathOperation: math operation
  • Add
  • Divide
  • Modulo
  • Multiply
  • Subtract
• boolean: literal

Exceptions if:

• Any column1 or, if literal is false, value cannot be identified⁠
• New name header is not valid⁠
• Any non-empty cell in the columns specified cannot be parsed to double

⁠Row Statistical Math Node

Description: Calculate the count, min, max, average or sum of a specified columns of a row.
Inputs: 1 csv file.
Outputs: Copy of csv with an additional column suffixed.
Attributes: 3

• Comma-separated-list: columns
• String: new name
• StatisticalType: math operation
  • Average
  • Count
  • Max
  • Min
  • Sum

Exceptions if:

• Any column in columns cannot be identified⁠
• Any non-empty cell in the columns specified cannot be parsed to double

⁠Set Complement Node

Description: Returns the complementary set of subset compared to super set using key header as indicator for which fields are present. Note bigger csv supplied assumed as super set. Worthy of note is that the assumption is the column specified is unique.
Inputs: 2 csv files.
Outputs: Complementary set of the subset compared to the super set. If either set passed in is empty it will output the csv passed as the outer set.
Attributes: 1

• String: key header

Exceptions if:

• Column specified cannot be identified⁠
• Headers in two supplied files are not identical

⁠Take Columns Node

Description: Takes the whitelist of columns from the resultant csv.
Inputs: 1 csv file.
Outputs: Copy of csv with only rows specified.
Attributes: 1

• Comma-separated-list: columns

Exceptions if:

• Any column in list cannot be identified⁠

⁠Unique Column Node

Description: Merges the rows with matching value in specified column into one row.
Inputs: 1 csv file.
Outputs: Copy of csv with only unique rows on the column specified.
Attributes: 1

• String: column

Exceptions if:

• Column supplied cannot be identified⁠
• Any of the row merges contain clashing⁠ data

⁠Write File Node

Description: Renames input file and ensures it is in the returned files in response. Useful in debugging queries too.
Inputs: 1 csv file.
Outputs: Copy of input csv.
Attributes: 1

• String: name