phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "dalin.qin" <dalin...@gmail.com>
Subject Re: Cloning a table in Phoenix
Date Fri, 09 Sep 2016 01:43:34 GMT
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 <http://about.me/kumar.palaniappan>
> <https://twitter.com/intent/follow?original_referer=https://twitter.com/about/resources/buttons&region=follow_link&screen_name=megamda&source=followbutton&variant=2.0>
>  [image: Description: Macintosh HD:Users:Kumarappan:Desktop:linkedin.gif]
> <http://www.linkedin.com/in/kumarpalaniappan>
>
> 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