Querying Daton created child tables for nested data
Daton creates separate 'child' tables for each nested data present in source schema(or parent table). Users can control this behavior using the advanced loading options during the source configuration.
For example, if data is present in Listrecommendations.Fulfillmentrecommendations.Member.Itemdimensions.Dimensiondescriptionthen the following tables would be created -
- 1.Listrecommendations (Parent Table)
- 2.Listrecommendations_Fulfillmentrecommendations (Level 1 Child)
- 3.Listrecommendations_Fulfillmentrecommendations_Member (Level 2 Child)
- 4.Listrecommendations_Fulfillmentrecommendations_Member_Itemdimensions (Level 3 Child)
Any nested data present in Itemdimesions would be stored at text and any excess data which does not fit into that field would be truncated.
Data between parent and child tables can be mapped or combined using daton_parent_batch_id and daton_batch_runtime meta fields. The same applies for other levels of child tables.
For example, if API response for ListOrderItems contains data in the following structure
Column Name | Datatype |
AmazonOrderId | STRING |
ASIN | STRING |
SellerSKU | STRING |
OrderItemId | STRING |
ItemPrice | RECORD |
ItemPrice.CurrencyCode | STRING |
ItemPrice.Amount | STRING |
then the following tables would be created by Daton
Table 1: ListOrderItem (Parent Table)Column Name | Datatype |
AmazonOrderId | STRING |
ASIN | STRING |
SellerSKU | STRING |
OrderItemId | STRING |
daton_user_id | NUMERIC |
daton_batch_runtime | NUMERIC |
daton_batch_id | NUMERIC |
Table 2: ListOrderItem_ItemPrice (Level 1 Child Table)Column Name | Datatype |
CurrencyCode | STRING |
Amount | STRING |
daton_user_id | NUMERIC |
daton_batch_runtime | NUMERIC |
daton_batch_id | NUMERIC |
daton_parent_batch_id | NUMERIC |
In order to fetch Item price for an AmazonOrderId, daton_batch_id and daton_batch_runtime must be used for mapping records
select Amount from listorderitems_itemprice
where
daton_parent_batch_id = (select daton_batch_id from mws_listorderitems where amazonorderid = 'XXX-7370285-XXXX')
and
daton_batch_runtime = (select daton_batch_runtime from mws_listorderitems where amazonorderid = 'XXX-7370285-XXXX')
Last modified 2yr ago