How to use the MapReduce job to pull data from the Pivotal HDB table with corrupted data file on segment

Products

Services Suite

Environment

Cause

Sometimes the data file of the HDB table may be corrupted on some segments which can cause a query against this table to fail with errors as shown below.

gpadmin=# select count(*) from employees;
ERROR: read beyond eof in table "employees" in file "hdfs://gphd21/hawq_data/gpseg0/16385/16554/476868.1" (cdbbufferedread.c:201) (seg0 slice1 hdw1.hadoop.local:40000 pid=403758) (cdbdisp.c:1571)

The reason is that the data file (476868.1 in the example above) is corrupted and its size does not match the metadata in the system catalog table.

[gpadmin@hdm1 ~]$ hdfs dfs -ls /hawq_data/gpseg0/16385/16554/476868.1
Found 1 items
-rw-r--r-- 2 gpadmin gpadmin 40 2016-04-13 23:15 /hawq_data/gpseg0/16385/16554/476868.1

gpadmin=# select relfilenode from pg_class where relname='employees';
 relfilenode
-------------
 476868
(1 row)
gpadmin=# select eof from pg_aoseg.pg_aoseg_476868 where content=0;
 eof
-------
 43056
(1 row)

As shown in the example above, file 476868.1 has a size of only 40 on HDFS, but it should be 43056 according to the metadata in the system table.

Resolution

Sometimes, it is still required to extract table data from other segments which still contain valid data files of the concerned table.

As described in the HDB documentation (section "HAWQ InputFormat for MapReduce") as well as this KB article, it's possible to solve the issue by writing a MapReduce program to dump table data from data files directly.

Scenario 1: the size of the corrupted data file is non-zero

First, try to dump data directly with the written MapReduce program as mentioned above. Before doing, that make sure the program can work correctly by testing against a good table.

If the program fails with exception, it might mean that the corrupted data file is not able to be read data from. Please try the method in Scenario 2.

Scenario 2: the size of corrupted data file is zero

An exception will be shown if a data file with zero bytes is to be read. So a temporary data file with some fake data needs to be prepared first to run the MapReduce program.

1. Create a temporary table with the same schema as the corrupted one.

gpadmin=# \d employees
 Append-Only Table "public.employees"
 Column | Type | Modifiers
--------+-----------------------+-----------
 id | integer | not null
 name | character varying(32) | not null
Compression Type: None
Compression Level: 0
Block Size: 32768
Checksum: f
Distributed by: (id)
gpadmin=# \d employees1
 Append-Only Table "public.employees1"
 Column | Type | Modifiers
--------+-----------------------+-----------
 id | integer | not null
 name | character varying(32) | not null
Compression Type: None
Compression Level: 0
Block Size: 32768
Checksum: f
Distributed by: (id)

2. Insert one record into the temporary table.

gpadmin=# select * from employees1;
 id | name
--------+----------
 888888 | test8888
(1 row)

3. Find out the segment which contains the data of the temporary table.

gpadmin=# select relfilenode from pg_class where relname='employees1';
 relfilenode
-------------
 476873
(1 row)
gpadmin=# select gp_segment_id, count(*) from employees1 group by 1;
 gp_segment_id | count
---------------+-------
 2 | 1
[gpadmin@hdm1 ~]$ hdfs dfs -ls /hawq_data/gpseg2/16385/16554/476873*
Found 1 items
-rw------- 3 postgres gpadmin 0 2016-04-13 23:13 /hawq_data/gpseg2/16385/16554/476873
Found 1 items
-rw------- 3 postgres gpadmin 40 2016-04-13 23:14 /hawq_data/gpseg2/16385/16554/476873.1

4. Backup the corrupted data file first, then overwrite it with the one for the temporary table.

[gpadmin@hdm1 ~]$ hdfs dfs -cp /hawq_data/gpseg0/16385/16554/476868.1 /hawq_data/gpseg0/16385/16554/476868.1.orig
[gpadmin@hdm1 ~]$ hdfs dfs -cp -f /hawq_data/gpseg2/16385/16554/476873.1 /hawq_data/gpseg0/16385/16554/476868.1

5. Run the MapReduce program again to dump the table data.

6. Remove the record from the temporary table, from the file generated by the MapReduce program.

[gpadmin@hdm1 employees]$ ls -l
total 124
-rw-r--r-- 1 gpadmin gpadmin 21681 Apr 13 23:38 part-m-00000
-rw-r--r-- 1 gpadmin gpadmin 21665 Apr 13 23:38 part-m-00001
-rw-r--r-- 1 gpadmin gpadmin 21469 Apr 13 23:38 part-m-00002
-rw-r--r-- 1 gpadmin gpadmin 21417 Apr 13 23:38 part-m-00003
-rw-r--r-- 1 gpadmin gpadmin 21337 Apr 13 23:38 part-m-00004
-rw-r--r-- 1 gpadmin gpadmin 16 Apr 13 23:38 part-m-00005
-rw-r--r-- 1 gpadmin gpadmin 0 Apr 13 23:38 _SUCCESS

[gpadmin@hdm1 employees]$ grep test8888 part-m*
part-m-00005:888888 test8888

Additional Information

For further information, please refer to the following articles: