4 methods for exporting CSV files from Redshift | Census
Michel ZurkirchenSeptember 16, 2021
Michel Zurkirchen is an Amsterdam-based digital analyst and a regular contributor to the Census blog. After starting his career in digital marketing, Michel started learning Python and has been an enthusiastic learner and practitioner of data analytics ever since. Amsterdam, North Holland, Netherlands
What you'll learn in this article: How to export a CSV From Redshift using four helpful methods for data analytics:
Think you know your way around Amazon Redshift? Chances are, since it’s so loaded with features, you’re probably just discovering the tip of the iceberg.
And even if you’re a superuser, there’s probably an easier way for you to do the day-to-day tasks you’ve come to master.
Case in point: Exporting CSV files. Did you know Redshift allows you to export (part of) your Redshift data to a CSV file? And if so, are you sure you know the best way to get it done?
Of course, the “best” heavily depends on your context and use case, whether that's pulling data for a business intelligence dashboard or helping to inform better segmentation in your next marketing campaign. Regardless, we'll show you four different ways and let you pick what works best for you.
Option 1: UNLOAD command
You can quickly export your data from Redshift to CSV with some relatively simple SQL. If you log into the Redshift console, you'll see the editor button in the menu on the left. Hover over it and proceed to the query editor, where you can connect to a database. Once connected, you can start running SQL queries. The basic syntax to export your data is as below.
UNLOAD ('SELECT * FROM your_table')TO's3://object-path/name-prefix'
IAM_ROLE 'arn:aws:iam::<aws-account-id>:role/<role-name>'
CSV;
On the first line, you query the data you want to export. Be aware that Redshift only allows a LIMIT clause in an inner SELECT statement.
The second line contains the TO clause, where you define the target S3 bucket path. You will need to have the write permission to be able to execute the query.
The third line is your authorization and is one of several ways in which you can authorize. If you decide to use the method above, you can find your 12 digit account ID in the support center, by clicking on your account name in the navigation bar.
Finally, the fourth line tells Redshift that you want your data to be saved as CSV, which is not the default.
There are numerous other options that you can add to the above query to customize it to fit your needs. A few ones that can be especially useful are:
HEADER. This adds a row with column names at the top of your output file(s). You'll want to do this in pretty much every scenario.
DELIMITER AS 'character'. The default character for CSV files is a comma. If your data contains commas, it may lead to unexpected results. In such a case you might use, for instance, a pipe ( | ) instead.
ADDQUOTES. This will add quotes around every field in your data, which is another way of making sure that commas in your data don't lead to unexpected results.
BZIP2, GZIP, or ZSTD. Adding one of these compression options will significantly reduce your file size and hence make it quicker to download or send elsewhere.
You can find all options in the AWS Database Developer guide. Once you've saved your data to your S3 bucket, the easiest way to download it to your local machine is to navigate to your bucket and file in the AWS console, from where you can download it directly.
Option 2: AWS SDK
You can programmatically interact with AWS using one of their SDKs, which are available in many different programming languages, including JavaScript, Python, Node.js, and Ruby. Through an SDK, you can run SQL queries to store your data in a variable within your code and then save the data stored in the variable as a CSV file.
In this example, we'll use Python. Even if you're going to use another language, the example should be clear enough for you to get an idea of how you can approach this. The AWS SDK for Python is called boto3, which you'll have to install.
pip install boto3
Once installed, import the library and declare a client. For region_name, you'll generally want to use the region in which your resources are already located, which you'll find at the beginning of the URL when logged into the Redshift console. For example: https://us-east-2.console.aws.amazon.com/redshiftv2/home
You're now ready to execute a query, which will gather your data.
response = client.execute_statement(
ClusterIdentifier='your-cluster',
Database='your-database',
DbUser='your-user',
Sql='SELECT * FROM users;'# Insert your SQL query here)
The response is a dictionary, holding information about the request you just made. One of the keys is Id, which is the universally unique ID of your query.
Insert this query ID into describe_statement to see the current status of your query. There is no use in trying to access your data if the key Status doesn't yet equal FINISHED.
When the status indicates that your query is done, retrieve the data with get_statement_result and save it to a variable.
data = client.get_statement_result(Id=query_id)
Always check the NextToken key as well, to make sure you're not missing any of your data. If there is a NextToken, your data has been paginated and there’s more to retrieve.
Depending on how much data you queried, you might want to build a for loop to go through your data and append it all to one variable. Assuming you now have all your data, you'll find that it comes in a very inconvenient format. Inconvenient here meaning that you can't simply turn it into a pandas DataFrame straight away - at least, not into one that makes sense. We wrote a function to help you with that.
defredshift_to_dataframe(data):
df_labels =[]for i in data['ColumnMetadata']:
df_labels.append(i['label'])
df_data =[]for i in data['Records']:
object_data =[]for j in i:
object_data.append(list(j.values())[0])
df_data.append(object_data)
df = pd.DataFrame(columns=df_labels, data=df_data)return df
That takes care of the heavy lifting for you. All you need to do now is call the function to create a DataFrame and save that to CSV.
And with that, you have a nicely formatting CSV that you can use for your use case!
Option 3: AWS command-line interface (CLI)
Another good option is to use the AWS CLI. You'll have to install it first. Since the installation process is different for every OS, we'll leave you with Amazon's own instructions. Once installed, you are ready to query and export your data. The basic syntax is as follows.
With the execute-statement command, you can run a SQL query from the CLI. You're free to build whichever SQL query you need to get the right data. There are a few more options that you can add to the execute-statement command, to further tweak it to your liking. After executing this command, you'll receive an Id for your SQL statement as part of the response. You can plug this in the below command to download your data.
Lastly, you can use a SQL client on your local machine to connect to Redshift. Setting up your tool and connection might take some time, so you'll only want to go this way if you intend on querying Redshift more often. There's an abundance of tools out there, such as MySQL Workbench, which are up to the task. The exact buttons to click and commands to enter will vary from tool to tool, but in each case you'll follow the same process.
Connect to a database.
Query a table.
Export the result as CSV.
However, if exporting a CSV file from Redshift is more of a one-off task for you, there are faster and easier options (as we walked through above).
One more thing to consider when exporting CSV files from Redshift
Congrats! You now know four different ways of exporting your data to a CSV file from Amazon Redshift. Hopefully, you've had success with at least one of the methods we explained.
However, if the idea of doing any of these options over and over again makes you want to swear each time this request comes in, we have one final recommendation: Sign up for a demo of Census.
Instead of trying to export your data to CSV until you’re at your wits' end, you can use Census to easily move your data from A (Redshift) to B (literally anywhere else you could want it), all without the headache of Googling and reading through these kinds of tutorials every time.
