I cant't connect with ElasticSearch from AWS

[krisnamourt]

I'm trying to test this wrapper with AWS Elastic Search My Elastic Object

 {
                "_index": "twitter",
                "_type": "_doc",
                "_id": "1",
                "_score": 1.0,
                "_source": {
                    "User": "olivere",
                    "Message": "Take Five",
                    "Retweets": 0
                }
            }

My foreing table

    CREATE FOREIGN TABLE articles_es
    (
        "User" TEXT,
        Message TEXT,
        Retweets NUMERIC
    )
SERVER multicorn_es
OPTIONS
    (
        host 'https://vpc-my-elastic-jxd4u4p3jwj4sl00ydrardrt7i.us-east-1.es.amazonaws.com',
        port '80',
        index 'twitter',
        type '',
        rowid_column 'id',
        query_column 'query',
        query_dsl 'false',
        score_column 'score',
        default_sort 'last_updated:desc',
        sort_column 'sort',
        refresh 'false',
        complete_returning 'false',
        timeout '20',
        username '',
        password ''
    )
;

I got this error

SQL Error [XX000]: ERROR: COUNT for /twitter failed: ConnectionError(<urllib3.connection.HTTPConnection object at 0x7f5373493750>: Failed to establish a new connection: [Errno -2] Name or service not known) caused by: NewConnectionError(<urllib3.connection.HTTPConnection object at 0x7f5373493750>: Failed to establish a new connection: [Errno -2] Name or service not known)

[matthewfranglen]

You've got <my url> in the host. The error is Name or service not known, so your problem is that whatever you have put in the host doesn't resolve from the postgres machine. The host is not a url, it's a hostname.

[krisnamourt]

Yeah, AWS Elasticsearch is accessed by a URL not a hostname or IP

[matthewfranglen]

Yes and the URL has a hostname or IP and possibly a port in it. Try putting those in the table settings.

Sorry if this sounds patronizing. I haven't seen one of the elasticsearch urls that AWS uses, and that URL is the source of your problem and solution. Perhaps you could include it but change the hostname slightly for confidentiality?

[krisnamourt]

@matthewfranglen I put a similar URL and also saw that on AWS I have to use port 80, but I got the same error

I'm using a postgres inside a container and I was able to use another data wrapper for mysql, below is the elastic response usning postman

[matthewfranglen]

I'm sure this can be solved. Could you provide the url here (mask the hostname if you want) that you put into postmaster or the aws provided one?

[krisnamourt]

The URL is this: https://vpc-tst-catalog-jxd4u4p3jwj4sl63ydrardrt7i.us-east-1.es.amazonaws.com

As I mention before I'm using a postgres container, this one postgres:10.16 I also deployed a local elastic using this image elasticsearch:7.10.1 I'm able to access through postman but no from my postgres.

By the way this URL just can be accessd from my vpn.

[matthewfranglen]

Can you try:

CREATE FOREIGN TABLE articles_es
    (
        "User" TEXT,
        Message TEXT,
        Retweets NUMERIC
    )
SERVER multicorn_es
OPTIONS
    (
        host 'vpc-my-elastic-jxd4u4p3jwj4sl00ydrardrt7i.us-east-1.es.amazonaws.com',
        port '443',
        scheme 'https',
        index 'twitter',
        type '',
        rowid_column 'id',
        query_column 'query',
        query_dsl 'false',
        score_column 'score',
        default_sort 'last_updated:desc',
        sort_column 'sort',
        refresh 'false',
        complete_returning 'false',
        timeout '20',
        username '',
        password ''
    )
;

[matthewfranglen]

Ooh I think you'll also need a scheme. I've added that to ^^

[krisnamourt]

Nice!!! The error changed, my Elastic doens't have user and password

SQL Error [XX000]: ERROR: COUNT for /twitter failed: AuthorizationException(403, u'{"message":"Authorization header requires \'Credential\' parameter. Authorization header requires \'Signature\' parameter. Authorization header requires \'SignedHeaders\' parameter. Authorization header requires existence of either a \'X-Amz-Date\' or a \'Date\' header. Authorization=Basic Og=="}')

By the way, using a local elastic I just have to change local host for the IP assigned for docker 172.17.0.1 and leave empty default_sort field and worked perfectly

[matthewfranglen]

if you leave out the username and password then they won't be used. Having them as empty strings is not the same as not having them.

[krisnamourt]

Cool, I removed username and password and worked, thanks!

[matthewfranglen]

I think that concludes this ticket then.