Key Features of SQL Analytics in Azure Databricks

Published Nov 23 2020 01:34 PM 4,919 Views

As customers continue to standardize on data lakes and the Lakehouse architecture, users expect to be able to query the data in their data lake using SQL.  In fact, approximately 41% of all code executed on Azure Databricks is SQL.  The SQL Analytics service in Azure Databricks was created to provide SQL users with a familiar SQL-editor experience as well as provide optimized BI connections for querying and analyzing data in the data lake.

 

SQL Analytics Key Features

 

MikeCornell_0-1606143341501.png

 

Below are some of the key features in the SQL Analytics service in Azure Databricks:

 

  • The first key feature to highlight is the Query Editor. This editor provides a familiar experience (vs. the traditional notebook experience in Azure Databricks) where users can explore their databases, write SQL queries with intelligent auto-complete, and view query output in either a tabular display or in a rich set of visualizations

MikeCornell_1-1606143767460.png

  • Users can turn the queries and visualizations they create in the query editor into Dashboards.  Dashboards can include content from several different queries, and also allow for basic text/markdown cells.  Once created, dashboard data can be manually refreshed or refreshed on a schedule.
  • In addition to dashboards, queries can be scheduled, and alerts can be created to notify users when a field in the scheduled query meets a certain threshold.  Alerts can even be created for multiple columns.
  • SQL Analytics also has a catalog for dashboards and queries.  This catalog allows users to save their queries and dashboards and share them with other users.  Users can also add tags to their saved queries and dashboards to make them easier to search, and they can even use Favorites to mark frequently used queries and dashboards.
  • The SQL Endpoint in the SQL Analytics service also provides easy connectivity to other BI and SQL tools via ODBC/JDBC connections.  Tools such as Power BI can connect using the native Azure Databricks connector and take advantage of faster, more efficient ODBC/JDBC drivers.
  • Another exciting feature in the SQL Analytics service is the ability to see Query History details.  On the History page, users and admins can see details about all the queries that have been run.  This includes specifics such as the query itself, who ran the query, what endpoint did it run on, was it successful, the duration, how much time was spent compiling-executing-result-fetching, rows read/returned, files scanned, bytes read from cache, and many other details.

MikeCornell_0-1606147151628.png

  • The final key feature to look at in the SQL Analytics service is the compute engine.  SQL Analytics uses the same Delta Engine found in the rest of Azure Databricks.  This means a single, consistent set of APIs and functions across the entire workspace.  The SQL Analytics service goes one step further by also making use of the Photon-powered Delta Engine. This vectorized engine was purpose built for doing SQL and data frame operations while maintaining the same single, consistent set of APIs and functions currently used across the entire workspace.

For more information about the SQL Analytics service in Azure Databricks, check out the docs page and the Databricks launch blog. The SQL Analytics service is currently in Public Preview. Contact your Azure Databricks representative to request access. Get started with Azure Databricks by joining an Azure Databricks workshop.

%3CLINGO-SUB%20id%3D%22lingo-sub-1924410%22%20slang%3D%22en-US%22%3EKey%20Features%20of%20SQL%20Analytics%20in%20Azure%20Databricks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1924410%22%20slang%3D%22en-US%22%3E%3CP%3EAs%20customers%20continue%20to%20standardize%20on%20data%20lakes%20and%20the%20%3CA%20href%3D%22https%3A%2F%2Fdbricks.co%2F3pTc7Ab%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3ELakehouse%20architecture%3C%2FA%3E%2C%20users%20expect%20to%20be%20able%20to%20query%20the%20data%20in%20their%20data%20lake%20using%20SQL.%26nbsp%3B%20In%20fact%2C%20approximately%2041%25%20of%20all%20code%20executed%20on%20Azure%20Databricks%20is%20SQL.%26nbsp%3B%20The%20SQL%20Analytics%20service%20in%20Azure%20Databricks%20was%20created%20to%20provide%20SQL%20users%20with%20a%20familiar%20SQL-editor%20experience%20as%20well%20as%20provide%20optimized%20BI%20connections%20for%20querying%20and%20analyzing%20data%20in%20the%20data%20lake.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--1185463093%22%20id%3D%22toc-hId--1185463088%22%3ESQL%20Analytics%20Key%20Features%3C%2FH2%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22MikeCornell_0-1606143341501.png%22%20style%3D%22width%3A%20725px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F235563i06EA512C152FAA5D%2Fimage-dimensions%2F725x339%3Fv%3D1.0%22%20width%3D%22725%22%20height%3D%22339%22%20role%3D%22button%22%20title%3D%22MikeCornell_0-1606143341501.png%22%20alt%3D%22MikeCornell_0-1606143341501.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBelow%20are%20some%20of%20the%20key%20features%20in%20the%20SQL%20Analytics%20service%20in%20Azure%20Databricks%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EThe%20first%20key%20feature%20to%20highlight%20is%20the%20%3CA%20href%3D%22https%3A%2F%2Fdbricks.co%2F3nQsBaw%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3E%3CSTRONG%3EQuery%26nbsp%3BEditor%3C%2FSTRONG%3E%3C%2FA%3E.%20This%20editor%20provides%20a%20familiar%20experience%20(vs.%20the%20traditional%20notebook%20experience%20in%20Azure%20Databricks)%20where%20users%20can%20explore%20their%20databases%2C%20write%20SQL%20queries%20with%20intelligent%20auto-complete%2C%20and%20view%20query%20output%20in%20either%20a%20tabular%20display%20or%20in%20a%20%3CA%20href%3D%22https%3A%2F%2Fdbricks.co%2F3nOu9Bz%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Erich%20set%20of%20visualizations%3C%2FA%3E.%26nbsp%3B%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MikeCornell_1-1606143767460.png%22%20style%3D%22width%3A%20471px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F235567iA358DED7A1C9F427%2Fimage-dimensions%2F471x344%3Fv%3D1.0%22%20width%3D%22471%22%20height%3D%22344%22%20role%3D%22button%22%20title%3D%22MikeCornell_1-1606143767460.png%22%20alt%3D%22MikeCornell_1-1606143767460.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EUsers%20can%20turn%20the%20queries%20and%20visualizations%20they%20create%20in%20the%20query%20editor%20into%20%3CSTRONG%3E%3CA%20href%3D%22https%3A%2F%2Fdbricks.co%2F2J8Hfec%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EDashboards%3C%2FA%3E%3C%2FSTRONG%3E.%26nbsp%3B%20Dashboards%20can%20include%20content%20from%20several%20different%20queries%2C%20and%20also%20allow%20for%20basic%20text%2Fmarkdown%20cells.%26nbsp%3B%20Once%20created%2C%20dashboard%20data%20can%20be%20%3CA%20href%3D%22https%3A%2F%2Fdbricks.co%2F395TzGZ%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Emanually%20refreshed%20or%20refreshed%20on%20a%20schedule%3C%2FA%3E.%3C%2FLI%3E%0A%3CLI%3EIn%20addition%20to%20dashboards%2C%20queries%20can%20be%20%3CSTRONG%3E%3CA%20href%3D%22https%3A%2F%2Fdbricks.co%2F2J404Ps%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Escheduled%3C%2FA%3E%3C%2FSTRONG%3E%2C%20and%20%3CSTRONG%3E%3CA%20href%3D%22https%3A%2F%2Fdbricks.co%2F3pXq9AA%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ealerts%3C%2FA%3E%3C%2FSTRONG%3E%20can%20be%20created%20to%20notify%20users%20when%20a%20field%20in%20the%20scheduled%20query%20meets%20a%20certain%20threshold.%26nbsp%3B%20Alerts%20can%20even%20be%20created%20for%20%3CA%20href%3D%22https%3A%2F%2Fdbricks.co%2F3fpfjyH%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Emultiple%20columns%3C%2FA%3E.%3C%2FLI%3E%0A%3CLI%3ESQL%20Analytics%20also%20has%20a%20%3CSTRONG%3Ecatalog%3C%2FSTRONG%3E%20for%20dashboards%20and%20queries.%26nbsp%3B%20This%20catalog%20allows%20users%20to%20save%20their%20queries%20and%20dashboards%20and%20share%20them%20with%20other%20users.%26nbsp%3B%20Users%20can%20also%20%3CA%20href%3D%22https%3A%2F%2Fdbricks.co%2F3718yiP%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Eadd%20tags%3C%2FA%3E%20to%20their%20saved%20queries%20and%20dashboards%20to%20make%20them%20easier%20to%20search%2C%20and%20they%20can%20even%20use%20%3CA%20href%3D%22https%3A%2F%2Fdbricks.co%2F3pXtC24%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EFavorites%3C%2FA%3E%20to%20mark%20frequently%20used%20queries%20and%20dashboards.%3C%2FLI%3E%0A%3CLI%3EThe%20%3CSTRONG%3ESQL%20Endpoint%3C%2FSTRONG%3E%20in%20the%20SQL%20Analytics%20service%20also%20provides%20easy%20%3CSTRONG%3Econnectivity%3C%2FSTRONG%3E%20to%20other%20%3CA%20href%3D%22https%3A%2F%2Fdbricks.co%2F35XJ9qY%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EBI%20and%20SQL%20tools%3C%2FA%3E%20via%20ODBC%2FJDBC%20connections.%26nbsp%3B%20Tools%20such%20as%20Power%20BI%20can%20connect%20using%20the%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdbricks.co%2F3nI5e2K%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Enative%20Azure%20Databricks%20connector%3C%2FA%3E%20and%20take%20advantage%20of%20faster%2C%20more%20efficient%20%3CA%20href%3D%22https%3A%2F%2Fdbricks.co%2F3lZWxjJ%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EODBC%2FJDBC%20drivers%3C%2FA%3E.%3C%2FLI%3E%0A%3CLI%3EAnother%20exciting%20feature%20in%20the%20SQL%20Analytics%20service%20is%20the%20ability%20to%20see%20%3CSTRONG%3E%3CA%20href%3D%22https%3A%2F%2Fdbricks.co%2F2UXFX88%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EQuery%20History%3C%2FA%3E%3C%2FSTRONG%3E%20details.%26nbsp%3B%20On%20the%20History%20page%2C%20users%20and%20admins%20can%20see%20details%20about%20all%20the%20queries%20that%20have%20been%20run.%26nbsp%3B%20This%20includes%20specifics%20such%20as%20the%20query%20itself%2C%20who%20ran%20the%20query%2C%20what%20endpoint%20did%20it%20run%20on%2C%20was%20it%20successful%2C%20the%20duration%2C%20how%20much%20time%20was%20spent%20compiling-executing-result-fetching%2C%20rows%20read%2Freturned%2C%20files%20scanned%2C%20bytes%20read%20from%20cache%2C%20and%20many%20other%20details.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MikeCornell_0-1606147151628.png%22%20style%3D%22width%3A%20502px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F235586i2BAC77EF9B9F7783%2Fimage-dimensions%2F502x246%3Fv%3D1.0%22%20width%3D%22502%22%20height%3D%22246%22%20role%3D%22button%22%20title%3D%22MikeCornell_0-1606147151628.png%22%20alt%3D%22MikeCornell_0-1606147151628.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EThe%20final%20key%20feature%20to%20look%20at%20in%20the%20SQL%20Analytics%20service%20is%20the%20%3CSTRONG%3Ecompute%20engine%3C%2FSTRONG%3E.%26nbsp%3B%20SQL%20Analytics%20uses%20the%20same%20%3CA%20href%3D%22https%3A%2F%2Fdbricks.co%2F3fsw0ZX%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EDelta%20Engine%3C%2FA%3E%20found%20in%20the%20rest%20of%20Azure%20Databricks.%26nbsp%3B%20This%20means%20a%20single%2C%20consistent%20set%20of%20APIs%20and%20functions%20across%20the%20entire%20workspace.%26nbsp%3B%20The%20SQL%20Analytics%20service%20goes%20one%20step%20further%20by%20also%20making%20use%20of%20the%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdbricks.co%2F2ITyiFS%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EPhoton-powered%20Delta%20Engine%3C%2FA%3E.%20This%20vectorized%20engine%20was%20purpose%20built%20for%20doing%20SQL%20and%20data%20frame%20operations%20while%20maintaining%20the%20same%20single%2C%20consistent%20set%20of%20APIs%20and%20functions%20currently%20used%20across%20the%20entire%20workspace.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3EFor%20more%20information%20about%20the%20SQL%20Analytics%20service%20in%20%3CA%20href%3D%22https%3A%2F%2Fdbricks.co%2F3kCItuU%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EAzure%20Databricks%3C%2FA%3E%2C%20check%20out%20the%20%3CA%20href%3D%22https%3A%2F%2Fdbricks.co%2F39cOOeC%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Edocs%20page%3C%2FA%3E%20and%20the%20%3CA%20href%3D%22https%3A%2F%2Fdbricks.co%2F3nSxMqu%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EDatabricks%20launch%20blog%3C%2FA%3E.%20%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3EThe%20SQL%20Analytics%20service%20is%20currently%20in%20%3CA%20href%3D%22https%3A%2F%2Fdbricks.co%2F2UTt8Mb%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EPublic%20Preview%3C%2FA%3E.%20Contact%20your%20Azure%20Databricks%20representative%20to%20request%20access.%3C%2FFONT%3E%20Get%20started%20with%20Azure%20Databricks%20by%20joining%20an%20%3CA%20href%3D%22https%3A%2F%2Fdbricks.co%2F2K93eBX%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EAzure%20Databricks%20workshop%3C%2FA%3E.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1924410%22%20slang%3D%22en-US%22%3E%3CP%3ESQL%20users%20now%20have%20a%20native%20experience%20in%20Azure%20Databricks.%26nbsp%3B%20Read%20more%20about%20the%20key%20features%20of%20the%20SQL%20Analytics%20service%20in%20Azure%20Databricks.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1924410%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAnalytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%20Databricks%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDelta%20Lake%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Nov 23 2020 01:33 PM