{"id":4037,"date":"2017-09-09T12:18:02","date_gmt":"2017-09-09T12:18:02","guid":{"rendered":"http:\/\/data-flair.training\/blogs\/?p=4037"},"modified":"2017-09-09T12:18:02","modified_gmt":"2017-09-09T12:18:02","slug":"apache-hive-operators","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/apache-hive-operators\/","title":{"rendered":"Hive Operators – A Complete Tutorial for Hive Built-in Operators"},"content":{"rendered":"

In our previous blog, we have discussed the introduction to Apache Hive<\/strong> and Hive Data types<\/strong> in detail. In this Hive tutorial, we are going to cover the Hive Operators – Relational Operators, Arithmetic Operators, Logical Operators, String Operators, Operators on Complex Types etc in detail.<\/p>\n

What is Hive Operators?<\/h2>\n

Apache Hive<\/strong> provides various Built-in operators for data operations to be implemented on the tables present inside Apache Hive warehouse.<\/p>\n

Hive operators<\/strong> are used for mathematical operations on operands. It returns specific value as per the logic applied.<\/p>\n

Types of Hive Built-in Operators<\/h2>\n
    \n
  • Relational Operators<\/li>\n
  • Arithmetic Operators<\/li>\n
  • Logical Operators<\/li>\n
  • String Operators<\/li>\n
  • Operators on Complex Types<\/li>\n<\/ul>\n

    3.1. Hive Relational Operators<\/h3>\n

    These operators compare two operands and generate a TRUE<\/strong> or FALSE<\/strong> value. The below table describes the relational operators available in Hive:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    Operator<\/strong><\/td>\nOperand Types<\/strong><\/td>\n\n

    Description<\/strong><\/p>\n<\/td>\n<\/tr>\n

    \n

    A=B<\/p>\n<\/td>\n

    		All primitive types<\/td>\n\n

    TRUE if expression A is equal to expression B. Otherwise FALSE.<\/p>\n<\/td>\n<\/tr>\n

    \n

    A!=B<\/p>\n<\/td>\n

    		All primitive types<\/td>\nTRUE if expression A is not equal to expression B. Otherwise FALSE.<\/td>\n<\/tr>\n
    A<B<\/td>\nAll primitive types<\/td>\n\n

    TRUE if expression A is less than expression B. Otherwise FALSE.<\/p>\n<\/td>\n<\/tr>\n

    \n

    A <= B<\/p>\n<\/td>\n

    		All primitive types<\/td>\nTRUE if expression A is less than or equal to expression B. Otherwise FALSE.<\/td>\n<\/tr>\n
    \n

    A > B<\/p>\n<\/td>\n

    		All primitive types<\/td>\n\n

    TRUE if expression A is greater than expression B. Otherwise FALSE.<\/p>\n<\/td>\n<\/tr>\n

    A >= B<\/td>\nAll primitive types<\/td>\n\n

    TRUE if expression A is greater than or equal to expression B. Otherwise FALSE.<\/p>\n<\/td>\n<\/tr>\n

    \n

    A IS NULL<\/p>\n<\/td>\n

    		All types<\/td>\nTRUE if expression A evaluates to NULL. Otherwise FALSE.<\/td>\n<\/tr>\n
    A IS NOT NULL<\/td>\nAll types<\/td>\n\n

    FALSE if expression A evaluates to NULL. Otherwise FALSE.<\/p>\n<\/td>\n<\/tr>\n

    \n

    A LIKE B<\/p>\n<\/td>\n

    		String<\/td>\nTRUE if string pattern A matches to B. Otherwise FALSE.<\/td>\n<\/tr>\n
    \n\n\n\n
    \u00a0 \u00a0 \u00a0 A REGEXP B<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/td>\nString<\/td>\n\n

    Same as RLIKE.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    3.2. Hive Arithmetic Operators<\/h3>\n

    Arithmetic Operators in Hive supports various arithmetic operations on the operands. All return number types. If any of the operands are NULL<\/strong>, then the result is also NULL.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n

    Operator<\/strong><\/p>\n<\/td>\n

    		\n

    \u00a0 \u00a0Operand types<\/strong><\/p>\n<\/td>\n

    		\n

    Description<\/strong><\/p>\n<\/td>\n<\/tr>\n

    \n

    A+B<\/p>\n<\/td>\n

    		\u00a0 All number types<\/td>\n\u00a0 Gives the result of adding A and B<\/td>\n<\/tr>\n
    \n

    A-B<\/p>\n<\/td>\n

    		\u00a0 All number types<\/td>\n\u00a0 Gives the result of subtracting B from A<\/td>\n<\/tr>\n
    \n

    A*B<\/p>\n<\/td>\n

    		All number types<\/td>\n\n

    Gives the result of multiplying A and<\/p>\n<\/td>\n<\/tr>\n

    A\/B<\/td>\nAll number types<\/td>\n\n

    Gives the result of dividing A by B<\/p>\n<\/td>\n<\/tr>\n

    \n

    A % B<\/p>\n<\/td>\n

    		All number types<\/td>\nGives the remainder resulting from dividing A by B<\/td>\n<\/tr>\n
    A & B<\/td>\nAll number types<\/td>\n\n

    Gives the result of bitwise AND of A and B<\/p>\n<\/td>\n<\/tr>\n

    \n

    A | B<\/p>\n<\/td>\n

    		All number types<\/td>\n\n

    Gives the result of bitwise OR of A and B<\/p>\n<\/td>\n<\/tr>\n

    \n

    A ^ B<\/p>\n<\/td>\n

    		All number types<\/td>\n\n

    Gives the result of bitwise XOR of A and B<\/p>\n<\/td>\n<\/tr>\n

    ~A<\/td>\nAll number types<\/td>\n\n

    Gives the result of bitwise NOT of A.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    3.3. Hive Logical Operators<\/h3>\n

    Logical operators in Hive provide support for creating logical expressions. All return boolean TRUE, FALSE, or NULL depending upon the boolean values of the operands. IN this NULL behaves as an “unknown” flag, so if the result depends on the state of an unknown, the result itself is unknown.<\/p>\n\n\n\n\n\n\n\n
    \n

    Operator<\/strong><\/p>\n<\/td>\n

    		Operand types<\/strong><\/td>\n\n

    Description<\/strong><\/p>\n<\/td>\n<\/tr>\n

    \n

    A AND B<\/p>\n<\/td>\n

    		Boolean<\/td>\n\n

    TRUE if both A and B are TRUE. Otherwise FALSE. NULL if A or B is NULL.<\/p>\n<\/td>\n<\/tr>\n

    \n

    A OR B<\/p>\n<\/td>\n

    		Boolean<\/td>\nRUE if either A or B or both are TRUE, FALSE OR NULL is NULL. Otherwise FALSE.<\/td>\n<\/tr>\n
    \n

    NOT A<\/p>\n<\/td>\n

    		Boolean<\/td>\n\n

    TRUE if A is FALSE or NULL if A is NULL. Otherwise FALSE.<\/p>\n<\/td>\n<\/tr>\n

    ! A<\/td>\nBoolean<\/td>\n\n

    Same as NOT A.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    3.4. Hive String Operators<\/h3>\n\n\n\n\n
    \n

    Operator<\/strong><\/p>\n<\/td>\n

    		Operand types<\/strong><\/td>\n\n

    Description<\/strong><\/p>\n<\/td>\n<\/tr>\n

    A || B<\/td>\nstrings<\/td>\n\n

    Concatenates the operands – shorthand for\u00a0concat(A,B)<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    3.5. Operators on Complex Types<\/h3>\n

    These operators provide the mechanism to access elements in Complex Types.<\/p>\n\n\n\n\n\n\n
    \n

    Operator<\/strong><\/p>\n<\/td>\n

    		Operand types<\/strong><\/td>\nDescription<\/strong><\/td>\n<\/tr>\n
    \n

    A[n]<\/p>\n<\/td>\n

    		A is an Array and n is an int<\/td>\nReturns the nth element in the array A. The first element has index 0<\/td>\n<\/tr>\n
    M[key]<\/td>\nM is a Map<K, V> and key has type K<\/td>\n\n

    Returns the value corresponding to the key in the map<\/p>\n<\/td>\n<\/tr>\n

    S.x<\/td>\nS is a struct<\/td>\n\n

    Returns the x field of S.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    So, this was all in Hive Operators. Hope you like our explanation.<\/p>\n

    Conclusion<\/h2>\n

    In conclusion, Hive provides different types of Build-in operators which are used to perform mathematical operations on operands. Hope this blog will help you to understand the Hive essentials.<\/p>\n

    In the next section, we will discuss the Hive functions in detail, where we will learn to use these Hive Operators.<\/p>\n","protected":false},"excerpt":{"rendered":"

    In our previous blog, we have discussed the introduction to Apache Hive and Hive Data types in detail. In this Hive tutorial, we are going to cover the Hive Operators – Relational Operators, Arithmetic...<\/p>\n","protected":false},"author":7,"featured_media":42617,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[26],"tags":[191,811,5675,5688,5758,5795,16659],"class_list":["post-4037","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-hive","tag-aapche-hive","tag-apache-hive-operators","tag-hive","tag-hive-built-in-operators","tag-hive-operators","tag-hive-tutorial","tag-operators-in-hive"],"yoast_head":"\nHive Operators - A Complete Tutorial for Hive Built-in Operators - DataFlair<\/title>\n<meta name=\"description\" content=\"Introduction to Apache Hive operators include Hive Relational Operator,Arithmetic Operator, Logical Operator,String Operators & many Hive Built-in Operators\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/data-flair.training\/blogs\/apache-hive-operators\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Hive Operators - A Complete Tutorial for Hive Built-in Operators - DataFlair\" \/>\n<meta property=\"og:description\" content=\"Introduction to Apache Hive operators include Hive Relational Operator,Arithmetic Operator, Logical Operator,String Operators & many Hive Built-in Operators\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/apache-hive-operators\/\" \/>\n<meta property=\"og:site_name\" content=\"DataFlair\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DataFlairWS\/\" \/>\n<meta property=\"article:published_time\" content=\"2017-09-09T12:18:02+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2017\/09\/apache-hive-operators-2.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"628\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"DataFlair Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:site\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"DataFlair Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Hive Operators - A Complete Tutorial for Hive Built-in Operators - DataFlair","description":"Introduction to Apache Hive operators include Hive Relational Operator,Arithmetic Operator, Logical Operator,String Operators & many Hive Built-in Operators","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/data-flair.training\/blogs\/apache-hive-operators\/","og_locale":"en_US","og_type":"article","og_title":"Hive Operators - A Complete Tutorial for Hive Built-in Operators - DataFlair","og_description":"Introduction to Apache Hive operators include Hive Relational Operator,Arithmetic Operator, Logical Operator,String Operators & many Hive Built-in Operators","og_url":"https:\/\/data-flair.training\/blogs\/apache-hive-operators\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2017-09-09T12:18:02+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2017\/09\/apache-hive-operators-2.jpg","type":"image\/jpeg"}],"author":"DataFlair Team","twitter_card":"summary_large_image","twitter_creator":"@DataFlairWS","twitter_site":"@DataFlairWS","twitter_misc":{"Written by":"DataFlair Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/data-flair.training\/blogs\/apache-hive-operators\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/apache-hive-operators\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/beb0cab24b7aa54423a3b50e669a9dcd"},"headline":"Hive Operators – A Complete Tutorial for Hive Built-in Operators","datePublished":"2017-09-09T12:18:02+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/apache-hive-operators\/"},"wordCount":740,"commentCount":3,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/apache-hive-operators\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2017\/09\/apache-hive-operators-2.jpg","keywords":["Aapche Hive","Apache Hive operators","hive","Hive built-in Operators","Hive Operators","hive tutorial","Operators in Hive"],"articleSection":["Hive Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/apache-hive-operators\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/apache-hive-operators\/","url":"https:\/\/data-flair.training\/blogs\/apache-hive-operators\/","name":"Hive Operators - A Complete Tutorial for Hive Built-in Operators - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/apache-hive-operators\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/apache-hive-operators\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2017\/09\/apache-hive-operators-2.jpg","datePublished":"2017-09-09T12:18:02+00:00","description":"Introduction to Apache Hive operators include Hive Relational Operator,Arithmetic Operator, Logical Operator,String Operators & many Hive Built-in Operators","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/apache-hive-operators\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/apache-hive-operators\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/apache-hive-operators\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2017\/09\/apache-hive-operators-2.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2017\/09\/apache-hive-operators-2.jpg","width":1200,"height":628,"caption":"Hive Operators - A Complete Tutorial for Hive Built-in Operators"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/apache-hive-operators\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Home","item":"https:\/\/data-flair.training\/blogs\/"},{"@type":"ListItem","position":2,"name":"Hive Tutorials","item":"https:\/\/data-flair.training\/blogs\/category\/hive\/"},{"@type":"ListItem","position":3,"name":"Hive Operators – A Complete Tutorial for Hive Built-in Operators"}]},{"@type":"WebSite","@id":"https:\/\/data-flair.training\/blogs\/#website","url":"https:\/\/data-flair.training\/blogs\/","name":"DataFlair","description":"Learn Today. Lead Tomorrow.","publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/data-flair.training\/blogs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/data-flair.training\/blogs\/#organization","name":"DataFlair","url":"https:\/\/data-flair.training\/blogs\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","width":106,"height":48,"caption":"DataFlair"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/DataFlairWS\/","https:\/\/x.com\/DataFlairWS","https:\/\/www.linkedin.com\/company\/dataflair-web-services-pvt-ltd\/","https:\/\/www.youtube.com\/user\/DataFlairWS"]},{"@type":"Person","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/beb0cab24b7aa54423a3b50e669a9dcd","name":"DataFlair Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/c322416204232f4dd97ef3901b0a499a5d34d7ba7fe333f4bfe53a907873d293?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/c322416204232f4dd97ef3901b0a499a5d34d7ba7fe333f4bfe53a907873d293?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c322416204232f4dd97ef3901b0a499a5d34d7ba7fe333f4bfe53a907873d293?s=96&d=mm&r=g","caption":"DataFlair Team"},"description":"DataFlair Team specializes in creating clear, actionable content on programming, Java, Python, C++, DSA, AI, ML, data Science, Android, Flutter, MERN, Web Development, and technology. Backed by industry expertise, we make learning easy and career-oriented for beginners and pros alike.","url":"https:\/\/data-flair.training\/blogs\/author\/dfteam3\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/4037","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/comments?post=4037"}],"version-history":[{"count":0,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/4037\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/42617"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=4037"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=4037"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=4037"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}