EnterpriseDB / hdfs_fdw

PostgreSQL foreign data wrapper for HDFS
Other
135 stars 37 forks source link

Selecting fields called "timestamp" of type BIGINT does not work #65

Open tomwganem opened 5 years ago

tomwganem commented 5 years ago

Trying to understand the nature of the issue. Any help would be appreciated. I know this project hasn't gotten any recent attention, but I'm hoping to utilize this library in our production environment.

Version info: Spark SQL - version 2.4.3 Postgres - 10.9.0 Hadoop Common Library - version 2.6.4 hive-standalone-bin - version 1.0.1 hdfs_fdw - latest master

Here's my table in hive

CREATE TABLE `organization_metadata` (
  `id` INT,
  `destroyed` BOOLEAN,
  `name` STRING,
  `timestamp` BIGINT
)

Here's my table is postgres

CREATE SCHEMA analytics_cassandra;

ALTER DEFAULT PRIVILEGES IN SCHEMA analytics_cassandra GRANT SELECT ON TABLES TO postgres;

CREATE SERVER analytics_cassandra_server FOREIGN DATA WRAPPER hdfs_fdw
  OPTIONS (host 'redash-spark-hive.analytics-qa', port '10000', client_type 'spark');

CREATE FOREIGN TABLE analytics_cassandra.organization_metadata (
    id INT,
    destroyed BOOLEAN,
    name TEXT,
    timestamp BIGINT
) SERVER analytics_cassandra_server OPTIONS (dbname 'default', table_name 'organization_metadata');

Here's what happens during a SELECT *

I have no name!@redash-postgresql-0:/$ psql -U postgres
psql (10.9)
Type "help" for help.

postgres=# \c redash
You are now connected to database "redash" as user "postgres".
redash=# SELECT * FROM analytics_cassandra.organization_metadata LIMIT 1;
  id   | destroyed |         name          |      timestamp
-------+-----------+-----------------------+---------------------
 14379 | f         | None of your Business | 1552679197624386604
(1 row)

Here's what happens during a SELECT timestamp:

redash=# SELECT timestamp FROM analytics_cassandra.organization_metadata LIMIT 1;
ERROR:  invalid input syntax for integer: "timestamp"

I've tried specifying the type of the column I'm selecting, to no success

redash=# SELECT timestamp::bigint FROM analytics_cassandra.organization_metadata LIMIT 1;
ERROR:  invalid input syntax for integer: "timestamp"
tomwganem commented 5 years ago

This actually seems to be caused by having the field called "timestamp". Any BIGINT fields called "timestamp" runs into this error. Any BIGINT fields not called "timestamp" do fine.