Amazon Marketplace connector

• Startup
• Business
• Enterprise
• On-Premise
• Add-on

Overview

Etlworks Amazon Marketplace connector allows fast, real-time access to Selling Partner API (SP-API) and MWS API (deprecated). The connector works just like any other database connector.

Etlworks partnered with CData to provide access to the MWS API using industry-standard JDBC protocol. Read about CData Amazon Marketplace JDBC connector.

When to use Amazon Marketplace connector

Use Amazon Marketplace connector to:

• Extract data from the following data objects:
  •  CatalogItems
  •  Feeds
  •  InboundShipmentItems
  •  InboundShipments
  •  InventorySupply
  •  OrderItems
  •  Orders
  •  ReportList

• Perform the following actions:
  •  CancelFeed
  •  CancelReport
  •  GetReport
  •  RequestReport
  •  SubmitImageFeed
  •  SubmitInventoryFeed
  •  SubmitOrderAcknowledgementFeed
  •  SubmitOrderAdjustmentFeed
  •  SubmitOrderFulfillmentFeed
  •  SubmitOverrideFeed
  •  SubmitPriceFeed
  •  SubmitProductFeed
  •  SubmitRelationshipFeed
  •  SubmitSourcingOnDemandFeed

Prerequisites

Enable Amazon Marketplace connector for your Etlworks account

Contact support@etlworks.com to enable connector.

Create a connection

Step 1. In the Connections window, click +, and type in Marketplace. Select Amazon Marketplace.

Step 2. Configure Connection parameters

Connection parameters

• Marketplace: select one of the available regional Marketplaces. 
• Amazon Marketplace API: select Selling Partner API (SP-API). MWS API is still supported by the connector but deprecated by Amazon.
• OAUth Token: click Sign in with Amazon to authorize the connection to access Amazon Marketplace.

Use Other parameters to specify the Connection string options. Read about available Connection string options.

Work with Amazon Marketplace

Amazon Marketplace data model

Read about the Amazon Marketplace data model (SP-API). 

Stored procedures

Stored Procedures are available to complement the data available from the Data Model. Sometimes, it is necessary to update the data available from a view using a stored procedure because the data does not provide direct, table-like, two-way updates. In these situations, the retrieval of the data is done using the appropriate view or table, while the update is done by calling a stored procedure. Stored procedures take a list of parameters and return back a dataset that contains the collection of tuples that constitute the response.

Read about available stored procedures. 

To call stored procure from the SQL flow or from Before/After SQL, use EXEC sp_name params=value syntax. Example:

EXEC GetReport ReportId = 'id', DownloadPath = '{app.data}/report.dat'

Extract data from Amazon Marketplace

Extracting data from Amazon Marketplace is similar to extracting data from the relational database.

Step 1. Create an Amazon Marketplace Connection which will be used as a source (FROM).

Step 2. Create a destination Connection, for example, a Connection to the relational database, and if needed a Format (Format is not needed if the destination is a database or well-known API).

Step 3. Create a Flow where the source is a database and the destination is a Connection created in step 2, for example, a relational database.

Step 4. Add a new source-to-destination transformation.

Step 5. Select the Amazon Marketplace Connection created in step 1 as a source Connection and select the data object you are extracting data from.

Step 6. Select TO Connection, Format (if needed), and object (for example database table) to load data into.

Step 7. Click MAPPING and optionally enter Source Query (you don't need a query if you are extracting data from the Amazon Marketplace data object unconditionally).

Step 8. Optionally define the per-field Mapping.

Step 9. Add more transformations if needed.

Return data for the specific report

Step 1. Get the report ID. You can get it by viewing the ReportList data object in Etlworks Explorer.

Step 2. Create SQL Flow.

Step 3. Use the following SQL to get the report data:

EXEC GetReport ReportId = 'report_id', DownloadPath = '{app.data}/report.dat'

Use any filename as a part of the DownloadPath parameter. Use token {app.data} to download report data into the server storage associated with your Etlworks account.

Read more about GetReport stored procedure.

Submit Feed using Amazon Marketplace Connector

Step 1. Create Any to Any ETL Flow.

Step 2. Add source-to-destination transformation where the source is your source for the feed and the destination is Amazon Marketplace Connection.

Look at the examples of the INSERT statements below to set the destination object name (TO) and define the Mapping:

•  Image Feed - ImageFeedAggregate#TEMP
•  Inventory Feed - InventoryFeed#TEMP
•  Order Acknowledgement Feed - OrderAcknowledgementFeedAggregate#TEMP
•  Order Adjustment Feed - ItemPriceAdjustmentsAggregate#TEMP
•  Order Fulfillment Feed - ItemAggregate#TEMP
•  Override Feed - ShippingOverrideAggregate#TEMP
•  Price Feed - PriceFeedAggregate#TEMP
•  Product Feed - ProductDescriptionDataAggregate#TEMP
•  Relationship Feed - RelationAggregate#TEMP
•  Sourcing On-Demand Feed - OrderSourcingOnDemandFeedAggregate#TEMP

Example for pricing feed:

Step 3. Execute the appropriate stored procedure for the specific feed in the After SQL. See EXEC FeedNameFeed examples below:

• Image Feed
• Inventory Feed
• Order Acknowledgment Feed
• Order Adjustment Feed
• Order Fulfillment Feed
• Override Feed
• Price Feed
• Product Feed
• Relationship Feed
• Sourcing on Demand Feed

Example for pricing feed:

Browse data in Amazon Marketplace

You must have Amazon Marketplace Connection to browse objects, run SQL queries, and execute stored procedures.

Use Etlworks Explorer to browse data and metadata in Amazon Marketplace as well as execute DML and SELECT queries against Amazon Marketplace Connection.