HomeMogDBMogDB StackUqbar
v3.0

Documentation:v3.0

Supported Versions:

Running the COPY FROM STDIN Statement to Import Data


Data Import Using COPY FROM STDIN

Run the COPY FROM STDIN statement to import data to MogDB in either of the following ways:

  • Write data into the MogDB database by typing. For details, see COPY.
  • Import data from a file or database to MogDB through the CopyManager interface driven by JDBC. You can use any parameters in the COPY syntax.

Introduction to the CopyManager Class

CopyManager is an API class provided by the JDBC driver in MogDB. It is used to import data to the MogDB database in batches.


Inheritance Relationship of CopyManager

The CopyManager class is in the org.opengauss.copy package and inherits the java.lang.Object class. The declaration of the class is as follows:

public class CopyManager
extends Object

Construction Method

public CopyManager(BaseConnection connection)
throws SQLException

Common Methods

Table 1 Common methods of CopyManager

Return Value Method Description throws
CopyIn copyIn(String sql) - SQLException
long copyIn(String sql, InputStream from) Uses COPY FROM STDIN to quickly import data to tables in a database from InputStream. SQLException,IOException
long copyIn(String sql, InputStream from, int bufferSize) Uses COPY FROM STDIN to quickly import data to tables in a database from InputStream. SQLException,IOException
long copyIn(String sql, Reader from) Uses COPY FROM STDIN to quickly import data to tables in a database from Reader. SQLException,IOException
long copyIn(String sql, Reader from, int bufferSize) Uses COPY FROM STDIN to quickly import data to tables in a database from Reader. SQLException,IOException
CopyOut copyOut(String sql) - SQLException
long copyOut(String sql, OutputStream to) Sends the result set of COPY TO STDOUT from the database to the OutputStream class. SQLException,IOException
long copyOut(String sql, Writer to) Sends the result set of COPY TO STDOUT from the database to the Writer class. SQLException,IOException

Handling Import Errors

Scenarios

Handle errors that occurred during data import.

Querying Error Information

Errors that occur when data is imported are divided into data format errors and non-data format errors.

  • Data format errors

    When creating a foreign table, specify LOG INTO error_table_name. Data format errors during data import will be written into the specified table. You can run the following SQL statement to query error details:

    mogdb=# SELECT * FROM error_table_name;

    Table 1 lists the columns of the error_table_name table.

    Table 1 Columns in the error information table

    Column Name Type Description
    nodeid integer ID of the node where an error is reported
    begintime timestamp with time zone Time when a data format error was reported
    filename character varying Name of the source data file where a data format error occurs
    rownum numeric Number of the row where a data format error occurs in a source data file
    rawrecord text Raw record of a data format error in the source data file
    detail text Error details
  • Non-data format errors

    A non-data format error leads to the failure of an entire data import task. You can locate and troubleshoot a non-data format error based on the error message displayed during data import.

Handling Data Import Errors

Troubleshoot data import errors based on obtained error information and descriptions in the following table.

Table 2 Handling data import errors

Error Message Cause Solution
missing data for column "r_reason_desc" 1. The number of columns in the source data file is less than that in the foreign table.
2. In a TEXT-format source data file, an escape character (for example, ) leads to delimiter or quote mislocation.
Example: The target table contains three columns, and the following data is imported. The escape character () converts the delimiter (|) into the value of the second column, causing the value of the third column to lose.
`BE
Belgium
extra data after last expected column The number of columns in the source data file is greater than that in the foreign table. - Delete extra columns from the source data file.
- When creating a foreign table, set the parameter ignore_extra_data to on. In this way, if the number of columns in the source data file is greater than that in the foreign table, the extra columns at the end of rows will not be imported.
invalid input syntax for type numeric: "a" The data type is incorrect. In the source data file, change the data type of the columns to import. If this error information is displayed, change the data type to numeric.
null value in column "staff_id" violates not-null constraint The not-null constraint is violated. In the source data file, add values to the specified columns. If this error information is displayed, add values to the staff_id column.
duplicate key value violates unique constraint "reg_id_pk" The unique constraint is violated. - Delete duplicate rows from the source data file.
- Run the SELECT statement with the DISTINCT keyword to ensure that all imported rows are unique.
mogdb=# INSERT INTO reasons SELECT DISTINCT * FROM foreign_tpcds_reasons;
value too long for type character varying(16) The column length exceeds the upper limit. In the source data file, change the column length. If this error information is displayed, reduce the column length to no greater than 16 bytes (VARCHAR2).

Example 1: Importing and Exporting Data Through Local Files

When the JAVA language is used for secondary development based on MogDB, you can use the CopyManager interface to export data from the database to a local file or import a local file to the database by streaming. The file can be in CSV or TEXT format.

The sample program is as follows. Load the MogDB JDBC driver before executing it.

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 of the database
      String username = new String("username");            // Username
      String password = new String("passwd");             // Password
      String tablename = new String("migration_table"); // Table information
      String tablename1 = new String("migration_table_1"); // Table information
      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);
      }

      // Export data from the migration_table table to the d:/data.txt file.
      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();
      }
      // Import data from the d:/data.txt file to the migration_table_1 table.
      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();
      }

      // Export data from the migration_table_1 table to the d:/data1.txt file.
      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 with (" + "DELIMITER"+"'"+ delimiter +  "'" + "ENCODING " + "'" + encoding + "')", 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();
              }
          }
      }
  }
}

Example 2: Migrating Data from a MySQL Database to the MogDB Database

The following example shows how to use CopyManager to migrate data from MySQL to the MogDB database.

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 of the database
        String user = new String("username");            // MogDB database user name
        String pass = new String("passwd");             // MogDB database password
        String tablename = new String("migration_table_1"); // Table information
        String delimiter = new String("|");              // Delimiter
        String encoding = new String("UTF8");            // Character set
        String driver = "org.opengauss.Driver";
        StringBuffer buffer = new StringBuffer();       // Buffer to store formatted data

        try {
            // Obtain the query result set of the source database.
            ResultSet rs = getDataSet();

            // Traverse the result set and obtain records row by row.
            // The values of columns in each record are separated by the specified delimiter and end with a linefeed, forming strings.
            // Add the strings to the 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 {
                // Connect to the target database.
                Class.forName(driver);
                Connection conn = DriverManager.getConnection(url, user, pass);
                BaseConnection baseConn = (BaseConnection) conn;
                baseConn.setAutoCommit(false);

                // Initialize the table.
                String sql = "Copy " + tablename + " from STDIN with (DELIMITER " + "'" + delimiter + "'" +","+ " ENCODING " + "'" + encoding + "'");

                // Commit data in the 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();
        }
    }

    //********************************
    // Return the query result set from the source database.
    //*********************************
    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;
    }
}
Copyright © 2011-2024 www.enmotech.com All rights reserved.