User specific default database in Hive

hadoophive

I am running a Oracle Big Data Appliance platform that has Cloudera EDH 5.9.x running under the hood. My users are mainly using beeswax, which is the Hive query editor app within Hue.

When a user logs into Hue and then opens beeswax, the default Hive database "default" is preselected. I want to change this so that the first database they see is their own sandbox database. Currently, the user has to manually select the database from beeswax or run the use DATABASE command in the editor.

Is there a configuration item I can change within any of the CDH software modules that will help me do this? Or is there a concept of a Hive startup script where I can run the use DATABASE command?

Best Answer

In bypass mode Hive clients directly access the metastore database instead of using the Hive Metastore Server for metastore information.

Go to the Hive service.
Click the Configuration tab.
Select Scope > HIVE service_name (Service-Wide)
Select Category > Advanced.
Deselect the Bypass Hive Metastore Server property.
Click Save Changes to commit the changes.
Re-deploy Hive client configurations.
Restart Hive and any Hue or Impala services configured to use that Hive service.

This might work for you.

Related Solutions

Correlate subquery in hive

The syntax of you query is not often called "correlated subquery". A correlated subquery would be like this:

SELECT 
    ndo.sku AS ParentSKU, 
    (
        SELECT SUM(g.sessions)
        FROM gasessiondata AS g
        WHERE g.date >= ndo.FromDate
          AND g.date <= ndo.ToDate
    ) AS s
FROM 
    Temp.NumberOfDaysOnline AS ndo ;

Your query is similar to a LATERAL join (CROSS / OUTER APPLY in SQL Server). This type of correlated joins has not been implemented in many DBMS.

It's easy to rewrite though so the join is done first and then the GROUP BY. This simple query should be supported by most, if not all DBMS:

SELECT 
    ndo.sku AS ParentSKU, 
    SUM(g.sessions) AS s
FROM 
        Temp.NumberOfDaysOnline AS ndo 
    JOIN
        gasessiondata AS g
    ON  g.date >= ndo.FromDate
    AND g.date <= ndo.ToDate
GROUP BY 
    ndo.PK,             -- the PRIMARY KEY of the table
    ndo.sku ;           -- and any column used in the SELECT list

Seems that Hive supports only equi-joins and cross joins. Moving the condition to the WHERE clause might solve this:

SELECT 
    ndo.sku AS ParentSKU, 
    SUM(g.sessions) AS s
FROM 
        Temp.NumberOfDaysOnline AS ndo 
    CROSS JOIN
        gasessiondata AS g
WHERE 
        g.date >= ndo.FromDate
    AND g.date <= ndo.ToDate
GROUP BY 
    ndo.PK,             -- the PRIMARY KEY of the table
    ndo.sku ;           -- and any column used in the SELECT list

IDE Access to Hive without user password

I could not test the below described procedure as I don't have the same infrastructure as you, but I suggest you to try this and come back with further details if any of the steps fails.
DBeaver should be able to do this trick for you:

Install DBeaver, either as an Eclipse plug-in or as a stand-alone application
Create a new connection, for example by clicking on the icon
Select Apache Hive as connection type, and then click "next":
In Generic JDBC connection setting, go first to "SSH" tab and setup your SSH tunnel:
Still in Generic JDBC connection setting, come back to "General" tab and click on "Connection details (name, type, ... )" at the bottom right of the window
On the new screen, click on "Shell commands":
Then I am less sure, but either in "Before connect" or "After connect", add your command sudo su - hive_user. I would rather expect "Before connect", but it depends in which sequence DBeaver connects to SSH tunnel and execute this command. If the first solution doesn't work then I would test the other one:
Once done, click "Back" and fill in the "General" tab with your Hive user. You click "Test connection ..." to ensure everything works fine. Once it works, you can click on "Finish".