A Fine Slice Of SQL Server
Mark Kromer shows off a few functions in Azure Data Factory to modify data in arrays:
The first transformation function is map() and allows you to apply data flow scalar functions as the 2nd parameter to the map() function. In my case, I use upper() to uppercase every element in my string array:
map(columnNames(),upper(#item))
Read on for more iteration and aggregation functions akin to map, reduce, and filter.
Comments closedDinesh Asanka takes us through the cross-validation component in Azure ML Studio:
Let us look at implementing Cross-Validation in Azure Machine Learning. Let us use the sample Adventure Works database that we used for all the articles.
Then Cross Validate Model is dragged and dropped to the experiment. The Cross Validate model has two inputs and two outputs. Two inputs are data input and the relation to the Machine Learning technique. Let us use the Two-Class Decision Jungle as the Machine Learning Technique. Then the first output is connected to the Evaluate Model as shown in the following figure:
Click through for the process.
Comments closedTomaz Kastrun laughs at 24-day advent calendars:
In the last two days we have focused on understanding Apache Spark through performance tuning and through troubleshooting. Both require some deeper understanding of Spark and Azure Databricks, but gives also a great insight to all who will need to improve performance and work with Spark.
Today, I would like to list couple of additional Learning material, documentation and any other additional resources for further exploration on Azure Databricks.
Click through for links to additional resources on Apache Spark and Databricks, as well as the other 30 entries in the series.
Comments closedChris Webb shows you how you can include KQL query fragments in Power Query:
If the title wasn’t enough to warn you, this post is only going to be of interest to M ultra-geeks and people using Power BI with Azure Data Explorer – and I know there aren’t many people in either group. However I thought the feature I’m going to show you in this post is so cool I couldn’t resist blogging about it.
Limited in its utility, but still quite interesting.
Comments closedMeagan Longoria needs to move some log data around:
For this project, we have several Azure SQL Databases configured to send logs and metrics to a Log Analytics workspace. You can execute KQL queries against the workspace in the Log Analytics user interface in the Azure Portal, a notebook in Azure Data Studio, or directly through the API. The resulting format of the data downloaded from the API leaves something to be desired (it’s like someone shoved a CSV inside a JSON document), but it’s usable after a bit of parsing based upon column position. Just be sure your KQL query actually states the columns and their order (this can be done using the Project operator).
Click through for an example of moving this resultant data into Azure Storage.
Comments closedHasan Savran explains the purpose of the Cosmos DB Analytics Storage Engine:
Analytics storage uses Column Store format to save your data. This means data is written to disk column by column rather than row by row. This makes all aggregation function run fast because disk does not need to work hard to find data row by row anymore. Cosmos DB takes responsibility to move data from Transaction Store to Analytical Store too. You do not need to write any ETL packages to accomplish this. That means you do not need to figure out which data needs to update, which data should be deleted. Azure Cosmos DB figures all data for you, syncs the data between these two storage engines. This gives us the isolation we have been looking for between transactional and analytical environments. Data written to transactional storage will be available in Analytical Storage less than 5 minutes. In my experience, it really depends on the size of the database, if you have a smaller database usually data becomes available in Analytical Storage in less than a minute.
This makes the data easy to ingest into Azure Synapse Analytics, for example.
Comments closedPaul Andrew differentiates two form of pipeline activity:
Firstly, you might be thinking, why do I need to know this? Well, in my opinion, there are three main reasons for having an understanding of internal vs external activities:
1. Microsoft cryptically charges you a different rate of execution hours depending on the activity category when the pipeline is triggered. See the Azure Price Calculator.
2. Different resource limitations are enforced per subscription and region (not per Data Factory instance) depending on the activity category. See Azure Data Factory Resource Limitations.
3. I would suggest that understanding what compute is used for a given pipeline is good practice when building out complex control flows. For example, this relates to things like Hosted IR job concurrency, what resources can connect to what infrastructure and when activities may might become queued.
Paul warns that this is a dry topic, but these are important reasons to know the difference.
Comments closedDavid Smith has a demo for us:
It’s important to note that the model prediction is not being generated by the Shiny app: rather, it’s being generated by an Azure Function running R in the cloud. That means you could integrate the model estimate into any application written in any language: a mobile app, or an IoT service, or anything that can call an HTTP endpoint. Furthermore, you don’t need to worry how many apps are running or how often estimates will be requested by the app: Azure Functions will automatically scale to meet the demand as needed.
Read the whole thing. Given that R isn’t naturally supported by Azure Functions, I think this is quite interesting.
Comments closedNick Edwards shows off the Switch activity in Azure Data Factory:
Prior to the switch statement I could achieve this using 4 ‘IF’ activities connected to a lookup activity as shown in the snip below using my ‘Wait’ example pipeline.
However a neater solution is to use the ‘Switch’ activity to do this work instead. I’ll now jump straight into a worked example to show you how I achieved this.
Click through for the demo.
Comments closedThere are couple of things you need to know about them. First, User-defined functions are only for reading data. User-defined functions will always require more Request Units than regular SQL queries. You should always try to solve your application logic problem with regular queries first. Get familiar with system functions, be sure that you are not trying to write a user-defined function when there is already a system function solving the same problem. System functions will always use less Request Units than your custom user-defines function. Just like SQL Server, User-defined functions will cause a table-scan in Cosmos DB. That is why they cost more than regular queries. If you want to use the User-defined function in a where clause. Try to filter by other properties too. Other properties might hit to indexes and that will help you with request units.
Click through to see an example of them in action.
Comments closed