Substrait Installable Extension #3034

pdet · 2022-02-04T12:37:31Z

This PR introduces the DuckDB-Substrait Installable Extension.

It also inlines substrait (only the generated CPP code from the proto files) and protobuf.

At this point, this extension is still highly experimental, with all tests being round-trips to and from substrait plans.

To build duckdb with the substrait extension, you need to pass the BUILD_SUBSTRAIT_EXTENSION flag on:
e.g.,

BUILD_SUBSTRAIT_EXTENSION=1  make debug

To use it, two new functions are introduced:

get_substrait()
This function requires a valid SQL query and returns a BLOB column with the serialized query plan.
e.g.,

CREATE TABLE crossfit (exercise text,dificulty_level int);

insert into crossfit values ('Push Ups', 3), ('Pull Ups', 5) , (' Push Jerk', 7), ('Bar Muscle Up', 10);

CALL get_substrait('select count(exercise) from crossfit where dificulty_level <=5')
----
\x12\x11\x1A\x0F\x1A\x0Dlessthanequal\x12\x11\x1A\x0F\x10\x01\x1A\x0Bis_not_null\x12\x09\x1A\x07\x10\x02\x1A\x03and\x12\x10\x1A\x0E\x10\x03\x1A\x0Acount_star\x1A\x5C\x0AZ:X\x12N"L\x12B\x0A@\x1A(\x1A&\x08\x02\x12\x12\x1A\x10\x12\x08\x12\x06\x0A\x04\x12\x02\x08\x01\x12\x04\x0A\x02(\x05\x12\x0E\x1A\x0C\x08\x01\x12\x08\x12\x06\x0A\x04\x12\x02\x08\x01"\x08\x0A\x06\x0A\x02\x08\x01\x0A\x00:\x0A\x0A\x08crossfit\x1A\x00"\x04\x0A\x02\x08\x03\x1A\x06\x12\x04\x0A\x02\x12\x00

from_substrait()
This function requires a serialized plan as a BLOB and returns the query result

CALL from_substrait('\x12\x11\x1A\x0F\x1A\x0Dlessthanequal\x12\x11\x1A\x0F\x10\x01\x1A\x0Bis_not_null\x12\x09\x1A\x07\x10\x02\x1A\x03and\x12\x10\x1A\x0E\x10\x03\x1A\x0Acount_star\x1A\x5C\x0AZ:X\x12N"L\x12B\x0A@\x1A(\x1A&\x08\x02\x12\x12\x1A\x10\x12\x08\x12\x06\x0A\x04\x12\x02\x08\x01\x12\x04\x0A\x02(\x05\x12\x0E\x1A\x0C\x08\x01\x12\x08\x12\x06\x0A\x04\x12\x02\x08\x01"\x08\x0A\x06\x0A\x02\x08\x01\x0A\x00:\x0A\x0A\x08crossfit\x1A\x00"\x04\x0A\x02\x08\x03\x1A\x06\x12\x04\x0A\x02\x12\x00'::BLOB)
----
2

If the pragma enable_verification is executed, we also run an extra query verification step on the get_substrait() function that roundtrips the query internally and verifies if the results are correct.

cc @cpcloud

…files

…sion

…ntly

…formation

Mytherin

Thanks for the PR! Looks great. Some comments:

.github/workflows/Main.yml

Mytherin · 2022-02-08T11:10:14Z

extension/substrait/CMakeLists.txt

+  to_substrait.cpp from_substrait.cpp substrait-extension.cpp
+  ${SUBSTRAIT_SOURCES} ${PROTOBUF_SOURCES})
+
+build_loadable_extension(substrait substrait-extension.cpp)


This is missing many sources, it will fail when actually used as a loadable extension (although this is not tested right now).

lava · 2022-02-10T11:34:18Z

I tried compiling this to check out the created query plans for another project. It seems to work as described, but I'm not sure how to actually get the generated query plan and store it as a protobuf? The duckdb terminal only shows an abbreviated version of the blob:

D CREATE TABLE http (port int);
D CALL get_substrait('SELECT * from http');
┌────────────────────────────────────────────────────────────────────────────────────┐
│                                     Plan Blob                                      │
├────────────────────────────────────────────────────────────────────────────────────┤
│ \x1A\x1E\x0A\x1C:\x1A\x12\x10\x0A\x0E"\x04\x0A\x02\x0A\x00:\x06\x0A\x04http\x1A... │
└────────────────────────────────────────────────────────────────────────────────────┘

pdet · 2022-02-10T11:39:52Z

Maybe using it through the shell is not the most practical way, a python script through the python API should be easier.

pdet · 2022-02-10T11:41:22Z

@Mytherin I'm only missing the windows-extensions now, but I'm a bit lost on the fixes necessary for it.

Any idea what I'm missing or how to gather more debugging info?

Mytherin · 2022-02-14T20:39:51Z

I tried compiling this to check out the created query plans for another project. It seems to work as described, but I'm not sure how to actually get the generated query plan and store it as a protobuf? The duckdb terminal only shows an abbreviated version of the blob:

D CREATE TABLE http (port int);
D CALL get_substrait('SELECT * from http');
┌────────────────────────────────────────────────────────────────────────────────────┐
│                                     Plan Blob                                      │
├────────────────────────────────────────────────────────────────────────────────────┤
│ \x1A\x1E\x0A\x1C:\x1A\x12\x10\x0A\x0E"\x04\x0A\x02\x0A\x00:\x06\x0A\x04http\x1A... │
└────────────────────────────────────────────────────────────────────────────────────┘

The truncation only happens in the box mode rendering. You can change the rendering using the .mode specifier (e.g. .mode csv or .mode json).

Mytherin · 2022-02-14T20:41:09Z

@pdet

The remaining windows error is related to missing DUCKDB_API specifiers:

2022-02-14T14:19:39.0787294Z from_substrait.obj : error LNK2019: unresolved external symbol "public: __cdecl duckdb::FunctionExpression::FunctionExpression(class std::basic_string<char,struct std::char_traits<char>,class std::allocator<char> > const &,class std::vector<class std::unique_ptr<class duckdb::ParsedExpression,struct std::default_delete<class duckdb::ParsedExpression> >,class std::allocator<class std::unique_ptr<class duckdb::ParsedExpression,struct std::default_delete<class duckdb::ParsedExpression> > > >,class std::unique_ptr<class duckdb::ParsedExpression,struct std::default_delete<class duckdb::ParsedExpression> >,class std::unique_ptr<class duckdb::OrderModifier,struct std::default_delete<class duckdb::OrderModifier> >,bool,bool,bool)" (??0FunctionExpression@duckdb@@QEAA@AEBV?$basic_string@DU?$char_traits@D@std@@V?$allocator@D@2@@std@@V?$vector@V?$unique_ptr@VParsedExpression@duckdb@@U?$default_delete@VParsedExpression@duckdb@@@std@@@std@@V?$allocator@V?$unique_ptr@VParsedExpression@duckdb@@U?$default_delete@VParsedExpression@duckdb@@@std@@@std@@@2@@3@V?$unique_ptr@VParsedExpression@duckdb@@U?$default_delete@VParsedExpression@duckdb@@@std@@@3@V?$unique_ptr@VOrderModifier@duckdb@@U?$default_delete@VOrderModifier@duckdb@@@std@@@3@_N44@Z) referenced in function "class std::unique_ptr<class duckdb::FunctionExpression,struct std::default_delete<class duckdb::FunctionExpression> > __cdecl std::make_unique<class duckdb::FunctionExpression,class std::basic_string<char,struct std::char_traits<char>,class std::allocator<char> > &,class std::vector<class std::unique_ptr<class duckdb::ParsedExpression,struct std::default_delete<class duckdb::ParsedExpression> >,class std::allocator<class std::unique_ptr<class duckdb::ParsedExpression,struct std::default_delete<class duckdb::ParsedExpression> > > >,0>(class std::basic_string<char,struct std::char_traits<char>,class std::allocator<char> > &,class std::vector<class std::unique_ptr<class duckdb::ParsedExpression,struct std::default_delete<class duckdb::ParsedExpression> >,class std::allocator<class std::unique_ptr<class duckdb::ParsedExpression,struct std::default_delete<class duckdb::ParsedExpression> > > > &&)" (??$make_unique@VFunctionExpression@duckdb@@AEAV?$basic_string@DU?$char_traits@D@std@@V?$allocator@D@2@@std@@V?$vector@V?$unique_ptr@VParsedExpression@duckdb@@U?$default_delete@VParsedExpression@duckdb@@@std@@@std@@V?$allocator@V?$unique_ptr@VParsedExpression@duckdb@@U?$default_delete@VParsedExpression@duckdb@@@std@@@std@@@2@@4@$0A@@std@@YA?AV?$unique_ptr@VFunctionExpression@duckdb@@U?$default_delete@VFunctionExpression@duckdb@@@std@@@0@AEAV?$basic_string@DU?$char_traits@D@std@@V?$allocator@D@2@@0@$$QEAV?$vector@V?$unique_ptr@VParsedExpression@duckdb@@U?$default_delete@VParsedExpression@duckdb@@@std@@@std@@V?$allocator@V?$unique_ptr@VParsedExpression@duckdb@@U?$default_delete@VParsedExpression@duckdb@@@std@@@std@@@2@@0@@Z) [D:\a\duckdb\duckdb\build\release\extension\substrait\substrait_loadable_extension.vcxproj]

pdet added 18 commits January 31, 2022 14:25

Adding substrait as a third party and script to update/gen substrait …

3666d5b

…files

We dont really have to keep proto files, start of the substrait exten…

59ef2a7

…sion

Mocking first substrait test

ac3baa7

to_substrait works

6367bca

From substrait also working

4730a2f

New git ignore

684e8d4

Add compiled substrait at third_party

8b11a91

Inlining Protobuf dependency

d6dbcf5

Protobuf License

dfb0a93

Adding substrait tests, Cross Product and CI

1c2b978

warning on protobuf

94fa795

Warning fixes on protobuf

007df45

Gcc doesnt know how to link libraries compiled on dif versions appare…

d34a2e9

…ntly

A lot of refactoring and memory leak fixing in the to_substrait trans…

ad77ddb

…formation

Fixing joins and case stmts for substrait

aeb8581

Refactoring to_substrait

f221562

Refactor of from_substrait

4f355fb

from_substrait with multiple columns test

6dcf9bc

pdet requested a review from Mytherin February 4, 2022 12:37

pdet added the feature label Feb 4, 2022

Print call statement results

316893c

Mytherin reviewed Feb 8, 2022

View reviewed changes

Fighting the linker

a935d6c

mavam mentioned this pull request Feb 9, 2022

Add substrait query frontend tenzir/tenzir#2075

Closed

9 tasks

pdet added 3 commits February 9, 2022 20:47

Trying to make windows happy

ebe511f

Trying to make windows happy

1392113

Clean up cmake

2418265

pdet added 2 commits February 14, 2022 13:08

Merge branch 'master' into substrait

662c6c1

Am i missing this define?

2a0b0dd

pdet added 2 commits February 15, 2022 19:08

Merge branch 'master' into substrait

6ea3795

More windows linking fixes

a5836ee

Mytherin merged commit 0b9c1c2 into duckdb:master Feb 16, 2022

pdet deleted the substrait branch June 27, 2024 13:58

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Substrait Installable Extension #3034

Substrait Installable Extension #3034

pdet commented Feb 4, 2022 •

edited

Loading

Mytherin left a comment

Mytherin Feb 8, 2022

lava commented Feb 10, 2022

pdet commented Feb 10, 2022

pdet commented Feb 10, 2022

Mytherin commented Feb 14, 2022

Mytherin commented Feb 14, 2022

Substrait Installable Extension #3034

Substrait Installable Extension #3034

Conversation

pdet commented Feb 4, 2022 • edited Loading

Mytherin left a comment

Choose a reason for hiding this comment

Mytherin Feb 8, 2022

Choose a reason for hiding this comment

lava commented Feb 10, 2022

pdet commented Feb 10, 2022

pdet commented Feb 10, 2022

Mytherin commented Feb 14, 2022

Mytherin commented Feb 14, 2022

pdet commented Feb 4, 2022 •

edited

Loading