[
https://issues.apache.org/jira/browse/PHOENIX-5543?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16959110#comment-16959110
]
Bharath Vissapragada edited comment on PHOENIX-5543 at 10/24/19 6:02 PM:
-------------------------------------------------------------------------
[~yanxinyi] Both "show tables" and "!tables" take the same code path. Please
look at the patch [1] for more details. In fact {{!tables}} does a syscat scan
too. Here is the prepared statement that is generated when you type in
{{!tables}}
{noformat}
SELECT tenant_id TABLE_CAT,
table_schem,
table_name,
Sqltabletype(table_type) AS TABLE_TYPE,
remarks,
type_name,
self_referencing_col_name,
ref_generation,
Indexstatename(index_state) AS INDEX_STATE,
immutable_rows,
salt_buckets,
multi_tenant,
view_statement,
Sqlviewtype(view_type) AS VIEW_TYPE,
Sqlindextype(index_type) AS INDEX_TYPE,
transaction_provider IS NOT NULL AS TRANSACTIONAL,
is_namespace_mapped,
guide_posts_width,
Transactionprovidername(transaction_provider) AS TRANSACTION_PROVIDER
FROM system."catalog" "SYSTEM.TABLE"
WHERE column_name IS NULL
AND column_family IS NULL
AND table_name != ' '
AND tenant_id LIKE ?
AND tenant_id = ?
AND table_schem IS NULL
AND table_name LIKE ?
ORDER BY 4,
1,
2,
3
{noformat}
With "show tables" you can add more predicates to the above query using {{IN
<schema> LIKE <pattern>}} making it even more efficient.
[1] https://github.com/apache/phoenix/pull/606/commits
was (Author: bharathv):
[~yanxinyi] Both "show tables" and "!tables" take the same code path. Please
look at the patch [1] for more details. In fact {{!tables}} does a syscat scan
too. Here is the prepared statement that is generated when you type in
{{!tables}}
{noformat}
select
TENANT_ID TABLE_CAT,TABLE_SCHEM,TABLE_NAME ,SQLTableType(TABLE_TYPE) AS
TABLE_TYPE,REMARKS
,TYPE_NAME,SELF_REFERENCING_COL_NAME,REF_GENERATION,IndexStateName(INDEX_STATE)
AS
INDEX_STATE,IMMUTABLE_ROWS,SALT_BUCKETS,MULTI_TENANT,VIEW_STATEMENT,SQLViewType(VIEW_TYPE)
AS VIEW_TYPE,SQLIndexType(INDEX_TYPE) AS INDEX_TYPE,TRANSACTION_PROVIDER IS
NOT NULL AS
TRANSACTIONAL,IS_NAMESPACE_MAPPED,GUIDE_POSTS_WIDTH,TransactionProviderName(TRANSACTION_PROVIDER)
AS TRANSACTION_PROVIDER from SYSTEM."CATALOG" "SYSTEM.TABLE" where COLUMN_NAME
is null and COLUMN_FAMILY is null and TABLE_NAME != ' ' and TENANT_ID LIKE ?
and TENANT_ID = ? and TABLE_SCHEM is null and TABLE_NAME like ? order by 4, 1,
2, 3
{noformat}
With "show tables" you can add more predicates to the above query using {{IN
<schema> LIKE <pattern>}} making it even more efficient.
[1] https://github.com/apache/phoenix/pull/606/commits
> Implement show schemas / show tables SQL commands
> -------------------------------------------------
>
> Key: PHOENIX-5543
> URL: https://issues.apache.org/jira/browse/PHOENIX-5543
> Project: Phoenix
> Issue Type: Improvement
> Affects Versions: 4.15.0, 5.1.0
> Reporter: Bharath Vissapragada
> Priority: Minor
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Currently users rely on {{!tables}} and {{!schemas}} commands provided by
> sqlline which pulls the information using the standard JDBC metadata calls
> like {{getTables()}} and {{getSchemas()}}.
> Most other databases (like mysql[1,2]) implement these as first class SQL
> commands that gives the user more flexibility in querying by adding necessary
> filters and looking up for table information in specific schemas. The ask
> here is to implement the following SQL commands.
> {noformat}
> SHOW SCHEMAS [LIKE '<pattern>']
> SHOW TABLES [IN <schema>] [LIKE '<pattern>']
> {noformat}
> [1] https://dev.mysql.com/doc/refman/8.0/en/show-tables.html
> [2] https://dev.mysql.com/doc/refman/8.0/en/show-databases.html
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
