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

Sql integration

Why am I not able to import .sql file to matillion.
It is giving me the following error -
Error parsing file.

9 Community Answers

Matillion Agent  

Veronica Kupetz —

Hi Manav,

Thanks for reaching out. Are you trying to import that file via the Project —> Import option? If so, that is only allowed to import Matillion jobs which are JSON files. If you want to run a sql query, you can use the following components:

SQL Orchestration Script Component. The script may contain multiple SQL statements, however since there is no way to recover the output of the script the statements should not be SELECT. See the component below for SELECT statements.
SQL Transformation Component. You can write your own complete SQL SELECT statement within this component.

Hope this helps!

Best Regards,
Veronica


Manav Veer Gulati —

Hi Veronica,
If I have .sql files in github, how do I link them to matillion?
Do I need to run a sql to json script every time I import data? Is there no direct way?

Best Regards,
Manav Veer Gulati


Matillion Agent  

Veronica Kupetz —

Hi Manav,

Unfortunately, there is no direct way to import the .sql files. They can be ran within the components I mentioned earlier. Can you give an example of what you are trying to achieve? Are these SQL files containing DML or DDL statements? Matillion helps to remove the hand coding piece of the ETL process. There are several components that can help in your data extraction, loading and transformation.

Best Regards,
Veronica


Manav Veer Gulati —

Something like this - CREATE TABLE IF NOT EXISTS data (
orchestration_jobs_components_26326_parameters_1_elements_1_values_1_slot INT NULL,
orchestration_jobs_redo_command VARCHAR(0) NULL,
orchestration_jobs_components_26327_parameters_2_elements_1_values_1_value VARCHAR(7243) NULL,
orchestration_jobs_timestamp INT NULL,
jobs_tree_children_jobs_type VARCHAR(13) NULL,
orchestration_jobs_components_26327_execution_hint VARCHAR(7) NULL,
orchestration_jobs_components_26327_parameters_1_elements_1_slot INT NULL,
version VARCHAR(6) NULL,
orchestration_jobs_components_26326_output_cardinality VARCHAR(4) NULL,
jobs_tree_name VARCHAR(4) NULL,
orchestration_jobs_created INT NULL,
orchestration_jobs_components_26326_width INT NULL,
jobs_tree_children_jobs_id INT NULL,
orchestration_jobs_components_26327_output_cardinality VARCHAR(4) NULL,
orchestration_jobs_components_26326_input_cardinality VARCHAR(4) NULL,
orchestration_jobs_components_26327_parameters_1_elements_1_values_1_type VARCHAR(6) NULL,
orchestration_jobs_undo_created INT NULL,
orchestration_jobs_redo_created INT NULL,
db_environment VARCHAR(8) NULL,
jobs_tree_children_id INT NULL)

There are other similar sql queries.


Matillion Agent  

Jason Kane —

Hi Manav,

For create table statements, we recommend using the Create Table Component. Within that component, you can set the Create/Replace Property to “Create if not exists”. Do all of your sql files contain create table statements?

Thanks,
Jason


Manav Veer Gulati —

Hi Jason,
All my files contain a mixture of DDL and DML statements. But they are all .sql files.

How do I import them in Matillion?


Matillion Agent  

Veronica Kupetz —

Hi Manav,

After reviewing and per Jason’s suggestion, I would recommend using either the Create Table Component or the SQL Script component as there is not a direct way to import those .sql files. If you have DML statements, you can use the SQL Component within Transformation jobs. I would highly recommend looking at the other various components we have to extract, load and transform your data. As mentioned earlier, Matillion helps to remove the hand coding piece of the ETL process. I know this is not ideal in your current situation, but there is going to be some work when converting to a new ETL/ELT product. If you run into any blockers during your development process, please feel free to reach out.

Best Regards,
Veronica


Manav Veer Gulati —

Hi Veronica,
We came up with a new way out :)
But we would need your help with one thing -
We would love to have access to the script which Matillion uses to convert all .sql file to json as that is the only way out for the new way.
Please I hope you can help.

Best Regards,
Manav Veer Gulati


Matillion Agent  

Paul Johnson —

Hi Manav,

Matillion generates SQL based on he component logic, this “job” is stored as a JSON object. This is core functionality of the Matillion product and we do not expose any of that to end users.
I don’t have anything to add to Veronica’s previous comment which describes the best way to migrate from hand coded SQL jobs to Matillion jobs.

Regards,
Paul

Post Your Community Answer

To add an answer please login