Magento helps you create customized shopping experiences for your customers. Magento is an open-source e-commerce platform written in PHP.

You can write query job results from Treasure Data directly to Magento (BETA). 

This data connector is in Beta. For more information, contact support@treasuredata.com


This topic contains:


Prerequisites

  • Basic knowledge of Treasure Data

  • Basic knowledge of Magento 2

  • Basic knowledge of Bulk API Magento 2

  • Understanding of JSON formatted queries and POST API formats

Supported

This integration supports APIs with the following specifications:

 [{"column1":"v1_1", "column2":"v2_1"},{"column1":"v1_2", "column2":"v2_2"},...]

Limitations

Token Expiration

By default, an admin token is valid for 4 hours. You can change these values from the Admin page if running the job takes a long time.

Performance

On self-hosted Magento instances, performance benchmarks were run on the following example environment:

The following performance benchmark results occurred using the default configuration: 

  • 1000 records, 2 columns, 26 KB → 1 min

  • 1500 records, 2 columns, 43 KB → 1 min

  • 10000 records, 2 columns, 279 KB → 12 min

  • 1000000 records, 2 columns, 29.3 MB → 12 hours, 16 mins

Example Queries for Magento Export Results

JSON fields in the POST API request should be specified as JSON or String in a TD query result.

Magento reference document for POST API endpoints:

https://magento.redoc.ly/

The following are some examples of queries for different endpoints:

POST API Endpoint :/async/bulk/V1/products

SELECT
'{
	"id": 45638,
	"sku": "39887-speaker-XL",
	"name": "39887-speaker-XL",
	"attribute_set_id": 9,
	"price": 450,
	"status": 1,
	"visibility": 1,
	"type_id": "virtual",
	"created_at": "2016-04-05 23:04:09",
	"updated_at": "2016-04-05 23:04:09",
	"product_links": [],
	"options": [],
	"tier_prices": [],
	"custom_attributes": [{
			"attribute_code": "description",
			"value": "<p>Precise Long-Sleeve Shirt in Black, Denim, or White.</p>"
		},
		{
			"attribute_code": "meta_title",
			"value": "Precise Long-Sleeve Shirt"
		},
		{
			"attribute_code": "meta_keyword",
			"value": "Precise Long-Sleeve Shirt"
		},
		{
			"attribute_code": "meta_description",
			"value": "Precise Long-Sleeve Shirt <p>Precise Long-Sleeve Shirt in Black, Denim, or White.</p>"
		},
		{
			"attribute_code": "color",
			"value": "11"
		},
		{
			"attribute_code": "options_container",
			"value": "container2"
		},
		{
			"attribute_code": "required_options",
			"value": "0"
		},
		{
			"attribute_code": "has_options",
			"value": "0"
		},
		{
			"attribute_code": "url_key",
			"value": "10090-white-xl"
		},
		{
			"attribute_code": "msrp_display_actual_price_type",
			"value": "0"
		},
		{
			"attribute_code": "tax_class_id",
			"value": "2"
		},
		{
			"attribute_code": "size",
			"value": "8"
		}
	]
}' AS product,
TRUE AS saveOptions


POST API Endpoint :/async/bulk/V1/customers

SELECT '{
  "email":"dummyuser@example.com",
  "firstname":"test",
  "lastname":"user",
  "addresses":[
     {
        "region":{
           "region_code":"MI",
           "region":"Michigan",
           "region_id":33
        },
        "region_id":33,
        "country_id":"US",
        "street":[
           "6146 Honey Bluff Parkway"
        ],
        "telephone":"(555) 229-3326",
        "postcode":"49628-7978",
        "city":"Calder",
        "firstname":"Veronica",
        "lastname":"Costello",
        "default_shipping":true,
        "default_billing":true
     }
  ]
}' as customer,
'dummy!@@#A_pass' as password;

 From Treasure Data, run the following query with export results into a connection for Magento:

SELECT '{"email":"demo@example.com","firstname":"demo Shaw","lastname":"Doe","addresses":[{"region":{"region_code":"MI","region":"Michigan","region_id":33},"region_id":33,"country_id":"US","street":["6146 Honey Bluff Parkway"],"telephone":"(555) 229-3326","postcode":"49628-7978","city":"Calder","firstname":"Veronica","lastname":"Costello","default_shipping":true,"default_billing":true}]}' as customer,
'Strong-Password' as password;

Sample parameter configuration:


Use the TD Console to Create Your Connection

When you configure a data connection, you provide authentication to access the integration. In Treasure Data, you configure the authentication and then specify the source information.

  1. Open the TD Console

  2. Navigate to Integrations Hub > Catalog.

  3. Search for Magento.

  4. Select Create.

    The following dialog opens.


  5. Choose one of the Authentication Modes.

  6. Edit the required credentials.


  7. Select Continue.

Authentication Mode Options

Using Login Credentials to Authenticate

  1. Select Login Credential and enter your Admin-level user name and password.


Using an Integration Access Token to Authenticate

  1. To get the access token, in your Magento Admin view, go to System > Integrations > Add New Integration (or open an existing one).



  2. Select the appropriate permissions for your target resources.



  3. Activate the integration.

  4. Copy the Access Token.


  5. In Treasure Data, paste the name of the access token. For Base URL, set the value to your Magento 2 REST API endpoint. For example, http://my-magento-server/rest/.


  6. Select Continue.

  7. Name your Authentication.

  8. Select Done.

Configure Export Results in Your Data Connection

In this step, you create or reuse a query. In the query, you configure the data connection.

  1. Open TD Console.

  2. Navigate to Data Workbench > Queries.

  3. Select the query that you plan to use to export data.
    For example:

    SELECT '{"email":"demo@example.com","firstname":"demo Shaw",
       "lastname":"Doe","addresses":[{"region":{"region_code":"MI",
       "region":"Michigan","region_id":33},"region_id":33,"country_id":"US",
       "street":["6146 Honey Bluff Parkway"],"telephone":"(555) 229-3326",
       "postcode":"49628-7978","city":"Calder","firstname":"Veronica",
       "lastname":"Costello","default_shipping":true,"default_billing":true}]}' 
       as customer,
    'Strong-Password' as password;
  4. Select Export Results located at top of your query editor. 
    The Choose Integration dialog opens.


  5. Select one of the following:

  1. Select Use Existing Integration.

  2. Type the connection name in the search box to filter.

  3. Select your connection.


  1. Select Create New Integration.


  2. Follow steps 5-7 in Use the TD Console to Connect.

  3. Follow all the steps in Using an Integration Access Token to Authenticate.

Continue Defining the Data Export

  1. Define the parameters for your export including:

Field

Required Optional

Description

  • Post API Endpoint

Required

  • Endpoint of the bulk POST API to consume the exported collections of data

  • Number of Rows per Batch

Optional

  • Maximum number of records per API post. Default: 1000

  • JSON Columns

Optional

  • Comma-separated list of string columns to send as JSON

  • Ignore Invalid Records

Optional

  • If enabled, continue the job even if there are records failed to upload because of invalid json format

  • Connect Timeout in Seconds

Optional

  • The time, in seconds, to wait until aborting a connection operation. Default: 300, which is equivalent to 5 minutes. Max: 1800

  • Read Timeout in Seconds

Optional

  • The time, in seconds, to wait until aborting a read operation. Default: 300, which is equivalent to 5 minutes. Max: 1800

  • Write Timeout in Seconds

Optional

  • The time, in seconds, to wait until aborting a write operation. Default: 300, which is equivalent to 5 minutes. Max: 1800

  • Retry Limit

Optional

  • Number of retries before the system gives up. Default: 7. Max: 10

  • Initial retry time wait in milliseconds

Optional

  • The time, in milliseconds, between the first and second attempt. Default: 500, which is equivalent to 0.5 seconds. Max: 900000

  • Max retry wait in milliseconds

Optional

  • The time, in milliseconds, between the second and all subsequent attempts. Default: 300000, which is equivalent to 5 minutes. Max: 900000



Optionally Schedule the Export

You can use Scheduled Jobs with Result Export, to periodically write the output result to a target destination that you specify. 

  1. Select the link text next to Schedule:.


  2. Select one of the following:


  1. Specify the timezone.

  2. Select or clear Delay execution.
    If selected you can indicate the time in hours, minutes, or second to delay data transfer.


  3. Select Schedule.

  1. Select and define the following:

Element

Action

Description

Cron

Specify a cron value

see Cron Schedule Values

Timezone

Time zone list of values.

Select the time zone that you want used for data collection.

Delay execution

selected or clear

If selected you can indicate the time in hours, minutes, or second to delay data transfer.

Select Schedule.

Run the Query

Even if you have defined a schedule for the query to run on you can run the query immediately to validate it.

  1. Save your query specification if necessary.

  2. Select Run.


Optionally Configure Export Results in Workflow

Within Treasure Workflow, you can specify the use of this data connector to export data.

timezone: UTC

_export:
  td:
    database: sample_datasets

+td-result-into-target:
  td>: queries/sample.sql
  result_connection: your_connections_name
  result_settings:
    base_url: http://base_url.com/rest
    api_endpoint: /all/async/bulk/V1/customers
    username: admin
    password: **xyz***
    #auth_method: token
    #token: **xyz***
    json_columns: customer
    ignore_invalid_records: true
    retry_count: 7
    retry_initial_wait_millis: 500
    max_retry_wait_millis: 300000
    request_connect_timeout: 300
    request_read_timeout: 300
    request_write_timeout: 300



  • No labels