!tables for a specific schema?

julianhyde / sqlline

Shell for issuing SQL to relational databases via JDBC

BSD 3-Clause "New" or "Revised" License

618 stars 146 forks source link

!tables for a specific schema? #452

Closed matteoredaelli closed 2 years ago

matteoredaelli commented 2 years ago

Hello

First of all I like sqlline ;-) and I'd like to use it more and more instead of dbeaver with Emacs & sql-sqlline package.

I can connect to Amazon Athena: glue/athena databases are mapped as "schemas" in sqlline. I can retreive their list with !schemas

When using db users with limited permissions (users that can read only tables inside a specific aws glue db) I cannot extract tables

!tables jdbc:awsathena://AwsRegion=eu-west-1> !tables Error: [Simba]AthenaJDBC An error has been thrown from the AWS Glue client. User: arn:aws:iam::XXX:user/app_XXX is not authorized to perform: glue:GetTables on resource: arn:aws:glue:eu-west-1:XXX:database/db1 (state=HY000,code=100141)

How could I extract only tables for a specific schema?

I tried with !tables db1.% but I get the same error: sqlline still tries to retreive tables from all schemas

Thanks Matteo

snuyanzin commented 2 years ago

In fact sqlline does not retrieve all, it sends "filter" options to the driver. Could you try to call java.sql.DatabaseMetaData#getTables method from jdbc driver? it could be done via !metadata command like !metadata <catalog_name> <schema_name_pattern> <table_name_pattern> <types> I do not know your catalog_name but probably something like that !metadata % db1% % %

P.S. glad that you like sqlline

matteoredaelli commented 2 years ago

Yes, it work

!metadata getTables AwsDataCatalog db1 ttm% %

Thanks Matteo

snuyanzin commented 2 years ago

!tables under the hood also invokes !metadata getTables however it uses current catalog. I don't know if there is a way in Athena to setup a current catalog. If yes then !tables command also should work after that

matteoredaelli commented 2 years ago

The command !metadata getTables AwsDataCatalog dbX ttm% %
works correctly

when using db2 I get the list of tables because the dbuser is enabled to retreive tablenames from db2 [Ok, expected result]
when using db1 I get an error because the user is not enabled to retreive tablenames from db1 . [Ok, expected result]

!tables fails because it tries to retreive all tables from ALL schemas (but the user is not enabled to get ALL tablenames from all db/schemas)

With "!tables" I can add a filter but it is related to the table name and not the db/schema. Is it possibile to filter the schema with the command "!tables" ?