apache hive - hive sql - hive commands - hive tutorial - hadoop hive - hadoop hive - hiveql


What is hive sql - hive commands ?

  • Commands are instructions, coded into SQL statements, which are used to communicate with the database to perform specific tasks, work, functions and queries with data. ...
  • These Data Manipulation Language commands are: SELECT, INSERT, UPDATE, and DELETE.
  • Start a Hive CLI(Command Line Interface) terminal and run
    --------$ hive
  • Should see a prompt like:
    --------hive>
  • Set a Hive or Hadoop conf prop:
    --------hive> set propkey=value;
  • List all properties and values:
    --------hive> set –v;
  • Add a resource to the DCache:
    --------hive> add [ARCHIVE|FILE|JAR] filename;
  • List tables:
    -------- hive> show tables;
  • Describe a table:
    -------- hive> describe <tablename>;
  • More information:
    -------- hive> describe extended <tablename>;
  • List Functions:
    -------- hive> show functions;
  • More information:
    -------- hive> describe function <functionname>;
  • Selecting data - Limiting top 10 data to display

    -------- hive> SELECT * FROM <tablename> LIMIT 10;
  • Selecting data - Top 10 data satisfying the where condition and sort by column
    -------- hive> SELECT * FROM <tablename> WHERE freq > 100 SORT BY freq ASC LIMIT 10;
  • Creating Tables in Hive
    -------- CREATE TABLE foo(id INT, msg STRING);
  • Changing Row Format
    -------- CREATE TABLE foo(id INT, msg STRING) DELIMITED FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’;
  • learn hive - hive tutorial - apache hive - Apache Hive database connectivity - hive examples

    learn hive - hive tutorial - apache hive - Apache Hive database connectivity - hive examples

    Partitioning Data


    -------- CREATE TABLE foo (id INT, msg STRING)PARTITIONED BY (dt STRING);
  • Creates a subdirectory for each value of the partition column, e.g.:
    -------- /user/hive/warehouse/foo/dt=2009-03-20/
    • -------- Queries with partition columns in WHERE clause will scan through only a subset of the data

    DML OPERATIONS

    Examples:
  • Loads a file that contains two columns separated by ctr-a into wikitechy table. 'LOCAL' signifies that the input is on the local file system.
  • If 'LOCAL' is omitted then it looks for the file in HDFS. The keyword 'OVERWRITE' signifies that existing data in the table is deleted.
  • If the 'OVERWRITE' keyword is omitted, data files are appended to existing data sets.

    • --------hive> LOAD DATA LOCAL INPATH './examples/files/kv1.txt'
    --------hive> OVERWRITE INTO TABLE wikitechy;

  • The two LOAD statements below load data into two different partitions of the table invites.
  • Table invites must be created as partitioned by the key ds for this to succeed.

    • --------hive> LOAD DATA LOCAL INPATH './examples/files/kv2.txt' OVERWRITE INTO TABLE invite PARTITION (ds='2008-08-15');
    --------hive> LOAD DATA LOCAL INPATH './examples/files/kv3.txt' OVERWRITE INTO TABLE invites PARTITION (ds='2008-08-08');

  • The below command will load data from an HDFS file/directory to the table.
  • Note that loading data from HDFS will result in moving the file/directory.

    • --------hive> LOAD DATA INPATH '/user/myname/kv2.txt';
    --------hive>OVERWRITE INTO TABLE invites PARTITION (ds='2008-08-15');

    apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql

    Hive Commands

    • Hive supports Data definition Language(DDL), Data Manipulation Language(DML) and User defined functions.

    Hive DDL Commands

    • create database
    • drop database
    • create table
    • drop table
    • Truncate table
    • alter table
    • create index
    • create views

    Create Database in Hive

    • DDL command in Hive is used for creating databases.
    apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql

    Syntax 

    CREATE (DATABASE) [IF NOT EXISTS] database_name

  [COMMENT database_comment]

  [LOCATION hdfs_path]

  [WITH DBPROPERTIES (property_name=property_value, ...)];
    Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team
    • In the above syntax for create database command, the values mentioned in square brackets [] are optional.

    Drop Database in Hive

    • This command is used for deleting an already created database in Hive.
    apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql

    Syntax 

    DROP (DATABASE) [IF EXISTS] database_name [RESTRICT|CASCADE];
    Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team

    Alter Database Command in Hive

    • The developers want to change the metadata of any of the databases, can be used alter hive DDL command.
    apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql

    Syntax 

    ALTER (DATABASE) database_name SET DBPROPERTIES (property_name=property_value, ...);
    Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team
    apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql

    DDL Commands on Tables in Hive

    Create Table Command in Hive

    • Hive create table command is used to create a table in the existing database that is in use for a particular session.
    apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql

    Syntax 

    CREATE  TABLE [IF NOT EXISTS] [db_name.]table_name    --

  [(col_name data_type [COMMENT col_comment], ...)]

  [COMMENT table_comment]

   [LOCATION hdfs_path]
    Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team

    DROP Table Command in Hive

    • Drops the table and all the data associated with it in the Hive metastore.

    Syntax 

    DROP TABLE [IF EXISTS] table_name [PURGE];
    Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team

    TRUNCATE Table Command in Hive

    • This hive command is used to truncate all the rows present in a table.
    • It is deletes all the data from the Hive meta store and the data cannot be restored.
    apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql

    Syntax 

    TRUNCATE TABLE [db_name].table_name
    Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team

    ALTER Table Command in Hive

    • To use ALTER Table command, the structure and metadata of the table can be modified even after the table has been created.
    • Let us try to change the name of an existing table using the ALTER command

    Syntax 

    ALTER TABLE [db_name].old_table_name RENAME TO [db_name].new_table_name;
    Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team

    Hive DML Commands

    • DML (Data Manipulation Language) commands in Hive are used for inserting and querying the data from hive tables once the structure and architecture of the database has been defined using the DDL commands.
      • Select
      • Where
      • Group By
      • Order By
      • Load Data
      • Join
    apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql

    SELECT Table command in Hive

    • · Let check whether the data has been loaded correctly by selecting the rows from Student_name.

    Example 

    hive> select * from Student_name;
    Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team

    Where Table command in Hive

    • A WHERE clause is used to filter the result set by using predicate operators and logical operators.
    • Functions can also be used to compute the condition.
      • List of Predicate Operators
      • List of Logical Operators
      • List of Functions

    Example 

    hive> SELECT name FROM table_name WHERE name = 'xxxxx';
    Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team

    GROUP BY

    • Contrast this to the GROUP BY clause, which is used to group like column values into a single row.
    • This is useful as it allows you to summarize information.
    • For example you can use aggregate functions such as SUM and AVERAGE to calculate values.

    Example 

    SELECT   SalesOrderID,
         SUM(OrderQty* UnitPrice) As TotalPrice
FROM     Sales.SalesOrderDetail
GROUP BY SalesOrderID
    Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team
    apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql

    ORDER BY

    • The ORDER BY statement is used to sort values.

    Example 

    SELECT   SalesOrderID,
         ProductID,
         OrderQty* UnitPrice As ExtendedPrice
FROM     Sales.SalesOrderDetail
ORDER BY SalesOrderID
    Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team
    • It will sort the value, according to SalesOrderID.
    • Every row in the table is included in the result. The values are sorted in ascending order according to the SalesOrderID.
    apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql

    LOAD DATA Statement

    • The LOAD DATA statement streamlines the ETL process for an internal Impala table by moving a data file or all the data files in a directory from an HDFS location into the Impala data directory for that table.

    Syntax: 

    LOAD DATA INPATH 'hdfs_file_or_directory_path' [OVERWRITE] INTO TABLE tablename
  [PARTITION (partcol1=val1, partcol2=val2 ...)]
    Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team
    • When the LOAD DATA statement operates on a partitioned table, it always operates on one partition at a time.
    • Require the PARTITION clauses and list all the partition key columns, with a constant value specified for each.
    apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql

    Hive Special Commands

    learn hive - hive tutorial - hive run query command - hive programs - hive examples

    learn hive - hive tutorial - hive run query command - hive programs - hive examples

    apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql

    Hive Shell Commands

    learn hive - hive tutorial - hive shell command - hive programs - hive examples

    learn hive - hive tutorial - hive shell command - hive programs - hive examples

    Wikitechy Apache Hive tutorials provides you the base of all the following topics . Enjoy learning on big data , hadoop , data analytics , big data analytics , mapreduce , hadoop tutorial , what is hadoop , big data hadoop , apache hadoop , apache hive , hadoop wiki , hadoop jobs , hadoop training , hive tutorial , hadoop big data , hadoop architecture , hadoop certification , hadoop ecosystem , hadoop fs , apache pig , hadoop cluster , cloudera hadoop , hadoop download , hadoop mapreduce , hadoop workflow , hive data types , hadoop hive , pig hadoop , hadoop administration , hadoop installation , hive hadoop , learn hadoop , hadoop for dummies , hadoop commands , hive definition , hiveql , learnhive , hive sql , hive database , hive date functions , hive query , apache hive tutorial , hive apache , hive wiki , what is a hive , hive big data , programming hive , what is hive in hadoop , hive documentation , how does hive work

    Related Searches to Hive Commands

    hive commandshive sql commandshive shell commandshive set commandload data local command in hivehadoop commands with exampleshive command linehive commands pdfbeeline commandshadoop commands listhadoop hive commandshive commands in hadoophive basic commandshive commands with examplesapache hive commandsbasic hive commandshive commands in shell scripthive version commandhive text commandshive hdfs commandshive command optionshive database commandsinsert command in hivehive commands examples
    Wikitechy Rated 5 / 5 based on 378284 reviews.
    Wikitechy-logo

    World's No 1 Animated self learning Website with Informative tutorials explaining the code and the choices behind it all.

    Wikitechy

    About Us
    Terms of Use
    Privacy Policy
    Contact Us

    Workshop

    Webinar

    Join our Community

    Advertise

    Other Languages

    English
    Chinese
    Deutsch
    Russian

    © 2016 - 2024 KaaShiv InfoTech, All rights reserved. Powered by Inplant Training in chennai | Internship in chennai