Streaming import

This document describes how to use SQL statements to stream data in MatrixOne Intelligence. Specifically, MatrixOne Intelligence supports importing strings organized in csv format using the LOAD DATA INLINE syntax, and the import speed is faster than the INSERT operation.

Syntax Structure

mysql> LOAD DATA INLINE
FORMAT='csv',
DATA=$XXX$
csv_string $XXX$
INTO TABLE tbl_name;

Parameter explanation

FORMAT='csv' means that the string data in the following DATA is organized in the format csv.

DATA=$XXX$ in csv_string $XXX$is the identifier of the start and end of the data.csv_stringis to organize string data incsvas the format, with\nor\r\n` as the newline character.

Note

$XXX$ is the identifier of the start and end of the data. Note that $XXX$ at the end of the data needs to be placed in the same line as the last line. A line break may cause ERROR 20101

Import data using the LOAD DATA INLINE command in MySQL Client

You can use LOAD DATA INLINE to import streaming data into MatrixOne Intelligence. This chapter will explain how to do streaming imports and give examples of importing csv data.

1. Create a MatrixOne Intelligence instance.

2. Connect the MatrixOne Intelligence service through MySQL Client.

3. Before executing LOAD DATA INLINE in the MatrixOne Intelligence database, you need to create the data table user in advance:

   CREATE TABLE `user` (
   `name` VARCHAR(255) DEFAULT null,
   `age` INT DEFAULT null,
   `city` VARCHAR(255) DEFAULT null
   )
   

4. Execute LOAD DATA INLINE on the MySQL client to import data, and import data in csv format:

   mysql> LOAD DATA INLINE
   FORMAT='csv',
   DATA=$XXX$
   Lihua,23,Shanghai
   Bob,25,Beijing $XXX$
   INTO TABLE user;
   

Use the LOAD DATA INLINE command to import data in Python-pymysql application

PyMySQL is a pure Python MySQL client library. The following will guide you how to use PyMySQL for LOAD DATA INLINE operations.

1. Create a MatrixOne Intelligence instance.

2. Connect the MatrixOne Intelligence service through MySQL Client

3. Use the MySQL client to connect to the MatrixOne Intelligence instance and create a new data table with the name test database and user:

   mysql> create database test;
   use test;
   CREATE TABLE `user` (
   `name` VARCHAR(255) DEFAULT null,
   `age` INT DEFAULT null,
   `city` VARCHAR(255) DEFAULT null
   );
   

4. Download and install pymysql:

   pip3 install pymysql
   
   #If you are in China mainland and have a low downloading speed, you can speed up the download by following commands.
   pip3 install pymysql -i https://pypi.tuna.tsinghua.edu.cn/simple
   

5. Create a plain text file pymysql_load_data_inline.py and write the code to the file. Here as a demonstration csv_string only writes two lines. You can decide the size of csv_string based on your own streaming data:

   #!/usr/bin/python3
   import pymysql
   
   # Open database connection
   db = pymysql.connect(
       host='freetier-01.cn-hangzhou.cluster.matrixonecloud.cn',
       port=6001,
       user='585b49fc_852b_4bd1_b6d1_d64bc1d8xxxx:admin:accountadmin',
       password = "your_password",
       db='test',
       )
   # prepare a cursor object using cursor() method
   cursor = db.cursor()
   csv_string="Lihua,23,Shanghai \n Bob,25,Beijing"
   sql="load data inline format='csv',data=$XXX$" + csv_string + " $XXX$ into table user;"
   # execute SQL query using execute() method.
   cursor.execute(sql)
   data=db.commit()
   # Fetch a single row using fetchone() method.
   #disconnect from server
   db.close()
   

6. Open a terminal and execute the following commands in the terminal:

   python3 pymysql_load_data_inline.py
   

7. Open the mysql client and query the data in the data table. The results are as follows:

   mysql> select * from user;
   +--------+---------------------+
   | name | age | city |
   +--------+---------------------+
   | Lihua | 23 | Shanghai |
   | Bob | 25 | Beijing |
   +--------+---------------------+
   2 rows in set (0.02 sec)