Apache Hive : HPL/SQL - 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.

Syntax:

Export data to a file:

COPY table_name | (select_stmt) TO [HDFS] file_name [options]

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]

options:
  BATCHSIZE num

Notes:

• 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

See also:

• COPY FROM LOCAL
• INSERT DIRECTORY