Discovered Joins¶
/api/repositories/(string:repository)/joins/discovered
| Parameter | Type | Purpose |
|---|---|---|
| repository | string | the name of the Repository |
Get¶
-
GET/api/repositories/(string: repository)/joins/discovered¶ Lists all discovered joins of the corresponding Repository
GET /api/repositories/jupiter/joins/discovered 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 Repository.
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 |
data row will contain the list of all discovered joins in the corresponding Repository:
"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 Repository, rows will be an empty string.
Commands¶
Edit¶
This command allows join status to change to permanent or discovered.
POST /api/repositories/alpha/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/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 a join |
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.
Create¶
POST /api/repositories/alpha/joins HTTP/1.1
Content-Type: application/json
{
"command": "create",
"data": {
"jobName": "restjob",
"params": {
"LH": {
"entity": "1",
"attributes": "1 2 3 4",
"expression": ""
},
"RH": {
"entity": "3",
"attributes": "1 2 3 4",
"expression": ""
}
},
"createJoinIndex": "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 |
|---|---|
| create | Create a permanent join |
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 |
| 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 |
The response will contain the URL of the scheduled job.
Delete¶
-
DELETE/api/repositories/(string: repository)/joins¶ Delete the joins
DELETE /api/repositories/alpha/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 |