Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

Do Matillion have a Split component?

My destinations:
CREATE TABLE TBL_1
(DT DATETIME );
CREATE TABLE TBL_2
(INTG INT );
CREATE TABLE TBL_3
(TXT VARCHAR(30));
Source (one file, no header, pipe delimiter):
TYPE1| 2018-08-13 00:00:00
TYPE2|1234
TYPE1| 2018-07-17 00:00:00
TYPE3|SOME TEXT
Job will read the file and create three different flow. Each flow should go to the corresponding table.
The first field has hardcoded type information. “TYPE1” should go to TBL_1, “TYPE2” should go to TBL_2 and TYPE3 should go to the TBL_3 table.

7 Community Answers

Matillion Agent  

Ian Funnell —

Hi Dmitriy,

Yes, Matillion certainly does have Split components:

So you need to load your data into a table with two columns: type and date. Then add a Transformation job with three different vertical filters going into those three different tables.

Best regards,
Ian


Dmitriy (Consultant) Burtsev —

Lets say TBL_1 has 20 columns, TBL_2 has 50 columns and TBL_3 has 150 columns. All columns are different types. You recommend create a table with 150 text columns and then use a Transformation job with vertical filters?
Thank you,
Dmitriy


Matillion Agent  

Kalyan Arangam —

Hi Dmitry,

Please see following image of what your Transformation jobs may look like

https://s3-eu-west-1.amazonaws.com/mtln-stage-bucket/breakout+into+own+tables.png

Job-layout

STEP 1 starts with a source table with just one column that has relevant data. Use a calculator to create a new column that has the relevant TYPE for each row. The use a Filter for each TYPE and persist results to a respective tables. A REWRITE TABLE may be suitable for the last step.

STEP 2. You may repeat this for each table you create in STEP1.
Start with a TYPE-table, use SPLIT component to break out into relevant columns and write to the relevant target table.

I haven’t tried this but If you may be able to get away with just a single transformation job for STEP2 which is parameterised using Grid variables with relevant metadata.

Hope that helps.

Best
Kalyan


Dmitriy (Consultant) Burtsev —

In my case step 1 will be create a table with only one text column,
step 2 is to copy data from S3 file into this table and use it as a source for yours STEP 1

Is it possible to use file as source instead of table?


Matillion Agent  

Paul Johnson —

Hi Dmitriy,
You can use an External table as a source which is technically a file(s) in s3 or you can use an s3 load component to load the files from s3 into a table in an orchestration job.

Regards,
Paul


Dmitriy (Consultant) Burtsev —

Thank you


Matillion Agent  

Dan D'Orazio —

Hi Dmitriy -

We’re very happy to help. Let us know if you need anything else.

Best -
Dan

Post Your Community Answer

To add an answer please login