| >> |
| >>cqd hbase_native_iud 'ON'; |
| |
| --- SQL operation complete. |
| >>cqd hbase_filter_preds 'ON'; |
| |
| --- SQL operation complete. |
| >> |
| >>drop hbase table T022HBT1; |
| |
| --- SQL operation complete. |
| >>create hbase table T022HBT1 (column family 'cf'); |
| |
| --- SQL operation complete. |
| >> |
| >>drop hbase table T022HBT2; |
| |
| --- SQL operation complete. |
| >>create hbase table T022HBT2 (column family 'cf'); |
| |
| --- SQL operation complete. |
| >> |
| >>drop table if exists t022t1; |
| |
| --- SQL operation complete. |
| >>create table if not exists t022t1 (a int not null primary key, b int not null); |
| |
| --- SQL operation complete. |
| >> |
| >>insert into hbase."_CELL_".t022hbt1 values ('1', 'cf', '1a', -1, '101'); |
| |
| --- 1 row(s) inserted. |
| >>insert into hbase."_ROW_".t022hbt1 values ('2', column_create('cf:2b', '201')), |
| +> ('1', column_create('cf:1b', '102')); |
| |
| --- 2 row(s) inserted. |
| >>prepare s from insert into hbase."_ROW_".t022hbt1 values |
| +> (?, column_create((?, ?), ('cf:3b', '302'))); |
| |
| --- SQL command prepared. |
| >>execute s using '3', 'cf:3a', '301'; |
| |
| --- 1 row(s) inserted. |
| >> |
| >>select left(row_id, 10) row_id, column_display(column_details, 40) column_details |
| +> from hbase."_ROW_".t022hbt1; |
| |
| ROW_ID COLUMN_DETAILS |
| ---------- ---------------------------------------- |
| |
| 1 cf:1a => 101, cf:1b => 102 |
| 2 cf:2b => 201 |
| 3 cf:3a => 301, cf:3b => 302 |
| |
| --- 3 row(s) selected. |
| >>select left(row_id, 10) row_id, left(column_display(column_details), 40) |
| +> from hbase."_ROW_".t022hbt1; |
| |
| ROW_ID (EXPR) |
| ---------- ---------------------------------------- |
| |
| 1 cf:1a => 101, cf:1b => 102 |
| 2 cf:2b => 201 |
| 3 cf:3a => 301, cf:3b => 302 |
| |
| --- 3 row(s) selected. |
| >>select left(row_id, 10) row_id, left(column_display(column_details, ('cf:2b', 'cf:1b')), 40) |
| +> from hbase."_ROW_".t022hbt1; |
| |
| ROW_ID (EXPR) |
| ---------- ---------------------------------------- |
| |
| 1 cf:1b => 102 |
| 2 cf:2b => 201 |
| |
| --- 2 row(s) selected. |
| >> |
| >>-- no rows updated. where pred fails. |
| >>update hbase."_ROW_".t022hbt1 set column_details = column_create ('cf:a', |
| +> (cast(column_lookup(column_details, 'cf:a') as varchar(10)) || '0103')) |
| +> where row_id = '3' and column_lookup(column_details, 'cf:3b') = '3021'; |
| |
| --- 0 row(s) updated. |
| >> |
| >>-- no rows updated. column not found in set clause. |
| >>update hbase."_ROW_".t022hbt1 set column_details = column_create ('cf:a', |
| +> (cast(column_lookup(column_details, 'cf:a') as varchar(10)) || '0103')) |
| +> where row_id = '3' and column_lookup(column_details, 'cf:3b') = '302'; |
| |
| --- 0 row(s) updated. |
| >> |
| >>begin work; |
| |
| --- SQL operation complete. |
| >>-- one row updated |
| >>update hbase."_ROW_".t022hbt1 set column_details = column_create ('cf:3a', |
| +> (cast(column_lookup(column_details, 'cf:3a') as varchar(10)) || '0103')) |
| +> where row_id = '3' and column_lookup(column_details, 'cf:3b') = '302'; |
| |
| --- 1 row(s) updated. |
| >> |
| >>select left(row_id, 10) row_id, left(column_display(column_details), 50) |
| +> from hbase."_ROW_".t022hbt1; |
| |
| ROW_ID (EXPR) |
| ---------- -------------------------------------------------- |
| |
| 1 cf:1a => 101, cf:1b => 102 |
| 2 cf:2b => 201 |
| 3 cf:3a => 3010103, cf:3a => 301, cf:3b => 302 |
| |
| --- 3 row(s) selected. |
| >>rollback work; |
| |
| --- SQL operation complete. |
| >> |
| >>select left(row_id, 10) row_id, left(column_display(column_details), 50) |
| +> from hbase."_ROW_".t022hbt1; |
| |
| ROW_ID (EXPR) |
| ---------- -------------------------------------------------- |
| |
| 1 cf:1a => 101, cf:1b => 102 |
| 2 cf:2b => 201 |
| 3 cf:3a => 301, cf:3b => 302 |
| |
| --- 3 row(s) selected. |
| >> |
| >>begin work; |
| |
| --- SQL operation complete. |
| >>-- one row updated |
| >>update hbase."_ROW_".t022hbt1 set column_details = column_create ('cf:3a', |
| +> (cast(column_lookup(column_details, 'cf:3a') as varchar(10)) || '0103')) |
| +> where row_id = '3' and column_lookup(column_details, 'cf:3b') = '302'; |
| |
| --- 1 row(s) updated. |
| >> |
| >>select left(row_id, 10) row_id, left(column_display(column_details), 50) |
| +> from hbase."_ROW_".t022hbt1; |
| |
| ROW_ID (EXPR) |
| ---------- -------------------------------------------------- |
| |
| 1 cf:1a => 101, cf:1b => 102 |
| 2 cf:2b => 201 |
| 3 cf:3a => 3010103, cf:3a => 301, cf:3b => 302 |
| |
| --- 3 row(s) selected. |
| >>commit work; |
| |
| --- SQL operation complete. |
| >> |
| >>select left(row_id, 10) row_id, left(column_display(column_details), 50) |
| +> from hbase."_ROW_".t022hbt1; |
| |
| ROW_ID (EXPR) |
| ---------- -------------------------------------------------- |
| |
| 1 cf:1a => 101, cf:1b => 102 |
| 2 cf:2b => 201 |
| 3 cf:3a => 3010103, cf:3b => 302 |
| |
| --- 3 row(s) selected. |
| >> |
| >>begin work; |
| |
| --- SQL operation complete. |
| >>insert into hbase."_ROW_".t022hbt1 values |
| +> ('4', column_create(('cf:4a', '301'), ('cf:4b', '302'))); |
| |
| --- 1 row(s) inserted. |
| >>select left(row_id, 10) row_id, left(column_display(column_details), 40) |
| +> from hbase."_ROW_".t022hbt1 where row_id = '4'; |
| |
| ROW_ID (EXPR) |
| ---------- ---------------------------------------- |
| |
| 4 cf:4a => 301, cf:4b => 302 |
| |
| --- 1 row(s) selected. |
| >>rollback work; |
| |
| --- SQL operation complete. |
| >> |
| >>select col_family, col_name, left(col_value, 20) from hbase."_CELL_".t022hbt1; |
| |
| COL_FAMILY COL_NAME (EXPR) |
| ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- -------------------- |
| |
| cf 1a 101 |
| cf 1b 102 |
| cf 2b 201 |
| cf 3a 3010103 |
| cf 3b 302 |
| |
| --- 5 row(s) selected. |
| >>select left(row_id, 10) row_id, left(column_display(column_details), 40) |
| +> from hbase."_ROW_".t022hbt1 where row_id = '4'; |
| |
| --- 0 row(s) selected. |
| >> |
| >>select left(column_lookup (column_details, 'cf:1a'), 20) from hbase."_ROW_".t022hbt1; |
| |
| (EXPR) |
| -------------------- |
| |
| 101 |
| |
| --- 1 row(s) selected. |
| >> |
| >>select left(column_lookup (column_details, 'cf:3a'), 20) from hbase."_ROW_".t022hbt1 |
| +> where column_lookup (column_details, 'cf:3b') = '302'; |
| |
| (EXPR) |
| -------------------- |
| |
| 3010103 |
| |
| --- 1 row(s) selected. |
| >> |
| >>select left(column_lookup (column_details, 'cf:3a'), 20) from hbase."_ROW_".t022hbt1 |
| +> where column_lookup (column_details, 'cf:3a') = '301' |
| +> and column_lookup (column_details, 'cf:3b') = '302'; |
| |
| --- 0 row(s) selected. |
| >> |
| >>select left(column_lookup (column_details, 'cf:3a'), 20) from hbase."_ROW_".t022hbt1 |
| +> where column_lookup (column_details, 'cf:3a') = '3010103' |
| +> and column_lookup (column_details, 'cf:3b') = '302'; |
| |
| (EXPR) |
| -------------------- |
| |
| 3010103 |
| |
| --- 1 row(s) selected. |
| >> |
| >>select left(column_lookup (column_details, 'cf:3c'), 20) from hbase."_ROW_".t022hbt1 |
| +> where column_lookup (column_details, 'cf:3b') = '3020'; |
| |
| --- 0 row(s) selected. |
| >> |
| >>select left(column_lookup (column_details, 'cf:3c'), 20) from hbase."_ROW_".t022hbt1 |
| +> where column_lookup (column_details, 'cf:3b') = '302'; |
| |
| (EXPR) |
| -------------------- |
| |
| ? |
| |
| --- 1 row(s) selected. |
| >> |
| >>select column_lookup (column_details, 'cf:3a', cast as int), |
| +> column_lookup (column_details, 'cf:3a', cast as int) + 1 |
| +> from hbase."_ROW_".t022hbt1; |
| |
| (EXPR) (EXPR) |
| ----------- -------------------- |
| |
| 3010103 3010104 |
| |
| --- 1 row(s) selected. |
| >> |
| >>select column_lookup (column_details, 'cf:3a', cast as int), |
| +> column_lookup (column_details, 'cf:3a', cast as int) + 1 |
| +> from hbase."_ROW_".t022hbt1 |
| +> where |
| +> column_lookup (column_details, 'cf:3b', cast as int) = 302; |
| |
| (EXPR) (EXPR) |
| ----------- -------------------- |
| |
| 3010103 3010104 |
| |
| --- 1 row(s) selected. |
| >> |
| >>begin work; |
| |
| --- SQL operation complete. |
| >>delete from hbase."_ROW_".t022hbt1 where row_id = '3'; |
| |
| --- 1 row(s) deleted. |
| >>select left(row_id, 10) from hbase."_ROW_".t022hbt1; |
| |
| (EXPR) |
| ---------- |
| |
| 1 |
| 2 |
| |
| --- 2 row(s) selected. |
| >>commit work; |
| |
| --- SQL operation complete. |
| >>select left(row_id, 10) from hbase."_ROW_".t022hbt1; |
| |
| (EXPR) |
| ---------- |
| |
| 1 |
| 2 |
| |
| --- 2 row(s) selected. |
| >> |
| >>delete columns ('cf:1a') from hbase."_ROW_".t022hbt1 where row_id = '1'; |
| |
| --- 1 row(s) deleted. |
| >>select left(row_id, 10) row_id, column_display(column_details, 40) column_details |
| +> from hbase."_ROW_".t022hbt1; |
| |
| ROW_ID COLUMN_DETAILS |
| ---------- ---------------------------------------- |
| |
| 1 cf:1b => 102 |
| 2 cf:2b => 201 |
| |
| --- 2 row(s) selected. |
| >> |
| >>delete from hbase."_ROW_".t022hbt1; |
| |
| --- 2 row(s) deleted. |
| >>select count(*) from hbase."_ROW_".t022hbt1; |
| |
| (EXPR) |
| -------------------- |
| |
| 0 |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- operations between trafodion and hbase tables |
| >>begin work; |
| |
| --- SQL operation complete. |
| >>insert into t022t1 values (1, 100); |
| |
| --- 1 row(s) inserted. |
| >>insert into hbase."_ROW_".t022hbt1 values ('1', column_create('cf:a', '100')); |
| |
| --- 1 row(s) inserted. |
| >>select count(*) from t022t1; |
| |
| (EXPR) |
| -------------------- |
| |
| 1 |
| |
| --- 1 row(s) selected. |
| >>select count(*) from hbase."_ROW_".t022hbt1; |
| |
| (EXPR) |
| -------------------- |
| |
| 1 |
| |
| --- 1 row(s) selected. |
| >>rollback work; |
| |
| --- SQL operation complete. |
| >>select count(*) from t022t1; |
| |
| (EXPR) |
| -------------------- |
| |
| 0 |
| |
| --- 1 row(s) selected. |
| >>select count(*) from hbase."_ROW_".t022hbt1; |
| |
| (EXPR) |
| -------------------- |
| |
| 0 |
| |
| --- 1 row(s) selected. |
| >> |
| >>insert into hbase."_ROW_".t022hbt1 values |
| +> ('3', column_create(('cf:3a', '301'), ('cf:3b', '302'))); |
| |
| --- 1 row(s) inserted. |
| >>update hbase."_ROW_".t022hbt1 set column_details = column_create('cf:3b', '3022') |
| +> where row_id = '3' and column_lookup(column_details, 'cf:3a', cast as int) = 3011; |
| |
| --- 0 row(s) updated. |
| >>update hbase."_ROW_".t022hbt1 set column_details = column_create('cf:3b', '3022') |
| +> where row_id = '3' and column_lookup(column_details, 'cf:3a', cast as int) = 301; |
| |
| --- 1 row(s) updated. |
| >>select left(row_id, 10) row_id, left(column_display(column_details), 40) |
| +> from hbase."_ROW_".t022hbt1; |
| |
| ROW_ID (EXPR) |
| ---------- ---------------------------------------- |
| |
| 3 cf:3a => 301, cf:3b => 3022 |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- negative tests |
| >>delete from hbase."_CELL_".t022hbt1; |
| |
| *** ERROR[1425] This operation could not be performed on HBASE."_CELL_".T022HBT1. Reason: Cannot delete from an hbase table in CELL format. Use ROW format for this operation. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>insert into hbase."_ROW_".t022hbt1 values ('2', column_create(':b', '201')); |
| |
| *** ERROR[1426] An invalid HBase column name :b was specified. A valid name must be of the format: <ColumnFamily>:<ColumnName> |
| |
| --- 0 row(s) inserted. |
| >>insert into hbase."_ROW_".t022hbt1 values ('2', '100'); |
| |
| *** ERROR[1429] Inserts into _ROW_ format external HBase tables can only use the VALUES clause and must use the column_create function to create values. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>insert into hbase."_ROW_".t022hbt1 select * from hbase."_ROW_".t022hbt2; |
| |
| *** ERROR[1429] Inserts into _ROW_ format external HBase tables can only use the VALUES clause and must use the column_create function to create values. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>-- tests with large rows |
| >>invoke hbase."_CELL_".t022hbt2; |
| |
| -- Definition of Trafodion table HBASE."_CELL_".T022HBT2 |
| -- Definition current Mon May 15 01:28:23 2017 |
| |
| ( |
| ROW_ID VARCHAR(100) CHARACTER SET ISO88591 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COL_FAMILY VARCHAR(100) CHARACTER SET ISO88591 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COL_NAME VARCHAR(100) CHARACTER SET ISO88591 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COL_TIMESTAMP LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COL_VALUE VARCHAR(1000) CHARACTER SET ISO88591 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| ) |
| PRIMARY KEY (ROW_ID ASC) |
| |
| --- SQL operation complete. |
| >>invoke hbase."_ROW_".t022hbt2; |
| |
| -- Definition of Trafodion table HBASE."_ROW_".T022HBT2 |
| -- Definition current Mon May 15 01:28:24 2017 |
| |
| ( |
| ROW_ID VARCHAR(100) CHARACTER SET ISO88591 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COLUMN_DETAILS VARCHAR(10000) CHARACTER SET ISO88591 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| ) |
| PRIMARY KEY (ROW_ID ASC) |
| |
| --- SQL operation complete. |
| >>cqd hbase_max_column_val_length '100000'; |
| |
| --- SQL operation complete. |
| >>cqd hbase_max_column_info_length '100000'; |
| |
| --- SQL operation complete. |
| >>invoke hbase."_CELL_".t022hbt2; |
| |
| -- Definition of Trafodion table HBASE."_CELL_".T022HBT2 |
| -- Definition current Mon May 15 01:28:24 2017 |
| |
| ( |
| ROW_ID VARCHAR(100) CHARACTER SET ISO88591 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COL_FAMILY VARCHAR(100) CHARACTER SET ISO88591 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COL_NAME VARCHAR(100) CHARACTER SET ISO88591 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COL_TIMESTAMP LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COL_VALUE VARCHAR(100000) CHARACTER SET ISO88591 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| ) |
| PRIMARY KEY (ROW_ID ASC) |
| |
| --- SQL operation complete. |
| >>invoke hbase."_ROW_".t022hbt2; |
| |
| -- Definition of Trafodion table HBASE."_ROW_".T022HBT2 |
| -- Definition current Mon May 15 01:28:25 2017 |
| |
| ( |
| ROW_ID VARCHAR(100) CHARACTER SET ISO88591 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COLUMN_DETAILS VARCHAR(100000) CHARACTER SET ISO88591 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| ) |
| PRIMARY KEY (ROW_ID ASC) |
| |
| --- SQL operation complete. |
| >>insert into hbase."_CELL_".t022hbt2 values ('1', 'cf', 'a', -1, repeat('a', 100000)); |
| |
| --- 1 row(s) inserted. |
| >>insert into hbase."_ROW_".t022hbt2 values |
| +> ('2', column_create( |
| +> ('cf:1a', repeat('a', 40000)), |
| +> ('cf:1b', repeat('z', 40000)))); |
| |
| --- 1 row(s) inserted. |
| >>select count(*) from hbase."_CELL_".t022hbt2; |
| |
| (EXPR) |
| -------------------- |
| |
| 3 |
| |
| --- 1 row(s) selected. |
| >>select char_length(col_value) from hbase."_CELL_".t022hbt2 order by 1; |
| |
| (EXPR) |
| ---------- |
| |
| 40000 |
| 40000 |
| 100000 |
| |
| --- 3 row(s) selected. |
| >>select count(*) from hbase."_CELL_".t022hbt2; |
| |
| (EXPR) |
| -------------------- |
| |
| 3 |
| |
| --- 1 row(s) selected. |
| >>select char_length(col_value) from hbase."_CELL_".t022hbt2 order by 1; |
| |
| (EXPR) |
| ---------- |
| |
| 40000 |
| 40000 |
| 100000 |
| |
| --- 3 row(s) selected. |
| >>select count(*) from hbase."_ROW_".t022hbt2; |
| |
| (EXPR) |
| -------------------- |
| |
| 2 |
| |
| --- 1 row(s) selected. |
| >>select char_length(column_details) from hbase."_ROW_".t022hbt2 order by 1; |
| |
| (EXPR) |
| ---------- |
| |
| 80022 |
| 100000 |
| |
| --- 2 row(s) selected. |
| >>select left(row_id, 10) row_id, left(column_display(column_details), 40) |
| +> from hbase."_ROW_".t022hbt2; |
| |
| ROW_ID (EXPR) |
| ---------- ---------------------------------------- |
| |
| 1 cf:a => aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
| 2 cf:1a => aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
| |
| --- 2 row(s) selected. |
| >> |
| >> |
| >>-- tests to map hbase tables to relational traf tables |
| >>cqd traf_hbase_mapped_tables 'ON'; |
| |
| --- SQL operation complete. |
| >> |
| >>drop hbase table t022hbm1; |
| |
| --- SQL operation complete. |
| >>create hbase table t022hbm1 (column family 'cf'); |
| |
| --- SQL operation complete. |
| >>insert into hbase."_ROW_".t022hbm1 values ('a1', |
| +> column_create(('cf:B', '100 '))); |
| |
| --- 1 row(s) inserted. |
| >>insert into hbase."_ROW_".t022hbm1 values ('a2', column_create(('cf:A', 'a2'))); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>drop table if exists t022hbm1 cascade; |
| |
| --- SQL operation complete. |
| >>drop external table if exists t022hbm1; |
| |
| --- SQL operation complete. |
| >>create external table t022hbm1 (a varchar(4) not null, b char(4)) |
| +> primary key (a) |
| +> attribute default column family 'cf' |
| +> map to hbase table t022hbm1; |
| |
| --- SQL operation complete. |
| >>invoke t022hbm1; |
| |
| -- Definition of Trafodion HBase mapped table T022HBM1 |
| -- Definition current Mon May 15 01:28:41 2017 |
| |
| ( |
| "cf".A VARCHAR(4) CHARACTER SET ISO88591 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , "cf".B CHAR(4) CHARACTER SET ISO88591 COLLATE |
| DEFAULT DEFAULT NULL |
| ) |
| PRIMARY KEY NOT SERIALIZED (A ASC) |
| |
| --- SQL operation complete. |
| >> |
| >>-- if no schema is specified, table is looked in regular and then mapped schema |
| >>invoke t022hbm1; |
| |
| -- Definition of Trafodion HBase mapped table T022HBM1 |
| -- Definition current Mon May 15 01:28:41 2017 |
| |
| ( |
| "cf".A VARCHAR(4) CHARACTER SET ISO88591 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , "cf".B CHAR(4) CHARACTER SET ISO88591 COLLATE |
| DEFAULT DEFAULT NULL |
| ) |
| PRIMARY KEY NOT SERIALIZED (A ASC) |
| |
| --- SQL operation complete. |
| >>create table t022hbm1 (a int); |
| |
| --- SQL operation complete. |
| >>invoke t022hbm1; |
| |
| -- Definition of Trafodion table TRAFODION.SCH.T022HBM1 |
| -- Definition current Mon May 15 01:28:44 2017 |
| |
| ( |
| SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , A INT DEFAULT NULL |
| ) |
| |
| --- SQL operation complete. |
| >> |
| >>-- join between traf and hbase table with the same name. |
| >>prepare s from select * from t022hbm1, hbase."_MAP_".t022hbm1; |
| |
| --- SQL command prepared. |
| >>explain options 'f' s; |
| |
| LC RC OP OPERATOR OPT DESCRIPTION CARD |
| ---- ---- ---- -------------------- -------- -------------------- --------- |
| |
| 3 . 4 root 1.00E+004 |
| 2 1 3 hybrid_hash_join 1.00E+004 |
| . . 2 trafodion_scan T022HBM1 1.00E+002 |
| . . 1 trafodion_scan T022HBM1 1.00E+002 |
| |
| --- SQL operation complete. |
| >> |
| >>drop table t022hbm1; |
| |
| --- SQL operation complete. |
| >>invoke t022hbm1; |
| |
| -- Definition of Trafodion HBase mapped table T022HBM1 |
| -- Definition current Mon May 15 01:28:50 2017 |
| |
| ( |
| "cf".A VARCHAR(4) CHARACTER SET ISO88591 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , "cf".B CHAR(4) CHARACTER SET ISO88591 COLLATE |
| DEFAULT DEFAULT NULL |
| ) |
| PRIMARY KEY NOT SERIALIZED (A ASC) |
| |
| --- SQL operation complete. |
| >> |
| >>-- should return error 4056 |
| >>prepare s from select * from t022hbm1, hbase."_MAP_".t022hbm1; |
| |
| *** ERROR[4056] Exposed name TRAFODION."_HB_MAP_".T022HBM1 appears more than once. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>prepare s from select * from t022hbm1 x, hbase."_MAP_".t022hbm1 y; |
| |
| --- SQL command prepared. |
| >> |
| >>prepare s from select * from t022hbm1; |
| |
| --- SQL command prepared. |
| >>execute s; |
| |
| A B |
| ---- ---- |
| |
| a1 100 |
| a2 ? |
| |
| --- 2 row(s) selected. |
| >>select * from t022hbm1 where a = 'a1'; |
| |
| A B |
| ---- ---- |
| |
| a1 100 |
| |
| --- 1 row(s) selected. |
| >>select a, cast(b as int) from t022hbm1; |
| |
| A (EXPR) |
| ---- ----------- |
| |
| a1 100 |
| a2 ? |
| |
| --- 2 row(s) selected. |
| >> |
| >>alter table t022hbm1 add column "cf".c int; |
| |
| --- SQL operation complete. |
| >>invoke t022hbm1; |
| |
| -- Definition of Trafodion HBase mapped table T022HBM1 |
| -- Definition current Mon May 15 01:28:55 2017 |
| |
| ( |
| "cf".A VARCHAR(4) CHARACTER SET ISO88591 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , "cf".B CHAR(4) CHARACTER SET ISO88591 COLLATE |
| DEFAULT DEFAULT NULL |
| , "cf".C INT DEFAULT NULL /*added_col*/ |
| ) |
| PRIMARY KEY NOT SERIALIZED (A ASC) |
| |
| --- SQL operation complete. |
| >> |
| >>-- create a traf table like a mapped table |
| >>cqd schema reset; |
| |
| --- SQL operation complete. |
| >>create table t022hbm1_like like t022hbm1; |
| |
| --- SQL operation complete. |
| >>invoke t022hbm1_like; |
| |
| -- Definition of Trafodion table TRAFODION.SCH.T022HBM1_LIKE |
| -- Definition current Mon May 15 01:29:01 2017 |
| |
| ( |
| "cf".A VARCHAR(4) CHARACTER SET ISO88591 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , "cf".B CHAR(4) CHARACTER SET ISO88591 COLLATE |
| DEFAULT DEFAULT NULL |
| , "cf".C INT DEFAULT NULL |
| ) |
| PRIMARY KEY (A ASC) |
| |
| --- SQL operation complete. |
| >> |
| >>insert into t022hbm1_like select * from t022hbm1; |
| |
| --- 2 row(s) inserted. |
| >>select * from t022hbm1_like; |
| |
| A B C |
| ---- ---- ----------- |
| |
| a1 100 ? |
| a2 ? ? |
| |
| --- 2 row(s) selected. |
| >> |
| >>create table t022hbm1_ctas as select * from t022hbm1; |
| |
| --- 2 row(s) inserted. |
| >>select * from t022hbm1_ctas; |
| |
| A B C |
| ---- ---- ----------- |
| |
| a1 100 ? |
| a2 ? ? |
| |
| --- 2 row(s) selected. |
| >> |
| >>-- create view on mapped table |
| >>create view t022v1 as select * from t022hbm1; |
| |
| --- SQL operation complete. |
| >> |
| >>drop external table t022hbm1 cascade; |
| |
| --- SQL operation complete. |
| >>invoke t022hbm1; |
| |
| *** ERROR[4082] Object TRAFODION.SCH.T022HBM1 does not exist or is inaccessible. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>create external table t022hbm1 ("cf".a varchar(4) not null, |
| +> b int) |
| +> primary key (a) |
| +> map to hbase table t022hbm1; |
| |
| --- SQL operation complete. |
| >>invoke t022hbm1; |
| |
| -- Definition of Trafodion HBase mapped table T022HBM1 |
| -- Definition current Mon May 15 01:29:22 2017 |
| |
| ( |
| "cf".A VARCHAR(4) CHARACTER SET ISO88591 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , B INT DEFAULT NULL |
| ) |
| PRIMARY KEY NOT SERIALIZED (A ASC) |
| |
| --- SQL operation complete. |
| >> |
| >>alter table t022hbm1 drop column b; |
| |
| --- SQL operation complete. |
| >>invoke t022hbm1; |
| |
| -- Definition of Trafodion HBase mapped table T022HBM1 |
| -- Definition current Mon May 15 01:29:25 2017 |
| |
| ( |
| "cf".A VARCHAR(4) CHARACTER SET ISO88591 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| ) |
| PRIMARY KEY NOT SERIALIZED (A ASC) |
| |
| --- SQL operation complete. |
| >> |
| >>alter table t022hbm1 add column "cf".b int; |
| |
| --- SQL operation complete. |
| >>invoke t022hbm1; |
| |
| -- Definition of Trafodion HBase mapped table T022HBM1 |
| -- Definition current Mon May 15 01:29:28 2017 |
| |
| ( |
| "cf".A VARCHAR(4) CHARACTER SET ISO88591 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , "cf".B INT DEFAULT NULL /*added_col*/ |
| ) |
| PRIMARY KEY NOT SERIALIZED (A ASC) |
| |
| --- SQL operation complete. |
| >> |
| >>-- IUD operations on mapped tables |
| >>cqd traf_hbase_mapped_tables_iud 'ON'; |
| |
| --- SQL operation complete. |
| >> |
| >>cqd schema reset; |
| |
| --- SQL operation complete. |
| >>delete from hbase."_ROW_".t022hbm1; |
| |
| --- 2 row(s) deleted. |
| >>drop external table t022hbm1; |
| |
| --- SQL operation complete. |
| >>create external table t022hbm1 (a varchar(4) not null, b int) |
| +> primary key (a) |
| +> attribute default column family 'cf' |
| +> map to hbase table t022hbm1 |
| +> data format native; |
| |
| --- SQL operation complete. |
| >>--This part of the test is being commented out due to non deterministic results |
| >>--It needs to be renabled after this JIRA is fixed : TRAFODION-2613 |
| >>--insert into t022hbm1 values ('a', 1); |
| >>--select * from t022hbm1; |
| >>--update t022hbm1 set b = b + 1; |
| >>--select * from t022hbm1; |
| >>--insert into t022hbm1 values ('a', 1); -- should fail |
| >>--insert into t022hbm1 values ('b', null); |
| >>--select * from t022hbm1; |
| >>--delete from t022hbm1 where a = 'a'; |
| >>--select * from t022hbm1; |
| >>--update t022hbm1 set b = 10; |
| >>--select * from t022hbm1; |
| >>--update t022hbm1 set b = null; |
| >>--select * from t022hbm1; |
| >>--delete from t022hbm1; |
| >>--select * from t022hbm1; |
| >> |
| >>drop external table t022hbm1; |
| |
| --- SQL operation complete. |
| >>create external table t022hbm1 (a int not null, b int not null, c int) |
| +> primary key (a, b) |
| +> attribute default column family 'cf' |
| +> map to hbase table t022hbm1 |
| +> data format native; |
| |
| --- SQL operation complete. |
| >>invoke t022hbm1; |
| |
| -- Definition of Trafodion HBase mapped table T022HBM1 |
| -- Definition current Mon May 15 01:29:41 2017 |
| |
| ( |
| "cf".A INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , "cf".B INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , "cf".C INT DEFAULT NULL |
| ) |
| PRIMARY KEY NOT SERIALIZED (A ASC, B ASC) |
| |
| --- SQL operation complete. |
| >>insert into t022hbm1 values (1,2,3); |
| |
| --- 1 row(s) inserted. |
| >>insert into t022hbm1 values (1,2,3); |
| |
| *** ERROR[8102] The operation is prevented by a unique constraint. |
| |
| --- 0 row(s) inserted. |
| >>insert into t022hbm1 values (1, 1, 1); |
| |
| --- 1 row(s) inserted. |
| >>insert into t022hbm1 values (-1, -2, -3); |
| |
| --- 1 row(s) inserted. |
| >>select * from t022hbm1 order by 1; |
| |
| A B C |
| ----------- ----------- ----------- |
| |
| 1 1 1 |
| 1 2 3 |
| -1 -2 -3 |
| |
| --- 3 row(s) selected. |
| >>upsert into t022hbm1 values (1,2,4); |
| |
| --- 1 row(s) inserted. |
| >>select * from t022hbm1 order by 1; |
| |
| A B C |
| ----------- ----------- ----------- |
| |
| 1 1 1 |
| 1 2 4 |
| -1 -2 -3 |
| |
| --- 3 row(s) selected. |
| >> |
| >>-- various serialization options |
| >>drop external table t022hbm1; |
| |
| --- SQL operation complete. |
| >>create external table t022hbm1 (a varchar(4) not null, primary key not serialized (a), b int) |
| +> attribute default column family 'cf' |
| +> map to hbase table t022hbm1; |
| |
| --- SQL operation complete. |
| >>invoke t022hbm1; |
| |
| -- Definition of Trafodion HBase mapped table T022HBM1 |
| -- Definition current Mon May 15 01:29:47 2017 |
| |
| ( |
| "cf".A VARCHAR(4) CHARACTER SET ISO88591 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , "cf".B INT DEFAULT NULL |
| ) |
| PRIMARY KEY NOT SERIALIZED (A ASC) |
| |
| --- SQL operation complete. |
| >> |
| >>drop external table t022hbm1; |
| |
| --- SQL operation complete. |
| >>create external table t022hbm1 (a char(4) not null, primary key serialized (a), b int) |
| +> attribute default column family 'cf' |
| +> map to hbase table t022hbm1 |
| +> data format native; |
| |
| --- SQL operation complete. |
| >>invoke t022hbm1; |
| |
| -- Definition of Trafodion HBase mapped table T022HBM1 |
| -- Definition current Mon May 15 01:29:52 2017 |
| |
| ( |
| "cf".A CHAR(4) CHARACTER SET ISO88591 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , "cf".B INT DEFAULT NULL |
| ) |
| PRIMARY KEY (A ASC) |
| |
| --- SQL operation complete. |
| >> |
| >>drop external table t022hbm1; |
| |
| --- SQL operation complete. |
| >>create external table t022hbm1 (a varchar(4) not null, primary key(a), b int) |
| +> attribute default column family 'cf' |
| +> map to hbase table t022hbm1; |
| |
| --- SQL operation complete. |
| >>invoke t022hbm1; |
| |
| -- Definition of Trafodion HBase mapped table T022HBM1 |
| -- Definition current Mon May 15 01:29:56 2017 |
| |
| ( |
| "cf".A VARCHAR(4) CHARACTER SET ISO88591 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , "cf".B INT DEFAULT NULL |
| ) |
| PRIMARY KEY NOT SERIALIZED (A ASC) |
| |
| --- SQL operation complete. |
| >> |
| >>drop external table t022hbm1; |
| |
| --- SQL operation complete. |
| >>create external table t022hbm1 (a varchar(4) not null primary key, b int) |
| +> attribute default column family 'cf' |
| +> map to hbase table t022hbm1; |
| |
| --- SQL operation complete. |
| >>invoke t022hbm1; |
| |
| -- Definition of Trafodion HBase mapped table T022HBM1 |
| -- Definition current Mon May 15 01:30:01 2017 |
| |
| ( |
| "cf".A VARCHAR(4) CHARACTER SET ISO88591 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , "cf".B INT DEFAULT NULL |
| ) |
| PRIMARY KEY NOT SERIALIZED (A ASC) |
| |
| --- SQL operation complete. |
| >> |
| >>drop external table t022hbm1; |
| |
| --- SQL operation complete. |
| >>create external table t022hbm1 (a varchar(4) not null, b int) |
| +> primary key not serialized (a) |
| +> attribute default column family 'cf' |
| +> map to hbase table t022hbm1; |
| |
| --- SQL operation complete. |
| >>invoke t022hbm1; |
| |
| -- Definition of Trafodion HBase mapped table T022HBM1 |
| -- Definition current Mon May 15 01:30:06 2017 |
| |
| ( |
| "cf".A VARCHAR(4) CHARACTER SET ISO88591 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , "cf".B INT DEFAULT NULL |
| ) |
| PRIMARY KEY NOT SERIALIZED (A ASC) |
| |
| --- SQL operation complete. |
| >> |
| >>drop external table t022hbm1; |
| |
| --- SQL operation complete. |
| >>create external table t022hbm1 (a char(4) not null, b int) |
| +> primary key serialized (a) |
| +> attribute default column family 'cf' |
| +> map to hbase table t022hbm1 |
| +> data format native; |
| |
| --- SQL operation complete. |
| >>invoke t022hbm1; |
| |
| -- Definition of Trafodion HBase mapped table T022HBM1 |
| -- Definition current Mon May 15 01:30:11 2017 |
| |
| ( |
| "cf".A CHAR(4) CHARACTER SET ISO88591 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , "cf".B INT DEFAULT NULL |
| ) |
| PRIMARY KEY (A ASC) |
| |
| --- SQL operation complete. |
| >> |
| >>drop external table t022hbm1; |
| |
| --- SQL operation complete. |
| >>create external table t022hbm1 (a varchar(4) not null, b int) |
| +> primary key (a) |
| +> attribute default column family 'cf' |
| +> map to hbase table t022hbm1; |
| |
| --- SQL operation complete. |
| >>invoke t022hbm1; |
| |
| -- Definition of Trafodion HBase mapped table T022HBM1 |
| -- Definition current Mon May 15 01:30:15 2017 |
| |
| ( |
| "cf".A VARCHAR(4) CHARACTER SET ISO88591 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , "cf".B INT DEFAULT NULL |
| ) |
| PRIMARY KEY NOT SERIALIZED (A ASC) |
| |
| --- SQL operation complete. |
| >> |
| >>drop hbase table t022hbm1; |
| |
| --- SQL operation complete. |
| >>create hbase table t022hbm1 (column family 'cf'); |
| |
| --- SQL operation complete. |
| >>drop external table if exists t022hbm1; |
| |
| --- SQL operation complete. |
| >>create external table t022hbm1 (a varchar(4) not null, b int) |
| +> primary key (a) |
| +> attribute default column family 'cf' |
| +> map to hbase table t022hbm1; |
| |
| --- SQL operation complete. |
| >>insert into hbase."_ROW_".t022hbm1 values ('a1', column_create(('cf:B', '10'))); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>-- multi column families in mapped table |
| >>drop external table if exists t022hbm2; |
| |
| --- SQL operation complete. |
| >>drop hbase table t022hbm2; |
| |
| --- SQL operation complete. |
| >>create hbase table t022hbm2 (column family 'cf1', column family 'cf2'); |
| |
| --- SQL operation complete. |
| >>create external table t022hbm2 ("cf1".A int, "cf2".B int, |
| +> "cf1".Z varchar(4) not null primary key) |
| +> map to hbase table t022hbm2; |
| |
| --- SQL operation complete. |
| >>insert into hbase."_ROW_".t022hbm2 values ('a11', |
| +> column_create(('cf1:A', '10'), ('cf2:B', '20'))); |
| |
| --- 1 row(s) inserted. |
| >>invoke t022hbm2; |
| |
| -- Definition of Trafodion HBase mapped table T022HBM2 |
| -- Definition current Mon May 15 01:30:31 2017 |
| |
| ( |
| "cf1".A INT DEFAULT NULL |
| , "cf2".B INT DEFAULT NULL |
| , "cf1".Z VARCHAR(4) CHARACTER SET ISO88591 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| ) |
| PRIMARY KEY NOT SERIALIZED (Z ASC) |
| |
| --- SQL operation complete. |
| >>select * from t022hbm2; |
| |
| A B Z |
| ----------- ----------- ---- |
| |
| 10 20 a11 |
| |
| --- 1 row(s) selected. |
| >>cleanup hbase table t022hbm2; |
| |
| --- SQL operation complete. |
| >> |
| >>-- registration of external hbase tables |
| >>drop external table if exists t022hbm2; |
| |
| --- SQL operation complete. |
| >>drop hbase table t022hbm2; |
| |
| --- SQL operation complete. |
| >>create hbase table t022hbm2 (column family 'cf1', column family 'cf2'); |
| |
| --- SQL operation complete. |
| >>create external table t022hbm2 ("cf1".A int, "cf2".B int, |
| +> "cf1".Z varchar(4) not null primary key) |
| +> map to hbase table t022hbm2; |
| |
| --- SQL operation complete. |
| >> |
| >>insert into hbase."_ROW_".t022hbm2 values ('a11', |
| +> column_create(('cf1:A', '10'), ('cf2:B', '20'))); |
| |
| --- 1 row(s) inserted. |
| >>invoke t022hbm2; |
| |
| -- Definition of Trafodion HBase mapped table T022HBM2 |
| -- Definition current Mon May 15 01:30:52 2017 |
| |
| ( |
| "cf1".A INT DEFAULT NULL |
| , "cf2".B INT DEFAULT NULL |
| , "cf1".Z VARCHAR(4) CHARACTER SET ISO88591 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| ) |
| PRIMARY KEY NOT SERIALIZED (Z ASC) |
| |
| --- SQL operation complete. |
| >>select * from t022hbm2; |
| |
| A B Z |
| ----------- ----------- ---- |
| |
| 10 20 a11 |
| |
| --- 1 row(s) selected. |
| >> |
| >>showddl hbase."_CELL_".t022hbm2; |
| |
| /* |
| CREATE TABLE HBASE."_CELL_".T022HBM2 |
| ( |
| ROW_ID VARCHAR(100) CHARACTER SET ISO88591 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COL_FAMILY VARCHAR(100) CHARACTER SET ISO88591 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COL_NAME VARCHAR(100) CHARACTER SET ISO88591 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COL_TIMESTAMP LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COL_VALUE VARCHAR(100000) CHARACTER SET ISO88591 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , PRIMARY KEY (ROW_ID ASC) |
| ) |
| ; |
| */ |
| |
| /* HBase DDL */ |
| CREATE HBASE TABLE T022HBM2 ( COLUMN FAMILY '#1') |
| |
| --- SQL operation complete. |
| >>get hbase registered tables in catalog trafodion, match '%T022HBM2%'; |
| |
| --- SQL operation complete. |
| >>register hbase table t022hbm2; |
| |
| --- SQL operation complete. |
| >>get hbase registered tables in catalog trafodion, match '%T022HBM2%'; |
| |
| HBase Registered Tables in Catalog TRAFODION |
| ============================================ |
| |
| "_CELL_".T022HBM2 |
| "_ROW_".T022HBM2 |
| |
| --- SQL operation complete. |
| >>showddl hbase."_CELL_".t022hbm2; |
| |
| /* |
| CREATE TABLE HBASE."_CELL_".T022HBM2 |
| ( |
| ROW_ID VARCHAR(100) CHARACTER SET ISO88591 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COL_FAMILY VARCHAR(100) CHARACTER SET ISO88591 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COL_NAME VARCHAR(100) CHARACTER SET ISO88591 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COL_TIMESTAMP LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COL_VALUE VARCHAR(100000) CHARACTER SET ISO88591 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , PRIMARY KEY (ROW_ID ASC) |
| ) |
| ; |
| */ |
| |
| /* HBase DDL */ |
| CREATE HBASE TABLE T022HBM2 ( COLUMN FAMILY '#1') |
| |
| REGISTER HBASE TABLE T022HBM2; |
| /* ObjectUID = 8642537124144485947 */ |
| |
| --- SQL operation complete. |
| >>showddl hbase."_ROW_".t022hbm2; |
| |
| /* |
| CREATE TABLE HBASE."_ROW_".T022HBM2 |
| ( |
| ROW_ID VARCHAR(100) CHARACTER SET ISO88591 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COLUMN_DETAILS VARCHAR(100000) CHARACTER SET ISO88591 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , PRIMARY KEY (ROW_ID ASC) |
| ) |
| ; |
| */ |
| |
| /* HBase DDL */ |
| CREATE HBASE TABLE T022HBM2 ( COLUMN FAMILY '#1') |
| |
| REGISTER HBASE TABLE T022HBM2; |
| /* ObjectUID = 8642537124144485957 */ |
| |
| --- SQL operation complete. |
| >>unregister hbase table t022hbm2; |
| |
| --- SQL operation complete. |
| >>get hbase registered tables in catalog trafodion, match '%T022HBM2%'; |
| |
| --- SQL operation complete. |
| >>showddl hbase."_CELL_".t022hbm2; |
| |
| /* |
| CREATE TABLE HBASE."_CELL_".T022HBM2 |
| ( |
| ROW_ID VARCHAR(100) CHARACTER SET ISO88591 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COL_FAMILY VARCHAR(100) CHARACTER SET ISO88591 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COL_NAME VARCHAR(100) CHARACTER SET ISO88591 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COL_TIMESTAMP LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COL_VALUE VARCHAR(100000) CHARACTER SET ISO88591 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , PRIMARY KEY (ROW_ID ASC) |
| ) |
| ; |
| */ |
| |
| /* HBase DDL */ |
| CREATE HBASE TABLE T022HBM2 ( COLUMN FAMILY '#1') |
| |
| --- SQL operation complete. |
| >> |
| >>--showstats for table hbase."_CELL_".t022hbm2 on every column; |
| >>update statistics for table hbase."_CELL_".t022hbm2 on every column; |
| |
| --- SQL operation complete. |
| >>showstats for table hbase."_CELL_".t022hbm2 on every column; |
| |
| Histogram data for Table HBASE."_CELL_".T022HBM2 |
| Table ID: 8849702707055529909 |
| |
| Hist ID # Ints Rowcount UEC Colname(s) |
| ========== ====== =========== =========== =========================== |
| 2003317081 1 2 1 ROW_ID |
| 2003317076 2 2 2 COL_FAMILY |
| 2003317071 2 2 2 COL_NAME |
| 2003317066 1 2 1 COL_TIMESTAMP |
| 2003317061 2 2 2 COL_VALUE |
| |
| |
| --- SQL operation complete. |
| >>get hbase registered tables in catalog trafodion, match '%T022HBM2%'; |
| |
| HBase Registered Tables in Catalog TRAFODION |
| ============================================ |
| |
| "_CELL_".T022HBM2 |
| |
| --- SQL operation complete. |
| >>showddl hbase."_CELL_".t022hbm2; |
| |
| /* |
| CREATE TABLE HBASE."_CELL_".T022HBM2 |
| ( |
| ROW_ID VARCHAR(100) CHARACTER SET ISO88591 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COL_FAMILY VARCHAR(100) CHARACTER SET ISO88591 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COL_NAME VARCHAR(100) CHARACTER SET ISO88591 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COL_TIMESTAMP LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COL_VALUE VARCHAR(100000) CHARACTER SET ISO88591 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , PRIMARY KEY (ROW_ID ASC) |
| ) |
| ; |
| */ |
| |
| /* HBase DDL */ |
| CREATE HBASE TABLE T022HBM2 ( COLUMN FAMILY '#1') |
| |
| --- SQL operation complete. |
| >> |
| >>showstats for table hbase."_ROW_".t022hbm2 on every column; |
| |
| Histogram data for Table HBASE."_ROW_".T022HBM2 |
| Table ID: 0 |
| |
| Hist ID # Ints Rowcount UEC Colname(s) |
| ========== ====== =========== =========== =========================== |
| |
| No Histograms exist for the requested columns or groups |
| |
| |
| --- SQL operation complete. |
| >>update statistics for table hbase."_CELL_".t022hbm2 on every column; |
| |
| --- SQL operation complete. |
| >>showstats for table hbase."_CELL_".t022hbm2 on every column; |
| |
| Histogram data for Table HBASE."_CELL_".T022HBM2 |
| Table ID: 8849702707055529909 |
| |
| Hist ID # Ints Rowcount UEC Colname(s) |
| ========== ====== =========== =========== =========================== |
| 2003317080 1 2 1 ROW_ID |
| 2003317077 2 2 2 COL_FAMILY |
| 2003317070 2 2 2 COL_NAME |
| 2003317067 1 2 1 COL_TIMESTAMP |
| 2003317060 2 2 2 COL_VALUE |
| |
| |
| --- SQL operation complete. |
| >>get hbase registered tables in catalog trafodion, match '%T022HBM2%'; |
| |
| HBase Registered Tables in Catalog TRAFODION |
| ============================================ |
| |
| "_CELL_".T022HBM2 |
| |
| --- SQL operation complete. |
| >>showddl hbase."_CELL_".t022hbm2; |
| |
| /* |
| CREATE TABLE HBASE."_CELL_".T022HBM2 |
| ( |
| ROW_ID VARCHAR(100) CHARACTER SET ISO88591 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COL_FAMILY VARCHAR(100) CHARACTER SET ISO88591 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COL_NAME VARCHAR(100) CHARACTER SET ISO88591 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COL_TIMESTAMP LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , COL_VALUE VARCHAR(100000) CHARACTER SET ISO88591 |
| COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , PRIMARY KEY (ROW_ID ASC) |
| ) |
| ; |
| */ |
| |
| /* HBase DDL */ |
| CREATE HBASE TABLE T022HBM2 ( COLUMN FAMILY '#1') |
| |
| --- SQL operation complete. |
| >> |
| >>showstats for table hbase."_MAP_".t022hbm2 on every column; |
| |
| Histogram data for Table HBASE."_MAP_".T022HBM2 |
| Table ID: 8642537124144485432 |
| |
| Hist ID # Ints Rowcount UEC Colname(s) |
| ========== ====== =========== =========== =========================== |
| |
| No Histograms exist for the requested columns or groups |
| |
| |
| --- SQL operation complete. |
| >>update statistics for table hbase."_MAP_".t022hbm2 on every column; |
| |
| --- SQL operation complete. |
| >>showstats for table hbase."_MAP_".t022hbm2 on every column; |
| |
| Histogram data for Table HBASE."_MAP_".T022HBM2 |
| Table ID: 8642537124144485432 |
| |
| Hist ID # Ints Rowcount UEC Colname(s) |
| ========== ====== =========== =========== =========================== |
| 2023711938 1 1 1 A |
| 2023711933 1 1 1 B |
| 2023711928 1 1 1 Z |
| |
| |
| --- SQL operation complete. |
| >>showddl hbase."_MAP_".t022hbm2; |
| |
| CREATE EXTERNAL TABLE T022HBM2 |
| ( |
| "cf1".A INT DEFAULT NULL |
| , "cf2".B INT DEFAULT NULL |
| , "cf1".Z VARCHAR(4) CHARACTER SET ISO88591 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , PRIMARY KEY NOT SERIALIZED (Z ASC) |
| ) |
| MAP TO HBASE TABLE T022HBM2 DATA FORMAT VARCHAR |
| ; |
| |
| --- SQL operation complete. |
| >> |
| >>-- error cases |
| >> |
| >>-- primary key cannot be missing |
| >>select * from t022hbm1; |
| |
| A B |
| ---- ----------- |
| |
| a1 10 |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- operations not allowed |
| >>alter table t022hbm1 alter column "cf".b largeint; |
| |
| *** ERROR[3242] This statement is not supported. Reason: This feature not available for an HBase mapped table. |
| |
| --- SQL operation failed with errors. |
| >>invoke t022hbm1; |
| |
| -- Definition of Trafodion HBase mapped table T022HBM1 |
| -- Definition current Mon May 15 01:31:41 2017 |
| |
| ( |
| "cf".A VARCHAR(4) CHARACTER SET ISO88591 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , "cf".B INT DEFAULT NULL |
| ) |
| PRIMARY KEY NOT SERIALIZED (A ASC) |
| |
| --- SQL operation complete. |
| >> |
| >>set schema trafodion."_HB_MAP_"; |
| |
| --- SQL operation complete. |
| >> |
| >>-- cannot invoke using map schema name |
| >>invoke "_HB_MAP_".t022hbm1; |
| |
| *** ERROR[4261] The schema name _HB_MAP_ is reserved for HBase mapped tables and cannot be explicitly specified. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>-- cannot use "_HB_MAP_" in a table name |
| >>prepare s from select * from t022hbm1; |
| |
| *** ERROR[4261] The schema name _HB_MAP_ is reserved for HBase mapped tables and cannot be explicitly specified. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>prepare s from select * from "_HB_MAP_".t022hbm1; |
| |
| *** ERROR[4261] The schema name _HB_MAP_ is reserved for HBase mapped tables and cannot be explicitly specified. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>drop table "_HB_MAP_".t022hbm1; |
| |
| *** ERROR[4261] The schema name _HB_MAP_ is reserved for HBase mapped tables and cannot be explicitly specified. |
| |
| --- SQL operation failed with errors. |
| >>alter table trafodion."_HB_MAP_".t022hbm1 drop column b; |
| |
| *** ERROR[4261] The schema name _HB_MAP_ is reserved for HBase mapped tables and cannot be explicitly specified. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>set schema trafodion.sch; |
| |
| --- SQL operation complete. |
| >> |
| >>drop external table if exists t022hbm1; |
| |
| --- SQL operation complete. |
| >> |
| >>-- cannot specify serialized option |
| >>create external table t022hbm1 (a varchar(4) not null, b int) |
| +> primary key serialized (a) |
| +> attribute default column family 'cf' |
| +> map to hbase table t022hbm1; |
| |
| *** ERROR[3242] This statement is not supported. Reason: SERIALIZED option cannot be specified for primary key of this table. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>-- cannot be aligned format |
| >>create external table t022hbm1 (a varchar(4) not null primary key) |
| +> attribute aligned format map to hbase table t022hbm1; |
| |
| *** ERROR[3242] This statement is not supported. Reason: Aligned format cannot be specified for an HBase mapped table. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>-- must specify pkey |
| >>create external table t022hbm1 (a char(4)) map to hbase table t022hbm1; |
| |
| *** ERROR[4259] Primary Key clause must be specified for an HBase mapped table. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>-- external and hbase table names must be the same |
| >>create external table t022hbm11 (a char(4) not null primary key) |
| +> map to hbase table t022hbm1; |
| |
| *** ERROR[1180] Trying to create an external HBASE table with a different schema or table name (T022HBM11) than the source table (T022HBM1). The external schema and table name must be the same as the source. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>-- all non-pkey columns must be nullable |
| >>create external table t022hbm1 (a varchar(4) not null primary key, |
| +> b int not null) |
| +> map to hbase table t022hbm1; |
| |
| *** ERROR[3242] This statement is not supported. Reason: Non-key columns of an HBase mapped table must be nullable with default value of NULL. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>-- all non-pkey columns must have default value of null |
| >>create external table t022hbm1 (a varchar(4) not null primary key, |
| +> b int default 10) |
| +> map to hbase table t022hbm1; |
| |
| *** ERROR[3242] This statement is not supported. Reason: Non-key columns of an HBase mapped table must be nullable with default value of NULL. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>-- mapped table already exist |
| >>create external table t022hbm1 (a varchar(4) not null primary key) |
| +> map to hbase table t022hbm1; |
| |
| --- SQL operation complete. |
| >>create external table t022hbm1 (a varchar(4) not null primary key) |
| +> map to hbase table t022hbm1; |
| |
| *** ERROR[1390] Object TRAFODION."_HB_MAP_".T022HBM1 already exists in Trafodion. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>-- hbase table doesn't exist |
| >>create external table t022hbm11 (a char(4) not null primary key) |
| +> map to hbase table t022hbm11; |
| |
| *** ERROR[4260] HBase table(T022HBM11) to be mapped as an external table does not exist. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>-- cannot create view in HB_MAP schema |
| >>create view "_HB_MAP_".v as select * from t022hbm1; |
| |
| *** ERROR[1118] Creating object TRAFODION."_HB_MAP_".V is not allowed in a reserved system schema. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>-- cannot create index on an hbase external table |
| >>create index ti on t022hbm1 (a); |
| |
| *** ERROR[3242] This statement is not supported. Reason: Cannot create index on an HBase mapped table. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>drop external table if exists t022hbm1; |
| |
| --- SQL operation complete. |
| >>drop hbase table t022hbm1; |
| |
| --- SQL operation complete. |
| >>create hbase table t022hbm1 (column family 'cf'); |
| |
| --- SQL operation complete. |
| >>create external table t022hbm1 (a varchar(4) not null, b int) |
| +> primary key (a) |
| +> attribute default column family 'cf' |
| +> map to hbase table t022hbm1; |
| |
| --- SQL operation complete. |
| >>insert into hbase."_ROW_".t022hbm1 values ('a1', |
| +> column_create(('cf:A', '10'))); |
| |
| --- 1 row(s) inserted. |
| >>-- rowID must match pkey col contents |
| >>select * from t022hbm1; |
| |
| *** ERROR[8556] An error occurred while accessing HBase table T022HBM1. Details: HBase rowID content must match the primary key column content. |
| |
| --- 0 row(s) selected. |
| >> |
| >>drop hbase table t022hbm1; |
| |
| --- SQL operation complete. |
| >>create hbase table t022hbm1 (column family 'cf'); |
| |
| --- SQL operation complete. |
| >>insert into hbase."_ROW_".t022hbm1 values ('a1234567', |
| +> column_create(('cf:B', '10'))); |
| |
| --- 1 row(s) inserted. |
| >>-- primary key col length must be big enough to hold rowID |
| >>select * from t022hbm1; |
| |
| *** ERROR[8556] An error occurred while accessing HBase table T022HBM1. Details: retrieved rowID of length 8 is larger than the specified key size of 4. |
| |
| --- 0 row(s) selected. |
| >> |
| >>drop hbase table t022hbm1; |
| |
| --- SQL operation complete. |
| >>create hbase table t022hbm1 (column family 'cf'); |
| |
| --- SQL operation complete. |
| >>insert into hbase."_ROW_".t022hbm1 values ('a1', |
| +> column_create(('cf:B', '1000000'))); |
| |
| --- 1 row(s) inserted. |
| >>-- buffer to retrieve column value must be big enough |
| >>cqd hbase_max_column_val_length '5'; |
| |
| --- SQL operation complete. |
| >>select * from t022hbm1; |
| |
| *** ERROR[8556] An error occurred while accessing HBase table T022HBM1. Details: actual column value length of 7 is greater than the expected max buffer size of 5. |
| |
| --- 0 row(s) selected. |
| >> |
| >>log; |
