Hive

• Bucketing decomposes data sets into more manageable parts • Users can specify the number of buckets for their data set • Specifying bucketing does not guarantee that table is properly populated • The number of bucket does not vary with data • Bucketing is best suited for sampling • Map-side joins can be done well with bucketing In the below sample code , a hash function will be done on the  ‘emplid’ and similar ids will be placed in the same bucket SET hive.enforce.bucketing = true; or Set mapred.reduce.tasks = <<number of buckets>> CREATE TABLE empdata(emplid INT, fname STRING, lname STRING) PARTITIONED BY (join_dt STRING) CLUSTERED BY (emplid) INTO 64 BUCKETS;

Partition improves query performance The way Hive structures data storage changes with Partitioning Partitions are stored as sub-directories in the table directory Over Partitioning to be avoided – Each partition creates an HDFS directory with many files in it – It increases large number of small sized files in HDFS – It eventually consume the capacity of namenode as the metadata is kept in main memory by Hadoop Use a partition scheme that creates partitions with size in multiples of the HDFS block size Hive supports dynamic partitions also where partitions are created from query parameters Static partitions are created by the ‘PARTITIONED BY’ clause Dynamic partitions are not enabled by default and if enabled , it works in ‘strict’ mode. The maximum number of dynamic partitions are limite...

Hive supports 2 types of tables : 1. Managed / Internal tables 2. External Tables Managed Tables – Life cycle of data in the table is controlled by Hive – data is stored under the sub directory defined by ‘ hive.metastore.warehouse.dir ‘ – When table is dropped , data & metadata is deleted – Not a good choice for sharing data with other tools External Tables – Use the keyword EXTERNAL with CREATE TABLE – Life cycle of data in the table is NOT controlled by Hive – data is stored under the directory defined by LOCATION clause in the CREATE TABLE command – When table is dropped , data is not deleted but metadata is deleted – Better choice for sharing data with other tools – Few HiveQL constructs are not allowed for external tables DESCRIBE EXTENDED <<table name>> could be used...

Hive Databases are like namespaces/catalogs If no database name is specified, ‘default’ database is used We can also use the keyword SCHEMA instead of DATABASE in all the database related commands below. Hive creates a directory for each of the databases it creates The default directory created for the database under a top-level directory specified by the property hive.metastore.warehouse.dir You can specify a different directory using the LOCATION option in the CREATE command Creating Database CREATE DATABASE IF NOT EXISTS Mydb1 WITH DBPROPERTIES ( ‘prop1’=‘value1’; Listing all databases ( regular expressions also allowed in listing dbs) SHOW DATABASES; SHOW DATABASES LIKE ‘M*’; Describing Database DESCRIBE DATABASE Mydb1; // shows DB name, comment & DB directory DESCRIBE DATABASE EXT...

File Format specifies how records are encoded in files Record Format implies how a stream of bytes for a given record are encoded The default file format is TEXTFILE – each record is a line in the file Hive uses different control characters as delimeters in textfiles ᶺA ( octal 001) , ᶺB(octal 002), ᶺC(octal 003), \n The term field is used when overriding the default delimiter FIELDS TERMINATED BY ‘\001’ Supports text files – csv, tsv TextFile can contain JSON or XML documents. Commonly used File Formats – TextFile format Suitable for sharing data with other tools Can be viewed/edited manually SequenceFile Flat files that stores binary key ,value pair SequenceFile offers a Reader ,Writer, and Sorter classes for reading ,writing, and sorting respectively Supports – Uncompr...

Data  in Hive is organised into – Databases –  Namespace to separate table and other data Tables – Homogeneous collection of data having same schema Partitions – Divisions in table data based on key value Buckets – Divisions in partitions based on hash value of a particular column Hive Data Types: Hive supports primitive data types and three collection types. Primitive type – tinyint,   smallint,  int, bigint,   boolean,   string, timestamp, float,   double ,  binary Collection Types – 1. Struct address struct <city:STRING; state:STRING> – Eg: struct (‘Bengaluru’, ‘Karnataka’) and address.city = ‘Bengaluru’ 2. Array names array(‘Hari’, ’Sai’) – Eg: name[1] = Sai 3. Maps name map(‘first’, ‘Mahendra’, ‘last&#...