CODEWITHSUNDEEP
jsonamazon-s3amazon-redshift
realcodes
realcodes

Reputation: 269

Redshift Copy Command Error "Overflow, Column type: Integer"

I am using Copy command of Redshift database and storing json file from s3 bucket to databse. but I am getting this error "Overflow, Column type: Integer" and the error code is 1216 and line number in json file is 33.

Here is my json file:

             {
                "id": 119548805147,
                "title": "Shoes",
                "vendor": "xyz",
                "product_type": "",
                "handle": "shoes",
                "options": [
                    {
                        "id": 171716739099,
                        "product_id": 119548805147,
                        "name": "Size",
                        "position": 1,
                        "values": [
                            "9",
                            "10",
                            "11"
                        ]
                    },
                    {
                        "id": 171716771867,
                        "product_id": 119548805147,
                        "name": "Color",
                        "position": 2,
                        "values": [
                            "Red",
                            "white",
                            "Black"
                        ]
                    }
                ],
                "images": [],
                "image": null
            }   //line number 33
            {
                "id": 119548805147,
                "title": "Shoes",
                "vendor": "xyz",
                "product_type": "",
                "handle": "shoes",
                "options": [
                    {
                        "id": 171716739099,
                        "product_id": 119548805147,
                        "name": "Size",
                        "position": 1,
                        "values": [
                            "9",
                            "10",
                            "11"
                        ]
                    },
                    {
                        "id": 171716771867,
                        "product_id": 119548805147,
                        "name": "Color",
                        "position": 2,
                        "values": [
                            "Red",
                            "white",
                            "Black"
                        ]
                    }
                ],
                "images": [],
                "image": null
            }

my table in redshift is as below

CREATE TABLE products (
    "_id" int4 DEFAULT "identity"(297224, 0, '1,1'::text),
    "id" int4,
    title varchar(50),
    product_type varchar(200),
    vendor varchar(200),
    handle varchar(200),
    variants_id int4,
    "options" varchar(65535),
    images varchar(65535),
    image varchar(65535)
);

And my Copy command in Redshift is here:

copy products
from 's3://kloudio-data-files'
access_key_id 'my access key'
secret_access_key 'my secret key'
json 'auto'

I think there is a mismatch of column and json file data type but I am not getting it.

Upvotes: 1

Views: 2279

Answers (1)

Haleemur Ali
Haleemur Ali

Reputation: 28253

The error suggests that that the value you're trying to input is bigger than the type can hold, and I can see from your data sample that id takes the value 171716771867 which is greater than the max value an INTEGER can hold.

Integers are 4 bytes long in Redshift, so they can hold (2 ^ (8))^4 = 4294967296 distinct values, which gives us the range: [-2147483648, 2147483647], or one can read this off from the table in the official documentation

The solution is to use a different type for your data. Use a Big Integer, if you want the id as numeric or use a text field. note, I only scanned your sample input for 1 overflow error, it may be necessary to correct the type for other fields

Upvotes: 3

Related Questions