Press "Enter" to skip to content

Author: Kevin Feasel

Columnar File Formats in Hadoop

Matthew Rathbone gives us an overview of the benefits behind the ORC and Parquet file formats:

People throw this term around a lot, but I don’t think it is always clear exactly what this means in practice.

The textbook definition is that columnar file formats store data by column, not by row. CSV, TSV, JSON, and Avro, are traditional row-based file formats. Parquet, and ORC file are columnar file formats.

Read on for a comparison and example. In the SQL Server world, think columnstore versus rowstore indexes and you won’t be too far off.

Leave a Comment

Aggregations in Power BI

Shabnam Watson takes us through aggregations in Power BI:

In Power BI, Aggregations start as tables just like any other table in a model. They can be based off a view or table in the source database, or created in Power BI with Power Query. They can be in Import or Direct Query storage mode.

Once in the model, these tables can be configured so that the engine can use them instead of a detail table to answer queries when possible. The process of creating and configuring aggregations in Power BI is significantly easier than the process of creating aggregations in SSAS multidimensional.

Once an aggregation table is configured, it becomes hidden from end users. Report developers and end users don’t know that it exists and don’t need to change anything in how they query the dataset.

This was one of the key benefits to a multidimensional model. Shabnam has an excellent, detailed article here, so give it a read if you are a Power BI developer.

Leave a Comment

Decomposition Trees in Power BI

Tomaz Kastrun takes us through a new visual in Power BI:

Decomposition tree is a data presentation of slicing and dicing of selected metrics based on the attributes of these metrics or with combination of other metrics. Another great aspect of this visual is to analyze the selected variable with many metrics or attributes (dimensions) as the same time.

It’s not the type of visual I’d want to see on a dashboard, but I can see it as quite useful in exploratory data analysis.

Leave a Comment

Profiling Hive Jobs on Tez

Dmitry Tolpeko takes us through Hive query diagnostics:

I was asked to diagnose and tune a long and complex ad-hoc Hive query that spent more than 4 hours on the reduce stage. The fetch from the map tasks and the merge phase completed fairly quickly (within 10 minutes) and the reducers spent most of their time iterating the input rows and performing the aggregations defined by the query – MIN, SUM, COUNT and PERCENTILE_APPROX and others on the specific columns.

After the merge phase a Tez reducer does not output many log records to help you diagnose the performance issues and find the bottlenecks. In this article I will describe how you can profile an already running Tez task without restarting the job.

Click through for the process, as well as the root cause of the problem.

Leave a Comment

Why the DBA is Important

Melody Zacharias takes us through five areas where DBAs are important in the SQL Server 2019 world:

Databases are the beating heart of digital transformation. Businesses increasingly realize that having a unified view gives them a competitive advantage in a world where data is king. The task of breaking down those silos will fall to highly skilled DBAs using cool new technologies such as PolyBase [https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-guide?view=sql-server-ver15]. Although it was introduced in SQL Server 2016, PolyBase got a whole lot more interesting in SQL Server 2019 with the ability to query external SQL Server, Oracle, Teradata, and MongoDB using T-SQL. Our world just got a whole lot bigger!

Read on for the full set of reasons. My agreement with this comes with one caveat: DBAs are important insasmuch as they are willing to grow, try new things, and develop skills. If you’re a stodgy type who hasn’t learned a thing since SQL Server 2008, you’ve got a shelf life.

Leave a Comment

Upgrading SQL Server 2017 Containers to 2019

Anthony Nocentino takes us through one of the big changes to SQL Server containers:

When you start up the 2017 container, the SQL Server (sqlservr) process is running as root (uid 0). Any files created by this process will have the user and group ownership of the root user. Now when we come along later and start up a 2019 container, the sqlservr process is running as the user msssql (uid 10001 by default). This new user doesn’t have permission to open the database files and other files used by SQL Server.

Read on to see how Anthony fixed this.

Leave a Comment

Creating a Failover Cluster Instance with Shared Storage

Ryan Adams wraps up a video series on setting up a SQL Server lab environment:

You are going to create a SQL Server Failover Cluster Instance in Part 4 of our series on how to build a SQL Cluster Lab. The FCI will only be installed on Node1 and Node2. FCIs require shared storage so you will make your domain controller an iSCSI target. Last you will create your FCI using the iSCSI drives you presented to the cluster. 

Click through for links to the entire series.

Leave a Comment

T-SQL Tuesday 120 Round-Up

Wayne Sheffield summarizes T-SQL Tuesday #120:

The end of the first 10 years of T-SQL Tuesday blogging occurred this month, with me hosting T-SQL Tuesday #120. The theme this month was to talk about something you’ve seen that made you think “What were you thinking?” (you can read the invitation here). We had several bloggers jump in and post their thoughts. So let’s just jump into a quick recap of who posted what (for each blogger, I also include a link to their Twitter account, their main blog, and the link to their T-SQL Tuesday #120 post).

Click through for the recap.

Leave a Comment