Running PROC COPY In-Database for Vertica

Details about Running PROC COPY for Vertica

Copying data in-database results in faster copy times because the data does not have to be loaded into or moved out of SAS. You can run the COPY procedure in-database to copy Vertica data from one schema to another. In this case, the librefs for the IN= and OUT= options for PROC COPY must match for the SERVER=, PORT=, and DATABASE= values. To copy data in-database, the user who is specified for the IN= location must have Read permission for that location. Also, the user who is specified for the OUT= location must have Read permission for the IN= location and Write permission for the OUT= location.

Here are some points to keep in mind when you copy data in-database:

• You can specify the ACCEL | NOACCEL option in the PROC COPY statement when you copy data. When the IN= and OUT= locations are database locations, the ACCEL option specifies that data is copied in-database. The ACCEL option is enabled by default.
• You can also specify the REPLACE system option with PROC COPY. The REPLACE (or NOREPLACE) system option specifies whether to replace a data set if it already exists. The REPLACE system option is enabled by default.
  Note: When the REPLACE system option is enabled, an existing target table is deleted before PROC COPY attempts to copy data.
• In-database copy is not supported if the input library is a concatenated library. You can copy to a concatenated library if the first target library matches the input library.
• The OVERRIDE= and CLONE | NOCLONE options in the COPY statement are ignored when copying data in-database.
• You can specify PRESERVE_NAMES=YES in the LIBNAME statement to preserve the case of table or column names. This also enables you to copy names with special characters. PRESERVE_NAMES= is an alias for both the PRESERVE_TAB_NAMES= and PRESERVE_COL_NAMES= LIBNAME options.
• Data types that are not supported in the SAS Viya platform, such as BINARY or DECIMAL, are retained when you copy data in-database. If the data is moved into and out of a SAS Viya offering (without copying data in-database), then data types or attributes are converted to types and attributes that are supported in the SAS Viya platform.
• Indexes and integrity constraints are copied with a table when it is copied in-database. These are not copied if the table is moved into and out of a SAS Viya offering (without copying data in-database) during the copy operation.

To verify whether the COPY procedure ran in-database, specify MSGLEVEL=i in the OPTIONS statement.

See Also

• COPY Procedure in Base SAS Procedures Guide
• DATASETS Procedure in Base SAS Procedures Guide
• MSGLEVEL= System Option in SAS System Options: Reference
• REPLACE System Option in SAS System Options: Reference

PROC COPY Example

This example shows how to copy data in Vertica between schemas in a database. This example copies the my_table table from inschema schemainto the outsch schema. By specifying the REPLACE system option, you ensure that, if the destination table already exists, it is replaced by the COPY procedure. The ACCEL option for the PROC COPY statement is enabled by default. This option controls whether data is copied in-database.

libname vtin vertica user="myusr1 " database=mydb1 port=5433 
   server="mysrv1" password=mypwd1 schema=inschema;
libname vtout vertica user="myusr1" database=mydb1 port=5433 
   server="mysrv1" password=mypwd1 schema=outsch;

options replace;

proc copy in=vtin out=vtout;
   select my_table;
run;

proc contents data=vtout.my_table;run;