Connect to Hive using Teradata Studio 16

Introduction

Teradata Studio is the client used to perform database administration task on Aster and Teradata databases, as well as moving data from and to Hadoop. Recently I was asked to test a solution to integrate Hadoop with Teradata in order to build a modern Data Warehouse architecture, this was my first step and I want to share it with you.

Teradata Studio Download

1. Download Teradata Studio 16 following this link:

Teradata Studio Download

2. Open Teradata studio

Teradata Studio has three different profiles

  • Administration
  • Query Development
  • Data Transfer

3. Change to the query development profile for this quick demo

change-profile

Change profile view

Create a new Hadoop connection profile

1. Click on the New Connection Profile button as shown in the figure

create-a-new-connection-profile

Create a new connection profile

Depending on the Hadoop distribution used you have to change the following parameters.

I tested it with Hortonworks HDP 2.5 with hive.server2.transport.mode = http

2. Select Hadoop as profile type and give it a name. Click on Next:

create-a-hadoop-profile

Create a Hadoop profile

3. Select Hortonworks and Hive connection service

Hive connection service.png

Hive connection service

4. Set WebHCat Connection Properties and test the connection. Click on Next:

webhcat-connection-properties

WebHCat connection properties

Again, I used the host name and credentials from my environment.

5. Set the JDBC connection properties. In my case I used the foodmart sample database.

jdbc-connection-properties

JDBC connection properties

Test the connection

If everything was properly set in the previous steps you should be able to see your databases in the Data Source Explorer:

databases-available

Data source explorer

 

Open a SQL Editor and execute a query. I used this sample query:

select c.country,  s.store_Type, sum(unit_sales) as sum_unit_sales
from sales_fact_dec_1998 as f
inner join customer as c
on c.customer_id = f.customer_id
inner join store as s
on s.store_id = f.store_id
group by c.country,  s.store_Type
order by c.country,  s.store_Type

Result Set

resultset

Result set

And that’s it. I hope you find it useful.

Advertisements

About Paul Hernandez

I'm an Electronic Engineer and Computer Science professional, specialized in Data Analysis and Business Intelligence Solutions. Also a father, swimmer and music lover.
This entry was posted in Big Data, hadoop, Teradata and tagged , , , , . Bookmark the permalink.

8 Responses to Connect to Hive using Teradata Studio 16

  1. Leandro Julian Vaca Halvorsen says:

    Hello it was really helpfull. But i can’t do the last step
    could you say me please which is the jdbc username and password? or how can i configure?

    • Hi Leandro,
      That depends on your environment settings. I am using the Hortonworks Data Platform 2.5 sandbox, which comes with a predefine set of users and groups. For the JDBC settings I am using my linux root user and the password I gave it after the first-time login. For the WebHCat you can use the group hadoop or the hcat user that belongs to this group.
      Which Hadoop distribution are you using? If you are working on Windows use your login user and password or create a new one with the appropriate credentials. Under Linux, you can find the list of groups and members with this command:
      cat /etc/group
      and list your users with this one:
      cat /etc/passwd

  2. Very Nice Article. Great Work .

  3. Great Work . Under Linux, you can find the list of groups and members with this command:
    cat /etc/group
    and list your users with this one:
    cat /etc/passwd

  4. Great Work . Great Work .

  5. Great Work . Great Work .

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s