phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From James Taylor <jamestay...@apache.org>
Subject Re: Cloning a table in Phoenix
Date Fri, 09 Sep 2016 17:27:10 GMT
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