I’m trying to run a simple chain s3-pipeline-redshift, but I’ve got completely stucked with input data format. Here’s my file:
2,Columbus Crew Stadium,Columbus,OH
4,CommunityAmerica Ballpark,Kansas City,KS
6,New York Giants Stadium,East Rutherford,NJ
8,The Home Depot Center,Carson,CA
9,Dick’s Sporting Goods Park,Commerce City,CO
10,Pizza Hut Park,Frisco,TX
and here’s the table I’m using:
create table venue_new(
venueid smallint not null,
venuename varchar(100) not null,
venueseats integer not null default '1000');
When I use | as a delimiter, I’m getting error 1214 – Delimiter not found , when I use comma – same thing, when I converted file to utf-8, I’m getting “Invalid digit, Value ‘.’, Pos 0, Type: Short’.
I ran out of ideas. What a heck is wrong with that thing? Can somebody please give me the example of the input file or tell what I’m doing wrong? Thanks in advance.
P.S. I also found that sample files are available in bucket awssampledb, but I have no idea how to get them.
Based on the data in the file example. You need to remember that you have 5 fields in your table, and there is no 5th field in any of your data – yet it is a not null field. Your Copy command needs to reference the 4 columns you are providing at the start of the statement.
copy venue_new(venueid, venuename, venuecity, venuestate)
I found the above command (from AWS Docs COPY examples worked successfully for me, leaving me with the default 1000 in the ‘venueseats’ column.