Greenplum PXF EXTERNAL READABLE TABLE using pre-defined query about pxf HOT 14 CLOSED

@sree80 please modify your $PXF_CONF/servers/mydb/report.sql file with the following:

SELECT dept.name AS name, count(*) AS count, max(emp.salary) AS max
FROM demodb.dept JOIN demodb.emp
ON dept.id = emp.dept_id
GROUP BY dept.name

Please note that I removed the semicolon at the end of the query. We will take this feedback and will create a bug.

from pxf.

I tried it without the semicolon and got the same error. Thank you.

from pxf.

@sree80 I was able to get it to work. In my case I used the mysql database, and I removed the db name from the table names:

SELECT dept.name AS name, count(*) AS count, max(emp.salary) AS max
FROM dept JOIN emp
ON dept.id = emp.dept_id
GROUP BY dept.name

I hope this helps

from pxf.

In a multinode cluster, you will have to run pxf cluster sync

from pxf.

Same Error , even after removing the db name from the table names.
It looks like it is complaining somewhere around this line - right syntax to use near ':report' at line 1

ERROR: remote component error (500) from '127.0.0.1:5888': type Exception report message You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':report' at line 1 description The server encountered an internal error that prevented it from fulfilling this request. exception java.io.IOException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':report' at line 1 (libchurl.c:944) (seg1 slice1 172.17.0.2:50001 pid=8466) (libchurl.c:944)
CONTEXT: External table dept_report

from pxf.

I am using MYSQL 5.1 and JDBC 5.1

from pxf.

I assume you're using the latest code, correct ? You can turn on TRACE level logging and see the exact SQL statement being sent to MySQL via JDBC in pxf-service.log file.

It looks like maybe if your code is old, the named query feature is not available and query:report is being used as a table name ?

from pxf.

@sree80 what version of Greenplum are you using? Named query feature was introduced in Greenplum 5.19, make sure you are using Greenplum 5.19 and PXF version 5.4.0

> $PXF_HOME/bin/pxf version
PXF version 5.4.0

from pxf.

PostgreSQL 9.4.20
Greenplum Database 6.0.0-beta.3
PXF version 5.3.2

from pxf.

If i have to upgrade PXF Version to 5.4.0. From where Can I find this Version.

from pxf.

You can get it with Pivotal GPDB 5.19 distribution or build from the open source, but it will not be officially supported by Pivotal with GPDB 6 (beta) until after it ships with the next beta release of Pivotal GPDB 6.

from pxf.

thanks so much.

from pxf.

Thanks for reporting this issue. We added a fix for semicolon. In your case, the query was not working because you are on PXF version 5.3.2 that does not support Named Queries

from pxf.

PXF 5.4.0 is also available at https://github.com/greenplum-db/pxf/releases/tag/5.4.0

from pxf.