Insert Into Adobe Experience Platfrom (AEP) Query Guidance

[SaurabhCh3]

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.

[kautuk_sahni]

@abhinavbalooni  @Kumar_Saurabh_  @Ankit_Chaudhary  @Pradeep_Kumar_Srivastav  @dwright  @gkalyan  @Jason_Egan  @narendragandhi  @mittalabhi86  @dhanesh04s Kindly take a moment to review this question and share your valuable insights. Your expertise would be greatly appreciated!

[gkalyan]

@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.

[BitunSenAEP]

@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 

[brekrut · Accepted solution]

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