Sqoop, Hive and Impala for Data Analysts (Formerly CCA 159)

Learn to download and install Oracle VirtualBox on Mac, create Linux virtual machines, mount ISO images, configure storage and memory, and use Vagrant or vendor images to automate multi-VM setups.

Set up the Cloudera QuickStart VM on a high-end laptop with virtualization to practice Hadoop, Spark, Kafka, and Impala APIs for data analytics.

Explore the Cloudera QuickStart VM setup, including HDFS, YARN, Hive, Spark, and Sqoop, with Cloudera Manager and Hue interfaces for hands-on exam preparation.

Learn to verify a running MySQL server on a Cloudera quickstart VM, connect with the MySQL client or Sqoop, list databases and tables, and validate data with queries and metadata.

Set up nyc database in MySQL on the Cloudera Quickstart VM, create nyc_user, clone and unzip new york stock exchange data, and load it into stock_id table for verification.

Provide an overview of HDFS on the Cloudera Quickstart VM, and show how to create /public and copy nyc_all and retail_db into HDFS with Hadoop fs.

Learn Hive basics by launching Hive, creating an external table against data in HDFS, and using Beeline for queries on a Cloudera Quickstart VM.

Explore Sqoop scope commands and validations in Cloudera quickstart vm. Practice import and export between MySQL and Hdfs, list databases and tables, and validate data movement while noting resource limits.

Sign up for the labs to access an 11-node multi-node cluster with Ambari, enabling affordable hands-on big data learning and preparation for Cloudera or Hortonworks certification exams.

Gateway node to access Ambari and run Hadoop, Hive, Sqoop, and Spark from a web console or SSH, using credentials and tools like PuTTY, Cygwin, or WSL.

Access hdfs from the cluster gateway node using hadoop fs commands to explore public datasets. Learn to set up passwordless ssh login and use command line tools for efficient access.

Launch hive and Beeline from the gateway node to access hive databases and tables, create databases and tables, and connect via JDBC to the hive server to run queries.

Understand how a centralized MySQL server in the cluster hosts read-only and write-enabled databases, and how to connect via SSH and the MySQL client for Sqoop and Spark JDBC workflows.

Learn to run sqoop commands in the cluster lab, connect via the gateway, verify access with sqoop help, and list databases and tables before importing data via mysql jdbc url.

Explore how Ambari and other management tools orchestrate open source Hadoop components—from Zookeeper, HDFS, YARN, and MapReduce to Tez and Hive—across on-prem and cloud distributions.

Audit and manage properties and properties files across big data tools, review standard conf locations, and use command lines or Ambari to view, adjust, and restart services safely.

Explore the Hadoop distributed file system (HDFS) with name node, secondary name node, and data nodes, focusing on block storage and replication for fault tolerance.

Explore how hdfs, yarn, and MapReduce two coordinate distributed data processing. Use yarn components—resource manager and node managers—to monitor, manage resources, and access history servers.

Submit a MapReduce job using the YARN framework, inspect its execution through logs, and troubleshoot Hive queries or Sqoop jobs by reviewing the history server logs.

Explore how MapReduce determines map tasks and reducers using HDFS block size and splits, revealing 90 map tasks and one reducer for a wordCount job, with defaults and override options.

Review yarn and MapReduce two property files in Hadoop conf, focusing on resource manager address and memory settings for map and reduce tasks. Use web ui and job history.

Review MapReduce run time properties for running or completed jobs, apply overrides for subsequent runs, and track details via the resource manager UI including maps, reducers, and split size.

Review MapReduce and yarn job logs via the resource manager and job history server UI, using the tracking URL to inspect task attempts and troubleshoot failures with syslog.

Explore how to interpret MapReduce job counters to benchmark performance, examining map tasks, reduce tasks, combiner effects, file system counters, and final logs from the job history.

Explore Hive architecture with Hive Metastore and Hive Server 2, and learn how Hive translates SQL queries into MapReduce or Spark SQL jobs for processing data in HDFS.

Learn how Hive creates databases and tables, loads data, and runs queries against Hive tables using multiple execution engines—MapReduce, Tez, Spark SQL, Impala, Presto, and Drill—and compare their trade-offs.

Learn how to ingest data into Hdfs using Sqoop for batch load and Flume or Kafka for streaming, preparing data for processing with MapReduce or Spark.

Learn to work with HDFS for certification exams by reading, processing, and saving data back to HDFS; preview, validate results, and master essential HDFS commands and permissions.

Develop a developer-focused overview of Hdfs architecture, including name node, secondary name node, and data nodes, and review core-site.xml and hdfs-site.xml properties via Ambari or Cloudera Manager.

Explore the hadoop fs or hdfs dfs CLI to interact with the Hadoop distributed file system from gateway node, using essential commands like ls, mkdir, cp, put, get, and cat.

Learn to list files in hdfs using the hadoop fs ls command, with options -t, -s, -R, -h, -C, and -r to sort, display readable sizes, and recursively traverse directories.

Explore how hdfs uses user spaces under /user, where the owner is the login user, and read, write, and execute permissions apply, including acl considerations.

Create and manage directories in HDFS using Hadoop fs commands like mkdir, rm, and rmdir, including recursive options -p and -R to build or remove directory structures.

Copy files and directories from local file system into HDFS using Hadoop fs put or copy from local, creating retail_db in user training and handling existing targets carefully.

Review file and directory permissions in hdfs, including owner, group, others, and execute requirements on directories, plus acl and admin controls affecting access for certification exams.

Copy files from hdfs to the local file system using get or copy to local, then view, head, or tail, converting avro to json when needed to review data.

Preview text data in HDFS with cat and tail, using Hadoop fs -cat for patterns and full output, and Hadoop fs -tail for the last kilobyte of large files.

Learn how to copy and move files in hdfs using hadoop fs -cp and -mv, including permissions, ownership, and the efficiency of metadata updates.

Learn to determine file-system size and data sizes with df and du, using -h for human-readable output, and account for replication factor when estimating usable storage to allocate resources.

Understand how hdfs divides files into 128 MB blocks and distributes them across worker nodes, with replication factor ensuring reliability (two copies in labs, three by default in production).

Learn to retrieve file and block metadata with hdfs fsck, including files, blocks, and their locations, and understand block IDs, block indices, and replication factors for data analysis workflows.

Review hdfs architecture via Ambari or Cloudera manager and master core hadoop fs commands like mkdir, put, copy, mv, cat, tail, df, du for certification exercises.

Explore the Hive language manual to learn data types, data definition statements, and how to create, partition, or bucket tables; load data with insert or load, and avoid acid operations.

Explore how to launch and use the hive command line interface (CLI) to run hive commands and queries, manage databases and tables, load data, and run scripts.

Explore hive properties, grouped by category, and learn to view and override them with set and hive conf. Use hive -e to run commands and capture output.

Explore hive history and dot hive rc to override session properties, then launch hive and use up arrow or ctrl-r to navigate commands and set hive execution engine to tez.

Execute HDFS commands from Hive CLI using DFS as alias for Hadoop FS to list files with DFS LS, including recursive -R. Piping or grepping do not work in Hive.

Explore how Hive uses the metastore warehouse directory to store databases, tables, and partitions as HDFS directories, typically under /apps/hive/warehouse or /user/hive/warehouse, with dot db subdirectories.

Learn to create Hive databases with create database, create a training_retail database from the default, and view its dot db directory in the Hive metastore warehouse.

this lecture demonstrates creating a simple hive table, orders, in a multi-tenant environment by using training_retail, defining columns, row format, and comma-delimited text in hdfs.

Learn to retrieve complete Hive table metadata using describe formatted, compare with describe extended, and interpret columns, data types, comments, database, owner, location, and storage details.

Learn beeline as a JDBC-based alternative to hive cli, enabling authentication and authorization, connecting to hive server two, and running hive queries with exclamation commands and database selection.

Explore how Hive metastore stores metadata in a MySQL database rather than HDFS, enabling efficient query compilation and config via Ambari or Cloudera Manager.

Connect to a hive database directly with Beeline, pass credentials, and run hive queries. Use Beeline options to execute interactive and scripted queries against hive server two.

Create Hive tables using DDL, choosing managed, external, or temporary types, then define int and string columns and load comma separated data stored as a text file.

Explore Hive data types from primitive numeric and string types to complex array, map, struct, and union types, and understand schema on read versus schema on write for data analysis.

Define comments on Hive table columns and the table using the comment clause, then verify with describe or describe formatted and note that comments are stored in the metastore.

Learn how to load data from the local file system into a Hive table using load data local, with a row format and fields terminated by comma for correct querying.

learn how to load data into a hive table from hdfs using load data inpath, including permissions and the move versus copy behavior, and validate the data.

Demonstrate loading data into Hive tables with append by default, showing how files accumulate, and explain using overwrite to replace existing data for daily snapshots.

Create an external table in hive with the external keyword, define columns and comments, specify comma-delimited text storage, and load data the same way as a managed table.

Learn how to specify a custom location for Hive tables with the location attribute, explore the default Hive warehouse directory under the Hive Metastore, and manage permissions for loading data.

Understand the difference between external and managed tables in hive, including how drop table operations affect data and metadata and how a table location determines whether data remains.

Learn the default field and line delimiters in Hive text file tables, and how to use row format delimited clauses and a staging table to load comma-delimited data.

Learn to use the stored as clause in Hive, with text as default and options like VRC, Parkway, Avro, and Json, including the deprecated RC file.

Compare traditional rdbms and Hive for data warehousing, highlighting schema on write, constraints and data quality, batch loading overhead, and Hive's hdfs-based, MapReduce and Spark processing.

Learn how to truncate and drop tables in Hive, distinguishing managed and external tables, and understand how metadata and data are removed from the metastore and HDFS.

Explore hive resources and exercises, create tables with comments, load data from local and HDFS, compare external and managed tables, and practice with New York Stock Exchange data and delimiters.

Explore partitioning and bucketing in hive to manage large tables and boost performance, using list partitioning, cluster by bucketing, and enabling asset transactions on bucketed tables in VRC formats.

create a hive table using orc format from csv data, define fields like order_item_id, order_item_order_id, product_id, quantity, order_item_subtotal, and order_item_product_price, and learn pre-processing with insert to load data.

Create an order items stage from the CSV data, load it, then insert into the destination table with required transformations. Validate data via counts and selective queries.

Compare load and insert commands in Hive for loading data into orc tables; load copies files quickly when formats match, while insert applies transformations at the cost of speed.

Create partitioned hive tables with partition by, deriving order_month from order_date. Loading uses staging or insert into, not direct load, for partitioned tables.

Add partitions to a hive partition table using alter table add partition, including composite partitioning and if not exists, and optionally specify a location; partitions map to hdfs directories.

Learn how to pre-partition data and load it into Hive partitioned tables by specifying the partition column order_month, then validate with counts and data previews.

Learn to add a Hive partition and insert data into it from another table using insert into table ... partition, validating non partition column alignment.

Learn to insert into partitioned tables using dynamic partitions in Hive, with hive.exec.dynamic.partition set to true and non strict. See how partitions are created on insert and verified by counts.

Explore bucketing in Hive using clustered by a key and optional sorted by clause, with eight buckets, and compare inserting versus loading data into bucketed tables.

Insert data into the bucket table from the stage table after enabling hive bucketing, then verify eight buckets and data distribution with describe formatted and data previews.

Learn how bucketing with sorting distributes data into eight buckets by order_id, demonstrates sorting within each bucket, and uses the sorted by clause when creating and inserting into bucket tables.

Discover how Hive handles acid transactions, where inserts can work but updates and deletes require a bucketed table and acid-compatible file formats like orc, emphasizing batch operations for performance.

Learn to create bucketed Hive tables with acid output formats like ORC, enable transaction manager and concurrency, and perform inserts, updates, and deletes using ACID.

Learn how to insert multiple records into Hive tables with asset transactions enabled, including configuring transactional properties and transaction manager. Understand how compaction merges small files to improve efficiency.

Learn how to update and delete data in Hive bucketed transactional tables, including enabling transaction management and concurrency, soft deletes, and compaction, with emphasis on batch operations for performance.

Explore Hive functions and how to list them with show functions. Categorize into string manipulation, date manipulation, and typecasting to apply in queries.

Validate Hive functions by using show functions and describe function, then practice with select queries like current date and substr, while noting Oracle and MySQL validation styles and dual usage.

Learn string manipulation in Sqoop, Hive and Impala: use lower, upper, init cap, and length to transform and measure textual data in queries.

Learn to extract data from fixed-length and delimited fields using substring and split, then convert arrays to rows with explode, mastering year, month, date, and timestamp extraction.

Learn to clean strings with ltrim, rtrim, and trim, and pad values using lpad and rpad in Hive. Build correctly formatted dates from year, month, and day, and validate results.

Explore reverse and concatenate functions, rebuilding dates from year-month-day using lpad, and see how aliasing and unions support query construction in Hive and RDBMS for performance.

Discover how to get current date and current timestamp in Hive using operators, not functions, with default date-time formatting for filtering in where clauses.

Explore hive date arithmetic with date_add, date_sub, date_diff, and add_months to compute dates. Use current_date, timestamps, and yyyy-mm-dd formatting; understand boundary conditions.

Explore the trunk function for date manipulation, extracting the first date of a month or year from the current or a given date to support month-to-date and year-to-date reports.

Learn how to use the date_format function in Hive to extract year, month, day, and time components from dates, timestamps, or strings, and format outputs for queries.

Learn to extract year, month, day, and week of year from dates or timestamps using date format or high level functions, and understand when to choose each approach.

Learn how to convert Unix epoch timestamps to regular date or time and back, using from_unix_time, to_unix_timestamp, and unix_timestamp with flexible format patterns.

Explore Hive numeric functions, from abs and round to aggregate tools like avg, max, min, and variance, including power, sqrt, ceil, floor, rand, and cumulative distribution function.

Explore data type conversion in Hive using the cast function to perform explicit and implicit conversions between strings, integers, and floats, with examples on dates and decimals.

Use the nvl function to replace null values with a default, ensuring accurate sums and averages when aggregating data such as a commission field.

Apply split, explode, and count to derive word frequencies from sentences, using a nested query and group by to produce the word count in a MapReduce context.

Master the basics of SQL in Hive, from selecting and joining data to grouping, filtering, and sorting. Explore nested subqueries, set operations, and windowing functions.

Explore the Hive query execution lifecycle, from syntax and semantics checks to MapReduce job creation, illustrated by computing revenue by order id using subtotals.

Learn how to troubleshoot Hive queries by examining Hive logs and tracking URLs for MapReduce jobs, understanding the Hive dot log location under /tmp and the input directories.

Learn to write basic Hive queries with select and from clauses, project specific fields and derived expressions, and use limit for preview while understanding how group by affects projections.

Learn to use case and when in the select clause. Map complete or closed to completed, four pending statuses to pending, and all other statuses to other.

Learn how to project distinct values with select distinct across dates, statuses, and order items, and assess MapReduce overhead and when to apply distinct.

Learn how to filter data in Hive with the where clause, using equal, not equal, greater than, and numeric versus string comparisons on orders and order items.

Explore boolean operations for filtering in SQL, using or and and across orders data to combine status and date conditions.

Master when to use the in operator versus boolean or for filtering multiple values on one column, including not in, and apply equal comparisons in sql queries.

Explore the like operator in the where clause for partial date matching on orders, filtering by 2014% and the 07 pattern to target July, with group by for counts.

Learn to write basic aggregations in sql, using count, distinct, sum, min, max, and average with aliases, and apply group by to compute revenue per order id.

Master group by to perform aggregations such as sum, min, max, and count, using multiple keys and distinct values while avoiding non-grouped field errors.

Filter aggregated results after a group by using the having clause, applying conditions on aggregates such as sum of order item subtotal to refine data.

Explore global sorting in Hive using order by, and compare it with sort by for per-key sorting; learn composite sorting by order customer ID and date, with limits.

Sort data locally within each key by using distribute by and sort by instead of global order by, improving performance with multiple reducers.

Cluster by replaces distribute by and sort by when you want to group data by a key and sort within a group, yielding ascending order and avoiding global sorting.