A Fine Slice Of SQL Server
Hiram Fleitas builds a process:
I use an Azure Automation account to host my PowerShell script because it provides reusability and management of required modules. Schedules and logging are included but most importantly, it provides a REST API webhook that I can call from pipelines if necessary.
Click through to see how this all works.
Comments closedSaveen Reddy catalogs what’s new in Azure Synapse Analytics:
Quick Reuse of Spark clusters
By default, every data flow activity spins up a new Spark cluster based upon the Azure Integration Runtime (IR) configuration. Cold cluster start-up time takes a few minutes. If your pipelines contain multiple sequential data flows, you can enable a time-to-live (TTL) value, which keeps a cluster alive for a certain period of time after its execution completes. If a new job starts using the IR during the TTL duration, it will reuse the existing cluster and start up time will be greatly reduced.
Read on for the full list of updates.
Comments closedTino Zishiri has some tips for people trying out the Azure Synapse Analytics serverless SQL pool:
Serverless SQL Pools or SQL on-demand is a serverless distributed data processing service offered by Microsoft. The service is comparable to Amazon Athena. The serverless nature of the service means that there is no infrastructure to manage, and you only pay for what you use (pay-per-query model).
Through Serverless SQL pools, you query the data in your data lake using T-SQL. The architecture behind the service is optimized for querying and analyzing big data by running queries in parallel.
Read on to understand where the serverless SQL pool fits, as well as some tips about data transformation with this pool.
Comments closedCathrine Wilhelmsen troubleshoots an Azure issue:
I ran into an issue today while trying to publish a storage event trigger in Azure Synapse Analytics. After publishing, I got error messages that said “failed to subscribe” and “failed to activate”. The storage event trigger had been published, but it wouldn’t start. Help!
Click through for some resources on documentation, a few things which didn’t work, and what finally resolved the issue.
Comments closedJames Serra keeps us up to date on Synapse:
Delta Lake support for serverless SQL is generally available: Azure Synapse has had preview-level support for serverless SQL pools querying the Delta Lake format. This enables BI and reporting tools to access data in Delta Lake format through standard T-SQL. With this latest update, the support is now Generally Available and can be used in production. See How to query Delta Lake files using serverless SQL pools
Click through for the full list of what James likes.
Comments closedSaveen Reddy shows how the Synapse product team has been busy this year:
Previously, Synapse workspaces had a kind of database called a Spark Database. Spark databases had two key characteristics:
– Tables in Spark databases kept their underlying data in Azure Storage accounts (i.e. data lakes)
– Tables in Spark databases could be queried by both Spark pools and by serverless SQL pools.
To help make it clear that these databases are supported by both Spark and SQL and to clarify their relationship to data lakes, we have renamed Spark databases to Lake databases. Lake databases work just like Spark databases did before. They just have a new name.
Okay, this is the kind of change I can do without. That’s a really dumb name. Spark databases tell you what a thing is. It’s a database which lives in Apache Spark. Lake databases run what? Apache Spark. But if anything really should be called a Lake database, it’d be a serverless SQL pool’s database because everything in there is built on top of the data lake—it’s all external tables pointing to a lake. So calling a Spark database a Lake database brings more confusion than elucidation.
Most of the other changes on that list? Really cool. This one? Not at all.
Comments closedJovan Popovic confirms that Microsoft is using the term “Lakehouse” like Databricks does:
The lakehouse pattern enables you to keep a large amount of your data in Data Lake and to get the analytic capabilities without a need to move your data to some data warehouse to start an analysis. A lakehouse represents a good trade-off between query performance and the ability to access the latest version of data without the need to wait for data to be reloaded.
Azure Synapse Analytics workspace enables you to implement the Lakehouse pattern on top of Azure Data Lake storage.
When you think about your lakehouse solution, be aware that there are two options for creating databases over the lake:
– Lake databases that are created using Spark or database template
– SQL databases that are created using serverless SQL pools on top of data lake.
Although you might use different tools and languages to create these types of databases, the principles described in this article apply to both types. I will use the term “lakehouse” whenever i reference Spak Lake database or SQL database created using the serverless SQL pools.
Click through for Jovan’s guidance.
Comments closedJovan Popovic has a public service announcement for us:
The serverless SQL pool is a distributed computing system that executes concurrent queries on a set of distributed compute nodes. Multiple compute nodes are running the parts of a distributed query plan that read the underlying files, join the data sets, group, and aggregate results. Different queries might try to use the same compute nodes to execute the parts of the queries.
The oversized column types like VARCHAR(MAX) might trick the compute node to allocate more resources than is needed. However, the allocation is based on the estimate, but these over-allocated resources will not be used in actual execution because they are not needed. If a compute node needs 100MB to sort the results it will use these 100MB although the query optimizer allocated 4GB of memory for the task on the compute node.
Read the whole thing.
Comments closedAaron Merrill announces database templates for Azure Synapse Analytics:
The Synapse database template for Agriculture is a comprehensive data model that addresses the typical data requirements of organizations engaged in growing crops, raising livestock, and producing dairy products, including field and pasture management and satellite and drone data.
The Synapse database template for Energy & Commodity Trading is a comprehensive data model that addresses the typical data requirements of organizations engaged in trading energy, commodities, and/or carbon credits, whether as a primary trading business or in support of their supply chains, operating businesses, and hedging activities.
You may remember Microsoft buying ADRM Software a while back. This is why.
Comments closedKevin Chant reminds me I need to spend more time with GitHub Actions:
I want to cover one way you can do CI/CD for Azure Synapse Analytics serverless SQL pools using GitHub Actions in this post. For various reasons.
For a start, in a previous post I wrote about how you can CI/CD for serverless SQL pools using Azure DevOps. So, I thought I would balance things out and show how you can do the same thing within GitHub.
In addition to this, there have been a few discussions about using GitHub Actions instead of Azure Pipelines within the Microsoft Data Platform community recently. For example, the topic came up during the DataWeekender conference.
With this in mind, I want to show how easy it can be to migrate an Azure DevOps pipeline to GitHub Actions.
Click through for the example.
Comments closed