phoenix元数据查询命令

1.1 phoenix的连接

1. Maven中添加jar包

 <dependency>
            <groupId>org.apache.phoenix</groupId>
            <artifactId>phoenix-core</artifactId>
            <version>4.13.2-cdh5.11.2</version>
        </dependency>

2. 添加hbase-site.xml文档

<?xml version="1.0" encoding="UTF-8"?>

<!--Autogenerated by Cloudera Manager-->
<configuration>
  <property> 
    <name>phoenix.schema.isNamespaceMappingEnabled</name>
    <value>true</value>
  </property>
</configuration>

3. 配置连接信息

   phoenix:
       datasource:
         url: 'jdbc:phoenix:DB-test4,DB-test5,DB-test6:2181'
         driverClassName: org.apache.phoenix.jdbc.PhoenixDriver
         maxActive: 100
         initialSize: 1
         maxWait: 60000
         minIdle: 1
         testWhileIdle: false
         defaultAutoCommit: true
   

4. 获取JdbcTemplate

   @Bean(name = "phoenixJdbcTemplate")
       public JdbcTemplate phoenixJdbcTemplate(@Qualifier("phoenixJdbcDataSource") DataSource dataSource) {
           return new JdbcTemplate(dataSource);
       }
   

1.2 获取模式

select TABLE_SCHEM from SYSTEM.catalog where  table_name = ' '

1.3 获取表名

获取SCHEMA下的所有表，table_type没找到文档，看数据u应该是表，i是索引，v是视图

select table_schem,table_name from SYSTEM.catalog where table_type = 'u' and table_schem = 'schema'

1.4 获取表描述

1.5 获取表字段

select 'hbase' AS data_Source_type,table_name,column_name,column_family,column_size AS col_len, nullable as is_nullable,ordinal_position,key_seq AS primary_key,
            CASE data_type WHEN -5 THEN 'BIGINT' WHEN 4 THEN 'INTEGER' ELSE 'VARCHAR' END AS data_type 
            from SYSTEM.catalog where table_schem = 'BMBI_TEST' and table_name='DEMO_USER' and  data_type is not null  order by ordinal_position 

1.6 获取视图

获取SCHEMA下的所有视图，table_type没找到文档，看数据u应该是表，i是索引，v是视图

select table_schem,table_name from SYSTEM.catalog where table_type = 'v' and table_schem = 'SCHEMA'

1.7 获取视图列

select 'hbase' AS data_Source_type,table_name,column_name,column_family,column_size AS col_len, nullable as is_nullable,ordinal_position,key_seq AS primary_key,
            CASE data_type WHEN -5 THEN 'BIGINT' WHEN 4 THEN 'INTEGER' ELSE 'VARCHAR' END AS data_type 
            from SYSTEM.catalog where table_schem = 'crawl' and table_name='fact_moon_order_tude_par1_test2' and  data_type is not null  order by ordinal_position 

1.8 获取所有表的主键

select column_name,KEY_SEQ from SYSTEM.catalog where table_schem = '%s' AND table_name = '%s' AND KEY_SEQ IS NOT NULL ORDER BY KEY_SEQ

1.9 获取索引

select column_family as index_name from SYSTEM.catalog where table_schem = '%s' and  table_name = '%s' and table_type = 'i'

1.10创建索引

CREATE INDEX \"%s\" ON  \"%s\".\"%s\" ";

1.11删除索引

DROP INDEX \"%s\".\"%s\";\n";

1.12创建主键

DROP TABLE IF EXISTS \"%s\".%s";

参考文档

phoenix官网