{"id":10260,"date":"2018-03-08T08:45:08","date_gmt":"2018-03-08T08:45:08","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=10260"},"modified":"2018-03-08T08:45:08","modified_gmt":"2018-03-08T08:45:08","slug":"hiveql-select-statement","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/hiveql-select-statement\/","title":{"rendered":"HiveQL SELECT Statement – JDBC Program With Syntax and Example"},"content":{"rendered":"

In Apache Hive<\/strong>, to process and analyze structured data in a Metastore<\/strong>, we have Hive Query Language (HiveQL) as a query language. Moreover, to retrieve the data from a table we use HiveQL SELECT statement. <\/span><\/p>\n

However, there are many more insights we can learn about the HiveQL SELECT statement. So, in this blog, we will learn how to use the HiveQL SELECT statement with WHERE clause. Also, we will see \u00a0JDBC Program, syntax as well as an example of\u00a0HiveQL SELECT statement to understand it well. <\/span><\/p>\n

HiveQL (Hive Query Language)<\/span><\/h2>\n

Generally, to write Hive queries,\u00a0Hive<\/strong> offers a command line interface (CLI). It is possible by using Hive Query Language (HiveQL). Moreover, we can say HQL syntax is similar to the SQL syntax that most data analysts are familiar with.<\/span><\/p>\n

In addition, from the complexity of MapReduce programming basically, Hive’s SQL-inspired language separates the user. Also, it reuses familiar concepts from the relational database world. Like tables, rows, columns, and schema, to ease learning.<\/span><\/p>\n

Moreover, here most of the interactions tend to take place over a command line interface (CLI).<\/span>
\nHowever, there are four file formats which Hive supports. Such as TEXTFILE, SEQUENCEFILE, ORC and RCFILE (Record Columnar File).<\/span><\/p>\n

    \n
  • Basically, for single user metadata storage Hive uses derby database. <\/span><\/li>\n
  • Whereas Hive uses MYSQL for multiple user Metadata or shared Metadata case.<\/span><\/li>\n<\/ul>\n

    HiveQL SELECT Statement<\/span><\/h2>\n

    Basically, to retrieve the data from a table we use HiveQL SELECT statement. Moreover, WHERE clause works similar to a condition. <\/span><\/p>\n

    Further, using the condition it generally filters the data and gives you a finite result. In addition, all the built-in functions and operators generate an expression, that fulfills the condition.<\/span><\/p>\n

    i. Syntax<\/span><\/h3>\n

    Here is the syntax of\u00a0<\/span>HiveQL SELECT Statement<\/span>:<\/span><\/p>\n

    SELECT [ALL | DISTINCT] select_expr, select_expr, ...\nFROM table_reference\n[WHERE where_condition]\n[GROUP BY col_list]\n[HAVING having_condition]\n[CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list]]\n[LIMIT number];<\/pre>\n
    ii. Example<\/span><\/h3>\n
    So, let\u2019s suppose as given below we have the employee table. Basically, it includes fields named Id, Name, Salary, Designation, and Dept. Further, to retrieve the employee details who earn a salary of more than Rs 30000 \u00a0generate a query.<\/span><\/p>\n\n\n\n\n\n\n\n\n
    ID \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/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>\nTechnical 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>\nChandler<\/span><\/td>\n40000<\/span><\/td>\nTechnical writer<\/span><\/td>\nTP<\/span><\/td>\n<\/tr>\n
    1204<\/span><\/td>\nMonika<\/span><\/td>\n40000<\/span><\/td>\nHr Admin<\/span><\/td>\nHR<\/span><\/td>\n<\/tr>\n
    1205<\/span><\/td>\nMike<\/span><\/td>\n 30000 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span><\/td>\n Op Admin <\/span><\/td>\nAdmin<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    However, using the above scenario the following query retrieves the employee details are:
    \nhive> SELECT * FROM employee WHERE salary>30000;<\/strong><\/p>\n
    Now, we get to see the following response to the successful execution of the query:<\/span><\/p>\n\n\n\n\n\n\n\n
    ID \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/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>\nTechnical 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>\nChandler<\/span><\/td>\n40000<\/span><\/td>\nTechnical writer<\/span><\/td>\nTP<\/span><\/td>\n<\/tr>\n
    1204<\/span><\/td>\nMonika<\/span><\/td>\n40000<\/span><\/td>\nHr Admin<\/span><\/td>\nHR<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    iii. JDBC Program<\/h3>\n
    However, now see for the given example the JDBC program to apply where clause is as follows.<\/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 HiveQLWhere\n    {\n\u00a0\u00a0       private static String driverName = \"org.apache.hadoop.hive.jdbc.HiveDriver\"; \u00a0\u00a0\n\u00a0       \u00a0public static void main(String[] args) throws SQLException\n\u00a0         { \u00a0\u00a0\n\/\/ Register driver and create driver instance\n\u00a0\u00a0\u00a0         \u00a0\u00a0Class.forName(driverName); \u00a0\u00a0\u00a0\u00a0\u00a0\n\/\/ get connection\n\u00a0\u00a0\u00a0         \u00a0\u00a0Connection con = DriverManager.getConnection(\"jdbc:hive:\/\/localhost:10000\/userdb\", \"\", \"\"); \u00a0\u00a0\n\/\/ create statement\n\u00a0\u00a0\u00a0\u00a0\u00a0         Statement stmt = con.createStatement(); \u00a0\u00a0\u00a0\u00a0\n\/\/ execute statement\n\u00a0\u00a0\u00a0\u00a0\u00a0         Resultset res = stmt.executeQuery(\"SELECT * FROM employee WHERE salary>30000;\"); \u00a0\n\u00a0\u00a0\u00a0\u00a0         \u00a0System.out.println(\"Result:\");\n\u00a0\u00a0\u00a0\u00a0\u00a0         System.out.println(\" ID \\t Name \\t Salary \\t Designation \\t Dept \"); \u00a0\u00a0\u00a0\u00a0\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0         while (res.next())\n                  {\nSystem.out.println(res.getInt(1) + \" \" + res.getString(2) + \" \" + res.getDouble(3) + \" \" + res.getString(4) + \" \" + res.getString(5));\n             \u00a0\u00a0\u00a0\u00a0\u00a0}\ncon.close();\n\u00a0\u00a0}\n}<\/pre>\n
    However, save the program in a file named HiveQLWhere.java. Moreover, to compile and execute this program, we can use the following commands.<\/span>
    \n$ javac HiveQLWhere.java<\/strong>
    \n$ java HiveQLWhere<\/strong><\/p>\n
    iv. Output<\/span><\/h3>\n\n\n\n\n\n\n\n
    ID \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/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>\nTechnical 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>\nChandler<\/span><\/td>\n40000<\/span><\/td>\nTechnical writer<\/span><\/td>\nTP<\/span><\/td>\n<\/tr>\n
    1204<\/span><\/td>\nMonika<\/span><\/td>\n40000<\/span><\/td>\nHr Admin<\/span><\/td>\nHR<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    So, this was all in HiveQL Select Statement Tutorial. Hope you like our explanation.<\/p>\n
    Conclusion<\/h2>\n
    As a result, we have seen the whole concept of \u00a0HiveQL SELECT statement with WHERE clause. Also, we have seen\u00a0<\/span>syntax as well as HiveQL SELECT Statement example to understand well with JDBC Program. However, if you have any query, feel free to ask in the comment section.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"
    In Apache Hive, to process and analyze structured data in a Metastore, we have Hive Query Language (HiveQL) as a query language. Moreover, to retrieve the data from a table we use HiveQL SELECT...<\/p>\n","protected":false},"author":7,"featured_media":10431,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[26],"tags":[5818,5819,5821,15753,15948],"class_list":["post-10260","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-hive","tag-hiveql-select-statement","tag-hiveql-select-statement-example","tag-hiveql-select-statement-syntax","tag-what-is-hiveql","tag-what-is-select-statement-in-hive"],"yoast_head":"\nHiveQL SELECT Statement - JDBC Program With Syntax and Example - DataFlair<\/title>\n<meta name=\"description\" content=\"HiveQL SELECT statement-what is HiveQL,What is Select statement in HiveQL, syntax & Example of HiveQL SELECT statement,JDBC Program, SELECT statement Output\" \/>\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-select-statement\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"HiveQL SELECT Statement - JDBC Program With Syntax and Example - DataFlair\" \/>\n<meta property=\"og:description\" content=\"HiveQL SELECT statement-what is HiveQL,What is Select statement in HiveQL, syntax & Example of HiveQL SELECT statement,JDBC Program, SELECT statement Output\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/hiveql-select-statement\/\" \/>\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-08T08:45:08+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/Hive-Query-Language-SELECT-statement-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 Statement - JDBC Program With Syntax and Example - DataFlair","description":"HiveQL SELECT statement-what is HiveQL,What is Select statement in HiveQL, syntax & Example of HiveQL SELECT statement,JDBC Program, SELECT statement Output","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-select-statement\/","og_locale":"en_US","og_type":"article","og_title":"HiveQL SELECT Statement - JDBC Program With Syntax and Example - DataFlair","og_description":"HiveQL SELECT statement-what is HiveQL,What is Select statement in HiveQL, syntax & Example of HiveQL SELECT statement,JDBC Program, SELECT statement Output","og_url":"https:\/\/data-flair.training\/blogs\/hiveql-select-statement\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2018-03-08T08:45:08+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/Hive-Query-Language-SELECT-statement-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-select-statement\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/hiveql-select-statement\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/beb0cab24b7aa54423a3b50e669a9dcd"},"headline":"HiveQL SELECT Statement – JDBC Program With Syntax and Example","datePublished":"2018-03-08T08:45:08+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/hiveql-select-statement\/"},"wordCount":551,"commentCount":0,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/hiveql-select-statement\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/Hive-Query-Language-SELECT-statement-01.jpg","keywords":["HiveQL SELECT statement","HiveQL SELECT Statement example","HiveQL SELECT statement Syntax","What is HiveQL","What is SELECT Statement in hive"],"articleSection":["Hive Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/hiveql-select-statement\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/hiveql-select-statement\/","url":"https:\/\/data-flair.training\/blogs\/hiveql-select-statement\/","name":"HiveQL SELECT Statement - JDBC Program With Syntax and Example - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/hiveql-select-statement\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/hiveql-select-statement\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/Hive-Query-Language-SELECT-statement-01.jpg","datePublished":"2018-03-08T08:45:08+00:00","description":"HiveQL SELECT statement-what is HiveQL,What is Select statement in HiveQL, syntax & Example of HiveQL SELECT statement,JDBC Program, SELECT statement Output","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/hiveql-select-statement\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/hiveql-select-statement\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/hiveql-select-statement\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/Hive-Query-Language-SELECT-statement-01.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/Hive-Query-Language-SELECT-statement-01.jpg","width":1200,"height":628,"caption":"What is HiveQL SELECT Statement"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/hiveql-select-statement\/#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 Statement – JDBC Program With Syntax and Example"}]},{"@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\/10260","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=10260"}],"version-history":[{"count":0,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/10260\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/10431"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=10260"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=10260"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=10260"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}