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 > <[email protected]> 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 <[email protected]> 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 >>> <[email protected]> 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 <[email protected]> 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 >>>>> <[email protected]> wrote: >>>>> What is an easy solution or is there a solution to clone the table/schema >>>>> in phoenix? >>>>> >>>>> Thanks in advance. >>>>> >>>>> >>>> >>
