CODEWITHSUNDEEP
amazon-redshift
devvgautam
devvgautam

Reputation: 1

RedShift copy command return

can we get the number of row inserted through copy command? Some records might fail, then what is the no of records successfully inserted?

I have a file with json object in Amazon S3 and trying to load data into Redshift using copy command. How do I know how many of records successfully got inserted and how many failed?

Upvotes: 0

Views: 1196

Answers (1)

moertel
moertel

Reputation: 1580

Loading some example data:

db=# copy test from 's3://bucket/data' credentials '' maxerror 5;
INFO:  Load into table 'test' completed, 4 record(s) loaded successfully.
COPY

db=# copy test from 's3://bucket/err_data' credentials '' maxerror 5;
INFO:  Load into table 'test' completed, 1 record(s) loaded successfully.
INFO:  Load into table 'test' completed, 2 record(s) could not be loaded.  Check 'stl_load_errors' system table for details.
COPY

Then the following query:

with _successful_loads as (
    select
        stl_load_commits.query
      , listagg(trim(filename), ', ') within group(order by trim(filename)) as filenames
    from stl_load_commits
    left join stl_query using(query)
    left join stl_utilitytext using(xid)
    where rtrim("text") = 'COMMIT'
    group by query
),
_unsuccessful_loads as (
    select
        query
      , count(1) as errors
    from stl_load_errors
    group by query
)
select
    query
  , filenames
  , sum(stl_insert.rows)            as rows_loaded
  , max(_unsuccessful_loads.errors) as rows_not_loaded
from stl_insert
inner join _successful_loads using(query)
left join _unsuccessful_loads using(query)
group by query, filenames
order by query, filenames
;

Giving:

 query |                   filenames                    | rows_loaded | rows_not_loaded
-------+------------------------------------------------+-------------+-----------------
 45597 | s3://bucket/err_data.json                      |           1 |               2
 45611 | s3://bucket/data1.json, s3://bucket/data2.json |           4 |
(2 rows)

Upvotes: 1

Related Questions