phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jonathan Leech <jonat...@gmail.com>
Subject Re: Cloning a table in Phoenix
Date Fri, 09 Sep 2016 04:41:50 GMT
I think you're best off running DDL with a new table name, but you could probably upsert the
values yourself into system.catalog. If you have a lot of data to copy, you can use hbase
snapshots and restore into the new table name. This would also take care of creating the underlying
hbase table, index table if applicable etc. Things to double check are the value in the system.sequence
table for the local indexes, and the corresponding values in system.catalog for local indexes.
These need to match the rows for the original table if you copy the index data via hbase snapshots,
CopyTable, etc. Otherwise queries using the index could return bad results until it is rebuilt.
This also applies to hbase replication of indexes of the same Phoenix table between environments.

- Jonathan

> On Sep 8, 2016, at 8:12 PM, Kumar Palaniappan <kpalaniappan@marinsoftware.com>
wrote:
> 
> Yes, we found a way to do off of system.catalog
> 
> In the meantime, trying to to explore are there any off the
> shelves options.
> 
> Thanks dalin.
> 
> Kumar Palaniappan   
> 
>> On Sep 8, 2016, at 6:43 PM, dalin.qin <dalinqin@gmail.com> wrote:
>> 
>> Hi Kumar,
>> 
>> I believe right now there is no way to directly generate the DDL statement for the
existing table,better to write down you sql immedately after exection  (in oracle ,dbms_metadata
is so perfect ,in hive show create table also works )
>> however you can query system.catalog for all the information you need .
>> 
>> +------------+--------------+-------------+----------------------------+------------+-------------------+--------------+----------------+-----------------+-----------------+-----------+----------+-------------+----------------+---------+
>> | TABLE_CAT  | TABLE_SCHEM  | TABLE_NAME  |        COLUMN_NAME         | DATA_TYPE
 |     TYPE_NAME     | COLUMN_SIZE  | BUFFER_LENGTH  | DECIMAL_DIGITS  | NUM_PREC_RADIX  |
NULLABLE  | REMARKS  | COLUMN_DEF  | SQL_DATA_TYPE  | SQL_DAT |
>> +------------+--------------+-------------+----------------------------+------------+-------------------+--------------+----------------+-----------------+-----------------+-----------+----------+-------------+----------------+---------+
>> |            | SYSTEM       | CATALOG     | TENANT_ID                  | 12     
   | VARCHAR           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | TABLE_SCHEM                | 12     
   | VARCHAR           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | TABLE_NAME                 | 12     
   | VARCHAR           | null         | null           | null            | null          
 | 0         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | COLUMN_NAME                | 12     
   | VARCHAR           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | COLUMN_FAMILY              | 12     
   | VARCHAR           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | TABLE_SEQ_NUM              | -5     
   | BIGINT            | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | TABLE_TYPE                 | 1      
   | CHAR              | 1            | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | PK_NAME                    | 12     
   | VARCHAR           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | COLUMN_COUNT               | 4      
   | INTEGER           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | SALT_BUCKETS               | 4      
   | INTEGER           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | DATA_TABLE_NAME            | 12     
   | VARCHAR           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | INDEX_STATE                | 1      
   | CHAR              | 1            | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | IMMUTABLE_ROWS             | 16     
   | BOOLEAN           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | VIEW_STATEMENT             | 12     
   | VARCHAR           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | DEFAULT_COLUMN_FAMILY      | 12     
   | VARCHAR           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | DISABLE_WAL                | 16     
   | BOOLEAN           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | MULTI_TENANT               | 16     
   | BOOLEAN           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | VIEW_TYPE                  | -6     
   | UNSIGNED_TINYINT  | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | VIEW_INDEX_ID              | 5      
   | SMALLINT          | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | DATA_TYPE                  | 4      
   | INTEGER           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | COLUMN_SIZE                | 4      
   | INTEGER           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | DECIMAL_DIGITS             | 4      
   | INTEGER           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | NULLABLE                   | 4      
   | INTEGER           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | ORDINAL_POSITION           | 4      
   | INTEGER           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | SORT_ORDER                 | 4      
   | INTEGER           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | ARRAY_SIZE                 | 4      
   | INTEGER           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | VIEW_CONSTANT              | -3     
   | VARBINARY         | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | IS_VIEW_REFERENCED         | 16     
   | BOOLEAN           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | KEY_SEQ                    | 5      
   | SMALLINT          | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | LINK_TYPE                  | -6     
   | UNSIGNED_TINYINT  | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | TYPE_NAME                  | 12     
   | VARCHAR           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | REMARKS                    | 12     
   | VARCHAR           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | SELF_REFERENCING_COL_NAME  | 12     
   | VARCHAR           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | REF_GENERATION             | 12     
   | VARCHAR           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | BUFFER_LENGTH              | 4      
   | INTEGER           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | NUM_PREC_RADIX             | 4      
   | INTEGER           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | COLUMN_DEF                 | 12     
   | VARCHAR           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | SQL_DATA_TYPE              | 4      
   | INTEGER           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | SQL_DATETIME_SUB           | 4      
   | INTEGER           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | CHAR_OCTET_LENGTH          | 4      
   | INTEGER           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | IS_NULLABLE                | 12     
   | VARCHAR           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | SCOPE_CATALOG              | 12     
   | VARCHAR           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | SCOPE_SCHEMA               | 12     
   | VARCHAR           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | SCOPE_TABLE                | 12     
   | VARCHAR           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | SOURCE_DATA_TYPE           | 5      
   | SMALLINT          | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | IS_AUTOINCREMENT           | 12     
   | VARCHAR           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | INDEX_TYPE                 | -6     
   | UNSIGNED_TINYINT  | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | INDEX_DISABLE_TIMESTAMP    | -5     
   | BIGINT            | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | STORE_NULLS                | 16     
   | BOOLEAN           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | BASE_COLUMN_COUNT          | 4      
   | INTEGER           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | IS_ROW_TIMESTAMP           | 16     
   | BOOLEAN           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | TRANSACTIONAL              | 16     
   | BOOLEAN           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | UPDATE_CACHE_FREQUENCY     | -5     
   | BIGINT            | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | IS_NAMESPACE_MAPPED        | 16     
   | BOOLEAN           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | AUTO_PARTITION_SEQ         | 12     
   | VARCHAR           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> |            | SYSTEM       | CATALOG     | APPEND_ONLY_SCHEMA         | 16     
   | BOOLEAN           | null         | null           | null            | null          
 | 1         |          |             | null           | null    |
>> +------------+--------------+-------------+----------------------------+------------+-------------------+--------------+----------------+-----------------+-----------------+-----------+----------+-------------+----------------+---------+
>> 
>> 
>>> On Thu, Sep 8, 2016 at 8:57 PM, Kumar Palaniappan <kpalaniappan@marinsoftware.com>
wrote:
>>> It's not about data. Would like to clone just the table structure(s) under the
schema partially or entire tables.
>>> 
>>> 
>>> Kumar Palaniappan   
>>> 
>>>> On Sep 8, 2016, at 5:48 PM, dalin.qin <dalinqin@gmail.com> wrote:
>>>> 
>>>> try this:
>>>> 
>>>> 0: jdbc:phoenix:namenode:2181:/hbase-unsecure> CREATE TABLE TABLE1 (ID
BIGINT NOT NULL PRIMARY KEY, COL1 VARCHAR);
>>>> No rows affected (1.287 seconds)
>>>> 0: jdbc:phoenix:namenode:2181:/hbase-unsecure> UPSERT INTO TABLE1 (ID,
COL1) VALUES (1, 'test_row_1');
>>>> 1 row affected (0.105 seconds)
>>>> 0: jdbc:phoenix:namenode:2181:/hbase-unsecure> UPSERT INTO TABLE1 (ID,
COL1) VALUES (2, 'test_row_2');
>>>> 1 row affected (0.011 seconds)
>>>> 0: jdbc:phoenix:namenode:2181:/hbase-unsecure>  CREATE TABLE TABLE2 (ID
BIGINT NOT NULL PRIMARY KEY, COL1 VARCHAR);
>>>> No rows affected (1.251 seconds)
>>>> 0: jdbc:phoenix:namenode:2181:/hbase-unsecure> upsert into table2 select
* from table1;
>>>> 2 rows affected (0.049 seconds)
>>>> 0: jdbc:phoenix:namenode:2181:/hbase-unsecure> select * from table2;
>>>> +-----+-------------+
>>>> | ID  |    COL1     |
>>>> +-----+-------------+
>>>> | 1   | test_row_1  |
>>>> | 2   | test_row_2  |
>>>> +-----+-------------+
>>>> 2 rows selected (0.06 seconds)
>>>> 
>>>> 
>>>>> On Thu, Sep 8, 2016 at 4:17 PM, Kumar Palaniappan <kpalaniappan@marinsoftware.com>
wrote:
>>>>> What is an easy solution or is there a solution to clone the table/schema
in phoenix?
>>>>> 
>>>>> Thanks in advance.
>>>>> 
>>>>> 
>>>> 
>> 

Mime
View raw message