HBase: Create table with same schema as existing table

Question

I tried searching on the forum, where I can create a new empty hbase table from existing hbase table schema, but not able to find.

To be more precise, suppose I have a table with multiple column families and many column qualifier within those families.

Now I have to create another empty table with the same schema. Do we any way to create table like we do in RDBMS.

Create table new_table as
select * from existing_table where 1=2;

The existing table has a complex structure, so normal hbase create table command with column family and column qualifier specified is not an option.

FYI.. I am using Mapr HBase 0.98.12-mapr-1506 and I do not have option to switch to any advance version or another distribution.

score 20 · Accepted Answer · answered Apr 07 '16 at 06:32

Here is the full-proof approach, I used. Hope it will help other people.

1) launch HBase shell vial below command

 hbase shell

2) Query existing table metadata with below command

 hbase> describe ‘existing_table’;

Output would be similar to:

{NAME => 'cf1', BLOOMFILTER => 'ROW', VERSIONS => '1', IN_MEMORY => 'false', KEEP_DELETED_CELLS => 'FALSE', DATA_BLOCK_ENCODING 
=> 'NONE', TTL => 'FOREVER', COMPRESSION => 'NONE', MIN_VERSIONS => '0', BLOCKCACHE => 'true', BLOCKSIZE => '65536', REPLICATION
_SCOPE => '0'}                                                                                                                  
{NAME => 'cf2', BLOOMFILTER => 'ROW', VERSIONS => '1', IN_MEMORY => 'false', KEEP_DELETED_CELLS => 'FALSE', DATA_BLOCK_ENCODING 
=> 'NONE', TTL => 'FOREVER', COMPRESSION => 'NONE', MIN_VERSIONS => '0', BLOCKCACHE => 'true', BLOCKSIZE => '65536', REPLICATION
_SCOPE => '0'}

3) Copy this output to a notepad and do below changes:

  a) Replace  TTL => 'FOREVER' with TTL => org.apache.hadoop.hbase.HConstants::FOREVER
  b) Put an additional comma (,) between each column family description to connect column family definition.
  c) Remove newline characters (\n, \r) for the text; such that the content become one line text.

4) Finally running the create query with new table name:

create ‘copy_of_exsting_table_schema’, {NAME => 'cf1', BLOOMFILTER => 'ROW', VERSIONS => '1', IN_MEMORY => 'false', KEEP_DELETED_CELLS => 'FALSE', DATA_BLOCK_ENCODING 
=> 'NONE', TTL => org.apache.hadoop.hbase.HConstants::FOREVER, COMPRESSION => 'NONE', MIN_VERSIONS => '0', BLOCKCACHE => 'true', BLOCKSIZE => '65536', REPLICATION
_SCOPE => '0'} ,{NAME => 'cf2', BLOOMFILTER => 'ROW', VERSIONS => '1', IN_MEMORY => 'false', KEEP_DELETED_CELLS => 'FALSE', DATA_BLOCK_ENCODING 
=> 'NONE', TTL => org.apache.hadoop.hbase.HConstants::FOREVER, COMPRESSION => 'NONE', MIN_VERSIONS => '0', BLOCKCACHE => 'true', BLOCKSIZE => '65536', REPLICATION
_SCOPE => '0'}

And you are done. The new table schema is exactly same as existing table.

This will help to create the new schema with existing schema, how we will copy the data with existing table and schema also — Shyam Gupta, Feb 27 '17 at 13:14

halil · Answer 2 · 2016-02-24T13:14:12.177

11

you can use snapshot feature to do this. like this;

hbase> snapshot 'tableName', 'tableSnapshot'
hbase> clone_snapshot 'tableSnapshot', 'newTableName'
hbase> delete_snapshot 'tableSnapshot'
hbase> truncate 'newTableName'

i hope your table is not huge. And you can not copy column qualifiers with empty values, if i did not understand wrong, you mean this in your question. you can either copy all data to new table or only table structure with column families, coprocessors.. etc.

edited Feb 24 '16 at 13:14

answered Feb 24 '16 at 09:18

halil

1,789
15
18

Thanks for response, just wanted to understand, what `disable 'tableName'` would do. I do not have option to make the original table offline (can't stop read/write from other program). I just need schema, not interested into data to be copied. Can we just make a new schema copy using snapshot feature? – Gyanendra Dwivedi Feb 24 '16 at 10:21
Sorry, i edited my answer. you don't need to disable table to run snapshot command. – halil Feb 24 '16 at 13:14
This is supported by reading the snapshot section of the HBase book: https://hbase.apache.org/book.html#ops.snapshots – David Sep 30 '19 at 21:32
it is 4 years old question and showing a workaround not giving exact code to do what he is asking. – halil Jun 22 '20 at 08:38

HBase: Create table with same schema as existing table

2 Answers2