Stage¶
In MatrixOne, Stage is used to connect to external storage locations, such as AWS S3, MinIO, or a file system, so that data files can be imported, exported, and managed in batches. DATALINK is a data type used to reference and access data files in external storage. It allows the database to associate external resources directly without storing the file content in the database. This structured management reduces database storage requirements and provides flexible data access.
Using an external Stage together with DATALINK enables efficient data file management and processing. With this combination, the platform can store and load large external files, such as datasets and model files, more easily, and provide continuous file updates for analysis and processing tasks. This is especially useful for scenarios that require fast access and dynamic updates, such as periodic data analysis, model training, and real-time data processing.
Use cases¶
Stage and DATALINK are suitable for a wide range of scenarios, especially applications that require large-scale data processing, analysis, or model training. Typical use cases include:
Dataset management and processing: In daily data processing and analysis, large datasets are often stored in external storage, such as AWS S3. With an external Stage, the platform can configure and access these data sources easily. With the DATALINK data type, it can reference and read file locations directly without importing data into the database. This reduces database storage usage, loads datasets quickly, and helps data scientists perform data cleaning and preprocessing.
Machine learning model training and updates: As data changes, machine learning models need to be updated frequently to keep predictions accurate. In this case, an external Stage can store training datasets and model files, and DATALINK can reference model files quickly. When training a new model, the latest data can be loaded directly from external storage, and the trained model version can be saved externally for later use in production.
Multimedia file management and analysis: When a service manages large numbers of images, audio files, videos, or other multimedia files, storing them directly in the database can be costly and inefficient. With external Stage and DATALINK, multimedia files can be stored in external systems and referenced through DATALINK for flexible access. This is useful for workloads that need dynamic access to many files, content analysis, or media recommendation.
Long-term storage and query of logs and audit files: Log data and audit files usually need to be stored for a long time to meet compliance requirements. Storing them directly in the database can consume a large amount of space. By storing logs in an external system through Stage and referencing them with DATALINK, you can reduce storage pressure, improve query efficiency, and meet audit requirements.
Periodic reports and data backups: Periodic reports and backup files generated by business operations can be stored in external storage and referenced through DATALINK. This simplifies backup and recovery operations and supports historical data tracing.
These scenarios show the flexibility and efficiency of combining external Stage and DATALINK for data management. With this approach, enterprises can manage and access large data files efficiently at lower cost, providing strong support for data processing and analysis.
Example¶
An e-commerce platform generates a large amount of order data every day, including user purchases, product information, payment status, and other details. To improve sales forecasting and user behavior analysis, the platform saves the daily orders as CSV files and uploads them to a MatrixOne external Stage for data analysis and machine learning.
Procedure¶
Create a bucket on S3¶
In this example, AWS S3 is used. Create a bucket named orders-bucket and upload the daily order files to S3.
Create a Stage¶
First, create an external Stage in MatrixOne that points to the data storage location on S3.
create stage stage01 url = 's3://orders-bucket/test' credentials = {"aws_key_id"='xxxx',"aws_secret_key"='xxxx',"AWS_REGION"='us-west-2','PROVIDER'='Amazon', 'ENDPOINT'='s3.us-west-2.amazonaws.com'};
mysql> select * from stage_list('stage://stage01') as f;
+-----------------------------+
| file |
+-----------------------------+
| /test/orders_2023_11_06.csv |
+-----------------------------+
1 row in set (0.01 sec)
Create a table to manage dataset files¶
Create an order_documents table to record CSV file metadata, and use a DATALINK column to store the file path. DATALINK makes file storage and access more efficient. It also allows you to inspect and preprocess file content before importing data, which helps ensure data quality. In addition, DATALINK supports multi-source data integration, making cross-system file references easier and avoiding the need to load file data directly into the database. This reduces storage pressure and improves data processing flexibility.
CREATE TABLE order_documents (
document_id INT AUTO_INCREMENT,
file_link DATALINK -- CSV file link
);
INSERT INTO order_documents (file_link) VALUES
('stage://stage01/orders_2023_11_06.csv');
select document_id,load_file(file_link) from order_documents;
mysql> select document_id,load_file(file_link) from order_documents;
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| document_id | load_file(file_link) |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | OrderID,UserID,ProductID,Quantity,TotalAmount,PaymentStatus,OrderDate
1001,123,2001,2,49.99,Completed,2023-11-06
1002,124,2002,1,19.99,Pending,2023-11-06
1003,125,2003,3,29.97,Completed,2023-11-06
1004,126,2001,1,24.99,Cancelled,2023-11-06
1005,127,2004,4,79.96,Completed,2023-11-06
1006,128,2005,2,39.98,Completed,2023-11-06
1007,129,2006,1,15.99,Pending,2023-11-06
1008,130,2002,2,39.98,Completed,2023-11-06
1009,131,2007,1,9.99,Completed,2023-11-06
1010,132,2008,5,49.95,Completed,2023-11-06
|
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)
Load data into a table¶
After the daily order file is uploaded, use the following SQL statement to load data into the MatrixOne orders table for further analysis and queries.
CREATE TABLE orders (
OrderID INT,
UserID INT,
ProductID INT,
Quantity INT,
TotalAmount DECIMAL(10, 2),
PaymentStatus VARCHAR(20),
OrderDate DATE
);
mysql> load data infile 'stage://stage01/orders_2023_11_06.csv' into table orders fields terminated by ',' ignore 1 lines;
Query OK, 10 rows affected (0.56 sec)
mysql> select * from orders;
+---------+--------+-----------+----------+-------------+---------------+------------+
| orderid | userid | productid | quantity | totalamount | paymentstatus | orderdate |
+---------+--------+-----------+----------+-------------+---------------+------------+
| 1001 | 123 | 2001 | 2 | 49.99 | Completed | 2023-11-06 |
| 1002 | 124 | 2002 | 1 | 19.99 | Pending | 2023-11-06 |
| 1003 | 125 | 2003 | 3 | 29.97 | Completed | 2023-11-06 |
| 1004 | 126 | 2001 | 1 | 24.99 | Cancelled | 2023-11-06 |
| 1005 | 127 | 2004 | 4 | 79.96 | Completed | 2023-11-06 |
| 1006 | 128 | 2005 | 2 | 39.98 | Completed | 2023-11-06 |
| 1007 | 129 | 2006 | 1 | 15.99 | Pending | 2023-11-06 |
| 1008 | 130 | 2002 | 2 | 39.98 | Completed | 2023-11-06 |
| 1009 | 131 | 2007 | 1 | 9.99 | Completed | 2023-11-06 |
| 1010 | 132 | 2008 | 5 | 49.95 | Completed | 2023-11-06 |
+---------+--------+-----------+----------+-------------+---------------+------------+
10 rows in set (0.01 sec)
Unload data to a Stage¶
Assume that a merchant needs to export daily order data from the orders table to an external storage system, such as AWS S3, for backup, data sharing, or partner integration. You can unload data to a Stage.
-- Create stage02 pointing to an external storage system.
create stage stage02 url = 's3://order-share-bucket/' credentials = {"aws_key_id"='xxxx',"aws_secret_key"='xxxx',"AWS_REGION"='us-west-2','PROVIDER'='Amazon', 'ENDPOINT'='s3.us-west-2.amazonaws.com'};
-- Unload data from the orders table to stage02.
select * from orders into outfile 'stage://stage02/order_share.csv';
-- The data has been unloaded successfully.
mysql> select * from stage_list('stage://stage02') as f;
+------------------+
| file |
+------------------+
| /order_share.csv |
+------------------+
1 row in set (0.01 sec)