How to view the AST after duckdb's parser? like clickhouse EXPLAIN AST

[iwanttobepowerful]

like clickhouse EXPLAIN AST

[Tishj]

We should probably add this as an option to EXPLAIN, but recently we added json_serialize_sql, which outputs a json serialized statement (that is our AST)

So you can use that in the meantime
#6647

[iwanttobepowerful]

┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                     json_serialize_sql('SELECT 1 + 2 FROM tbl1')                                                                      │
│                                                                                         json                                                                                          │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ {"error":false,"statements":[{"type":"SELECT_NODE","modifiers":[],"cte_map":{"map":[]},"select_list":[{"class":"FUNCTION","type":"FUNCTION","alias":"","function_name":"+","schema"…  │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

it will miss sonething in ternimal, how to avoid it?

[Mytherin]

Change the display mode with .mode

[iwanttobepowerful]

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                                                                                                                                                                                                                                                                                                                                             json_serialize_sql('SELECT 1 + 2 FROM tbl1')                                                                                                                                                                                                                                                                                                                                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"error":false,"statements":[{"type":"SELECT_NODE","modifiers":[],"cte_map":{"map":[]},"select_list":[{"class":"FUNCTION","type":"FUNCTION","alias":"","function_name":"+","schema":"","children":[{"class":"CONSTANT","type":"CONSTANT","alias":"","value":{"type":{"id":"INTEGER","type_info":null},"is_null":false,"value":1}},{"class":"C |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

thanks for your quick reply, but it still miss sonething in ternimal, sorry.

[Mytherin]

There are multiple possible values for .mode, some truncate the rows and others do not. Have you tried all of them?

[iwanttobepowerful]

https://duckdb.org/docs/api/cli#output-formats

thanks again!

[manticore-projects]

Greetings.

You could try JSQLParser which understands a lot of SQL.
In case you hit any DuckDB specific, unsupported syntax I would be very willing to implement it -- since I got really curios about DuckDB.

[paultiq]

FYI, If you're looking for a graphical view or tree view of the json_serialize_sql output: added a mode to magic_duckdb. Only works on the 0.7.2 pre builds.

-e ast_draw (requires graphviz)

%dql -e ast_tree

| Root:

• | error: False
• | statements:
  -- | node: SELECT_NODE
  --- | modifiers: ORDER_MODIFIER
  ---- | orders: ORDER_DEFAULT
  ----- | null_order: ORDER_DEFAULT
  ----- | expression: COLUMN_REF
  ------ | class: COLUMN_REF
  ------ | column_names: part_name
  --- | cte_map:
  --- | select_list: STAR
  ---- | class: STAR
  ---- | columns: False
  --- | from_table: BASE_TABLE
  ---- | table_name: part
  --- | where_clause: GREATERTHAN
  ---- | class: COMPARISON

Demonstration:

• -e ast_tree: Text only
• -e ast_json: Json from json_serialize_sql()
• -e ast_draw: Requires graphviz binaries in your PATH, and graphviz module (pip install graphviz): two separate steps

%pip install magic_duckdb
%load_ext magic_duckdb
%dql CREATE OR REPLACE TABLE shipment (sno int, pno int, qty int, item_name VARCHAR(32));
%dql CREATE OR REPLACE TABLE supplier (sno int, quota int);
%dql CREATE OR REPLACE TABLE part (pno int, amount int, part_name VARCHAR(32));

%dql -e ast_tree SELECT * FROM part WHERE amount > (SELECT MAX(qty) FROM shipment WHERE shipment.pno = part.pno) order by part_name

** Can also use this directly from Python, via magic_duckdb.extras.ast_graphviz
** Will switch the tree formatting to use whatever awesomeness duckdb ends up providing!

[Tishj]

You're a wizard Paul!

Awesome stuff :D
@Maxxen had a similar idea to write an implementation of our Serializer class to write graphviz.