Often when using grid variables in Matillion, the order of the values is important. For example, you could be iterating through the values of a grid variable sequentially using the Grid Iterator component and need the iterations to complete in a desired order. You may even have populated your grid variable using the Query Result to Grid component, which is not guaranteed to order the values in your desired structure due to how Matillion structures its SQL.

If you have run into such a situation and need to reorder the values in a grid variable, you can do so with a simple Python script.

Ordinal Position Indexing in Python

Before looking at the script, it is important to know the ordinal position of the field within the grid variable you wish to order by, as this is fed into the sort operation.

Consider the following grid variable, where I have indicated Python’s ordinal position index for each field. This indexing starts at 0 (zero), not 1, which is a standard practice among programming languages:

If desired, a more detailed explanation can be found here.

Python Component to Reorder a Grid Variable

As promised, here is the Python script. The first section allows you to consider a few variables, such as the name of your grid variable and whether to sort the values in ascending or descending order. For our example, we set the field index to be 0 (zero), so the sort will take place on the ID field from the example screenshot above:

##################################################
## Input variables

# Determine the name of the grid variable which will be sorted
matillion_grid_variable = 'MY_GRID_VARIABLE'

# Determine the ordinal position index of the field that will be used for the sort.
# Remember to start your index at 0, not 1
field_index = 0

# Determine whether the sort should be descending (True) or ascending (False)
sort_descending = True

##################################################
## Main process 

# Retrieve the current, unsorted values from the grid variable
py_my_grid_variable = context.getGridVariable(matillion_grid_variable)

# Only perform the script if the grid variable is not empty
if py_my_grid_variable is not None:
  
  # Sort the list by the desired element in the list.
  py_my_grid_variable.sort(key=lambda x: x[field_index], reverse = sort_descending)

  # Update the Matillion grid variable with the new, sorted list
  context.updateGridVariable(matillion_grid_variable, py_my_grid_variable)

If you place this piece of code into a Python Script component, you can read, sort and update the values of a grid variable.

I hope you find this short and sweet post useful. It may be the shortest post I’ve ever written!