phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Heather, James (ELS-LON)" <james.heat...@elsevier.com>
Subject Re: Cloning a table in Phoenix
Date Fri, 09 Sep 2016 07:53:50 GMT
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<mailto: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>  [X] <https://twitter.com/intent/follow?original_referer=https://twitter.com/about/resources/buttons&region=follow_link&screen_name=megamda&source=followbutton&variant=2.0>
 [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<mailto: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<mailto: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