List tables with columns in bulk

[densypko]

Is there a way to get the table columns in bulk? For example connection.list_tables(with_columns=True). Because when I want to do a scan of a database/schema it is slow.

Postgres:

        base_sql_get_columns = f"SELECT cols.table_name, cols.column_name, cols.data_type, pg_catalog.col_description(c.oid, cols.ordinal_position::int) as column_description " \
                               f"FROM pg_catalog.pg_class c, information_schema.columns as cols WHERE cols.table_name = c.relname AND table_name"

        if isinstance(table_names, list):
            query_table_names = ','.join([f"'{value}'" for value in table_names])
            sql_get_columns = f"{base_sql_get_columns} IN ({query_table_names})"
        else:
            sql_get_columns = f"{base_sql_get_columns} = '{table_names}'"

        sql_get_columns = f"{sql_get_columns} AND table_catalog='{self.datasource.database}';"

        columns_indexed_by_table_name = {}
        cursor = self.execute_query(sql_get_columns)

[cpcloud]

Hi @densypko 👋🏻!

Thanks for opening a discussion about this.

We don't currently have an API to get schema information for every existing table with a single query.

If we were to add that, it'd probably be a separate API (perhaps column_metadata()?) to avoid having APIs with input arguments that change the return type of the call.

Additionally, its implementation would have to be optimized to the single-call version on a backend-specific basis.

Can you describe what you want to do with this information and/or your use for such an API?

[densypko]

We are currently developing an application that serves as a hub for various data sources, allowing users to effortlessly scan database/schema metadata, run queries, and more.

We support a few clients like Postgres and Clickhouse, but wanted to add more using your project.

For the query execution part it is great, but we have noticed some slowness in the metadata scanning process.

Thanks for your quick response.

[cpcloud]

Ah, nice.

By any chance can you list which ibis API calls were not performing well for you?

Perhaps there are some hotspots that we need to address that would obviate the need for a new API!