A Fine Slice Of SQL Server
Michael Rys has a large batch of updates to Azure Data Lake and the U-SQL language:
Here are the March 9 2017 Updates for Azure Data Lake U-SQL and Developer Tooling!
The main take away is that we continue the deprecation of items that we changed during the preview phase and introduce a lot of new capabilities including
PIVOT/UNPIVOTmore catalog sharing and much more!
There’s a pretty hefty list of updates to check out.
Comments closedGopal Wunnava shows how to run R scripts using Amazon Athena as a data source:
Next, you’ll practice interactively querying Athena from R for analytics and visualization. For this purpose, you’ll use GDELT, a publicly available dataset hosted on S3.
Create a table in Athena from R using the GDELT dataset. This step can also be performed from the AWS management console as illustrated in the blog post “Amazon Athena – Interactive SQL Queries for Data in Amazon S3.”
This is an interesting use case for Athena.
Comments closedThis is a quick post to share how we can use the coalesce operator in Azure DocumentDB (which is a schema-free, NoSQL database) to handle situations when the data structure varies from file to file. Varying data structure is a common issue in big data and analytics projects. A schema-free database like DocumentDB allows us to ingest and store the data with varying structures without a lot of upfront effort. However, accommodating these varying data structures is challenging later when we want to analyze the data. When querying the data (think Schema on Read here), I do need to impose a consistent structure on the data to perform analytics.
Read the whole thing.
Comments closedMeagan Longoria builds Azure Data Factory pipelines using BimlScript:
The great thing about Biml is that I can use it as much or as little as I feel is helpful. That T-SQL statement to get column lists could have been Biml, but it didn’t have to be. The client can maintain and enhance these pipelines with or without Biml as they see fit. There is no vendor lock-in here. Just as with Biml-generated SSIS projects, there is no difference between a hand-written ADF solution and a Biml-generated ADF solution, other than the Biml-generated solution is probably more consistent.
And have I mentioned the time savings? There is a reason why Varigence gives out shirts that say “It’s Monday and I’m done for the week.”
Click through for the script.
Comments closedSaveen Reddy explains how to build a custom Python library and use it with U-SQL:
First, let’s talk about “zipimport”. Thanks to the adoption of PEP 273 – Python had the ability to import modules from ZIP files since Python 2.3. This ability is called “zipimport” and is a built-in feature of the Python’s existing import statement. Read the zipimport documentation now.
To review the basics.
You create a module (a .py file, etc.)
ZIP up the module into a .zip file
Add the path to the .zip file to sys.path
Then import the module
Read on for the step-by-step process.
Comments closedHDInsight clusters consist of several virtual machines (nodes) serving different purposes. The most common architecture of an HDInsight cluster is – two head nodes, one or more worker nodes, and three zookeeper nodes.
Head nodes: Hadoop services are installed and run on head nodes. There are two head nodes to ensure high availability by allowing master services and components to continue to run on the secondary node in the event of a failure on the primary. Both head nodes are active and running within the cluster simultaneously. Some services, such as HDFS or YARN, are only ‘active’ on one head node at any given time (and ‘standby’ on the other head node). Other services such as HiveServer2 or Hive Metastore are active on both head nodes at the same time. There are services like Application Timeline Server (ATS) and Job History Server (JHS) which are installed on both head nodes but should run only on the head node where Ambari server is running. If these components sound unfamiliar, please revisit the article on Hadoop ecosystem in HDInsight.
Read on to see the other classes of nodes HDInsight uses.
Comments closedBrent Ozar Unlimited has a couple whitepapers out about working with SQL Server in Google Compute Engine. First, Brent and Tara Kizer create an Availability Group:
In this white paper we built with Google, we’ll show you:
How to build your first Availability Group in Google Compute Engine
How to test your work with four failure simulations
How to tell whether your databases will work well in GCE
Erik Darling also has a whitepaper on performance tuning:
Relax. Have a drink. In this white paper we built with Google, we’ll show you:
How to measure your current SQL Server using data you’ve already got
How to size a SQL Server in Google Compute Engine to perform similarly
After migration to GCE, how to measure your server’s bottleneck
How to tweak your SQL Server based on the performance metrics you’re seeing
If you’re looking at GCE as a potential migratory spot, you’ve got some extra reading material.
Comments closedIf you ever need to move a copy of a SQL database in Azure across servers then here is a quick easy way.
So let’s say you need to take a copy of database called [Rack] within Subscription A that is on server ABCSQL1 and name it database [NewRack] within subscription B on server called RBARSQL1 (The SQL Servers are in totally different data centers too).
Read on for the answer.
Comments closedMeagan Longoria has a multi-part series on using Biml to script Azure Data Factory tasks to migrate data from an on-prem SQL Server instance to Azure Data Lake Store. Here’s part 1:
My Azure Data Factory is made up of the following components:
Gateway – Allows ADF to retrieve data from an on premises data source
Linked Services – define the connection string and other connection properties for each source and destination
Datasets – Define a pointer to the data you want to process, sometimes defining the schema of the input and output data
Pipelines – combine the data sets and activities and define an execution schedule
Click through for the Biml.
Comments closedAmit Kulkarni shows how to install Azure Data Lake Store support on your “older” Hadoop clusters:
How old is really old?
The Azure Data Lake Store binaries have been broadly certified for Hadoop distributions after 3.0 and above. We are really in uncharted territory for lower versions. So the farther away you go from 3.0 the higher the likelihood of them not working. My personal recommendation is to go no lower than 2.6. After that your mileage may really vary.
This is a good article, and do check it out. A very small mini-rant follows: Hadoop version 2.6 is not old. Nor is 2.7. 2.7 is the most recent production-worthy branch and 3.0 isn’t expected to go GA until August.
Comments closed