Tuesday, September 22, 2020

Snowflake Merge Statement Syntax, Usage and Examples

In a data warehouse, it is common to manipulate the table data. Sometimes, you have to manipulate the source table using data from another table. Many relational databases support Merge command that can perform update or delete simultaneously.

Merge Statement:

The merge command in SQL is a command that allows you to update, delete, or insert into a source table using target table. Based on the matching condition rows from the tables are updated, deleted, or new records are inserted.

The target table can be a view or sub query. Use sub query if you need to use join to merge tables.

The MERGE command in Snowflake is similar to merge statement in other relational databases.

Syntax:

Megre into <target_table>

Using <source>

On <join_expr>

When matched [ AND <Case_predicate> ] then

{ UPDATE set <col_name> = <expr> [ , <col_name2> = <expr2>….. ] | DELETE  } [ …… ]

When not matched [ AND <case_predicate> ] then INSERT [ (

<col_name> [ , ….. ] ) ] values ( <expr> [ , …. ] )

 

 

Ex:

select * from MERGE_TEST;

 

+---+----+

| A |  B |

|---+----|

| 0 | 10 |

+---+----+

 

select * from MERGE_TEST2;
 
+---+----+
| A |  B |
|---+----|
| 1 | 11 |
| 2 | 12 |
| 0 | 13 |
+---+----+
 
Update Table:
 
Merge into merge_test
Using merge_test2 on merge_test.a = merge_test2.a
When matched then 
Update merge_test Set merge_test.b = merge_test2.b;
 
O/P:
 
select * from merge_test;
+---+----+
| A |  B |
|---+----|
| 0 | 13 |
+---+----+
 
Insert Table:
 
Update and Insert Table:
 
 
Merge into merge_test
Using merge_test2 on merge_test.a = merge_test2.a
When matched then
Update merge_test set merge_test.b = merge_test2.b
When not matched then 
Insert into (a,b) values (merge_test2.a, merge_test2.b);
 
O/P: 
 
select * from merge_test;
+---+----+
| A |  B |
|---+----|
| 1 | 11 |
| 2 | 12 |
| 0 | 13 |
+---+----+
 
Delete and Insert Table:
 
Merge into merge_test
Using merge_test2 on merge_test.a = merge_test2.a
When matched then
Delete
When not matched then
Insert into (a,b) values (merge_test2.a,merge_test2.b);
 
O/P: 
 
select * from merge_test;
+---+----+
| A |  B |
|---+----|
| 1 | 11 |
| 2 | 12 |
+---+----+
 
 

 

 

Linux Basic commands

 

Date:  Display the System date.

                Date +  <formatspecifier>

Formatspecifiers:

                %D  – Date

                %T   ­– Time

                %d   – Date in the month

                %m  – month

                %Y   – Year

[root@Hemanth ~]#  date +%d-%m-%Y

14-september-2020

2.       Cal :  Displays the calendar

Cal -3    :  shows the 3 months calendar(previous,current,coming).

Cal –y    :  Displays Complete year calendar.

Cal –j     :  Displays the Julian calendar ( count of days in a year)

Cal –m  :  Calendar starts from Monday.

Cal mm year : Display the given month in the given year calendar.

3.       Clear : clear the screen  or  (ctrl+l)

4.       Scroll the Screen  (Shift + pageup/pagedown).

5.       dmidecode :  Displays the system Hardware information.

Dmidecode –t <no> : Displays the given no type device information.

Numbers:

1 : System Bios

2 : Mother Board Information

4 : Processor information

6 : RAM Information.

6.       Fdisk –l : displays the secondary storage device information

(storage sige / no of partitions)

7.       Lsusb : displays the  list of USB devices connected.

8.       Free : Get the RAM and swap memory information.

                Free –m/-g : Displays informstion in mega and gega bytes.

9.       Df : Display the information of the partitions used by the Linux operating system (mounted device information).

Df –h : Display the size in mega and giga bytes.

10.   Hostname : Display the Linux System Name.

11.   Uname : Display the name of the unix system.

Uname –r : Display the kernel version.

Uname –a : All the daetails about OS and architecture.

12.   Arch : useful to know the machine architecture.

13.   Uptime : Display the system boot tome information.

14.   Runlevel : Display the prev and current Run level information.

15.   Who : Display the list of terminals and users working in the linux system

Who –r : Display the RUN level information.

16.   Who am I / who I am : Current working terminal information.

17.    whoami : Display the username of the current terminal.

18.   Tty : current terminal information.

19.   W : Detailed information of the terminal and Login users.

20.   Lastlog : Display all the users  with the last login information.

21.   Last : Detailed Login and logout information of the current users.

Last username : Display the information of the given user.

22.   Ifconfig : Display the all Ethernet devices and IP Address

Ifconfig –a : Display all devices along with inactive devices.

23.   Ping <ipaddress> : Display the given IP addressdevice can  e access from the machine or not?

Ctrl + c ->  Stop the ping operation.

Ping –c <no> <ipaddress> : send the ping request for the given number of times.

24.   Traceroute <url / ipaddress> : Provide the route information with the HOP count

25.   Cat / etc / resolv.conf : DNS server information.

26.   Dig <url> : DNS response information for the given url (domaine information grouper)

27.   Nslookup <url> : Complete information for the given url in the Name Server.

28.   Netstat : Get the running port information in the current system.

Netstat –a : All the open ports information.

Netstat –nat/u :  Information of the ports working with the given protocol.

29.   Daemon : The process which is running in the background.

 

 

 

30.   Ps : display the information of the processes running in the current terminal.

Ps –A: Display all the processes r4unning in the system.

Ps –A –l : Long list information.

31.   Top : Display the dynamic view of the running processes.

</>  :  Scroll the pages

q : close

k : close any application.

Short cut keys:

Ctrl + d : End of the file

Ctrl + z : Send the process to back ground.

Ctrl + c : Kill the process

32.   Kill <processed> : Kill the process of the given process id

Note: By default kill uses the signal no 15.

Kill –l : view the list of signals.

33.   Jobs : useful to view the list of applications running in the background

34.   Bg : Display the recent background process information

35.   Fg <jobid> : bringback the given jobid process to Foreground(default will bring back the recent process).

36.    <command> & : Run the given command in the background.

37.   History : Display the list of executed commands.

!historyid : Execute the command of the history id.

History <no> : Display given no of commands from history

History –c : clear the history

Note : up arrow and down arrow is useful to get the used command one by one.

38.   Id : Display user id and group id of current Login user

Id username : Display the information of the given user.

II.                  Help commands:

1.       <Command> --help :

Display the information about the given command

2.       Info <commandname> :

It is like a chain usefulto provide other information along with the required information.

q -> quit

u -> up

 p -> prev

n -> next

3.       Whatis <commandname> :

Display the purpose of the given command.

4.       Man <commandname> :

Display the detail information of the command.

q -> quit

man <cno> <command> : display the given command information from the given chapter

man –f <command> : Display the information of the chapters where this command exists.

5.       Which <command> :

Display the location of the command.

6.       Whereis <command> :

Display the location of the command and the manual information of the command.

Whereis –m <command> : provides only manual information

7.       Env : Display all the linux environment variables information. ( This information useful for the application in the various cases)

8.       Echo $variablename : print the value in the given variable

Echo $PATH

9.       Print $variablename : Print the value in the given variable

Print $HISTSIZE

10.   Alias : Display all the alias commands

Alias aliasname=”command” - > create the alias command for the given command.

11.   Unalias aliasname : remove the alias command.

12.   Pwd : Display the absolute path of the current working directory.

 

Friday, September 18, 2020

Pivoting and unpivoting Data in ODI 12c

 We have recently added several new Mapping Components in

Oracle Data Integrator 12c such as Pivot or Unpivot. In this blog post we will
walk you through an example of how to use the new Pivot Component.

You can use the following SQL statements to recreate this
example in your environment. It will create the source (PIVOT_SRC) and target
(PIVOT_ TRG) tables used in this article in your database then you can reverse engineer them in ODI.

CREATE TABLE pivot_src
(

year NUMBER,

quarter VARCHAR2(255),

sales NUMBER

);

 

insert into pivot_src
values (2012, 'Q1', 10.5);

insert into pivot_src
values (2012, 'Q2', 11.4);

insert into pivot_src
values (2012, 'Q3', 9.5);

insert into pivot_src
values (2012, 'Q4', 8.7);

insert into pivot_src
values (2013, 'Q1', 9.5);

insert into pivot_src
values (2013, 'Q2', 10.5);

insert into pivot_src
values (2013, 'Q3', 10.3);

insert into pivot_src
values (2013, 'Q4', 7.6);

commit;

CREATE TABLE
pivot_trg(

year NUMBER,

q1_sales NUMBER,

q2_sales NUMBER,

q3_sales NUMBER,

q4_sales NUMBER

);

Our goal is to pivot the data on the Quarter column when
going from PIVOT_SRC into PIVOT_TRG as shown below:

 

Follow these steps to add and configure a Pivot Component in
an ODI 12c Mapping:

First add the Source table PIVOT_SRC into your Mapping, to do so drag and drop the PIVOT_SRC
datastore from the Models into the Mapping.

Next add a Pivot component into the Mapping. This is done by clicking on the Pivot
Component in the Component palette and then clicking on the Mapping diagram. A new PIVOT component will appear in the
Mapping:

 

Drag and drop the YEAR column from PIVOT_SRC into the PIVOT component. There is no
need to add the QUARTER and SALES attributes yet, they will be used later in
the Row Locator and Attributes sections.

Click on the PIVOT component and in the Properties window select the Row Locator
panel. In our example the Row Locator will be the QUARTER column which is
transposed from rows into 4 columns in our target table PIVOT_TRG.

Open up the Expression Editor next to the Row Locator field and select the QUARTER
column from our source table PIVOT_SRC. Then click OK.

Now specify the various values the QUARTER column can take. This is done using the
Row Locator Values table. Click on the + icon under Row Locator Values and add
the 4 possible values: ‘Q1’, ‘Q2’, ‘Q3’ and ‘Q4’.    

Then click on the Attributes panel and add the 4 output attributes which correspond
to each Row Locator values: Q1_SALES, Q2_SALES, Q3_SALES and Q4_SALES.

Next select a Matching Row for the output attributes you just created. The Matching
Row values come from the Row Locator Values entered earlier.
Pick ‘Q1’ for Q1_SALES, ‘Q2’ for Q2_SALES, ‘Q3’ for Q3_SALES and ‘Q4’ for
Q4_SALES.
Finally enter an expression for each of the new attributes, use
PIVOT_SRC.SALES for all of them as we are interested in getting the Sales data
into those columns. You can type the expression using the Expression Editor or
drag and drop the SALES column from PIVOT_SRC into each of the newly created
attributes.

Finally add the target table PIVOT_ TRG and connect the PIVOT component to it. Unselect
the Create Attributes on Source checkbox in the Attribute Matching window and
click OK to finish the Mapping configuration.

In this example you can use the default Physical settings for your Mapping.
Integration Type is set to Control Append by default and the IKM Oracle Insert is used.

Click on Run to execute the Mapping, 2 inserts are performed and you should see the
following data in your target table.

 

If you review the generated code you will notice that ODI leverages the PIVOT
function on Oracle to perform such operation. The Pivot component supports
Oracle as well as any other database supported by ODI 12c.

Unpivot component :

It is used to converting column data to rows .

Take an source table as shown below…

Drag and drop source and target into mapping builder and drag unpivot component into mapping builder as shown below.

 

Now drag first column into unpivot component.

 

 

 

 

Now click on unpivot component ,in attribute properties we can able to see the dragged column(here is year).

Add the attribures by clicking + symbol as per our target as shown below..

In general, select row locator as quarter as shown below.

 

 

 

 

 

 

 

In unpivot transforms  add the values in which we want to convert to rows