Press "Enter" to skip to content

Author: Kevin Feasel

Apache Ranger On ElasticMapReduce

Published 2016-12-06 by Kevin Feasel

Varun Rao explains role-based access control using Apache Ranger on Amazon ElasticMapReduce:

Using the HUE SQL Editor, execute the following query.

These queries use external tables, and Hive leverages EMRFS to access the data stored in S3. Because HiveServer2 (where Hue is submitting these queries) is checking with Ranger to grant or deny before accessing any data in S3, you can create fine-grained SQL-based permissions for users even though there is a single EC2 role specified for the cluster (which is used by all requests the cluster makes to S3). For more information, see Additional Features of Hive on Amazon EMR.

If your job includes securing a Hadoop cluster, this is a nice read, even if you don’t use EMR.

Comments closed

Cleaning Up SSISDB

Published 2016-12-06 by Kevin Feasel

Peter Schott extends a script to clean up the SSIS catalog database:

I really appreciate what MS has done w/ the SSIS Catalog. We have built-in logging at a level that wasn’t possible in prior releases, but that comes at a cost. The default retention is pretty high (365 days) and MS chose to handle cleanup using cascading deletes. This sort of coding makes life easier for the developers, but almost always performs poorly. That’s especially the case when you have 1 parent row with 100’s of thousands or child rows related to it.  The problem is compounded because the first time people realize they need to lower the retention limit is about the same time that the database is starting to fill up or has filled up. At that point, it’s too late to lower the retention by a large number because the corresponding delete will still cause issues.

Click through for a script which helps extricate you from sticky situations.  The ideal scenario here would be to set your retention period correctly and not have to delete rows directly, but sometimes you’re stuck in a less-than-ideal situation.

Comments closed

Data Flow Sequence Containers

Published 2016-12-06 by Kevin Feasel

Todd McDermid is excited about data flow groups in Integration Services:

Data Flow Groups

Data Flow Groups is what they’re calling it, and it’s deceptively simple to use.  One of the reasons I’m sure I (and SSIS people I talk to who DID NOT LET ME KNOW IT WAS THERE) missed it is because I was expecting it to be a component in the toolbox.  Not so.
Code up your Data Flow as you normally would.  Then go and select the components that you want to group together – via clicking and dragging a selection window, or click-selecting components.  Any component combinations you want.  Then right-click and select Group.

I admit that I didn’t know it existed either.  This does seem rather useful.

Comments closed

Multivariate Analysis In R

Published 2016-12-06 by Kevin Feasel

Mala Mahadevan looks at using R to describe data sets with two explanatory variables:

From the plot we can see that type 3 trees have the smallest circumference while type 4 have the largest, with type 2 close to type 4. We can also see that type 1 trees have the thinnest dispersion of circumference while type 4 has the highest, closely followed by type 2.  We can also see that there are no significant outliers in this data.

Understanding whether variables are categorical or continuous is vital to understanding what you can and should do with them.

Comments closed

Custom Functions In Power BI Desktop

Published 2016-12-06 by Kevin Feasel

Reza Rad explains custom functions:

Benefits of Custom Function

  • Re-Use of Code
  • Increasing Consistency
  • Reducing Redundancy

With a Custom function you are able to re-use a query multiple times. If you want to change part of it, there is only one place to make that change, instead of multiple copies of that. You can call this function from everywhere in your code. and you are reducing redundant steps which normally causes extra maintenance of the code.

I like Reza’s example of reading from a holidays table, as it’s easy enough to follow without being so trivial that it leaves you to wonder what the real value is.

Comments closed

SQL Server Easy Button

Published 2016-12-05 by Kevin Feasel

Drew Furgiuele has an easy button for SQL Server:

Sounds great, right? I bet some of you already already thinking “Oh man, I can’t wait to run the Linux version SQL Server on this thing!” There’s just one really big catch: the CPUs on Pi boards are ARM-architecture based. Unlike modern processors in our desktops and laptops, these chips are more akin to what you find in mobile phones or other small devices. It also means programs you run or write on your computer are probably 32 or 64 bit and designed for Intel or AMD processors. ARM is a completely different architecture, so we can’t upload something to it and expect it to run. Programs have to be designed for it.

Furthermore a lot of “stock” Pi operating system images are Linux based so it can be difficult to write code that interfaces with .NET or Windows-based services. Not that you can’t; you can certainly write bash scripts that make wget or curl requests.

Based on my experiences at least, nothing with Windows IoT was really that easy…  This is an intro post with a shopping list attached, so I am looking forward to Drew’s making everybody’s lives easier on a budget of $98.

Comments closed

SARGability And Date Math

Published 2016-12-05 by Kevin Feasel

Erik Darling explains performance ramifications of date math operations:

Both plans are helped by our thoughtfully named index on the OrderDate column, though the one with cheaper estimated cost is the bottom one. Yes, I know this can sometimes lie, but we’re not hiding any functions in here that would throw things off horribly. If you’re concerned about the Nested Loops join, don’t worry too much. There is a tipping point where the Constant Scan operator is removed in favor of just index access. I didn’t inflate this table to find exact row counts for that, but I’ve seen it at work elsewhere. And, yeah, the second query will still be cheaper even if it also scans.

Which side of the equation you put a function on can make all the difference.

Comments closed

Identity Not Found

Published 2016-12-05 by Kevin Feasel

Melissa Coates explains Azure Active Directory tenancy to solve an Azure Analysis Services error:

The Analysis Services product team explained to me that a a user from a tenant which has never provisioned Azure Analysis Services cannot be added to another tenant’s provisioned server. Put another way, our Corporate tenant had never provisioned AAS so the Development tenant could not do so via cross-tenant guest security.

One resolution for this is to provision an AAS server in a subscription associated with the Corporate tenant, and then immediately delete the service from the Corporate tenant. Doing that initial provisioning will do the magic behind the scenes and allow the tenant to be known to Azure Analysis Services. Then we can proceed to provision it for real in the Development tenant.

Read the whole thing.

Comments closed