CODEWITHSUNDEEP
pythonsqldatabasecsvamazon-redshift
Loyal_Burrito
Loyal_Burrito

Reputation: 125

Export CSV file from redshift to local with appropriate variable types

I am trying to directly export a table from redshift to my local computer. I am successful in getting data from redshift however it doesn't differentiate any of the data. When I do pandas.dtypes they all come out as objects and not various data types such as string or date timestamps. I would also like to add the headers of the columns straight from the export.

I've successfully exported to my local using PSQL commands from my terminal to access the redshift.

psql -h omaha-prod-cluster.example.us-east-1.redshift.amazonaws.com -d prod -U <username> -p 5439 -A -t -c "select * from l2_survey.survey_customerinsight" -F ',' -o Downloads/survey_customerInsights.csv

I am then running the panda command to read the kinds of data types

data.dtypes()

and it is returning every column with the data type of object. It also doesn't give me the headers of the columns with the psql command above

Upvotes: 1

Views: 658

Answers (1)

Red Boy
Red Boy

Reputation: 5739

There is problem with your command where you are explicitly asking the export command to skip the column names by supplying the argument -t, which tells the command to just export tuples without column names. Just change it like below and it will provide you the header.

psql -h <host-values>.redshift.amazonaws.com -U <user> -d <database> -p 5439 -c "select * from your_schema.your_table" > out.txt

Hope it helps you.

Upvotes: 1

Related Questions