Hi,
I've a dataset which contains 51 columns but when I query my dataset in query service it return just 16 columns.
Column in dataset :
_id | endUserIDs._experience.emailid.id | web.webInteraction.URL |
application.launches.value | endUserIDs._experience.emailid.namespace.code | web.webInteraction.linkClicks.id |
channel._id | endUserIDs._experience.mcid.id | web.webInteraction.linkClicks.value |
channel._type | endUserIDs._experience.mcid.namespace.code | web.webInteraction.name |
channel.mediaAction | environment.duration | web.webInteraction.type |
channel.mediaType | eventType | web.webPageDetails.URL |
commerce.checkouts.value | identityMap | web.webPageDetails.isErrorPage |
commerce.order.currencyCode | marketing.campaignGroup | web.webPageDetails.name |
commerce.order.payments | marketing.campaignName | web.webPageDetails.pageViews.value |
commerce.order.priceTotal | marketing.trackingCode | web.webReferrer.URL |
commerce.order.purchaseID | placeContext.geo._id | web.webReferrer.type |
commerce.order.purchaseOrderNumber | placeContext.geo._schema.latitude | dataSource._id |
commerce.productListAdds.value | placeContext.geo._schema.longitude | dataSource.code |
commerce.productListViews.value | placeContext.geo.city | device.type |
commerce.productViews.value | productListItems | search.position |
commerce.purchases.value | search.isPaid | search.searchEngine |
commerce.saveForLaters.value | search.keywords | timestamp |
Get List of Columns :
SELECT *
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name = '<tablename>'
Query :
SELECT * FROM <TABLENAME>
Column return by query service/Query:
application | web |
eventType | placeContext |
channel | identityMap |
timestamp | marketing |
_id | environment |
productListItems | device |
commerce | search |
endUserIDs | dataSource |
I can see, query service ignores the column name having dot(.) or underscore(_). I checked with other SQL client like DbVisualizer and HeidiSQL. It returns 16 column only. Does it have any restriction at column naming convention?
Thanks.
Solved! Go to Solution.
Topics help categorize Community content and increase your ability to discover relevant content.
@vikash4 If fields are created directly under the root object of the schema (_tenantId), select * query will display all the columns. But when object/array type field is used in schema, nested columns of the object will not display with select * query.
AEP is supporting couple of functions which supports array type fields to unnest columns like explode, flatten, inline etc. check https://experienceleague.adobe.com/docs/experience-platform/query/sql/spark-sql-functions.html?lang=.... I have not come across function in AEP which can unnest/flatten object type filed.
Try to use inline function to expand array object column . Check https://experienceleague.adobe.com/docs/experience-platform/query/sql/syntax.html
Hi @Anuhya-Y ,
Thanks for your reply. As per document, inline function supports array element. I have different data model which does not have arrays. Also, I feel inline function would be more helpful when we know the column name in advance. So I'm getting error as type mismatch.
Thanks.
Views
Replies
Total Likes
@vikash4 If fields are created directly under the root object of the schema (_tenantId), select * query will display all the columns. But when object/array type field is used in schema, nested columns of the object will not display with select * query.
AEP is supporting couple of functions which supports array type fields to unnest columns like explode, flatten, inline etc. check https://experienceleague.adobe.com/docs/experience-platform/query/sql/spark-sql-functions.html?lang=.... I have not come across function in AEP which can unnest/flatten object type filed.
Views
Likes
Replies
Views
Likes
Replies
Views
Likes
Replies