Big Data Analyst -using Sqoop and Advance Hive (CCA159)

Learn how hive, a sql-like framework on top of hdfs, enables analytics by converting sql-like queries to mapreduce jobs, with schema on read and data in hdfs.

Explore hive databases by creating and describing databases, setting location and properties, and using cascade drops to manage tables within the hive warehouse.

Understand the difference between managed and external hive tables, including that dropping a managed table deletes schema and data, while external tables keep data on hdfs and load data.

Create and use Hive external tables by defining an explicit data location, ensuring data persists after drop, and compare external versus managed tables for safe data handling.

Learn how to load data into the subject table from local files or hdfs, use row format delimited with a comma, and perform inserts that trigger MapReduce jobs.

Demonstrate Hive multi-insert by loading data from customer_new into two tables, Los Vegas and Brownsville, filtering by city, and illustrate append versus override.

Explore how to alter Hive tables with the alter command: add columns, rename columns and tables, set properties, and change file formats and table locations.

Explore Hive data types, from tinyint to bigint, float, double, and decimal, plus timestamp and date, and master complex types including arrays, maps, and structs through practical examples.

Define a Hive external table with an array data type for customer emails and age, using comma separated fields and colon delimited collection items, then load and verify data.

Explore map data types in Hive by creating a customer_map with name, a map<string,string> relation, and city, loading data locally, and querying map keys such as wife of John.

Learn to use the struct data type to model nested fields in a Hive table, mapping an address struct with city, country, and zip code, and query address dot city.

Explore Hive collection functions for complex data types, including size for arrays and maps, and map_keys and map_values, with practical examples on customer_email and customer_map tables.

Explore conditional functions in Hive, including the if clause, is null, is not null, and case statements, with practical city data examples.

Master hive string functions for data formatting with concat and concat underscore. Apply length, lower, upper, reverse, padding, split, substring, and regex replace on first name, last name, city.

Explore hive joins by comparing inner and outer joins on the movie and movie underscore rating tables, using on clauses with movie_id and learning left, right, and full outer joins.

Master multi joins in Hive by linking movies, movie_rating, and user tables to output user name, movie name, and rating; learn two stage execution and star plus streaming hints.

Examine map side joins in Hive, where small tables under 25 megabytes are joined in the mapper using buffered data, avoiding the reduce phase.

Create a Hive table stored as parquet, load data with create table as select from customers, and explore parquet formats, input/output formats, and table properties.

Enable parquet compression in hive by setting the compression output to true, selecting gzip or snappy, creating a parquet table, and verifying with parquet tools and describe formatted.

Read a fixed-format file using a regular expression in Hive, create an external table employee_fixed, map four-digit customer_id, ten-character name, and two-digit age via input_regex, and query first five records.

Learn how Hive partitioning optimizes queries by distributing data into status-based partitions, enabling dynamic partitioning, and efficient data layout with external tables and show partitions.

Learn how Hive bucketing partitions data by a column using a hash function to create multiple buckets. Enable bucketing and populate with insert overwrite to produce bucket files.

Create views in hive to save query results without touching hdfs, using create view with a where clause, a virtual view that runs the base query on demand.

Explore how to use lateral view and explode to turn array-type columns into multiple rows, illustrated with an actors table and the movies they played.

Explore Hive analytics with group by and order by, apply aggregates such as max, min, sum, and use having and case logic to analyze city customer counts and MapReduce execution.

Explore rank and dense rank window functions in Hive, partitioned by category and ordered by price descending, with practical examples showing ties, ranking, and differences from row number.

Learn lead and lag window functions in Hive by partitioning by role and ordering by salary descending to derive next and previous salaries, with offset options.

Learn to use count, min, max, and sum windowing functions over partition by category in Hive to compute per-category price statistics.

Explore windowing specifications in Hive sql, using partition by role and window frames such as rows between current row and unbounded following to compute minimum and maximum salaries.

Explore how Apache Sqoop migrates data between relational databases and the Hadoop ecosystem, using MapReduce and YARN for parallel import and export, with MySQL as a demonstration.

Learn to set a warehouse dir and a target dir for sqoop imports, configure HDFS paths, and understand how MapReduce jobs run on the YARN cluster to produce part files.

Learn to import data as parquet with Sqoop, a columnar, compressed format that ties schema to data, enabling efficient columnar queries, and inspect with parquet tools.

Explore the Avro file format, a schema-based, language-neutral, row-based data serialization used with Sqoop to import and process data in HDFS and Hive.

Explore working with gzip, snappy, bzip2, and lz4 compression codecs in Sqoop ingestion to HDFS, considering splittable formats, speed, and storage.

Learn how to perform conditional imports in Sqoop, using where clauses to filter customers by name, import selective columns, and run free-form queries with a split by column.

Discover how split by and boundary queries divide data among mappers in sqoop imports, defaulting to the primary key, with product ID boundaries and min and max calculations.

Implement field delimiters for imports, switching from comma to a pipe delimiter, and handle nulls by representing string and non-string nulls as triple x and triple y.

Learn incremental import with Sqoop to sync MySQL data with HDFS by fetching the delta. Use incremental append with boundary queries and last value tracking; automate with scoop jobs.

Import data from MySQL to Hive using Sqoop with Hive import, creating tables in the default database like customer_mysql and customer_mysql_new, then verify with show tables and describe formatted.

Connect to a MySQL database with Sqoop, import complete orders from the orders table into parquet files with tab delimiter, snappy compression, and null representations -1 and n.

Import four customer columns from a MySQL database on localhost to HDFS using Sqoop, filtering by customer_state, as an Avro data file with Snappy compression, and verify with Avro tools.

Use Sqoop to import customers from MySQL database where street contains plaza, output id, first name, last name, street to text with fields separated by star and lines by pipe.

Explore Apache airflow, an orchestration engine to schedule and monitor data pipelines with Python, DAGs, and operators, enabling scoop jobs via cron expressions.

Export data from HDFS to MySQL with Sqoop export, configuring star-delimited input, handling null values, and using three mappers to populate the customer_exported table.

Demonstrate how Sqoop export automatically handles Avro, Parquet, and compressed files when exporting from HDFS to MySQL, with no special options required.

Export data from Hive to MySQL using Sqoop export, moving product_hive into retail_db.product_exported. Validate the transfer by querying the exported table counts to confirm success.