Press "Enter" to skip to content

Day: September 29, 2021

What is Parquet and Why Use It?

The folks at Jumping Rivers explain what the Parquet file format is and how you can use it in R:

Apache Parquet is a popular column storage file format used by Hadoop systems, such as Pig, Spark, and Hive. The file format is language independent and has a binary representation. Parquet is used to efficiently store large data sets and has the extension .parquet. This blog post aims to understand how parquet works and the tricks it uses to efficiently store data.

Read on for that explanation and plenty of sample code.

Comments closed

Error Handling in Scala

Ashish Chaudhary gives us three different ways of handling errors in Scala:

Error handling is the process of handling the possibility of failure. For example, failing to read a file and then continuing to use that bad input would clearly be problematic. Noticing and explicitly managing these errors saves the rest of the program from various pitfalls.

Exceptions in Scala work the same way as in C++ or Java. When an exception occurs, say an Arithmetic Exception then the current operation is aborted, and the runtime system looks for an exception handler that can accept an Arithmetic Exception. Control resumes with the innermost such handler. If no such handler exists, the program terminates.

Or, another way to put it is, structural programming with try/catch or functional programming via monads (Option and Either).

Comments closed

Unique Constraints vs Unique Indexes

Erik Darling calls out unique key constraints:

I do love appropriately applied uniqueness. It can be helpful not just for keeping bad data out, but also help the optimizer reason about how many rows might qualify when you join or filter on that data.

The thing is, I disagree a little bit with how most people set them up, which is by creating a unique constraint.

Data modeling Kevin wants to use unique key constraints because that’s the correct thing to do. Implementation Kevin uses unique nonclustered indexes for the reasons Erik describes. Not mentioned in Erik’s post but potentially relevant is that operations on unique nonclustered indexes can be done online, whereas unique key constraint operations (creation and alteration via drop+create) are offline.

Comments closed

Tips on using Included Columns on Indexes

Chad Callihan shares some advice:

In my previous blog post, we saw how using INCLUDE to add a column to an index can make a difference compared to a key column. Let’s do a quick overview of INCLUDE and when it should be used.

Included columns are columns that can added to an index as non-key columns. They are only added to the leaf nodes of an index and have a bit more flexibility. Having trouble adding a particular data type to an index? Included columns can be data types unable to be added as key columns. Are you possibly maxed out on index key columns? Use INCLUDE to add any necessary columns.

Read on for an example and note the warning that you shouldn’t just add all of the columns to the INCLUDE clause.

Comments closed

Create and Update Columns on Tables

Kenneth Fisher adds four columns to a table:

One of the easiest ways to collect information about table activity is to add a series of audit columns to the table. The most common set of column consists of four columns.

– When was the row created?

– Who created it?

– When was the row last updated?

– Who last updated it?

Read on to see how to create these, but definitely read the comments. Joe Celko has a good reason to avoid this style, and there’s another good reason as well: if you update the columns and the updated values are longer than what they replace, you can end up generating a lot of page splits as full pages get broken up to handle those in-page updates.

If you do need something like this, I’m growing more and more fond of an activity log table which is append-only and tracks these operations separately. That removes the page splits, allows you to deal with deletions, and gives a better idea of what happened over time for a third party. It does come at a cost if you frequently need to show the create and last updated dates (or users), but my experience has been that a vast majority of the time, we include those for internal purposes, not to display to users.

Comments closed

The Alt Key in SSMS

Barney Lawrence shows off vertical selection in SQL Server Management Studio:

I went years without knowing how useful the Alt key was, it’s not well documented but I can guarantee that once you know about it and give it a little practice you won’t be able to live without it.

While I’m filing this under SSMS Tips and Tricks but it works equally in Visual Studio, VS Code, Azure Data Studio and even Notepad ++ (but not plain old vanilla notepad). It’s worth a test in other places too.

As a quick note, it does not work quite the same way in Visual Studio Code or Azure Data Studio as in Visual Studio or SSMS: Alt+Shift+Down arrow copies the current row and pastes it in the row below. Holding Alt and Shift while using the mouse does work, but if you’re big on keyboard commands, you’ll be a bit disappointed.

Comments closed