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 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

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

3. Select Hortonworks and Hive connection service

Hive connection service

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

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

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:

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

Result set

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

Advertisement