Gerhard Brueckl has a Powershell module for interacting with Databricks, either Azure or AWS:
As most of our deployments use PowerShell I wrote some cmdlets to easily work with the Databricks API in my scripts. These included managing clusters (create, start, stop, …), deploying content/notebooks, adding secrets, executing jobs/notebooks, etc. After some time I ended up having 20+ single scripts which was not really maintainable any more. So I packed them into a PowerShell module and also published it to the PowerShell Gallery (https://www.powershellgallery.com/packages/DatabricksPS) for everyone to use!
This looks like a pretty good module if you work with Databricks.
Dinakar Nethi announces a new public preview of SQL Server Management Studio 18:
We are very excited to announce the release of Public Preview 5 of SQL Server Management Studio (SSMS) 18.0. This release has a number of new features and capabilities and several bug fixes across SQL Server Management Objects (SMO), UI, etc.
You can download SSMS 18.0 Public Preview 5 here.
The most interesting thing in it for me is probably the menu item for CREATE OR ALTER with scripts.
Joey D’Antoni points out a potential big performance problem with clustered columnstore indexes:
In the last year or so, with a large customer who makes fairly heavy use of this pattern, I’ve noticed another concern. Sometimes, and I can’t figure out what exactly triggers it, the execution plan generated, will do a seek against the nonclustered index and then do a key lookup against the columnstore as seen below. This is bad for two reasons–first the key lookup is super expensive, and generally columnstores are very large, secondly this key lookup is in row execution mode rather than batch and drops the rest of the execution plan into row mode, thus slowing the query down even further.
Joey also has a UserVoice item as well, so check it out.
Max Trinidad shows us how to grab the latest version of Powershell Core if you aren’t using Windows:
So, if PowerShell Core isn’t available in the package repository, with a few steps you can download and install PowerShell. But, the first thing I do is to remove it before installing.
Ubuntu
## - When PowerShell Core isn't available in their repository: (download and execute install) cd Downloads wget https://github.com/PowerShell/PowerShell/releases/download/v6.1.1/powershell_6.1.1-1.ubuntu.18.04_amd64.deb sudo dpkg -i powershell_6.1.1-1.ubuntu.18.04_amd64.deb ## - When available in Apt/Apt-Get repository: sudo apt install -y powershell #-> Or, powershell-preview
Click through for demos of CentOS (or any other yum-based system) and MacOS X.
Imke Feldmann points out a problem with trying to use Power BI to generate financial reports:
Although this might not be what the inventors of Power BI had in mind, large lots of folks are trying to create classical financial statements in it. And putting aside the afford that might go into getting the numbers right, there is still a major drawback to swallow:
Click through for a depiction of the problem and then go vote for this on Power BI Ideas.
Tom Norman shares a case study of using Query Store to fix a nasty implicit conversion problem:
A while ago, we contracted with a third party to start using their software and database with our product. We put the database in Azure but within a year, the database grew to over 250 gigs and we had to keep raising the Azure SQL Database to handle performance issues. Due to the cost of Azure, a decision was made to bring the database back on-premise. Before putting the database on the on-premise SQL Server, the server was running with eight CPUs. In production, we are running SQL Server 2016 Enterprise Edition. When we put the vendor database into production, we had to dramatically increase our CPUs in production, ending up with twenty-eight CPUs. Even with twenty-eight CPUs, during most of the production day, CPUs were running persistently at seventy-five percent. But why?
Tom takes us from symptom (high CPU utilization) to diagnosis and is able to provide the third-party vendor enough information to improve their product.
Frank Gill shows how to migrate a database from on-premises to an Azure SQL Managed Instance:
If you have run through my last Managed Instance blog post, you have a Managed Instance at your disposal. The PowerShell script for creating the network requirements also contains steps to create an Azure VM in a different subnet in the same VNet. Unless you have a site-to-site VPN or Express Route between your on-prem environment and Azure, you will use this VM to connect to your Managed Instance.
Install Management Studio on the Azure VM. To connect to your Managed Instance, you will need the host name for your Managed Instance. You can find the Managed Instance host name on the resource page for your Managed Instance in the Portal.
I think this migration story is a bit easier for DBAs than the old Azure SQL Database strategy of building dacpacs.
Robin Moffatt goes into great detail on Apache Kafka Connect converters and serialization techniques:
Kafka Connect is modular in nature, providing a very powerful way of handling integration requirements. Some key components include:
- Connectors – the JAR files that define how to integrate with the data store itself
- Converters – handling serialization and deserialization of data
- Transforms – optional in-flight manipulation of messages
One of the more frequent sources of mistakes and misunderstanding around Kafka Connect involves the serialization of data, which Kafka Connect handles using converters. Let’s take a good look at how these work, and illustrate some of the common issues encountered.
Read on for a good overview of the topic.
Finally I drag in the Values column and the Hierarchy in a matrix (I also turned on the +/- icons so we can expand collapse which is another November feature). The first thing we see is that the matrix looks weird with blanks and all.
The reason we are seeing this is this is how the data is set up, we have stored totals and grand totals at the lowest level in the hierarchy. Instead we want to show to them at the level where they are correct. Also we don’t want to show the aggregation created by the SUM.
Click through for the full example.
Greg Low shows how to have every Management Studio tab open in SQLCMD mode:
Note the :CONNECT command is used to connect to another server.
Because everything else works pretty much the same, and you get a whole lot of additional options, you might choose to open all your new queries in SQLCMD mode. That’s easy to do.
SQLCMD mode is one of those things where I thought I’d use it a lot, but aside from deploying database projects, I don’t. Granted, this could be a failure of imagination on my part.
Kevin Feasel
2018-11-16
Cloud, Hadoop, Powershell, Spark
No Comment