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. >
