Querying Daton created tables on RedShift

How to query RedShift tables

Querying Daton created tables on RedShift

RedShift data warehouse does not support nested tables. Hence, Daton creates separate 'child' tables for each nested data present in source schema(or parent table).

At present Daton creates separate child tables up to three levels.

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 Mapping

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 on RedShift

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')