Skip to content

DATALINK type

The DATALINK type is a special data type used to store links to documents (such as satges) or files. Its main purpose is to store the link address of the document in the database, rather than storing the document itself. This type can be applied in various scenarios, especially when dealing with large-scale document management, providing quick access to documents without actually storing the documents in a database.

Use the DATALINK data type to:

  • Saves storage space: the document is actually stored in external storage (such as an object storage system), while the database only saves the link.
  • Convenient document access: By storing links, the system can quickly access documents without additional storage and processing.
  • Improve data operation efficiency: Avoid processing large files directly in the database, improving the speed and efficiency of data operations.

GRAMMAR STRUCTURE

INSERT INTO TABLE_NAME VALUES ('<file://<path>/<filename>>|<stage://<stage_name>/<path>/<file_name>>?<offset=xx>&<size=xxx>' )

Parameter explanation

Parameters Description
file Points to the local file system file location.
stage points to stage pointing to file location.
offset Optional. Offset, indicating the starting point of the read content.
size Optional. Specifies the size of the read content, in subsections.

If you want to read the data pointed by DATALINK to a file link, you can use the load_file function.

Example

There is a file t1.csv under /Users/admin/case

(base) admin@192 case % cat t1.csv 
this is a test message

drop table test01;
create table test01 (col1 int, col2 datalink);
create stage stage01 url='file:///Users/admin/case/';
insert into test01 values (1, 'file:///Users/admin/case/t1.csv');
insert into test01 values (2, 'file:///Users/admin/case/t1.csv?size=2');
insert into test01 values (3, 'file:///Users/admin/case/t1.csv?offset=4');
insert into test01 values (4, 'file:///Users/admin/case/t1.csv?offset=4&size=2');
insert into test01 values (5, 'stage://stage01/t1.csv');
insert into test01 values (6, 'stage://stage01/t1.csv?size=2');
insert into test01 values (7, 'stage://stage01/t1.csv?offset=4');
insert into test01 values (8, 'stage://stage01/t1.csv?offset=4&size=2');

mysql> select * from test01;
+------+-------------------------------------------------+
| col1 | col2                                            |
+------+-------------------------------------------------+
|    1 | file:///Users/admin/case/t1.csv                 |
|    2 | file:///Users/admin/case/t1.csv?size=2          |
|    3 | file:///Users/admin/case/t1.csv?offset=4        |
|    4 | file:///Users/admin/case/t1.csv?offset=4&size=2 |
|    5 | stage://stage01/t1.csv                          |
|    6 | stage://stage01/t1.csv?size=2                   |
|    7 | stage://stage01/t1.csv?offset=4                 |
|    8 | stage://stage01/t1.csv?offset=4&size=2          |
+------+-------------------------------------------------+
8 rows in set (0.01 sec)

mysql> select col1, load_file(col2) from test01;
+------+-------------------------+
| col1 | load_file(col2)         |
+------+-------------------------+
|    1 | this is a test message
 |
|    2 | th                      |
|    3 |  is a test message
     |
|    4 |  i                      |
|    5 | this is a test message
 |
|    6 | th                      |
|    7 |  is a test message
     |
|    8 |  i                      |
+------+-------------------------+
8 rows in set (0.01 sec)