Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

CREATE EXTERNAL TABLE does not support schema (fully qualified names) #12607

Open
alamb opened this issue Sep 24, 2024 · 1 comment · May be fixed by #12645
Open

CREATE EXTERNAL TABLE does not support schema (fully qualified names) #12607

alamb opened this issue Sep 24, 2024 · 1 comment · May be fixed by #12645
Assignees
@OussamaSaoudi
Labels
bug Something isn't working

Comments

@alamb
Copy link
Contributor

alamb commented Sep 24, 2024 •
edited
Loading

Describe the bug

DataFusion will create external tables with qualified names as though they were single names

For example, this statement will create a table name "staging.foo" in the default schema, rather than a table named foo in the staging schema

create external table staging.foo stored as csv location 'foo.csv';

To Reproduce

Create foo.csv:

echo "x" > foo.csv
echo "1" >> foo.csv

Run SQL:

DataFusion CLI v42.0.0
> create schema staging;
0 row(s) fetched.
Elapsed 0.001 seconds.

> create external table staging.foo stored as csv location 'foo.csv';
0 row(s) fetched.
Elapsed 0.011 seconds.

> select * from staging.foo;
Error during planning: table 'datafusion.staging.foo' not found
> select * from "staging.foo";
+---+
| x |
+---+
| 1 |
+---+
1 row(s) fetched.
Elapsed 0.005 seconds.

Note the table is in the default (public) schema:

> show tables;
+---------------+--------------------+-------------+------------+
| table_catalog | table_schema       | table_name  | table_type |
+---------------+--------------------+-------------+------------+
| datafusion    | public             | staging.foo | BASE TABLE |
| datafusion    | information_schema | tables      | VIEW       |
| datafusion    | information_schema | views       | VIEW       |
| datafusion    | information_schema | columns     | VIEW       |
| datafusion    | information_schema | df_settings | VIEW       |
| datafusion    | information_schema | schemata    | VIEW       |
+---------------+--------------------+-------------+------------+
6 row(s) fetched.
Elapsed 0.002 seconds.

Expected behavior

The external table should be named foo in the staging schema

Note this works as expected for CREATE TABLE (just not CREATE EXTERNAL TABLE)

> create table staging.bar as values (2);
0 row(s) fetched.
Elapsed 0.006 seconds.

> select * from staging.bar;
+---------+
| column1 |
+---------+
| 2       |
+---------+
1 row(s) fetched.
Elapsed 0.001 seconds.

> show tables;
+---------------+--------------------+-------------+------------+
| table_catalog | table_schema       | table_name  | table_type |
+---------------+--------------------+-------------+------------+
| datafusion    | public             | staging.foo | BASE TABLE |
| datafusion    | staging            | bar         | BASE TABLE |
| datafusion    | information_schema | tables      | VIEW       |
| datafusion    | information_schema | views       | VIEW       |
| datafusion    | information_schema | columns     | VIEW       |
| datafusion    | information_schema | df_settings | VIEW       |
| datafusion    | information_schema | schemata    | VIEW       |
+---------------+--------------------+-------------+------------+
7 row(s) fetched.
Elapsed 0.004 seconds.

Additional context

Found by @matthewmturner in datafusion-contrib/datafusion-dft#154 (comment)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@OussamaSaoudi OussamaSaoudi

Labels
bug Something isn't working
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

[WIP] Add support for external tables with qualified names OussamaSaoudi/datafusion
2 participants
@alamb @OussamaSaoudi