Redshift Loading Data Points To Different Bucket

This post was going to be about my issues loading some simple csv data in a Redshift table but halfway through writing it I realised that, for whatever reason, when selecting the bucket in the COPY command, Redshift was pointing to the wrong one!

Can someone explain why this is the case? For context, the bucket I selected was

s3://soccer-project/Player

but Redshift defaulted to

s3://soccer-project/Player_Attributes

which is another file in my bucket

New to Redshift... can someone help me understand this

Thanks

Only the top (left most) part of the S3 object path is the bucket. In both of your cases this is "soccer-project".

Now I expect that what you are showing is only part of the object name - "Player" and "Player_Attributes" in your question. These are not the full names to the objects. The full object names are these parts plus a slash and more text. Redshift is set up to take partial object names so that it can expand the files copied to include all object names that match the partial. Correct me if I'm interpreting the question incorrectly.

To understand what is going on you need to understand that S3 is an object store and not a file system. This means that all files are stored "flat" under each bucket. Only 2 things identify the object - bucket name and object name. There is no real hierarch in the storage. However to make things a little more organized when us humans look S3 will organize the objects by looking at slashes in the object name and make things seem hierarchical. But in reality everything after the bucket-name and slash is the object name, including any slashes, "folder" names, or anything else you think has unique meaning. It is all the object name.

Now to your situation: You likely have object names in your bucket that start with "Player" or "Player_Attributes", with the next character in the name being a slash. This is all just the first part of the object name. I'd also guess that your COPY command has a FROM clause like "s3://soccer-project/Player*". (Providing your COPY command in the question would really help clear up what is going on.) The '' is a wildcard that matches all following characters in the object name which will match "Player_Attributes". If all of this is correct then you can fix this by changing the FROM clause to "s3://soccer-project/Player/" (slash added).

As I said above this is a best guess based on the partial info provided. Please update the question if this is incorrect.