Data and Azure links you don’t want to miss

Comments 0

Share to social media

Browser tabs hold some similarities with tribbles in the way they multiply. Before you can notice, you have hundreds of them. However, in the same way tibbles are fluffy, browser tabs sometimes hold fluffy information as well, the kind of information you know you need but usually you are not sure where to hold it and may not be able to find it again.

An interesting solution is share with all of you the best links open when my desktop ship get too floaded with them and transport the other ones to a klingon ship. Let’s see what I have today:

Synapse Analytics

Many CTAS Sintaxes with the table configuration

This link has many CTAS (Create Table as Select) syntax examples, not only with simple queries but aggregations and more, besides also many different distribution examples.

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-develop-ctas?WT.mc_id=DP-MVP-4014132

Parquet to SQL Type Mapping

Synapse SQL Pool is very demanding about parquet data types. This link has details about the parquet data type mapping to SQL. However, there is something even better on this link: The address and query samples for public blob storages with data about the pandemic and the NYC Taxi sample

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/query-parquet-files?WT.mc_id=DP-MVP-4014132

 

Parquet complex types

This link is a blog post with interestingĀ  examples of parquet complex type, using a public blob storage for that

https://techcommunity.microsoft.com/t5/azure-synapse-analytics/easily-load-complex-data-types-with-azure-synapse-analytics/ba-p/1479676?WT.mc_id=DP-MVP-4014132

COPY INTO and public example of PARQUET complex types

This is the documentation of the COPY INTO statement. Unlinke CTAS, in this case we can import data into existing table. COPY INTO also is extremely flexible in relation to the loading data options

https://docs.microsoft.com/en-us/sql/t-sql/statements/copy-into-transact-sql?view=azure-sqldw-latest&WT.mc_id=DP-MVP-4014132

External tables and URI format reference

There are many links about creating external tables, but this one is special: on this link you will find a very interesting explanation about the LOCATION setting, including what prefix you should use with each kind of blob storage

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=sql-pool&WT.mc_id=DP-MVP-4014132

More URI format reference for external tables

This other link contains reference for many kinds for external sources, Oracle, Teradata and more

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=sql-server-ver15&WT.mc_id=DP-MVP-4014132

Create external table as select

This is the reverse of the most famouse cousing, CTAS. Instead of loading data, we can use this syntax to export data.

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-table-as-select-transact-sql?view=aps-pdw-2016-au7&WT.mc_id=DP-MVP-4014132

Storage access with credentials

This link contains details about how to access a private blog using SQL On demand and many different credentials: SAS, user identity and managed identity

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-storage-files-storage-access-control?tabs=shared-access-signature&WT.mc_id=DP-MVP-4014132

 

Azure Monitor, Log Analytics and more

Resource Manager Templates for Log Analytics

This link explain in details how to build an ARM template to create and configure a log analytics workspace. Each piece of configuration is explained, I used this link to help in automate the export of log analytics workspace to ARM templates. I will write about this later.

https://docs.microsoft.com/en-us/azure/azure-monitor/samples/resource-manager-workspace?WT.mc_id=DP-MVP-4014132

Application Insight Workbooks

This link is a github repo full of workbooks built to provide management information about many azure services

https://github.com/microsoft/Application-Insights-Workbooks

Resource Manager Templates for Workbooks

This link explains how to create an ARM template to install an Azure Monitor Workbook. Specially the type property is not so easy to find in documentations

https://docs.microsoft.com/en-us/azure/azure-monitor/samples/resource-manager-workbooks?WT.mc_id=DP-MVP-4014132

Programatically Manage Workbooks

This link explains how to create an ARM template to install an Azure Monitor Workbook template. The template will be available for the creation of new workbooks from it.

https://docs.microsoft.com/en-us/azure/azure-monitor/platform/workbooks-automate?WT.mc_id=DP-MVP-4014132

 

Powershell

Creating ARM templates involves manipulating JSON files. While building some script to manipulate and create ARM templates, I had the challenge to manipulate custom objects, creating or removing properties on the fly. These are references I found about it.

Remove a Powershell object property
https://lazywinadmin.com/2017/06/Remove_PSObject_Property.html

Add-Member
https://ss64.com/ps/add-member.html
https://community.idera.com/database-tools/powershell/ask_the_experts/f/learn_powershell_from_don_jones-24/13052/adding-new-values-to-a-psobject
https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/add-member?view=powershell-7&WT.mc_id=DP-MVP-4014132

Array of Custom Objects
https://stackoverflow.com/questions/17353797/how-to-initialize-an-array-of-custom-objects/17354800

Add Many properties
https://stackoverflow.com/questions/51620654/can-i-create-a-custom-powershell-object-with-an-array-property
https://stackoverflow.com/questions/36200749/how-do-you-add-more-property-values-to-a-custom-object

Real-Time Twitter sentiment Analysis in Azure Stream Analytics
https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-twitter-sentiment-analysis-trends?WT.mc_id=DP-MVP-4014132

 

Billing

Billing Permissions on Azure

You can delegate the task to manage billing information and maybe you should (I hate billing). These two links help with this, explaining how to give access to another user to manage billing and what are the existing roles in a customer agreement scenario

https://docs.microsoft.com/en-us/azure/cost-management-billing/costs/assign-access-acm-data?WT.mc_id=DP-MVP-4014132

https://docs.microsoft.com/en-us/azure/cost-management-billing/manage/understand-mca-roles?WT.mc_id=DP-MVP-4014132

Azure Comsumption Insights

This link explains how to user Power BI to access Azure Comsumption Insights API to manage Enterprise Agreement azure subscriptions

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-connect-azure-consumption-insights?WT.mc_id=DP-MVP-4014132

Azure Cost Management App

This link explains how to use Power BI Azure Cost Management Application to manage Azure billing. This application is exclusive for enterprise agreement subscriptions

https://docs.microsoft.com/en-us/azure/cost-management-billing/costs/analyze-cost-data-azure-cost-management-power-bi-template-app?WT.mc_id=DP-MVP-4014132

 

Azure Enterprise Portal

The enterprise portal is used by companies with Enterprise Agreement contract. On this link you can learn details about the enterprise portal.

https://docs.microsoft.com/en-us/azure/cost-management-billing/manage/ea-portal-get-started#create-another-enterprise-administrator#?WT.mc_id=DP-MVP-4014132

Azure Enterprise Roles

The roles used by companies with Enterprise Agreement contract are different than the roles used by companies with customer agreement. The link below explain these roles

https://docs.microsoft.com/en-us/azure/cost-management-billing/manage/understand-ea-roles?WT.mc_id=DP-MVP-4014132

Load comments

About the author

Dennes Torres

See Profile

Dennes Torres is a Data Platform MVP and Software Architect living in Malta who loves SQL Server and software development and has more than 20 years of experience. Dennes can improve Data Platform Architectures and transform data in knowledge. He moved to Malta after more than 10 years leading devSQL PASS Chapter in Rio de Janeiro and now is a member of the leadership team of MMDPUG PASS Chapter in Malta organizing meetings, events, and webcasts about SQL Server. He is an MCT, MCSE in Data Platforms and BI, with more titles in software development. You can get in touch on his blog https://dennestorres.com or at his work https://dtowersoftware.com