A Fine Slice Of SQL Server
Kevin Chant deploys some Synapse pipelines:
In this post I want to cover how you can automate a pipeline migration to a Synapse workspace using Azure DevOps. As a follow up to a previous post I did about one way to copy an Azure Data Factory pipeline to Synapse Studio.
Because even though the post is good it deserves a follow up showing an automated way of doing it. I wanted to show that it can be done more gracefully.
And we all want to be graceful, right?
Comments closedAndy Leonard has an announcement:
The very first data integration / data engineering framework I ever wrote was for Data Transformation Services, or DTS. The DTS framework had one job: manage connections. I don’t recall all the details, but I remember DTS included a task that allowed packages to retrieve settings from INI files. INI files are key-value files, so I simply added entries with identical keys and different values – values that matched connection strings for each lifecycle tier – and placed each version of the INI file in the same location on every server in the lifecycle.
The next framework I wrote was for SSIS. I stored metadata in tables – including connections metadata – and created a concept I called an SSIS Application. An SSIS application is, according to my definition, a “collection of SSIS packages that execute in a pre-determined order.”
The SSIS Framework File Community Edition is very similar to this first framework, except for the connections management.
Click through to learn more about the SSIS Framework File Community Edition and check it out.
Comments closedRayis Imayev has a trick question for us:
Can I create a CI/CD pipeline to deploy Python Function to Azure Function App using Windows self-hosted Azure DevOps agent?
My short answer to this question is Yes and No. Yes, you can use Windows self-hosted Azure DevOps agent to deploy Python function to the Linux based Azure Function App; and, No, you can’t use Windows self-hosted Azure DevOps agent to build Python code since it will require collection/compilation/build of all Python-depended libraries on a Linux OS platform.
Click through for the full answer.
Comments closedAndy Leonard has a four-parter four us on database solutions in SQL Server Management Studio. Part one provides an introduction:
I like Microsoft Visual Studio a lot. I know some members of the team that developed Visual Studio, and they are scary-smart individuals who have forgotten more about developing software than I will ever know.
For some reason, I am not fond of SQL Server projects in Visual Studio. I believe the reason is that I am not familiar with the template. Please note I used the word fond intentionally. It’s an emotion. In this case, it’s all about me. I believe my emotion would change if I took the time to learn more about the Visual Studio SQL Server project template.
I continue to attempt to learn VS database projects. In the meantime, I prefer SQL Server Management Studio solutions.
Part two shows how to add a new query:
One solution is to add instrumentation to T-SQL scripts. I personally like to write T-SQL scripts that idempotent (a fancy way to describe “re-executable with the same results”). One way to write idempotent T-SQL is:
1. First check for the current state
2. Provide feedback (instrumentation) on the status
3. Provide more feedback on actions driven by the status (yep, more instrumentation)
Part three includes tables and views in the mix:
Click the “New Query” button in SSMS and add the following T-SQL:
Part four includes stored procedures:
Note the DDL to manage stored procedures is very similar to the DDL for managing views.
If all goes according to plan, the first execution of the s.i DDL T-SQL statement should generate the following messages:
Andy also shows how to use SQLCMD to create a proper deployment script.
Comments closedRajendra Gupta builds an ARM template:
The Azure Resource Manager (ARM) template is a JavaScript Object Notation (JSON) file for deploying Azure resources automatically. You can use a declarative syntax to specify the resources, their configurations. Usually, if you need to deploy Azure resources, it might be a tiring experience of navigating through different services, their configurations. With the ARM templates, you no longer need to click and navigate around the portal. For example, you can use configure the template for Azure VM or Azure SQL Database deployment.
Click through for a step-by-step walkthrough. I will say, though, that I tend heavily to revise ARM templates the Azure Portal creates. They tend to make everything parameters, to the point where you get inundated with context-free decisions.
Comments closedHiram 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 closedLauren Mullennex, et al, build out some pipelines:
Machine learning operations (MLOps) are key to effectively transition from an experimentation phase to production. The practice provides you the ability to create a repeatable mechanism to build, train, deploy, and manage machine learning models. To quickly adopt MLOps, you often require capabilities that use your existing toolsets and expertise. Projects in Amazon SageMaker give organizations the ability to easily set up and standardize developer environments for data scientists and CI/CD (continuous integration, continuous delivery) systems for MLOps engineers. With SageMaker projects, MLOps engineers or organization administrators can define templates that bootstrap the ML workflow with source version control, automated ML pipelines, and a set of code to quickly start iterating over ML use cases. With projects, dependency management, code repository management, build reproducibility, and artifact sharing and management become easy for organizations to set up. SageMaker projects are provisioned using AWS Service Catalog products. Your organization can use project templates to provision projects for each of your users.
In this post, you use a custom SageMaker project template to incorporate CI/CD practices with GitLab and GitLab pipelines. You automate building a model using Amazon SageMaker Pipelines for data preparation, model training, and model evaluation. SageMaker projects builds on Pipelines by implementing the model deployment steps and using SageMaker Model Registry, along with your existing CI/CD tooling, to automatically provision a CI/CD pipeline. In our use case, after the trained model is approved in the model registry, the model deployment pipeline is triggered via a GitLab pipeline.
Click through for the step-by-step guide on how to do this.
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 closedThe Hadoop in Real World team shows how you can generate the DDL to create an existing Hive table:
If you have worked with tools like Toad, DB Visualizer or SQL Server Management Studio you know it is quite easy to select an existing table and create a DDL script for the table to get the create script. How do we do the same or get the DDL or create script of an existing Hive table?
The syntax is quite similar to MySQL.
Comments closedKevin Chant doesn’t have time for manual deployments:
I want to cover one way you can do CI/CD for Azure Synapse Analytics serverless SQL pools using Azure DevOps in this post. Because I know it is a popular topic.
It’s related to my post about how you can create a dacpac for an Azure Synapse Analytics dedicated SQL pool using Azure DevOps. Since they are both based in the same service.
Plus, a while ago I wrote about the increase in demand for Data Platform automation. So, I really wanted to do a post about how you can do CI/CD for Azure Synapse Analytics serverless SQL pools.
Read on to learn how.
Comments closed