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 openGauss in either of the following ways:
- Write data into openGauss by typing. For details, see COPY.
- Import data from a file or database to openGauss 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 openGauss. It is used to import data to the openGauss database in batches.
Inheritance Relationship of CopyManager
The CopyManager class is in the org.postgresql.copy package and inherits the java.lang.Object class. The declaration of the class is as follows:
public class CopyManager
extends ObjectConstruction Method
public CopyManager(BaseConnection connection)
throws SQLException
Common Methods
Table 1 Common methods of CopyManager
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 INTOerror_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:
openGauss=# SELECT * FROM error_table_name;Table 1 lists the columns of the error_table_name table.
Table 1 Columns in the error information table
Name of the source data file where a data format error occurs
Number of the row where a data format error occurs in a source data file
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
Example 1: Importing and Exporting Data Through Local Files
When Java is used for secondary development based on openGauss, 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 openGauss 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.postgresql.copy.CopyManager;
import org.postgresql.core.BaseConnection;
public class Copy{
public static void main(String[] args)
{
String urls = new String("jdbc:postgresql://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.postgresql.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 openGauss Database
The following example shows how to use CopyManager to migrate data from MY to openGauss.
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.postgresql.copy.CopyManager;
import org.postgresql.core.BaseConnection;
public class Migration{
public static void main(String[] args) {
String url = new String("jdbc:postgresql://localhost:8000/postgres"); // URL of the database
String user = new String("username"); // openGauss database user name
String pass = new String("passwd"); // openGauss 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.postgresql.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", "xxxxxxxxx");
Statement stmt = conn.createStatement();
rs = stmt.executeQuery("select * from migration_table");
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
return rs;
}
}