Connector Postgres

Follow
Avatar
Ivan1981

hello in version 6.1 connector Postgres was announced, i downloaded the distribution but it is not there, is there a plan to return this connector, or even better, build in a tool to connect to any database

0

Comments

13 comments

Sort by Date Votes
  • Avatar
    Candido Dessanti

    Hi @Ivan1981,

    you can connect to Postgres using HeavyConnect, but I guess the feature is for EE Only (I'm not sure of that)

    You can find references to that feature here

    https://docs.heavy.ai/heavyconnect/heavyconnect-release-overview

    Postgres is the first database added, but we are adding some more (e.g. SnowFlake, Hive, etc.)

    The feature should be in Beta.

    Best Regards, Candido

    0
    Comment actions Permalink
  • Avatar
    Иван Л

    Hey! There is not a single connection announced in version 6.4.0 in UI execution (.

    Should I wait? And can I find out the priority directions for the development of the platform?

    0
    Comment actions Permalink
  • Avatar
    Candido Dessanti

    Hi,

    You can use the heavy connector thru Heavy Immerse, but you have to install a driver for the database you are interested in (e.g., Postgres, but also Snowflake and RedShift have been testing).

    Please refer to the heavy connect docs and pay special attention to setting up a heavy connector for immerse's section.

    After that, everything is correctly configured, you should see something like that in the Data Manager section of Immerse (I added just PostgreSQL)


    Then you can select PostgreSQL as datasource to Connect (create a foreign table thru the UI) or Import (copy the data to a local table) 


    Pressing connect, you should get a preview , and you can choose the name of the foreign table you are going to create


    More or less, the same happens when you choose the Import option.

    After creating the table, you can query or inspect it in the SQL Editor. Let

    Let me know if we replied to your question or if you need other info.

    0
    Comment actions Permalink
  • Avatar
    Иван Л

    odbcinst.ini and odbc.ini in the /etc/ directory.   it"s  OK

    docker file it"s true?  I don"t see postgresql

     

    # Copy and extract HEAVY.AI tarball. In own stage so that the temporary tarball
    # isn't included in a layer.
    FROM ubuntu:18.04 AS extract

    WORKDIR /opt/heavyai/
    COPY heavyai-latest-Linux-x86_64-cpu.tar.gz /opt/heavyai/
    RUN tar xvf heavyai-latest-Linux-x86_64-cpu.tar.gz --strip-components=1 && \
    rm -rf heavyai-latest-Linux-x86_64-cpu.tar.gz

    # Build final stage
    FROM ubuntu:18.04
    LABEL maintainer "HEAVY.AI Support <support@heavy.ai>"

    RUN apt-get update && apt-get install -y --no-install-recommends \
    libldap-2.4-2 \
    bsdmainutils \
    wget \
    curl \
    libgeos-dev \
    default-jre-headless && \
    apt-get remove --purge -y && \
    rm -rf /var/lib/apt/lists/*

    COPY --from=extract /opt/heavyai /opt/heavyai

    WORKDIR /opt/heavyai

    EXPOSE 6274 6273

    CMD /opt/heavyai/startheavy --non-interactive --data /var/lib/heavyai/storage --config /var/lib/heavyai/heavy.conf

    FROM heavyai/heavyai-ee-cuda

    # Install PostGreSQL ODBC driver.
    # The Snowflake ODBC driver depends on unixodbc.
    RUN apt-get update && apt-get install -y odbc-postgresql unixodbc

    # Install Redshift ODBC driver.
    RUN dpkg -i ./AmazonRedshiftODBC-64-bit-1.4.52.1000-1.x86_64.deb
    RUN rm ./AmazonRedshiftODBC-64-bit-1.4.52.1000-1.x86_64.deb

    # Install Snowflake ODBC driver.
    RUN dpkg -i ./snowflake-odbc-2.25.2.x86_64.deb
    RUN rm ./snowflake-odbc-2.25.2.x86_64.deb
    0
    Comment actions Permalink
  • Avatar
    Иван Л

    I understood that the instructions for UBUNTU , I have docker in mac os

    I can get settings docker file for mac os?

    0
    Comment actions Permalink
  • Avatar
    Candido Dessanti

    Hi,

    To use heavy connect using odbc within a docker container, you should install the odbc manager and the drivers in the container.

    To do that in a permanent way you have to use the docker build command and a Dockerfile and a modified odbcini.ini file

    the Dockerfile

    FROM heavyai/heavyai-ee-cpu:latest
    RUN apt-get update && apt-get install -y odbc-postgresql unixodbc
    COPY odbcinst.ini /etc

    The modified odbcini.ini file:

    [ODBC Drivers]
    PostgreSQL=Installed
    PostgreSQL Unicode=Installed 

    [PostgreSQL]
    Description=PostgreSQL ODBC driver (ANSI version)
    Driver=psqlodbca.so
    Setup=libodbcpsqlS.so
    Debug=0
    CommLog=1
    UsageCount=1

    [PostgreSQL Unicode]
    Description=PostgreSQL ODBC driver (Unicode version)
    Driver=psqlodbcw.so
    Setup=libodbcpsqlS.so
    Debug=0
    CommLog=1
    UsageCount=1

    With both files in a directory run the docker build command (I'm using myimage as the name of the image i'm going to create).

    Run the docker build

    mapd@zion-tr:~/docker$ sudo docker build -t myimage .
    Sending build context to Docker daemon  3.072kB
    Step 1/3 : FROM heavyai/heavyai-ee-cuda:latest
     ---> 2d5fc83daf17
    Step 2/3 : RUN apt-get update && apt-get install -y odbc-postgresql unixodbc
     ---> Using cache
     ---> e6a24f77a5a5
    Step 3/3 : COPY odbcinst.ini /etc
     ---> Using cache
     ---> dabd8d64d463
    Successfully built dabd8d64d463
    Successfully tagged myimage:latest
    mapd@zion-tr:~/docker$ ls -ltr^C
    mapd@zion-tr:~/docker$ vi Dockerfile 
    mapd@zion-tr:~/docker$ sudo docker build -t myimage .
    Sending build context to Docker daemon  3.072kB
    Step 1/3 : FROM heavyai/heavyai-ee-cpu:latest
    latest: Pulling from heavyai/heavyai-ee-cpu
    23884877105a: Already exists 
    bc38caa0f5b9: Already exists 
    2910811b6c42: Already exists 
    36505266dcc6: Already exists 
    670ae58ce54d: Pull complete 
    db571baa598b: Pull complete 
    Digest: sha256:16361a7a6deb298020d1375d45805e901bfcaba7d0f786f8149f9e7e54fe6637
    Status: Downloaded newer image for heavyai/heavyai-ee-cpu:latest
     ---> b598af014b9c
    Step 2/3 : RUN apt-get update && apt-get install -y odbc-postgresql unixodbc
     ---> Running in 76dede9d1993
    Get:1 http://security.ubuntu.com/ubuntu bionic-security InRelease [88.7 kB]
    Get:2 http://archive.ubuntu.com/ubuntu bionic InRelease [242 kB]
    Get:3 http://security.ubuntu.com/ubuntu bionic-security/main amd64 Packages [3158 kB]
    Get:4 http://security.ubuntu.com/ubuntu bionic-security/universe amd64 Packages [1577 kB]
    Get:5 http://security.ubuntu.com/ubuntu bionic-security/restricted amd64 Packages [1387 kB]
    Get:6 http://security.ubuntu.com/ubuntu bionic-security/multiverse amd64 Packages [22.9 kB]
    Get:7 http://archive.ubuntu.com/ubuntu bionic-updates InRelease [88.7 kB]
    Get:8 http://archive.ubuntu.com/ubuntu bionic-backports InRelease [83.3 kB]
    Get:9 http://archive.ubuntu.com/ubuntu bionic/multiverse amd64 Packages [186 kB]
    Get:10 http://archive.ubuntu.com/ubuntu bionic/universe amd64 Packages [11.3 MB]
    Get:11 http://archive.ubuntu.com/ubuntu bionic/main amd64 Packages [1344 kB]
    Get:12 http://archive.ubuntu.com/ubuntu bionic/restricted amd64 Packages [13.5 kB]
    Get:13 http://archive.ubuntu.com/ubuntu bionic-updates/restricted amd64 Packages [1430 kB]
    Get:14 http://archive.ubuntu.com/ubuntu bionic-updates/universe amd64 Packages [2352 kB]
    Get:15 http://archive.ubuntu.com/ubuntu bionic-updates/multiverse amd64 Packages [30.8 kB]
    Get:16 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 Packages [3580 kB]
    Get:17 http://archive.ubuntu.com/ubuntu bionic-backports/main amd64 Packages [64.0 kB]
    Get:18 http://archive.ubuntu.com/ubuntu bionic-backports/universe amd64 Packages [20.5 kB]
    Fetched 27.0 MB in 4s (6041 kB/s)
    Reading package lists...
    Reading package lists...
    Building dependency tree...
    Reading state information...
    The following additional packages will be installed:
      libltdl7 libodbc1 libpq5 libreadline7 odbcinst odbcinst1debian2
      readline-common
    Suggested packages:
      libmyodbc tdsodbc unixodbc-bin readline-doc
    The following NEW packages will be installed:
      libltdl7 libodbc1 libpq5 libreadline7 odbc-postgresql odbcinst
      odbcinst1debian2 readline-common unixodbc
    0 upgraded, 9 newly installed, 0 to remove and 68 not upgraded.
    Need to get 959 kB of archives.
    After this operation, 3571 kB of additional disk space will be used.
    Get:1 http://archive.ubuntu.com/ubuntu bionic/main amd64 readline-common all 7.0-3 [52.9 kB]
    Get:2 http://archive.ubuntu.com/ubuntu bionic/main amd64 libreadline7 amd64 7.0-3 [124 kB]
    Get:3 http://archive.ubuntu.com/ubuntu bionic/main amd64 libltdl7 amd64 2.4.6-2 [38.8 kB]
    Get:4 http://archive.ubuntu.com/ubuntu bionic/main amd64 libodbc1 amd64 2.3.4-1.1ubuntu3 [183 kB]
    Get:5 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 libpq5 amd64 10.23-0ubuntu0.18.04.1 [108 kB]
    Get:6 http://archive.ubuntu.com/ubuntu bionic/main amd64 odbcinst amd64 2.3.4-1.1ubuntu3 [12.4 kB]
    Get:7 http://archive.ubuntu.com/ubuntu bionic/main amd64 odbcinst1debian2 amd64 2.3.4-1.1ubuntu3 [40.4 kB]
    Get:8 http://archive.ubuntu.com/ubuntu bionic/universe amd64 odbc-postgresql amd64 1:10.01.0000-1 [379 kB]
    Get:9 http://archive.ubuntu.com/ubuntu bionic/universe amd64 unixodbc amd64 2.3.4-1.1ubuntu3 [22.0 kB]
    debconf: delaying package configuration, since apt-utils is not installed
    Fetched 959 kB in 0s (2692 kB/s)
    Selecting previously unselected package readline-common.
    (Reading database ... 5564 files and directories currently installed.)
    Preparing to unpack .../0-readline-common_7.0-3_all.deb ...
    Unpacking readline-common (7.0-3) ...
    Selecting previously unselected package libreadline7:amd64.
    Preparing to unpack .../1-libreadline7_7.0-3_amd64.deb ...
    Unpacking libreadline7:amd64 (7.0-3) ...
    Selecting previously unselected package libltdl7:amd64.
    Preparing to unpack .../2-libltdl7_2.4.6-2_amd64.deb ...
    Unpacking libltdl7:amd64 (2.4.6-2) ...
    Selecting previously unselected package libodbc1:amd64.
    Preparing to unpack .../3-libodbc1_2.3.4-1.1ubuntu3_amd64.deb ...
    Unpacking libodbc1:amd64 (2.3.4-1.1ubuntu3) ...
    Selecting previously unselected package libpq5:amd64.
    Preparing to unpack .../4-libpq5_10.23-0ubuntu0.18.04.1_amd64.deb ...
    Unpacking libpq5:amd64 (10.23-0ubuntu0.18.04.1) ...
    Selecting previously unselected package odbcinst.
    Preparing to unpack .../5-odbcinst_2.3.4-1.1ubuntu3_amd64.deb ...
    Unpacking odbcinst (2.3.4-1.1ubuntu3) ...
    Selecting previously unselected package odbcinst1debian2:amd64.
    Preparing to unpack .../6-odbcinst1debian2_2.3.4-1.1ubuntu3_amd64.deb ...
    Unpacking odbcinst1debian2:amd64 (2.3.4-1.1ubuntu3) ...
    Selecting previously unselected package odbc-postgresql:amd64.
    Preparing to unpack .../7-odbc-postgresql_1%3a10.01.0000-1_amd64.deb ...
    Unpacking odbc-postgresql:amd64 (1:10.01.0000-1) ...
    Selecting previously unselected package unixodbc.
    Preparing to unpack .../8-unixodbc_2.3.4-1.1ubuntu3_amd64.deb ...
    Unpacking unixodbc (2.3.4-1.1ubuntu3) ...
    Setting up readline-common (7.0-3) ...
    Setting up libreadline7:amd64 (7.0-3) ...
    Setting up libpq5:amd64 (10.23-0ubuntu0.18.04.1) ...
    Setting up libltdl7:amd64 (2.4.6-2) ...
    Setting up libodbc1:amd64 (2.3.4-1.1ubuntu3) ...
    Setting up odbcinst1debian2:amd64 (2.3.4-1.1ubuntu3) ...
    Setting up odbc-postgresql:amd64 (1:10.01.0000-1) ...
    grep: /etc/odbcinst.ini: No such file or directory
    odbcinst: Driver installed. Usage count increased to 1. 
        Target directory is /etc
    odbcinst: Driver installed. Usage count increased to 1. 
        Target directory is /etc
    Setting up odbcinst (2.3.4-1.1ubuntu3) ...
    Setting up unixodbc (2.3.4-1.1ubuntu3) ...
    Processing triggers for libc-bin (2.27-3ubuntu1) ...
    Removing intermediate container 76dede9d1993
     ---> c737e3a54199
    Step 3/3 : COPY odbcinst.ini /etc
     ---> eae39fd92e09
    Successfully built eae39fd92e09
    Successfully tagged myimage:latest

    After the image is created you have to run the newly built image called myimage in the usual way

     

    docker run -d -v /var/lib/heavyai:/var/lib/heavyai -p 6273-6278:6273-6278 myimage:latest

    now you should see PostgreSQL as an available data source.

    Let mw know if it's working fro yoo too.

    Candido

    0
    Comment actions Permalink
  • Avatar
    Иван Л

    hello!

    I make. odbcini.ini file (it's ok)

    I make Dockerfile

    1.
    # Copy and extract HEAVY.AI tarball. In own stage so that the temporary tarball
    # isn't included in a layer.
    FROM ubuntu:18.04 AS extract

    WORKDIR /opt/heavyai/
    COPY heavyai-latest-Linux-x86_64-cpu.tar.gz /opt/heavyai/
    RUN tar xvf heavyai-latest-Linux-x86_64-cpu.tar.gz --strip-components=1 && \
    rm -rf heavyai-latest-Linux-x86_64-cpu.tar.gz

    # Build final stage
    FROM ubuntu:18.04
    LABEL maintainer "HEAVY.AI Support <support@heavy.ai>"

    RUN apt-get update && apt-get install -y --no-install-recommends \
    libldap-2.4-2 \
    bsdmainutils \
    wget \
    curl \
    libgeos-dev \
    default-jre-headless && \
    apt-get remove --purge -y && \
    rm -rf /var/lib/apt/lists/*

    COPY --from=extract /opt/heavyai /opt/heavyai

    WORKDIR /opt/heavyai

    EXPOSE 6274 6273

    CMD /opt/heavyai/startheavy --non-interactive --data /var/lib/heavyai/storage --config /var/lib/heavyai/heavy.conf

    FROM heavyai/heavyai-ee-cpu:latest
    RUN abrew install psqlodbc
    COPY odbcinst.ini /etc

    2. 

    MacBook-Air-Ivan:~ ivanzaharov$ docker commit c5e12205b90a heavyai/heavyai-ee-cpu:latest1

    sha256:469194de0c08f6ce7f533ffb4ab046f6074d00b1b1fb666878c5536c670ea952

    3. 

    I followed this instruction (odbc) https://macappstore.org/psqlodbc/. 

    4.

    MacBook-Air-Ivan:~ ivanzaharov$ sudo docker build -t heavyai/heavyai-ee-cpu:latest1

    "docker build" requires exactly 1 argument.

    See 'docker build --help'.

    Usage:  docker build [OPTIONS] PATH | URL | -

    Build an image from a Dockerfile

    MacBook-Air-Ivan:~ ivanzaharov$

     

    What's my mistake?

    0
    Comment actions Permalink
  • Avatar
    Candido Dessanti

    I don't know, but I suggested starting using the image heavyai/heavyai-ee-cpu:latest

    and then adding into that ubuntu image the driver of PostgreSQL and the odbc manager using the apt command, not the abrew, because the command will run in the container, not in the host OS.

    If you follow my instruction in this post https://support.heavy.ai/hc/en-us/community/posts/10752490731415/comments/11978396776727 what happens? Unfortunately, I haven't a MAC to try the procedure, but I'm confident it should work.

    The sudo docker built -t you used is wrong  because you can't use the tag latest1, and it doesn't have a path
    this is the right command /the point means the actual directory)
    sudo docker build -t heavyai-ee-cpu1:latest .


    If you think that can be useful, I can share the docker image with the odbc manager and the driver installed with you, but it would be better if you can do by your own.



    0
    Comment actions Permalink
  • Avatar
    Иван Л

    "If you think that can be useful, I can share the docker image with the odbc manager and the driver installed with you, but it would be better if you can do by your own"

    I will be grateful for it. I am not a programmer and no one can help me!)

    0
    Comment actions Permalink
  • Avatar
    Candido Dessanti

    Hi,

    you can download an image of ee-cpu with postgres at this link.

    after downloading, import the image with this command 

    sudo docker load < heaviai_6_4_with_postgres.tar

    then run the image in the usual way e.g.

    sudo docker run -d -v /var/lib/heavyai:/var/lib/heavyai -p 6273-6278:6273-6278 heavyai-ee-cpu-with-postgres

    check if the docker image is running

    sudo docker ps -a

    CONTAINER ID   IMAGE                            COMMAND                  CREATED              STATUS                     PORTS                                                           NAMES
    8873c7737237   heavyai-ee-cpu-with-postgres     "/bin/sh -c '/opt/he…"   About a minute ago   Up About a minute          0.0.0.0:6273-6278->6273-6278/tcp, :::6273-6278->6273-6278/tcp   quizzical_satoshi

    and then connect to heavy immerse, checking if PostgreSQL is a datasource available.

    0
    Comment actions Permalink
  • Avatar
    Иван Л

    Hello! This worked for me! Thank you!)

    0
    Comment actions Permalink
  • Avatar
    Иван Л

    Hello! This not worked for me!

    :Type[SQL_ERROR] [Odbc error SQLSTATE = [00000]. Native Error Code = [0]. Details:"[iODBC][Driver Manager]psqlodbca.so: cannot open shared object file: No such file or directory"] [Odbc error SQLSTATE = [IM003]. Native Error Code = [0]. Details:"[iODBC][Driver Manager]Specified driver could not be loaded"]

    2023-03-14T18:11:19.029009 E 18 47 7 odbc_utils.cpp:494 Error in ~OdbcDatabaseHandle allocation [Error: recieved code [-1]. Expected [0] or [1]

    :Type[SQL_ERROR] [Odbc error SQLSTATE = [08003]. Native Error Code = [0]. Details:"[iODBC][Driver Manager]Connect not open"]]

    2023-03-14T18:11:19.053779 E 18 47 7 DBHandler.cpp:4829 detect_column_types error: Error: recieved code [-1]. Expected [0] or [1]

    :Type[SQL_ERROR] [Odbc error SQLSTATE = [00000]. Native Error Code = [0]. Details:"[iODBC][Driver Manager]psqlodbca.so: cannot open shared object file: No such file or directory"] [Odbc error SQLSTATE = [IM003]. Native Error Code = [0]. Details:"[iODBC][Driver Manager]Specified driver could not be loaded"]

     

    0
    Comment actions Permalink
  • Avatar
    Candido Dessanti

    So it worked or not?

    0
    Comment actions Permalink

Please sign in to leave a comment.

Didn't find what you were looking for?

New post