mini project HDFS-HIVE
Budget: €30 – €250 EUR
The rendering will be in the form of a report with the list of commands and screenshots of commands, results and NiFi development + export of the nfi template
Work to do:
HDFS:
In HDFS, create in HDFS command lines (hdfs dfs -??????) the following tree structure /data/common/raw/DATABASE_M1/ETUDIANT_M1
In HDFS command lines, Create a file studentM1_1.csv in this directory (having 3 columns firstName, lastName,email, with your data)
Display HDFS command line contents of directory
Display the HDFS command line contents of the file
HIVE:
Create a database DATABASE_M1
With HQL, create a database DATABASE_M2
With HQL, create a hive table ETUDIANT_M1 in the DATABASE_M1 database pointing to the data/common/raw/DATABASE_M1/ETUDIANT_M1 directory
With HQL, Display the contents of the STUDENT_M1 table
With HQL, Create an ETUDIANT_M1_PART table in the DATABASE_M1 database partitioned on the DateRecep field (in year month, day, hour, minute format: YYYYMMDDHHmm) and pointing to the /common/raw/DATABASE_M1/ETUDIANT_M1_PART directory
Create an external table STUDENT_M2 in the DATABASE_M2 database
NIFI :
Expose a NIFI API to receive external file data (use the 2 HandleHttpRequest and HandleHttpResponse)
Send, 10 times, the data filestudent.csv (attached to course) to nifi api.
Convert data received with CSV format to avro format
Drop the data in the directory (use the processesor putHdfs) HDFS /common/raw/DATABASE_M1/ETUDIANT_M1_PART/DateRecep=202210ddHHmm (this value must be generated dynamically by nifi, (use an attribute of the flowfile with a date value in the requested format ex: Variable_DateRecep with value DateRecep=${now():format('yyyyMMddHHmm')}
Do a select on the table, what do you notice?
Run the following sql command Msck repair table DATABASE_M1.ETUDIANT_M1_PART;
Copy the data (via an hql query executed by NIFI) from the ETUDIANT_M1_PART table to the ETUDIANT_M2 table so as to keep only the latest version of the file sent (used the OVERWRITE keyword and in the where clause of the select use the value of the last score.
Work to do:
HDFS:
In HDFS, create in HDFS command lines (hdfs dfs -??????) the following tree structure /data/common/raw/DATABASE_M1/ETUDIANT_M1
In HDFS command lines, Create a file studentM1_1.csv in this directory (having 3 columns firstName, lastName,email, with your data)
Display HDFS command line contents of directory
Display the HDFS command line contents of the file
HIVE:
Create a database DATABASE_M1
With HQL, create a database DATABASE_M2
With HQL, create a hive table ETUDIANT_M1 in the DATABASE_M1 database pointing to the data/common/raw/DATABASE_M1/ETUDIANT_M1 directory
With HQL, Display the contents of the STUDENT_M1 table
With HQL, Create an ETUDIANT_M1_PART table in the DATABASE_M1 database partitioned on the DateRecep field (in year month, day, hour, minute format: YYYYMMDDHHmm) and pointing to the /common/raw/DATABASE_M1/ETUDIANT_M1_PART directory
Create an external table STUDENT_M2 in the DATABASE_M2 database
NIFI :
Expose a NIFI API to receive external file data (use the 2 HandleHttpRequest and HandleHttpResponse)
Send, 10 times, the data filestudent.csv (attached to course) to nifi api.
Convert data received with CSV format to avro format
Drop the data in the directory (use the processesor putHdfs) HDFS /common/raw/DATABASE_M1/ETUDIANT_M1_PART/DateRecep=202210ddHHmm (this value must be generated dynamically by nifi, (use an attribute of the flowfile with a date value in the requested format ex: Variable_DateRecep with value DateRecep=${now():format('yyyyMMddHHmm')}
Do a select on the table, what do you notice?
Run the following sql command Msck repair table DATABASE_M1.ETUDIANT_M1_PART;
Copy the data (via an hql query executed by NIFI) from the ETUDIANT_M1_PART table to the ETUDIANT_M2 table so as to keep only the latest version of the file sent (used the OVERWRITE keyword and in the where clause of the select use the value of the last score.