Discover how a bimodal integration strategy can address the major data management challenges facing your organization today.
Get the Report →How to Query BigCommerce Data in MySQL Workbench
Execute MySQL queries against live BigCommerce data from MySQL Workbench.
You can use the SQL Gateway from the ODBC Driver for BigCommerce to query BigCommerce data through a MySQL interface. Follow the procedure below to start the MySQL remoting service of the SQL Gateway and work with live BigCommerce data in MySQL Workbench.
Connect to BigCommerce Data
If you have not already done so, provide values for the required connection properties in the data source name (DSN). You can use the built-in Microsoft ODBC Data Source Administrator to configure the DSN. This is also the last step of the driver installation. See the "Getting Started" chapter in the help documentation for a guide to using the Microsoft ODBC Data Source Administrator to create and configure a DSN.
BigCommerce authentication is based on the standard OAuth flow. To authenticate, you must initially create an app via the Big Commerce developer platform where you can obtain an OAuthClientId, OAuthClientSecret, and CallbackURL. These three parameters will be set as connection properties to your driver.
Additionally, in order to connect to your BigCommerce Store, you will need your StoreId. To find your Store Id please follow these steps:
- Log in to your BigCommerce account.
- From the Home Page, select Advanced Settings > API Accounts.
- Click Create API Account.
- A text box named API Path will appear on your screen.
- Inside you can see a URL of the following structure: https://api.bigcommerce.com/stores/{Store Id}/v3.
- As demonstrated above, your Store Id will be between the 'stores/' and '/v3' path paramters.
- Once you have retrieved your Store Id you can either click Cancel or proceed in creating an API Account in case you do not have one already.
Configure the SQL Gateway
See the SQL Gateway Overview to set up connectivity to BigCommerce data as a virtual MySQL database. You will configure a MySQL remoting service that listens for MySQL requests from clients. The service can be configured in the SQL Gateway UI.
Query BigCommerce from MySQL Workbench
The steps below outline connecting to the virtual BigCommerce database created in the SQL Gateway from MySQL Workbench and issuing basic queries to work with live BigCommerce data.
Connect to BigCommerce through the SQL Gateway
- In MySQL Workbench, click to add a new MySQL connection.
- Name the connection (CData SQL Gateway for BigCommerce).
- Set the Hostname, Port, and Username parameters to connect to the SQL Gateway.
- Click Store in Vault to set and store the password.
- Click Test Connection to ensure the connection is configured properly and click OK.
NOTE: When we refer to Username and Password, we mean the credentials for the user(s) created for the SQL Gateway.
Query BigCommerce Data
- Open the connection you just created (CData SQL Gateway for BigCommerce).
- Click File -> New Query Tab.
- Write a SQL query to retrieve BigCommerce data, like SELECT * FROM `CData BigCommerce Sys`.Customers;
With access to live BigCommerce data from MySQL Workbench, you can easily query and update BigCommerce, just like you would a MySQL database. Get started now with a free, 30-day trial of the CData ODBC Driver for BigCommerce and the CData SQL Gateway.