Support DESCRIBE <table> to show table schemas #2606
Description
Is your feature request related to a problem or challenge? Please describe what you are trying to do.
DataFusion mostly follows postgres when it comes to compatibility. However, it also supports some usability features from mysql, notably SHOW TABLES;
It would be nice to also get basic schema information (column names, types) about a table using DESCRIBE <TABLE>
Describe the solution you'd like
I would like to run this sequence of commands and get a nicely formatted output other than an error:
❯ create table foo as select * from (values (1), (3), (2), (10), (8));
+---------+
| column1 |
+---------+
| 1 |
| 3 |
| 2 |
| 10 |
| 8 |
+---------+
5 rows in set. Query took 0.005 seconds.
❯ show tables;
+---------------+--------------------+------------+------------+
| table_catalog | table_schema | table_name | table_type |
+---------------+--------------------+------------+------------+
| datafusion | public | foo | BASE TABLE |
| datafusion | information_schema | tables | VIEW |
| datafusion | information_schema | columns | VIEW |
+---------------+--------------------+------------+------------+
3 rows in set. Query took 0.001 seconds.
❯ describe foo;
NotImplemented("Unsupported SQL statement: ExplainTable { describe_alias: true, table_name: ObjectName([Ident { value: \"foo\", quote_style: None }]) }")Here is what mysql does (the table definition is different than the example above):
mysql> describe foo;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| bar | set('a','b') | YES | | NULL | |
| baz | enum('a','b') | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
2 rows in set (0.00 sec)Note that the information is accesable via information_schema so this ticket would just plumbing it all together:
❯ select * from information_schema.columns;
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+-------------------------+---------------+--------------------+---------------+
| table_catalog | table_schema | table_name | column_name | ordinal_position | column_default | is_nullable | data_type | character_maximum_length | character_octet_length | numeric_precision | numeric_precision_radix | numeric_scale | datetime_precision | interval_type |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+-------------------------+---------------+--------------------+---------------+
| datafusion | public | foo | column1 | 0 | | YES | Int64 | | | | | | | |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+-------------------------+---------------+--------------------+---------------+
1 row in set. Query took 0.004 seconds.Describe alternatives you've considered
A clear and concise description of any alternative solutions or features you've considered.
Additional context
Add any other context or screenshots about the feature request here.