How to import Amazon MWS data into SQL Server (T-SQL)

Introduction

In our previous blog post we saw how to import rest API in SQL Server. Using same concepts lets look at how to import Amazon MWS Data into SQL Server. We will explore many techniques to call Amazon MWS API and learn how to automate Amazon MWS data extraction without doing any ETL. We will call MWS XML or CSV API just using T-SQL code. We will also learn how to Download Reports from by calling MWS API.

You can also refer other articles here (Power BI) and here (SSIS)

 

Requirements

This article talks about few tools and techniques in order to load API data in SQL Server. Please make sure following prerequisites are met.
  1. Download and Install ZappySys ODBC PowerPack (This includes XML / JSON / REST API and few other drivers for SQL Server and ODBC connectivity in tools like Excel, Power BI, SSRS)
  2. Make sure you have access to SQL Server Instance. If you cant find one still want to try what is mentioned in this article then install FREE SQL Express Edition
  3. Confirm that you have SSMS Installed. If you don't have then you can download from here.

About Amazon MWS  API

If you want to sell something on Amazon you can use their e-commerce platform with some monthly fee. You can setup your entire online store / inventory using admin interface.

Amazon Marketplace Web Service (Amazon MWS) is an integrated web service API that helps Amazon sellers to programmatically exchange data on listings, orders, payments, reports, and more. Using these API you can read or write data from your Seller account and integrate it inside your own Systems (e.g. Reporting / ETL / BI tools).

NOTE: Amazon MWS may allow only certain number of requests per minute depending which API you calling so you might want to use each call wisely. We will suggest you various techniques throughout this article so you can avoid errors so read each section carefully. You can find Throttling information on API help page itself (like this one see throttling section. Also this one).

Obtain MWS API Access Key / Secret and Seller ID

Very first thing to call any MWS API is to obtain Access Key, Secret Key and Seller ID (i.e. Merchant ID). To obtain this you first have to register a developer.

Here is where to look for your Merchant ID and Key / Secret.  Here is direct link to User Permissions page.

How to get Amazon MWS developer keys and know your Merchant ID (i.e. Seller ID)

How to get Amazon MWS developer keys and know your Merchant ID (i.e. Seller ID)

Getting Started

Now let’s look at step by step instructions on how to call Amazon MWS API and then import into Power BI. In below steps we will use  ZappySys ODBC PowerPack so make sure it’s installed first.

Using ScratchPad to Test Amazon MWS API

Each MWS API has set of required and optional parameters. You can refer each API page for details. For example ListMatchingProducts API which we will use as an example. To make MWS API testing / learning easy, Amazon provides online testing tool called MWS ScratchPad. We will use this tool to craft API requests and use that information in ODBC driver SQL queries and later import data in Power BI.

  1. Open MWS ScratchPad  by vising https://mws.amazonservices.com/scratchpad/index.html
  2. Select Products API category and pick ListMatchingProducts API
  3. Enter your SellerID (i.e. Merchant ID), Developer AccessKey (i.e. AWSAccessKeyId) and Secret Key we obtained in the previous section. Enter MarketId (e.g. ATVPDKIKX0DER for USA Market) and other parameters as below and Click Submit to see response.
    Using Amazon MWS ScratchPad (API Testing Tool - Created by Amazon)

    Using Amazon MWS ScratchPad (API Testing Tool – Created by Amazon)

  4. Click on the Response Details Tab to extract some important information we will use for ODBC configuration. Notice attributes in red rectangles. They are the ones which we will need in the POST request Body. ZappySys Drivers supply many common parameters automatically but any API endpoint specific parameter must be supplied in the Request Body. For example you dont have to supply SignatureVersion, Timestamp, Signature or SignatureMethod
    Calling Amazon MWS API in Scratchpad Testing Tool (Response Details Tab) - ListMatchingProducts Example

    Calling Amazon MWS API in Scratchpad Testing Tool (Response Details Tab) – ListMatchingProducts Example

This API requires Market ID parameter so enter it as per your Market where you selling. For example USA marketplaceid = ATVPDKIKX0DER. You can lookup correct MarketplaceId by checking this help (endpoints / marketplaces).

Setup Amazon MWS API Connections

Configure ZappySys Data Gateway

Now let's look at steps to configure Data Gateway after installation.
  1. Assuming you have installed ZappySys ODBC PowerPack using default options (Which also enables Data Gateway Service)
  2. Search "Gateway" in your start menu and click ZappySys Data Gateway
    Open ZappySys Data Gateway

    Opening ZappySys Data Gateway

  3. First, make sure Gateway Service is running (Verify Start icon is disabled)
  4. Also, verify Port on General Tab
    Port Number setting on ZappySys Data Gateway

    Checking port number setting on ZappySys Data Gateway

  5. Now go to Users tab. Click Add icon to add a new user. Check Is admin to give access to all data sources you add in future. If you don't check admin then you have to manually configure user permission for each data source.

    Adding the Gateway user

 

Create XML Driver Connection for Amazon MWS API (e.g. RequestReport )

Some APIs we need to call in this article is XML format and some API (e.g. GetReport) is CSV format – Tab delimited so we need to create 2 different connections using CSV driver and XML driver. So lets get started with Amazon MWS Connection for XML API.

  1. Click on Add New Data source. Name as AMAZON-MWS-XML and select Native – ZappySys XML Driver option.
    Add Gateway Data Source

    Add Gateway Data Source

  2. Now click Edit to Configure Settings. For now we will only care OAuth Connection settings. All other options are overwritten in SQL Query.
    Edit Gateway Data Source Settings

    Edit Gateway Data Source Settings

  3. When UI opens for Driver Enter MWS API Service URL as below. Notice that we added /Products/2011-10-01 (This was extracted from previous section). You can check full list of Amazon MWS Endpoints here. Because we are connecting to USA region we will use below URL.
  4. Select OAuth from the Connection dropdown and Click Configure Settings
  5. On the Connection UI, Select OAuth Provider Amazon Market Web Service (MWS)
  6. Enter your API Access Key and Secret Key (obtained from here)
  7. Your connection will look like below.
    ODBC Connection Credentials for Amazon MWS API (Access Key, Secret Key, URL Endpoint)

    ODBC Connection Credentials for Amazon MWS API (Access Key, Secret Key, URL Endpoint)

  8. Also configure Retry options to handle API limit reached error when you call API too often. For Amazon MWS we get Status code 503 when request is throttled so lets narrow down on which error we want to retry.
    Retry Options

    Retry Options

  9. Click OK to save OAuth Connection UI to go back to main UI
  10. Now Go to Throttling Tab and enter some delay (e.g. enter 20000 for 20sec delay after each request). This will slow down API calls so we don’t face errors about API Limit reached.
  11. Click OK to Save UI
  12. Click Save button on Gateway UI to save and restart

Create CSV Driver Connection for Amazon MWS API (e.g. GetReport )

Now lets create a connection CSV API (e.g. GetReport ).

Follow almost same steps as previous section except use CSV Driver when you click on Add new Data source select Native – ZappySys CSV Driver and name data source as AMAZON-MWS-CSV . As we mentioned for now we mostly want to setup Connection and not worry about Request Body, Pagination etc.

Create Linked Server for SQL Server

Run following Script in SSMS to create Linked servers. Change ######## with Your gateway password. Change localhost with machine name or IP where ZappySys Gateway Is running. Use localhost if SQL Server and Gateway both on the same machine.

If you want to know how to configure Linked Server using UI then check this article.

 

Amazon MWS ListOrders Example

Now lets look at how to call ListOrders request with pagination in SQL Server. Create a following Stored Procedure to List MWS Orders. If you want to extract many orders then call Report rather than this proc (See next section for generating reports)

Here is how to call this proc. Change SellerId, MarketplaceId and Date.

 

Download Amazon MWS Report data in SQL Server (CSV / XML Format)

Now lets look at how to extract data from Custom Report API (You must need ODBC PowerPack v1.1.1 or higher). However reading data from Reports not single step process because report generation is Job style API. Which means you send report request and wait  until its done. Once Report ready you have to read in CSV format or XML. Some Reports only available in CSV format. Check this post to understand how complex it can be to get data. We will make it simple for you to understand this in 3 steps. Basically calling reports requires minimum 3 API calls (see below)

  1. Create a new report request – Cal RequestReport API . It returns ReportRequestId (You can use it in the next step)
  2. Check Report Status see its done – Call GetReportRequestList API (Pass ReportRequestId got in the previous step to get data for only one Report request we care). Keep checking Report Status every few seconds until you get _DONE_  or _DONE_NO_DATA_ status in response.
  3. Read Report Data – Call GetReport. This API call returns CSV or XML data. So use correct Driver for this step. If CSV data is returned then we must use CSV Driver rather than XML Driver.

Method 1 – Single query approach (New Driver)

In new driver ZappySys added few new features which makes it possible to call above 3 steps process in a single query. New query syntax offers StatusCheck and Mixing Content Formats using same driver. Because Most MWS Reports are in CSV Format we have to use CSV Driver but oddly first 2 steps listed in above sections are in XML Format so we have to use XML driver. Technically we need atleast 2 steps (i.e 2 queries) but we will show you how to achieve using single query as below.

  1. Assuming you have followed steps to create a linked server AMAZON-MWS-CSV (see previous section)
  2. Run below query. Replace necessary parameters as per your need. (i.e MarketplaceId , SellerId, CreatedAfter date)
     

 

Performance Tip

If you like to improve performance and dont want to use SELECT INTO approach to load into temp table as above then you can use EXEC (….) AT YourLinkedServerName  as below.

Advantage of this method is your Query speed will increase because system calls API only once when you call EXEC AT. In OPENROWSET it needs to call above query twice (Once to obtain metadata and once to get data).

When you use OPENQUERY / EXEC..AT with Data Gateway there is a known limitation with SQL Query Length. Your query must be less than 2048 characters otherwise you may get [**** Data is invalid error ****] Try to avoid column names for long query and use SELECT * INTO temp_table FROM OPENQUERY(…..) approach.

 

Method 2 – Multiple query approach (For old driver)

Now lets see how to load Amazon MWS data in SQL Server using old driver (v1.1.2 or older) which didnt support single query approach like previous section. If you are using older driver or for some reason you like to split steps into multiple procedures then use below approach.

usp_MWS_Report  Stored Proc ( Generate ReportRequest, Wait until done, Read data )

 

usp_MWS_RequestReport  Stored Proc ( Step1 – Create Report Request )

 

 

 

usp_MWS_GetReportIdAndStatus Stored Proc ( Step2 – Get Report Id and Report Status )

 

 

usp_MWS_GetReportDataCsv Stored Proc (Step3 – Get Report Data in CSV format )

Last step in report generation is download the report data in CSV format.

 

Example – Import data from Amazon MWS into SQL Server Table

Finally when we have all stored procs created we can load into target table as below. In our previous article we saw how to use SELECT INTO along with OPENQUERY to load data into new temp table or insert into some existing table. However when you want to load Stored Proc output into table its not straight forward specially when we calling dynamic SQL.

Three tricks worth mentioning here which makes it possible to load data from stored proc into temp table dynamically.

  1. Create a linked server to Self so we can use OPENQUERY against to call stored proc so its easy to use SELECT INTO.
  2. Use WITH RESULT SETS to describe metadata of output. This is needed because we are using dynamic SQL inside proc and WITH RESULT SETS describes the metadata.
  3. Use of SET NO COUNT ON to avoid error like below
  4. If you dont do above tricks then you may see errors like this (INSERT is not allowed when we use DYNAMIC SQL inside nested Stored Procs)

To load output into table you must know column names from the output.  If you dont know you can try to run stored proc without @Columns like below.

Once we know columns from report, you can run following script to insert into table.

Full Script – Load MWS Report Output into Table

Import Amazon MWS Data into SQL Table (Load Report output into temp table)

Import Amazon MWS Data into SQL Table (Load Report output into temp table)

Conclusion

In this article, we saw how to load Amazon MWS Data into SQL Server. We used simple SQL queries with ZappySys XML / CSV Driver to pull data from Amazon MWS API. ZappySys ODBC PowerPack makes it super easy to consume data from virtually any API (JSON / XML or CSV Web Services), no coding required. Download here to get started with ZappySys ODBC drivers.

 

 

 

 

Posted in CSV File / REST API Driver, ODBC Gateway, T-SQL (SQL Server), XML File / SOAP API Driver and tagged , , , .