String Exceeds DDL Length - Unloading the data from S3 to Redshift

Question

Hi AWS Support, I am unloading a .csv file from S3 bucket to Redshift but while running the SQL query using Workbench. Here is the code:

```
DROP TABLE IF EXISTS demo_schema.sample_backbook_data;

CREATE TABLE demo_schema.sample_backbook_data (
    ReferenceID varchar(100),
    LenderName varchar(100),
    UsageType varchar(100),
    NoticeFlag varchar(100),
    ExecutionDate varchar(20),
    IsOpenEnded varchar(100),
    EndDate varchar(20),
    CreditCategory varchar(100),
    AccountType varchar(100),
    FeeCategory varchar(100),
    LoanType varchar(100),
    StatusFlag varchar(100),
    EntityID varchar(100),
    EntityName varchar(100),
    AgreementType varchar(100),
    ManualFlag varchar(100),
    ServiceType char(100),
    AgreementOption varchar(100),
    FutureValueGuarantee varchar(100),
    AmendedFlag varchar(200),
    ProductBrand varchar(100),
    IsP2P varchar(200),
    CustomerType varchar(100),
    RefID varchar(120),
    CreditRecipientName varchar(1000), 
    NumberOfEntities integer,
    CustomerID varchar(120),
    CustomerDOB date,
    CollateralType varchar(100),
    GuarantorFlag varchar(100),
    GuarantorID varchar(100),
    GuarantorDOB varchar(20),
    TotalAmount numeric(18,2),
    TotalCharges numeric(18,2),
    APR numeric(18,2)
);

CALL admin.sp_load_to_redshift(
    'demo_schema.sample_backbook_data',
    'DEMO',
    '',
    TRUE
);

```

When I ran this command I got **String Exceeds DDL Length**.  I converted the first column type to VARCHAR(256) which is synonymous of TEXT which can be used nicely with Redshift considering it uses Postgres Engine under the hood but then I got a different error: **String contains invalid or unsupported UTF8 codepoints. Bad UTF8 hex sequence: fc (error 1)**.

Please acknowledge and respond ASAP as it is extremely urgent.

re:Post Agent · Answer

I understand you're encountering issues while unloading data from S3 to Redshift. Let's address both errors you've encountered:

1. "String Exceeds DDL Length": This error occurs when the data being loaded is longer than the column length defined in your table. In your case, changing the column to VARCHAR(256) didn't solve the issue, which suggests some of your data might be longer than 256 characters.

2. "String contains invalid or unsupported UTF8 codepoints": This error indicates that your data contains characters that are not valid UTF-8 encoded characters.

To resolve these issues, you can try the following:

1. Increase the VARCHAR length for columns that might contain longer data. For example, you could use VARCHAR(1000) or even VARCHAR(MAX) for columns that might contain very long strings.

2. Use the TRUNCATECOLUMNS option in your COPY command if you're okay with truncating data that exceeds the defined column length.

3. To handle the UTF-8 issue, you can use the ACCEPTINVCHARS option in your COPY command. This will replace invalid UTF-8 characters with a specified character.

Here's a modified version of your CREATE TABLE statement that increases some VARCHAR lengths:

```sql
CREATE TABLE demo_schema.sample_backbook_data (
    ReferenceID varchar(256),
    LenderName varchar(256),
    UsageType varchar(256),
    NoticeFlag varchar(256),
    ExecutionDate varchar(20),
    IsOpenEnded varchar(256),
    EndDate varchar(20),
    CreditCategory varchar(256),
    AccountType varchar(256),
    FeeCategory varchar(256),
    LoanType varchar(256),
    StatusFlag varchar(256),
    EntityID varchar(256),
    EntityName varchar(256),
    AgreementType varchar(256),
    ManualFlag varchar(256),
    ServiceType varchar(256),
    AgreementOption varchar(256),
    FutureValueGuarantee varchar(256),
    AmendedFlag varchar(256),
    ProductBrand varchar(256),
    IsP2P varchar(256),
    CustomerType varchar(256),
    RefID varchar(256),
    CreditRecipientName varchar(1000), 
    NumberOfEntities integer,
    CustomerID varchar(256),
    CustomerDOB date,
    CollateralType varchar(256),
    GuarantorFlag varchar(256),
    GuarantorID varchar(256),
    GuarantorDOB varchar(20),
    TotalAmount numeric(18,2),
    TotalCharges numeric(18,2),
    APR numeric(18,2)
);
```

Then, when using the COPY command to load data, include the TRUNCATECOLUMNS and ACCEPTINVCHARS options:

```sql
COPY demo_schema.sample_backbook_data
FROM 's3://your-bucket/your-file.csv'
CREDENTIALS 'aws_iam_role=your-iam-role'
CSV
TRUNCATECOLUMNS
ACCEPTINVCHARS AS '?';
```

These modifications should help resolve both the string length and UTF-8 encoding issues you're experiencing. If you're using a stored procedure to load the data, you may need to modify it to include these options.  
**Sources**  
[Troubleshoot S3 ingestion into Redshift | AWS re:Post](https://repost.aws/knowledge-center/redshift-troubleshoot-s3-ingestion)  
[Tutorial: Using spatial SQL functions with Amazon Redshift - Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/dg/spatial-tutorial.html)

String Exceeds DDL Length - Unloading the data from S3 to Redshift

Add your answer

Relevant content