Converting a Disk Table into an MOT Table
Tags: opengauss
Published: 2023-04-19 04:01:04

The direct conversion of disk tables into MOT tables is not yet possible, meaning that no ALTER TABLE statement yet exists that converts a disk-based table into an MOT table.

The following describes how to manually perform a few steps in order to convert a disk-based table into an MOT table, as well as how the **gs_dump** tool is used to export data and the **gs_restore** tool is used to import data.

**Prerequisite Check**
Check that the schema of the disk table to be converted into an MOT table contains all required columns.

Check whether the schema contains any unsupported column data types, as described in the Unsupported Data Types_ _section.

If a specific column is not supported, then it is recommended to first create a secondary disk table with an updated schema. This schema is the same as the original table, except that all the unsupported types have been converted into supported types.

Afterwards, use the following script to export this secondary disk table and then import it into an MOT table.

**Converting**
To covert a disk-based table into an MOT table, perform the following –

1. Suspend application activity.
2. Use gs_dump tool to dump the table’s data into a physical file on disk. Make sure to use the data only.
3. Rename your original disk-based table.
4. Create an MOT table with the same table name and schema. Make sure to use the create FOREIGN keyword to specify that it will be an MOT table.
5. Use gs_restore to load/restore data from the disk file into the database table.
6. Visually/manually verify that all the original data was imported correctly into the new MOT table. An example is provided below.
7. Resume application activity.

**IMPORTANT Note** – In this way, since the table name remains the same, application queries and relevant database stored-procedures will be able to access the new MOT table seamlessly without code changes. An additional method is to copy data from a regular (Heap) table into the new MOT table by using an “INSERT INTO SELECT” statement.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i9fgsshw0amrt0jre9oy.png)

This method is subject to MOT transaction size limitation of less than 1 GB.

**Conversion Example**
Let's say that you have a database name **benchmarksql** and a table named **customer** (which is a disk-based table) to be migrated it into an MOT table.

To migrate the customer table into an MOT table, perform the following –

1. Check your source table column types. Verify that all types are supported by MOT, refer to section Unsupported Data Types.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jhno50gpfnou096d5ugo.png)

2. Check your source table data.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7tbgq6a1hua78hdo897y.png)

3. Dump table data only by using **gs_dump**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/unxfby40sl9n924c3u7f.png)

4. Rename the source table name.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ss0hr26oa8ahhr1e6m7w.png)

5. Create the MOT table to be exactly the same as the source table.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jpttisgg45d8yct36pdz.png)

6. Import the source dump data into the new MOT table.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vge35o6kmcjd41txj96s.png)