Hashed File Stage in DataStage Server Jobs with an "Example": Video 5 (HD) Video Lecture - Data & Analytics

hey YouTube this is Ruchika again so
this is my fifth video tutorial on data
station as I said in my previous fourth
tutorial fourth video tutorial and
attestation we discussed about a
sequential file stage in server jobs and
as promised in this video I gonna deal
with hashed a file stage with all the
parameters and all that stuff and simple
job design on what exactly is hashed
file station all that so yeah now coming
to the hash file stage basically you can
just say it has like a small database
table hashed file stage serves as like a
reference table or a small database
table so let's get into this so yeah
this is the stage name you can just like
edit a tannoy lap and this is like
account name use account name is like if
you chose this you must have like
accounts linked data I mean I don't have
any linked accounts so yeah if you chose
this buta you can have an account linked
linked with the hash things and if you
should just choose the account that
particular account then it can associate
all the table definitions for that hash
thing and everything everything is like
a pre different yeah but usually use a
director path director path is like if
you remember the sequential file stage
we are just giving the part of the file
right so that's that's this is how it so
we just get if we just specified
directory directory and yeah just click
on the Browse and yeah just keep the
path directly and this I mean I I'll
show this pilot working with the real
time example okay and then the
sequential null value it determines like
what character actually represents the
sequel no value in the hashed file with
the corresponding stage you can just
come if you have like euro you can just
hand X special characters if you here
like euro and all that stuff yeah you
can just choose an option
it can also have like auto detect how to
detect option its disabled because we
didn't have anything so but yeah it does
have an auto detect option so if you
choose that auto detect option then the
datastage info InfoSphere thing
determines what represents SQL asked
it's equal now in my lab and the
universe stage compatibility so if you
check this check this box then any job
conversions like any uh just like all
the job convert it dis compatibility
ensured that all the conversions work
perfectly so yeah and then coming to the
output path I mean yeah description you
can use have your own description when
did you create and what did you create
and what time and all that stuff so yeah
coming to the output link tab because I
do have because I do I have only one
output link it's showing me one
outputting let's let's go like let's
make a different output rings okay now
come to the output tab see I can have
three different outputs for for this
thing I can have like metadata defined
and once I define a metadata I can just
go for next thing and here I can have
like different metadata and all that
yeah so that is how it works and yep we
can have we can have we can have
multiple output links and basically the
thing is as I said a Hodge file stage is
like for referencing right so for
referencing it'sit's not possible with
this link so let's make it convert to
reference so yeah it can have it can
have multiple output links and all the
output links can be rough friends links
it's not that just one reference link I
can convert all these links to there are
for instance it doesn't really matter it
depends on the files and all that stuff
so as of now let's delay to us let's
make it two and we
so coming to this at filename you just
need to have like a filename defined in
here
file name like name of the dad name of
the file to which this hashed file data
is written to so yeah you can have a job
parameter here you can have a job
parameter to the present of file
filename and yeah this normalize or I
mean yeah this normalized on I'm sorry
this her file name is not to which we
know on a right to win which we wanna
pull the data so yeah which we want to
pull the data that's a file which
basically I can say the name of the file
from which the data is read from so
that's that's a file name and we can
have like a record of this record level
record level read record level rate is
something which which says that we're
gonna I mean the file make sure that it
reads a record by record so yeah I mean
this because it rates record by record
it it may be little slower but sometimes
if you want to if you want if you want
to read and write the hash file at the
same time yeah you need to go for this
option and this preload file to memory
you have like all these options the
default value is disabled it's always
and disabled but you can choose any of
this so if if the stage is like if you
select enable this means that the
highest file hashed file is read into
the memory but when the job is made to
run this this hashed file is ready into
the memory and if enable I mean if if
you select anybod enable lock for
updates
then this hash file which is ready to
memory I mean obviously enable right so
the hash file is ready into memory when
the job is run but if if there is no
lookup or if there is nothing found in a
memory then the job looks on a file disk
if still it doesn't find any file or
anything then it gonna have it gonna
initiate its own update lock update lock
so it doesn't I just
update all the locks so disable lock for
updates yeah it has it does pretty much
same everything as you enable lock lock
for updates but except that the hash
file is not read into the memory yeah
you really don't need to worry about
this because these are not really the
things which actually people use more
often so yet normalize on and yeah
coming to the normalize on this
basically allow us to normalize the data
before that is right so you can have
like basically where if you want to
choose this this is oil only enable for
non reference output links like
non-reference of buildings let's make it
okay let's make it known reference so
let's make it convert a string so let's
see it's disabled right because Deus
lean 15 Diaz link 15 is a known
reference link I mean it's at a
Frenchman but if I move to dealing 19
sorry it's turning out here but
selection tab is a selection tab is say
right so yeah you can have a selection
tab is something like which we have we
can have like selection tab then then we
have it takes column record record level
record yep this this gonna select file
name it and all this gonna come up so
yep so that's how it works and let's
move to okay so that's pretty much this
hash file you need not worry about under
the other things so yeah let's now let's
now go to designing designing a job with
all the parameters and all the things
okay so let's yeah this is I already
open server job for you so this is a
server job and let's make it a design so
I just pull the stages and then explain
you what what I was trying to do okay so
just two sequential files and then hash
file I'm using sequential file because
we already learned about the sequential
file right so now it would be easy for
you to understand a sequential file as
well as hash file station now again
we're gonna write it to the sequential
file and now reference okay
now let let me explain to you so I so
this is the first sequential file the
sequential file is a hashed file is
getting data from the sequential file so
this is one of this this is the source
file for massaged file for my reference
thing so then ADA which comes from the
sequential file this data serves as a
reference table and the other thing is
this is the main source data so this
data gonna look up on to this so I'll
tell you or what's look up and all that
they're taking an example so transformer
stage you need not worry about it I'll
just like give it if interaction with
the transformer stage you need not worry
about it and then ultimately I'm gonna
write it to the sequential file so I'm
just like making an output as a
sequential file so yep so let's create
our files so yeah I do have two folders
in and out so let's make a new file
and text document let's make it some
amplified okay so that's that's the one
which strikes to me often so let's make
a number you numbers column and then
department and then side okay so he
number is one okay so yep this is how
our data so yeah the other most
important thing is yeah so the other
important thing is when I need to have a
reference on this thing on this data I
need to help one key column one column
which is I need to have one column which
is common among these three so I just
can't if let's say if if I have if I
don't have any of these columns in my
reference thing it doesn't make sense
which column I I was referencing to it
doesn't make sense right so yeah I need
to create a reference column to I mean
reference table our friends will dare
say reference data to so let's call it
rough so reference is yeah if you'll
remember I do have a number department'
and salary' in my actual data so let's
make department as my key Department as
a common column so department and
[Music]
okay so War Department we have ten and
let's move it sails twenty captain or
something 40 is 40 years finance okay
let's make fifty to fifty is some xxx
and sixty is I'm short of department
name so I'm not really I don't really
know the department name so this is how
it works so this is my reference table
and yeah so now now that we have the
data for this let's create the table
definitions so go to the table of
Nations and import table definitions and
then sequential file definitions so
create a MP dot text and then click
input it sort of fixed of it right so
yeah but obviously I do have first kind
in column names and the space delimiter
is comma and yeah in as I said my key
colon is a number here so let's click on
okay and yeah this is yeah so this
important and other one which we need to
import is reference table right so click
on the reference table and we had start
fixed to it again and sure first line in
column names and here my department is
my department number is Mikey :
so just input it yeah but yeah so we do
have to defer to table definitions
important now as I said in my as I
promised in my previous video I gonna
create the parameters as real time so
yeah before I create parameters I need
to tell about one more thing
I thought at some how forward in my I
meant to mention in my previous video so
when you click the job properties tab so
this is how the
door opens right so then you wanna let
me just minimize this okay so when you
wanna when you when you want to have
like before extracting the data from the
sequential file to the highest file if
you want to have only few columns or
something to be extracted then you can
just write a shell script and then
activate this before some job ready
you're gonna write some shell script and
choose this and give the file path here
a file path so before before it passing
to the other stage it gonna execute this
shell script and make sure that it
passes only what we specified in the
shell script so that is before the
observer subroutine and if it's after
jobs or protein or if it's after job
subroutine so just execute same thing
but above it it's it's not in the source
part it's in the target part it's after
the target if after the file is written
and if you want to say only a few
specified that's where we're gonna write
transcript I mean we do have all options
but I am I'm focusing now on shell
script so you can have the shell script
and near the file path here and you can
have this click to a click - because it
doesn't it doesn't make sense if the job
fails and even though a trans transcript
it doesn't make sense right because even
though job the job is failed if the
shell script gonna run it's based off
our resources so you need not do that so
if you want to check this if you wanna
check this check this to make sure your
resources are not I mean are not wasted
or being saved so if you check this it
makes sure that this after job
subroutine is only is only done when a
job is successfully run when it's about
it you need not worry about this it
doesn't it doesn't go for the shell
script activation so yeah this is how it
works and now let's create a parameters
so parameters are going to create for
first input input
folder input
in it does his infant file part this is
input file pad have some disturbance
just a second guys yeah so input file
path input file part is where I give my
folder name so just copy this prompt if
someone gonna see my parameters they're
gonna understand our what is this input
file power and all that so it's a stream
string and what's my input file pad
where is my file stored this right so
this folder so let's copy this and the
input file back and next thing is I
chose I have to even input file that
next days I need to choose file because
input file but have multiple files so
I'm gonna have which file which does fit
file and I'm working on so my file is
EMP you need to give extension to don't
forget that and other reference file
right I do have a two input file so the
other thing is reference so again this
is DXT and next is my output folder
obviously we do have an end sequential
file so I need to have a output file too
right so I'm strained and then what's my
output side I created out out folder so
let's give this bad just paste it here
and what's my output out file name
because we have output file path it
doesn't have okay and apart from that we
need to have hash file because these are
for sequential file parameters but we do
have hashed file too right
so far I let's create a I didn't create
any folder but let's see what happens if
I don't create any folder so let's have
a hash fine let's give it let's give it
just okay let me just choose the path
wait this gonna say ok so let's give
this path so that all the things gets
created here so we can see what's
happening so yep so this is how these
are the parameters so you can just have
like click all the parameters and create
a pyramid for your parameter set for
yourself but I'm not going to do that
because we do have only these files so I
need create parameter for set for that
now click on OK and now I'll show how
you gonna how you gonna put the
parameters in your file so if you
remember your sequential file okay let's
this coffee professional so read and
this is reference right reference what's
her friends department her friends
so let's rename our stage and then
coming to the output yeah this is this
is where you give your file path last
time if you remember in the sequential
file stage I just choose this way I just
browse the file but this time are they
are not going to do that way we're gonna
create a parameters this is this way how
to create input it always a parameter is
always fall followed by started I mean
it's always started and and ends start
and ends with our hashtags so the
parameter should go in between so it
should be exactly the same caps caps
caps letter and capital letters and
inside it should be exactly the same so
yeah sometimes you do have a small
mistake so yeah we don't need to do that
that's a lot of so yep so this is if you
see this this first parameter between
the hashtags input file name it's my
directory path input it's I it's one
input file in its input file path so
this is my this is my directory path
once I have given directory path it's
not enough because it my detector has
multiple files right so I need to choose
a file - so what Phi I need to choose
this is my this is my file so this is
this is my file which file is that it's
am input file is PMP rot okay so and
it's not input file here at a rough PI
right so it's okay
so it's rough file so and and it's again
I ended with this and so I'm giving the
part of my a part of my file and then
part of the directory in which my file
exists and then give
their parameter for my file so this is
how you give your parameters then going
to the format tip first line in column
names and Union worry about anything and
then now load your table definitions
which were created this is a Department
reference right so yep rough friends see
once you loaded here you're gonna see
view data so let's click on that see
this is how you are you have if we give
parameters this is how your thing looks
if you want to edit anything you can
just let it click the click on edit and
but it doesn't make sense because we
have already given right thing so just
click on OK and this is our table I mean
this is our data so yep we are good here
and now let's go here and this is a
great EMP EMP that's it
and next outputs again it's file input
and forward slash because that's
directory and then forward slash and
then hash tag again and now graphite and
now sorry input file because this is EMP
input file and now close with hashtag
little and go to the format first Landon
column names and then columns and then
load your data here at ski MP right so
we just load your data now click on your
we let us see this is your folder and
this is your text will wear disguises so
click on OK yep we are good here so we
did get that now ok now coming to the
hoc let's rename the links to and I said
I'm gonna do it in professional way
right so yep so let's say read
redevelopment ok
let's meet this breed bmp-2 the
transformer
I didn't want to put the same thing to
his suggest but transformed transformer
in real-time it's represented as XFM
itself a means transform a stage so yep
so and this is let's training is as
department reference okay
let's rename okay
let's rename this as right we are
writing it right we read on everything
now you are writing so right data so now
we have everything so let's open the
hatch define stage so yeah we are not
using account name we are using directed
path so what's a directory path I'm
sorry I don't remember the parameter
which I gives let's see what did I keep
hash file okay and capital H and capital
F okay so here the director director
path we gonna give that parameter
parameter is cash five okay so this is
how you give you a parameter and you
need not worry about anything and then
input input is read apartment which is
our input this rate department so
because we already have data in does
data is flown through this link we need
not worry about anything say it's
already in here so yep that's it and
output output yeah Department reference
output link is the one which is writing
to the transformer right this one so
it's writing to the transformer and this
file name here it should be same one it
should be the same one which which we
need to give on it sorry it should be
the same file name which we gave here so
you can just like rename this file
matter so let's give like Josh or
something okay
so yeah this file names let's copy this
and paste this the file name here and
the output folder it should be seen if
it's not same then it gonna create
problem you can you can you can have a
parameter here too but for now there's
no parameter in created so yeah you need
to have a you need to load your table
definitions here too because of you need
you need to pass it to the transformer
stage right so just load your table data
okay so it doesn't exist so just click
on OK and now come to the transformer
transformers see it's that I'll deal
with this transformer clearly in my
problem next third probably next video
next video yeah probably next video so
as I said transformer lookup is like
your reference I mean hash file stage is
like looking up like a reference table
it's like sequel thing a sequel to when
we have like sequel join when we have
two different tables I mean to have only
only only say I mean it's like let's
let's say
I'm trying to explain to the sequel
Celeste let's do that okay so it's like
let's say we have now we do have EMP and
Dept table right so EMP have EMP has all
10 20 and 30 and Department has 50 62 so
but if I want to select only this only
this select a dot e number EMP EMP dot e
number and EMP dot Department dot
Department name from EMP comma
Department where so this is how this is
what your what your are this thing do
EMP dot Department number is equal to de
PE dot Department number this is where
you write your sequel query right so
what does it do the things which we have
like same Department numbers which exist
in these tables we can have it when we
when you execute the sequel for it so
this thing this EMP dot Department
number in depth there is how it look it
works in this hash file station so let's
see that see a department or department
this is showing and this is okay let me
just click on this let's so this is
showing in red it's pretty clear that if
it's red it's something is not wrong so
what does that mean is it doesn't have
any key expression to have joined we
need to have this type of expression
right
this type of like EMP dot Department
number so we can't write that so what
we're gonna do is click on this
department and then drag and just drop
here just right and then drop here so
see so now this repeats this department
this field from this dot
is now is is now linker to this and this
so this is how it works we adjust by
this expression we are just defining the
key expression in both we are just
defining a common columns so if you
don't have any common cons it doesn't it
doesn't make sense right so that's a
reason I chose Department as a common
column between my two data now so we
have a key expression defined now we
need to move or move this data to the
next level right drive data is the next
linked in our thing if we remember ok so
just if you want to track whole columns
whole columns you can just click on this
and just hold your left cursor and just
drag on to this if you want a single
column just select this and then drag on
to the single column that's it you need
not worry about writing typing and
anything so this is how it works so once
you have once you have this these are
the columns you need to say in the end
of your data right so just click on OK
so now coming to the sequential file
let's rename this as write what we are
writing EMP and Dept data right so EMP
Department okay let's make it that
simple
and then here again this is this should
be given in the parameter form so what's
our parameter out 5 part make sure guys
you have the right spelling right
capitalization and everything so yeah
you can just override or yeah anything
that doesn't matter so you can choose
yourself and yep first line in column
names again and columns because the link
carried to this stage we did not load
any table definition here the link which
we defined the data in the transformer
all the four fields
these are passed the four fields of
prior to the date our
the next stage next sequential target
stage through this link so we need not
worry about loading the table definition
here now click OK and now compile the
job and let's see if it has any mistakes
ok let's save this show before I'm gonna
hit on save jobs and let's call this job
ok and let's see if we have any errors
yep so no errors ok so just close it and
click on run so yeah if you choose a
parameter set I bought this input file
path name is going to show like
parameter so you can just click on the
parameter set which we have I mean
parameter set name with all this stuff
so once you choose that name or
everything everything going to choose
but because we didn't create a parameter
set here this is how it works say we
have input file path and everything yep
everything good looks good then he don't
run say you can say you can just bite
while seeing the job ok so this is
something happened let's see ok this is
good for us because we know you guys can
know like oh where did we go wrong and
all that stuff so let's see ready pick
around if it's always successful we
don't worry about errors right we will
not know anything about arrows so yeah
it's good that we do have some errors so
let's figure out that this director
let's run direct
again
I'm sorry it's taking a little longer so
just hold on for a moment
yeah it takes midline because I have
like all the browsers and everything no
pencil I think that's a problem
so let's let's please bear with me guys
[Music]
just one and I'll just like to resume
the video it's still a little long a
time I don't want to waste your time
so yeah I'm sorry guys that took little
longer than expected so yeah now it's
opened so let's open the director so as
I said what warnings you can just have
like warning sissy hello one with
exclamation right so let's click on
what's happening it says before job
execute no command to execute oh I got
it so yeah once you see here you'll get
an idea of what where it is going wrong
so the thing which I did is while I was
explaining to you guys yep this is a
problem I didn't have anything but I
just put that so that's problem I
explained it and I didn't change my
values so just a moment I'll change this
yep none and then now click on OK
so yeah that's pretty cool right because
we will know where degreed commit our
mistake or wait where we went wrong so
yep now compile it again and then run it
again run the Java game now it shouldn't
have any errors as far as I know but
let's see
yep see you have seven rows here
Department will you have like seven
departments right but we have only five
five rows here so out of the seven rows
it's it's checking with these seven rows
every record coming with this every
record coming through this ring link is
checking with this link with the
reference and then it is checking if the
record exists it gonna pass on to the
next stage if it doesn't exist it
doesn't pass to the next stage so that's
the reason we have only five rules so
let's go to the diet directors see
everything is good enough so yep let's
see how the data looks like
so let's look on to the data okay and
see now how it look like right because
we have this we don't have we didn't
have Department name in our MP data
right but we did have Department name in
our Department data so we are just like
having a common we are a just Department
name to our whole whole data so this is
how it works you might be wondering what
did what did the other things because in
the department we did have 50 60 and 70
if you remember right see in the
department we did have 50 60 and 70
right so so in a 50 60 70 you might be
wondering what happened so the thing is
the thing with the hashed five stage is
basically it gonna create it gonna
create like a hash file usually is
composed of two files one is the data
file and the other is the over loop file
the data file like which is like the
rows they like the file which there are
French thing and all that this thing
reference thing which has which we
should which gonna meet with this thing
it gonna store in data file the other
overflown thing which doesn't have like
rows that cannot fit within the data
file will then spill over to the next
spill over to the overflow file
so that is basically composed of two
files so yep if you see here you can
just get a notice of that if you see
here if you see here see a hash file
right see over 30 so data data file this
is the data file which we have liked for
four of our I mean five I guess yep
fire fire fire rules are in the data
file and the three are two other rows
which we have which are like six 50 60
and 70 they don't have we don't have it
in our data file so this is how this
looks like this is where the data goes
so yep so you might be also wondering if
we have like so what happens if we have
seven rows here and fire rows in here
right so it doesn't it doesn't matter I
mean it doesn't really change much if we
have like seven D seven Dept table here
and the reference EMP table here the
output data would be like the output
okay let me let me show how to output
data looks like so the output data would
be like four five and then we have other
department names too like 50 60 70 and
department names and we would have a
number and salary as null so that is how
it looks like if if we have this thing
so yeah that's pretty much about this
thing yeah the hash value of one more
thing finally the hashed fry it's
basically hash fights are basically for
three main uses one is for small
reference looks reference lookups by the
primary key defined primary key in our
case is Department number Department
number so that's one of the users and
the other is our when like staging file
small staging files usually like when
you have a reference capability so
that's when it comes into place so these
are the pretty much things did gonna if
that that's a place where we gonna use
our hash file so yep now that we have a
computer of file stages so we will get
into the car a processing status in our
next
next video tutorial because it's already
40 minutes I guess I don't want make you
so tired with all of it in one so I in
one video so yet let's steal back the
cross a few of the crossing stages
because a few of them you're gonna see
them again in parallel stages so yep
that's it guys hope you all got it let's
let's come in please guys try to
practice it with your own data so it'll
be very clear and make sure you have you
defined parameters and everything
because in real time you do not directly
browse and everything so make sure you
pass you parameterize all your values
and I'll act thanks guys thanks for
watching this video are gonna come up
with predict new tutorials pretty soon
thank you thanks have a nice day