User Tools

Site Tools


**HPL/SQL is included to Apache Hive since version 2.0** * [[home|Home]]\\ * [[why|Why HPL/SQL]]\\ * [[features|Key Features]]\\ * [[start|Get Started]]\\ * [[doc|HPL/SQL Reference]]\\ * [[download|Download]]\\ * [[new|What's New]]\\ * [[about|About]]


COPY Statement

COPY statement allows to transfer data between tables and files. Use it to transfer relatively small volumes of data i.e. query results, look-up and dimension tables.

When you copy data between tables they can be located in different databases.


Export data to a file:

COPY table_name | (select_stmt) TO [HDFS] file_name [options]
  DELIMITER 'char'
| SQLINSERT target_table_name

Copy data between existing tables:

COPY table_name | (select_stmt) TO target_table_name AT target_conn_name [options]


  • table_name is an identifier.
  • file_name can be an expression, quoted or unquoted string.
  • If file_name already exists it is overwritten.
  • When HDFS is specified, the data is copied to a HDFS-compatible file system.
  • DELIMITER specifies the delimiter between column values, '\t' (TAB character) is the default.
  • SQLINSERT specifies to generate SQL INSERT statements to insert data to target_table_name. The statements are just stored in the file, and not executed.
  • When you copy data into another table, the table must exist.
  • BATCHSIZE specifies the number of rows to transfer in a single batch when copying rows between tables, the default is 1000.

Example 1:

Export the query results to a file:

COPY (SELECT id, name FROM sales.users WHERE local_dt = CURRENT_DATE) 
  TO /data/users.txt DELIMITER '\t';

Example 2:

Generate SQL INSERT statements:

COPY sales.users TO /data/users2.sql SQLINSERT sales.users;

Example 3:

Copy rows from between tables in different databases:

COPY sales.users TO sales.users2 AT tdconn;

Compatibility: HPL/SQL Extension, PostgreSQL

Version: HPL/SQL 0.3.7

See also: