Hadoop/Hive Data Ingestion

Data Ingestion:

    Files : Stage the files & use Hadoop/Hive CLI 
    Database: Sqoop, no CDC for smaller tables but only for larger (10M+) , use -m option for large db        dump, NiFi is another option 
    Streaming: NiFi , Flume , Streamsets. NiFi is popular.

   
File Ingestion:   

CSV  into TEXTFILE : 

Overwrite: 
  • Move the file to HDFS, create an external  TEXTFILE table on top of the HDFS location.    
  • You can also create the table and use "LOAD DATA INPATH LOCAL localpath OVERWRITE INTO  tablename".  This approach will be handy for internal tables where location is not specified and if you  don't know the HDFS warehouse location where table is created.   You can use LOAD DATA command for loading data from local as well as hdfs file. 
Append: 
  • You can still use "LOAD DATA INPATH ....INSERT INTO tablename" .
  • create a temporary table using overwrite approach and then insert into original table from temporary table.
Same approach will work for partitioned tables also.

  CSV file into PARQUET:  you need to first create a temporary TEXTFILE table and create a PARQUET table and then insert from TEXTFILE into PARQUET table. 
  CSV file into AVRO : similar to PARQUET
    

PRECAUTIONS:

Try to minimize  number of INSERT operations. Each single operation results into Map Reduce job and it also creates additional files into HDFS.  So always try to club as many INSERT operations into single one as possible. Especially try to merge  individual inserts into single one. 

HIVE is especially not suited for update/delete operations.  Insert/append is still ok. 

UPDATE in HIVE basically creates new file and marks earlier file for delete. HIVE is not build for UPDATES, so try to minimize those. See link for more details. 

Try to do COMPACTION after each such operation to cleanup unnecessary files.  

    

Comments

Post a Comment

Popular posts from this blog

SQL

Joins - Not always very obvious ( Following example uses Hive ). create table x_temp ( name string, p_date int  ) insert into x_temp(name,p_date) values ('x1',20181025),('x2', 20181026), ('x3',20181027) create table y_temp ( name string, s_date int  ) insert into y_temp(name,s_date) values ('x1',20181025),('x2', 20181026), ('x3',20181027) create table z_temp ( name string, p_date int, s_date int ) insert into z_temp(name,p_date,s_date) values ('z1','20181025','20181025') select x.*, y.* from x_temp x, y_temp y, z_temp z  where x.p_date != z.p_date and  y.s_date != z.s_date) generates 4 records... x.name,x.p_date,y.name,y.s_date x2, 20181026, x2, 20181026 x3, 20181027, x2, 20181026 x2, 20181026, x3, 20181027 x3, 20181027, x3, 20181027 select x.*, y.* from x_temp x, y_temp y, z_temp z  where x.p_date != z.p_date or  y.s_date != z.s_date) generates 8 records x.name,x.p_date,y.
Read more

Analytics

Types of Analytics ( Notes from AWS Course )  Descriptive - What happened ?  hindsight  Diagnostic - Why did it  happen?  hindsight & insight  Predictive  - What will happen ? insight & foresight  Prescriptive - What should I do ? foresight  Cognitive & AI  - What are recommended actions ? foresight & hypothesis input  Popular BI Tools:  https://www.cio.com/article/3284415/10-bi-tools-for-data-visualization.html
Read more

HIVE

1. When doing multi table joins, except the last ( right most ) table, other tables are buffered in the reducers. So to have less memory footprint on reducers, largest tables should be the last one as it is not buffered but streamed to reducers.   For Impala , it is different. 2.  You can filter the unwanted records within the join itself. SELECT  a.*,   b. * FROM a LEFT OUTER JOIN b ON ( a.key=b.key  AND  b.name= 'Tim'   AND a .name= 'Kim' ) 3. Joins are left associative. So execution happens from left to right and the result of joins is  fed into next join operation. 4. To find the value of variable     set hive.execution.engine 5. To find values of all variables set  6. If you want to pass different hive configuration settings in hive shell, you can use --hiveconf. For example hive --hiveconf hivi.optimize.sort.dynamic.partition=true --hiveconf hive.exec.dynamic.partition.mode=nonstrict 7.  In case you accidentally drop the p
Read more