You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "adragomir (via GitHub)" <gi...@apache.org> on 2024/07/31 11:48:40 UTC
[I] Add the ability to create tables with deeply nested schemas in SQL [datafusion]
adragomir opened a new issue, #11746:
URL: https://github.com/apache/datafusion/issues/11746
### Is your feature request related to a problem or challenge?
At the moment, we cannot create tables with deeply nested columns, for example a column that has a map of string to list of structs etc.
We should have the ability to create deeply nested schemas in SQL
### Describe the solution you'd like
[DuckDB](https://duckdb.org/) has in its SQL DML dialect some markers / functions that allow this:
```
CREATE OR REPLACE TABLE test1 (
struct1 STRUCT(
name VARCHAR,
bools BOOLEAN,
uint32 UINTEGER,
-- a list of strings
tags VARCHAR[]
),
-- a list of structs
list_struct STRUCT(
bools BOOLEAN,
uint32 UINTEGER,
-- a list of ints
ints32 INTEGER[]
)[],
struct_list STRUCT(
bools BOOLEAN,
uint32 UINTEGER,
-- a list of structs
products STRUCT(
qty INT4,
name VARCHAR
)[]
),
-- map of string to map
map_map_struct MAP(
VARCHAR,
-- map of string to struct
MAP(
VARCHAR,
STRUCT(
status VARCHAR,
changed INTEGER
)
)
)
);
```
We should have either syntax or functions that we could use to have the same result
### Describe alternatives you've considered
_No response_
### Additional context
_No response_
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Add the ability to create tables with deeply nested schemas in SQL [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #11746:
URL: https://github.com/apache/datafusion/issues/11746#issuecomment-2656489040
I think this is woring wekk now
```
> create table t as values
(
[
{'name': 'n1', 'info': {'color': 'r', 'size': 1}},
{'name': 'n2', 'info': {'color': 'b', 'size': 2}}
]
);
0 row(s) fetched.
Elapsed 0.002 seconds.
> select * from t;
+--------------------------------------------------------------------------------+
| column1 |
+--------------------------------------------------------------------------------+
| [{name: n1, info: {color: r, size: 1}}, {name: n2, info: {color: b, size: 2}}] |
+--------------------------------------------------------------------------------+
1 row(s) fetched.
Elapsed 0.001 seconds.
```
```sql
> create table t (s struct<name varchar, info struct<color varchar, size int>>[]);
0 row(s) fetched.
Elapsed 0.001 seconds.
> select * from t;
+---+
| s |
+---+
+---+
0 row(s) fetched.
Elapsed 0.001 seconds.
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Add the ability to create tables with deeply nested schemas in SQL [datafusion]
Posted by "jayzhan211 (via GitHub)" <gi...@apache.org>.
jayzhan211 commented on issue #11746:
URL: https://github.com/apache/datafusion/issues/11746#issuecomment-2277289669
```
statement error DataFusion error: This feature is not implemented: Unsupported CAST from List\(Field \{ name: "item", data_type: Struct\(\[Field \{ name: "name", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: \{\} \}, Field \{ name: "info", data_type: Struct\(\[Field \{ name: "color", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: \{\} \}, Field \{ name: "size", data_type: Int64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: \{\} \}\]\), nullable: true, dict_id: 0, dict_is_ordered: false, metadata: \{\} \}\]\), nullable: true, dict_id: 0, dict_is_ordered: false, metadata: \{\} \}\) to Struct\(\[Field \{ name: "name", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: \{\} \}, Field \{ name: "info", data_type: List\(Field \{ name: "item", data_type: Struct\(\[Field \{ name: "color", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: \{\} \}, Field
\{ name: "size", data_type: Int32, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: \{\} \}\]\), nullable: true, dict_id: 0, dict_is_ordered: false, metadata: \{\} \}\), nullable: true, dict_id: 0, dict_is_ordered: false, metadata: \{\} \}\]\)
create table t (s struct<name varchar, info struct<color varchar, size int>>[]) as values
(
[
{'name': 'n1', 'info': {'color': 'r', 'size': 1}},
{'name': 'n2', 'info': {'color': 'b', 'size': 2}}
]
);
```
We need to support such syntax in sqlparser.
List of struct is tracked in https://github.com/sqlparser-rs/sqlparser-rs/pull/1372/files
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Add the ability to create tables with deeply nested schemas in SQL [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb closed issue #11746: Add the ability to create tables with deeply nested schemas in SQL
URL: https://github.com/apache/datafusion/issues/11746
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org