WooCommerce - Hevo Data

WooCommerce Setup Guide

WooCommerce is an open-source, e-commerce plugin for WordPress. It is designed for small to large-sized online merchants using WordPress. It uses a MySQL database.

The details associated with the content in WooCommerce are stored as Events in the MySQL database. For example, orders, payments, and shipping. WooCommerce provides some default tables and also allows you to create custom tables. You can replicate the Events data from your WooCommerce account to a Destination database or data warehouse using Hevo Pipelines.

Note: You must provide the MySQL database details to configure the WooCommerce Source.

Getting Started

Setup

Prerequisites

Database credentials of the MySQL database used by WooCommerce. Read more about creating a database for WooCommerce.
Hevo’s IP addresses are whitelisted.
SELECT privileges are granted to the database user.
You are assigned the Team Administrator, Team Collaborator, or Pipeline Administrator role in Hevo to create the Pipeline.

Perform the following steps to configure WooCommerce via MySQL as the Source in your Pipeline. You must provide details of the MySQL database to configure the WooCommerce Source.

Whitelist Hevo’s IP Addresses

Click your MySQL variant below for steps to whitelist Hevo’s IP address:

Grant Privileges to the User

Perform the following steps to grant the SELECT privilege to the database user:

Log in to your MySQL database instance as a root user. For example, mysql -u root -p.
Run the following command to grant SELECT privilege to the database user:
GRANT SELECT ON *.* TO 'username'; # to grant privileges to a user with <username> as username

Specify WooCommerce via MySQL Connection Settings

Perform the following steps to configure WooCommerce via MySQL as the Source in your Pipeline:

Click PIPELINES in the Navigation Bar.
Click + CREATE PIPELINE in the Pipelines List View.
On the Select Source Type page, select WooCommerce via MySQL.
On the Configure your WooCommerce via MySQL Source page, specify the following:
- Pipeline Name: A unique name for your Pipeline, not exceeding 255 characters.
- Database Host: The MySQL host’s IP address or DNS.
  
  Note: For URL-based hostnames, exclude the http:// or https:// part. For example, if the hostname URL is http://mysql-replica.westeros.inc, enter mysql-replica.westeros.inc.
- Database Port: The port on which your MySQL server listens for connections. Default value: 3306.
- Database User: The authenticated user that can read the tables in your database.
- Database Password: Password for the database user.
- Database Name: The database you want to load data from, if the Pipeline mode is Table or Custom SQL.
- Connection Settings
  - Connect through SSH: Enable this option to connect to Hevo using an SSH tunnel, instead of directly connecting your MySQL database host to Hevo. This provides an additional level of security to your database by not exposing your MySQL setup to the public. Read Connecting Through SSH.
    
    If this option is disabled, you must whitelist Hevo’s IP addresses. Refer to the content for your MySQL variant for steps to do this.
  - Use SSL: Enable it to use SSL encrypted connection. To enable this, specify the following:
    - CA File: The file containing the SSL server certificate authority (CA).
      - Load all CA Certificates: If selected, Hevo loads all CA certificates (up to 50) from the uploaded CA file, else it loads only the first certificate.
        
        Note: Select this check box if you have more than one certificate in your CA file.
    - Client Certificate: The client public key certificate file.
    - Client Key: The client private key file.
Click TEST & CONTINUE to proceed for setting up the Destination. This button is enabled once you specify all the mandatory fields.

Additional Information

Read the detailed Hevo documentation for the following related topics:

Schema and Primary Keys

The schema is derived based on the data in your MySQL Source tables.

Data Model

WooCommerce is a WordPress plugin and uses many of the WordPress tables. See the WordPress Database Description document to understand the structure of WordPress.

The following is the list of tables (objects) that are created by default at the Destination when you run the Pipeline. In addition to the default tables, you can ingest any custom tables that you have created.

Note: Each table name is prefixed with your <_WP Database="" Table="" Prefix="">_, for example, _wp__.

Object	Description
actionscheduler_actions	Contains information on actions executed by Action Scheduler.
actionscheduler_claims	Contains the lists of claims associated with each order.
actionscheduler_groups	Contains the groups associated with orders.
actionscheduler_logs	Contains the Action Scheduler execution logs.
woocommerce_sessions	Contains the customer session data, such as carts.
woocommerce_api_keys	Contains the API keys used for the REST API.
woocommerce_attribute_taxonomies	Contains the global attribute taxonomy names for products.
woocommerce_downloadable_product_permissions	Contains the product access permissions for downloadable products (granted after purchase).
woocommerce_order_items	Contains the items associated with orders.
woocommerce_order_itemmeta	Contains the metadata of order items.
woocommerce_tax_rates	Contains the tax rates defined in the admin area.
woocommerce_tax_rate_locations	Contains the locations (postcodes and cities) associated with the tax rates.
woocommerce_shipping_zones	Contains the shipping zones created in the Settings area.
woocommerce_shipping_zone_locations	Contains the list of locations associated with your shipping zones.
woocommerce_shipping_zone_methods	Contains the list of shipping methods linked to your shipping zones.
woocommerce_payment_tokens	Contains the list of customer payment tokens (used by gateways).
woocommerce_payment_tokenmeta	Contains the metadata associated with payment tokens.
woocommerce_log	Contains the general logs which is used as an alternative to file based logging.
wc_webhooks	Contains the list of webhooks that have been setup on the store.
wc_download_log	Contains the list of products download by the user.
wc_product_meta_lookup	Contains the order metadata for indexing, to speed up requests.
wc_tax_rate_classes	Contains the tax classes data.
wc_reserved_stock	Contains the list of reserved stocks to ensure availability at checkout.

Learn more about WooCommerce database.

Limitations

The WordPress SQL table prefix should be less than 20 characters. Otherwise, WooCommerce is unable to create the tables properly and can lead to problems in loading the data.

Revision History

Refer to the following table for the list of key updates made to this page:

Date	Release	Description of Change
Mar-18-2024	2.21.2	Updated section, Specify WooCommerce via MySQL Connection Settings to add information about the Load all CA certificates option.
Dec-19-2022	2.04	Updated section, Specify WooCommerce via MySQL Connection Settings to add information that you must specify all fields to create a Pipeline.
Jan-24-2022	1.80	New document.

Last updated on Nov 04, 2024