Wrong index data after importing Phoenix/HBASE table (by snapshot )-CodePudding

I need to migrate Phoenix/HBase tables from a very-old cluster to new one.

Origin cluster versions

HDP-2.5.3.0
HBASE 1.1.2 (HDP-2.5.3.0-37)
Phoenix 4.7.0 (HDP-2.5.3.0-37)
Ubuntu 12.04

Target cluster versions

BigTop(3.1.1) or vanilla Apache Hadop/Hbase binary build (same behaviours)
HBASE 2.4.11
Phoenix 5.1.2 (for HBase 2.4)
Ubuntu 20.04

I've used method and advices from https://github.com/karthikhw/hbase-snapshot and HBASE documentation (https://hbase.apache.org/book.html#ops.snapshots).

Exporting database from origin

On origin cluster, I've snapshoted PIMEP_MDB2 (with hbase shell)

snapshot 'PIMEP_MDB2', 'PIMEP_MDB2_20221103'

Next, I've copied the snapshot to target cluster HDFS.

hbase org.apache.hadoop.hbase.snapshot.ExportSnapshot -snapshot PIMEP_MDB2_20221103 -copy-to hdfs://172.16.42.155:9000/hbase -mappers 16

Importing database on target

On target cluster, with Phoenix SQLine.py, I've created the database

CREATE TABLE IF NOT EXISTS PIMEP_MDB2 (
    KINDEX FLOAT NOT NULL,
    KLON FLOAT NOT NULL,
    KLAT FLOAT NOT NULL,
    KDATE DATE NOT NULL,
    C.SSS_DEPTH_ARGO FLOAT,
    C.SSS_DEPTH_MAMMAL FLOAT,
    C.SSS_DEPTH_PLATFORM FLOAT,
    C.DIST_TO_COAST_ARGO FLOAT,
    C.DIST_TO_COAST FLOAT,
    C.SSTARGO FLOAT,
    C.SSTMAMMAL FLOAT,
    C.SST_PLATFORM FLOAT,
    C.SSSMAMMAL FLOAT,
    C.SSS_PLATFORM FLOAT,
    C.SSSARGO FLOAT,
    C.TIMELAGS FLOAT,
    C.SPATIALLAGS FLOAT,
    C.DELTASSS FLOAT,
    C.DMRTARGO FLOAT,
    C.CMORPH3H FLOAT,
    C.ASCATWIND FLOAT,
    C.SSSSAT FLOAT,
    C.SSSISAS FLOAT,
    C.SSTDRIFTER FLOAT,
    C.SSSDRIFTER FLOAT,
    C.ASCATWINDARGO FLOAT,
    CONSTRAINT pk PRIMARY KEY (KINDEX,KLON,KLAT,KDATE))
    COLUMN_ENCODED_BYTES = 0;

On Hbase shell, I've disabled database, imported snapshot and re-enabled database.

disable 'PIMEP_MDB2'
import_snapshot 'PIMEP_MDB2_20221103'
enable 'PIMEP_MDB2'

Seems ok, but when I look data on target cluster, my KINDEX, KLON, KLAT en KDATE are not consistent. These entries are concatenated to create the Hbase row key (see CONSTRAINT pk PRIMARY KEY (KINDEX,KLON,KLAT,KDATE)) on database creation).

Wrong data

Here, expected results (from origin cluster)

0: jdbc:phoenix:> select * from PIMEP_MDB2 limit 10;
 ---------- ------------ ----------- -------------------------- ----------------- ------------------- --------------------- ------------ 
|  KINDEX  |    KLON    |   KLAT    |          KDATE           | SSS_DEPTH_ARGO  | SSS_DEPTH_MAMMAL  | SSS_DEPTH_PLATFORM  | DIST_TO_CO |
 ---------- ------------ ----------- -------------------------- ----------------- ------------------- --------------------- ------------ 
| 35010.0  | -179.986   | -0.135    | 2016-05-08 06:19:15.000  | 4.1             | null              | null                | null       |
| 35010.0  | -179.993   | -19.317   | 2016-06-08 04:25:24.000  | 6.1             | null              | null                | null       |
| 35010.0  | -179.9976  | 18.612    | 2017-10-07 02:44:55.000  | 0.92            | null              | null                | null       |
| 35010.0  | -179.99    | 21.355    | 2015-10-21 11:54:23.000  | 4.2             | null              | null                | null       |
| 35010.0  | -179.998   | -39.668   | 2012-05-13 19:04:25.000  | 4.2             | null              | null                | null       |
| 35010.0  | -179.979   | 15.085    | 2013-03-21 00:27:21.000  | 5.2             | null              | null                | null       |
| 35010.0  | -179.999   | 59.138    | 2012-09-12 23:08:00.000  | 6.4             | null              | null                | null       |
| 35010.0  | -179.99    | -37.369   | 2014-07-21 23:04:05.000  | 6.1             | null              | null                | null       |
| 35010.0  | -179.999   | -28.172   | 2013-02-13 19:15:28.000  | 5.8             | null              | null                | null       |
| 35010.0  | -179.998   | -39.2038  | 2017-12-18 22:57:10.000  | 4.4             | null              | null                | null       |
 ---------- ------------ ----------- -------------------------- ----------------- ------------------- --------------------- ------------

Here, results from target cluster ( look at KDATE !, but KINDEX is faulty too )

0: jdbc:phoenix:> select * from PIMEP_MDB2 limit 10;
 -------------- --------------- ---------------- ------------ ---------------- ------------------ -------------------- ----------------- 
|    KINDEX    |     KLON      |      KLAT      |   KDATE    | SSS_DEPTH_ARGO | SSS_DEPTH_MAMMAL | SSS_DEPTH_PLATFORM | DIST_TO_COAST_A |
 -------------- --------------- ---------------- ------------ ---------------- ------------------ -------------------- ----------------- 
| -2.458617E38 | -1.297347E38  | 1.5667962E-25  | 4891-02-17 | 4.1            | null             | null               | null            |
| -2.458617E38 | -1.2973468E38 | 2.3466038E29   | 4716-06-14 | 2.0            | null             | null               | null            |
| -2.458617E38 | -1.2973466E38 | -3.26837102E12 | 8274-04-12 | 5.5            | null             | null               | null            |
| -2.458617E38 | -1.2973464E38 | 7.959833E8     | 2659-08-04 | 1.16           | null             | null               | null            |
| -2.458617E38 | -1.2973464E38 | 3.9434477E36   | 3920-01-27 | 5.5            | null             | null               | null            |
| -2.458617E38 | -1.2973464E38 | 3.9718884E36   | 8031-07-08 | 5.4            | null             | null               | null            |
| -2.458617E38 | -1.2973464E38 | 8.041029E36    | 7064-02-01 | 2.9            | null             | null               | null            |
| -2.458617E38 | -1.2973462E38 | -0.002895198   | 4131-09-25 | 4.1            | null             | null               | null            |
| -2.458617E38 | -1.2973459E38 | -4.6812513E26  | 3387-11-24 | 6.2            | null             | null               | null            |
| -2.458617E38 | -1.2973453E38 | -24859.004     | 0216-05-26 | 4.1            | null             | null               | null            |
 -------------- --------------- ---------------- ------------ ---------------- ------------------ -------------------- -----------------

Seems like Hbase row key isn't correctly splits on Phoenix "columns" (KINDEX, KLON, KLAT, KDATE)...

When creating, I following the same order and data type as on origin cluster (according origin metadata).

If anyone has a clue (good) or the solution (better), it will keep me from going crazy.

Best regards,

Tristan

CodePudding user response：

The answers is in the salt... The origin HBASE/Phoenix table was salted. The target table must be salted with same buckets number.

On origin cluster, we need to retrieved salt_buckets (here 12).

select table_name, salt_buckets from SYSTEM.CATALOG where salt_buckets is not null and TABLE_NAME = 'PIMEP_MDB2';

Next, when creating table on target, we must use COLUMN_ENCODED_BYTES = 0, SALT_BUCKET = 12; instead of COLUMN_ENCODED_BYTES = 0.

Import is successful and data are coherent.

Hope this help someone