Magento to Snowflake: 2 Easy Methods

Do you want to gather fresh insights and analytics from your Magento data? If so, then transferring your data from Magneto to a data warehouse like Snowflake is a good way to initiate this process. In this blog, we will look at 2 ways that can help you load data from Magento to Snowflake. Then you will be able to evaluate both methods and choose the one which best suits your needs.

Introduction to Magento

Magento is an open-source e-commerce platform that enables you to manage and control the functionality of your e-commerce online shopping system. Magento also tracks and stores data relating to these processes. Some of the data stored include attributes on customers, orders, order items, etc. This data can be accessed by making calls to Magento’s REST API. 

Introduction to Snowflake

Snowflake is a popular Software-as-a-Service cloud data warehouse. Snowflake is completely managed and rids the user of any maintenance activities involved in setting up a data warehouse. Snowflake has a decoupled storage and compute layer and prices for these separately as well. It has become known for its scalability and relative ease of use when compared to other data warehouse options on the market. You can read more about Snowflake data warehouse here.

Methods to Move Data from Magento to Snowflake

Here are the methods you can use to connect Magento to Snowflake in a seamless fashion:

• Method 1: Using Custom ETL Scripts to Move Data from Magento to Snowflake
• Method 2: Using Hevo Data to Move Data from Magento to Snowflake

Method 1: Using Custom ETL Scripts to Move Data from Magento to Snowflake

This approach would need you to invest time and effort from the engineering team. The broad steps would look as follows:

• Step 1: Extracting the Data
• Step 2: Data Preparation
• Step 3: Loading the Data

Step 1: Extracting the Data

Magento data can be accessed by executing simple GET requests to its API. Generally, you can use the command GET  http://magentohost/api/rest/ and then specify the required endpoint. The data will be returned in either JSON or XML format. For example, GET http://magentohost/api/rest/products. 

Result (in JSON): 

{"1":{"entity_id":"1","type_id":"simple","sku":"dress_test","status":"1","visibility":"4","tax_class_id":"2",
"weight":"1.0000","price":"1500.0000","special_price":"1000.0000","name":"Wedding dress","url_key":"dress",
"country_of_manufacture":"AO","msrp_display_actual_price_type":"2","gift_message_available":"1","news_from_date":"2012-03-21 00:00:00",
"news_to_date":"2012-03-24 00:00:00","special_from_date":"2012-03-21 00:00:00","special_to_date":"2012-03-24 00:00:00",
"description":"White wedding dress"},"2":{"entity_id":"2","type_id":"simple","sku":"black_sunglasses","status":"1","visibility":"4",
"tax_class_id":"2","weight":"0.2000","price":"500.0000","special_price":"300.0000","name":"Sunglasses","url_key":"sunglasses",
"country_of_manufacture":"AR","msrp_display_actual_price_type":"2","gift_message_available":null,"news_from_date":null,"news_to_date":null,
"special_from_date":"2012-03-21 00:00:00","special_to_date":"2012-03-24 00:00:00","description":"Black sunglasses"}}

Further information on Magento’s Rest API can be found here https://devdocs.magento.com/guides/m1x/api/rest/Resources/resources.html

Step 2: Data Preparation

Before loading the data into Snowflake, it is necessary to have a well-defined schema. For example, it can be useful to create a schema where data from each Magento end point is mapped to a distinct table in Snowflake. It is also imperative to make sure that the data types from the Magento file are mapped properly to the accepted data types in Snowflake. Snowflake provides support for a wide array of data types. Information on accepted data types in Snowflake can be found here Preparing Your Data Files.

Step 3: Loading the Data

The data is loaded into Snowflake with the help of the COPY INTO SQL statement through SnowSQL ( Snowflake’s Command Line Interface). The files can be loaded directly from Snowflake staging tables or from any of Snowflake’s external storage locations. For the purposes of this blog, we will look at loading directly from the Snowflake internal stage types and also through each of Snowflake’s external storage locations. 

Loading from Snowflake Stages:

The data can be ingested into a Snowflake internal stage using the PUT command before we use COPY INTO to load it into Snowflake. 

The following are commands to load the data from the stages:

• User Stage Type:
  

   

  COPY INTO TABLE1 FROM @~/staged file_format=(format_name=’json_format’)

• Table Stage Type:

  COPY INTO TABLE1 file_format=(Type=’JSON’ Strip_outer_array=”TRUE”)

• Created Internal Stage:

  COPY INTO TABLE1 FROM @Stage_Name

Loading Data from External Stages:

• Amazon S3:
  You can load data directly from an Amazon S3 bucket but the recommended method is to first create an Amazon S3 external stage. The same applies to Microsoft Azure and GCP buckets.

   

  COPY INTO TABLE1 FROM s3://bucket
  
  CREDENTIALS= (AWS_KEY_ID='YOUR AWS ACCESS KEY' AWS_SECRET_KEY='YOUR AWS SECRET ACCESS KEY')
  
  ENCRYPTION= (MASTER_KEY = 'YOUR MASTER KEY')
  
  FILE_FORMAT = (FORMAT_NAME = JSON_FORMAT)

• Microsoft Azure:
  

   

  COPY INTO TABLE1 FROM azure://your account.blob.core.windows.net/container
  
  STORAGE_INTEGRATION=(Integration_name)
  
  ENCRYPTION= (MASTER_KEY = 'YOUR MASTER KEY')
  
  FILE_FORMAT = (FORMAT_NAME = JSON_FORMAT)

• GCS:
  

   

  COPY INTO TABLE1 FROM 'gcs://bucket’
  
  STORAGE_INTEGRATION=(Integration_name)
  
  ENCRYPTION= (MASTER_KEY = 'YOUR MASTER KEY')
  
  FILE_FORMAT = (FORMAT_NAME = JSON_FORMAT)

Manually loading the data from Magento is not without its drawbacks. Here are a few:

Limitations of using Custom ETL Scripts to Move Data from Magento to Snowflake

• Time Consuming Process: It is not ideal for fast-paced organizations since it is very time-consuming. The process also has a dependency on precious developer bandwidth.
• Limited Real-Time Data Load Capability: This method does not enable real-time data updates. This means that you would have to configure cron jobs and write a lot of extra code to enable simple real-time data load functionality.
• Maintenance Issues: Inaccurate data may be generated whenever Magento has issues with its API. Thus, it is necessary to have a lot of resources monitoring the data source to check for problems with consistency. Additionally, you would need to keep availing the engineering team’s help to keep the code up to date with Magento’s changing API.
• Data Transformations: You will also need to write additional code for any data transformations as this method has no built-in functionality to take care of that. This can make the process even more lengthy.

Method 2: Using Hevo Data to Move Data from Magento to Snowflake

Hevo, an official Snowflake data pipeline partner, makes moving data from Magento to Snowflake a very simple process. Hevo enables the lowest time to production for such copy operations, allowing developers to focus on their core business logic rather than waste time on the configuration nightmares involved in setting these up.

Sign up here for a 14-Day Free Trial!

Magento uses MySQL database as its underlying data store. Hevo can connect to this directly and extract data. The following are the steps:

• Step 1: Magento Platform uses MySQL database as an underlying data store. Hevo connects directly to Magento’s MySQL database rather than connecting through the APIs. This approach ensures the most comprehensive coverage in terms of data and more reliability. To set up MySQL database as a source you need to select the source type, pipeline mode, and MySQL Connection settings.

• Step 2: Complete Magento to Snowflake migration by providing your destination name, account name, region of your account, database username and password, database and schema name, and the Data Warehouse name.

Hevo automatically maps all of your data to its relevant tables in Snowflake, giving you access to the Magento data in real-time. Sign up for a free and no-risk 14-day trial with Hevo to experience a hassle-free data migration from Magento to Snowflake.

Additionally, Hevo helps you clean, enrich and transform data before and after it is moved into your data warehouse. This ensures that your data is analysis-ready at any point in the data warehouse.

The Hevo Advantage:

• Real-time Data – Hevo allows you to have up-to-date data in your Snowflake data warehouse through its real-time streaming architecture. This makes you able to get insights in real-time without any delays.
• Transformations – Hevo provides preload transformations through Python code. It also allows you to run transformation code for each event in the pipelines you set up. You need to edit the event object’s properties received in the transform method as a parameter to carry out the transformation. Hevo also offers drag and drop transformations like Date and Control Functions, JSON, and Event Manipulation to name a few. These can be configured and tested before putting them to use.
• Connectors – Hevo supports 100+ integrations to SaaS platforms, files, databases, analytics, and BI tools. It supports various destinations including Google BigQuery, Amazon Redshift, Snowflake Data Warehouses; Amazon S3 Data Lakes; and MySQL, MongoDB, TokuDB, DynamoDB, PostgreSQL databases to name a few.
• Simplicity – Hevo is very intuitive and easy to use. Data can be loaded from any data source into Snowflake with just a few clicks.
• Reliable Data Load – Hevo has an inbuilt fault-tolerance architecture that ensures that data loads are consistent and reliable with no data loss. 
• Scalability – Hevo is built to handle any scale of data. It can also handle data from a wide variety of sources in addition to Magento. This makes it able to help you scale your data infrastructure as your data needs increase.
• Minimal Setup – Hevo is a fully managed platform. This means that setting it up requires very little effort on your part.

Conclusion

This blog talks about the two methods you can use to connect Magento to Snowflake in a seamless fashion: using Custom ETL Scripts and a third-party tool, Hevo.

Visit our Website to Explore Hevo

Extracting complex data from a diverse set of data sources can be a challenging task and this is where Hevo saves the day! Hevo offers a faster way to move data from Databases or SaaS applications such as Magento into your Data Warehouse like Snowflake to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code.

As we have seen, Hevo greatly simplifies the process of migrating data from your Magento to Snowflake or indeed any other source and destination. Sign Up for your 14-day free trial and experience stress-free data migration today! You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.