Dear all,
I had a problem in hawq register in HDB 2.1.0 backed by Isilon HDFS on HDP
2.5
I created a one-level partitioned table:
create table sales2( amount numeric(10,2), year integer)
with(appendonly=true) distributed randomly partition by range(year)
(start(2014) end (2016) every(1));
then insert two records into that.
After that, I use "hawq extract -d testdb -o sales2.yml sale2" to extract
the table metadata.
Then I use "hawq register -d testdb --config sales2.yml sales6" to register
the HDFS into a new (not existing) table sales6.
I met below errors:
20161209:07:07:10:081923 hawqregister:kevi41hwxworker0:gpadmin-[INFO]:-try
to connect database localhost:5432 testdb
20161209:07:07:10:081923 hawqregister:kevi41hwxworker0:gpadmin-[INFO]:-Files
check...
20161209:07:07:13:081923 hawqregister:kevi41hwxworker0:gpadmin-[INFO]:-Files
check done.
20161209:07:07:13:081923 hawqregister:kevi41hwxworker0:gpadmin-[INFO]:-Files
check for table sales2_1_prt_1...
20161209:07:07:15:081923 hawqregister:kevi41hwxworker0:gpadmin-[INFO]:-Files
check done for table sales2_1_prt_1.
20161209:07:07:15:081923 hawqregister:kevi41hwxworker0:gpadmin-[INFO]:-Files
check for table sales2_1_prt_2...
20161209:07:07:17:081923 hawqregister:kevi41hwxworker0:gpadmin-[INFO]:-Files
check done for table sales2_1_prt_2.
20161209:07:07:22:081923 hawqregister:kevi41hwxworker0:gpadmin-[INFO]:-New
file(s) to be registered: ['hdfs://kevinhwx12061712.
bdl02.shanghai.dev:8020/hawq_default/16385/16508/16910/1']
20161209:07:07:25:081923 hawqregister:kevi41hwxworker0:gpadmin-[ERROR]:-Bucket
number of public.sales6_1_prt_1 is not consistent with previous bucket
number.
20161209:07:07:25:081923 hawqregister:kevi41hwxworker0:gpadmin-[INFO]:-Error
found, Hawqregister starts to rollback...
20161209:07:07:25:081923 hawqregister:kevi41hwxworker0:gpadmin-[INFO]:-Hawq
Register Rollback Finished.
*However*, if I created the table like:
create table sales3(id int, amount numeric(10,2), year integer)
with(appendonly=true) *distributed by* (id) partition by range(year)
(start(2014) end (2016) every(1));
And the extract and register works properly.
I have pasted the sales3.yml in below.
Please kindly help to check what is the problem and do I need to specify
the distributed by (column) in the Create Table DDL if I want to use hawq
register for one-level partitioned table?
Thanks!
Regards,
Lu Wenbin
===========================================================================
1) Sales schema by pg_dump -d testdb -t sales2 --schema-only
--inserts is preferred over -d. -d is deprecated.
--
-- Greenplum Database database dump
--
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET gp_enable_column_oriented_table = true;
SET default_with_oids = false;
--
-- Name: GPDUMPGUC; Type: INTERNAL GUC; Schema: -; Owner:
--
SET gp_called_by_pgdump = true;
SET search_path = public, pg_catalog;
SET default_tablespace = '';
--
-- Name: sales2; Type: TABLE; Schema: public; Owner: gpadmin; Tablespace:
--
CREATE TABLE sales2 (
amount numeric(10,2),
year integer
)
WITH (appendonly=true) DISTRIBUTED RANDOMLY PARTITION BY RANGE(year)
(
START (2014) END (2015) EVERY (1) WITH
(tablename='sales2_1_prt_1', orientation=row , appendonly=true ),
START (2015) END (2016) EVERY (1) WITH
(tablename='sales2_1_prt_2', orientation=row , appendonly=true )
);
ALTER TABLE public.sales2 OWNER TO gpadmin;
--
-- Greenplum Database database dump complete
--
2) Sales2.yml
AO_FileLocations:
Blocksize: 32768
Checksum: false
CompressionLevel: 0
CompressionType: null
Files:
- eofuncompressed: 0
path: /hawq_default/16385/16508/16910/1
size: 0
tupcount: 0
varblockcount: 0
PartitionBy: PARTITION BY range (year)
Partitions:
- Blocksize: 32768
Checksum: false
CompressionLevel: 0
CompressionType: null
Constraint: START (2014) END (2015) EVERY (1) WITH (appendonly=true)
Files:
- eofuncompressed: 32
path: /hawq_default/16385/16508/16915/1
size: 32
tupcount: 1
varblockcount: 1
Name: sales2_1_prt_1
- Blocksize: 32768
Checksum: false
CompressionLevel: 0
CompressionType: null
Constraint: START (2015) END (2016) EVERY (1) WITH (appendonly=true)
Files:
- eofuncompressed: 32
path: /hawq_default/16385/16508/16921/1
size: 32
tupcount: 1
varblockcount: 1
Name: sales2_1_prt_2
AO_Schema:
- name: amount
type: numeric
- name: year
type: int4
DBVersion: PostgreSQL 8.2.15 (Greenplum Database 4.2.0 build 1) (HAWQ
2.1.0.0 build
2490) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.8.2
20140120 (Red
Hat 4.8.2-15) compiled on Nov 30 2016 03:44:46
DFS_URL: hdfs://kevinhwx12061712.bdl02.shanghai.dev:8020
Distribution_Policy: DISTRIBUTED RANDOMLY
Encoding: UTF8
FileFormat: AO
TableName: public.sales2
Version: 1.0.0
3) sales3 schema
--inserts is preferred over -d. -d is deprecated.
--
-- Greenplum Database database dump
--
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET gp_enable_column_oriented_table = true;
SET default_with_oids = false;
--
-- Name: GPDUMPGUC; Type: INTERNAL GUC; Schema: -; Owner:
--
SET gp_called_by_pgdump = true;
SET search_path = public, pg_catalog;
SET default_tablespace = '';
--
-- Name: sales3; Type: TABLE; Schema: public; Owner: gpadmin; Tablespace:
--
CREATE TABLE sales3 (
id integer,
amount numeric(10,2),
year integer
)
WITH (appendonly=true) DISTRIBUTED BY (id) PARTITION BY RANGE(year)
(
START (2014) END (2015) EVERY (1) WITH
(tablename='sales3_1_prt_1', orientation=row , appendonly=true ),
START (2015) END (2016) EVERY (1) WITH
(tablename='sales3_1_prt_2', orientation=row , appendonly=true )
);
ALTER TABLE public.sales3 OWNER TO gpadmin;
--
-- Greenplum Database database dump complete
--
4) sales3.yml
AO_FileLocations:
Blocksize: 32768
Checksum: false
CompressionLevel: 0
CompressionType: null
Files:
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/1
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/2
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/3
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/4
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/5
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/6
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/7
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/8
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/9
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/10
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/11
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/12
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/13
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/14
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/15
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/16
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/17
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/18
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/19
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/20
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/21
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/22
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/23
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/24
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/25
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/26
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/27
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/28
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/29
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/30
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/31
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/32
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/33
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/34
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/35
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/36
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/37
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/38
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/39
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/40
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/41
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16970/42
size: 0
tupcount: 0
varblockcount: 0
PartitionBy: PARTITION BY range (year)
Partitions:
- Blocksize: 32768
Checksum: false
CompressionLevel: 0
CompressionType: null
Constraint: START (2014) END (2015) EVERY (1) WITH (appendonly=true)
Files:
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/1
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/2
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/3
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/4
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/5
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/6
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/7
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/8
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/9
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/10
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/11
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/12
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/13
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/14
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/15
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/16
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/17
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/18
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/19
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/20
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/21
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/22
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/23
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/24
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/25
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/26
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/27
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/28
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/29
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/30
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 32
path: /hawq_default/16385/16508/16975/31
size: 32
tupcount: 1
varblockcount: 1
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/32
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/33
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/34
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/35
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/36
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/37
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/38
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/39
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/40
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/41
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16975/42
size: 0
tupcount: 0
varblockcount: 0
Name: sales3_1_prt_1
- Blocksize: 32768
Checksum: false
CompressionLevel: 0
CompressionType: null
Constraint: START (2015) END (2016) EVERY (1) WITH (appendonly=true)
Files:
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/1
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/2
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/3
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/4
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/5
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/6
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/7
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/8
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/9
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/10
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/11
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/12
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/13
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/14
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/15
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 32
path: /hawq_default/16385/16508/16981/16
size: 32
tupcount: 1
varblockcount: 1
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/17
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/18
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/19
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/20
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/21
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/22
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/23
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/24
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/25
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/26
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/27
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/28
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/29
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/30
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/31
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/32
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/33
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/34
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/35
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/36
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/37
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/38
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/39
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/40
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/41
size: 0
tupcount: 0
varblockcount: 0
- eofuncompressed: 0
path: /hawq_default/16385/16508/16981/42
size: 0
tupcount: 0
varblockcount: 0
Name: sales3_1_prt_2
AO_Schema:
- name: id
type: int4
- name: amount
type: numeric
- name: year
type: int4
Bucketnum: 42
DBVersion: PostgreSQL 8.2.15 (Greenplum Database 4.2.0 build 1) (HAWQ
2.1.0.0 build
2490) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.8.2
20140120 (Red
Hat 4.8.2-15) compiled on Nov 30 2016 03:44:46
DFS_URL: hdfs://kevinhwx12061712.bdl02.shanghai.dev:8020
Distribution_Policy: DISTRIBUTED BY (id)
Encoding: UTF8
FileFormat: AO
TableName: public.sales3
Version: 1.0.0
