Split the column value

Help
1

I have a column value coming in like X.Y, Y,Z
and want to break this value into 2 variables using macro code in jinja

Main_value= X.Y
Var1= X Var 2=Y ( they are always sepearated by dot)

According to my understanding contains & like is not working in my macro code.

Can you please help me, It is important & urgent at same time

2

There’s not enough detail here to completely tell what you are trying to do. But I’ve taken a guess at an example based on the info you gave.
The answer is going to largely depend on what flavor of SQL you are using. But is sounds like you are trying to split on the dot character. Here is an example that works in Snowflake.
First I created a table:

create temporary table example
as
(
select 'x.y' as my_column
    union all
select 'y.x'
);

then I select from it using split

select 
split(my_column,'.')[0]::text as left_of_dot,
split(my_column,'.')[1]::text as right_of_dot
from
example;

returns this:

LEFT_OF_DOT RIGHT_OF_DOT
x y
y x

then you can store the column values in variables as shown here:

1 Like
3

I agree with @jeffsk’s code, but want to check whether you actually need to load the results into the Jinja context.

If you’re just transforming your data for output into a table, you shouldn’t load the results into jinja as it will need to be stored in memory and you run the risk of significant performance issues.

run_query is only suitable for loading small pieces of introspected data to make the query generation process easier, not for storing the result of an entire transformation query.

4

Hey both, Thanks for sharing the queries, but I am writing this in if/else condition in marco code and it doesn’t seem to work there. Any idea why?

5

The create table statement is something I did on my end so I can simulate data that you’re explaining you already have. It wasn’t intending for you to also create the table because you said you already had data that look like that.

6

Making a macro which took a column and split on the dots would look something like this:

{% macro split_on_period(column_name) %}
  split({{ column_name }}, '.')[0]::text as left_of_dot,
  split({{ column_name }}, '.')[1]::text as right_of_dot
{% endmacro %}

and then you’d call it as

select 
  id, 
  {{ split_on_period('column_containing_period') }},
  some_other_column
from {{ ref('my_table') }}

which would render to

select 
  id, 
  split(column_containing_period, '.')[0]::text as left_of_dot,
  split(column_containing_period, '.')[1]::text as right_of_dot,
  some_other_column
from database.schema.my_table

Check out Jinja and macros | dbt Developer Hub for more info on creating and using macros

7 
{% macro checknum(hivetab) %}
    {%- set src_db=dbt.split_part(string_text=dbt.string_literal(hivetab),delimiter_text="'.'",part_number=1) -%}}
    {%- set src_tb=dbt.split_part(string_text=dbt.string_literal(hivetab),delimiter_text="'.'",part_number=2) -%}}
{% endmacro %}
1 Like
8

Thanks a ton yipiantian2023 :+1: