Hive Table usage in Hadoop

In data engineering world, many times we may have to make schema changes to an existing table
or rebuild an existing table's data due to multiple reasons like bugs fell through the cracks
or new business requirements and you realized the data needs rebuild to rectify, and so on.

Very first thing that we need to understand in such scenarios is; what was the impact or who has been using that data in the past 0-6 months or even more depending on the criticality of the data set.

To answer such queries, following code snippet will be very useful to identify the impacted users:

<<
Step:1

 hadoop jar /apache/hadoop/share/hadoop/tools/lib/hadoop-streaming-2.7.1.2.4.2.66-4.jar \
-Dstream.non.zero.exit.is.failure=false \
-Dmapred.job.queue.name=<QUEUE Name> \
-Dmapred.job.name="grepper" \
-Dmapred.reduce.tasks=1 \
-input /logs/<HADOOP NAMENODE>/auditlog/YYYY-* \
-output <a HDFS location where your account has write access>/ \
-mapper 'zgrep <table name that you are looking to identify usage>'
Step2:

 hadoop fs -text <a HDFS location where your account has write access>/part-00000.gz | awk '{print $6}' | sort | uniq

 >>

Above snippet helps us to parse the logs using streaming jar for a specific date regex, and writes only the interested logs information to a partition/file.

So that we can review the users list and send eNotification as needed to notify the users on the impact.




Comments

Post a Comment

Popular posts from this blog

Parsing a long text and fetching desired pattern strings using SPARK SQL

Image
  One of my co-workers from analyst group has approached me for helping with a real-time problem that she was facing in extracting all matching words/substrings within a long text column using spark SQL. The long text is basically an email body that a seller sends out to his/her customers with many urls like their store webpage, facebook page etc. So first thing obviously she tried was using  regex_extract  built-in function. SET spark. sql .parser.escapedStringLiterals= true ; select REGEXP_EXTRACT( lower ( ‘ Thanks for your purchase Leave your feedback “ http //feedback.abc.com ” . Like us on facebook “ https //www.facebook.com/abcPets ". Visit our site here “www.abcpets.com" and and save 7 of your next purchase use code save7 at checkout. Call us 123 456 7890 . ’ ), ’ ((http //|https //|http://|https://|(www.)) \ w+ \ . \ w+) ' , 0 ) AS URL_PATTERN_1 ; But the result was only first matching string as shown below: URL_PATTERN_1 — — — — — — — — — - http...
Read more

SQL to pivot rows to columns

-- Create a sample table   create multiset table mydbname.stscorecard (     id integer ,     sname varchar (20),     subject varchar (20),     score integer ) primary index (id) ; -- Insert sample data for student1 insert into mydbname.stscorecard (id, sname, subject, score) values (1, 'student1' , 'Maths' ,90); insert into mydbname.stscorecard (id, sname, subject, score) values (1, 'student1' , 'Science' ,91); insert into mydbname.stscorecard (id, sname, subject, score) values (1, 'student1' , 'Social' ,99); -- Insert sample data for student2 insert into mydbname.stscorecard (id, sname, subject, score) values (1, 'student2' , 'Maths' ,92); insert into mydbname.stscorecard (id, sname, subject, score) values (1, 'student2' , 'Science' ,93); insert into mydbname.stscorecard (id, sname, subject, score) values (1, 'student2...
Read more

Spark SQL - Performance Tuning tips

In this article I am sharing my understanding and experiences with Spark SQL since we just completed migrating 300+ tables in my domain, from Teradata based ETL processes to Spark SQL process using HDFS as backed end data storage cluster. How can we maintain the distribution of data like we have in Teradata based on Primary Index? Spark does support this feature using “ distribute by ” method. As the name reflects it distributes the data based on the columns/(valid expressions) you pass it distributes the data across the partitions evenly. Of course if the columns/(valid expressions) chosen by us itself is not balanced, again we will have uneven distribution of data. Which you may even have experienced in Teradata by choosing inappropriate primary index column. Well, why do we have to distribute the data evenly in all partitions? Answer is simple. Let’s say in your team there are 4 engineers and your scrum lead is assigning the work load unevenly, will it be of any good? ...
Read more