Insert Into Adobe Experience Platfrom (AEP) Query Guidance | Community
Skip to main content
S
SaurabhCh3
New Participant
December 9, 2024
Solved

Insert Into Adobe Experience Platfrom (AEP) Query Guidance

  • December 9, 2024
  • 4 replies
  • 1843 views

Hello community,

I am trying to use insert into sql command in AEP query services, my end goal is to create a CTAS schedule queries to ingest data from one dataset to another dataset for a specific phone number column. 

Dataset A has a schema field '_abc.phone.number.number' which has data, Dataset B has a schema field '_abc.uGuestPrimary.phone.number' but is empty. 

I am using below query:

 

INSERT INTO Dataset B

SELECT struct(_abc as _abc, phone as uGuestPrimary, number as phone, number as number) _abc FROM Dataset A

 

I am getting following error:
ErrorCode: 58000 Batch query execution gets : [failed reason ErrorCode: 58000 Batch query execution gets : [Analysis error encountered. Reason: [sessionId: 8bed5f80-d51-bb39-090286252d53 queryId: b26abfc3-1a62ab-627fd6504350 Column phone does not exist.]]]


I am using this document for reference:
https://experienceleague.adobe.com/en/docs/experience-platform/query/sql/syntax#select-queries

 

What could be wrong in my query?

I also tried using last column where I need the data, getting same error.

INSERT INTO Dataset B
SELECT struct(number as number) _abc FROM Dataset A

 

Thanks all for your time.

Best answer by brekrut

Hello @saurabhch3 

 

if you have data in dataSet A under the path of _abc.phone.number.number

 

I would create an a query similar to the following.  Each object should be represented by a struct.

 

insert into dataSetB

select struct( 

             struct(

              struct(

          (x.number as number)

                         ) as phone

                         ) as uGuestPrimary

                          )as _abc

            

from ( 

select a._abc.phone.number.number as number

from dataSetA a) x

4 replies

brekrut
brekrutAccepted solution
Employee
December 17, 2024

Hello @saurabhch3 

 

if you have data in dataSet A under the path of _abc.phone.number.number

 

I would create an a query similar to the following.  Each object should be represented by a struct.

 

insert into dataSetB

select struct( 

             struct(

              struct(

          (x.number as number)

                         ) as phone

                         ) as uGuestPrimary

                          )as _abc

            

from ( 

select a._abc.phone.number.number as number

from dataSetA a) x

    B
    BitunSenAEP
    New Participant
    December 17, 2024

    @saurabhch3 - From the path of your phone field (_abc.uGuestPrimary.phone.number), what I can understand is that you have an object "uGuestPrimary" as an object which has another object called "phone". This "phone" object has a field "number". So, for this, you need to create internal objects using respective struct. For example:

     

    select struct(struct(sturct(_abc.phone.number.number as number) as phone) as uGuestPrimary) as _abc from dataset_a

     

    Hope this helps.

     

    Thanks, 

    Bitun 

     

    gkalyan
    gkalyan
    New Participant
    December 12, 2024

    @saurabhch3 

    I guess you already confirmed "phone" column exists in the dataset schema.

     

    If there is a row which does not meet the criteria of the column, all records for the batch will not be inserted into the dataSet. So, I would suggest to look into the dataset with valid rows, otherwise try to run this for a sample data set where you know valid data is available.

      kautuk_sahni
      kautuk_sahni
      Employee
      December 12, 2024

      @abhinavbalooni  @kumar29917170hcyp  @ankit_chaudhary  @pradeep_kumar_srivastav  @dwright-adobe  @gkalyan  @jason_egan  @narendragandhi  @mittalabhi86-1  @dhaneshsh2 Kindly take a moment to review this question and share your valuable insights. Your expertise would be greatly appreciated!

      Kautuk Sahni
      Terms & ConditionsAccessibility statement

      Loading footer...