Scribd
Upload
7 views3 pages

Data Ingestion Process for Hive Tables

The document outlines a process for ingesting files into a Hive database, including steps for checking database status, reading metadata, validating file types, and transferring data to HDFS. It details the use of regex patterns for parsing fixed-width files and the necessary checks for file integrity before ingestion. If successful, the data is loaded into staging and raw tables, with metadata entries updated, while failures result in moving files to an exception zone.

Uploaded by

Anand Walandikar
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
7 views3 pages

Data Ingestion Process for Hive Tables

The document outlines a process for ingesting files into a Hive database, including steps for checking database status, reading metadata, validating file types, and transferring data to HDFS. It details the use of regex patterns for parsing fixed-width files and the necessary checks for file integrity before ingestion. If successful, the data is loaded into staging and raw tables, with metadata entries updated, while failures result in moving files to an exception zone.

Uploaded by

Anand Walandikar
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

[Link] Db_status_check.

[Link] metadata from file_ctlgue table into an array.


[Link] Regex-Pattern from [Link] variable Hive_file=regex_pattern
What is Regex-Pattern:
It has Stg table and Raw table creation hql script,as shown below.
How a fixed width file is read (regex given in defn)–Creating table defn for STG TABLE.
Later on data is moved from STG table to RAW table.

[Link] souce to target encoding .i.e Latin1 to UTF 8 with iconv.

[Link] Validations:

[Link] md5sum value for the file to check if it is already ingested or not.(Genearte SQL query to see if this
md5 already exists for the given odate and sts=’success’).If not exist then ingest.

[Link] type of file-:


if FIXED WIDTH-
[Link] row length.
[Link] actual row length with metadata row length.

if DELIMITED-
[Link] no. of columns.
[Link] actual no. of columns with metadata no . of columns

[Link] row_count from file. Get row_count info from file footer. Compare if it is equal.

If above conditions are satisfied,only then transfer the file to HDFS

[Link] Load in HDFS.


Step1: Inside the Hive_file , where STG table is defined ,we have a regex pattern –which helps us to
parse/understand the fixed width file. The regex_pattern and awk command is extracted using grep.

Step2:Below is hive_file,showing BASE24 STAGING table defn and the regex_ptn with awk command to parse the
file .
The fixed width file is passed thru the above awk command ,which puts the delimiter ‘<^>’ in between all the fields
and makes it a delimited file (<^>).IF you see STG table also expects a ‘<^>’ delimited file.
The header and footer is also added to the file .and finally a delimited file is created which is fit for loading into the
STAGING Table.

Step3:Using LOAD DATA LOCAL INPATH ’<Landing-Zone>’ into STAGING_TBL


Data is moved from from landing to HDFS Landing zone and thus the STG table is populated with current batch-data.
AS it is a STG tbl,it holds only today’s data.

Step4:Data is then moved from STG table to RAW Table ..where in extra fields like odate,prcs_id,batch_date,time are
added via hivevariables.
Hive_Load.sql below

How data loading is done?


We have a hive_load.sql script .which is called from main file_exec script .All hive variables are passed along with
the hive –hivevar1 –hivevar2–f hive_load.sql
Once data is loaded to RAW table,Data is verified via a hql script-hive_count.
Count of records of RAW table shud match with row_count of file.

[Link] Succesful ingestion,a entry is done to file_exec Oracle metadata.


AND File is archived to Archive Zone

[Link] at any point the job fails during file validation or data movement,the file is moved to exception zone in HDFS.

You might also like