Today, I will explain about “How to get position and values separated by delimiter in a string”. Delimiter may be comma,pipe,colon etc. STEP1 : Get position of all delimiters in a string. I am explaining with SQL-server as Database. ;with T as ( select 0 as row_id, charindex(‘,’, @string) pos union all select pos + 1, charindex(‘,’, @string, pos + 1) from T where pos > 0)select * from T For example , consider a string “Harsimran,Kaur,91-123-456-7890” . So, it has first name and last name and dummy mobile number.…
Category: DATABASE
Check duplicate record in Hive
Today, I will discuss about ” How to automate the process where in you can check entire row duplicate record in hive”. As I have mentioned in all Automation blogs, I will share the pseudo code.STEP1: In hive , use “desc table_name” , this command will give you column names along with datatype and data length. Store the output of this command in a file , say HIVE_TABLE_DDL.txt STEP2 : Read the file HIVE_TABLE_DDL.txt using “cat” command. cat HIVE_TABLE_DDL.txt | awk ‘{print $1}’ ORS=’,’ | sed ‘s/,$//’ * awk'{print $1}’…
create table using file having variable length for each field in hive
Today, I will discuss about to how to create Hive table using file in which fields length is different for all fields .To put in differently, it is neither fixed length file nor delimited file.Below is the example of file contentDefinition of a fileFIELD1, length 2, value are 10 ,10FIELD2, length 5 , value are 5,125FIELD3, length 6 , value are 3,12FIELD4, length 7, value are ABC,EFFIELD5, length 4, value are 12,21FIELD6, length 10, value are 15,10 In order to create table for such files, use the below syntax for…
Equivalent of rollup in hive
Today, i will discuss about ” equivalent of rollup in hive”. There are scenarios where we need to replace the keyword rollup from the queries as it might not be working as expected. To put it differently, we might need to transform the oracle/sqlserver query to hive where rollup doesn’t give same results which we got in oracle. In an employee table. I have EMP_ID,DEPT_NAME,SAL_MONTH,SALARY columns. Query-1 with rollup :select EMP_ID,DEPT_NAME,SAL_MONTH,sum(SALARY) from EMP group by EMP_ID,DEPT_NAME ,rollup( SAL_MONTH ); Equivalent Query-1 without rollup using group by clause : select EMP_ID,DEPT_NAME,null,sum(SALARY)…
Masking the mobile number in hive
Today, I will discuss about the masking the details of the customer. It may be mobile number, Bank Account Number,Security PIN,customer_ID etc.For this, we can use combination of substring and regexp_replace function in hive.For example, you want to mask the first 5 digit of a mobile number like 1234567890 as #####67890, then use the below query. select concat(regexp_replace(substring(‘1234567890′,1,5),'[0-9]’,’#’),substring(‘1234567890’,6,5)); You can use mask_show_last_n function but here concern is, it will replace number by “n”. See the below Query : select mask_show_last_n(‘1234567890’,5) ; Result is nnnnn67890
Date functions in Hive
Today, I will share list of queries related to “date” logic in which developers generally face issues during Hive query execution. * Query to get the sysdate select from_unixtime(unix_timestamp(current_timestamp())) * Query to get the yesterday’s date. Here 86400 is the number of seconds in a day(24 Hrs * 60 Minutes(in a hour)* 60 Seconds(in a minute)) select to_date(from_unixtime(unix_timestamp(current_timestamp(),’yyyy-MM-dd’)-86400*1));* Substract the x number of days from the particular date. select to_date(from_unixtime(unix_timestamp(current_timestamp(),’yyyy-MM-dd’)-86400*x)); * Convert the sysdate to YYYY-MM-DD format select to_date(from_unixtime(unix_timestamp(current_timestamp(),’yyyy- MM-dd’)));* Convert the sysdate to YYYYMMDD format select regexp_replace(to_date(from_unixtime(unix_timestamp(current_timestamp (),’yyyy-MM-dd’))),’-‘,”);* Convert…
Run hive queries online
Today, I will discuss about “How to run hive queries online”. Below are the steps for the same.* Go to below link https://demo.gethue.com/hue/accounts/login?next=/ * Enter the username and password as “demo”.* Click on “Sign in” Button. Below Screen will appear. As you can see, many users have created their databases. * In order to create your own database, Click on “+” icon.* Fill Database Name and description . * Click on submit button. The output screen will appear on the top-right section.So, we have database ready and name is “hive_testing”…
Query to get all records not matching in all tables.
Today, I will discuss about the scenario/Problem which i faced in current project. Problem Statement : You have two Employee tables having data mentioned below. Emp-1 EmpId EmpName 1 Shivani 2 Gaurav 3 Radhe 4 Rahul 5 Anil Emp-2 EmpId EmpName 1 Shivani 6 Ashok 3 Radhe 7 Vikram 4 Rahul Output : EmpId EmpName 2 Gaurav 5 Anil 6 Ashok 7 Vikram Solution : Here, you need to use except or minus based on the database you are using and union clause. See the below query for the same.…
Avro Output in Pentaho
Today, I will discuss about Avro Output Component in Pentaho. In my previous blog, I have share my experience about Avro input component where Data Deserialization happens. In this Component, Data Serialization Happens. So, if you have data in a text format, you can convert the same in Avro format as well. As soon as you do this conversion, a Schema file also get generated along the Avro file.This all can be achieved through Avro Output Component in Pentaho I have designed a very simple Transformation wherein we have csv…
Avro File Input In Pentaho
Today, I will discussing about the Avro input component in Pentaho. Avro uses the concept of serialization and De-serialization. Serialization means processing the data into binary format. Its very clear that if we have data in binary format ,its unreadable and hence very effective way to transfer over the network. Therefore, many Organization are adopting this technique due to data security concerns. Deserialization means convert the binary formatted data into a readable form. Now the question comes how binary data is deserialized. Here , comes the concept of Schema file.…