← Back to Blog

How to Run the COPY Command for Amazon Redshift

Amazon Redshift is a powerful, fully managed, petabyte-scale data warehouse solution in the cloud. It is part of Amazon Web Services (AWS) and allows fast analysis of large datasets using SQL-based tools. One of the key features of Redshift is the ability to load vast amounts of data simultaneously from multiple data streams. This is made possible by the COPY command.
By Saturn Cloud | | Miscellaneous

How to Run the COPY Command for Amazon Redshift

Amazon Redshift is a powerful, fully managed, petabyte-scale data warehouse solution in the cloud. It is part of Amazon Web Services (AWS) and allows fast analysis of large datasets using SQL-based tools. One of the key features of Redshift is the ability to load vast amounts of data simultaneously from multiple data streams. This is made possible by the COPY command.

What is the COPY Command?

The COPY command in Amazon Redshift is a potent tool used for parallel data loading. It reads from multiple data files or Amazon DynamoDB tables and loads the data into a single Redshift table. This command is especially useful when you need to import large amounts of data into your Redshift database efficiently.

Where to Run the COPY Command?

The COPY command is run within the SQL environment of your Amazon Redshift instance. This means it can be executed in any SQL client that supports JDBC or ODBC connections to Amazon Redshift.

To run the COPY command, you would need to be connected to your Amazon Redshift cluster. Here is a step-by-step guide:

  1. Connect to Your Redshift Cluster: Use your preferred SQL client to establish a connection to your Redshift cluster. Ensure the client supports JDBC or ODBC connections.

  2. Access the SQL Terminal: Once you are connected, navigate to the SQL terminal or query editor in your client. This is where you will input your SQL commands.

  3. Run the COPY Command: Now you are ready to run the COPY command. The command’s syntax is as follows:

COPY table_name
FROM 's3://bucket_name/key_name'
IAM_ROLE 'arn:aws:iam::account_id:role/role_name'
FORMAT AS format_type;

Replace the placeholders with your actual data. The table_name is the destination Redshift table, the S3 path ('s3://bucket_name/key_name') is the source data location, and the IAM_ROLE is the role that has sufficient permissions to read from the S3 bucket and write to the Redshift table. format_type is the data format of your source file (e.g., CSV, JSON, AVRO, etc.)

After running the command, Redshift will begin loading the data into your specified table in parallel, making the process significantly faster than traditional single-stream insert commands.

Best Practices

While running the COPY command, remember these best practices for optimal performance and efficiency:

  • Use Compression: Compress your data files before loading them into Redshift. This will reduce the amount of data that needs to be transferred, thereby speeding up the loading process.

  • Split Large Files: The COPY command works best when it can load data in parallel from multiple files. If your data source is a single, large file, consider splitting it into smaller chunks.

  • Manage Permissions: Ensure that your IAM role has the necessary permissions to access both your Redshift cluster and the S3 bucket where your data resides.

  • Monitor the Process: Keep an eye on the stl_load_errors system table in Redshift. This table logs any errors that occur during the COPY process.

Amazon Redshift’s COPY command is a powerful tool for data scientists and software engineers working with large datasets. Understanding where and how to use this command can greatly improve your data loading and analysis workflows.

About Saturn Cloud

Saturn Cloud is your all-in-one solution for data science & ML development, deployment, and data pipelines in the cloud. Spin up a notebook with 4TB of RAM, add a GPU, connect to a distributed cluster of workers, and more. Join today and get 150 hours of free compute per month.

Try Saturn Cloud Now