phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kumar Palaniappan <kpalaniap...@marinsoftware.com>
Subject Re: Cloning a table in Phoenix
Date Fri, 09 Sep 2016 02:12:57 GMT
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