DBQuery App: MySQL, PostgreSQL, Oracle and Sqlite Query Editors

DBQuery App: MySQL, PostgreSQL, Oracle and Sqlite Query Editors

In Thailand, a brand new application that enables viewing data in MySQL, PostgreSQL, Oracle and Sqlite has been committed.

Inspired from the Beeswax application, it allows you to query a relational database and view it in a table.

Example of configuration in hue.ini:

[librdbms]
  # The RDBMS app can have any number of databases configured in the databases
  # section. A database is known by its section name
  # (IE sqlite, mysql, psql, and oracle in the list below).

  [[databases]]
    # sqlite configuration.
    [[[sqlite]]]
      # Name to show in the UI.
       nice_name=SQLite

      # For SQLite, name defines the path to the database.
       name=/home/romain/projects/hue/desktop/desktop.db

      # Database backend to use.
       engine=sqlite

    # mysql, oracle, or postgresql configuration.
     [[[mysql]]]
      # Name to show in the UI.
      nice_name="My SQL DB"

      # For MySQL and PostgreSQL, name is the name of the database.
      # For Oracle, Name is instance of the Oracle server. For express edition
      # this is 'xe' by default.
      #name=hue

      # Database backend to use. This can be:
      # 1. mysql
      # 2. postgresql
      # 3. oracle
      engine=mysql

      # IP or hostname of the database to connect to.
      ## host=localhost

      # Port the database server is listening to. Defaults are:
      # 1. MySQL: 3306
      # 2. PostgreSQL: 5432
      # 3. Oracle Express Edition: 1521
      ## port=3306

      # Username to authenticate with when connecting to the database.
      user=root

      # Password matching the username to authenticate with when
      # connecting to the database.
      password=root

Note: you can look at the Hue database guide for installing the DB connectors

 

69 Comments

  1. Udi 4 years ago

    Is there a way to add other DBs via JDBC connection? I’m particularly interested in connecting to Vertica

    • Hue Team 4 years ago

      Unfortunately no, we are not using JDBC since it’s a Python project and we have defined just Sqlite, Oracle, PostgreSQL and MySQL as libraries. You could use Sqoop though to import your data into the Hadoop cluster!

  2. Kevin 4 years ago

    I just installed CDH 5.1.1 and when I run the dbquery option in Hue, i get this msg: There are currently no databases configured.
    Please go to your Hue configuration and add a database under the “rdbms” section. Where is the Hue config page? I can’t find it. I’d like to add in my postgres db’s. For my Actian db’s I guess I’ll use sqoop, per the response for Vertica. Thanks.

    • Hue Team 4 years ago

      If you do that package install, hue.ini is in /etc/hue/conf
      If using CM, you can add the full section into the Hue advanced safety valve

  3. Miguel Ángel González Fernández 4 years ago

    I have CDH 5.0 and I add the configuration into the Hue advanced safety valve for query external Oracle RDBMS but unfortunately when I try from the HUE UI start the DB query it said : ” Error loading cx_Oracle module: No module named cx_Oracle”
    I’m using next configuration:

    [librdbms]
    [[databases]]
    [[[BI_EXADATA]]]
    nice_name=”Oracle_RDBMS”
    name=PT2BI01
    engine=oracle
    host=ephol0xx.gest.cpd.osi
    port=50599
    user=x
    password=x

  4. Benjamin Kim 4 years ago

    I just installed CDH 5.1.0 with Hue 3.6 and tried the DB Query tool for a test drive. In the Query Editor, I see that the Server is a drop down and that the Database is a drop down. Does this mean that a server can have multiple databases configured for it? If so, that would be great! We are using PostgreSQL 9.2, and our servers do have multiple databases on them. I do not see anywhere an example on how to configure this. I tried several combinations to no avail. Can someone give me any suggestions?

  5. Hue Team 4 years ago

    Yes it should work if you just point to your server: https://github.com/cloudera/hue/blob/master/desktop/conf.dist/hue.ini#L524 The dropdown will then be filled up.

  6. Ramzi Alqrainy 4 years ago

    How I can add characterEncoding=UTF-8 in configuration about rdbms ?

  7. ravindra jain 3 years ago

    is there a way to pass those passwords which contain # in RDBMS section?

    • Hue Team 3 years ago

      Ha, I don’t think so as the ini file will truncate them. Escaping did not help? (if not will create a jira)

  8. Pratik 3 years ago

    Is there a step missing in the postgresql rdbms configuration instructions for DB Query?

    I configured the RDBMS section in hue.ini and restart the hue service, but the database and schema drop down menus don’t get populated.

  9. Jim 3 years ago

    This does not appear functional, at least for MySQL, in the latest quickstart VM (5.3). I have verified connection capability from the VM to the MySQL host via the mysql command line. The hue web gui, under librdbms, shows the configuration as entered in hue.ini, but the query editor page shows no servers, databases, or tables. Refreshing, restarting processes & the VM itself, none of this resolves the issue. I have searched all of the logs I could find to see where Hue might log the connection attempt, to see what is happening in the background, but I cannot find that either.

    • Jim 3 years ago

      I have found an archived copy of the 5.2 VM. (Just change the download url from *.5.3.* to *.5.2.*) and have circumvented this issue. Should serve as temporary work around.

  10. marko 3 years ago

    connecting to the mysql is not wroking for me…
    this is in my hue.ini:

    ###########################################################################
    # Settings for the RDBMS application
    ###########################################################################

    [rdbms]
    [[databases]]
    [[[mydb]]]
    engine=mysql
    host=name-to-remote-server
    user=ubuntu
    password=welcome1

    I have ubuntu as a proxy user in the core-site.xml file
    Hue log files arent giving me any errors.

    I have created a ubuntu user on the mysql database:
    ubuntu | %
    ubuntu| name-of-the-hue-server

    Can someone tell me what im missing?

    • marko 3 years ago

      reinstalled the whole thing and it works now.
      im glad i wrote documentation along the way 🙂

  11. Rich 3 years ago

    What about support for other DBMS e.g. Vertica?

    • Hue Team 3 years ago

      It will probably come up at some point, right now we are working on making the app itself slicker/better.

  12. Nagendra 3 years ago

    I am using cdh 5.3.0 quickstart vm. i configured in hue.ini but i am not able to access mysql database in hue. in rdbms server and database drop down options are not showing any list like databases which are exist in my mysql. Please provide me required modifications i need to change or what to do. Thanks in advance.

  13. Nagendra 3 years ago

    No i cant use cm because my ram is only 4 gb . i have done configuration manually in hue.ini file then i restarted the entire vm and then i am checking in hue db query app whether i am able to access mysql or not. please provide me the required inputs to make run .

    my configuration is
    [[mysql]]

    engine= mysql
    host = quickstart.cloudera
    name = hue
    port=3306
    user = hue
    password = cloudera

    • Hue Team 3 years ago

      it should be
      [librdbms]
      [[databases]]
      [[[mysql]]]
      engine=mysql
      host=quickstart.cloudera
      port=3306
      user=hue
      password=cloudera
      name=hue

  14. Ben 3 years ago

    I added following to “Hue Service Advanced Configuration Snippet (Safety Valve) for hue_safety_valve.ini”
    but on Hue DB Query I get “The selected database has no tables.” message. What version of postgres does Hue support?

    [librdbms]
    [[databases]]
    [[[postgres]]]
    nice_name=”Test DB”
    name=testdb
    engine=postgresql
    host=my.testdb
    port=10432
    user=testuser
    password=testpass

    • Ben 3 years ago

      Update:

      I’m able to query from tables already knowing the table names. But the table list still doesn’t show up on the left hand side panel. Any ideas?

      • Hue Team 3 years ago

        Are you querying PostGres? This was fixed in master and the latest CDH

        • Ben 3 years ago

          Yes, im querying PostGres 8.4.20. im using CDH 5.4.1, which i believe includes Hue 3.7.

    • Ben 3 years ago

      it also seems to be ignoring order by and always sorts with first column

  15. Vis 3 years ago

    Hue is developing really nicely – it would be great to be able to plugin other databases using e.g. via JDBC. I know this has been asked in the forum before but is there an update to this? When could I expect to add other databases for query/visualization via a pluggable interface?

    Thanks!

  16. Andrey 3 years ago

    Hi,
    Can we get user and password for DBQuery from hue account? Or ask for them for each user?

    Regards,
    Andrey

    • Hue Team 3 years ago

      Yes, there is plan for that to make the app more useful. Hue does not store the password of the current user, so most probably it will have to prompt for a particular username/password.

  17. Abhijit Das 3 years ago

    Hi Team,

    How can I connect to HAWQ using HUE ?

  18. JKarmelek 3 years ago

    What about SQL Server? Is this supported in the latest Hue version? And and if not is it on the roadmap?

  19. Serena 2 years ago

    Hi,

    I want to my Hue connect to my local PostgreSQL, so I configured my new installed HUE /hue/desktop/conf/hue.ini as following:

    [[postgresql]]
    nice_name=”My Postgresql DB”
    name=hue
    engine=postgresql
    host=localhost
    port=5432
    user=hue
    password=hue

    For my postgresql, I can connect to hue database.
    psql -d hue -U hue -W
    Password for user hue:
    psql (8.4.20)
    Type “help” for help.
    hue=>

    When I start my HUE server, I can see it use my configuration because i changed the port number for HUE.
    ^C[[email protected] hue]# build/env/bin/supervisor
    starting server with options:
    {‘daemonize’: False,
    ‘host’: ‘0.0.0.0’,
    ‘pidfile’: None,
    ‘port’: 9999,
    ‘server_group’: ‘hue’,
    ‘server_name’: ‘localhost’,
    ‘server_user’: ‘hue’,
    ‘ssl_certificate’: None,
    ‘ssl_certificate_chain’: None,

    After refresh the http://0.0.0.0:9999/rdbms/, i still got “There are currently no databases configured.
    Please go to your Hue configuration and add a database under the “rdbms” section.”

    Please suggest what is wrong for my database setup.

    Thanks

  20. Serena 2 years ago

    How to sync postgres users or Mysql users with unix users

    • Serena 2 years ago

      I think I am wrong for my previous question. In the hue.ini, it will use hue user which we created in hue databases to connect. but how we let hue know which driver to use to connect the databases?

  21. Serena 2 years ago

    I have very strange behavior for connecting to the Hue DB connect.

    DB configuration:
    nice_name=”My Postgresql DB”
    name=hue
    engine=postgresql
    host=localhost
    port=5432
    user=hue
    password=hue

    The Hue can show the nice_name , but no databases name…

    If i delete the name=hue, got error : Server error occured : ‘name’

    • Hue Team 2 years ago

      Could you use ‘postgresql_psycopg2’ instead of ‘postgresql’ for the engine?

      • Serena 2 years ago

        I got the reason. It should be :

        nice_name=”My DB”
        server_name=postgresql
        server_port=5432
        username=hue
        password=hue
        name=hue

  22. Serena 2 years ago

    How to configure multiple databases under one Server?

    For example, I have one db SERVER as postgresql, it asked me to give name: (database) : hue
    But I want to list multiple databases hue user has. How to write the hue.ini ?

    I tried change the following name=hue to name=hue,test . it doesn’t work.

    DB configuration:
    nice_name=”My Postgresql DB”
    name=hue
    engine=postgresql
    host=localhost
    port=5432
    user=hue
    password=hue

    • Hue Team 2 years ago

      You need to duplicate the sections, so something like


      [librdbms]
      [[databases]]
      [[[postgresql]]]
      nice_name="xxx"
      ...
      [[[postgresql2]]]
      nice_name="yyy"
      ....

      • rainysia 2 years ago

        Thanks! It’s very useful!

  23. Serena 2 years ago

    Is Hue is running on it’s own Virtual Environments from the directory /build/env/bin ?
    How can i change that to use my system python version ?

    • Hue Team 2 years ago

      I suppose that you are using a packaged distribution, this one is going to target the default Python version of your OS. If you want to use a non OS default Python, you will need to rebuild Hue, from github or a tarball release.

      And for information, is there any reason you need to use another Python?

      • Serena 2 years ago

        The material I found how to install the hue is from github, so I build Hue myself. I saw the Hue is using env virtual environment not using my OS default Python. The python it used and all the modules are located in /build/env/bin.

        Why i want to use that is I need to customize Hue which need some modules installation. I want to know I need to to pip install within the virtual environment or on my OS python level.

        • Hue Team 2 years ago

          Serena, you should be able to run the pip command packaged in /build/env/bin. So from the Hue root: ./build/env/bin/pip install

  24. Suresh Sethuramaswamy 2 years ago

    Hi

    Is there a way to export the results to an excel/csv , i do not see an export option at all on RDBMS UI

    Regards
    Suresh

    • Hue Team 2 years ago

      This is coming up in Hue 3.10 were the RDBMS app was migrated to the new Notebook

  25. Venkatesh 2 years ago

    Dear Hue Team,

    Am new to hadoop and its my learninig period, I just installed CDH 5.5 from cloudera quickstart vm and when I run the dbquery option in Hue, i get this msg: There are currently no databases configured.
    Please go to your Hue configuration and add a database under the “rdbms” section, can you plz share how to add db in hue briefly. If possible share the videos too. I need step by step process. Do u have videos how to work with hue, how to run all thing is hue.

    Thanks

  26. rainysia 2 years ago

    Hi HUE team,,
    I want to add more RDBMS for MySQL,
    I have three mysql instance(192.168.55.2, 192.168.55.3, 192.168.55.4) in different server, and 1 mysql instance has lots of database(db1, db2, db3, db4.).

    If i want to add all mysql RDBMS in HUE, I have to copy 3*4 = 12 mysql part.
    In HUE DB Query, The server will have 12 rows. and 55.2 will display 4 rows, also the 55.3, 55.4
    Is there any way so i can make the “Server” part only display each single host?
    Old:
    SERVER: 192.168.55.2, 192.168.55.2, 192.168.55.2, 192.168.55.2, 192.168.55.3, 192.168.55.3, 192.168.55.3, 192.168.55.3, 192.168.55.4, 192.168.55.4, 192.168.55.4, 192.168.55.4
    DATABASE: 2.db1, 2.db2, 2.db3, 2.db4, 3.db1, 3.db2, 3.db3, 3.db4, 4.db1, 4.db2, 4.db3, 4.db4

    ===>
    NEW:
    SERVER: 192.168.55.2, 192.168.55.3, 192.168.55.4
    When I select SERVER 192.168.55.2, The DATABASE party will display 4 database: 2.db1, 2.db2, 2.db3, 2.db4

    Thanks

  27. Ray 2 years ago

    how can I configure 2 more databases name for MySql?

    • Hue Team 2 years ago

      Hi! Just repeat the mysql configuration of the first one for the other 2 🙂

      • zhongweichen 2 years ago

        how can I configure 2 more databases name for MySql?
        can you give me a demo?
        i repeat the mysql configuration is not Always Successfully!

  28. Kaiming Wan 2 years ago

    how to configure more than one mysql server?

    • Author
      Hue Team 2 years ago

      You can keep adding sub configs in [[databases]], e.g. [[[mysql1]]] …, [[[mysql2]]] …

  29. wuchang 1 year ago

    mysql use utf-8 as the charactor encoding ,but I find that if my query condition contains chinese charaters, the query will failed.I checked my pseudo-distributed.ini , I have configured options={“init_command”:”SET NAMES ‘UTF8′”} .So ,I tried to add a option ,like {“charset”:”utf8″,”init_command”:”SET NAMES ‘UTF8′”} , i suppose this option charset=utf8 will parsed as a parameter when MySQLdb try to connect to mysql(my unit test has proved that is works),but hue cannot startup any more.So , anyone can help me ?

    • Author
      Hue Team 1 year ago

      You will need to ALTER all your tables, or recreate the DB and see if it works. You could do the later in a fresh test DB and see if executing the queries in Hue work now before doing it in your real DB.

Leave a reply

Your email address will not be published. Required fields are marked *

*