Device entitlement report
This does not need to be published (Claro-only). Setting to internal rather than deleting.
Tenants parameter store configuration
Configuration is defined in the SSM parameter store. To create the parameter store, see the Amazon page: https://docs.aws.amazon.com/systems-manager/latest/userguide/parameter-create-console.html.
Two configurations are defined for each tenant:
- Tenants default configuration
- Tenants query level configuration
The prefix of the parameter store name should be /dwh/data-export/config/.
Name | type | Value |
|---|---|---|
| Tenants default configuration | ||
| /dwh/data-export/config/<tenant>/default_job_config | String | { "file": { "size": "1GB", "format": "CSV", "name": "device_entitlement", "delimiter": ";"}, "sftp_config_name": "default_sftp" } |
| Tenants queries level configuration | ||
| /dwh/data-export/config/<tenant>/<query name/id> | String | { "file": { "size": "1GB", "name": "device_entitlement", "delimiter": ";", "format": "CSV"}, "query_text": "select d1.casn as SMARTCARD_ID,e1.productid as PACKAGE_NO, TO_CHAR( e1.CreatedDateTime,'MM/DD/YYYY hh12:mi:ss AM') as OPERATE_DATE from entitlement e1,device d1 where e1.accountid=d1.accountid and e1.operatorid=d1.operatorid and e1.operatorid= 'claro' and d1.operatorid= 'claro' and d1.modifieddatetime = (select max(d2.modifieddatetime) FROM device d2 WHERE d2.deviceid = d1.deviceid and d2.operatorid=d1.operatorid) and e1.modifieddatetime = (select max(e2.modifieddatetime) FROM entitlement e2 WHERE e2.entitlementid = e1.entitlementid and e1.operatorid=e2.operatorid and e2.CreatedDateTime >= dateadd(day,-1,'today') and e2.CreatedDateTime < dateadd(day,0,'today'));" "color: rgb(22,25,31);">} |
Configuration of parameter store Tenants level query
select d1.casn as SMARTCARD_ID,e1.productid as PACKAGE_NO, TO_CHAR( e1.CreatedDateTime,'MM/DD/YYYY hh12:mi:ss AM') as OPERATE_DATE from entitlement e1,device d1
where e1.accountid=d1.accountid and e1.operatorid=d1.operatorid
and e1.operatorid= 'claro'
and d1.operatorid= 'claro'
and d1.modifieddatetime = (select max(d2.modifieddatetime)
FROM device d2
WHERE d2.deviceid = d1.deviceid and d2.operatorid=d1.operatorid)
and e1.modifieddatetime = (select max(e2.modifieddatetime)
FROM entitlement e2
WHERE e2.entitlementid = e1.entitlementid and e1.operatorid=e2.operatorid
and e2.CreatedDateTime >= dateadd(day,-1,'today') and e2.CreatedDateTime < dateadd(day,0,'today'));Cloud watch scheduling configuration
Cloud watch scheduling for each tenant is made at the query level. To create a rule that triggers on a regular schedule, see the Amazon page: https://docs.aws.amazon.com/AmazonCloudWatch/latest/events/Create-CloudWatch-Events-Scheduled-Rule.html. Follow steps 1 to 4, then:
- Select the target for the Lambda function created for the data extract, for example: nagra-dwh-upgrade-dev-test-data-dataExtractHandler-72GUG9U98AS6.
For Configure input, select Constant (JSON text). Provide the value as { "operator_id": "claro", "query_id": "deviceEntitlement_1" }; then follow the remaing steps in the page above.
Currently, the generated data extract file does not have the file extension and RedShift exports in parallel by default. Each slice exports its data.
UNLOAD writes data in parallel to multiple files, according to the number of slices in the cluster. The default option is ON or TRUE.
If PARALLEL is OFF or FALSE, UNLOAD writes to one or more data files serially, sorted absolutely according to the ORDER BY clause, if one is used. The maximum size for a data file is 6.2 GB. If, for example, you unload 13.4 GB of data, UNLOAD creates three files.
As new files are created after 6GB, suffix numbers are added.