Lucidworks Proof of Concept

# Lucidworks Proof of Concept ## POC Configuration | Configuration | Place Holder |Value| | -------- | -------- |-------| | Customer Code | `customerCode` | lucidworks | | Mesh Key | `meshKey` | dev | | Conscia Endpoint | `baseURL` | https://io-staging.conscia.ai | | API Key | `apiKey` | shared privately | | Conscia Admin | N/A |https://admin-staging.conscia.ai/applications| ## Platform Components ![](https://hackmd.io/_uploads/SkHa07WQY.png) ## Data Set The data set is from a grocery store with multiple store locations. The entities and the corresponding files are described in the table below: | Entity | File(s) | Data Record Type | | --- | --- | -- | | Customer | customer.csv.gz | Master | | Product | product.csv.gz | Master | | Promo | promo.csv.gz | Master | | Store | store.csv.gz | Master | | Transaction Header | transaction_header_YYYYMM.csv.gz | Transactional | | Transaction Detail | transaction_detail_YYYYMM.csv.gz | Transactional | The entity relationships and CSV columns are illustrated in the following ER diagram: ```mermaid erDiagram CUSTOMER ||--o{ TRANSACTION_HEADER : generates CUSTOMER ||--o{ TRANSACTION_DETAIL : generates TRANSACTION_HEADER ||--o{ TRANSACTION_DETAIL : contains TRANSACTION_DETAIL ||--o| PROMO : uses TRANSACTION_DETAIL ||--|| PRODUCT : has TRANSACTION_HEADER ||--|| STORE : "occurred at" TRANSACTION_DETAIL ||--|| STORE : "occurred at" CUSTOMER { string UNIQUE_CUSTOMER_ID string TYR_KEY string TYR_ACCOUNT_ID string CARDNUMBER string STATUSNUMBER date FIRST_TRANS_DATE date LAST_TRANS_DATE string SEGMENT_NUMBER string SEGMENT_NAME string PRIMARY_SITE string GG_CUSTOMER_ID boolean GG_CUSTOMER_FLAG integer POINTS_BALANCE decimal CASH_BALANCE boolean EMAIL_FLAG } PRODUCT { string PRODUCT_KEY string SKU_NO string PRODUCT_DESCRIPTION string BRAND string WeighedItemFlag string AttOrganic string AttHealth string Att_Gluten string Att_Natural string DiscontinuedFlag string PRODUCT_LEVEL_1_DEPTGRP_ID string PRODUCT_LEVEL_1_DEPTGRP_DESC string PRODUCT_LEVEL_2_DEPTID string PRODUCT_LEVEL_2_DEPT_DESC string PRODUCT_LEVEL_3_CLASS_ID string PRODUCT_LEVEL_3_CLASS_DESC string PRODUCT_LEVEL_4_LINEID string PRODUCT_LEVEL_4_LINE_DESC } PROMO { string PROMOID string PROMO_DESC date PROMO_START_DATE date PROMO_END_DATE string PROMO_TYPE } STORE { string SITEKEY string SITENUMBER string SITEFULLNAME string SITENUMBERANDNAME string CITY string REGION string PROVINCE string POSTALCODE string STORE_FORMAT string PRICE_ZONE string PRICE_ZONE_NAME date SITEOPENING } TRANSACTION_HEADER { string TRANSACTION_ID timestamp TRANSACTION_DATE string STORE_ID integer ITEM_COUNT integer SALE_QTY decimal SALES_AMOUNT decimal REGULAR_PRICE decimal DISCOUNT string TYR_CARDNUMBER } TRANSACTION_DETAIL { string TRANSACTION_ID string SITEKEY string PRODUCT_KEY string TYR_KEY string PROMOID timestamp TRANSACTION_DATE integer UNIT_QTY decimal WEIGHT decimal COGS_COST decimal SALES_AMOUNT_ACTUAL decimal SALES_AMOUNT_REGULAR boolean NON_REVENUE_FLAG string TYR_CARDNUMBER } ``` When representing the above entities as JSON, the fields are all lowercased. The Master Data schema is flexible, but for the POC we have chosen lowercased fields for the Master Data fields, so the fields of the entities being passed should match. Example customer record: ```json { "tyr_key": "10000182", "unique_customer_id": "A65F0081-778E-43EE-BD46-9C14ACE34356", "tyr_account_id": "45010000182", "cardnumber": "45010000182", "statusnumber": "A", "first_trans_date": 1600232398000, "last_trans_date": 1631768398000, "segment_number": "3", "segment_name": "Opal", "primary_site": "0", "gg_customer_flag": "N", "points_balance": 1928, "cash_balance": 0, "email_flag": 0 } ``` When passing in a values from the CSV as JSON, please ensure the following: - timestamp and date values should be sent as epoch values in milliseconds. - boolean values (represented as "N", 0, "Y", 1) should be passed as true or false ## Creating, updating and removing Master Data Records into a Conscia Collection Master Data Records are uploaded into a Data Collection inside the Conscia Graph and are typically enriched with the Computed Values that are calculated from the Transactional Data Records. For example: `Customers` are Master Data Records and `Product Purchases` are Transaction Data Records. `Product Views` could be used to calculate each Customer's "favourite" product category which could be used to populate a Customer's `favouriteCategory` field. Master Data Records are typically used as Lookups for Transactional Data Records. In the above example, the `Product Purchases` Data Records will have a Customer identifier and would need the `Customers` Data Records as a Lookup to get the Customer attributes. If Master Data Records do not need to be enriched or managed in any way, then they do not need to be uploaded into a Data Collection. Instead, upload them directly into a Data Table (as you would for Transactional Data Records). | Entity | `collectionCode` (in Conscia Graph) | | --- | --- | | Customer | `customer` | | Product | `product` | | Promo | `promo` | | Store | `store` | ### Asynchronous Webservices Asynchronous webservices are not rate-limited. This is the recommeneded way to Create/Update/Delete Master Data. Asynchronous endpoints will return an jobID which can be used to check status. They can optionally specify a boolean property `sendCompletionEmail` which defaults to `false` if not specified. If set to `true`, an email is sent to the user associated with whe API Key. Asynchronous endpoints can optionally specify a boolean property `eventSource` which is a string that will be associated to the Job. #### Creating Master Data Records (Asynchronously) Endpoint: ``` POST {{baseUrl}}/vue/_api/v1/collections/{{collectionCode}}/records/_async ``` | Property | Description | | --- | --- | | ifExists | `ignore` will skip the creation of the record if already exists. `conflict` will throw an error if the record already exists. | Example: ```http POST {{baseUrl}}/vue/_api/v1/collections/customer/records/_async Content-Type: application/json Authorization: Bearer {{apiKey}} X-Customer-Code: {{customerCode}} { "sendCompletionEmail": true, "ifExists": "ignore", "eventSource": "customer-123-pipeline", "dataRecords": [ { "tyr_key": "10000182", "unique_customer_id": "A65F0081-778E-43EE-BD46-9C14ACE34356", "tyr_account_id": "45010000182", "cardnumber": "45010000182", "statusnumber": "A", "first_trans_date": 1600232398000, "last_trans_date": 1631768398000, "segment_number": "3", "segment_name": "Opal", "primary_site": "0", "gg_customer_flag": "N", "points_balance": 1928, "cash_balance": 0, "email_flag": 0 }, { "tyr_key": "10000189", "unique_customer_id": "B12F0081-778E-43EE-BD46-9C14ACE34675", "tyr_account_id": "45010000189", "cardnumber": "45010000189", "statusnumber": "A", "first_trans_date": 1600232398000, "last_trans_date": 1631768398000, "segment_number": "3", "segment_name": "Opal", "primary_site": "0", "gg_customer_flag": "N", "points_balance": 1928, "cash_balance": 0, "email_flag": 0 } ] } ``` #### Updating Master Data Records (Asynchronously) Endpoint: ``` PATCH {{baseUrl}}/vue/_api/v1/collections/{{collectionCode}}/records/_async ``` | Property | Description | | --- | --- | | ifExists | `replace` will replace the full record. `merge` will overwrite the fields specified in the records. | | ifNotExists | `create` - if the specified identifier does not exist, create the record. `fail` - if the specified identifier does not exist, fail. `ignore` - if the specified identifier does not exist, skip it. | Example: Update 2 customers' `points_balance` and `cash_balance` values. The `unique_customer_id` is the record identifier which must be provided to update existing records. ```http PATCH {{baseUrl}}/vue/_api/v1/collections/{{collectionCode}}/records/_async Content-Type: application/json Authorization: Bearer {{apiKey}} X-Customer-Code: {{customerCode}} { "sendCompletionEmail": true, "ifNotExists": "ignore", "eventSource": "customer-123-pipeline", "dataRecords": [ { "unique_customer_id": "A65F0081-778E-43EE-BD46-9C14ACE34356", "points_balance": 2501, "cash_balance": 12.98 }, { "unique_customer_id": "B12F0081-778E-43EE-BD46-9C14ACE34675", "points_balance": 340, "cash_balance": 5.56 } ] } ``` #### Removing Master Data Records (Asynchronously) Endpoint: ``` DELETE {{baseUrl}}/vue/_api/v1/collections/{{collectionCode}}/records/_async ``` | Property | Description | | --- | --- | | ifNotExists | `ignore` - if the specified identifier does not exist, ignore. `fail` - if the specified identifier does not exist, fail. | Example: Remove two customers by their unique identifiers ```http DELETE {{baseURL}}/vue/_api/v1/collections/customer/records/_async Content-Type: application/json Authorization: Bearer {{apiKey}} X-Customer-Code: {{customerCode}} { "sendCompletionEmail": true, "ifNotExists": "fail", "eventSource": "customer-123-pipeline", "dataRecordIDs": [ "A65F0081-778E-43EE-BD46-9C14ACE34356", "B12F0081-778E-43EE-BD46-9C14ACE34675" ] } ``` ### Synchronous Webservices Synchronous webservices are rate-limited to 10 requests per second. #### Creating Master Data Records (Synchronously) Endpoint: ``` POST {{baseUrl}}/vue/_api/v1/collections/{{collectionCode}}/records ``` | Property | Description | | --- | --- | | ifExists | `ignore` will skip the creation of the record if already exists. `conflict` will throw an error if the record already exists. | Example: ```http POST {{baseUrl}}/vue/_api/v1/collections/customer/records Content-Type: application/json Authorization: Bearer {{apiKey}} X-Customer-Code: {{customerCode}} { "sendCompletionEmail": true, "ifExists": "ignore", "eventSource": "test-123-pipeline", "dataRecords": [ { "tyr_key": "10000182", "unique_customer_id": "A65F0081-778E-43EE-BD46-9C14ACE34356", "tyr_account_id": "45010000182", "cardnumber": "45010000182", "statusnumber": "A", "first_trans_date": 1600232398000, "last_trans_date": 1631768398000, "segment_number": "3", "segment_name": "Opal", "primary_site": "0", "gg_customer_flag": "N", "points_balance": 1928, "cash_balance": 0, "email_flag": 0 }, { "tyr_key": "10000189", "unique_customer_id": "B12F0081-778E-43EE-BD46-9C14ACE34675", "tyr_account_id": "45010000189", "cardnumber": "45010000189", "statusnumber": "A", "first_trans_date": 1600232398000, "last_trans_date": 1631768398000, "segment_number": "3", "segment_name": "Opal", "primary_site": "0", "gg_customer_flag": "N", "points_balance": 1928, "cash_balance": 0, "email_flag": 0 } ] } ``` #### Updating Master Data Records (Synchronously) Endpoint: ``` PATCH {{baseURL}}/vue/_api/v1/collections/{{collectionCode}}/records/_async ``` | Property | Description | | --- | --- | | ifExists | `replace` will replace the full record. `merge` will overwrite the fields specified in the records. | | ifNotExists | `create` - if the specified identifier does not exist, create the record. `fail` - if the specified identifier does not exist, fail. | Example: ```http PATCH {{baseURL}}/vue/_api/v1/collections/{{collectionCode}}/records/_async Content-Type: application/json Authorization: Bearer {{apiKey}} X-Customer-Code: {{customerCode}} { "sendCompletionEmail": true, "ifNotExists": "ignore", "eventSource": "10739827-EsmInventoryQPipeline", "dataRecords": [ { "ItemNo": "645779", "IsActive": false, "RequestDateTime": "2021-07-14T17:11:50.9346277-04:00" }, { "ItemNo": "645780", "IsActive": false, "RequestDateTime": "2021-07-14T17:11:50.9346277-04:00" }, { "ItemNo": "100495", "IsActive": false, "RequestDateTime": "2021-07-14T17:11:50.9346277-04:00" }, { "ItemNo": "100497", "IsActive": false, "RequestDateTime": "2021-07-14T17:11:50.9346277-04:00" } ] } ``` #### Removing Master Data Records (Synchronously) Endpoint: ``` DELETE {{baseURL}}/vue/_api/v1/collections/{{collectionCode}}/records/_async ``` | Property | Description | | --- | --- | | ifNotExists | `ignore` - if the specified identifier does not exist, ignore. `fail` - if the specified identifier does not exist, fail. | Example: ```http DELETE {{baseURL}}/vue/_api/v1/collections/customer/records/_async Content-Type: application/json Authorization: Bearer {{apiKey}} X-Customer-Code: {{customerCode}} { "sendCompletionEmail": true, "ifNotExists": "fail", "dataRecordIDs": [ "45010000182", "45010000189" ] } ``` #### Removing all Master Data Records (Synchronously) The following endpoint removes all records from a collection. Endpoint: ``` POST {{baseURL}}/vue/_api/v1/collections/{{collectionCode}}/_truncate ``` Example: ```http POST {{baseURL}}/vue/_api/v1/collections/customer/_truncate Content-Type: application/json Authorization: Bearer {{apiKey}} X-Customer-Code: {{customerCode}} ```  ## Creating and removing Transactional Data Records Transactional Data Records will always reside in a Data Table inside Conscia Intel. Delimited or [NDJSON](http://ndjson.org/) files can be used. For this POC, we have set up the Data Tables for comma-delimited files. Files may or may not be gzipped. Note: Transactional Data Records cannot be updated. Full time-based partitions of data may be removed. The following Data Buckets and Data Tables will be setup for this POC. | Incoming transactions | `dataBucketCode` | `dataTableCode` | | --- | --- | --- | | transaction_header | transaction | txn_header | | transaction_detail | transaction | txn_detail | ### Asynchronous Webservices  #### Removing Transactional Data Records ```http DELETE {{baseURL}}/vue/_api/v1/data-buckets/{{dataBuckerCode}}/data-tables/{{dataTableCode}}/records/_async Content-Type: application/json Authorization: Bearer {{apiKey}} X-Customer-Code: {{customerCode}} { "sendCompletionEmail": true, "partition": "2021-07-14T17:00:00" // July 14, 2021 between 5pm and 6pm EST } ``` ### File Uploads #### Uploading transactional data Endpoint: ``` POST {{baseURL}}/fs/customers/{{customerCode}}/meshKey/{{meshKey}}/buckets/{{dataBucketCode}}/upload?folder={{dataTableCode}} ``` Example: ```bash curl -F files[]=@transaction_header_201901.csv.gz --url "{{baseURL}}/fs/customers/{{customerCode}}/meshKey/{{meshKey}}/buckets/transaction/upload?folder=txn_header" -H "Authorization: Bearer {{apiKey}}" ``` #### Verifying file upload success The following request lists all the files uploaded to the Intel Data Bucket. It can be used to verify that the Data File was uploaded successfully. ```http POST {{baseUrl}}/graphql Content-Type: application/json Authorization: Bearer {{apiKey}} { "query": "query ($input: ListDataFilesInput) { listDataFiles(input: $input) { name lastModified size }}", "variables":{ "input":{ "customerCode":"{{customerCode}}", "meshKey":"{{meshKey}}", "dataBucketCode":"{{dataBucketCode}}" } } } ``` ## Checking job status of an asynchronous webservice ```http GET {{baseURL}}/vue/_api/v1/jobs/{{jobID}}/status Content-Type: application/json Authorization: Bearer {{apiKey}} X-Customer-Code: {{customerCode}} ``` ## Additional Notes Please note that we are not using Track API for this POC. Track API can be used when you are logging one event at a time. ## Querying Collections Endpoint: ``` POST {{baseUrl}}/vue/_api/v1/collections/{{collectionCode}}/records/_query ``` Example: ```http POST {{baseUrl}}/vue/_api/v1/collections/customer/records/_query Content-Type: application/json Authorization: Bearer {{apiKey}} X-Customer-Code: {{customerCode}} { "limit": 10 } ``` ```http POST {{baseUrl}}/vue/_api/v1/collections/customer/records/_query Content-Type: application/json Authorization: Bearer {{apiKey}} X-Customer-Code: {{customerCode}} { "filter": { "$eq": { "field": "cardnumber", "value": "45170793389" } } } ```