Query plans for analytic SQL constructs

Patent metadata

Field	Value
Publication number	US-12079244-B2
Application number	US-202217972003-A
Country	US
Kind code	B2
Filing date	Oct 24, 2022
Priority date	Feb 19, 2014
Publication date	Sep 3, 2024
Grant date	Sep 3, 2024

Abstract

A system and method for managing data storage and data access with querying data in a distributed system without buffering the results of intermediate operations in persistent storage. The method includes pushing, by a first operator, as a result of executing a query plan, an intermediate result to a plurality of secondary operators. Processing of the intermediate result by the plurality of secondary operators is delayed to coordinate generation of a plurality of second results based on beginning a final consumption operation of the plurality of second results.

First claim

What is claimed is:

1. A method comprising pushing, by a first operator, as a result of executing a query plan against a set of data, an intermediate result of the first operator to a plurality of secondary operators;
delaying beginning processing of the intermediate result by the plurality of secondary operators to coordinate concurrent generation, on one or more processors, of a plurality of second results based on beginning a final consumption operation; and
executing the final consumption operation, by the first operator, against the plurality of second results, to generate a final result.

2. The method of claim 1 , wherein the plurality of second results is generated without writing the intermediate result to a persistent storage device.

3. The method of claim 1 , wherein the final consumption operation is generated without writing the plurality of second results to a persistent storage device.

4. The method of claim 1 , wherein the intermediate result is not materialized.

5. The method of claim 1 , wherein the plurality of second results is not materialized.

6. The method of claim 1 , wherein the plurality of second results is not inlined.

7. The method of claim 1 , wherein the query plan comprises at least one of:
a grouping set;
a roll-up; or
a cube aggregate.

8. The method of claim 1 , wherein each of the secondary operators of the plurality of secondary operators are different from one another.

9. The method of claim 1 , wherein the final consumption operation is delayed until a completion of the plurality of secondary operators.

10. A non-transitory machine-readable medium storing instructions which, when executed by one or more processors of a computing device, cause the one or more processors to:
push, by a first operator, as a result of executing a query plan against a set of data, an intermediate result of the first operator to a plurality of secondary operators;
delay beginning processing of the intermediate result by the plurality of secondary operators to coordinate concurrent generation of a plurality of second results based on beginning a final consumption operation; and
execute the final consumption operation, by the first operator, against the plurality of second results, to generate a final result.

11. The non-transitory machine-readable medium of claim 10 , wherein the instructions further cause the one or more processors to generate the plurality of second results without writing the intermediate result to a persistent storage device.

12. The non-transitory machine-readable medium of claim 10 , wherein the instructions further cause the one or more processors to generate the final consumption operation without writing the plurality of second results to a persistent storage device.

13. The non-transitory machine-readable medium of claim 10 , wherein the intermediate result is not materialized.

14. The non-transitory machine-readable medium of claim 10 , wherein the plurality of second results is not inlined.

15. The non-transitory machine-readable medium of claim 10 , wherein the query plan comprises a window aggregate.

16. The non-transitory machine-readable medium of claim 10 , wherein the instructions further cause the first operator to push the intermediate result to the plurality of secondary operators without buffering the intermediate result.

17. The non-transitory machine-readable medium of claim 10 , wherein the instructions further cause the first operator and the plurality of second operators to execute concurrently.

18. The non-transitory machine-readable medium of claim 10 , wherein the instructions further cause a delay of the final consumption operation until a completion of the plurality of secondary operators.

19. A system comprising:
memory; and
one or more processors, operatively coupled to the memory, the one or more processors to:
push, by a first operator, as a result of executing a query plan against a set of data, an intermediate result of the first operator to a plurality of secondary operators;
delay beginning processing of the intermediate result by the plurality of secondary operators to coordinate concurrent generation of a plurality of second results based on beginning a final consumption operation; and
execute the final consumption operation, by the first operator, against the plurality of second results, to generate a final result.

20. The system of claim 19 wherein the intermediate result and the plurality of second results is not materialized.

21. The system of claim 19 , wherein the system generates the final consumption operation without writing the plurality of second results to a persistent storage device.

22. The system of claim 19 , wherein the plurality of second results is not inlined.

23. The system of claim 19 , wherein the query plan comprises at least one of:
a grouping set;
a roll-up; or
a cube aggregate.

24. The system of claim 19 , wherein each of the secondary operators of the plurality of secondary operators are different from one another.

25. The system of claim 19 , wherein the final consumption operation is delayed until a completion of the plurality of secondary operators.

26. The system of claim 19 , wherein the one or more processors generate the final consumption operation without writing the plurality of second results to a persistent storage device.

27. The system of claim 19 , wherein the query plan comprises a window aggregate.

28. The system of claim 19 , wherein the first operator pushes the intermediate result to the plurality of secondary operators without buffering the intermediate result.

29. The system of claim 19 , wherein the first operator and the plurality of second operators execute concurrently.

30. The system of claim 19 , wherein the final consumption operation delays until a completion of the plurality of secondary operators.

Assignees

• Snowflake Inc

Inventors

• Cruanes Thierry
• Dageville Benoit
• Lee Allison Waingold

Classifications

• G06F9/5083

  Techniques for rebalancing the load in a distributed system · CPC title

• H04L67/1097

  for distributed storage of data in networks, e.g. transport arrangements for network file system [NFS], storage area networks [SAN] or network attached storage [NAS] · CPC title

• G06F9/5044

  considering hardware capabilities · CPC title

• H04L67/1095

  Replication or mirroring of data, e.g. scheduling or transport for data synchronisation between network nodes · CPC title

• G06F9/5088

  involving task migration · CPC title