Return query from postgreSQL into Talend Flow

Highlighted
Six Stars

Return query from postgreSQL into Talend Flow

"
SELECT 2 AS rowtype
     , source AS "TrafficTypes_Name"
     , COUNT(source) AS "Traffic"
     , to_char(week,'MM/dd/yyyy') AS "Week_Ending"
  FROM amazon.infocentertracker
  where project_id = 'LFC'
GROUP 
    BY source
     , to_char(week,'MM/dd/yyyy')
UNION ALL
SELECT 1 
     , 'Visitor Center Walk-ins'
     , COUNT(source)
     , to_char(week,'MM/dd/yyyy') as week 
  FROM amazon.infocentertracker
  where project_id = 'LFC'
GROUP 
    BY to_char(week,'MM/dd/yyyy')
ORDER 
    BY "Week_Ending"
     , rowtype
"

I am using tpostgresqlInput to return results of the above query into Talend but it throws an error saying "Syntax Error On token, delete these tokens."

 

Is tpostgreSQLinput the right component to use for such instances.

 

Interestingly enough, when I do "select * from amazon.infocentertracker", and hit Guess Schema tpostgreSQLinput returns the schema.

 

Any insights would be appreciated.


Accepted Solutions
Highlighted
Sixteen Stars
Sixteen Stars

Re: Return query from postgreSQL into Talend Flow

You don't need the () but you have to change to remove " after infocentertracker in your query.

Got it?

 


TRF

View solution in original post


All Replies
Highlighted
Sixteen Stars
Sixteen Stars

Re: Return query from postgreSQL into Talend Flow

Your SQL query contains " inside which is not allowed here except if you protect them by an "\".

Replace 

"Traffic"

by

\"Traffic\"

 and so on

 


TRF
Highlighted
Six Stars

Re: Return query from postgreSQL into Talend Flow

Thanks TRF for the rapid response, now it states Syntax Error, insert ")" to complete MethodInvocation. It does not work even after I inserted the ().

 

"
(SELECT 2 AS rowtype
     , source AS \"TrafficTypes_Name\"
     , COUNT(source) AS \"Traffic\"
     , to_char(week,'MM/dd/yyyy') AS \"Week_Ending\"
  FROM amazon.infocentertracker"
  where project_id = 'LFC'
GROUP 
    BY source
     , to_char(week,'MM/dd/yyyy')
UNION ALL
SELECT 1 
     , 'Visitor Center Walk-ins'
     , COUNT(source)
     , to_char(week,'MM/dd/yyyy') as week 
  FROM amazon.infocentertracker
  where project_id = 'LFC'
GROUP 
    BY to_char(week,'MM/dd/yyyy')
ORDER 
    BY \"Week_Ending\"
     , rowtype);
"
Highlighted
Sixteen Stars
Sixteen Stars

Re: Return query from postgreSQL into Talend Flow

You don't need the () but you have to change to remove " after infocentertracker in your query.

Got it?

 


TRF

View solution in original post

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog