This page provides a guide to Establishing a Connection to Amazon Marketplace in CData Cloud, as well as information on the available resources, and a reference to the available connection properties.

Connecting to Amazon Marketplace

Establishing a Connection shows how to authenticate to Amazon Marketplace and configure any necessary connection properties to create a database in CData Cloud

Accessing Data from CData Cloud Services

Accessing data from Amazon Marketplace through the available standard services and CData Cloud administration is documented in further details in the CData Cloud Documentation.

Connect to Amazon Marketplace by selecting the corresponding icon in the Database tab. Required properties are listed under Settings. The Advanced tab lists connection properties that are not typically required.

Connecting to Amazon Marketplace

The following properties are required:

• Schema: Set this to SellerCentral.
• InitiateOAuth: Set this to GETANDREFRESH.
• Marketplace: Set this to the Marketplace region that you are registered to sell in.

Authenticate to Amazon Marketplace

OAuth

Amazon Marketplace uses the OAuth authentication standard.

To authenticate using OAuth, you must either use the embedded application or create a new custom OAuth app. The embedded application supports desktop applications and headless machines. Web applications require that you create a custom OAuth application.

You can use a custom OAuth application to authenticate with a service account or a user account. See Creating a Custom OAuth App for more information.

Downloading Embedded Credentials

Because Amazon Marketplace requires that embedded credentials rotate every six months, CData credentials are hosted on oa.cdata.com. If you do not specify custom credentials, the embedded credentials are downloaded from our web service and saved in the location specified in OAuthClientLocation by default. NOTE: Make sure your firewall does not block oa.cdata.com.

For security, Amazon Marketplace restricts access to PII (Personally Identifiable Information). You can only retain PII for 30 days after order delivery and only for the purpose of, and as long as is necessary to

• fulfill orders
• calculate and remit taxes
• produce tax invoices
• meet legal requirements, including tax or regulatory requirements.

This means, for example, that the Amazon Marketplace driver no longer displays customer shipping address information after 30 days. See the Amazon documentation for more information:

Amazon Marketplace Data Protection Policy

Customizing the SSL Configuration

By default, the Cloud attempts to negotiate TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.

To specify another certificate, see the SSLServerCert connection property.

Connecting Through a Firewall or Proxy

HTTP Proxies

To authenticate to an HTTP proxy, set the following:

• ProxyServer: the hostname or IP address of the proxy server that you want to route HTTP traffic through.
• ProxyPort: the TCP port that the proxy server is running on.
• ProxyAuthScheme: the authentication method the Cloud uses when authenticating to the proxy server.
• ProxyUser: the username of a user account registered with the proxy server.
• ProxyPassword: the password associated with the ProxyUser.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

The CData Cloud models Amazon Marketplace objects as relational tables and views. An Amazon Marketplace object has relationships to other objects; in the tables, these relationships are expressed through foreign keys. The following sections show the available API objects and provide more information on executing SQL to Amazon Marketplace APIs.

Schemas for most database objects are defined in simple, text-based configuration files.

Using SellerCentral API

See Seller Central Data Model for the available entities in the Seller Central API.

Using VendorCentral API

See Vendor Central Data Model for the available entities in the Vendor Central API.

The CData Cloud models the Seller Central API as relational views, and stored procedures.

To use Amazon Seller Central Data Model, simply set Schema to SellerCentral.

Views

Views are tables that cannot be modified, such as Orders, OrderItems. Typically, data that are read-only and cannot be updated are shown as views.

Stored Procedures

Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.

Using Reports

For each report type there is a view exposed. For example, report type FEE_DISCOUNTS_REPORT will be exposed as a view named REPORT_FEE_DISCOUNTS_REPORT. These views can then be queried by using 'DataStartTime' and 'DataEndTime' optional datetime parameters. When both datetime parameters are specified, the driver automatically searches for an existing report that matches the specified interval, and if not found a new report is created. Reports can be manually created with the RequestReport stored procedure. You can also use ReportOptions JSON-aggregate pseudo-column to specify additional fields that may be required depending on report type. For more details about report options please check Amazon Selling-Partner API Documentation

After a report has been created and pushed to the result set, the next time you query this report type with the 'DataStartTime' and 'DataEndTime' same filters, the previously created report is downloaded instead of creating a new report.

The Cloud models the data in Amazon Marketplace as a list of tables in a relational database that can be queried using standard SQL statements.

CData Cloud - Amazon Marketplace Tables

Returns information about all destinations.

Columns

Retrieves all delivery window options for a shipment.

Columns

List the inbound compliance details for MSKUs in a given marketplace.

Columns

Retrieves a list of all packing options for an inbound plan.

Columns

Provides a list of all placement options for an inbound plan.

Columns

Provides a list of inbound plans with minimal information.

Columns

Retrieves a list of available self-ship appointment slots used to drop off a shipment at a warehouse.

Columns

Retrieve a paginated list of shipment content update previews for a given shipment.

Columns

Returns details about a listings item for a selling partner.

The following filter is required:

• SellerId: You can specify SellerId either as a criteria condition in WHERE filters or in the connection string.

SELECT * FROM ListingsItems WHERE SellerId = 'XXXXXXXXXXXXXX'

INSERT INTO ListingsItems (ProductType, Requirements, Attributes, SellerId)
VALUES ('product_type', 'LISTING', '{\"AttributeName\": \"test_attribute\", \"AttributeValue\": \"value\"}', '12345', 'XXXXXXXXXXXXXX')

DELETE FROM ListingsItems WHERE SellerId = 'XXXXXXXXXXXXXX'

INSERT INTO Attributes#TEMP (AttributeName, AttributeValue) VALUES ('attr1', 'val1')
INSERT INTO Attributes#TEMP (AttributeName, AttributeValue) VALUES ('attr2', 'val2')
INSERT INTO Attributes#TEMP (AttributeName, AttributeValue) VALUES ('attr3', 'val3')

INSERT INTO ListingsItems (ProductType, Requirements, Attributes, SellerId)
VALUES ('product_type', 'LISTING', 'Attributes#TEMP', '12345', 'XXXXXXXXXXXXXX')

*The temporary table must be defined and used within the same connection. Closing the connection will clear out any temporary tables in memory.

Columns

Returns details about a listings item attributes for a selling partner.

The following filters are required:

• SKU
• SellerId: You can either specify SellerId as a pseudo-column condition in WHERE filters, or in the connection string.

SELECT * FROM ListingsItemsAttributes WHERE SKU = '12345' AND SellerId = 'XXXXXXXXXXXXXX'
UPDATE ListingsItemsAttributes SET AttributeValue = 'test_value', ProductType = 'LUGGAGE'
       WHERE SKU = '12345' AND AttributeName = 'item_name_value'

SELECT * FROM ListingsItems WHERE SellerId = 'XXXXXXXXXXXXXX' AND SKU IN (
        SELECT DISTINCT(SKUIdentifierSellerSKU) FROM CatalogItems WHERE MarketplaceId = 'XXXXXXXXXXXXXX' AND Query = 'test' AND SkuIdentifierSellerSku IS NOT NULL
)

Columns

Returns a list of fulfillment orders fulfilled after (or at) a specified date-time, or indicated by the next token parameter.

Columns

Returns information about subscriptions of the specified notification type.

Columns

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

CData Cloud - Amazon Marketplace Views

The Catalog Items table helps you retrieve item details for items in the catalog.

The following filters are required:

• MarketplaceId
• One of the following: Query, SellerSKU, UPC, EAN, ISBN, JAN

SELECT * FROM CatalogItems WHERE MarketplaceID = 'XXXXXXXXXXXXX' AND ISBN = 'XXXXXXXXXXXXX'

SELECT * FROM CatalogItems WHERE MarketplaceID = 'XXXXXXXXXXXXX' AND IncludedData = 'attributes'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

The Catalog Items Classifications table helps you retrieve classification details for items in the catalog.

Columns

Returns competitive pricing information for a seller's offer listings based on seller SKU or ASIN.

Columns

The GetFeedSubmissionList operation returns a list of feed submissions.

Select

The Cloud will use the Amazon Marketplace API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.

Note: 'FeedType' attribute is required to query the view. You can view available values for 'FeedType' here.

• FeedId supports the '=' comparison.
• FeedType supports the '=' and 'IN' comparisons.
• MarketplaceIds supports the '=' and 'IN' comparisons.
• ProcessingStatus supports the '=' and 'IN' comparisons.
• CreatedTime supports the '=', '<', '>', '<=' and '>=' comparisons.

Following are example queries that are processed server side:

SELECT * FROM Feeds WHERE FeedId = '50950018754'

SELECT * FROM Feeds WHERE FeedType = 'POST_PRODUCT_PRICING_DATA'

SELECT * FROM Feeds WHERE FeedType IN ( 'POST_PRODUCT_PRICING_DATA', 'POST_INVENTORY_AVAILABILITY_DATA' )

SELECT * FROM Feeds WHERE FeedType = 'POST_PRODUCT_PRICING_DATA' AND MarketplaceIds = 'A1VC38T7YXB528'

SELECT * FROM Feeds WHERE FeedType = 'POST_PRODUCT_PRICING_DATA' AND ProcessingStatus = 'DONE'

SELECT * FROM Feeds WHERE FeedType = 'POST_PRODUCT_PRICING_DATA' AND CreatedTime > '2021-06-20' AND CreatedTime < '2021-08-01 12:00:00'

Note: When filtering with CreatedTime, values older than 90 days will not be accepted.

Columns

Returns the estimated fees for the listed products.

Columns

Provide delivery challan document for PCP transportation in India marketplace.

Columns

Gets the status of the processing of an asynchronous API call.

Columns

Retrieves a page of boxes from a given packing group.

Columns

Retrieves a page of items in a given packing group.

Columns

Provides a paginated list of box packages in an inbound plan.

Columns

Provides a paginated list of item packages in an inbound plan.

Columns

Provides a paginated list of pallet packages in an inbound plan.

Columns

Provides a paginated list of box packages in a shipment.

Columns

Returns a list of items in a specified inbound shipment.

Select

The Cloud will use the Amazon Marketplace API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.

• MarketplaceId supports the '=' comparison.
• ShipmentId supports the '=' comparison.
• LastUpdatedDate supports the '=', '<', '>', '<=', '>=' comparisons.

SELECT * FROM InboundShipmentItems  WHERE MarketplaceId = 'ATVPDKIKX0DER'

SELECT * FROM InboundShipmentItems  WHERE ShipmentId = 'FBA8J3K9LZPX'

SELECT * FROM InboundShipmentItems  WHERE LastUpdatedDate > '2020-01-01'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Provides a paginated list of pallet packages in a shipment.

Columns

Returns a list of inbound shipments based on criteria that you specify.

Select

The Cloud will use the Amazon Marketplace API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.

• ShipmentId supports the '=' and 'IN' comparisons.
• ShipmentStatus supports the '='and 'IN' comparisons.
• MarketplaceId supports the '=' comparison.
• LastUpdatedDate supports the '=', '<', '>', '<=', '>=' comparisons.

SELECT * FROM InboundShipments WHERE ShipmentStatus = 'Working'

SELECT * FROM InboundShipments WHERE ShipmentStatus IN ('Working', 'SHIPPED', 'IN_TRANSIT')

SELECT * FROM InboundShipments WHERE ShipmentId = '503-9993250-1405404'

SELECT * FROM InboundShipments WHERE MarketplaceId = 'ATVPDKIKX0DER'

SELECT * FROM InboundShipments WHERE LastUpdatedDate >= '2016-12-12'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Retrieves all transportation options for a shipment.

Columns

Returns information about the availability of inventory that a seller has in Amazon's fulfillment network and in current inbound shipments. You can check the current availability status for your Fulfillment by Amazon inventory as well as discover when availability status changes.

Select

The Cloud will use the Amazon Marketplace API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.

Note: Attributes 'GranularityType' and 'GranularityId' are required to query the view. You must set GranularityId to the MarketplaceId from the API matching your region.

• SellerSKU supports the '=', 'IN' comparisons.
• GranularityType supports the '='comparison.
• GranularityId supports the '=' comparison.
• MarketplaceId supports the '=' comparison.
• StartDateTime supports the '=', '<', '>', '<=', '>=' comparisons.

Following are example queries which are processed server side:

SELECT * FROM InventorySupply WHERE GranularityType = 'Marketplace' AND GranularityId = 'ATVPDKIKX0DER' AND SellerSKU = '123'

SELECT * FROM InventorySupply WHERE GranularityType = 'Marketplace' AND GranularityId = 'ATVPDKIKX0DER' AND StartDateTime > '2020-01-01'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Returns the lowest priced offers for a single item based on ASIN.

Columns

Generated schema file.

Columns

Returns details about a listings item issues for a selling partner.

The following filter is required:

• SellerId: You can specify SellerId either as a criteria condition in WHERE filters or in the connection string.

SELECT * FROM ListingsItemsIssues WHERE SellerId = 'XXXXXXXXXXXXXX'

Columns

Returns details about a listings item offers for a selling partner.

The following filter is required:

• SellerId: You can specify SellerId either as a criteria condition in WHERE filters or in the connection string.

SELECT * FROM ListingsItemsOffers WHERE SellerId = 'XXXXXXXXXXXXXX'

Columns

Returns details about a listings item summaries for a selling partner.

The following filter is required:

• SellerId: You can specify SellerId either as a criteria condition in WHERE filters or in the connection string.

SELECT * FROM ListingsItemsSummaries WHERE SellerId = 'XXXXXXXXXXXXXX'

Columns

Returns order items based on the Amazon Order Id that you specify.

Select

The Cloud will use the Amazon Marketplace API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud. OrderItems table supports server side filtering with = and IN operators for AmazonOrderId field.

• AmazonOrderId supports the '=' comparison.

SELECT * FROM OrderItems WHERE AmazonOrderId = '503-9993250-1405404'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Returns aggregated order metrics for a given interval, broken down by granularity, for a given buyer type.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Returns orders created or updated during a time frame that you specify.

Note: If you're retrieving personally identifiable information (PII), you must set IncludeRestrictedData to true. When retreiving PII, the OAuth application that you use to authenticate must be granted the Direct-to-consumer-shipping role to query this view. If you authenticated with the embedded OAuth application, you will need to create your own OAuth application with the Direct-to-consumer-shipping role enabled. See Creating a Custom OAuth App for more information about creating an OAuth application.

Select

The Cloud will use the Amazon Marketplace API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.

• AmazonOrderId supports the '=' comparison.
• SellerOrderId supports the '=' comparison.
• PurchaseDate supports the '=', '<', '>', '<=', '>=' comparison.
• LastUpdateDate supports the '=', '<', '>', '<=', '>=' comparison.
• OrderStatus supports the '=', 'IN' comparison.
• FulfillmentChannel supports the '=', 'IN' comparison.
• PaymentMethod supports the '=', 'IN' comparison.
• MarketplaceId supports the '=' comparison.
• BuyerEmail supports the '=' comparison.
• IsISPU supports the '=' comparison.

SELECT * FROM Orders WHERE AmazonOrderId = '249-7638334-8161403'

SELECT * FROM Orders WHERE SellerOrderId = '249-7638334' AND purchasedate > '2010-01-01'

SELECT * FROM Orders WHERE LastUpdateDate >= '2016-12-12'

SELECT * FROM Orders WHERE PurchaseDate >= '2016-12-12'

SELECT * FROM Orders WHERE MarketplaceId = 'ATVPDKIKX0DER'

SELECT * FROM Orders WHERE BuyerEmail = '[email protected]'

SELECT * FROM Orders WHERE IsISPU = false

SELECT * FROM Orders WHERE PurchaseDate >= '2016-12-12' AND OrderStatus = 'Canceled'

SELECT * FROM Orders WHERE PurchaseDate >= '2016-12-12' AND FulfillmentChannel = 'MFN'

SELECT * FROM Orders WHERE PurchaseDate >= '2016-12-12' AND BuyerEmail = '[email protected]'

SELECT * FROM Orders WHERE PurchaseDate >= '2016-12-12' AND PaymentMethod = 'Other'

Columns

Returns a list of features available for Multi-Channel Fulfillment orders in the marketplace you specify, and whether the seller for which you made the call is enrolled for each feature.

Columns

Returns the fulfillment order items indicated by the specified order identifier.

Columns

Returns a list of fulfillment order previews based on shipping criteria that you specify.

Columns

Returns delivery tracking information for a package in an outbound shipment for a Multi-Channel Fulfillment order.

Columns

An array of items that Amazon accepted for return. Returns empty if no items were accepted for return.

Columns

Returns a list of return reason codes for a seller SKU in a given marketplace.

Columns

Generated schema file.

Columns

Returns report details for the reports that match the filters that you specify.

Select

The Cloud will use the Amazon Marketplace API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.

Note: 'ReportType' attribute is required to query the view. You can view available values for 'ReportType' in Amazon Selling-Partner API Documentation, or you can query the 'ReportTypes' view.

• ReportId supports the '=' comparison.
• ReportType supports the '=', 'IN' comparisons.
• MarketplaceIds supports the '=', 'IN' comparisons.
• ProcessingStatus supports the '=', 'IN' comparisons.
• CreatedTime supports the '=', '<', '>', '<=', '>=' comparisons.

Following are example queries that are processed server side:

SELECT * FROM ReportList WHERE ReportId = '51013018828'

SELECT * FROM ReportList WHERE ReportType = 'GET_FLAT_FILE_OPEN_LISTINGS_DATA'

SELECT * FROM ReportList WHERE ReportType = 'GET_FLAT_FILE_OPEN_LISTINGS_DATA' AND CreatedTime > '2021-06-12' AND CreatedTime < '2021-08-01 12:00:00'

SELECT * FROM ReportList WHERE ReportType = 'GET_FLAT_FILE_OPEN_LISTINGS_DATA' AND MarketplaceIds = 'A1VC38T7YXB528'

SELECT * FROM ReportList WHERE ReportType = 'GET_FLAT_FILE_OPEN_LISTINGS_DATA' AND ProcessingStatus = 'DONE'

Note: When filtering with CreatedTime, values older than 90 days will not be accepted.

Columns

Returns report details for the reports that match the filters that you specify.

Select

This view lists all the available Report Types of Seller Central API and their respective format (JSON, XML, CSV, TSV, PDF, XLSX).

SELECT * FROM ReportTypes

Columns

Returns the shipping documents associated with a package in a shipment.

Columns

Returns the available shipping service offerings.

Columns