What are Pluggable database in Oracle & Setting up ODI Repository with Oracle 12c.

Oracle 12 c has recently introduced a new concept of plug-gable databases which actually aims to separate the metadata from business user data. The concept is to create a container that will hold the user data schema. The details about the plug-gable database and how they work can be referenced from below. 

http://oracle-base.com/articles/12c/multitenant-overview-container-database-cdb-12cr1.php

But here are few questions?

How do I connect to a pluggable database using SQL developer or TOAD?

The Answer is simple with oracle installation it creates and ask for the name of the pluggable database e.g. PDBORCL (in our case this is the pluggable database name). If you are not sure you can login to system using SYS user and execute below query. You can see I have a PDB with name as PDBORCL and its open. If it’s not open you can open it by issuing the second SQL statement below

select name, open_mode from v$pdbs;

PDBORCL             READ WRITE

Alter pluggable database all open;

Now you have to define a TNS entry in your oracletns.ora file as follows and you will be able to connect it using TOAD/SQL Developer.

PDBORCL=

  (DESCRIPTION=

    (ADDRESS=

      (PROTOCOL=TCP)

      (HOST=localhost)

      (PORT=1521)

    )

    (CONNECT_DATA=

      (SERVER=dedicated)

      (SERVICE_NAME=PDBORCL)

    )

  )

How do I Setup the ODI repository into a pluggable database using RCU?

The RCU utility will setup the repository for you in the oracle 12c, but if you have configured the pluggable database option the RCU pre check fails with below error.

RCU-6002: The specified database does not meet the minimum requirement to load metadata repository. RCU-6080: Global prerequisite check failed - Check requirement for specified database the selected Oracle database is a multitenant container database (CDB). Connecting to a multitenant container database (CDB) is not supported. Instead, connect to a valid pluggable database (PDB).

So to solve this issue while RCU asks for the details on “Database connection details” step provide the Service Name: PDBORCL instead of your main service name e.g. ORCL which is by default.  Doing so all the repository objects and user will be created in the PDBORCL database and later you will use the below steps to create or connect to your PDB based repository.

How do I connect to a pluggable database using ODI or any other application?

For my case we have an issue while connecting to the PDB database while setting up the ODI repository we did the following while filling up the JDBC URL. Giving only the service name will give error and to resolve it we did the following by providing the full TNS entry details.

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PDBORCL)))

Hope that helps.

Reference.

https://blogs.oracle.com/dataintegration/entry/odi_creating_a_repository_in

http://en.community.dell.com/techcenter/enterprise-solutions/w/oracle_solutions/5063.how-to-connect-to-pluggable-databases-in-oracle-rac-12cr1

http://www.areaetica.com/setup-oracle-12c-enterprise-manager-express-for-a-pdb/

http://blog.e-dba.com/blog/2013/06/29/oracle-database-12c-pluggable-databases/
http://www.youtube.com/watch?v=ynUURa5dA6Q
 

Basics : ETL/ELT Concepts with Big Data

Big Data and Hadoop analytics has been a big buzz in IT industry and often you find some catchy terms associated with it. But if we take a closer look I think it would not be wrong to conclude that Big Data community has derived some terms which have the roots in traditional data warehousing or ETL implementation and are there from decades. 

We have observed that over the period of time ETL/ELT is evolving to support integration across much more than traditional data warehouses. ETL can support integration across transactional systems, operational data stores, BI platforms, MDM hubs, the cloud, and Hadoop platforms.

Below are some terms you will see when it comes to data processing with any Hadoop platform and they are listed below with corresponding concept in traditional data warehousing.

Tuple:  This term is used to define the basic information record that can be mapped to one row in the physical table in RDBMS or a record in a file.

Pipe Assembly: It is defined as SET of records which are under processing, you can imagine them as group of rows from a table or a file.

Tuple Stream:  It is actually the group of records which are under any kind of data processing and transformation, usually in any ETL tool the source data is selected and it will undergo some processing or transformation and this operation take place either in system memory or in case of a push down optimization it is done in RDMS spool space. Regardless where the transformation is applied it’s basically the set of records under processing.

Taps : Generic component independent of a platform , it can be mapped to something similar to a transformation step/stage e.g. a router or filter transformation in Informatica or Data stage ( any other ETL tool).

Flow: It is the series of Taps or transformation stages that are linked together to read, process and store some value into the target.

Cascade: Finally the term because of which I had to go through a lot of tutorials to drill down the science behind, this is a traditional concept for workflow it is defined as a collection of flow or in traditional ETL paradigm ETL Mapping Job to execute in a designed way to produce or achieve some value.

Hope that will help all people who are from DWH/BI background to get a grip quickly over the concepts related to ETL in Big Data domain.

References:

http://docs.cascading.org/tutorials/etl-log/etl.html

http://www.cascading.org/documentation/tutorials/

Data Processing with Pig : Understanding PIG functions

In this tutorial we would try to explore some functions for data processing and learn them by example. We would be using PIG script to find out the Maximum runs scored by a player from a baseball stat data. You can download the sample file from http://seanlahman.com/files/database/lahman591-csv.zip.

It’s a collection of files having information about baseball stats while if you unzip this you would have number of files we will be using “Batting.csv”  in this tutorial.

Prerequisite: To understand this tutorial you should have a basic knowledge about PIG and loading file into H-Catalog. Please visit below URL for more details

http://tahir-aziz.blogspot.com/2013/08/hadoop-getting-started-with-pig.html

Steps:

1.       Logon to Hadoop console and Click on “PIG” Icon and Click “New Script”.

2.       Write down the code below in the editing area

a)      batting = load 'Batting.csv' using PigStorage(','); 

b)      runs = FOREACH batting GENERATE $0 as playerID, $1 as year , $8 as runs; 

c)       grp_data = GROUP runs by (year); 

d)      max_runs = FOREACH grp_data GENERATE group as grp , MAX(runs.runs) as max_runs;

e)      join_max_run = JOIN max_runs by ($0, max_runs), runs by (year,runs);

f)       join_data = FOREACH join_max_run GENERATE $0 as year, $2 as playerID, $1 as runs;

g)      dump join_data;

3.       Click on “Save”.

Now let’s try to understand code line by line and keep in mind what we want to do is to find out the Max score done by a player in any year.

batting = load 'Batting.csv' using PigStorage(',');   

This is pretty simple we just want to load one file using PigStorage() function it will load the file structure into “batting” variable ( an array actually).

runs = FOREACH batting GENERATE $0 as playerID, $1 as year , $8 as runs; 

Now here we just want to extract those columns which are useful for us into a separate array you can see that data is accessed based on the $index as first index in Batting.csv contain PlayerId ( name) and 8^th index contains the score done by player.

grp_data = GROUP runs by (year); 

GROUP function only groups data into chunks based on the column we specify in our case the data groups will be created based on YEAR which means all records that belongs to one year will be stored together in ONE ROW. If you want to understand it better execute below in PIG editir area

DESCRIBE grp_data;   // Results of this statement is below

grp_data: {group: bytearray,runs: {(playerID: bytearray,year: bytearray,runs: bytearray)}}

This is how GROUP looks like and if you look at that 'group' is the name of first column by default n will contain the "year" value as defined in GROUP BY clause.

The second column is an Object which means you will have records which falls in this group. e.g in this year.

max_runs = FOREACH grp_data GENERATE group as grp , MAX(runs.runs) as max_runs;

Now for each year it will calculate the MAX runs scored.

join_max_run = JOIN max_runs by ($0, max_runs), runs by (year,runs);

JOIN works in similar fashion as it does in SQL it is joining the $0 (Year) and max runs ( line d)  with Year and Runs with “runs” ( line b in code) array. 

In SQL terms the join is as follow

Max_runs inner join

Runs on  Max_runs.Year = runs.year

And Max_runs.max_runs = runs.runs;

join_data = FOREACH join_max_run GENERATE $0 as year, $2 as playerID, $1 as runs;

This last statement just generate a new final dataset by extracting Year , Player Name and MAX runs scored from the joined data set.

The final output of this program is as follows

(1871,barnero01,66.0)

(1872,eggleda01,94.0)

(1873,barnero01,125.0)

(1874,mcveyca01,91.0)

(1875,barnero01,115.0)

(1876,barnero01,126.0)

…

…

Thanks

 

Alter Table Vs Ins Select for Modifying Table Structure in Teradata

Conclusion

It is strongly recommend implementing Alter Table, at least start considering it. If you're concerned about availability you should bear in mind that this process will probably be scheduled out of business hours anyway.
And when you need to change the [P]PI or you just want the safeness of a copy of the old table you should definitely prefer Merge Into over good ol' Insert Select.

Please review below figure to see the Pros and Cons of using Alter/Insert Selct and Merge into options for modifying any table in Teradata.

The above conclusion is based on Dieter Blog reference link for details is as follows

http://developer.teradata.com/blog/dnoeth/2013/08/why-dont-you-use-alter-table-to-alter-a-table

Dynamic DDL Generation using BTEQ

Dynamic script can be generated using Teradata DBC tables. we would use dbc.tables to generate the DDL of all objects available in development database and later we can change that script to deploy to Test or any other environment.

You can use below BTEQ script which will actually generate the standard SHOW TABLE ; statement for all objects in development database and results will be exported to Prepare_DDL.BTEQ .

 .SET ERROROUT STDOUT
.logon 10.16.X.X/tdusre,tduserpwd
.Set Echoreq off
.Set Titledashes off
.Set Separator '|'
.Set Format off
.set width 5000
.Set Null ''
 .export file=.\Prepare_DDL.BTEQ;
       select distinct 'SHOW TABLE ' ||  TRIM(databasename) || '.' || TRIM(tablename) || ';'
from dbc.tables
where tablekind =t' and databasename in
(
'DD_DEV_ENV'

);

.export reset;
  
.Logoff;
.Quit;

You can invoke this BTEQ on command prompt as  "Bteq < Bteq_filename > FileName.logs

In next step you can open up  Prepare_DDL.BTEQ and modify it as above to execute that will give you all table DDL;s in one file. The modified file will look like as follows


.SET ERROROUT STDOUT
.logon 10.16.X.X/tdusre,tduserpwd
.Set Echoreq off
.Set Titledashes off
.Set Separator '|'
.Set Format off
.Set Null ''
.set width 5000
.export file=.\Generated_DDL.sql;

SHOW TABLE  DD_DEV_ENV.ACTION_TYPE;
SHOW TABLE  DD_DEV_ENV.BARRING;
...........
......
......
 .export reset;
 .Logoff;
.Quit;

The final exported file "Generated_DDL.sql" will have all the Create table statements which you can modify /parametrized and use to migrate to any other environment.

Thanks