Joins ===== .. code-block:: http /api/repositories/{repository}/entities/{entity}/joins .. csv-table:: :header: Parameter, Type, Purpose :widths: 20, 20, 60 :stub-columns: 1 repository, string, the name of the :doc:`repository` entity, int, The ID of the entity to view Show All ~~~~~~~~ .. http:get:: /api/repositories/(string:repository)/entities/(int:entityID)/joins :synopsis: Lists all joins of an entity Lists all joins of the corresponding :doc:`entity` .. code-block:: http GET /api/repositories/jupiter/entities/1/joins HTTP/1.1 Accept: application/json .. csv-table:: :header: Response Code, Reason :widths: 25, 75 200, No error 500, Server error The response will be a :doc:`standard-response`, with a list of all discovered joins in the corresponding :doc:`entity`. The ``columns`` array will contain the list of columns for each data row. .. csv-table:: :header: Human Name, Column Name, Description :widths: 25, 25, 50 "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 :doc:`entity`: .. code-block:: json { "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 :doc:`entity`, ``rows`` will be an empty string. Show Some ~~~~~~~~~~ .. http:post:: /api/repositories/(string:repository)/entities/(int:entityID)/joins :synopsis: Lists discovered and permanent joins of an entity that match various criteria Lists discovered and permanent joins of the corresponding :doc:`entity` that match various criteria :jsonparam body: The request body .. code-block:: http POST /api/repositories/jupiter/entities/1/joins HTTP/1.1 Content-Type: application/json { "command": "get", "options": { "pageSize":"2", "pageStart":"1" } } .. csv-table:: :header: Response Code, Reason :widths: 25, 75 200, Success 400, Invalid request body 400, Invalid command 500, Server error The request requires a :doc:`standard-request` packet. Available commands are: .. csv-table:: :header: Command, Description :widths: 20, 80 get, Lists discovered and permanent joins of an :doc:`entity` that matches the criteria in Options The response will be a :doc:`standard-response`, same as described for the above get requests, with a list of discovered and permanent joins in the corresponding :doc:`entity` matching the given criteria in Options. Commands ~~~~~~~~ .. _ljc_create: Create ------ .. code-block:: http 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" } } .. csv-table:: :header: Response Code, Reason :widths: 25, 75 200, No error 400, Invalid data passed with request 500, Server error The request requires a :doc:`standard-request` packet. Available commands are: .. csv-table:: :header: Command, Description :widths: 30, 70 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``: .. csv-table:: :header: Name, Mandatory, Default Value, Description :widths: 10, 10, 12, 75 :quote: ' 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": "" 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: .. csv-table:: :header: Value, Description :widths: 10, 75 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: .. csv-table:: :header: Value, Example :widths: 50, 75 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: .. code-block:: json { "url": "/api/repositories/jupiter/scheduler/1" } .. _ljc_edit: Edit ---- .. code-block:: http 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" } } .. csv-table:: :header: Response Code, Reason :widths: 25, 75 204, No error 400, Invalid data passed with request 500, Server error The request requires a :doc:`standard-request` packet. Available commands are: .. csv-table:: :header: Command, Description :widths: 30, 70 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``: .. csv-table:: :header: Field, Purpose :widths: 20, 80 joinKeys, A list of join keys. The value of individual keys should be similar to the 'Join key' columns returned from :doc:`joins` status, Either "Permanent" or "Discovered" (case sensitive) .. _ljc_discover: Discover -------- .. code-block:: http 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" } } .. csv-table:: :header: Response Code, Reason :widths: 25, 75 200, No error 400, Invalid data passed with request 500, Server error The request requires a :doc:`standard-request` packet. Available commands are: .. csv-table:: :header: Command, Description :widths: 30, 70 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``: .. csv-table:: :header: Name, Mandatory, Default Value, Description :widths: 10, 10, 12, 75 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 :doc:`/admin/patternencodings` 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: .. csv-table:: :header: Value, Description :widths: 10, 75 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: .. csv-table:: :header: Value, Example :widths: 50, 75 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 :doc:`scheduled job `. .. _ljc_delete: Delete ------- .. http:delete:: /api/repositories/(string:repository)/entities/(int:entityID)/joins :synopsis: Delete the joins Delete the joins .. code-block:: http 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"] } } .. csv-table:: :header: Response Code, Reason :widths: 25, 75 204, Success 400, Invalid data passed with request 500, Server error The request requires a :doc:`standard-request` packet. Available commands are: .. csv-table:: :header: Command, Description :widths: 30, 70 delete, Delete the joins The request packet must contain the ``command`` and ``data`` elements. The following fields must be sent as part of ``data``: .. csv-table:: :widths: 20, 80 :header: Field, Purpose joinKeys, A list of join keys. The value of individual keys should be similar to the 'Join key' columns returned from :doc:`joins` .. _ej_export: Export ------- Some or all of the rows may be exported as a scheduled task. .. code-block:: http 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" } } .. csv-table:: :header: Response Code, Reason :widths: 25, 75 200, No error 400, Invalid data passed with request 500, Server error The request requires a :doc:`standard-request` packet. Available commands are: .. csv-table:: :header: Command, Description :widths: 30, 70 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: .. csv-table:: :header: Field, Required :widths: 20, 10 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. .. code-block:: json { "job": "/api/repositories/jupiter/scheduler/1", "file": "/api/repositories/jupiter/download/filename.csv" }