Visit our new documentation site! This documentation page is no longer updated.

Data Connector for Shopify

This article describes how to use the data connector for Shopify, which allows you to directly import data from your Shopify to Treasure Data.

Table of Contents

Prerequisites

  • Basic knowledge of Treasure Data
  • Basic knowledge of Shopify

Step 0: Install ‘td’ command v0.11.9 or later

You can install the newest Treasure Data Toolbelt.

$ td --version
0.15.0

Step 1: Create Configuration File

Prepare configuration file (for eg: load.yml) like below, with your Shopify account access information.

in:
  type: shopify
  apikey: xxxxxxxx
  password: xxxxxxx
  target: products
  store_name: xxxxxxx
  from_date: '2016-09-07'
  fetch_days: 2 (optional)
out:
  mode: replace

This example shows a dump of Shopify Product objects. The values for apikey and password are a valid API Key and password, provided by Shopify. The value store_name is the name of the store for which data needs to be fetched. You can shopify an object for which data needs to be fetched from store using the target option.

in:
  type: shopify
  apikey: xxxxxxxx
  password: xxxxxxx
  target: orders
  store_name: xxxxxxx
  from_date: '2016-09-07T00:00:00.000Z'
  to_date: '2016-09-07T01:00:00.000Z' # 1-hour duration
out:
  mode: replace

The example shows how to specify a dump of Shopify Order objects from Sep 7 2016 12AM to Sep 7 2016 1AM

To get Shopify Credentials, complete the following steps:

  1. Sign up at https://www.shopify.com/ to create an online store for the user

  2. Enter the details about the user

  3. Enter additional details about business

  4. Create a private app

  5. The user will be able to see and use the API credentials for store and will be able to connect with different external applications

For more details on available out modes, see Appendix A

Configuration

  • apikey: Shopify provided API Key (string, required)

  • password: Shopify provided password (string, required)

  • store_name: Shopify store name that records are fetched from (string, required). No spaces, use hyphens (-). See Appendix B

  • target: Shopify object that data needs to be fetched for from store (string, required)

    • Supported targets are: customers, orders, products, and transactions
  • retry_initial_wait_msec: Parameter that provides the initial wait time (in milliseconds) for each retry logic to call the Shopify API for a specified target to fetch data (int, optional)

  • retry_limit: Parameter that provides number of attempts to call the Shopify API for a specified target to fetch data (int, optional)

  • max_retry_wait_msec: Parameter that provides the maximum wait time (in milliseconds) for each retry to call Shopify API for a specified target to fetch data (int, optional)

  • from_date: Parameter that specifies the date and time to fetch records from. (Date Format : yyyy-MM-dd or yyyy-MM-dd’T'hh:mm:ss.SSS'Z') (string, required)

  • to_date: Parameter that specifies the allowable duration to fetch records. (Date Format : yyyy-MM-dd’T'hh:mm:ss.SSS'Z') (string, optional, if omitted, Data Connector will calculate duration by fetch_days config)

    • Note: to_date has higher priority than fetch_days, and will override fetch_days if both are specified. It’s best to use just one of them, either to_date or fetch_days.
    • IMPORTANT NOTE: In non-incremental mode, if you plan to run consecutive imports, make sure not to use overlapped from_date and to_date. For example:
      • 1st import: from_date: 2017-10-10T00:00:00.000Z and to_date: 2017-10-10T01:00:00.000Z
      • 2nd import: from_date: 2017-10-10T01:00:01.000Z and to_date: 2017-10-10T02:00:00.000Z

      Failure in doing this will result in data duplication.

      In incremental mode, from_date and to_date will be calculated automatically based on initial import parameters.

  • fetch_days: If no to_date is specified, this parameter is used to calculate the duration to fetch records from from_date. (int, optional, default: 1)

  • incremental: if true, this parameter enables incremental loading (boolean, optional, default: true)

Step 2(optional): Preview data to imported

You can preview data to be imported using the command td connector:preview.

$ td connector:preview load.yml
+---------+--------------+------------------+----
| id:long | title:string | body_html:string | ...
+---------+--------------+------------------+----
| 1       | "33"         | <p>body_html<p>  |
| 2       | "34"         | <p>body_html<p>  |
| 3       | "35"         | <p>body_html<p>  |
| 4       | "36"         | <p>body_html<p>  |
| 6       | "37"         | <p>body_html<p>  |
+---------+--------------+------------------+----

Step 3: Execute Load Job

Finally, submit the load job. It may take a couple of hours depending on the data size. Users need to specify the database and table where their data are stored.

It is recommended to specify the --time-column option, because Treasure Data’s storage is partitioned by time (see also Data Partitioning) If the option is not given, the Data Connector will choose the first long or timestamp column as the partitioning time. The type of the column specified by --time-column must be either of type long and timestamp.

If your data doesn’t have a time column you can add it using the add_time filter option. More details at add_time filter plugin

$ td connector:issue load.yml --database td_sample_db --table td_sample_table --time-column created_at

The above command assumes that you have already created database(td_sample_db) and table(td_sample_table). If the database or the table does not exist in TD, this command will not succeed. Therefore, create the database and table manually or use the --auto-create-table option with the td connector:issue command to auto create the database and table:

$ td connector:issue load.yml --database td_sample_db --table td_sample_table --time-column created_at --auto-create-table
Untitled-3
You can assign Time Format column to the "Partitioning Key" by "--time-column" option.

Scheduled execution

You can schedule periodic Data Connector executions for periodic Shopify imports. The load distribution and operation of Treasure Data’s scheduler is optimized to achieve high availability. By using Treasure Data’s scheduler, you no longer need a cron daemon on your local datacenter.

Create the schedule

A new schedule can be created using the td connector:create command. Specify the name of the schedule, the cron-style schedule, the database and table where the data will be stored, and the Data Connector configuration file. The values are required.

$ td connector:create \
    daily_shopify_import \
    "10 0 * * *" \
    td_sample_db \
    td_sample_table \
    load.yml
Untitled-3
The `cron` parameter also accepts these three options: `@hourly`, `@daily` and `@monthly`.

By default, schedule is setup in UTC timezone. You can set the schedule in a timezone using -t or —timezone option. Note that the --timezone option supports only extended timezone formats like ‘Asia/Tokyo’, ‘America/Los_Angeles’ etc. Timezone abbreviations like PST, CST are not supported and may lead to unexpected schedules.

List the Schedules

You can see the list of currently scheduled entries by td connector:list.

$ td connector:list
+-----------------------+--------------+----------+-------+--------------+-----------------+----------------------------+
| Name                  | Cron         | Timezone | Delay | Database     | Table           | Config                     |
+-----------------------+--------------+----------+-------+--------------+-----------------+----------------------------+
| daily_shopify_import  | 10 0 * * *   | UTC      | 0     | td_sample_db | td_sample_table | {"type"=>"shopify", ... }  |
+-----------------------+--------------+----------+-------+--------------+-----------------+----------------------------+

Show the Setting and History of Schedules

td connector:show shows the execution setting of a schedule entry.

% td connector:show daily_shopify_import
Name     : daily_shopify_import
Cron     : 10 0 * * *
Timezone : UTC
Delay    : 0
Database : td_sample_db
Table    : td_sample_table

td connector:history shows the execution history of a schedule entry. To investigate the results of each individual execution, use td job <jobid>.

% td connector:history daily_shopify_import
+--------+---------+---------+--------------+-----------------+----------+---------------------------+----------+
| JobID  | Status  | Records | Database     | Table           | Priority | Started                   | Duration |
+--------+---------+---------+--------------+-----------------+----------+---------------------------+----------+
| 578066 | success | 10000   | td_sample_db | td_sample_table | 0        | 2015-04-18 00:10:05 +0000 | 160      |
| 577968 | success | 10000   | td_sample_db | td_sample_table | 0        | 2015-04-17 00:10:07 +0000 | 161      |
| 577914 | success | 10000   | td_sample_db | td_sample_table | 0        | 2015-04-16 00:10:03 +0000 | 152      |
| 577872 | success | 10000   | td_sample_db | td_sample_table | 0        | 2015-04-15 00:10:04 +0000 | 163      |
| 577810 | success | 10000   | td_sample_db | td_sample_table | 0        | 2015-04-14 00:10:04 +0000 | 164      |
| 577766 | success | 10000   | td_sample_db | td_sample_table | 0        | 2015-04-13 00:10:04 +0000 | 155      |
| 577710 | success | 10000   | td_sample_db | td_sample_table | 0        | 2015-04-12 00:10:05 +0000 | 156      |
| 577610 | success | 10000   | td_sample_db | td_sample_table | 0        | 2015-04-11 00:10:04 +0000 | 157      |
+--------+---------+---------+--------------+-----------------+----------+---------------------------+----------+
8 rows in set

Delete the Schedule

td connector:delete removes the schedule.

$ td connector:delete daily_shopify_import

Appendix

A) Modes for out plugin

You can specify file import mode in out section of load.yml.

append (default)

This is the default mode. Records are appended to the target table.

in:
  ...
out:
  mode: append

replace (In td 0.11.10 and later)

This mode replaces data in the target table. Any manual schema changes made to the target table remains intact with this mode.

in:
  ...
out:
  mode: replace

B) store_name configuration

Shopify translates your free-form Store Name into URL-friendly value, ie. it will truncate special characters and replace spaces with hyphens. For example: Example Shop-123-!#$ will become example-shop-123:



You need to use the translated value (in Admin URL, after signing in):




Last modified: Feb 21 2018 05:45:24 UTC

If this article is incorrect or outdated, or omits critical information, let us know. For all other issues, access our support channels.