Mastering Hive: From Basics to Advanced Big Data Analysis

Explore hive as a sql-like tool over HDFS that translates queries to MapReduce for batch processing. Store metadata, not data in Hive, and practice creating databases with if not exists.

Learn to move data between hive tables with the insert command, including overwrite and where conditions, and use multi insert to route data to multiple tables by criteria.

Mastering Hive teaches conditional statements like if and case, and functions such as isnull, coalesce, and nvl, with examples and string tools like split, substring, and instring.

Master Hive explode and lateral view to transform array elements into rows, launch a MapReduce job, join with other columns, and extract map keys and values.

Explore hive sorting techniques using order by, sort by, distribute by, and cluster by, showing how reducers, MapReduce jobs, and limit clauses affect output and data distribution.

Explore how Hive joins combine tables using equality conditions, covering left, right, and full outer joins, with two- and three-table joins and memory management via streaming versus buffering.

Use map join to load small tables into memory for a mapper-only join, avoiding mapreduce. Configure bucket map join with the same bucketed column and same number of buckets.

Partitioning divides a table into subdirectories by partition column values, speeding queries by scanning only relevant data; it covers static and dynamic partitioning and loading into hardcoded partitions.

Explore dynamic partitioning in Hive: enable dynamic partitions, run non-strict mode, load data into partitioned tables, view partitions, and compare static versus dynamic strategies and their limits.

Explore Hive alter commands to modify table schemas and structures. Rename tables, change column names and types, add or replace columns, and set properties.

Explore how to create external tables, implement dynamic and static partitions, and load data into hdfs, then manage partitions with show partitions, add partition, drop partition, and msck repair table.

explore bucketing in hive to decompose large datasets into manageable buckets using a hashing function. pair bucketing with partitioning for best performance and use map-side joins.

Master table sampling, a bucketing feature that draws samples from across partitions, unlike limit, using bucket one out of four or percent, memory, or rows parameters.

Hive archiving transfers older data to less frequently used storage to reduce NameNode load in HDFS, with archive and unarchive commands and notes that space is not salvaged.

Explore the rank function, dense rank function, and row number function and how they rank data within partitions, handle ties, and produce top-n results using partition by and order by.

Explore how hive indexes speed up queries by referencing records, compare partitioning and indexing, and learn compact and bitmap index types with deferred rebuild and alter commands.

Learn how to create, alter, and drop compact and bitmap indexes on a table, compare performance, and determine when indexing helps or hinders queries in large datasets.

Learn how hive views function as tables from SQL results, selecting columns and filtering rows. See how changes to base data don't reflect in views, and how to create them.

Master Hive shell scripting to run Hive commands from Bash, fire single or multiple queries, and execute Hive scripts using the f option; learn variable substitution and server connections.

Use hive conf and hive variables within a session by setting values with set, then reference them in queries with dollar; scope is limited to that session.

Explore hive variables in bash shell using hiveconf with E and F options, declaring and passing values to run queries and scripts.

Configure and use Hive variables in queries with Hive where and Hive conf, run scripts via source, and execute Unix and HDFS commands from the Hive shell.

Enable hive.variable.substitute to true to allow a new variable to inherit its value from a previous variable. Verify by using hive conf and the new_table value equal to table nine.

Load a comma-separated file into a one-column Hive table, split and explode words, then group by word to count occurrences, demonstrating MapReduce execution.

Understand Hive architecture by detailing five components—UI, driver, compiler, metastore, and execution engine—and their roles, from query submission to a dag of execution stages via Thrift, JDBC/ODBC, or web GUI.

Explore how Hive parallelism enables executing independent stages in parallel to speed up joins of partial data from two tables, noting the built-in parallel property hive.exec.parallel and potential deadlocks.

Master Hive table properties to control data loading, distinguishing active from passive. Use skip header line count and skip footer line count to omit header and footer lines during load.

Learn to use Hive's null format property to treat empty fields as null values by setting serialization.null.format during table creation and validating with queries.

Demonstrate setting table properties for RC and ORC formats, including compression codecs (zlib, snappy, bzip2, gzip) and options like stripe size, row index stride, and bloom filters.

Explore Hive purge commands: differentiate drop vs truncate, distinguish internal vs external tables, and understand purge behavior that permanently deletes data (no trash) since version 0.14.

Explore how to capture data changes in Hive using slowly changing dimensions, detailing type zero, type one, and type two approaches for preserving history.

Implement slow changing dimension type 1 by overwriting the base table with updates and new records from table three using a full outer join and a case statement.

Demonstrate a Hive-based slow changing dimension (SCD) example using a full outer join of two tables and CDC codes to label new, update, or no change records.

Learn to load semi-structured XML into Hive using a ready-made SerDe jar, mapping tags like title and author to columns while omitting tags.

Create a hive table book_details with title, author, country, company, price, and year using an xml row format and xpath mappings. Load local books.xml and verify two rows via select.

Learn to prevent dropping and offline querying in Hive by enabling no drop and offline protections on tables and partitions, and then re-enabling them with disable commands.

Learn to create an immutable hive table by setting immutable = true, perform first load with insert into, and see that later inserts fail; insert overwrite still works.

Learn how to configure hive with the hive rc file, manage session versus global settings, and enable headers and map join, then explore cartesian products or cross joins.

Discover how Hive links metadata to a single data file to support multiple tables, including cases with fewer, equal, or more columns than the file, where extra columns become null.

Master hive file merging to reduce small files by configuring hive.merge settings, and learn the are like function for substring and regex matching.

Explore Hive and Hadoop configuration settings, including block size, parquet block size, default file formats, and session scope; optimize with mapper mode, order by position alias, and reducer options.

Explore Hive configurations: speculative execution reduces slow tasks on multiple nodes, with map and reducer options; enable bucketing and Hive auto convert join for map joins on small tables.

Learn how Hive compression reduces storage and speeds data transfer in a Hadoop cluster by compressing input and map output, with codecs such as gzip, bzip2, lzma2, and snappy.

Explore the three Hive modes: embedded, local, and remote, and learn their use cases. Understand how Metastore, Derby database, and JDBC shape performance and parallel sessions.

Learn to enable compression in Hive to reduce storage and network traffic across input, map output, and reducer output, using gzip, bzip2, lzo, or snappy.

Explore hive's embedded, local, and remote modes, detailing metastore and Derby database setups, how to switch modes, and when to use each for small-scale to production environments.

Compare internal and external tables in hive, showing that internal tables are controlled by hive and lose data and metadata when dropped, while external tables retain data in hdfs.

Learn how to create Hive tables, including managed and external types, with a comma-delimited schema and load data from a local file into the employee table.

Explore Hive managed tables by creating a sample database and employee table, loading comma-delimited data, and querying with select star from employee in a safe mode Hive environment.

Learn to create external tables in Hive, including external table with location, using dollar as a delimiter, load data from a local path, and compare manage vs external tables.

Explore creating and managing external tables in Hive. Load data into employee details, view with show tables and show create table, and understand drop table behavior on the warehouse entry.

Learn how to create an external Hive table with location, point it to an HDFS path, load data from a local input file into HDFS, and verify with a select.

Explains how Hive partitions optimize queries by data segregation and reducing full-table scans, highlighting static and dynamic partitions and their use cases.

Learn dynamic partitions in Hive to avoid static partitions and prevent table scans. Implement by creating a temp table, loading data, setting hive.execute.dynamic.partition to non-strict, then inserting into partitioned table.

Master dynamic partitions in hive by creating a temp table, loading data, and building a partitioned table by department number, then enable non-stick partition mode and observe increase in performance.

Learn to add and delete partitions in a hive partitioned table. Load data into specific partitions and manage them with alter table drop partition commands, validating with show partitions.

Explore common Hive file formats, including text, RC, and ORC, and learn practical steps to create tables, load csv data, and work with multiple formats.

Discover how bucketing in Hive optimizes table sampling and joins by enabling and loading a bucketing table (three buckets by department number) and comparing with partitions.

Explore how Hive joins combine common data from the employee and department tables using inner join, left outer join, right outer join, and full outer join on the department number.

Explore Hive joins from inner join to full outer join, and learn to create tables, load data, and run join queries to analyze employee and department data.

Explore creating a joins query in Hive by examining employee and department tables, describing schemas, previewing data, and building a join_out table with an inner join on department_number.

Learn to perform inner, left outer, right outer, and full outer joins in Hive, create and query join tables, and understand how join semantics affect results.

Mastering Hive shows how to create normal and complex views to read only selected columns from existing tables, avoiding storage waste and improving performance.

Explore how Hive uses compact indexes to speed queries. Learn to create an index on employee number and load data.

Create and load a Hive table, then build an index on employee number to demonstrate index creation and data accessibility in Hive.

Explore Hive's data types, comparing primitive and complex types. Learn how arrays, maps, and structs store data, with a practical example of loading an array table from a csv.

Explore Hive complex data types, focusing on maps as key-value structures, and learn to create a map table, load map.csv, and query results.

Explore Hive data types with practical map datatype examples, including creating a map-based table for pay details, loading csv data, and querying by map keys like salary.

Explore Hive's struct data type and load CSV data into a table with a nested address field, then query address.street and other struct elements.

Learn to create and run Hive scripts by writing a sample ql, creating databases and tables, loading data, and executing scripts with Hive -f and parameterized configs.

Explore Hive user defined functions (UDF), their advantages over predefined functions, and the steps to create, package into a jar, and link a UDF to apply to a table.

Create a hive user defined function in java to compute bonuses, package as bonus.jar, and register it as a temporary function to add a bonus column to the employee table.

Practice practical implementation of user defined functions in Hive by configuring Hadoop services, creating and loading an employee table, and linking a Java-based UDF to Hive.

Implement a hive udf by extending the udf class and importing jars. Convert salary strings to doubles and apply 20% or 40% bonus, then query the employee table.

Learn the two types of tables in hive—external and managed—and how dropping or converting between them works, and how to create a hive tutorial database with a managed employee table.

Load data into Hive tables, convert between managed and external tables, and describe table formats. Explore why external tables protect data and remain accessible after deletions, with performance benefits.

Explore creating tables in Hive and HBase, including Hive table creation with a database and data loading, and HBase table creation with column families, plus put, describe, and scan.

Explore how hive and hbase handle updates, noting that hive cannot update values while hbase supports updates; learn table creation, column families, data loading, and basic queries.

Create an hbase employee table with cf1 for personal data and cf2 for working info. Insert rows with name, gender, age, job, and salary, then scan table using can command.

Demonstrates creating an HBase table with two column families, inserting data with put, and querying via scan, count, and get, while noting performance drops beyond three column families.

Learn to create a hash-based managed hive table and use HBase to manage Hive tables, addressing Hive and HBase disadvantages like no updates, null value space, and inserts not possible.

Explore updating a base table in Hive with changes reflected in HDFS, and configure jars to enable base manager Hive table creation and column qualifiers linking.

Map a hive external table to a base manager hive table by aligning student id, name, score, and region with the base primary key, with inserts and updates propagating automatically.

Explore Hive as a data warehouse on Hadoop, enabling OLAP-style data summarization and analysis through HiveQL, with a metastore, various data formats, and MapReduce execution on Hadoop.

Explore Hive data types from primitive boolean, int, bigint, float, double, and string to complex struct, map, and array, with implicit conversions. Understand partitions and buckets for faster queries.

Explore how partitions and buckets enhance Hive query performance by narrowing data searches to specific partitions and buckets, speeding up queries compared with scanning the whole table.

Explore hive database and table commands, including create, show, describe, alter, and drop databases; manage tables with create, show, describe, and cascade drops, including managed and external tables.

Master hive table creation and database modification by adding properties, and define columns such as name (string), salary (float), subordinates (array<string>), deductions (map type), and address (struct).

Discover how to manage tables in Hive by showing tables, describing and describe extended, and altering tables, including rename, add or drop columns, drop tables, with managed and external tables.

Learn the difference between managed and external tables in Hive. Dropping a managed table deletes data and metadata, while dropping an external table preserves the data and removes only metadata.

Explore partitioning and bucketing in Hive to optimize queries. Learn to create and manage partitions across internal, managed, and external tables with partition commands in a telecom data case study.

Create an external table log_messages with server process ID and message, partitioned by year, month, and day, then add partitions via alter table and verify with show partitions.

Learn how to create and manage partitions with alter table, rename and drop partitions, and apply bucketing using hashing and clustered by id into 50 buckets for telecom data analysis.

Demonstrates constructing Hive tables for telecom data, adding comments, and partitioning by plan and service codes, with practical examples for directory number and contract tables, plus bucketing by status.

Construct and modify hive tables with partitioning, defining columns like unique customer id, sequence number, status, activation and deactivation dates, and manage table lifecycle by dropping and recreating with partitions.

Create Hive tables for telecom data, focusing on the control services table with call_id, cs_sequence_number, change_of_service, status, and bill_date, using tab-delimited text storage.

Create the contract all and customer all tables with if not exists, define bigint, string, float, and int columns. Partition by ko user last mod and tm code.

Explore customer complaint analysis using mapreduce by counting complaints per product. Implement mapper, reducer, and driver in a Java project with text input.

Create and configure a Hadoop driver class with a main method, set the job, mapper, reducer, map output, and input/output paths, then build and run the jar.

Run a Hadoop MapReduce job, monitor map and reduce progress, and verify the output under hadoop fs, observe part-r output and sample product complaint counts.

Learn to compute location-wise counts of complaints and compare them with product-level totals using Hive, and prepare a grouped list of complaints by location.

Filter counts by user-specified location in Hive, taking a third-argument input, comparing with locations like Delhi or Mumbai, and writing location-specific results to the output folder.

Group complaints by location using MapReduce, count delayed responses, and flag not on time versus on time to produce a location-based delay report, and explore future Hive and Pig implementations.

Analyze bookmarking website data in the social media industry by moving data from rdbms to hdfs with sqoop. Process xml with mapreduce, pig, and hive to analyze reviews and location.

Process bookmarking site data from RDBMS to HDFS using Sqoop, convert flat files to XML, then analyze with MapReduce, Pig, and Hive, including custom data types and aggregation.

Compare Pig, MapReduce, and Hive for data processing and analytics; explain scope as a bridge between RDBMS and HDFS, and outline converting XML to flat files for HDFS processing.

Show how to move data from RDBMS to HDFS with Sqoop, converting flat files to XML via an ETL tool, using a MySQL DB demonstration for MapReduce, Pig, and Hive.

Learn to import data from an RDBMS to HDFS using Sqoop. Specify a target directory, verify the schema, and run a MapReduce job on the resulting flat file.

Export the MapReduce jar, deploy to the Hadoop cluster, and run a mapper-driven xml processing job with the driver, noting there is no reducer, to generate a flat output file.

Transform the xml into a flat, star-delimited file and use a mapper to tally positive, average, and negative reviews for each book based on comma-separated feedback.

Analyze book reviews with a Hadoop MapReduce job that counts positive, negative, and average comments by converting input to lowercase and matching keywords such as awesome and bad.

Analyze book by location demonstrates counting users per country with mapreduce, emitting each location as a key and aggregating counts in the reducer, handling multi-location fields split by comma.

Demonstrates a MapReduce reduce workflow to count bookmarks by location, aggregating values per key and producing a location-keyed output.

Analyze book data by author with a MapReduce workflow, counting how many books each author has written, from an XML to flat-file input and using author as the key.

Learn how to process XML data in Pig by loading semi-structured XML with the XML loader and piggy bank, transforming via relations, and analyzing on Hadoop.

Analyze book performance by loading an xml file with piggy bank's xml loader, extracting book name, author, review, and location, and applying a udf for positive, negative, and average reviews.

Analyze book reviews in xml with Pig by parsing data and counting positive, negative, and average reviews; register and call a jar function, then store results in HDFS as xml.

Register and use a piggy bank jar to process xml with pig and the xml loader. Employ tokenize and flatten to derive book id, category, and locations stored in SDFs.

Learn to convert an XML file into separate rows by location using tokenize and flatten in Pig. Then group by location and compute counts to summarize records per country.

Learn to process pig xml outputs by tokenizing and flattening data, grouping by review, counting book IDs, and storing results to hdfs while handling case sensitivity.

Explore how to analyze a Pig output in Hive to handle location data, using Hive's complex data types like arrays, maps, and structs.

Explore when to use MapReduce, Pig, and Hive for big data analysis; learn Hive's SQL-like approach, array data types, and loading complex data from HDFS for ad hoc analysis.

Learn to work with array, map, and struct in Hive by creating tables, loading data, and accessing elements by index or key.

Explore processing pig output into hive by converting a pipe-delimited location array and loading it into a table in the book analysis database, with collection items terminated by pipe.

Load Hive data with append or overwrite, explode array-typed locations into separate rows, and count by location for simple analysis. Convert complex types to simple types for Hadoop analytics.

Analyze sensor data from the Philippines to generate actionable information for the country's growth, using JSON inputs downloaded via Java utilities and processed in HDFS with MapReduce, Pig, and Hive.

Explore the production workflow of big data processing with dfs, hdfs, mapreduce, and hive, showing how json data moves from local to hdfs, mapreduce to hive, and reports.

Explore how to transform demographic data into meaningful insights using Hadoop, data science, and data analytics. The session demonstrates use cases for policy decisions, tax analysis, and population trends.

Explore big data basics with IBM’s four v framework: volume, velocity, variety. Understand data growth toward 40 zettabytes and MapReduce's role in distributed, fault-tolerant processing of XML and JSON.

Explore big data processing of sensor data, addressing data veracity and uncertainty, with MapReduce architecture, Hadoop HDFS and YARN, and the mapper, reducer, and driver workflow for scalable analytics.

Convert a json input file with mapreduce in hadoop to a simple comma-delimited flat file, using a json library or a custom parser.

Mastering Hive: from basics to big data analysis explores converting json to a simple text format using a map-only Hadoop job that extracts values, ignores keys, and outputs comma-delimited text.

Run a Hadoop MapReduce job using the sensor1.jar with the specified input and output paths, observe the job id, and verify the produced comma-separated output file in out demo2.

Convert json to plain text, then use mapreduce to count males and females in the Philippines by aggregating the gender field from a comma-delimited input.

implement a mapreduce workflow to compute the ratio of male and female by grouping on gender, counting each group, and exporting a driver jar for Hadoop execution.

Generate the count of females over 45 who are widowed or divorced using a MapReduce workflow, filtering by age, education, and marital status, with a mapper and reducer.

Learn to apply MapReduce to calculate total income and compare expected versus actual income tax, including string trimming, type conversion, and aggregating income in HDFS.

Examine a MapReduce workflow to compute total income and tax data, fix data types for decimals, and generate government-ready tax filer insights.

Run a MapReduce job to count people aged 18 and above with non-zero income, using a mapper and reducer to project next year’s income tax from HDFS data.

Use mapreduce to count educated versus non educated groups by education category, mapping education values to counts, then reducing to produce per-category totals for country demographics.

Explain how to count people by country of birth in a MapReduce job, mapping country of birth to one and reducing to totals, with sample outputs by country.

Develop a MapReduce workflow to count records where age is under 18 and income > 0, enabling government reporting on child labor using age and income data.

Compare MapReduce, Pig, and Hive to reveal abstraction differences: MapReduce is low-level and code-heavy, Pig uses Pig Latin for data flow, while Hive offers SQL-like declarative queries.

Compare Pig, MapReduce, and Hive for data processing and analysis, focusing on execution, performance, joins, and how each supports structured vs unstructured data.

Learn how to process sensor data in json format on hdfs with pig instead of mapreduce, using pig latin to analyze data and store results in hive for reporting.

Explore pig functions by writing user defined functions to process a JSON file, using filter and eval functions, passing the whole row and a key, and storing results in HDFS.

Develop and use custom functions in Pig by parsing key-value pairs with for-each loops, removing braces, and handling escape characters, then apply filter or eval functions on tuples.

Create and export a pig function jar, register it in the pig grunt shell, and apply a generic function to analyze gender, age, and marital status on HDFS.

Learn how Pig enables data flow processing for big data use cases, loading JSON data into HDFS with Pig storage and running grunt to compare Pig, MapReduce, and Hive.

Explore data flow with Pig by loading data, registering a jar with a user defined function, and extracting values for a given key using for each and generate.

Learn a Pig Latin use case to select females over 45 who are divorced or widowed, using data cleaning with trim, UDFs, and filters before grouping.

Advance Pig use cases by filtering S3 data with trim and equals to for female, divorced, or widow, then dump or store results to hdfs and prepare for udf integration.

Learn to compute actual vs. expected income and education ratios, and count groups, using mapreduce-inspired steps in pig and hive with sum, group by, trim, and filter on json data.

Explore Hive as a data analytics tool built on Hadoop, using Hive query language similar to SQL to load data into HDFS, create tables, and generate reports through MapReduce-backed queries.

Explore hive features, hive metastore, and hiveql for analytics on hdfs; learn internal and external tables and how json data is processed with built-in functions.

Explore json use cases in hive by creating a demo database, a tbl_json table with a single json_data string column, and loading input text data with a star delimiter.

Explore loading JSON data into Hive tables, troubleshoot missing files, and use Unix and Hadoop commands to manage JSON data in a Hive workflow.

Use Hive's get_json_object to extract gender, age, and marital status from JSON data and count by gender. Filter females over 45 who are divorced or widowed using JSON extraction.

Learn how to analyze JSON data in Hive using get_json_object, filtering by age and status, summing income, and grouping by age, education, and country of birth.

Explore end-to-end big data processing of JSON data, comparing MapReduce, Pig, and Hive, and deriving meaningful insights for government decision making from raw data.