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:
2. Open Teradata studio
Teradata Studio has three different profiles
- Query Development
- Data Transfer
3. Change to the query development profile for this quick demo
Create a new Hadoop connection profile
1. Click on the New Connection Profile button as shown in the figure
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:
3. Select Hortonworks and Hive connection service
4. Set WebHCat Connection Properties and test the connection. Click on Next:
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.
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:
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
And that’s it. I hope you find it useful.