- 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 connector for connected app.
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:
- Perform the following actions:
Alternative: Free API Connector
In addition to this native connector, Etlworks also offers a generic API Connector that can be used to connect to this platform via its public API. The API connector is free and is a good alternative when the native connector doesn’t support a specific feature or if you prefer working directly with the API.
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
Note: Extracting data from Amazon Marketplace is similar toextracting 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 connected app, 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.
Supported objects and actions
The Amazon Marketplace connector exposes Selling Partner API (SP-API) data as relational tables and stored procedures. Browse the schema in Etlworks Explorer, or use the lists below as a starting point.
Data objects (tables)
- CatalogItems
- Feeds
- InboundShipmentItems
- InboundShipments
- InventorySupply
- OrderItems
- Orders
- ReportList
Stored procedures (actions)
- CancelFeed
- CancelReport
- GetReport
- RequestReport
- SubmitImageFeed
- SubmitInventoryFeed
- SubmitOrderAcknowledgementFeed
- SubmitOrderAdjustmentFeed
- SubmitOrderFulfillmentFeed
- SubmitOverrideFeed
- SubmitPriceFeed
- SubmitProductFeed
- SubmitRelationshipFeed
- SubmitSourcingOnDemandFeed
Note: MWS Feeds API is deprecated
Etlworks previously included a separate MWS Feeds API connector. Amazon has deprecated the MWS API in favor of the Selling Partner API (SP-API). The MWS connector is no longer available when creating new connections; use this Amazon Marketplace connector instead.