暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / MogDB数据导入

MogDB数据导入

原创 MogDB 2024-08-07
233

导入方式

MogDB数据库提供了灵活的数据入库方式: INSERT、COPY以及gsql元命令\copy。各方式具有不同的特点，具体请参见表1。

表 1 导入方式特点说明

方式 特点
INSERT 通过INSERT语句插入一行或多行数据，及从指定表插入数据。
COPY 通过COPY FROM STDIN语句直接向MogDB数据库写入数据。
通过JDBC驱动的CopyManager接口从其他数据库向MogDB数据库写入数据时，具有业务数据无需落地成文件的优势。
gsql工具的元命令\copy 与直接使用SQL语句COPY不同，该命令读取/写入的文件只能是gsql客户端所在机器上的本地文件。
说明:
\COPY只适合小批量、格式良好的数据导入，不会对非法字符做预处理，也无容错能力，无法适用于含有异常数据的场景。导入数据应优先选择COPY。

通过INSERT语句直接写入数据

用户可以通过以下方式执行INSERT语句直接向MogDB数据库写入数据:

  • 使用MogDB数据库提供的客户端工具向MogDB数据库写入数据。

  • 通过JDBC/ODBC驱动连接数据库执行INSERT语句向MogDB数据库写入数据。

    详细内容请参见连接数据库。

MogDB数据库支持完整的数据库事务级别的增删改操作。INSERT是最简单的一种数据写入方式，这种方式适合数据写入量不大，并发度不高的场景。

使用COPY FROM STDIN导入数据


关于COPY FROM STDIN导入数据

用户可以使用以下方式通过COPY FROM STDIN语句直接向MogDB写入数据。

  • 通过键盘输入向MogDB数据库写入数据。详细请参见COPY。
  • 通过JDBC驱动的CopyManager接口从文件或者数据库向MogDB写入数据。此方法支持COPY语法中copy option的所有参数。

CopyManager类简介

CopyManager是MogDB JDBC驱动中提供的一个API接口类，用于批量向MogDB数据库中导入数据。


CopyManager的继承关系

CopyManager类位于org.opengauss.copy Package中，继承自java.lang.Object类，该类的声明如下:

public class CopyManager
extends Object

构造方法

public CopyManager(BaseConnection connection)
throws SQLException

常用方法

表 1 CopyManager常用方法

返回值 方法 描述 throws
CopyIn copyIn(String sql) - SQLException
long copyIn(String sql, InputStream from) 使用COPY FROM STDIN从InputStream中快速向数据库中的表导入数据。 SQLException,IOException
long copyIn(String sql, InputStream from, int bufferSize) 使用COPY FROM STDIN从InputStream中快速向数据库中的表导入数据。 SQLException,IOException
long copyIn(String sql, Reader from) 使用COPY FROM STDIN从Reader中快速向数据库中的表导入数据。 SQLException,IOException
long copyIn(String sql, Reader from, int bufferSize) 使用COPY FROM STDIN从Reader中快速向数据库中的表导入数据。 SQLException,IOException
CopyOut copyOut(String sql) - SQLException
long copyOut(String sql, OutputStream to) 将一个COPY TO STDOUT的结果集从数据库发送到OutputStream类中。 SQLException,IOException
long copyOut(String sql, Writer to) 将一个COPY TO STDOUT的结果集从数据库发送到Writer类中。 SQLException,IOException

处理错误表

操作场景

当数据导入发生错误时，请根据本文指引信息进行处理。

查询错误信息

数据导入过程中发生的错误，一般分为数据格式错误和非数据格式错误。

  • 数据格式错误

    在创建外表时，通过设置参数“LOG INTO error_table_name”，将数据导入过程中出现的数据格式错误信息写入指定的错误信息表error_table_name中。您可以通过以下SQL，查询详细错误信息。

    MogDB=# SELECT * FROM error_table_name;

    错误信息表结构如表1所示。

    表 1 错误信息表

    列名称 类型 描述
    nodeid integer 报错节点编号。
    begintime timestamp with time zone 出现数据格式错误的时间。
    filename character varying 出现数据格式错误的数据源文件名。
    rownum numeric 在数据源文件中，出现数据格式错误的行号。
    rawrecord text 在数据源文件中，出现数据格式错误的原始记录。
    detail text 详细错误信息。

  • 非数据格式错误

    对于非数据格式错误，一旦发生将导致整个数据导入失败。您可以根据执行数据导入过程中，界面提示的错误信息，帮助定位问题，处理错误表。

处理数据导入错误

根据获取的错误信息，请对照下表，处理数据导入错误。

表 2 处理数据导入错误

错误信息 原因 解决办法
missing data for column “r_reason_desc” 1. 数据源文件中的列数比外表定义的列数少。
2. 对于TEXT格式的数据源文件，由于转义字符（\）导致delimiter（分隔符）错位或者quote（引号字符）错位造成的错误。
示例：目标表存在3列字段，导入的数据如下所示。由于存在转义字符“\”，分隔符“|”被转义为第二个字段的字段值，导致第三个字段值缺失。
BE|Belgium|1		 1. 由于列数少导致的报错，选择下列办法解决：
- 在数据源文件中，增加列“r_reason_desc”的字段值。
- 在创建外表时，将参数“fill_missing_fields”设置为“on”。即当导入过程中，若数据源文件中一行数据的最后一个字段缺失，则把最后一个字段的值设置为NULL，不报错。
2. 对由于转义字符导致的错误，需检查报错的行中是否含有转义字符（\）。若存在，建议在创建外表时，将参数“noescaping”（是否不对’\'和后面的字符进行转义）设置为true。
extra data after last expected column 数据源文件中的列数比外表定义的列数多。 - 在数据源文件中，删除多余的字段值。
- 在创建外表时，将参数“ignore_extra_data”设置为“on”。即在导入过程中，若数据源文件比外表定义的列数多，则忽略行尾多出来的列。
invalid input syntax for type numeric: “a” 数据类型错误。 在数据源文件中，修改输入字段的数据类型。根据此错误信息，请将输入的数据类型修改为numeric。
null value in column “staff_id” violates not-null constraint 非空约束。 在数据源文件中，增加非空字段信息。根据此错误信息，请增加“staff_id”列的值。
duplicate key value violates unique constraint “reg_id_pk” 唯一约束。 - 删除数据源文件中重复的行。
- 通过设置关键字“DISTINCT”，从SELECT结果集中删除重复的行，保证导入的每一行都是唯一的。
MogDB=# INSERT INTO reasons SELECT DISTINCT * FROM foreign_tpcds_reasons;
value too long for type character varying(16) 字段值长度超过限制。 在数据源文件中，修改字段值长度。根据此错误信息，字段值长度限制为VARCHAR2(16)。

示例1: 通过本地文件导入导出数据

在使用JAVA语言基于MogDB进行二次开发时，可以使用CopyManager接口，通过流方式，将数据库中的数据导出到本地文件或者将本地文件导入数据库中，文件格式支持CSV、TEXT等格式。

样例程序如下，执行时需要加载MogDB的JDBC驱动。

import java.sql.Connection;
import java.sql.DriverManager;
import java.io.IOException;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.SQLException;
import org.opengauss.copy.CopyManager;
import org.opengauss.core.BaseConnection;

public class Copy{

     public static void main(String[] args)
     {
      String urls = new String("jdbc:opengauss://localhost:8000/postgres"); //数据库URL
      String username = new String("username");            //用户名
      String password = new String("passwd");             //密码
      String tablename = new String("migration_table"); //定义表信息
      String tablename1 = new String("migration_table_1"); //定义表信息
      String driver = "org.opengauss.Driver";
      Connection conn = null;

      try {
          Class.forName(driver);
          conn = DriverManager.getConnection(urls, username, password);
      } catch (ClassNotFoundException e) {
           e.printStackTrace(System.out);
      } catch (SQLException e) {
           e.printStackTrace(System.out);
      }

      // 将表migration_table中数据导出到本地文件d:/data.txt
      try {
          copyToFile(conn, "d:/data.txt", "(SELECT * FROM migration_table)");
      } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
      } catch (IOException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
      }
      //将d:/data.txt中的数据导入到migration_table_1中。
      try {
          copyFromFile(conn, "d:/data.txt", tablename1);
      } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
      } catch (IOException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
      }

      // 将表migration_table_1中的数据导出到本地文件d:/data1.txt
      try {
          copyToFile(conn, "d:/data1.txt", tablename1);
      } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
      } catch (IOException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
      }
  }

  public static void copyFromFile(Connection connection, String filePath, String tableName)
         throws SQLException, IOException {

     FileInputStream fileInputStream = null;

     try {
         CopyManager copyManager = new CopyManager((BaseConnection)connection);
         fileInputStream = new FileInputStream(filePath);
         copyManager.copyIn("COPY " + tableName + " FROM STDIN ", fileInputStream);
     } finally {
         if (fileInputStream != null) {
             try {
                 fileInputStream.close();
             } catch (IOException e) {
                 e.printStackTrace();
             }
         }
     }
 }
     public static void copyToFile(Connection connection, String filePath, String tableOrQuery)
          throws SQLException, IOException {

      FileOutputStream fileOutputStream = null;

      try {
          CopyManager copyManager = new CopyManager((BaseConnection)connection);
          fileOutputStream = new FileOutputStream(filePath);
          copyManager.copyOut("COPY " + tableOrQuery + " TO STDOUT", fileOutputStream);
      } finally {
          if (fileOutputStream != null) {
              try {
                  fileOutputStream.close();
              } catch (IOException e) {
                  e.printStackTrace();
              }
          }
      }
  }
}

示例2: 从MY向MogDB数据库进行数据迁移

下面示例演示如何通过CopyManager从MY向MogDB数据库进行数据迁移的过程。

import java.io.StringReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.opengauss.copy.CopyManager;
import org.opengauss.core.BaseConnection;

public class Migration{

    public static void main(String[] args) {
        String url = new String("jdbc:opengauss://localhost:8000/postgres"); //数据库URL
        String user = new String("username");            //MogDB数据库用户名
        String pass = new String("passwd");             //MogDB数据库密码
        String tablename = new String("migration_table_1"); //定义表信息
        String delimiter = new String("|");              //定义分隔符
        String encoding = new String("UTF8");            //定义字符集
        String driver = "org.opengauss.Driver";
        StringBuffer buffer = new StringBuffer();       //定义存放格式化数据的缓存

        try {
            //获取源数据库查询结果集
            ResultSet rs = getDataSet();

            //遍历结果集，逐行获取记录
            //将每条记录中各字段值，按指定分隔符分割，由换行符结束，拼成一个字符串
            //把拼成的字符串，添加到缓存buffer
            while (rs.next()) {
                buffer.append(rs.getString(1) + delimiter
                        + rs.getString(2) + delimiter
                        + rs.getString(3) + delimiter
                        + rs.getString(4)
                        + "\n");
            }
            rs.close();

            try {
                //建立目标数据库连接
                Class.forName(driver);
                Connection conn = DriverManager.getConnection(url, user, pass);
                BaseConnection baseConn = (BaseConnection) conn;
                baseConn.setAutoCommit(false);

                //初始化表信息
                String sql = "Copy " + tablename + " from STDIN with (DELIMITER " + "'" + delimiter + "'" +","+ " ENCODING " + "'" + encoding + "')";

                //提交缓存buffer中的数据
                CopyManager cp = new CopyManager(baseConn);
                StringReader reader = new StringReader(buffer.toString());
                cp.copyIn(sql, reader);
                baseConn.commit();
                reader.close();
                baseConn.close();
            } catch (ClassNotFoundException e) {
                e.printStackTrace(System.out);
            } catch (SQLException e) {
                e.printStackTrace(System.out);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //********************************
    // 从源数据库返回查询结果集
    //*********************************
    private static ResultSet getDataSet() {
        ResultSet rs = null;
        try {
            Class.forName("com.MY.jdbc.Driver").newInstance();
            Connection conn = DriverManager.getConnection("jdbc:MY://10.119.179.227:3306/jack?useSSL=false&allowPublicKeyRetrieval=true", "jack", "Enmo@123");
            Statement stmt = conn.createStatement();
            rs = stmt.executeQuery("select * from migration_table");
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return rs;
    }
}

使用gsql元命令导入数据

gsql工具提供了元命令\copy进行数据导入。


\copy命令

语法

\copy { table [ ( column_list ) ] |

( query ) } { from | to } { filename |

stdin | stdout | pstdin | pstdout }

[ with ] [ binary ] [ delimiter

[ as ] 'character' ] [ null [ as ] 'string' ]

[ csv [ header ] [ quote [ as ]

'character' ] [ escape [ as ] 'character' ]

[ force quote column_list | * ] [ force

not null column_list ] ]

在任何gsql客户端登录数据库成功后，可以使用该命令进行数据的导入/导出。但是与SQL的COPY命令不同，该命令读取/写入的文件是本地文件，而非数据库服务器端文件；所以，要操作的文件的可访问性、权限等，都是受限于本地用户的权限。

说明:

\COPY只适合小批量、格式良好的数据导入，不会对非法字符做预处理，也无容错能力，无法适用于含有异常数据的场景。导入数据应优先选择GDS或COPY。

参数说明

  • table

    表的名称（可以有模式修饰）。

    取值范围: 已存在的表名。

  • column_list

    可选的待拷贝字段列表。

    取值范围: 任意字段。如果没有声明字段列表，将使用所有字段。

  • query

    其结果将被拷贝。

    取值范围: 一个必须用圆括弧包围的SELECT或VALUES命令。

  • filename

    文件的绝对路径。执行copy命令的用户必须有此路径的写权限。

  • stdin

    声明输入是来自标准输入。

  • stdout

    声明输出打印到标准输出。

  • pstdin

    声明输入是来自gsql的标准输入。

  • pstout

  • 声明输出打印到gsql的标准输出。

  • binary

    使用二进制格式存储和读取，而不是以文本的方式。在二进制模式下，不能声明DELIMITER，NULL，CSV选项。指定binary类型后，不能再通过option或copy_option指定CSV、FIXED、TEXT等类型。

  • delimiter [ as ] ‘character’

    指定数据文件行数据的字段分隔符。

    img 说明:

    • 分隔符不能是\r和\n。
    • 分隔符不能和null参数相同，CSV格式数据的分隔符不能和quote参数相同。
    • TEXT格式数据的分隔符不能包含: \.abcdefghijklmnopqrstuvwxyz0123456789。
    • 数据文件中单行数据长度需<1GB，如果分隔符较长且数据列较多的情况下，会影响导出有效数据的长度。
    • 分隔符推荐使用多字符和不可见字符。多字符例如’$^&’；不可见字符例如0x07，0x08，0x1b等。

    取值范围: 支持多字符分隔符，但分隔符不能超过10个字节。

    默认值:

    • TEXT格式的默认分隔符是水平制表符（tab）。
    • CSV格式的默认分隔符为","。
    • FIXED格式没有分隔符。

  • null [ as ] ‘string’

    用来指定数据文件中空值的表示。

    取值范围:

    • null值不能是\r和\n，最大为100个字符。

    • null值不能和分隔符、quote参数相同。

      默认值:

    • CSV格式下默认值是一个没有引号的空字符串。

    • 在TEXT格式下默认值是\N。

  • header

    指定导出数据文件是否包含标题行，标题行一般用来描述表中每个字段的信息。header只能用于CSV，FIXED格式的文件中。

    在导入数据时，如果header选项为on，则数据文本第一行会被识别为标题行，会忽略此行。如果header为off，而数据文件中第一行会被识别为数据。

    在导出数据时，如果header选项为on，则需要指定fileheader。fileheader是指定导出数据包含标题行的定义文件。如果header为off，则导出数据文件不包含标题行。

    取值范围: true/on，false/off。

    默认值: false

  • quote [ as ] ‘character’

    CSV格式文件下的引号字符。

    默认值: 双引号。

    img 说明:

    • quote参数不能和分隔符、null参数相同。
    • quote参数只能是单字节的字符。
    • 推荐不可见字符作为quote，例如0x07，0x08，0x1b等。

  • escape [ as ] ‘character’

    CSV格式下，用来指定逃逸字符，逃逸字符只能指定为单字节字符。

    默认值: 双引号。当与quote值相同时，会被替换为’\0’。

  • force quote column_list | *

    在CSV COPY TO模式下，强制在每个声明的字段周围对所有非NULL值都使用引号包围。NULL输出不会被引号包围。

    取值范围: 已存在的字段。

  • force not null column_list

    在CSV COPY FROM模式下，指定的字段输入不能为空。

    取值范围: 已存在的字段。

  • force null column_list

    在CSV COPY FROM模式下，指定的字段输入表示空值的字符串设为空。

    取值范围：已存在的字段。


任务示例

  1. 创建目标表a。

    MogDB=# CREATE TABLE a(a int);

  2. 导入数据。

    从stdin拷贝数据到目标表a。

    MogDB=# \copy a from stdin;

    出现>>符号提示时，输入数据，输入\.时结束。

    Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1
>> 2
>> \.

    查询导入目标表a的数据。

    MogDB=# SELECT * FROM a;
 a
---
 1
 2
(2 rows)

  3. 从本地文件拷贝数据到目标表a。假设存在本地文件/home/omm/2.csv。

    • 分隔符为“,”。

    • 在导入过程中，若数据源文件比外表定义的列数多，则忽略行尾多出来的列。

    MogDB=# \copy a FROM '/home/omm/2.csv' WITH (delimiter',',IGNORE_EXTRA_DATA 'on');

使用gs_restore命令导入数据


操作场景

gs_restore是MogDB数据库提供的与gs_dump配套的导入工具。通过该工具，可将gs_dump导出的文件导入至数据库。gs_restore支持导入的文件格式包含自定义归档格式、目录归档格式和tar归档格式。

gs_restore具备如下两种功能。

  • 导入至数据库

    如果指定了数据库，则数据将被导入到指定的数据库中。其中，并行导入必须指定连接数据库的密码。导入时生成列会自动更新，并像普通列一样保存。

  • 导入至脚本文件

    如果未指定导入数据库，则创建包含重建数据库所需的SQL语句脚本，并将其写入至文件或者标准输出。该脚本文件等效于gs_dump导出的纯文本格式文件。

gs_restore工具在导入时，允许用户选择需要导入的内容，并支持在数据导入前对等待导入的内容进行排序。


操作步骤

img 说明:
gs_restore默认是以追加的方式进行数据导入。为避免多次导入造成数据异常，在进行导入时，建议选择使用"-c" 和"-e"参数。"-c"表示在重新创建数据库对象前，清理（删除）已存在于将要还原的数据库中的数据库对象；"-e"表示当发送SQL语句到数据库时如果出现错误请退出，默认状态下会继续，且在导入后会显示一系列错误信息。

  1. 以操作系统用户omm登录数据库主节点。

  2. 使用gs_restore命令，从mogdb整个数据库内容的导出文件中，将数据库的所有对象的定义导入到backupdb。

    gs_restore -U jack /home/omm/backup/MPPDB_backup.tar -p 8000 -d backupdb -s -e -c
Password:

    表 1 常用参数说明

    参数 参数说明 举例
    -U 连接数据库的用户名。 -U jack
    -W 指定用户连接的密码。
    - 如果主机的认证策略是trust，则不会对数据库管理员进行密码验证，即无需输入-W选项；
    - 如果没有-W选项，并且不是数据库管理员，会提示用户输入密码。    		 -W abcd@123
    -d 连接数据库dbname，并直接将数据导入到该数据库中。 -d backupdb
    -p 指定服务器所监听的TCP端口或本地Unix域套接字后缀，以确保连接。 -p 8000
    -e 当发送SQL语句到数据库时如果出现错误，则退出。默认状态下会忽略错误任务并继续执行导入，且在导入后会显示一系列错误信息。 -
    -c 在重新创建数据库对象前，清理（删除）已存在于将要导入的数据库中的数据库对象。 -
    -s 只导入模式定义，不导入数据。当前的序列值也不会被导入。 -

    其他参数说明请参见《参考指南》中“工具参考 > 服务端工具 > gs_restore”章节。


示例

示例一：执行gs_restore，导入指定MPPDB_backup.dmp文件（自定义归档格式）中mogdb数据库的数据和对象定义。

gs_restore backup/MPPDB_backup.dmp -p 8000 -d backupdb
gs_restore[2017-07-21 19:16:26]: restore operation successful
gs_restore: total time: 13053  ms

示例二：执行gs_restore，导入指定MPPDB_backup.tar文件（tar归档格式）中mogdb数据库的数据和对象定义。

gs_restore backup/MPPDB_backup.tar -p 8000 -d backupdb
gs_restore[2017-07-21 19:21:32]: restore operation successful
gs_restore[2017-07-21 19:21:32]: total time: 21203  ms

示例三：执行gs_restore，导入指定MPPDB_backup目录文件（目录归档格式）中mogdb数据库的数据和对象定义。

gs_restore backup/MPPDB_backup -p 8000 -d backupdb
gs_restore[2017-07-21 19:26:46]: restore operation successful
gs_restore[2017-07-21 19:26:46]: total time: 21003  ms

示例四：执行gs_restore，将mogdb数据库的所有对象的定义导入至backupdb数据库。导入前，数据库存在完整的定义和数据，导入后，backupdb数据库只存在所有对象定义，表没有数据。

gs_restore /home/omm/backup/MPPDB_backup.tar -p 8000 -d backupdb -s -e -c
gs_restore[2017-07-21 19:46:27]: restore operation successful
gs_restore[2017-07-21 19:46:27]: total time: 32993  ms

示例五：执行gs_restore，导入MPPDB_backup.dmp文件中PUBLIC模式的所有定义和数据。在导入时会先删除已经存在的对象，如果原对象存在跨模式的依赖则需手工强制干预。

gs_restore backup/MPPDB_backup.dmp -p 8000 -d backupdb -e -c -n PUBLIC
gs_restore: [archiver (db)] Error while PROCESSING TOC:
gs_restore: [archiver (db)] Error from TOC entry 313; 1259 337399 TABLE table1 gaussdba
gs_restore: [archiver (db)] could not execute query: ERROR:  cannot drop table table1 because other objects depend on it
DETAIL:  view t1.v1 depends on table table1
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
Command was: DROP TABLE public.table1;

手工删除依赖，导入完成后再重新创建。

gs_restore backup/MPPDB_backup.dmp -p 8000 -d backupdb -e -c -n PUBLIC
gs_restore[2017-07-21 19:52:26]: restore operation successful
gs_restore[2017-07-21 19:52:26]: total time: 2203  ms

示例六：执行gs_restore，导入MPPDB_backup.dmp文件中hr模式下表hr.staffs的定义。在导入之前，hr.staffs表不存在，需要确保存在hr的schema。

gs_restore backup/MPPDB_backup.dmp -p 8000 -d backupdb -e -c -s -n hr -t staffs
gs_restore[2017-07-21 19:56:29]: restore operation successful
gs_restore[2017-07-21 19:56:29]: total time: 21000  ms

示例七: 执行gs_restore，导入MPPDB_backup.dmp文件中hr模式下表hr.staffs的数据。在导入之前，hr.staffs表不存在数据，需要确保存在hr的schema。

gs_restore backup/MPPDB_backup.dmp -p 8000 -d backupdb -e -a -n hr -t staffs
gs_restore[2017-07-21 20:12:32]: restore operation successful
gs_restore[2017-07-21 20:12:32]: total time: 20203  ms

示例八：执行gs_restore，导入指定表hr.staffs的定义。在导入之前，hr.staffs表的数据是存在的。

human_resource=# select * from hr.staffs;
 staff_id | first_name  |  last_name  |  email   |    phone_number    |      hire_date      | employment_id |  salary  | commission_pct | manager_id | section_id
----------+-------------+-------------+----------+--------------------+---------------------+---------------+----------+----------------+------------+------------
      200 | Jennifer    | Whalen      | JWHALEN  | 515.123.4444       | 1987-09-17 00:00:00 | AD_ASST       |  4400.00 |                |        101 |         10
      201 | Michael     | Hartstein   | MHARTSTE | 515.123.5555       | 1996-02-17 00:00:00 | MK_MAN        | 13000.00 |                |        100 |         20

$ gsql -d human_resource -p 8000

gsql ((MogDB x.x.x build 56189e20) compiled at 2022-01-07 18:47:53 commit 0 last mr
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

human_resource=# drop table hr.staffs CASCADE;
NOTICE:  drop cascades to view hr.staff_details_view
DROP TABLE

$ gs_restore /home/omm/backup/MPPDB_backup.tar -p 8000 -d human_resource -n hr -t staffs -s -e
Password:
restore operation successful
total time: 904  ms

human_resource=# select * from hr.staffs;
 staff_id | first_name | last_name | email | phone_number | hire_date | employment_id | salary | commission_pct | manager_id | section_id
----------+------------+-----------+-------+--------------+-----------+---------------+--------+----------------+------------+------------
(0 rows)

示例九：执行gs_restore，导入staffs和areas两个指定表的定义和数据。在导入之前，staffs和areas表不存在。

human_resource=# \d
                                 List of relations
 Schema |        Name        | Type  |  Owner   |             Storage
--------+--------------------+-------+----------+----------------------------------
 hr     | employment_history | table | omm | {orientation=row,compression=no}
 hr     | employments        | table | omm | {orientation=row,compression=no}
 hr     | places             | table | omm | {orientation=row,compression=no}
 hr     | sections           | table | omm | {orientation=row,compression=no}
 hr     | states             | table | omm | {orientation=row,compression=no}
(5 rows)

$ gs_restore /home/mogdb/backup/MPPDB_backup.tar -p 8000 -d human_resource -n hr -t staffs -n hr -t areas
Password:
restore operation successful
total time: 724  ms

human_resource=# \d
                                 List of relations
 Schema |        Name        | Type  |  Owner   |             Storage
--------+--------------------+-------+----------+----------------------------------
 hr     | areas              | table | omm | {orientation=row,compression=no}
 hr     | employment_history | table | omm | {orientation=row,compression=no}
 hr     | employments        | table | omm | {orientation=row,compression=no}
 hr     | places             | table | omm | {orientation=row,compression=no}
 hr     | sections           | table | omm | {orientation=row,compression=no}
 hr     | staffs             | table | omm | {orientation=row,compression=no}
 hr     | states             | table | omm | {orientation=row,compression=no}
(7 rows)

human_resource=# select * from hr.areas;
 area_id |       area_name
---------+------------------------
       4 | Middle East and Africa
       1 | Europe
       2 | Americas
       3 | Asia
(4 rows)

示例十：执行gs_restore，导入hr的模式，包含模式下的所有对象定义和数据。

gs_restore /home/omm/backup/MPPDB_backup1.dmp -p 8000 -d backupdb -n hr -e
Password:
restore operation successful
total time: 702  ms

示例十一：执行gs_restore，同时导入hr和hr1两个模式，仅导入模式下的所有对象定义。

gs_restore /home/omm/backup/MPPDB_backup2.dmp -p 8000 -d backupdb -n hr -n hr1 -s
Password:
restore operation successful
total time: 665  ms

示例十二：执行gs_restore，将human_resource数据库导出文件导入至backupdb数据库中。

MogDB=# create database backupdb;
CREATE DATABASE

$ gs_restore /home/omm/backup/MPPDB_backup.tar -p 8000 -d backupdb

restore operation successful
total time: 23472  ms

$ gsql -d backupdb -p 8000 -r

gsql ((MogDB x.x.x build 56189e20) compiled at 2022-01-07 18:47:53 commit 0 last mr
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

backupdb=# select * from hr.areas;
 area_id |       area_name
---------+------------------------
       4 | Middle East and Africa
       1 | Europe
       2 | Americas
       3 | Asia
(4 rows)

示例十三：用户user1不具备将导出文件中数据导入至数据库backupdb的权限，而角色role1具备该权限，要实现将文件数据导入数据库backupdb，可以在导入命令中设置-role角色为role1，使用role1的权限，完成导入目的。

human_resource=# CREATE USER user1 IDENTIFIED BY "1234@abc";
CREATE ROLE role1 with SYSADMIN IDENTIFIED BY "abc@1234";

$ gs_restore -U user1 /home/omm/backup/MPPDB_backup.tar -p 8000 -d backupdb --role role1 --rolepassword abc@1234
Password:
restore operation successful
total time: 554  ms

$ gsql -d backupdb -p 8000 -r

gsql ((MogDB x.x.x build 56189e20) compiled at 2022-01-07 18:47:53 commit 0 last mr
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

backupdb=# select * from hr.areas;
 area_id |       area_name
---------+------------------------
       4 | Middle East and Africa
       1 | Europe
       2 | Americas
       3 | Asia
(4 rows)

更新表中数据


使用DML命令更新表

MogDB支持标准的数据库操作语言（DML）命令，对表进行更新。


操作步骤

假设存在表customer_t，表结构如下:

CREATE TABLE customer_t
( c_customer_sk             integer,
  c_customer_id             char(5),
  c_first_name              char(6),
  c_last_name               char(8)
) ;

可以使用如下DML命令对表进行数据更新。

  • 使用INSERT向表中插入数据。

    • 向表customer_t中插入一行。

      INSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES (3769, 5, 'Grace','White');

    • 向表customer_t中插入多行数据。

      INSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES
(6885, 1, 'Joes', 'Hunter'),
(4321, 2, 'Lily','Carter'),
(9527, 3, 'James', 'Cook'),
(9500, 4, 'Lucy', 'Baker');

  • 使用UPDATE更新表中数据。修改字段c_customer_id值为0。

    UPDATE customer_t SET c_customer_id = 0;

    更多关于UPDATE的使用方法，请参见UPDATE。

  • 使用DELETE删除表中的行。

    可以使用WHERE子句指定需要删除的行，若不指定即删除表中所有的行，只保留数据结构。

    DELETE FROM customer_t WHERE c_last_name = 'Baker';

    更多关于DELETE的使用方法，请参见DELETE。

  • 使用TRUNCATE命令快速从表中删除所有的行。

    TRUNCATE TABLE customer_t;

    更多关于TRUNCATE的使用方法，请参见TRUNCATE。

    删除表时，DELETE语句每次删除一行数据而TRUNCATE语句是通过释放表存储的数据页来删除数据，使用TRUNCATE语句比使用DELETE语句更加快速。

    使用DELETE语句删除表时，仅删除数据，不释放存储空间。使用TRUNCATE语句删除表时，删除数据且释放存储空间。


使用合并方式更新和插入数据

在用户需要将一个表中所有的数据或大量的数据添加至现有表的场景下，MogDB提供了MERGE INTO语句通过两个表合并的方式高效地将新数据添加到现有表。

MERGE INTO语句将目标表和源表中数据针对关联条件进行匹配，若关联条件匹配时对目标表进行UPDATE，关联条件不匹配时对目标表执行INSERT。此方法可以很方便地用来将两个表合并执行UPDATE和INSERT，避免多次执行。


前提条件

进行MERGE INTO操作的用户需要同时拥有目标表的UPDATE和INSERT权限，以及源表的SELECT权限。


操作步骤

  1. 创建源表products，并插入数据。

    MogDB=# CREATE TABLE products
( product_id INTEGER,
  product_name VARCHAR2(60),
  category VARCHAR2(60)
);

MogDB=# INSERT INTO products VALUES
(1502, 'olympus camera', 'electrncs'),
(1601, 'lamaze', 'toys'),
(1666, 'harry potter', 'toys'),
(1700, 'wait interface', 'books');

  2. 创建目标表newproducts，并插入数据。

    MogDB=# CREATE TABLE newproducts
( product_id INTEGER,
  product_name VARCHAR2(60),
  category VARCHAR2(60)
);

MogDB=# INSERT INTO newproducts VALUES
(1501, 'vivitar 35mm', 'electrncs'),
(1502, 'olympus ', 'electrncs'),
(1600, 'play gym', 'toys'),
(1601, 'lamaze', 'toys'),
(1666, 'harry potter', 'dvd');

  3. 使用MERGE INTO 语句将源表products的数据合并至目标表newproducts。

    MERGE INTO newproducts np
USING products p
ON (np.product_id = p.product_id )
WHEN MATCHED THEN
  UPDATE SET np.product_name = p.product_name, np.category = p.category
WHEN NOT MATCHED THEN
  INSERT VALUES (p.product_id, p.product_name, p.category) ;

    上述语句中使用的参数说明，请见[表1](#MERGE INTO语句参数说明)。更多信息，请参见MERGE INTO。

    表 1 MERGE INTO语句参数说明

    参数 说明 举例
    INTO 子句 指定需要更新或插入数据的目标表。
    目标表支持指定别名。    		 取值：newproducts np
    说明：名为newproducts，别名为np的目标表。
    USING子句 指定源表。源表支持指定别名。 取值：products p
    说明：名为products，别名为p的源表。
    ON子句 指定目标表和源表的关联条件。
    关联条件中的字段不支持更新。    		 取值：np.product_id = p.product_id
    说明：指定的关联条件为，目标表newproducts的product_id字段和源表products的product_id字段相等。
    WHEN MATCHED子句 当源表和目标表中数据针对关联条件可以匹配上时，选择WHEN MATCHED子句进行UPDATE操作。
    - 仅支持指定一个WHEN MATCHED子句。
    - WHEN MATCHED子句可缺省，缺省时，对于满足ON子句条件的行，不进行任何操作。
    - 若目标表中存在分布列，则该列不支持更新。    		 取值：WHEN MATCHED THEN UPDATE SET np.product_name = p.product_name, np.category = p.category
    说明：当满足ON子句条件时，将目标表newproducts的product_name、category字段的值替换为源表products相对应字段的值。
    WHEN NOT MATCHED子句 当源表和目标表中数据针对关联条件无法匹配时，选择WHEN NOT MATCHED子句进行INSERT操作。
    - 仅支持指定一个WHEN NOT MATCHED子句。
    - WHEN NOT MATCHED子句可缺省。
    - 不支持INSERT子句中包含多个VALUES。
    - WHEN MATCHED和WHEN NOT MATCHED子 句顺序可以交换，可以缺省其中一个，但不能同时缺省。    		 取值：WHEN NOT MATCHED THENINSERT VALUES (p.product_id, p.product_name, p.category)
    说明：将源表products中，不满足ON子句条件的行插入目标表newproducts。

  4. 查询合并后的目标表newproducts。

    SELECT * FROM newproducts;

    返回信息如下:

     product_id |  product_name  | category
------------+----------------+-----------
      1501 | vivitar 35mm   | electrncs
      1502 | olympus camera | electrncs
      1666 | harry potter   | toys
      1600 | play gym       | toys
      1601 | lamaze         | toys
      1700 | wait interface | books
(6 rows)

深层复制

数据导入后，如果需要修改表的分区键、或者将行存表改列存、添加PCK（Partial Cluster Key）约束等场景下，可以使用深层复制的方式对表进行调整。深层复制是指重新创建表，然后使用批量插入填充表的过程。

Mog提供了三种深层复制的方式供用户选择。


使用CREATE TABLE执行深层复制

该方法使用CREATE TABLE语句创建原始表的副本，将原始表的数据填充至副本并重命名副本，完成原始表的复制。

在创建新表时，可以指定表以及列属性，比如主键。

操作步骤

执行如下步骤对表customer_t进行深层复制。

  1. 使用CREATE TABLE语句创建表customer_t的副本customer_t_copy。

    CREATE TABLE customer_t_copy
( c_customer_sk             integer,
  c_customer_id             char(5),
  c_first_name              char(6),
  c_last_name               char(8)
) ;

  2. 使用INSERT INTO…SELECT语句向副本填充原始表中的数据。

    INSERT INTO customer_t_copy (SELECT * FROM customer_t);

  3. 删除原始表。

    DROP TABLE customer_t;

  4. 使用ALTER TABLE语句将副本重命名为原始表名称。

    ALTER TABLE customer_t_copy RENAME TO customer_t;

使用CREATE TABLE LIKE执行深层复制

该方法使用CREATE TABLE LIKE语句创建原始表的副本，将原始表的数据填充至副本并重命名副本，完成原始表的复制。该方法不继承父表的主键属性，您可以使用ALTER TABLE语句来添加它们。

操作步骤

  1. 使用CREATE TABLE LIKE语句创建表customer_t的副本customer_t_copy。

    CREATE TABLE customer_t_copy (LIKE customer_t);

  2. 使用INSERT INTO…SELECT语句向副本填充原始表中的数据。

    INSERT INTO customer_t_copy (SELECT * FROM customer_t);

  3. 删除原始表。

    DROP TABLE customer_t;

  4. 使用ALTER TABLE语句将副本重命名为原始表名称。

    ALTER TABLE customer_t_copy RENAME TO customer_t;

通过创建临时表并截断原始表来执行深层复制

该方法使用CREATE TABLE AS语句创建原始表的临时表，然后截断原始表并从临时表填充它完成原始表的深层复制。

在新建表需要保留父表的主键属性，或如果父表具有依赖项的情况下，建议使用此方法。

操作步骤

  1. 使用CREATE TABLE AS语句创建表customer_t的临时表副本customer_t_temp。

    CREATE TEMP TABLE customer_t_temp AS SELECT * FROM customer_t;

    img 说明:
    与使用永久表相比，使用临时表可以提高性能，但存在丢失数据的风险。临时表只在当前会话可见，本会话结束后将自动删除。如果数据丢失是不可接受的，请使用永久表。

  2. 截断当前表customer_t。

    TRUNCATE customer_t;

  3. 使用INSERT INTO…SELECT语句从副本中向原始表中填充数据。

    INSERT INTO customer_t (SELECT * FROM customer_t_temp);

  4. 删除临时表副本customer_t_temp。

    DROP TABLE customer_t_temp;

分析表

执行计划生成器需要使用表的统计信息，以生成最有效的查询执行计划，提高查询性能。因此数据导入完成后，建议执行ANALYZE语句生成最新的表统计信息。统计结果存储在系统表PG_STATISTIC中。


分析表

ANALYZE支持的表类型有行/列存表。ANALYZE同时也支持对本地表的指定列进行信息统计。下面以表的ANALYZE为例，更多关于ANALYZE的信息，请参见ANALYZE | ANALYSE。

更新表统计信息。

以表product_info为例，ANALYZE命令如下:

ANALYZE product_info;

ANALYZE

表自动分析

MogDB提供了GUC参数autovacuum用于控制数据库自动清理功能的启动。

autovacuum设置为on时，系统定时启动autovacuum线程来进行表自动分析，如果表中数据量发生较大变化达到阈值时，会触发表自动分析，即autoanalyze。

  • 对于空表而言，当表中插入数据的行数大于50时，会触发表自动进行ANALYZE。
  • 对于表中已有数据的情况，阈值设定为50+10%*reltuples，其中reltuples是表的总行数。

autovacuum可以进行表自动分析功能的生效还依赖于下面几个GUC参数：

  • track_counts参数需要设置为on，表示开启收集收据库统计数据功能。

  • autovacuum_max_workers参数需要大于0，该参数表示能同时运行的自动清理线程的最大数量。

  • autovacuum_mode参需要配置允许进行analyze。

img 须知：

  • autoanalyze只支持默认采样方式，不支持百分比采样方式。
  • 多列统计信息仅支持百分比采样，因此autoanalyze不收集多列统计信息。
  • autoanalyze支持行存表和列存表，不支持外表、临时表、unlogged表和toast表。

对表执行VACUUM

如果导入过程中，进行了大量的更新或删除行时，应运行VACUUM FULL命令，然后运行ANALYZE命令。大量的更新和删除操作，会产生大量的磁盘页面碎片，从而逐渐降低查询的效率。VACUUM FULL可以将磁盘页面碎片恢复并交还操作系统。

对表执行VACUUM FULL。

以表product_info为例，VACUUM FULL命令如下:

VACUUM FULL product_info

VACUUM

管理并发写入操作


事务隔离说明

MogDB基于MVCC（多版本并发控制）并结合两阶段锁的方式进行事务管理，其特点是读写之间不阻塞。SELECT是纯读操作，UPDATE和DELETE是读写操作。

  • 读写操作和纯读操作之间并不会发生冲突，读写操作之间也不会发生冲突。每个并发事务在事务开始时创建事务快照，并发事务之间不能检测到对方的更改。

    • 读已提交隔离级别中，如果事务T1提交后，事务T2就可以看到事务T1更改的结果。

    • 可重复读级别中，如果事务T1提交事务前事务T2开始执行，则事务T1提交后，事务T2依旧看不到事务T1更改的结果，保证了一个事务开始后，查询的结果前后一致，不受其他事务的影响。

  • 读写操作，支持的是行级锁，不同的事务可以并发更新同一个表，只有更新同一行时才需等待，后发生的事务会等待先发生的事务提交后，再执行更新操作。

    • READ COMMITTED: 读已提交隔离级别，事务只能读到已提交的数据而不会读到未提交的数据，这是缺省值。
    • REPEATABLE READ: 事务只能读到事务开始之前已提交的数据，不能读到未提交的数据以及事务执行期间其它并发事务提交的修改。

写入和读写操作

关于写入和读写操作的命令:

  • INSERT，可向表中插入一行或多行数据。
  • UPDATE，可修改表中现有数据。
  • DELETE，可删除表中现有数据。
  • COPY，导入数据。

INSERT和COPY是纯写入的操作。并发写入操作，需要等待，对同一个表的操作，当事务T1的INSERT或COPY未解除锁定时，事务T2的INSERT或COPY需等待，事务T1解除锁定时，事务T2正常继续。

UPDATE和DELETE是读写操作（先查询出要操作的行）。UPDATE和DELETE执行前需要先查询数据，由于并发事务彼此不可见，所以UPDATE和DELETE操作是读取事务发生前提交的数据的快照。写入操作，是行级锁，当事务T1和事务T2并发更新同一行时，后发生的事务T2会等待，根据设置的等待时长，若超时事务T1未提交则事务T2执行失败；当事务T1和事务T2并发更新的行不同时，事务T1和事务2都会执行成功。


并发写入事务的潜在死锁情况

只要事务涉及多个表的或者同一个表相同行的更新时，同时运行的事务就可能在同时尝试写入时变为死锁状态。事务会在提交或回滚时一次性解除其所有锁定，而不会逐一放弃锁定。 例如，假设事务T1和T2在大致相同的时间开始:

  • 如果T1开始对表A进行写入且T2开始对表B进行写入，则两个事务均可继续而不会发生冲突；但是，如果T1完成了对表A的写入操作并需要开始对表B进行写入，此时操作的行数正好与T2一致，它将无法继续，因为T2仍保持对表B对应行的锁定，此时T2开始更新表A中与T1相同的行数，此时也将无法继续，产生死锁，在锁等待超时内，前面事务提交释放锁，后面的事务可以继续执行更新，等待时间超时后，事务抛错，有一个事务退出。
  • 如果T1，T2都对表A进行写入，此时T1更新1-5行的数据，T2更新6-10行的数据，两个事务不会发生冲突，但是，如果T1完成后开始对表A的6-10行数据进行更新，T2完成后开始更新1-5行的数据，此时两个事务无法继续，在锁等待超时内，前面事务提交释放锁，后面的事务可以继续执行更新，等待时间超时后，事务抛错，有一个事务退出。

并发写入示例

本章节以表test为例，分别介绍相同表的INSERT和DELETE并发，相同表的并发INSERT，相同表的并发UPDATE，以及数据导入和查询的并发的执行详情。

CREATE TABLE test(id int, name char(50), address varchar(255));

相同表的INSERT和DELETE并发

事务T1:

START TRANSACTION;
INSERT INTO test VALUES(1,'test1','test123');
COMMIT;

事务T2:

START TRANSACTION;
DELETE test WHERE NAME='test1';
COMMIT;

场景1:

开启事务T1，不提交的同时开启事务T2，事务T1执行INSERT完成后，执行事务T2的DELETE，此时显示DELETE 0，由于事务T1未提交，事务2看不到事务插入的数据；

场景2:

  • READ COMMITTED级别

    开启事务T1，不提交的同时开启事务T2，事务T1执行INSERT完成后，提交事务T1，事务T2再执行DELETE语句时，此时显示DELETE 1，事务T1提交完成后，事务T2可以看到此条数据，可以删除成功。

  • REPEATABLE READ级别

    开启事务T1，不提交的同时开启事务T2，事务T1执行INSERT完成后，提交事务T1，事务T2再执行DELETE语句时，此时显示DELETE 0，事务T1提交完成后，事务T2依旧看不到事务T1的数据，一个事务中前后查询到的数据是一致的。


相同表的并发INSERT

事务T1:

START TRANSACTION;
INSERT INTO test VALUES(2,'test2','test123');
COMMIT;

事务T2:

START TRANSACTION;
INSERT INTO test VALUES(3,'test3','test123');
COMMIT;

场景1:

开启事务T1，不提交的同时开启事务T2，事务T1执行INSERT完成后，执行事务T2的INSERT语句，可以执行成功，读已提交和可重复读隔离级别下，此时在事务T1中执行SELECT语句，看不到事务T2中插入的数据，事务T2中执行查询语句看不到事务T1中插入的数据。

场景2:

  • READ COMMITTED级别

    开启事务T1，不提交的同时开启事务T2，事务T1执行INSERT完成后直接提交，事务T2中执行INSERT语句后执行查询语句，可以看到事务T1中插入的数据。

  • REPEATABLE READ级别

    开启事务T1，不提交的同时开启事务T2，事务T1执行INSERT完成后直接提交，事务T2中执行INSERT语句后执行查询语句，看不到事务T1中插入的数据。


相同表的并发UPDATE

事务T1:

START TRANSACTION;
UPDATE test SET address='test1234' WHERE name='test1';
COMMIT;

事务T2:

START TRANSACTION;
UPDATE test SET address='test1234' WHERE name='test2';
COMMIT;

事务T3:

START TRANSACTION;
UPDATE test SET address='test1234' WHERE name='test1';
COMMIT;

场景1:

开启事务T1，不提交的同时开启事务T2，事务T1开始执行UPDATE，事务T2开始执行UPDATE，事务T1和事务T2都执行成功。更新不同行时，更新操作拿的是行级锁，不会发生冲突，两个事务都可以执行成功。

场景2:

开启事务T1，不提交的同时开启事务T3，事务T1开始执行UPDATE，事务T3开始执行UPDATE，事务T1执行成功，事务T3等待超时后会出错。更新相同行时，事务T1未提交时，未释放锁，导致事务T3执行不成功。


数据导入和查询的并发

事务T1:

START TRANSACTION;
COPY test FROM '...';
COMMIT;

事务T2:

START TRANSACTION;
SELECT * FROM test;
COMMIT;

场景1:

开启事务T1，不提交的同时开启事务T2，事务T1开始执行COPY，事务T2开始执行SELECT，事务T1和事务T2都执行成功。事务T2中查询看不到事务T1新COPY进来的数据。

场景2:

  • READ COMMITTED级别

    开启事务T1，不提交的同时开启事务T2，事务T1开始执行COPY，然后提交，事务T2查询，可以看到事务T1中COPY的数据。

  • REPEATABLE READ级别

    开启事务T1，不提交的同时开启事务T2，事务T1开始执行COPY，然后提交，事务T2 查询，看不到事务T1中COPY的数据。

mogdb大数据
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论