Is calling GRID Variable in SQL Component possible?

Matillion ETL
1

Hi, spent the last hour or so trying to find an answer online, but cant seem to find one.

 

I am brand new to using Matillion and would like to know if / how do I call a Grid variable in a SQL query? I'd like to pivot a table we are using, but instead of hard coding all the column names / values I want to PIVOT on, I'd like to use the grid variable.

 

IE:

SELECT * FROM (

  SELECT

    ["group"],

    [source],

    [value]

  FROM MyTable

)

PIVOT (

  MAX(value)

  FOR source IN ('${pol_fields_grid}') --Essentialy, this would have Name, Surname, Address, etc as values

) AS PivotTable

2

https://documentation.matillion.com/docs/2943274

 

Grid Iterator Component

3

I would recommend using a Python component directly before your SQL component, which executes the following code. This would flatten your grid variable into a single string, stored as a scalar variable in Matillion, that can be used in your SQL component. Be sure to create the empty scalar variable in the Matillion UI to store the value.

 

Also, apologies for the formatting but I can't see a clean way to enter code in the text editor in Matillion's forums.

 

# The purpose of this Python script is to convert a Matillion grid

# variable into a flattened scalar variable, that contains the values

# in the example format below, which matches that required for an

# IN statement in Matillion:

# 'VALUE_1', 'VALUE_2', 'VALUE_3', etc

 

# Define an empty python variable to store the scalar variable value.

# Defining this at the start also ensures an empty value is present to leverage

# if the various IF steps below are not triggered, avoiding errors

py_my_scalar_variable = ''

 

# Retrieve the contents of the Matillion grid variable and store in a Python variable

py_my_grid_variable = context.getGridVariable('MY_GRID_VARIABLE')

 

# Only do the rest if the grid variable is not empty, and contains more than 0 values.

# Otherwise the default, empty value of py_my_scalar_variable will persist

if py_my_grid_variable is not None:

 if len(py_my_grid_variable) > 0 :

   ## Matillion grid variables are lists of lists.

   ## Since we only want the data in the first column of the grid variable,

   ## we leverage a concept known as "list comprehension" to unpack our

   ## variable value into a standard list, only retrieving the first column.

   ## Remember that in Python, counting starts at 0, not 1.

   py_unpacked_grid_variable = [x[0] for x in py_my_grid_variable]

 

   ## Flatten our unpacked grid variable into a single string, including

   ## quotes that indicate string values in SQL.

   py_my_scalar_variable = "'" + "', '".join(py_unpacked_grid_variable) + "'"

 

# Update the Matillion scalar variable with the contents of the Python variable

context.updateVariable('MY_SCALAR_VARIABLE', py_my_scalar_variable)

4

Thank you both for the response - will take a look at adding the python component :)

5

It is worth noting that if you SQL is in a transformation job then you will need to perform this Python script in an orchestration job, then pass the value of the new variable into your transformation job

6

Thank you Chris - appreciate the effort! Will give this a go.