EG: Cleanup LeadConduit CData Sync connections

jesusitd commented 4 years ago

Targets:

TAKE OUT

5 Connections
Migrate Jobs to 1 Connection using Schemas
Change to REST?

Tables Task List:

entities - JOB LC-Entities - CONN LC-Entities
entities_flows - JOB LC-Entities - CONN LC-Entities
entities_modules - JOB LC-Entities - CONN LC-Entities
event_statistics - JOB LC-Events-Stats - CONN LC-Events-Stats
events
fields - JOB LC-Fields - CONN LC-Fields
flow_fields- JOB LC-Flows - CONN LC-Flows
flow_sources- JOB LC-Flows - CONN LC-Flows
flow_steps- JOB LC-Flows - CONN LC-Flows
flows - JOB LC-Flows - CONN LC-Flows
leads
package_integrations - JOB LC-Packages - CONN LC-Packages
packages- JOB LC-Packages - CONN LC-Packages
sources- JOB LC-Flows - CONN LC-Flows

jesusitd commented 4 years ago

DEV ENVIRONMENT Progress:

entities schema:

Generated SQL and Schema for the 3 related tables. Row counts match, except for entities flows (2 rows diffence).

fields schema:

Generated SQL and Schema for the 1 related tables. Row counts match.

flows schema:

Generated Schema. 5 related tables.
FLOWS query completely overhauled
Cannot grab api_data_json column -> Support
SOURCES query replicated
FLOW_SOURCES replicated no issues
FLOW STEPS, rewrote the query a bit, no issues

NEW flow_fields schema

FLOW FIELDS query needed separate schema and finally works well. Not elegant

packages schema:

Generated SQL and Schema
PACKAGES QUERY done. No issues. Row counts match.
PACKAGES INTEGRATION QUERY done. No issues. Bettered structure too. Row counts match. MAY GRAB RAW RESPONSE/REQUEST VARS

events-statiscts schema:

Generated SQL and Schema
Events-Statistics is now completely achieved using CDATA SYNC, with no Python assistance :) 👍

events schema:

Generated SQL. Generated Schema
Working on Custom pagination. ~~Reach out to CData Support~~, ~~waiting on CData response~~ received response / installing
DROPPED Version column

leads schema

Edited same Events Schema. Modified to add all missing columns
Strong modifications to SQL REPLICATE statement
Added VARS.LEAD OBJECT OF THE API RESPONSE AS AN AGGREGATE

jesusitd commented 4 years ago

PRODUCTION ENVIRONMENT Integrations Moving Log:

fields

Modified SQL table: Added aggregate field, took defaults out for created_at and updated_at, changed field_id length to 255 varchar
Decommissioned old Job "LC-Fields"
Running in production

entities

Job LC_Entities
Entities SQL table adjusted. Running entities query
entities_flows query running smoothly. No schema change
entities_modules query running smoothly. No schema change
Decommissioned old Job "LC-Entities"
Running in production

packages

Job LC_Packages
packages query running smoothly. No schema change
package_integrations query running smoothly. Added tag column
Decommissioned old Job "LC-Packages"
Running in production

flows

Job LC_Flows
flows query running smoothly. Added missing columns
sources query running smoothly. No schema change
flow_sources query running smoothly. No schema change
flow_steps query running smoothly. No schema change
flow_fields query running smoothly. No schema change
Decommissioned old Job "LC-Flows"
Running in production

events_stats

Job LC_Events_Stats
events_stats query running smoothly. No schema change
Decommissioned in server:
Set up to run every 20 mins in CData Sync

events

Must backup data before - Tolled
SQL schemas are identical
For timestamp, Pre-Job events has a very odd format 'r' Tue,+04+Aug+2020+11:11:04+GMT that works just fine in LeadConduit (tested in Postman)
<api:set attr="out.env:last2hours" value="[null | now() | dateadd('hour', '-2', 'r')]" />
Scheduled every 20 mins and Decommissioned in refreshes server:

leads

Backed data up in CSV
SQL Schema added api_data_json
Configured job LC_Leads
Decommissioned in refreshes server:
Scheduled new to every 20 mins

jesusitd commented 4 years ago

Jobs Final Verification:

LC_Fields

Group	Item	OK?
REPLICATE	Complete Fields	👍
REPLICATE	Pointing to right table	👍
OLD JOB	Disabled	👍
SCHEDULE	Set up?	👍
LOGGING	Set to info?	👍
Events	Set up?	N/A
Advanced	Truncate Table?	YES
Advanced	Drop Table Disabled?	👍
Advanced	Alter Schema Disabled?	👍

LC_Entities

Group	Item	OK?
REPLICATE	Complete Fields	👍
REPLICATE	Pointing to right table	👍
OLD JOB	Disabled	👍
SCHEDULE	Set up?	👍
LOGGING	Set to info?	👍
Events	Set up?	N/A
Advanced	Truncate Table?	YES
Advanced	Drop Table Disabled?	👍
Advanced	Alter Schema Disabled?	👍

LC_Packages

Group	Item	OK?
REPLICATE	Complete Fields	👍
REPLICATE	Pointing to right table	👍
OLD JOB	Disabled	👍
SCHEDULE	Set up?	👍
LOGGING	Set to info?	👍
Events	Set up?	N/A
Advanced	Truncate Table?	YES
Advanced	Drop Table Disabled?	👍
Advanced	Alter Schema Disabled?	👍

LC_Flows

Group	Item	OK?
REPLICATE	Complete Fields	👍
REPLICATE	Pointing to right table	👍
OLD JOB	Disabled	👍
SCHEDULE	Set up?	👍
LOGGING	Set to info?	👍
Events	Set up?	N/A
Advanced	Truncate Table?	YES
Advanced	Drop Table Disabled?	👍
Advanced	Alter Schema Disabled?	👍

LC_Events_Stats

Group	Item	OK?
REPLICATE	Complete Fields	👍
REPLICATE	Pointing to right table	👍
OLD JOB	Disabled	👍
SCHEDULE	Set up?	👍
LOGGING	Set to info?	👍
Events	Set up?	👍
Advanced	Truncate Table?	NO
Advanced	Drop Table Disabled?	👍
Advanced	Alter Schema Disabled?	👍

LC_Events

Group	Item	OK?
REPLICATE	Complete Fields	👍
REPLICATE	Pointing to right table	👍
OLD JOB	Disabled	👍
SCHEDULE	Set up?	👍
LOGGING	Set to info?	👍
Events	Set up?	👍
Advanced	Truncate Table?	NO
Advanced	Drop Table Disabled?	👍
Advanced	Alter Schema Disabled?	👍

LC_Leads

Group	Item	OK?
REPLICATE	Complete Fields	👍
REPLICATE	Pointing to right table	👍
OLD JOB	Disabled	👍
SCHEDULE	Set up?	👍
LOGGING	Set to info?	👍
Events	Set up?	👍
Advanced	Truncate Table?	NO
Advanced	Drop Table Disabled?	👍
Advanced	Alter Schema Disabled?	👍

JOBS SETUP COMPLETE 🥇

jesusitd commented 4 years ago

Backup task:

ZIP File in Power BI SharePoint: 2020-08-05 Old Config Backup Before new REST connector.zip https://intakedesk.sharepoint.com/:f:/r/sites/power-bi-docs/Power%20BI%20Desktop%20Files/-%20Documents/CData%20Sync%20LeadConduit?csf=1&web=1&e=wvtZ1h

Backed up:

Connections JSON
Jobs JSON
CentOS folders with (job.cfg, job.sql, pre-job.rsb, post-job.rsb)
GitHub repo py-cdata-sync

jesusitd commented 4 years ago

Final Deletion of Old Jobs and Connections:

Deleted 7 Jobs

LC-Fields
LC-Flows
LC-Entities
LC-Packages
LC-Events
LC-Events-Stats
LC-Leads

Deleted 6 Connections:

LC-Fields
LC-Flows
LC-Entities
LC-Packages
LC-Events
LC-Events-Stats

Saved 5 connection Slots with new LeadConduit-REST connection 🥇

jesusitd commented 4 years ago

Done. CC @fedegarza @CesarITD

Follow-up tasks:

Monitor behavior during next weeks
Generate Documentation (ticket created) https://github.com/intakedesk/py-cdata-sync/issues/1

intakedesk / PowerBI-General

EG: Cleanup LeadConduit CData Sync connections #573