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 17:30:28 GMT
Will do James.

On Fri, Sep 9, 2016 at 10:27 AM, James Taylor <jamestaylor@apache.org>
wrote:

> Good idea - this would make a great contribution. Please file a JIRA.
>
> On Fri, Sep 9, 2016 at 6:29 AM, Kumar Palaniappan <
> kpalaniappan@marinsoftware.com> wrote:
>
>> Yes James.
>>
>> 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 9, 2016, at 12:53 AM, Heather, James (ELS-LON) <
>> james.heather@elsevier.com> wrote:
>>
>> This does rather suggest that it would be fairly easy to implement a SHOW
>> CREATE TABLE statement. Is that right?
>>
>> It would be useful if so.
>>
>> James
>>
>> On 9 September 2016 2:43:51 a.m. "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 <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.
>>>>>
>>>>>
>>>>>
>>>>
>>>
>> ------------------------------
>>
>> Elsevier Limited. Registered Office: The Boulevard, Langford Lane,
>> Kidlington, Oxford, OX5 1GB, United Kingdom, Registration No. 1982084,
>> Registered in England and Wales.
>>
>>
>

Mime
View raw message