0

I created a cluster and connected to the database via SQL Workbench, but how can I upload data via SQL to Amazon Redshift?

I guess I have to use Amazon S3 but I could not find a sample video or text that describes it well.

Improve this question
2
  • You mean put data in Redshift ? Yes you need to use S3 and execute a COPY command. Several documentations available. Commented Sep 8, 2017 at 0:13
  • @JonEkiz there is no other way ? Commented Sep 8, 2017 at 0:17

1 Answer 1

Reset to default
1

There are two ways to insert information into Amazon Redshift:

  • Via the COPY command
  • Via INSERT statements

It is not recommended to use INSERT statements because they are not efficient for large data volumes. They are okay for doing ETL-type processes such as copying data between tables, but as a general rule data should be loaded via COPY.

As per Using a COPY Command to Load Data, the COPY command can load data from:

  • Amazon S3 (recommended, highly parallel)
  • Amazon EMR (Hadoop)
  • Amazon DynamoDB
  • Via SSH from remote hosts

The load from Amazon S3 is performed in parallel across all nodes and is the most efficient way to load data.

The Amazon Redshift COPY command can read several file formats:

  • Delimited (eg CSV)
  • Fixed-Width
  • AVRO
  • JSON
  • And these formats can also be compressed (eg gzip)

Bottom line: Get your data into Amazon S3 in a compatible format, then use COPY to load it.

Also, try to understand DISTKEY and SORTKEY to get full performance benefits out of Redshift. Definitely read the manual -- it will save you more time than it takes to read!

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Well, I have a 5 GB sql file.As far as I understand, we first drop this file to S3 and then use the copy method to retrieve the data from S3.
What do you mean by an "sql file"? What is the format of the data in the file? And yes, you put the data in an S3 bucket, then run the COPY command within Redshift to import the data. If you're new to Redshift, try the Introduction to Amazon Redshift self-paced lab (it's free).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.