Hive: Auto Increment column & Incrementing existing row_sequence

We already have a existed jar for this, no need to write our own udf.                                                                 Down load (hive-contrib-1.1.0.jar)jar in below link ,                                                                                       http://www.mvnrepository.com/artifact/org.apache.hive/hive-contrib/1.1.0                                                                                                                                                                                      hive> add jar /home/hadoop/Desktop/hive-contrib-1.1.0.jar;

hive> CREATE TEMPORARY FUNCTION row_sequence as 'org.apache.hadoop.hive.contrib.udf.UDFRowSequence';
OK

CREATE TABLE IF NOT EXISTS users_inc
(
ID string,
name  string
) row format delimited fields terminated by ',' stored as textfile;

id ID value is int ,Then (ID int ...use this when creating table)
hive> insert into table users_inc
select m.max+row_sequence() as inc , ename
from (select ename from emp_csv) e
join
(select max(ID) as max from users_inc) m;

handling nulls when inserting table first time : (max val we get null when table is empty)
================
insert into table users_inc
select m.max+row_sequence() as inc , ename
from (select ename from emp_csv) e
join
(select coalesce(max(ID),0) as max from users_inc) m;

select * from users_inc;


===>for alpha numeric id increment value
insert into table users_inc
select concat("ABC-",m.max+row_sequence()) as inc , ename
from (select ename from emp_csv) e
join
(select coalesce(max(substr(ID,5)),0) as max from users_inc) m;

---->regexp_replace
insert into table users_inc
select regexp_replace(concat("ABC-",m.max+row_sequence()),'\\.0','') as inc, ename from (select ename from emp_csv) e
join
(select coalesce(max(substr(ID,5)),0) as max from users_inc) m;                                                                                                                                                                                                                                coalesce() : COALESCE(T v1, T v2, ...)
Returns the first v that is not NULL in list of values,                                                                             or   NULL if all v's are NULL.

unix path classpath settings

PATH=$PATH\:/u01/app/oracle/product/12.1.0/client_1/bin ; export PATH
PATH=$PATH\:/u01/app/oracle/product/12.1.0/client_1/bin ; export PATH




setenv ORACLE_HOME $ORACLE_HOME\:/dir/path

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/client_1;
export ORACLE_BASE=/u01/app/oracle;
export ORACLE_SID=EFMT;




https://somireddy.wordpress.com/2013/04/22/sp2-0667-message-file-sp1-msb-not-found/



export LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0/client_1/lib;
export TNS_ADMIN=/u01/app/oracle/product/12.1.0/client_1/network/admin;


 sqlplus usrname/pwd




----->JAVA unix
export CLASSPATH=/opt/IBM/WebSphere/AppServer/java_1.7.1_64/lib:.
PATH=$PATH\:/opt/IBM/WebSphere/AppServer/java_1.7.1_64/bin;export PATH

unix script to connect oracle DB

Connect_db_oracle_unix.sh
#This Next line contains the name of a file, if you want to save all data #outside.
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/client_1;
export ORACLE_BASE=/u01/app/oracle;
export ORACLE_SID=ORCL;//your db sid value

PATH=$PATH\:/u01/app/oracle/product/12.1.0/client_1/bin ; export PATH;

file="outputfile.txt"
sqlplus -s usrnm/pwd<< EOF
set linesize 10000
set head off
SET COLSEP "|"

spool $file

SELECT * from users where rownum<5;

spool off

MQ java api code to retrieve all msgs

private void receiveAllMQmsgs()
{
   int openOptions = CMQC.MQOO_INQUIRE + CMQC.MQOO_INPUT_SHARED + CMQC.MQOO_FAIL_IF_QUIESCING;
   MQGetMessageOptions getOptions = new MQGetMessageOptions();
   getOptions.options = CMQC.MQGMO_NO_WAIT + CMQC.MQGMO_FAIL_IF_QUIESCING;
   boolean getMore = true;
   MQQueueManager qMgr = null;
   MQQueue queue = null;
   MQMessage receiveMsg = null;

   try
   {
      qMgr = new MQQueueManager(qManager);
      queue = qMgr.accessQueue(inputQName, openOptions);

      while(getMore)
      {
         try
         {
            receiveMsg = new MQMessage();
            queue.get(receiveMsg, getOptions);
            byte[] b = new byte[receiveMsg.getMessageLength()];
            receiveMsg.readFully(b);
            System.out.println("Message-->" + new String(b));
         }
         catch (MQException e)
         {
            if ( (e.completionCode == CMQC.MQCC_WARNING) &&
                 (e.reasonCode == CMQC.MQRC_NO_MSG_AVAILABLE) )
            {
               System.out.println("Bottom of the queue reached.");
               getMore = false;
            }
            else
            {
               System.err.println("MQRead CC=" +e.completionCode + " : RC=" + e.reasonCode);
               getMore = false;
            }
         }
         catch (IOException e)
         {
            System.out.println("MQRead " +e.getLocalizedMessage());
         }
      }
   }
   catch (MQException e)
   {
      System.err.println("MQRead CC=" +e.completionCode + " : RC=" + e.reasonCode);
   }
   finally
   {
      try
      {
         if (queue != null)
            queue.close();
      }
      catch (MQException e)
      {
         System.err.println("MQRead CC=" +e.completionCode + " : RC=" + e.reasonCode);
      }
      try
      {
         if (qMgr != null)
            qMgr.disconnect();
      }
      catch (MQException e)
      {
         System.err.println("MQRead CC=" +e.completionCode + " : RC=" + e.reasonCode);
      }
   }
}

MQ java api useful sites

Read/write MQ  all msgs:
http://stackoverflow.com/questions/28645497/unable-to-retrieve-message-from-mq-queue

https://endrasenn.wordpress.com/2010/01/27/readwrite-to-ibm-mq-sample-java-code/---read/write
http://www.mqseries.net/phpBB/viewtopic.php?t=41438

http://www.ibm.com/developerworks/websphere/library/techarticles/0602_currie/0602_currie.html


http://bencane.com/2013/04/22/websphere-mq-cheat-sheet-for-system-administrators/
https://www-01.ibm.com/support/knowledgecenter/SSFKSJ_7.5.0/com.ibm.mq.ref.adm.doc/q083180_.htm

unix cmds

$ /usr/local/bin/pbrun ksh

--> validate xml
xmllint --valid xmlfile1.xml

xmllint --valid --noout doc.xml
xmllint --schema schema.xsd doc.xml




-->uniq -d test
 Print only Duplicate Lines using -d option (http://www.thegeekstuff.com/2013/05/uniq-command-examples/)

-->List the files containing a particular word in their text
grep check * -lR

grep '/opt/BAES_HOME/conf/wlm/fpfa' * -lR  (it will show all the files having word '/opt/BAES_HOME/conf/wlm/fpfa')


---->grep -c 'id="' DJRC_WL-AMe_XML_201510072359_F.xml
count word id=" in a file
grep -o "<item>" a.xml | wc -l

--->Find Java class inside a folder of JARS

 for a in *.jar
 do
 echo $a; unzip -t $a | grep -i CLassFileName
 done
==>unzip -t my.jar
==>unzip -t quartz.jar|grep -i 'Pair.class'


-->
awk 'FNR==1 && NR==1{printf $1"|"}FNR==1 && NR!=1{printf $1"\n"}' file1.txt file2.txt

paste -d"|" file1.txt file2.txt
paste -d"|" file1.txt file2.txt
http://www.folkstalk.com/2012/09/paste-command-examples-in-unix-linux.html


-->if ORACLE_HOME is not defined
find / \( -name catalog.sql -o -name sql.bsq \)
/u01/app/oracle/product/12.1.0/client_1/bin

https://kb.iu.edu/d/acar

-->http://design.liberta.co.za/articles/how-to-remove-spaces-from-filenames-in-linuxunix/

--grep -P "[\x80-\xFF]" *.*

->to remove non printable chars in a file.

perl -pe's/[[:^ascii:]]//g' < ACCOUNTS_DAILY_20121206.txt > newfile_accounts_ascii.txt

--->cut -d "," -f1-10,20-25,30-33 infile.csv > outfile.csv
-->http://www.shellhacks.com/en/Printing-Specific-Columns-Fields-in-Bash-using-AWK
-->ls -p | grep -v /