soletechie

Scenario 1: To copy data from S3 to Aurora Postgres (greater than v9 or latest) How ?: We can use aws_s3.table_import_from_s3 function (to migrate the data from S3 to Aurora Postgres).  Steps: A sample file with columns - id, prefix, mstr_id is copied to S3.  Create schema on Aurora Postgres (with the required columns).  drop table core . mstr ; CREATE TABLE core .mstr ( id varchar ( 300 ) NULL , prefix varchar ( 300 ) NULL , mstr_id float8 NULL ); Copy command to transfer the data from S3 to Aurora Postgres SELECT aws_s3 . table_import_from_s3 ( ' core.MSTR ' , ' id,prefix,mstr_id ' , ' (format csv, header true) ' , '<bucket-name> ' , ' MSTR_DATA/part_file_00.csv ' , ' us-east-2 ' , '<secret key> ' , '<access key> ' ); Note: If IAM roles are given, we need not specify access keys.  SELECT aws_s3 . table_import_from_s3 ( ' core.MSTR ' , ' id,prefix,mst...

We use s3 COPY command to move data from S3 to Redshift table. Make sure the schema for Redshift table is created before running your COPY command. I'm writing this post to log all errors related to the COPY command that I faced which might help others in saving their time. Copy command that I have used for a while: copy <redshift table name> from '<s3 file location>' iam_role '<iam role arn>' NULL AS 'NULL' EMPTYASNULL delimiter ',' IGNOREHEADER 1;; Problem 1: Faced issue with delimiters on CSV file . As we know the delimiter for any CSV file will be comma separators, but the problem on keeping this as a delimiter is that it will fail with below error if the data also have commas in it. Error Code:  1213, Error Description: Missing data for not-null field AWS Documentation link that helped in solving this issue: https://docs.aws.amazon.com/redshift/latest/dg/r_COPY_command_examples.html#load-from-csv Solution 1.1...