A Fine Slice Of SQL Server
Cathrine Wilhelmsen gets some Lego data:
In the April session of the Azure Synapse Analytics and Microsoft MVP series, I got to show one of my favorite demos: ingesting and exploring LEGO datasets! 🤓 In just a few minutes, I grab a compressed file from the web, decompress it, store it as a Parquet file in my data lake, and run SQL queries on it using Serverless SQL. It really is that simple:
Click through for a video and a step-by-step walkthrough.
Comments closedJust released this fall, is the fully managed Synapse Connector. Azure Synapse Analytics provides a platform for data analysts and data scientists to analyze and combine data from multiple sources. Within Confluent Cloud, data can be synched to dedicated SQL pools via the fully managed Synapse sink connector and attached to Synapse Analytics workspace. Once added to the Synapse Analytics workspace, analysts have the ability to perform advanced analytics and reporting on data in the Confluent pipeline. The ability to access event-level data enables event-level analytics and data exploration.
Click through for two examples, one of loading data into a dedicated SQL pool and one of streaming data into Spark Streaming running on (naturally) a Spark pool.
Comments closedSanjay Raut introduces an interesting report:
The Azure Synapse Analyzer Report was created to help you identify common issues that may be present in your database that can lead to performance issues. This report focuses on known best practices that Microsoft has identified with SQL Dedicated Pools. Following these best practices will help to get the best performance out of your solution.
One thing I appreciate about this is that it covers many items which people don’t know to think about when moving over from SQL Server or Azure SQL Database.
Comments closedAvinanda Chattapadday makes an announcement:
Traditionally, when querying a file or table from your data lake, the Apache Spark engine in Synapse makes a call to your remote ADLS Gen2 storage for each read of the data. For workloads with frequent repeat queries, this process can be redundant and add latency to the overall processing time. Although Apache Spark provides a great caching feature, it must be manually set and released to minimize the latency and improve overall performance. It can also result in queries of stale data if the underlying data changes. This is where the intelligent cache in Azure Synapse can simplify the process; by automatically detecting changes to the underlying files and automatically refreshing them in the cache, you ensure you have access to the most recent data. When the cache reaches its size limit, it will automatically release the least-read data to make space for more recent data.
Click through to see how you can enable this, as well as a few more details on the process.
Comments closedKevin Chant answers a question:
I got asked if I thought we were going backwards with CI/CD for serverless SQL Pools. Mostly due to the fact that we had to include defensive logic in the SQL scripts.
My answer was that we did this purely because of the limitations of the SchemaVersions table. Because the DBOps PowerShell module usually creates this table in the target location if it does not exist. Which it then uses it to log which scripts have already run.
However, you cannot easily create and update this table in a serverless SQL Pool.
Read the whole thing. This sounds like a case in which having a central schema versioning location rather than using the destination system could be advantageous. Of course, now you have a dependency on that central schema versioning location, so life is full of trade-offs.
Comments closedJovan Popovic shows off Synapse Link:
The serverless SQL pools enable you to implement near-real-time analytics solutions on top of your Cosmos DB data. Serverless SQL pools with the Synapse Link provide a cost-effective analytics solution for analyzing NoSQL data stored in Cosmos DB, which is not affecting or spending the resource units on your Cosmos DB transactional store. You can run heavy analytics on the serverless SQL pools that will not affect your workload or price of the main Cosmos DB transactional store. The serverless SQL pools enable you to use the T-SQL query language for analytics that enables you to connect the reporting & analytics tools (such as Power BI, Analytics Services) from a large ecosystem that works with SQL Server or Azure SQL database.
When you are integrating the serverless SQL pools in your solution, you need to apply some best practices. There are general best practices for the serverless SQL pools in the Synapse Analytics workspace, but some of these settings are not applicable to the Cosmos DB scenario. Probably you will use only a subset of the best practices that you can find here. In this post, you will find only the best practices that you should apply in the Cosmos DB solution and some additional hints that could help you to optimize your solution.
Click through to see how the process works and a few recommendations.
Comments closedYou can find more of the best practices here. These best practices are very important because some issues might cause performance degradation. You might be surprised how applying some of these best practices might improve the performance of your workload.
The last item that is related to schema optimization is sometimes hard to check. You would need to look at your schema, inspect all columns and find what to optimize. If you have a large schema, this might not be an easy task. But you can make your life easier if you use the QPI helper library that can detect schema issues for you.
Read on to see what it can find.
Comments closedJovan Popvic rides to the rescue with JSON:
The array cells are pivoted and returned as simple scalar columns. Now you can simply use WHERE or GROUP BY clauses to filter or summarize information by array element values. Another very useful piece of information might be the index of every element (generated as pos column).
Spark enables you to use the posexplode() function on every array cell. The posexplode() function will transform a single array element into a set of rows where each row represents one value in the array and the index of that array element. As a result, one row with the array containing three elements will be transformed into three rows containing scalar cells. This flattened/normalized representation is much easier for the analysis.
Once the array is flattened and normalized, you can easily analyze the data and find how much people knowing SQL or Java.
Read on to see how you can implement the equivalent of POSEXPLODE() using OPENJSON() in the Azure Synapse Analytics serverless SQL pool.
Abhishek Narain announces a pretty nice improvement to Azure Data Factory and Synapse Pipelines:
We are introducing a Script activity in pipelines that provide the ability to execute single or multiple SQL statements.
Using the script activity, you can execute common operations with Data Manipulation Language (DML), and Data Definition Language (DDL). DML statements like SELECT, UPDATE, and INSERT let users retrieve, store, modify, delete, insert and update data in the database. DDL statements like CREATE, ALTER, and DROP allow a database manager to create, modify, and remove database objects such as tables, indexes, and users.
Be sure to read the limitations at the bottom, however.
Comments closedJames Serra lets us know when and where we can use Delta Lake within Azure Synapse Analytics:
Many companies are seeing the value in collecting data to help them make better business decisions. When building a solution in Azure to collect the data, nearly everyone is using a data lake. A majority of those are also using delta lake, which is basically a software layer over a data lake that gives additional features. I have yet to see anyone using competing technologies to delta lake in Azure, such as Apache Hudi or Apache Iceberg (see A Thorough Comparison of Delta Lake, Iceberg and Hudi and Open Source Data Lake Table Formats: Evaluating Current Interest and Rate of Adoption).
Read on for more information.
Comments closed