I need to work on a query that will fetch results of join between two temp datasets that are not profile enabled then use that resultset to insert into profile enabled dataset. There is minimal mention in the documentation which doesn't show nested objects.
E.g. Query I tried below did not work
Path of fields in Schema structure is like the below
_Tenant.ProfileAttributes.address.city
_Tenant.ProfileAttributes.role
_Tenant.Identity.customerID
The query that I tried is as below
INSERT INTO final_dataset
select struct
(
city as city,
role as role,
customerID as customerID
) _Tenant
From
(
select a._Tenant.ProfileAttributes.address.city as city
b._Tenant.ProfileAttributes.role as role
b._Tenant.Identity.customerID as customerID
from address a, profileDetails b
where a._Tenant.Identity.customerID = b._Tenant.Identity.customerID
)
any idea how to achieve the successful insertion.
Solved! Go to Solution.
Views
Replies
Total Likes
Hi @vkt1989 ,
I tried to run below query successfully without any issue in our environment. please change field/object names according to your need and try.
58000 is related to "Internal system failure" , I believe it not issue with struct query construction.
_tenant.contactDetails.homePhone.type
_tenant.contactDetails.personalEmail
_tenant.custIdentities.epsId
SELECT struct(struct(struct(type as type)homePhone,personalEmail as personalEmail)contactDetails,struct(epsId as epsId)custIdentities)_tenant
from (select _tenant.custIdentities.epsId ,_tenant.contactDetails.homePhone.type as type,_tenant.contactDetails.personalEmail as personalEmail
from sample_ds)
Thanks,
Anu
Hi @vkt1989,
What is the error that you are seeing?
Based on your query (select struct), its creating as _Tenant.city, _Tenant.role, _Tenant.customerID and this is not matching with your Target schema - and probably the reason for error.
Did you try in this way?
select struct
(
city as "ProfileAttributes.address.city",
role as "ProfileAttributes.role",
customerID as "Identity.customerID"
) _Tenant
Try and let me know if that works fine?
Regards,
Chetanya
Views
Replies
Total Likes
I tried this already but it did not work. Receiving the error in QS " [Errors(Some(INGEST-1207-400),Some(List()),Some(A required field _Tenant.Identity of type: object is missing from the input data. Add the required field and try again.))]"
At the dataset level. I see the below errors.
A required field _Tenant.Identity of type: object is missing from the input data. Add the required field and try again.
The field: _Tenant.ProfileAttributes.address.city does not exist in the target schema. Remove the field and try again.
The field: _Tenant.ProfileAttributes.role does not exist in the target schema. Remove the field and try again.
The field: _Tenant.Identity.customerID does not exist in the target schema. Remove the field and try again.
When we insert the data at root level attributes, it works but when we try to ingest with nested objects it doesn't work. Adobe's documentation doesn't have information on how to work with nested objects. SQL doesn't support dot(.) in columns so we having an issue here.
Views
Replies
Total Likes
@vkt1989 What about something like this:
select struct( 'NY' as city) "_Tenant.ProfileAttributes.address",
struct('VP' as role) "_Tenant.ProfileAttributes",
struct('123' as customerID) "_Tenant"
OR
select struct( 'NY' as city) "_Tenant.ProfileAttributes.address.city",
struct('VP' as role) "_Tenant.ProfileAttributes.role",
struct('123' as customerID) "_Tenant.customerID"
Views
Replies
Total Likes
Not working as dot(.) not allowed while defining struct ( "_Tenant.customerID").
Error : Column names can only contain alphanumeric or underscore characters]]]
Views
Replies
Total Likes
Hi @vkt1989 ,
Try this.
SELECT struct(struct(struct(city as city)address, role as role)ProfileAttributes, struct(customerID as customerID)Identity)_Tenant
Thanks,
Anu
Views
Replies
Total Likes
Not sure why but always get a job aborted error when I try nested struct.
ErrorCode: 58000 Batch query execution gets : [failed reason ErrorCode: 58000 Batch query execution gets : [Failed to get results of query Reason: [Job aborted.]]]
Views
Replies
Total Likes
Hi @vkt1989 ,
I tried to run below query successfully without any issue in our environment. please change field/object names according to your need and try.
58000 is related to "Internal system failure" , I believe it not issue with struct query construction.
_tenant.contactDetails.homePhone.type
_tenant.contactDetails.personalEmail
_tenant.custIdentities.epsId
SELECT struct(struct(struct(type as type)homePhone,personalEmail as personalEmail)contactDetails,struct(epsId as epsId)custIdentities)_tenant
from (select _tenant.custIdentities.epsId ,_tenant.contactDetails.homePhone.type as type,_tenant.contactDetails.personalEmail as personalEmail
from sample_ds)
Thanks,
Anu
Thanks @Anuhya-Y. The structure worked.
Views
Replies
Total Likes
I am partially able to update objects that are 1 level below the tenant but still 2nd level nested objects unable to update as well as individual fields under the objects. Here is the working query for objects update except for nested 'address' object
INSERT INTO final_dataset
select struct (
ProfileAttributes as ProfileAttributes,
Identity as Identity ) _Tenant
From (
select b._Tenant.ProfileAttributes as ProfileAttributes,
a._Tenant.Identity as Identity
from address a, profileDetails b
where a._Tenant.Identity.customerID = b._Tenant.Identity.customerID )
Views
Likes
Replies