Using SQL in Starburst #
Starburst brings Presto’s open source distributed SQL engine for running fast analytic queries against various data sources ranging in size from gigabytes to petabytes to even more data sources, with more robust features. Because Starburst’s SQL is ANSI-compliant and supports most of the SQL language features you depend on, you can hit the ground running.
Business intelligence users and data scientists can continue to use their favorite tools such as Tableau, Qlik and Apache Superset to access and analyze virtually any data source, or multiple data sources in a single query.
The basics #
We know you want to jump right in, and we know you already have awesome analytics skills. It’s just a matter of harnessing the power of Starburst products to take your analytics even further. With that in mind, you can browse our latest reference materials to learn just how familiar Starburst SQL is:
One key difference worth highlighting is the concept of catalogs. Each of your data sources is defined as a catalog in Starburst, and that catalog in turn contains schemas. Using a SQL client such as our CLI, you can discover what catalogs are available:
presto> SHOW CATALOGS; Catalog --------- hive_sales mysql_crm (2 rows)
From there, you can use the familiar
SHOW SCHEMAS command to drill further
Fully-qualified table names #
Table names are fully qualified when they include the catalog name:
This becomes critical when creating federated queries.
General SQL features #
Just in case you’d like a review, here’s a walkthrough of some basic SQL features in Starburst from one of our founders, David Phillips:
Advanced SQL #
Ready to move past the basics? For your convenience, we’ve divided the Advanced SQL for Starburst video training course up into topic sections, and provided links to the relevant parts of our documentation below.
Advanced aggregation techniques #
Window functions #
Array and map functions #
Many data stores allow to to create arrays, but it isn’t always easy. Starburst allows you to easily create arrays and maps with your data. Creating arrays with your data is easy:
SELECT ARRAY[4, 5, 6] AS integers, ARRAY['hello', 'world'] AS varchars; integers | varchars -----------+---------------- [4, 5, 6] | [hello, world]
SQL array indexes are 1-based. Learn more about how to use and manipulate them in this in-depth video.
- Accessing array and map elements with element_at()
- Sorting arrays with array_sort()
- matching elements with any_match(), all_match() and none_match()
- Filtering elements
- Transforming elements
- Converting arrays to strings
- Computing array products
- Unnesting arrays and maps
- Creating maps from keys and values and an array of entry rows