About Amazon Marketplace connector
Etlworks Amazon Marketplace connector allows fast, real-time access to MWS API. 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:
- Orders
- Order Items
- Products
- Report List
- Inventory Supply
- Inbound Shipments
- Inbound Shipment Items
- Feed Submission List
- Feed Submission Result
- Perform the following actions:
Prerequisites
Configure access to Amazon Marketplace
Step 1. Log in to Amazon Seller Central and go to [User Permissions] under [Settings].
Step 2. In the [Amazon MWS Developer Permissions] section click [View your credentials] to retrieve their respective values:
- The AWS Access Key from the Amazon Marketplace web service settings.
- The AWS Secret Key from the Amazon Marketplace web service settings.
- The MWS Auth Token from the Amazon Marketplace web service settings.
- The Seller ID from the Amazon Marketplace web service settings.
- The Amazon Marketplace location (United States, Canada, Japan etc.).
Enable Amazon Marketplace connector for your Etlworks account
Contact support@etlworks.com to enable connector.
Creating a connection
Step 1. In the Connections, window click the + button, type in marketplace.
Step 2. Enter connection parameters
Connection parameters
- URL - the JDBC connection string which starts with
jdbc:amazonmarketplace:. The SellerId and Marketplace must point to the specific Seller ID schema and the comma-separated list of the marketplaces obtained when you configured access to the Amazon Marketplace - AWS Access Key - the access key obtained when you configured access to the Amazon Marketplace
- AWS Secret Key- the secret key obtained when you configured access to the Amazon Marketplace
- MWS Auth Token - the authentication token obtained when you configured access to the Amazon Marketplace
Read about available connection string options.
Connecting to Amazon Marketplace over SSH tunnel
If you need to access MWS API that can only be accessed via an SSH tunnel, you need to specify additional information in the "Connect over SSH tunnel" section of the connection screen.
- SSH Host - the name or IP address for the host accepting SSH connections.
- SSH Port - the port accepting SSH connections. The default value is 22.
- SSH User - the user name
- SSH Password - the optional password
- Private Key File - the private key file in the
pemorppkformat used for SSH authentication. Click the "Upload/Select ssh key" button to manage SSH keys using GUI. You can also upload the private key file manually and use the token{app.data}as a part of the filename, for example,{app.data}/keys/secret.pem. This parameter is optional.
- SSH Passphrase - the optional passphrase (password) used together with a private key file.
Working with Amazon Marketplace
Amazon Marketplace data model
Read about Amazon Marketplace data model.
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'Extracting data from Amazon Marketplace
extracting data from Amazon Marketplace is similar to extracting data from the relational database.
Step 1. Create 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 new source-to-destination transformation.
Step 5. Select 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 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
- 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:
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:
Submit Feed using MWS API Feed Connector
Alternatively, you can use MWS API Feed connector to submit the feed.
Cancel Feed
Step 1. Get the feed submission ID. You can obtain it by viewing the FeedSubmissionList data object in Explorer.
Step 2. Create SQL flow.
Step 3. Use the following SQL to cancel the specific feed:
EXEC CancelFeed FeedSubmissionId = 'submission_id'
Read more about CancelFeed stored procedure.
Browsing data in Amazon Marketplace
You must have Amazon Marketplace connection to browse objects, run SQL queries and execute stored procedures.
Use Explorer to browse data and metadata in Amazon Marketplace as well as execute DML and SELECT queries against Amazon Marketplace connection.
Comments
0 comments
Please sign in to leave a comment.