Joins¶
/api/repositories/{repository}/entities/{entity}/joins
Parameter | Type | Purpose |
---|---|---|
repository | string | the name of the Repository |
entity | int | The ID of the entity to view |
Show All¶
-
GET
/api/repositories/
(string: repository)/entities/
(int: entityID)/joins
¶ Lists all joins of the corresponding Entity
GET /api/repositories/jupiter/entities/1/joins HTTP/1.1
Accept: application/json
Response Code | Reason |
---|---|
200 | No error |
500 | Server error |
The response will be a Standard Response, with a list of all discovered joins in the corresponding Entity.
The columns
array will contain the list of columns for each data row.
Human Name | Column Name | Description |
---|---|---|
Left Entity ID | LEFT_ENTITY_ID | Left source entity ID |
Left Attribute List | LEFT_ATTRIBUTE_LIST | List of attribute Ids comprising left hand (compound) join |
Right Entity ID | RIGHT_ENTITY_ID | Right source entity ID |
Right Attribute List | RIGHT_ATTRIBUTE_LIST | List of attribute Ids comprising right hand (compound) join |
Sequence | SEQUENCE | A sequence number allowing for multiple joins to be defined between 2 entities |
Status | _status | Whether the join is permananent,discovered, deleted or in a project |
Left Entity | _left_ename | Left hand source entity |
Left Expression | _left_expr | Expression of LH attributes comprising the left hand side of the (compound) join |
Right Entity | _right_ename | Right hand source entity |
Right Expression | _right_expr | Expression of RH attributes comprising the right hand side of the (compound) join |
Matching Values | COMMON_VALUES | The number of distinct joined values |
Inner joined rows | INNER_PRODUCT | The number of rows in the inner join - joining rows only |
Outer joined rows | _full_outer_product | The number of rows in the outer join - joining rows and non-joining rows from both sides |
Left Non-Matching Values | LEFT_NOT_MATCH_VALUES | The number of distinct values on the left-hand side that did not join |
Left Non-Matching Rows | LEFT_NOT_MATCH_ROWS | The number of rows on the left-hand side that did not join |
Left outer joined rows | _left_outer_product | The number of rows in the left outer join - joining rows and non-joining left rows |
Right Non-Matching Values | RIGHT_NOT_MATCH_VALUES | The number of distinct values on the right-hand side that did not join |
Right Non-Matching Rows | RIGHT_NOT_MATCH_ROWS | The number of rows on the right-hand side that did not join |
Right outer joined rows | _right_outer_product | The number of rows in the right outer join - - joining rows and non-joining right rows |
Left Loaded Rows | _left_loaded_rows | Number of rows in the left hand source entity |
Left Filter | _left_select | The filter used to select the rows from the left hand entity |
Left Selected Rows | _left_rows | The number of rows selected by the Left hand filter (or loaded rows if not filtered) |
Right Loaded Rows | _right_loaded_rows | Number of rows in the right hand source entity |
Right Filter | _right_select | The filter used to select the rows from the right hand entity |
Right Selected Rows | _right_rows | The number of rows selected by the right hand filter (or loaded rows if not filtered) |
Join Type | _join_type | Whether the join used original values or a Standardised, Metaphone or Pattern function |
Actual Cardinality | ACTUAL_CARDINALITY | The actual cardinality of the join |
Inferred Cardinality | INFERRED_CARDINALITY | The Inferred Cardinality of the join |
Exact Cardinality | EXACT_CARDINALITY | The ‘exact’ cardinality of the join. This is defined as the ratio Left cardinality : Right Cardinality |
Match Best | MATCH_BEST | The percentage match of the join (best match of Values,LHrows or RHrows) |
Match Values | MATCH_VALUES | % of values that joined |
Match LHrows | MATCH_LHROWS | % of LH rows that joined |
Match RHrows | MATCH_RHROWS | % of RH rows that joined |
Left Join Rows | LEFT_JOIN_ROWS | The number of rows from the left-hand side that joined |
Left Join Cardinality | LEFT_JOIN_CARDINALITY | The cardinality of the joined values from the left-hand side of the join. On average, each joining value occurs this number of times on the LHS |
Right Join Rows | RIGHT_JOIN_ROWS | The number of rows from the right-hand side that joined |
Right Join Cardinality | RIGHT_JOIN_CARDINALITY | The cardinality of the joined values from the right-hand side of the join. On average, each joining value occurs this number of times on the RHS |
Join Card One One | JOIN_CARD_ONE_ONE | Number of joined values with 1:1 cardinality |
Join Card One Many | JOIN_CARD_ONE_MANY | Number of joined values with 1:M cardinality |
Join Rows One Many | JOIN_ROWS_ONE_MANY | Number of joined rows with 1:M cardinality |
Join Card Many One | JOIN_CARD_MANY_ONE | Number of joined values with M:1 cardinality |
Join Rows Many One | JOIN_ROWS_MANY_ONE | Number of joined rows with M:1 cardinality |
Join Card Many Many | JOIN_CARD_MANY_MANY | Number of joined values with M:M cardinality |
Join Rows Many Many | JOIN_ROWS_MANY_MANY | Number of joined rows with M:M cardinality |
Analyzed Date | _analysed_date | When the join was analyzed |
Analyzed By | ANALYSED_BY | Who analyzed the data. |
Created By | CREATED_BY | Who created the join. |
Created Date | _created_date | When the join definition was created |
Performed By | PERFORMED_BY | Who created the join result index. |
Performed Date | _performed_date | When the result join index was created. |
Edited By | EDITED_BY | Who edited the join definition |
Edited Date | _edited_date | Edited Date |
Note Count | note.COUNT | The number of notes for this join |
Join Job | JOIN_JOB | The name of the join wizard job which created the join |
Join Job ID | JOB_ID | The ID of the join wizard job which created the join |
Join Index | JOIN_INDEX | Join Result index name |
Join Result Segment | __JOIN_RESULT_SEGMENT | Whether the result is the natural (inner) join, or an outer (full,left or right) join. |
Left No Matching Row Action | __DOC_LEFT_NO_MATCH_OPTION | Documented left non matching row action, eg. accept,ignore, reject or warning |
Right No Matching Row Action | __DOC_RIGHT_NO_MATCH_OPTION | Documented right non matching row action, eg. accept,ignore, reject or warning |
Join key | _join_key | System Join key |
The rows
array will contain all joins in the corresponding Entity:
{
"rows": [
{
"dataRow": [
"57",
"1",
"58",
"1",
"1",
"Discovered",
"Employee(57)",
"Employeeid",
"Employeedept(58)",
"Employeeid",
"6",
"6",
"6",
"0",
"0",
"6",
"0",
"0",
"6",
"6",
"",
"6",
"6",
"",
"6",
"Value",
"1:1",
"1:1",
"1.000:1.000",
"100.000",
"100.000",
"100.000",
"100.000",
"6",
"1.000",
"6",
"1.000",
"6",
"0",
"0",
"0",
"0",
"0",
"0",
"2015/03/04 15:46:48",
"bob@TEST-7",
"bob@TEST-7",
"2015/03/04 15:46:49",
"",
"",
"",
"",
"",
"j6",
"",
"full",
"",
"",
"57 1 58 1 1"
],
"metadata": {
"url": "/api/repositories/alpha/joins/57-1-58-1-1"
}
}
]
}
If there are no discovered joins in the Entity, rows
will be an empty string.
Show Some¶
-
POST
/api/repositories/
(string: repository)/entities/
(int: entityID)/joins
¶ Lists discovered and permanent joins of the corresponding Entity that match various criteria
JSON Parameters: - body – The request body
POST /api/repositories/jupiter/entities/1/joins HTTP/1.1
Content-Type: application/json
{
"command": "get",
"options": {
"pageSize":"2",
"pageStart":"1"
}
}
Response Code | Reason |
---|---|
200 | Success |
400 | Invalid request body |
400 | Invalid command |
500 | Server error |
The request requires a Standard Request packet. Available commands are:
Command | Description |
---|---|
get | Lists discovered and permanent joins of an Entity that matches the criteria in Options |
The response will be a Standard Response, same as described for the above get requests, with a list of discovered and permanent joins in the corresponding Entity matching the given criteria in Options.
Commands¶
Create¶
POST /api/repositories/alpha/entities/1/joins HTTP/1.1
Content-Type: application/json
{
"command": "create",
"data": {
"jobName": "Create-Join-Input-201611216",
"createJoinIndex": "1",
"params": {
"LH": {
"entity": "1",
"attributes": "1 2 4",
"expression": ""
},
"RH": {
"entity": "10",
"attributes": "1 2 4",
"expression": ""
}
},
"scheduleNow": "0",
"scheduleTime": "2016-11-22 11:40:45"
}
}
Response Code | Reason |
---|---|
200 | No error |
400 | Invalid data passed with request |
500 | Server error |
The request requires a Standard Request packet. Available commands are:
Command | Description |
---|---|
create | Create the permanent joins between entities |
The request packet must contain the command
and data
elements. The following fields must be sent as part of data
:
Name | Mandatory | Default Value | Description |
---|---|---|---|
jobName | No | restjob | Name of the scheduled job |
params | Yes | NA | Object containing the entity information for the left (LH) and right (RH) sides of the join |
LH | Yes | NA | Object containing the entity information for the left side of the join |
RH | Yes | NA | Object containing the entity information for the right side of the join |
entity | Yes | NA | The entity ID for that side of the join |
attributes | Yes | NA | It should be a list in the following format “attributes”: “<attr list>” |
expression | No | NA | It should be a humanised expression |
createJoinIndex | No | 0 | Specify if join index needs to be created if there are more than 19446 rows |
scheduleNow | No | NA | Yes (1) / No (0) |
scheduleTime | No | NA | Required: No (if scheduleNow is 1) / Yes (if scheduleNow is 0) |
If scheduleNow
is 0 and scheduleTime
is not provided, the job will run immediately.
scheduleNow
field can be formatted as follows:
Value | Description |
---|---|
1 | Schedule the job immediately |
0 | Schedule the job at a given date and time (requires scheduleTime field) |
scheduleTime
field can be formatted as follows:
Value | Example |
---|---|
YYYY-Month-DD HH:MM:SS | 2016-JANUARY-01 10:30:45 |
YYYY/Month/DD HH:MM:SS | 2016/JANUARY/01 10:30:45 |
YYYY-Mon-DD HH:MM:SS | 2016-JAN-01 10:30:45 |
YYYY/Mon/DD HH:MM:SS | 2016/JAN/01 10:30:45 |
YYYY-MM-DD HH:MM:SS | 2016-01-01 10:30:45 |
YYYY/MM/DD HH:MM:SS | 2016/01/01 10:30:45 |
If the command is successful, the response will return the url link to a scheduled job as follows:
{
"url": "/api/repositories/jupiter/scheduler/1"
}
Edit¶
POST /api/repositories/alpha/entities/1/joins HTTP/1.1
Content-Type: application/json
{
"command": "edit",
"data": {
"joinKeys":["1 {8 4} 1 {3 13} 1", "1 {8 4} 25 {9 5} 1"],
"status":"Discovered"
}
}
Response Code | Reason |
---|---|
204 | No error |
400 | Invalid data passed with request |
500 | Server error |
The request requires a Standard Request packet. Available commands are:
Command | Description |
---|---|
edit | Edit the status of the join to discovered or permanent |
The request packet must contain the command
and data
elements. The following fields must be sent as part of data
:
Field | Purpose |
---|---|
joinKeys | A list of join keys. The value of individual keys should be similar to the ‘Join key’ columns returned from Joins |
status | Either “Permanent” or “Discovered” (case sensitive) |
Discover¶
POST /api/repositories/alpha/entities/1/joins HTTP/1.1
Content-Type: application/json
{
"command": "discover",
"data": {
"jobName":"restjob",
"attributes":
{
"1":"1 2 3 4",
"3":"5 6"
},
"function": "value",
"matchQuality": "5",
"selfJoins": "1",
"manyToMany" : "0",
"scheduleNow":"0",
"scheduleTime":"2016-11-22 11:40:45"
}
}
Response Code | Reason |
---|---|
200 | No error |
400 | Invalid data passed with request |
500 | Server error |
The request requires a Standard Request packet. Available commands are:
Command | Description |
---|---|
discover | Discover joins between entities |
The request packet must contain the command
and data
elements. The following fields should be sent as part of data
:
Name | Mandatory | Default Value | Description |
---|---|---|---|
jobName | No | restjob | Name of the scheduled job |
attributes | Yes | NA | It should be a list in the following format “entityID”:”attr list” |
function | No | value | Its value can be “standardize - Integer” “standardize - Decimal” “standardize - String” value metaphone mask pattern |
pattern | No | NA | this field is required only when the function is pattern it is selected from Pattern Encodings |
matchQuality | No | 0 | this is the match quality percentage |
selfJoins | No | 0 | 1 will enable self joins |
manyToMany | No | 0 | value of 1 will include the inferred M:M joins |
scheduleNow | No | NA | Yes (1) / No (0) |
scheduleTime | No | NA | Required: No (if scheduleNow is 1) / Yes (if scheduleNow is 0) |
If scheduleNow
is 0 and scheduleTime
is not provided, the job will run immediately.
scheduleNow
field can be formatted as follows:
Value | Description |
---|---|
1 | Schedule the job immediately |
0 | Schedule the job at a given date and time (requires scheduleTime field) |
scheduleTime
field can be formatted as follows:
Value | Example |
---|---|
YYYY-Month-DD HH:MM:SS | 2016-JANUARY-01 10:30:45 |
YYYY/Month/DD HH:MM:SS | 2016/JANUARY/01 10:30:45 |
YYYY-Mon-DD HH:MM:SS | 2016-JAN-01 10:30:45 |
YYYY/Mon/DD HH:MM:SS | 2016/JAN/01 10:30:45 |
YYYY-MM-DD HH:MM:SS | 2016-01-01 10:30:45 |
YYYY/MM/DD HH:MM:SS | 2016/01/01 10:30:45 |
The response will contain the URL of the scheduled job.
Delete¶
-
DELETE
/api/repositories/
(string: repository)/entities/
(int: entityID)/joins
¶ Delete the joins
DELETE /api/repositories/alpha/entities/1/joins HTTP/1.1
Content-Type: application/json
{
"command": "delete",
"data": {
"joinKeys":["1 {8 4} 1 {3 13} 1", "1 {8 4} 25 {9 5} 1"]
}
}
Response Code | Reason |
---|---|
204 | Success |
400 | Invalid data passed with request |
500 | Server error |
The request requires a Standard Request packet. Available commands are:
Command | Description |
---|---|
delete | Delete the joins |
The request packet must contain the command
and data
elements. The following fields must be sent as part of data
:
Field | Purpose |
---|---|
joinKeys | A list of join keys. The value of individual keys should be similar to the ‘Join key’ columns returned from Joins |
Export¶
Some or all of the rows may be exported as a scheduled task.
POST /api/repositories/jupiter/entities/1/joins HTTP/1.1
Content-Type: application/json
{
"command": "export",
"data": {
"filename": "filename.csv",
"filetype": "csv",
"encoding": "utf-8"
},
"options": {
"columns": ["_humanised_name", "ENTITY_ID"],
"where": "Ref = 1"
}
}
Response Code | Reason |
---|---|
200 | No error |
400 | Invalid data passed with request |
500 | Server error |
The request requires a Standard Request packet. Available commands are:
Command | Description |
---|---|
export | Export rows to a file |
The request packet must contain the command
and data
elements. The options
element is not required, but may specify the columns to export, and/or the rows to filter.
The following table shows which fields are required in the data
element:
Field | Required |
---|---|
filename | Y |
filetype | N |
encoding | N |
If filetype
is not specified, it defaults to csv. If encoding
is not specified, it defaults to the server native encoding.
The response will contain the URL of the scheduled task and the link to download the exported file.
{
"job": "/api/repositories/jupiter/scheduler/1",
"file": "/api/repositories/jupiter/download/filename.csv"
}