Level 6

Solved

Keep only 7 days of data: memo field not recognized?

Forum|Forum|2 years ago
November 29, 2023
4 replies
1800 views

I've a tablet with data from 2 years ago, up to today. I've been tasked to remove all records, except for the last 7 days.

So my approache is:

1.- Create table (toy table to store last 7 days records).
2.- Query the original table, lets call it "A", to filter all records based on the created column that were created on 21/11 and after.
3.- Use an Update Activity to copy those records to toy table "abc", let's called it "B" .
4.- Truncate A, just to keep the headers.
5.- Copy data from B back to A, so it only has the last 7 days records.

This is the original XML schema from A:

<?xml version='1.0'?>
<schema _cs="Pipeline Event (acx)" created="2020-11-23 18:03:44.362Z" createdBy-id="1055"
        desc="Trigger data coming from Analytics through pipeline" entitySchema="xtk:schema"
        img="" implements="" label="Pipeline Event" lastModified="2023-11-14 13:48:28.230Z"
        mappingType="sql" md5="860DFE30574D2300D59CC3EC7B0FFF63" modifiedBy-id="2762680"
        name="pipelineEvent" namespace="acx" xtkschema="xtk:schema">
  <element autopk="true" desc="Trigger data coming from Analytics through pipeline"
           label="Pipeline Events" name="pipelineEvent" pkSequence="acxPipelineEventId"
           sqltable="AcxPipelineEvent">
    <compute-string expr="@id"/>
    <dbindex name="declared_id">
      <keyfield xpath="@declared_id"/>
    </dbindex>
    <dbindex name="timeGMT">
      <keyfield xpath="@timeGMT"/>
    </dbindex>
    <dbindex name="created">
      <keyfield xpath="@created"/>
    </dbindex>
    <dbindex name="id" unique="true">
      <keyfield xpath="@id"/>
    </dbindex>
    <key internal="true" name="id">
      <keyfield xpath="@id"/>
    </key>
    <dbindex name="recipientId">
      <keyfield xpath="@recipient-id"/>
    </dbindex>
    <attribute desc="Internal primary key" label="Primary key" name="id" sqlname="iPipelineEventId"
               type="long"/>
    <attribute desc="time the event was created in Campaign" label="Creation date"
               name="created" sqlDefault="GetDate()" sqlname="tsCreated" type="datetime"/>
    <attribute desc="last the event was modified in Campaign" label="Modification date"
               name="lastModified" sqlname="tsLastModified" type="datetime"/>
    <attribute desc="time of the original event, coming from Analytics, in the timeGMT attribute"
               label="timeGMT" name="timeGMT" sqlname="tsTimeGMT" type="datetime"/>
    <attribute desc="customer identifier, coming from Analytics, in a eVar" label="declared_id"
               length="32" name="declared_id" sqlname="sDeclared_id" type="string"/>
    <attribute desc="type of trigger, coming from the pipeline, in the triggerId attribute"
               length="50" name="triggerType" sqlname="sTriggerType" type="string"/>
    <attribute advanced="true" desc="full payload of the pipeline event" label="Trigger Data"
               name="data" sqlname="mData" type="memo"/>
    <element label="Recipient" name="recipient" pkgStatus="never" revCardinality="single"
             revLink="pipelineEvent" target="nms:recipient" type="link">
      <join xpath-dst="@id" xpath-src="@recipient-id"/>
    </element>
    <attribute advanced="true" label="Foreign key of the link 'Recipient' (field 'id')"
               name="recipient-id" sqlname="iRecipientId" type="long"/>
    <element advanced="true" integrity="neutral" label="Current operator" name="currentOperator"
             revLink="_NONE_" target="xtk:operator" type="link">
      <join xpath-dst="@id" xpath-src="$(loginId)"/>
    </element>
    <element integrity="neutral" label="Delivery being analyzed" name="currentDelivery"
             revLink="_NONE_" target="nms:delivery" type="link" visibleIf="NodeValue('/ignored/@currentDeliveryId')!=''">
      <join xpath-dst="@id" xpath-src="$long(@currentDeliveryId)"/>
    </element>
    <element applicableIf="HasPackage('nms:interaction')" integrity="neutral" label="Offer being processed"
             name="currentOffer" revLink="_NONE_" target="nms:offer" type="link"
             visibleIf="NodeValue('/ignored/@currentOfferId')!=''">
      <join xpath-dst="@id" xpath-src="$long(@currentOfferId)"/>
    </element>
    <element applicableIf="HasPackage('nms:centralLocal') and (@namespace!='nms' or @2175539!='delivery')"
             integrity="neutral" label="Current order" name="currentOrder" revLink="_NONE_"
             target="nms:localOrder" type="link" visibleIf="NodeValue('/ignored/@currentOrderId')!=''">
      <join xpath-dst="@id" xpath-src="$long(@currentOrderId)"/>
    </element>
  </element>
</schema>

Table B: XML: I just created the fields not the indexes since I don't need those... I think.

<srcSchema _cs="abc (cus)" created="2023-11-29 04:36:16.768Z" createdBy-id="0" entitySchema="xtk:srcSchema"
           img="xtk:schema.png" label="abc" lastModified="2023-11-29 04:52:48.015Z"
           mappingType="sql" md5="E3213537CADFEE06FE5232BE532B2A35" modifiedBy-id="0"
           name="abc" namespace="cus" xtkschema="xtk:srcSchema">
  <createdBy _cs="Omar Gonzales (ogonzales)"/>
  <modifiedBy _cs="Omar Gonzales (ogonzales)"/>
  <element autopk="true" label="abc" name="abc" pkSequence="cusAbcId">
    <attribute desc="time the event was created in Campaign" label="Creation date"
               name="created" sqlDefault="GetDate()" type="datetime"/>
    <attribute desc="last the event was modified in Campaign" label="Modification date"
               name="lastModified" type="datetime"/>
    <attribute desc="time of the original event, coming from Analytics, in the timeGMT attribute"
               label="timeGMT" name="timeGMT" type="datetime"/>
    <attribute desc="customer identifier, coming from Analytics, in a eVar" label="declared_id"
               length="32" name="declared_id" type="string"/>
    <attribute desc="type of trigger, coming from the pipeline, in the triggerId attribute"
               length="50" name="triggerType" type="string"/>
    <attribute advanced="true" desc="full payload of the pipeline event" label="Trigger Data"
               name="data" type="memo"/>
  </element>
</srcSchema>

However, in my Update activity for the workflow that will query A and keep only 7 days results, then update those to B... I cannot use the memo type column called: "Trigger Data @data)"... even when I see it in the structure of B (and of course A), I don't see it in the Update activity, not in the Destination field or the Source field.

Why?

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

Best answer by Manoj_Kumar

Hello @god_prophet

Here is a simple way of doing this:

In the query use the condition (this will give you all the records created before 7 days)

Created on or before Current date -7 days

Then use the update data activity and select the operation Delete.

Make sure the Targeting Dimesion is same in both activities.

Manoj_Kumar

Accepted solution

Community Advisor

Hello @god_prophet

Here is a simple way of doing this:

In the query use the condition (this will give you all the records created before 7 days)

Created on or before Current date -7 days

Then use the update data activity and select the operation Delete.

Make sure the Targeting Dimesion is same in both activities.

Manoj Find me on LinkedIn

G

god_prophetAuthor

Level 6

Hi @_manoj_kumar_ I did do this becayse there are 140 M of records to delete. And according to DB best practices, deleting that amount of records can cause other issues, like problems with indexing, etc.

Is this not the case with Adobe Campaign?

Manoj_Kumar

Community Advisor

Hello @god_prophet No, there won't be any issue.

Also, check the "Pipeline triggers purge" which does the same thing.

Manoj Find me on LinkedIn

ParthaSarathy

Community Advisor

Hi @god_prophet ,

For your use case, Instead of creating new schema and updating records, you can try and consider the below approach which will be a best solution,

Create a workflow:

Scheduler > Query > update data

Scheduler: schedule the workflow to run daily once

Query: Select Targeting and filtering dimension as table_A (where you want to delete older records and keep only 7 days record)

Filtering condition:

DateOnly(@created) on or before DateOnly(DaysAgo(7))

Update data:

Select operation as delete

Schema: Table_A

Reconciliation: Reconcile with Primary key

Now when the workflow runs daily, it will delete older records and keep only last 7 days record in it.

~ ParthaSarathy S~ Click here to join ADOBE CAMPAIGN USER GROUP for Quarterly In-person | Hybrid | Virtual Meetups

G

god_prophetAuthor

Level 6

Hi @parthasarathy I did do this becayse there are 140 M of records to delete. And according to DB best practices, deleting that amount of records can cause other issues, like problems with indexing, etc.

Is this not the case with Adobe Campaign?

G

god_prophetAuthor

Level 6

Hi @god_prophet ,

If you see an issue with DB Size after deleting this huge Data, please try dropping and recreating the index using SQL Code activity or when you modify the Schema you can get the Script in the Update Database Structure, so that you will not be facing any issue.

DROP INDEX {DBIndexSQLName};

CREATE INDEX {newIndexName} ON {sqlTableName}(sqlColnames);

Regards,

Pravallika.

Hi @lakshmipravallika ,

so according to the XML, this SQL would be:

DROP INDEX created; // or DROP INDEX AcxpipelineEvent created;

CREATE INDEX created ON pipelineEvent(sqlColnames);

What would be the specific command to targed the field created in pipelineEvent table?

Craig_Thonis

Adobe Employee

Hi ogonzalesdiaz,

Can you check that your advanced fields are enabled in the update activity (click the button with the three dots in the lower right-hand corner of the window in your screenshot)? Specific areas will be hidden unless that has been checked, and it appears to be contained in one screenshot but not the other.

Sukrity_Wadhwa

Community Manager

Hi @god_prophet,

Were you able to resolve this query with the help of the given solutions or do you still need more help here? Do let us know. In case the given solutions were helpful, then kindly choose the one that helped you the most as the 'Correct Reply'.
Thanks!

Sukrity Wadhwa

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded