{"id":10373,"date":"2018-03-09T08:31:54","date_gmt":"2018-03-09T08:31:54","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=10373"},"modified":"2018-03-09T08:31:54","modified_gmt":"2018-03-09T08:31:54","slug":"hiveql-group-by-query","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/hiveql-group-by-query\/","title":{"rendered":"HiveQL Select – Group By Query | Group By Clause"},"content":{"rendered":"

In Apache Hive Tutorial<\/strong>,\u00a0for grouping particular column values mentioned with the group by Query. Basically, we use Hive Group by Query with Multiple columns on Hive tables<\/strong>. <\/span><\/p>\n

However, we need to know the syntax of HiveQL group by query to implement it. So, in this article, we will learn what is Hive Query – Group by Query, syntax, and an example of HiveQL Select Group By Clause to understand with JDBC Program.<\/span><\/p>\n

What is Hive Query?<\/span><\/h2>\n

However, for the ETL purpose on top of Hadoop<\/strong> file system Hive offers SQL type querying language.\u00a0<\/span>Also, to work with tables, databases queries Hive Query language (HiveQL) offers SQL type environment in Hive.<\/span><\/p>\n

In addition, to perform different type data manipulations and querying it is possible to have a different type of clauses associated with Hive. Especially, for better connectivity with different nodes outside the environment. Also, HIVE offers JDBC connectivity.<\/span><\/p>\n

Also, there are several features of Hive<\/strong> queries offers. Such as:<\/span><\/p>\n

    \n
  • For example data modeling. Basically, for the creation of databases, tables, etc.<\/span><\/li>\n
  • Moreover, ETL functionalities. For example, Extraction, Transformation, and Loading data into tables.<\/span><\/li>\n
  • Also, it offers joins to merge different data tables.<\/span><\/li>\n
  • While it comes to ease of code, it offers user-specific custom scripts.<\/span><\/li>\n
  • Also, it offers a faster-querying tool on top of Hadoop.<\/span><\/li>\n<\/ul>\n

    HiveQL Select<\/span><\/h2>\n

    Basically, for grouping particular column values mentioned with the group by query, Group by clause use columns on Hive tables<\/strong>. However, column name does not matter, since for whatever the name we are defining a Group By query will selects and display results by grouping the particular column values.<\/span><\/p>\n

    i. \u00a0Group by Query Syntax<\/span><\/h3>\n

    However, \u00a0see below the syntax of GROUP BY Clause:<\/span>
    \n<\/span>SELECT [ALL | DISTINCT] select_expr, select_expr, … <\/span>
    \n<\/span>FROM table_reference <\/span>
    \n<\/span>[WHERE where_condition] <\/span>
    \n<\/span>[GROUP BY col_list] <\/span>
    \n<\/span>[HAVING having_condition] <\/span>
    \n<\/span>[ORDER BY col_list]] <\/span>
    \n<\/span>[LIMIT number];<\/span><\/p>\n

    ii.\u00a0Group by Query Example<\/span><\/h3>\n

    Also, to understand well, see an example below.<\/span> Although, let\u2019s suppose an employee table. Basically, it includes Id, Name, Salary, Designation, and Dept fields. However, to retrieve the number of employees in each department Generate a query.
    \nTable 1- Group By Clause Example<\/strong><\/p>\n\n\n\n\n\n\n\n\n
    ID<\/strong><\/td>\nName<\/strong><\/td>\nSalary<\/strong><\/td>\nDesignation<\/strong><\/td>\nDept<\/strong><\/td>\n<\/tr>\n
    1201<\/span><\/td>\nRoss<\/span><\/td>\n45000<\/span><\/td>\nTech manager<\/span><\/td>\nTP<\/span><\/td>\n<\/tr>\n
    1202<\/span><\/td>\nRachel<\/span><\/td>\n45000<\/span><\/td>\nProofreader<\/span><\/td>\nPR<\/span><\/td>\n<\/tr>\n
    1203<\/span><\/td>\nMonika<\/span><\/td>\n40000<\/span><\/td>\nTechnical writer<\/span><\/td>\nTP<\/span><\/td>\n<\/tr>\n
    1204<\/span><\/td>\nMike<\/span><\/td>\n45000<\/span><\/td>\nProofreader<\/span><\/td>\nPR<\/span><\/td>\n<\/tr>\n
    1205<\/span><\/td>\nJoey<\/span><\/td>\n30000<\/span><\/td>\nOP Admin<\/span><\/td>\nAdmin<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    Moreover, BY using the above scenario, the following query retrieves the employee details.<\/span>
    \n<\/span>hive> SELECT Dept,count(*) FROM employee GROUP BY DEPT;<\/span><\/p>\n

    Also, we get to see the following response to the successful execution of the query:<\/span><\/p>\n

    Table 2 –\u00a0Group By Query\u00a0<\/strong><\/p>\n\n\n\n\n\n\n
    Dept <\/strong><\/td>\n Count(*)<\/strong><\/td>\n<\/tr>\n
    Admin<\/span><\/td>\n1<\/span><\/td>\n<\/tr>\n
    PR<\/span><\/td>\n2<\/span><\/td>\n<\/tr>\n
    TP<\/span><\/td>\n3<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    iii. JDBC Program<\/span><\/h3>\n

    Moreover, to apply the Group By clause for the given example, here is the JDBC program is given below.<\/span>
    \nFor example,<\/strong><\/span>
    \n<\/span><\/p>\n

    import java.sql.SQLException;\nimport java.sql.Connection;\nimport java.sql.ResultSet;\nimport java.sql.Statement;\nimport java.sql.DriverManager;\npublic class HiveQLGroupBy\n      {\n\u00a0\u00a0       private static String driverName = \"org.apache.hadoop.hive.jdbc.HiveDriver\";\u00a0\u00a0\n\u00a0\u00a0       public static void main(String[] args) throws SQLException\n              {\n\/\/ Register driver and create driver instance\n\u00a0\u00a0\u00a0\u00a0\u00a0              Class.forName(driverName);\u00a0\u00a0\u00a0\u00a0\u00a0\n\/\/ get connection\n\u00a0\u00a0\u00a0              \u00a0\u00a0Connection con = DriverManager.\n\u00a0\u00a0              \u00a0\u00a0\u00a0getConnection(\"jdbc:hive:\/\/localhost:10000\/userdb\", \"\", \"\");\u00a0\u00a0\u00a0\u00a0\u00a0\n\/\/ create statement\n\u00a0\u00a0              \u00a0\u00a0\u00a0Statement stmt = con.createStatement();\u00a0\u00a0\u00a0\u00a0\u00a0\n\/\/ execute statement\n\u00a0\u00a0\u00a0              \u00a0\u00a0Resultset res = stmt.executeQuery(\u201cSELECT Dept,count(*) \u201d + \u201cFROM employee GROUP BY DEPT; \u201d);\n\u00a0\u00a0\u00a0\u00a0              \u00a0System.out.println(\" Dept \\t count(*)\");\u00a0\u00a0\u00a0\u00a0\u00a0\n\u00a0\u00a0\u00a0\u00a0              \u00a0while (res.next())\n                     {\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0                     System.out.println(res.getString(1) + \" \" + res.getInt(2));\n\u00a0\u00a0\u00a0\u00a0\u00a0                }\n\u00a0\u00a0\u00a0\u00a0             \u00a0con.close();\n\u00a0\u00a0          }\n     }<\/pre>\n
    Also, use the following commands to compile and execute this program.<\/span> Moreover, save the program in a file named HiveQLGroupBy.java.
    \n<\/span>
    \n<\/span>$ javac HiveQLGroupBy.java<\/span>
    \n<\/span>$ java HiveQLGroupBy<\/span><\/p>\n
    iv. Group By Clause –\u00a0Output<\/span><\/h3>\n
    However, here is the possible output of Group By Query.
    \nTable 3 – Group By Query\u00a0<\/strong><\/p>\n\n\n\n\n\n\n
     Dept <\/strong><\/td>\n Count(*)<\/strong><\/td>\n<\/tr>\n
    Admin<\/span><\/td>\n1<\/span><\/td>\n<\/tr>\n
    PR<\/span><\/td>\n2<\/span><\/td>\n<\/tr>\n
    TP<\/span><\/td>\n3<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    So, this was all\u00a0about Apache HiveQL Select – Group By Query Tutorial. Hope you like our explanation of Hive Group by Clause.<\/p>\n
    Conclusion<\/span><\/h2>\n
    As a result, we have seen the whole concept of HiveQL Select -Group By query in Apache Hive<\/strong>, with a group by query example & syntax, we also discuss JDBC program with its output to understand HiveQL – Group By clause well. <\/span><\/p>\n
    In our next tutorial, we will study hive Oder By Query in detail. Still, if you have any query, feel free to ask in the comment section.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"
    In Apache Hive Tutorial,\u00a0for grouping particular column values mentioned with the group by Query. Basically, we use Hive Group by Query with Multiple columns on Hive tables. However, we need to know the syntax...<\/p>\n","protected":false},"author":7,"featured_media":10587,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[26],"tags":[5164,5168,5714,5716,5717,5812,5813,5815,7773,15727],"class_list":["post-10373","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-hive","tag-group-by-query","tag-group-by-query-syntax","tag-hive-group-by-alias","tag-hive-group-by-count","tag-hive-group-by-having","tag-hiveql-group-by-clause","tag-hiveql-group-by-query","tag-hiveql-select-groupby","tag-jdbc-program","tag-what-is-group-by-query"],"yoast_head":"\nHiveQL Select - Group By Query | Group By Clause - DataFlair<\/title>\n<meta name=\"description\" content=\"HiveQL Select statement-Group By Query, Group By Clause syntax, Group by Clause example, JDBC Program, Group By Query output, Hive group by count,hive query\" \/>\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\/hiveql-group-by-query\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"HiveQL Select - Group By Query | Group By Clause - DataFlair\" \/>\n<meta property=\"og:description\" content=\"HiveQL Select statement-Group By Query, Group By Clause syntax, Group by Clause example, JDBC Program, Group By Query output, Hive group by count,hive query\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/hiveql-group-by-query\/\" \/>\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=\"2018-03-09T08:31:54+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/HiveQL-Select-Group-By-Query-01.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=\"3 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"HiveQL Select - Group By Query | Group By Clause - DataFlair","description":"HiveQL Select statement-Group By Query, Group By Clause syntax, Group by Clause example, JDBC Program, Group By Query output, Hive group by count,hive query","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\/hiveql-group-by-query\/","og_locale":"en_US","og_type":"article","og_title":"HiveQL Select - Group By Query | Group By Clause - DataFlair","og_description":"HiveQL Select statement-Group By Query, Group By Clause syntax, Group by Clause example, JDBC Program, Group By Query output, Hive group by count,hive query","og_url":"https:\/\/data-flair.training\/blogs\/hiveql-group-by-query\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2018-03-09T08:31:54+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/HiveQL-Select-Group-By-Query-01.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":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/data-flair.training\/blogs\/hiveql-group-by-query\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/hiveql-group-by-query\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/beb0cab24b7aa54423a3b50e669a9dcd"},"headline":"HiveQL Select – Group By Query | Group By Clause","datePublished":"2018-03-09T08:31:54+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/hiveql-group-by-query\/"},"wordCount":604,"commentCount":0,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/hiveql-group-by-query\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/HiveQL-Select-Group-By-Query-01.jpg","keywords":["GROUP BY Query","Group By Query syntax","Hive group by alias","Hive group by count","Hive group by having","HiveQL Group by clause","HIVEQL Group By Query","HiveQL Select GroupBy","JDBC Program","what is Group By Query"],"articleSection":["Hive Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/hiveql-group-by-query\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/hiveql-group-by-query\/","url":"https:\/\/data-flair.training\/blogs\/hiveql-group-by-query\/","name":"HiveQL Select - Group By Query | Group By Clause - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/hiveql-group-by-query\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/hiveql-group-by-query\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/HiveQL-Select-Group-By-Query-01.jpg","datePublished":"2018-03-09T08:31:54+00:00","description":"HiveQL Select statement-Group By Query, Group By Clause syntax, Group by Clause example, JDBC Program, Group By Query output, Hive group by count,hive query","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/hiveql-group-by-query\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/hiveql-group-by-query\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/hiveql-group-by-query\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/HiveQL-Select-Group-By-Query-01.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/HiveQL-Select-Group-By-Query-01.jpg","width":1200,"height":628,"caption":"HiveQL Group By Query | Group By Clause"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/hiveql-group-by-query\/#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":"HiveQL Select – Group By Query | Group By Clause"}]},{"@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\/10373","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=10373"}],"version-history":[{"count":0,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/10373\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/10587"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=10373"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=10373"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=10373"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}