COPY From Stdin

Hologres current version supports using copy command to upload data. Users can upload data from stdin to specific tables.

Introduction

COPY: upload client data from stdin to a table.

COPY FROM: copy data from stdin to a table (append data to the table).

Synopsis

Hologres current version supports the following copy command:

COPY table_name [ ( column_name [, ...] ) ]
    FROM STDIN
    [ [ WITH ] ( option [, ...] ) ]
   
   where option can be one of:

    FORMAT format_name
    DELIMITER 'delimiter_character'
    NULL 'null_string'
    HEADER [ boolean ]
    QUOTE 'quote_character'
    ESCAPE 'escape_character'
    FORCE_QUOTE { ( column_name [, ...] ) | * }
    FORCE_NOT_NULL ( column_name [, ...] )

Parameter

table_name:table name

STDIN:standard input

FORMAT:support text and csv. Default is text.

DELIMITER:delimiter between columns. Defalt is tab. For CSV,specify DELIMITER as ‘,’

Example

1. Upload data from stdin

-- 1.create table
CREATE  TABLE copy_test (
  id    int,
  age   int,
  name  text
) ;

--2. upload data(type enter to finishing data input)

COPY copy_test from stdin WITH DELIMITER AS ',' NULL AS '';
53444,24,wangming
55444,38,ligang
55444,38,luyong
\.

--3. query data
select * from  copy_test;

2. Upload data from stdin to CSV

-- 1. create table
create table partsupp ( ps_partkey          integer not null,
                        ps_suppkey     integer not null,
                        ps_availqty    integer not null,     
                        ps_supplycost  float  not null,
                        ps_comment     text not null );
                                                                                                       
--2. upload data
copy partsupp from stdin with delimiter '|' csv;  
1|2|3325|771.64|final theodolites 
1|25002|8076|993.49|ven ideas
\.

--3.query data
select * from  partsupp;

3. Update client file using CopyManager

package com.aliyun.hologram.test.jdbc;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

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

public class jdbcCopyFile {

	public static void main(String args[]) throws Exception {
		System.out.println(copyFromFile(getConnection(), "/Users/feng/Workspace/region.tbl", "region"));
	}

	public static Connection getConnection() throws Exception {
		Class.forName("org.postgresql.Driver");
		String url = "jdbc:postgresql://11.163.188.167:12692/postgres";
		Properties props = new Properties();
    //set db user
		props.setProperty("user", "AAA");
    //set db password
		props.setProperty("password", "BBB");
		return DriverManager.getConnection(url, props);
	}

	/*
	 * 
	 * @param connection
	 * @param filePath
	 * @param tableName
	 * @return
	 * @throws SQLException
	 * @throws IOException
	 */
	public static long copyFromFile(Connection connection, String filePath, String tableName)
			throws SQLException, IOException {
		long count = 0;
		FileInputStream fileInputStream = null;

		try {
			CopyManager copyManager = new CopyManager((BaseConnection) connection);
			fileInputStream = new FileInputStream(filePath);
			count = copyManager.copyIn("COPY " + tableName + " FROM STDIN delimiter '|' csv", fileInputStream);
		} finally {
			if (fileInputStream != null) {
				try {
					fileInputStream.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return count;
	}

}