Skip to main content

Generating HTML Report From Hive and Sending thru Email

Script to Generate HTML Rpt #!/bin/ksh TODAY=`date +'%Y %b %d'` rm file.csv rm report.html #Hive Query to Fetch Results from Hive Table hive -S -e "SELECT COLA,COLB FROM hive_tbl;" > file.csv #Convert the Results to Comma Separated sed -i 's/\t/,/g' file.csv #awk command to parse the file #recCNT=`awk -F ',' '{print $1}' file.csv #Converting CSV File to HTML echo "<HTML><BODY>" > report.html echo "<h2><font color=blue>Report Header</font></h2>" >> report.html #loop through records in CSV and Convert to Table awk 'BEGIN{ FS="," print "<TABLE border="1"><TH><font color=brown><center> COLA HEADER</center></font></TH><TH><font color=brown><center>COLB HEADER< /center></font></TH>"} { printf "<TR>" for(i=1;i<=NF;i++) printf "<TD>%s</TD>", $i print "</TR>" } END{ printf "</TABLE><BODY></HTML>" }' file.csv >> report.html #Sending email using mutt command /usr/bin/mutt -e 'set content_type=text/html' -s "email Subject :$TODAY" email_id1 email_id2 < report.html #sending email using normal mailx command with attachment #{ #echo "Hi" #echo "message" #} | mailx -s "Subject:" -a report.html email_id1,email_id2
*****************************************************************************
#Report to Generate Header with Background Color and Font #!/bin/ksh TODAY=`date +'%Y %b %d'` rm file.csv rm report.html #Hive Query to Fetch Results from Hive Table hive -S -e "SELECT COLA,COLB FROM hive_tbl;" > file.csv #Convert the Results to Comma Separated sed -i 's/\t/,/g' file.csv #awk command to parse the file #recCNT=`awk -F ',' '{print $1}' file.csv #Converting CSV File to HTML echo "<HTML><HEAD>" > report.html echo "<style> .myTable { width: 100%; text-align: left; background-color: white; border-collapse: collapse; } .myTable th { background-color: yellowgreen; color: white; } .myTable td, .myTable th { padding: 10px; border: 1px solid black; } </style> </HEAD> <BODY>" >> report.html echo "<h2><font color=blue>Report Header</font></h2>" >> report.html #loop through records in CSV and Convert to Table awk 'BEGIN{ FS="," print "<TABLE class='myTable'><TR><TH>COLA HEADER</TH><TH>COLB HEADER</TH></TR>" } { printf "<TR>" for(i=1;i<=NF;i++) printf "<TD>%s</TD>", $i print "</TR>" } END{ printf "</TABLE><BODY></HTML>" }' file.csv >> report.html #Sending email using mutt command /usr/bin/mutt -e 'set content_type=text/html' -s "email Subject :$TODAY" email_id1 email_id2 < report.html #sending email using normal mailx command with attachment #{ #echo "Hi" #echo "message" #} | mailx -s "Subject:" -a report.html email_id1,email_id2 #Note: Use https://www.quackit.com/html/codes/tables/html_table_background_color.cfm for Checking the Styles #in CSS
*****************************************************************************
Labels:

Comments

Post a Comment

Popular posts from this blog

LookML

  What Is LookML? LookML is a language for describing dimensions, aggregates, calculations, and data relationships in a SQL database. Looker uses a model written in LookML to construct SQL queries against a particular database. LookML Projects A LookML Project is a collection of model, view, and dashboard files that are typically version controlled together via a Git repository. The model files contain information about which tables to use, and how they should be joined together. The view files contain information about how to calculate information about each table (or across multiple tables if the joins permit them). LookML separates structure from content, so the query structure (how tables are joined) is independent of the query content (the columns to access, derived fields, aggregate functions to compute, and filtering expressions to apply). LookML separates content of queries from structure of queries SQL Queries Generated by Looker For data analysts, LookML fosters DRY style...
1 comment
Read more

Data Warehouse 101 - Part 2

  Traditional Data Warehouse Concepts A  data warehouse  is any system that collates data from a wide range of sources within an organization. Data warehouses are used as centralized data repositories for analytical and reporting purposes. A traditional data warehouse is located on-site at your offices. You purchase the hardware, the server rooms and hire the staff to run it. They are also called on-premises, on-prem or (grammatically incorrect) on-premise data warehouses. Facts, Dimensions, and Measures The core building blocks of information in a data warehouse are facts, dimensions, and measures. A  fact  is the part of your data that indicates a specific occurrence or transaction. For example, if your business sells flowers, some facts you would see in your data warehouse are: Sold 30 roses in-store for $19.99 Ordered 500 new flower pots from China for $1500 Paid salary of cashier for this month $1000 Several numbers can describe each fact, and we call these...
Post a Comment
Read more

A Deep Dive Into Google BigQuery Architecture

Introduction Google’s BigQuery is an enterprise-grade cloud-native data warehouse. BigQuery was first launched as a service in 2010 with general availability in November 2011. Since inception, BigQuery has evolved into a more economical and fully-managed data warehouse which can run blazing fast interactive and ad-hoc queries on datasets of petabyte-scale. In addition, BigQuery now integrates with a variety of Google Cloud Platform (GCP) services and third-party tools which makes it more useful. BigQuery is serverless, or more precisely data warehouse as a service. There are no servers to manage or database software to install. BigQuery service manages underlying software as well as infrastructure including scalability and high-availability. The pricing model is quite simple - for every 1 TB of data processed you pay $5. BigQuery exposes simple client interface which enables users to run interactive queries. Overall, you don’t need to know much about underlying BigQuery architecture or...
1 comment
Read more